Oracle 2 - ynjch97/YNJCH_WIKI GitHub Wiki

참고 사이트
- https://thebook.io/006696/part03/ch14/01/
- https://grandma-coding.tistory.com/entry/OracleWITH%EC%A0%88

1. GTT(Global Temporary Table, 전역 임시 테이블)

Oracle에서 사용하는 임시 테이블을 Global Temporary 테이블이라고 함
생성 방법에 따라 트랜잭션 GTT, 세션 GTT로 구분
트랜잭션 GTT : 같은 트랜잭션 내에서만 데이터가 유지
세션 GTT : 같은 세션 내에서 데이터가 유지

1-1. 트랜잭션 GTT

트랜잭션이 살아 있는 동안에만 데이터가 유지됨
CREATE 다음에 "GLOBAL TEMPORARY", 맨 마지막에 "ON COMMIT DELETE ROWS" 구문을 추가
ON COMMIT DELETE ROWS : COMMIT 시에 row를 삭제하라는 의미
- DML로 GTT 테이블에 데이터를 생성, 수정할 당시에는 데이터가 남아 있지만 COMMIT을 실행하면 모든 데이터(Row)가 없어짐
- 생략이 가능하므로 이 구문을 빼고 GTT를 생성하면 디폴트로 트랜잭션 GTT가 만들어짐

CREATE GLOBAL TEMPORARY TABLE 테이블명 (
    컬럼1 데이터타입, ...
)
[ON COMMIT DELETE ROWS];

1-1-1. 트랜잭션 GTT 예제

아래와 같이 테이블 생성

CREATE GLOBAL TEMPORARY TABLE TX_GTT (
    NO      NUMBER,
    NAME    VARCHAR(20)
)
ON COMMIT DELETE ROWS;

데이터 INSERT 후, COMMIT 전후의 데이터 유지 여부 확인하기

DECLARE
    txGttCnt NUMBER;
BEGIN
    INSERT INTO TX_GTT (NO, NAME)
    SELECT 1, 'Kate' FROM DUAL
    UNION ALL 
    SELECT 2, 'Bob' FROM DUAL
    UNION ALL
    SELECT 3, 'Alex' FROM DUAL
    ;
    SELECT COUNT(1) INTO txGttCnt FROM TX_GTT;
    dbms_output.put_line('txGttCnt : ' || txGttCnt);
    COMMIT;
    SELECT COUNT(1) INTO txGttCnt FROM TX_GTT;
    dbms_output.put_line('txGttCnt : ' || txGttCnt);
END
;

/* 결과
txGttCnt : 6
txGttCnt : 0
*/

1-2. 세션 GTT

같은 세션 내에 있는 한 데이터가 유지됨
ON COMMIT PRESERVE ROWS는 COMMIT을 실행한 후에도 데이터(로우)를 보존하라는 뜻
COMMIT 여부에 상관없이 같은 세션에서 데이터가 보존됨(다른 세션에 있는 사용자는 이 데이터를 공유할 수 없음)
- 세션에서만 데이터가 공유되며, 세션을 종료하면 데이터는 사라짐
- 테이블 이름 앞에 '#'이 붙는 MSSQL의 임시 테이블과 같은 특성을 가짐

CREATE GLOBAL TEMPORARY TABLE 테이블명 (
    컬럼1 데이터타입, ...
)
ON COMMIT PRESERVE ROWS;

1-2-1. 세션 GTT 예제

아래와 같이 테이블 생성

CREATE GLOBAL TEMPORARY TABLE SS_GTT (
    NO      NUMBER,
    NAME    VARCHAR(20)
)
ON COMMIT PRESERVE ROWS;

데이터 INSERT 후, COMMIT 전후의 데이터 유지 여부 확인하기

DECLARE
    ssGttCnt NUMBER;
BEGIN
    INSERT INTO SS_GTT
    SELECT 1, 'Alex' FROM DUAL
    UNION ALL 
    SELECT 2, 'Rose' FROM DUAL
    UNION ALL 
    SELECT 3, 'Peter' FROM DUAL;
    SELECT COUNT(1) INTO ssGttCnt FROM SS_GTT;
    dbms_output.put_line('ssGttCnt : ' || ssGttCnt);
    COMMIT;
    SELECT COUNT(1) INTO ssGttCnt FROM SS_GTT;
    dbms_output.put_line('ssGttCnt : ' || ssGttCnt);
END;

/* 결과
ssGttCnt : 6
ssGttCnt : 6
*/

1-3. GTT의 특징

GTT의 한계
- 파티션 GTT를 만들 수 없음
- GTT에 인덱스는 만들 수 있으나 외래키를 만들 수 없음
- 병렬로 UPDATE, DELETE, MERGE 문을 실행할 수 없음
- GTT 컬럼으로는 중첩 테이블 타입을 사용할 수 없음
GTT의 활용

여러 개의 테이블을 조인해서 복잡한 연산을 수행한 결과를 보여주는 리포트를 만들어야 하는데 해당 결과를 산출하기에는 단일 SELECT문으로 구현하기가 어렵다고 하자. 이럴 때, 과거에는 최종 리포트 구조에 맞게 테이블을 만들고 프로시저 안에서 여러 단계에 걸쳐 데이터를 입력하고 조작해 원하는 결과를 만들었다. 이렇게 하면 리포트 화면에서는 WHERE 조건도 필요 없는 단순 SELECT문만 실행하면 됐다.
하지만 이 방법은, 다른 세션의 사용자가 거의 동시에 같은 프로시저를 실행하고 결과를 조회하면 데이터가 중복되거나 누락되는 등의 문제가 발생할 소지가 있다. 하지만 GTT를 사용하면 데이터가 세션별로 관리되므로 데이터가 꼬이는 현상이 발생할 가능성은 거의 없을 뿐만 아니라 원하던 기능도 구현할 수 있다.

2. WITH 문

이름을 가진 SubQuery Block을 정의한 후 사용하는 구문
Query의 전체적인 가독성을 높이고, 재사용할 수 있음
- 오라클 공유 메모리에 임시 테이블을 생성하여 반복 재사용이 가능하도록 함
- 자주 실행되는 경우 한 번만 Parsing되고 Plan 계획이 수립됨
- 쿼리의 성능 향상 : 동일 테이블 접근을 최소화하며 메모리에 생성된 임시 테이블에서 필요한 데이터를 메모리로 접근하기 때문에 디스크 IO로 테이블에 접근하는 것보다 효율적
계층형 쿼리 구현 가능
대부분의 DBMS에서 지원, 모든 DML에서 사용 가능
참고) UNION ALL, DECODE/CASE, WITH, ROLLUP/Grouping Sets 문장들과 서로 형태 변환이 가능하여 함께 쓰면 성능상 유리

2-1. WITH 문 구조

WITH [별명] AS (SUB QUERY)
컬럼명은 생략할 수 있음
쉼표(,)로 구분하여 여러개를 정의 가능
먼저 생성된 SubQuery는 나중에 생성하는 SubQuery에서 사용할 수 있음
- 예) [별명2]에서 [별명1]을 사용할 수 있음.

WITH [별명1] [(컬럼명1 [,컬럼명2])] AS (
    SUB QUERY
) [,별명2 AS ...]
MAIN QUERY

Oracle에서는 한 번만 사용되면 Inline View, 두 번 이상 사용되면 Materialize View로 처리함
- 한번도 사용하지 않으면 [ORA-01762] 오류 발생
- /*+ Materialize */ 힌트로 Inline View를 Materialize View로 만들 수 있음

2-2. WITH 문 예제

WITH TEMP_USER_INFO_SCORE AS 
(
    SELECT I.USER_NUM, I.USER_NM, I.USER_BIRTH, S.USER_GRADE
    FROM USER_INFO I
    LEFT JOIN USER_SCORE S
    ON I.USER_NUM = S.USER_NUM
)
SELECT 
    TEMP.USER_NUM
    , TEMP.USER_NM
    , TEMP.USER_BIRTH
    , TEMP.USER_GRADE
FROM TEMP_USER_INFO_SCORE TEMP
WHERE USER_GRADE <> 'A'
;

3. 사용자 함수

3-1. 기본 함수 생성

CREATE OR REPLACE : CREATE 만 사용하여 재컴파일 시 오류가 발생하므로 REPLACE 를 함께 사용

CREATE OR REPLACE FUNCTION [함수명] ([파라미터명] [파라미터타입])
    RETURN [리턴타입]
IS
    [변수명] [변수타입]
BEGIN
    (생략)
    RETURN [리턴값(변수 등)]
END;
/

-- 예시
CREATE OR REPLACE FUNCTION fn_get_user_nm (p_user_num NUMBER)
    RETURN VARCHAR2
IS 
    v_user_nm VARCHAR2(30);
BEGIN
    SELECT USER_NM INTO v_user_nm
    FROM USER_INFO 
    WHERE USER_NUM = p_user_num;
    RETURN v_user_nm;
END; 
/

3-1-1. 기본 함수 사용

SELECT
    USER_NUM
    , fn_get_user_nm(USER_NUM) AS USER_NM
FROM USER_SCORE;

3-2. 파이프라인 테이블 함수 생성

Object 타입, 테이블 타입을 정의하여, 파이프라인 테이블 함수 생성 시에 사용
파이프라인 테이블 함수 사용 시 장점
- 결과 집합이 모두 생성될 때까지 기다리지 않고 완료된 부분을 순차적으로 처리하므로 응답 시간이 빠름
- 스트리밍(하나의 연산을 종료하지 않고 일련의 연산을 순차적으로 반복 처리)
- 파이프라이닝(여러 연산을 연속해서 수행)
- 유연성(SQL 문장으로 표현하기 어려운 과정을 유연하게 처리)

3-2-1. Object 타입 생성

함수에서 반환하는 레코드의 스키마를 정의

CREATE OR REPLACE TYPE OBJ_STORE AS OBJECT (
    USER_NUM NUMBER(10)
    , USER_NM VARCHAR2(20)
    , STORE_NUM NUMBER(10)
    , STORE_NM VARCHAR2(50) 
    , STORE_ADDR VARCHAR2(30)
);

3-2-2. 테이블 타입 정의

함수에서 반환하는 레코드의 집합(테이블) 정의

CREATE OR REPLACE TYPE TABLE_STORE AS TABLE OF OBJ_STORE;

3-2-3. 파이프라인 테이블 함수 작성

RETURN 타입은 위의 테이블 타입으로 지정
변수는 위의 Object 타입으로 지정

CREATE OR REPLACE FUNCTION [함수명] ([파라미터명] [파라미터타입])
    RETURN [리턴타입] PIPELINED
IS
    [변수명] [변수타입]
BEGIN
    (생략)
    RETURN;
END;

-- 예시
CREATE OR REPLACE FUNCTION fn_get_store_info (p_user_num NUMBER)
    RETURN TABLE_STORE PIPELINED
IS
    v_obj_store OBJ_STORE;
BEGIN 
    FOR v_row IN (
        SELECT 
            U.USER_NUM
            , fn_get_user_nm(U.USER_NUM) AS USER_NM
            , S.STORE_NUM
            , S.STORE_NM
            , S.STORE_ADDR
        FROM USER_INFO U
        INNER JOIN STORE_INFO S
        ON U.USER_NUM = S.OWNER_NUM
        WHERE U.USER_NUM = p_user_num
    ) LOOP
        v_obj_store := OBJ_STORE(v_row.USER_NUM, v_row.USER_NM, v_row.STORE_NUM, v_row.STORE_NM, v_row.STORE_ADDR);
        PIPE ROW(v_obj_store);
    END LOOP;
    RETURN;
END;
/

3-2-4. 파이프라인 테이블 함수 사용

SELECT * FROM TABLE(fn_get_store_info(1));

3-3. 함수 조회

SELECT * FROM USER_SOURCE WHERE TYPE = 'FUNCTION';
SELECT * FROM USER_OBJECTS WHERE OBJECT_TYPE = 'FUNCTION';
-- 삭제
DROP FUNCTION [함수명];

4. 프로시저

Transact-SQL 문장의 집합
프로시저는 PL/SQL을 통해 만들어짐
자주 사용하는 SQL문을 프로시저로 만들어 필요할 때마다 호출, 사용하여 작업 효율 증가
특정 로직을 처리할 뿐 결과 값은 반환하지 않음
SQL Server에서 사용하는 프로그래밍 기능
빠르고, 여러 어플리케이션과 공유할 수 있음

4-1. 프로시저 생성

IN 뒤에 인수의 타입을 선언
- [파라미터명] IN VARCHAR2 (byte 크기는 지정하지 않음)
- [파라미터명] IN [테이블명].[컬럼명]%TYPE;
- [파라미터명] IN [테이블명].[컬럼명]%TYPE := 값;
- [파라미터명] IN [테이블명].[컬럼명]%TYPE DEFAULT 값;

CREATE OR REPLACE PROCEDURE [프로시저명] ([파라미터명] IN [파라미터타입])
IS
    [변수명] [변수타입];
BEGIN
    (생략)
END;
/

회원 정보 INSERT하는 프로시저 예시

CREATE OR REPLACE PROCEDURE PROC_INSERT_USER
    (p_user_nm IN VARCHAR2, p_user_birth IN VARCHAR2)
IS
    v_user_num NUMBER(10) := USER_NUM_SEQ.NEXTVAL;
BEGIN
    INSERT INTO USER_INFO values(v_user_num, p_user_nm, p_user_birth);
    COMMIT;
END;
/

4-2. 프로시저 실행

EXEC [프로시저명](); 을 입력하여 실행

EXEC PROC_INSERT_USER('YNJCH','20000101');

DECLARE 절에서 변수 선언 후 실행

DECLARE 
    param1 VARCHAR2(30) := 'TEST';
    param2 VARCHAR2(30) := '19991231';
BEGIN
    PROC_INSERT_USER(param1, param2);
END;
/

4-2-1. 프로시저 예외 처리

BEGIN~END 사이에 아래와 같이 예외 처리

(SELECT 문)

EXCEPTION 
    WHEN NO_DATA_FOUND THEN dbms_output.put_line('EXCEPTION 1'); RETURN;
    WHEN OTHERS THEN dbms_output.put_line('EXCEPTION 2'); RETURN;

4-3. 프로시저 조회

작성된 프로시저를 찾기 위해 데이터 사전 이용
데이터 사전은 대문자로 값을 저장하므로 유의

SELECT * FROM USER_OBJECTS WHERE OBJECT_TYPE = 'PROCEDURE';
SELECT * FROM USER_SOURCE WHERE TYPE = 'PROCEDURE' AND NAME = '[프로시저명]';

4-3-1. 프로시저 수정일 조회

SELECT	
    OBJECT_TYPE, OBJECT_NAME, STATUS
    , TO_CHAR(CREATED, 'YYYY.MM.DD HH24:MI:SS') AS CRT_DT
    , TO_CHAR(LAST_DDL_TIME, 'YYYY.MM.DD HH24:MI:SS') AS UPD_DT 
FROM USER_OBJECTS	
WHERE OBJECT_TYPE IN ('FUNCTION', 'PROCEDURE') -- 타입 지정 (함수, 프로시저 등)
    AND TO_CHAR(LAST_DDL_TIME, 'YYYYMMDD') > '20230101'
    AND OBJECT_NAME LIKE '%TB_PDBS_PD_BAS%'
ORDER BY 1, 2, 3, 4, 5;