※오라클 자주 사용하는 쿼리 & 명령어

※오라클 자주 사용하는 쿼리 & 명령어

1.특정테이블만 분석할때

analyze table 테이블명 compute statistics

2.특정테이블의 인덱스를 검색할때

SELECT A.UNIQUENESS,

       B.*

  FROM ALL_INDEXES A,

       ALL_IND_COLUMNS B

 WHERE A.INDEX_NAME = B.INDEX_NAME

   AND A.TABLE_NAME=UPPER('테이블명');

3.특정컬럼을 가지고 있는 테이블 조회

   SELECT *

    FROM ALL_TAB_COLUMNS

  WHERE COLUMN_NAME = '컬럼명'

4.MERGE(UPDATE&INSERT)

MERGE INTO  WMS_ETC_DTL A

    USING ( SELECT  'TS1' AS WH_CD, 'AS201607110011' AS ORD_NO, '1' AS ORD_SEQ,'동태LLL' SKU_NM  FROM DUAL) B - 가상테이블 생성

    ON (A.WH_CD=B.WH_CD AND A.ORD_NO=B.ORD_NO AND A.ORD_SEQ=B.ORD_SEQ) - 조건부분

    WHEN MATCHED THEN -조건 TRUE일 경우

              UPDATE SET   SKU_NM=B.SKU_NM

     WHEN NOT MATCHED THEN -조건 FALSE일 경우

            INSERT  ( WH_CD

                    , ORD_NO

                    , ORD_SEQ

                    , SKU_NM

            ) VALUES

                    (B.WH_CD,B.ORD_NO,B.ORD_SEQ,B.SKU_NM

            );

5.OVER(PARTITION BY~) 

:OVER함수는 ORDER BY, GROUP BY 서브쿼리를 개선하기 위해 나온 함수라고 할 수 있다.

:PARTITION BY (컬럼명) 은 (컬럼명) 기준으로 각각 그룹단위로 나눈다고 보면 된다.

COUNT(*)OVER() : 전체행 카운트

COUNT(*)OVER(PARTITION BY 컬럼) : 그룹단위로 나누어 카운트

MAX(컬럼)OVER() : 전체행 중에 최고값

MAX(컬럼)OVER(PARTITION BY 컬럼) : 그룹내 최고값

MIN(컬럼)OVER() : 전체행 중에 최소값

MIN(컬럼)OVER(PARTITION BY 컬럼) : 그룹내 최소값

SUM(컬럼)OVER() : 전체행 합

SUM(컬럼)OVER(PARTITION BY 컬럼) : 그룹내 합

AVG(컬럼)OVER() : 전체행 평균

AVG(컬럼)OVER(PARTITION BY 컬럼) : 그룹내 평균

ex)숫자           : 10,20,30,40,40,50

RANK()         :  1,2,3,4,4,6

DESEN_RANK()   :  1,2,3,4,4,5

ROW_NUMBER()   :  1,2,3,4,5,6

RANK() OVER (PARTITION BY A.HBL_NO ORDER BY SUM(CNTR_WGT) DESC) RANK

DESEN_RANK() OVER (PARTITION BY A.HBL_NO ORDER BY SUM(CNTR_WGT) DESC) RANK

ROW_NUMBER() OVER (PARTITION BY A.HBL_NO ORDER BY SUM(CNTR_WGT) DESC) RANK

6.IP확인

select SYS_CONTEXT('USERENV', 'IP_ADDRESS', 15) ipaddr from dual

7.프로시저 내용 검색

SELECT * FROM USER_SOURCE WHERE TYPE = 'PROCEDURE' AND TEXT LIKE '%USP_WO_PROFIT%'

8.함수 내용 검색

SELECT * FROM USER_SOURCE WHERE TYPE = 'FUNCTION' AND TEXT LIKE '%USP_WO_PROFIT%'

9.소수점

ROUND() - 반올림

 1)SELECT ROUND(2345.123,2) FROM DUAL => 2345.12

 2)SELECT ROUND(2345.626,2) FROM DUAL => 2346.63

TRUNC() - 소수점 자르기

 1)SELECT TRUNC(12345.5555,2) FROM DUAL => 12345.55

10.WITH

WITH AA AS ( SELECT * FROM TMS_SO_ALOC_MST WHERE SO_NO = 'AEI2018112000113'),

      BB AS ( SELECT * FROM TMS_SO_MST WHERE SO_NO = 'AEI2018112000113')

      SELECT * FROM AA A, BB B WHERE A.SO_NO = B.SO_NO

11.시간측정

SET TIMING ON; - 쿼리위에 입력하고 F5

12.ORACLE Characterset 조회 및 변경

SELECT * FROM NLS_SESSION_PARAMETERS

ALTER SESSION SET NLS_DATE_LANGUAGE = 'AMERICAN' 수정코드

13.다른유저 테이블 SELECT 권한주기

GRANT SELECT ANY TABLE TO ELVIS_MCI;

14.튜닝(Ctrl+E)

Explain plan 창으로 이동함 !SQL 명렁어가 올바르게 종료되지 않았습니다. 가 뜨면 쿼리마다 ;(세미클론)을 넣어줘야함.

16.곱 합산

SELECT EXP(SUM(LN(EXCH_BASERT)))

FROM MDM_EX_RATE

 

예)LN(N) 자연로그값 반환 EXP(N) 지수함수로 반환

 

LN(2) = 0.693147180559945

LN(5) = 1.6094379124341

-----

EXP(1) = 2.71828182845905

 

LN(2)+LN(5) = 2.30258509299405 => +는 곱으로 -는 나눗셈으로 보면된다.

EXP(2.30258509299405) = 10

 

LN(2)-LN(5) = -0.916290731874155

EXP(-0.916290731874155) = 0.4

 

17.나누기 합산

EXP(SUM(DECODE(ROWNUM,1,LN(EXCH_BASERT),LN(EXCH_BASERT)*-1)))

FROM MDM_EX_RATE

18.테이블 스페이스 변경

ALTER INDEX 인덱스명 REBUILD TABLESPACE 테이블스페이스명  

ALTER TABLE 테이블명 MOVE TABLESPACE 테이블스페이스명

19.계층적 쿼리 추출

SELECT REGEXP_SUBSTR(A.MYDATAS,'[^,]+', 1, ROWNUM) AS MYDATA

 FROM (SELECT 'AIR,SEA,TMS,ALL' AS MYDATAS FROM DUAL) A

CONNECT BY ROWNUM <= LENGTH(REGEXP_REPLACE(MYDATAS,'[^,]+')) + 1

20.문자열 합치기(10g 까진 WM_CONCAT, 11G부터는 LISTAGG 사용가능)

*LISTAGG(합칠 컬럼, 구분자) WITHIN GROUP (ORDER BY 순서컬럼)

SELECT GRP_CD,OPT_ITEM1,LISTAGG(COMN_CD, ',') WITHIN GROUP (ORDER BY ROWNUM) AS RESULT  FROM MDM_COM_CODE WHERE GRP_CD='M33' GROUP BY GRP_CD,OPT_ITEM1