회사원에서 개발자로~

# 소스 관리

데이터 딕셔너리

 - USER_*: 현재 접속된 스키마(사용자)가 소유한 DB 객체에 관한 정보를 담고 있는 뷰

 - ALL_*: 현재 접속된 스키마(사용자)가 접근할 수 있는 모든 DB 객체에 관한 정보를 담고 있는 뷰

 - DBA_*: 모든 DB 객체에 관한 정보를 담고 있는 뷰

ALL_TABLES: 현 스키마가 소유자인 모든 테이블의 목록

ALL_TAB_COLS: 테이블에 있는 컬럼에 대한 모든 정보

ALL_CONSTRAINTS: 제약사항 정보

ALL_INDEXS: 인덱스 정보

ALL_TAB_COMMENTS: 테이블 주석 정보

ALL_COL_COMMENTS: 컬럼 주석 정보

ALL_OBJECTS: 테이블 뿐만 아니라 프로시저, 함수, 인덱스 등 모든 DB객체에 대한 정보를 가진 뷰

ALL_PROCEDURES: 프로시저와 함수에 대한 정보를 가진 뷰, 패키지에 포함된 함수/프로시저 정보도 포함됨

ALL_ARGUMENTS: 함수/프로시저의 매개변수 정보

ALL_DEPENDENCIES: 객체간 서로 참조하는 정보를 가진 뷰

ALL_SOURCE: 프로시저, 함수, 패키지 등의 모든 프로그램의 소스 정보를 담고 있는 뷰

# 디버깅

 - DBMS_OUTPUT.PUT_LINE 프로시저

 - SQL%ROWCOUNT 커서속성 활용. 변경건수확인

 - 변수값 확인

 - SYSDATE 통해 실행시간 확인

 - 로그 테이블 활용

* 로우를 컬럼으로 전환하기

CREATE TABLE CH14_SCORE_TABLE (

  YEARS      VARCHAR2(4),

  GUBUN    VARCHAR2(30),

  SUBJECTS VARCHAR2(30),

  SCORE     NUMBER);

① DECODE 또는 CASE

SELECT YEARS

         , GUBUN

         , SUM(국어) 국어

         , SUM(영어) 영어

  FROM (

           SELECT YEARS

                    , GUBUN

                    , CASE WHEN SUBJECTS = '국어' THEN SCORE ELSE 0 END "국어"

                    , CASE WHEN SUBJECTS = '영어' THEN SCORE ELSE 0 END "영어"

            FROM CH14_SCORE_TABLE

           )

GROUP BY YEARS, GUBUN;

② WITH 절

WITH MAINS AS (

                       SELECT YEARS

                                , GUBUN

                                , CASE WHEN SUBJECTS = '국어' THEN SCORE ELSE 0 END "국어"

                                , CASE WHEN SUBJECTS = '영어' THEN SCORE ELSE 0 END "영어"

                        FROM CH14_SCORE_TABLE

                       )

SELECT YEARS

         , GUBUN

         , SUM(국어) 국어

         , SUM(영어) 영어

  FROM MAINS

GROUP BY YEARS, GUBUN;

③ PIVOT

SELECT *

  FROM (

           SELECT YEARS

                   , GUBUN

                   , SUBJECTS

                   , SCORE

             FROM CH14_SCORE_TABLE

           )

  PIVOT (

           SUM(SCORE)

            FOR SUBJECTS IN ('국어', '영어')

           );

* 컬럼을 로우로 전환

CREATE TABLE CH14_SCORE_COL_TABLE (

  YEARS      VARCHAR2(4),

  GUBUN    VARCHAR2(30),

  KOREAN   NUMBER,

  ENGLISH   NUMBER);

① UNION ALL

SELECT YEARS, GUBUN, '국어' SUBJECT, KOREAN SCORE

  FROM CH14_SCORE_COL_TABLE

UNION ALL

SELECT YEARS, GUBUN, '영어' SUBJECT, ENGLISH SCORE

  FROM CH14_SCORE_COL_TABLE

ORDER BY 1, 2 DESC;

② UNPIVOT

SELECT *

  FROM CH14_SCORE_COL_TABLE

  UNPIVOT (

               SCORE

               FOR SUBJECTS IN (

                                       KOREAN '국어'

                                     , ENGLISH '영어'

                                       )

              );

# 동적SQL 2가지 방법

 - NDS(Native Dynamic SQL)

 - DBMS_SQL 시스템 패키지

BULK COLLECT INTO

 - 컬렉션 변수에 결과 집합을 담는다. Cursor와 같이 하나의 Row를 Fetch해서 사용하는 것이 아니라 다중 Row를 한번에 사용한다.

SELECT *

  BULK COLLECT INTO vr_physicist

 FROM CH13_PHYSICIST;

FOR i IN 1..vr_physicist.count

LOOP

  DBMS_OUTPUT.PUT_LINE(vr_physicist(i).names);

END LOOP;

# 커서

: 특정 SQL 문장을 처리한 결과를 담고 있는 영역(PRIVATE SQL이라는 메모리 영역)을 가리키는 일종의 포인터

 - 묵시적 커서: PL/SQL 블록에서 실행하는 SQL 문장(DML, SELECT INTO) 실행될 때마다 자동으로 만들어지는 커서

 - 명시적 커서: 사용자가 직접 정의해서 사용

묵시적 커서 속성

 - SQL%FOUND: 결과 집합의 패치 로우 수가 1개 이상이면 TRUE, 아니면 FALSE

 - SQL%NOTFOUND: 로우 수가 0이면 TRUE, 아니면 FALSE

 - SQL%ROWCOUNT: 영향 받은 결과 집합의 로우 수 반환, 없으면 0

명시적 커서

 1) 커서 선언

 CURSOR 커서명[(매개변수1, 매개변수2, ...)]

 IS

 SELECT 문장;

 2) 커서 열기

 OPEN 커서명 [(매개변수1, 매개변수2, ...)]

 3) 패치 단계에서 커서 사용

 LOOP

   FETCH 커서명 INTO 변수1, 변수2,...;

   EXIT WHEN 커서명%NOTFOUND;

 END LOOP;

 4) 커서 닫기

 CLOSE 커서명;

* 커서변수

* 커서표현식

# 레코드

 - 사용자 정의형 레코드

 - 테이블형 레코드

• 변수명 테이블명.컬럼명%TYPE
• 레코드변수명 테이블명%ROWTYPE

 - 커서형 레코드

• 커서명%ROWTYPE

 * 중첩 레코드

# 컬렉션

# 예외처리

<예외처리 구문>

EXCEPTION WHEN 예외명1 THEN 예외처리 구문1

                WHEN 예외명2 THEN 예외처리 구문2

                ...

                WHEN OTHERS THEN 예외처리 구문n;

예외 정보를 참조하기 위해 오라클에서 SQLCODE, SQLERRM이란 빌트인 함수를 제공한다.

자세한 예외 정보 확인하기

 - DBMS_UTILITY.FORMAT_ERROR_BACKTRACE

 - DBMS_UTILITY.FORMAT_ERROR_STACK

 - DBMS_UTILITY.FORMAT_CALL_STACK

* 사용자 정의 예외

CREATE OR REPLACE ...

IS

  변수...

  ex_invalid_depid EXCEPTION;

BEGIN

  ...

  IF vn_cnt = 0 THEN

    RAISE ex_invalid_depid;

  END IF;

  ...

  COMMIT;

EXCEPTION

WHEN ex_invalid_depid THEN

  DBMS_OUTPUT.PUT_LINE('해당 부서번호가 없습니다');

WHEN OTHERS THEN

  ...

END;

# 트랜잭션

# PL/SQL 기본구조

<블록>

이름부

IS(AS)

  선언부

BEGIN

  실행부

EXCEPTION

  예외 처리부

END;

# 제어문

<IF 문>

IF 조건1 THEN

  조건처리1;

ELSE IF 조건2 THEN

  조건처리2;

ELSE

  조건처리n;

END IF;

<LOOP문>

LOOP

  처리문;

  EXIT [WHEN 조건];

END LOOP;

<WHILE문>

WHILE 조건

LOOP

  처리문;

END LOOP;

<FOR문>

FOR i IN [REVERSE] 1..9

LOOP

  처리문;

END LOOP;

<CONTINUE>

FOR i IN 1..9

LOOP

   CONTINUE WHEN i=5;

   DBMS_OUTPUT.PUT_LINE(i);

END LOOP;

# 사용자 정의 함수

<함수>

CREATE OR REPLACE FUNCTION 함수 이름 (매개변수1, 매개변수2, ...)

RETURN 데이터타입;

IS[AS]

  변수, 상수 등 선언

BEGIN

  실행부

  RETURN 반환값;

[EXCEPTION

  예외 처리부]

END [함수 이름];

<프로시저>

CREATE OR REPLACE PROCEDURE 프로시저 이름

  (매개변수1 [IN | OUT | IN OUT] 데이터타입 [:= 디폴트 값],

   매개변수2 [IN | OUT | IN OUT] 데이터타입 [:= 디폴트 값],

   ...

  )

IS[AS]

  변수, 상수 등 선언

BEGIN

  실행부

[EXCEPTION

  예외 처리부]

END [프로시저 이름];

*변수, 상수, 매개변수 명명법

 - 변수: vn_goods_cnt, vc_ord_no

 - 상수: cn_max_ord_psbt_cnt

 - 매개변수: pn_ord_cqty, pc_cust_no

조인의 종류

 - 조인 연산자에 따른 구분: 동등 조인, 안티 조인

 - 조인 대상에 따른 구분: 셀프 조인

 - 조인 조건에 따른 구분: 내부 조인, 외부 조인, 세미 조인, 카타시안 조인

 - 기타: ANSI 조인

동등 조인: =

세미 조인: IN, EXISTS

안티 조인: NOT IN, NOT EXISTS

셀프 조인: 동일 테이블 조인

외부 조인: OUTER JOIN은 조인 조건에 명시된 컬럼에 값이 NULL이거나 ROW가 없더라도 데이터를 모두 추출

※ OUTER JOIN

 1) 조인 대상 테이블 중 데이터가 없는 테이블 조인 조건에 (+)를 붙인다

 2) 외부 조인의 조인 조건이 여러 개일 때 모든 조건에 (+)를 붙인다

 3) 한 번에 한 테이블에만 외부 조인을 할 수 있다.

 4) (+)연산자가 붙은 조건과 OR를 같이 사용할 수 없다

 5) (+)연산자가 붙은 조건에는 IN 연산자를 같이 사용할 수 없다(단 IN절에 포함되는 값이 1개이면 사용 가능)

카타시안 조인: WHERE 절에 조인 조건이 없는 조인

ANSI 조인

서브쿼리 종류

 - 메인 쿼리와의 연관성에 따라

   > 연관성 없는 서브 쿼리

   > 연관성 있는 서브 쿼리

 - 형태에 따라

   > 일반 서브 쿼리(SELECT 절)

   > 인라인 뷰(FROM 절)

   > 중첩 쿼리(WHERE 절)

COUNT(expr)

 - NULL이 아닌 ROW수만 반환

 - COUNT(*) : 전체 로우 반환(NULL 포함)

SUM(expr)

AVG(expr)

MIN(expr), MAX(expr)

VARIANCE(expr), STDDEV(expr)

GROUP BY, HAVING

ROLLUP, CUBE

 - ROLLUP(expr1, expr2, ...) : expr로 명시한 표현식 기준으로 집계한 결과 뿐 아니라 추가적인 집계 정보도 보여준다.

   레벨별로 집계한 결과 반환. 표현식 개수가 n개이면 n+1 레벨까지 하위 레벨에서 상위 레벨 순으로 데이터 집계

 - CUBE(expr1, expr2, ...) : ROLLUP이 레벨별로 순차적 집계를 했다면, CUBE는 명시한 표현식의 모든 경우의 수를 조합     별로 집계한 결과를 반환. 2^(expr개수) 만큼 종류별로 집계

UNION, UNION ALL

 - UNION은 중복 제거

 - UNION ALL은 중복 포함

MINUS

INTERSECT

※ 집합 연산자 제한사항

 1) 각 SELECT문의 리스트 개수와 데이터 타입이 일치해야 한다

 2) ORDER BY절은 맨 마지막 문장에서만 사용가능

 3) BLOB, CLOB, BFILE 타입 컬럼은 집합 연산자 사용 불가

 4) UNION, INTERSECT, MINUS 연산자는 LONG형 컬럼에서 사용 불가

GROUPING SETS

 - GROUP BY GROUPING SETS(expr1, expr2, expr3) : (GROUP BY expr1) UNION ALL (GROUP BY expr2) UNION ALL (GROUP BY expr3)