231108 PL/SQL 독립 트랜잭션(Autonomous Transaction), 문맥전환(SQL엔진, PL/SQL 엔)

■ 독립 트랜잭션(Autonomous Transaction) (8i, 9iR2) 동시에 진행
- PRAGMA autonomous_transaction 지시어를 선언부분에 작성하면 독립트랜잭션이 수행된다.
- 독립트랜잭션은 시작 트랜잭션의 결과의 영향을 주지 않으면서 변경 사항에 대해서 COMMIT, ROLLBACK 할 수 있다.

 

CREATE TABLE test(name char(10));

CREATE OR REPLACE PROCEDURE p2
IS 
BEGIN 
    INSERT INTO test(name) VALUES('두번째'); -- 트랜잭션 진행중2
    COMMIT; -- 트랜잭션 진행완료. 
    -- 트랜잭션이 이루어지기땜에 독립적으로 사용해야 하면 주의해서 사용해야 한다
END p2;
/

CREATE OR REPLACE PROCEDURE p1
IS 
BEGIN 
    INSERT INTO test(name) VALUES('첫번째'); -- 시작 트랜잭션 진행중
    P2; -- 트랜잭션 진행중1
END p1;
/

execute p1;

SELECT * FROM test;
ROLLBACK;
SELECT * FROM test;

 

# 호출단위 프로그램 유용하다

TRUNCATE TABLE test;

 

TIP) TRUNCATE  VS DELECT 차이

DROP	TRUNCATE	DELETE
ROLLBACK 불가능	ROLLBACK 불가능	ROLLBACK 가능
AUTO COMMIT	AUTO COMMIT	USER COMMIT
테이블 정의 자체를 완전히 삭제	구조만 남기고 삭제	DATA 삭제

 

PRAGMA autonomous_transaction : ROLLBACK 기능 가능

CREATE OR REPLACE PROCEDURE p2
IS 
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN 
    INSERT INTO test(name) VALUES('두번째'); 
    COMMIT; 
END p2;
/

CREATE OR REPLACE PROCEDURE p1
IS 
BEGIN 
    INSERT INTO test(name) VALUES('첫번째');
    P2;
END p1;
/

execute p1;

SELECT * FROM test;
ROLLBACK;
SELECT * FROM test;

 

# test table 생성 

CREATE TABLE trigger_tab (
    id    NUMBER,
    name  VARCHAR2(30),
    day   TIMESTAMP DEFAULT systimestamp
);

CREATE TABLE trigger_log (
    id       NUMBER,
    name     VARCHAR2(30),
    log_day  TIMESTAMP DEFAULT systimestamp
);

CREATE OR REPLACE TRIGGER trig_log AFTER
    INSERT OR UPDATE OR DELETE ON trigger_tab
    FOR EACH ROW
DECLARE
    PRAGMA autonomous_transaction; -- 독립 트랜잭션, ROLLBACK, COMMIT 가능
BEGIN
    INSERT INTO trigger_log ( id, name, log_day )
    VALUES ( :new.id, :new.name, :new.day );
    COMMIT;
END trig_log;
/

 

# 생성확인

SELECT * FROM trigger_tab;
SELECT * FROM trigger_log;

 

# DATA 로드 

INSERT INTO trigger_tab(id, name, day) 
VALUES(1, user, DEFAULT);

 

# DATA 확인

SELECT * FROM trigger_tab;

 

■ 문맥전환 

1) SQL 엔진 : SQL문을 구문 분석하고 실행하는 엔진, 경우에 따라 PL/SQL 에 FETCH 시점에 데이터를 반환한다.

(명시적 커서를 사용할 때  결과집합을 변수에 로드하는 FETCH 시점)

2) PL/SQL 엔진 : 프로시점문을 실행하지만 SQL문은 SQL엔진에 전달해야 한다.

 

# SQL 엔진 -> PL/SQL 엔진 으로 성능저하

DECLARE 
    CURSOR emp_sur IS
        SELECT *
        FROM hr.employees
        WHERE department_id = 20;
    
    v_rec emp_cur%rowtype;
BEGIN
    OPEN emp_cur;
    FETCH emp_cur INTO v_rec; -- FETCH시 문맥전환(SQL엔진 -> PL/SQL엔진) -> 성능저하
        dbms_output.put_lien(v_rec.last_name);
    FETCH emp_cur INTO v_rec;
        dbms_output.put_lien(v_rec.last_name);
    CLOSE emp_cur;

END;
/

 

# SQL 메모리 생성만큼 PL/SQL 메모리도 동일하게 생성 = 배열변수 

DECLARE 
    CURSOR emp_sur IS
        SELECT *
        FROM hr.employees
        WHERE department_id = 20;
    
    v_rec emp_cur%rowtype;
BEGIN
    OPEN emp_cur; -- SQL 메모리 생성만큼 PL/SQL 메모리도 동일하게 생성 = 배열변수 
    LOOP    
        FETCH emp_cur INTO v_rec; --FETCH시 문맥전환(SQL엔진 -> PL/SQL엔진) -> 성능저하
        EXIT WHEN emp_cur%NOTFOUND;
            dbms_output.put_lien(v_rec.last_name);
    END LOOP;
    CLOSE emp_cur;
END;
/

 

# BULK COLLECT INTO 

- 결과집합을 PL/SQL 엔진에 전달 하기 전에 SQL 엔진이 이를 대량으로 바인드할 수 있도록 하는 절이다. 
- 명시적커서, 커서의 속성의 필요성이 크게 없다 하면 이용하면 된다.
- 성능이 좋아 BULK COLLECT INTO 지시어 사용을 권장

 

# 보편적인 쿼리문

DECLARE
    TYPE tab_type IS TABLE OF hr.employees%rowtype; --최대 2기가
    v_tab tab_type; -- 배열변수 선언, SQL엔진이 전달
BEGIN
    -- 명시적 커서
    SELECT *
    BULK COLLECT INTO v_tab --배열변수
    FROM hr.employees
    WHERE department_id = 20;
    
    FOR i IN v_tab.first .. v_tab.last LOOP
        dbms_output.put_line(v_tab(i).last_name);
    END LOOP;
END;
/

 

# DECLARE 절에 명시적 CURSOR 에 선언했을 경우

DECLARE 
    CURSOR emp_cur IS
        SELECT *
        FROM hr.employees
        WHERE department_id = 20;

    --2차원 배열변수 선언
    TYPE tab_type IS TABLE OF emp_cur%rowtype;
    v_tab tab_type; 
BEGIN
    OPEN emp_cur;
    FETCH emp_cur BULK COLLECT INTO v_tab; --2차원 배열변수
    CLOSE emp_cur;
    
    FOR i IN v_tab.first .. v_tab.last LOOP
        dbms_output.put_line(v_tab(i).last_name);
    END LOOP;
END;
/

 

# 문맥전환 : SQL엔진, PL/SQL 엔진 

CREATE TABLE hr.emp 
AS
SELECT *
FROM hr.employees;

 

# 값의 분포도에 따라 : 막대그래프, 히스토그램 -> 실행계획의 공유 유무 판단
문맥전환 의 문제

BEGIN 
    DELETE FROM hr.emp WHERE department_id = 10;
    DELETE FROM hr.emp WHERE department_id = 20;
    DELETE FROM hr.emp WHERE department_id = 30;
END;
/

 

# 실행계획 공유 가능

DECLARE
    v_id_10 NUMBER := 10;
    v_id_20 NUMBER := 20;
    v_id_30 NUMBER := 30;
BEGIN 
    DELETE FROM hr.emp WHERE department_id = v_id_10;
    DELETE FROM hr.emp WHERE department_id = v_id_20;
    DELETE FROM hr.emp WHERE department_id = v_id_30;
END;
/

 

# 배열변수 

DECLARE
    TYPE numlist IS TABLE OF NUMBER;
    v_num numlist := numlist(10, 20, 30);
BEGIN 
    DELETE FROM hr.emp WHERE department_id = v_num(1);
        dbms_output.put_line(sql%rowcount||' row deleted.');
    DELETE FROM hr.emp WHERE department_id = v_num(2);
        dbms_output.put_line(sql%rowcount||' row deleted.');
    DELETE FROM hr.emp WHERE department_id = v_num(3);
        dbms_output.put_line(sql%rowcount||' row deleted.');
END;
/

 

# 반복문(FOR) 활용 -> 문제점) 문맥전환 : 배열변수의 수만큼 

DECLARE
    TYPE numlist IS TABLE OF NUMBER;
    v_num numlist := numlist(10, 20, 30);
BEGIN 
    FOR i IN v_num.first .. v_num.last LOOP
        DELETE FROM hr.emp WHERE department_id = v_num(i);
            dbms_output.put_line(sql%rowcount||' row deleted.');
    END LOOP;
END;
/

 

# FORALL문 

- PL/SQL 엔진이 대량으로 내부적으로 모아서 SQL 엔진으로 전달하는 문

- 문맥전환을 줄이는 방법
- DML문에서만 적용

DECLARE
    TYPE numlist IS TABLE OF NUMBER;
    v_num numlist := numlist(10, 20, 30);
BEGIN 
    FORALL i IN v_num.first .. v_num.last
        DELETE FROM hr.emp --DML 문
        WHERE department_id = v_num(i); -- i 배열변수
        
    dbms_output.put_line(sql%ROWCOUNT||' row deleted.'); -- 1건인 것처럼 처리.
    
    dbms_output.put_line(sql%BULK_ROWCOUNT(1)||' row deleted.'); 
    dbms_output.put_line(sql%BULK_ROWCOUNT(2)||' row deleted.');
    dbms_output.put_line(sql%BULK_ROWCOUNT(3)||' row deleted.');
END;
/

 

# FOR문 으로 변경

DECLARE
    TYPE numlist IS TABLE OF NUMBER;
    v_num numlist := numlist(10, 20, 30);
BEGIN 
    FORALL i IN v_num.first .. v_num.last
        DELETE FROM hr.emp --DML 문
        WHERE department_id = v_num(i); -- i 배열변수
        
    FOR i IN v_num.first .. v_num.last LOOP
        dbms_output.put_line(sql%BULK_ROWCOUNT(i)||' row deleted.'); 
    END LOOP;
END;
/

 

# test table 생성

CREATE TABLE t (
    id int, 
    name varchar2(128)
);

desc t

DECLARE
    TYPE t_type IS TABLE OF t%rowtype INDEX BY PLS_INTEGER;
    v_tab t_type;
BEGIN
    SELECT employee_id, last_name
    BULK COLLECT INTO v_tab -- 타입이 동일한 2차원배열
    FROM hr.employees
    WHERE rownum <= 10;
    
    FOR i IN v_tab.first .. v_tab.last LOOP
        --DBMS_OUTPUT.PUT_LINE(v_tab(i).id||' '||v_tab(i).name);
        INSERT INTO t(id, name)
        VALUES (v_tab(i).id, v_tab(i).name);
    END LOOP;
END;
/

SELECT * FROM t;

 

# FORALL 활용(문맥전환) -> 대량 DATA 를 확인할 때

DECLARE
    TYPE t_type IS TABLE OF t%rowtype INDEX BY PLS_INTEGER;
    v_tab t_type;
BEGIN
    SELECT employee_id, last_name
    BULK COLLECT INTO v_tab -- 타입이 동일한 2차원배열
    FROM hr.employees
    WHERE rownum <= 10;
    
    FORALL i IN v_tab.first .. v_tab.last
        INSERT INTO t 
        VALUES v_tab(i);
END;
/

 

# 오류발생 

-- ORA-22160: element at index [2] does not exist

DECLARE
    TYPE t_type IS TABLE OF t%rowtype INDEX BY PLS_INTEGER;
    v_tab t_type;
BEGIN
    SELECT employee_id, last_name
    BULK COLLECT INTO v_tab -- 타입이 동일한 2차원배열
    FROM hr.employees
    WHERE rownum <= 10;
    
    v_tab.DELETE(2);
    v_tab.DELETE(4);
    v_tab.DELETE(6);
    
    FORALL i IN v_tab.first .. v_tab.last
        INSERT INTO t 
        VALUES v_tab(i);
END;
/

SELECT * FROM t;

 

 

# SAVE EXCEPTIONS : FORALL 실행중에 문제가 생기면 SAVE 한 후, 다음 데이터 실행하라.

EXECUTE DBMS_OUTPUT.PUT_LINE(SQLERRM(-01400));
-- NOT NULL 조건에 대한 오류번호

 

DECLARE
    TYPE t_type IS TABLE OF t%rowtype INDEX BY PLS_INTEGER;
    v_tab t_type;
    
    dml_errors EXCEPTION; --에러변수
    PRAGMA EXCEPTION_INIT(dml_errors, -24381);
    errors NUMBER; -- 에러건수 변수
BEGIN
    SELECT employee_id, last_name
    BULK COLLECT INTO v_tab -- 타입이 동일한 2차원배열
    FROM hr.employees
    WHERE rownum <= 10;
    
    v_tab.DELETE(2);
    v_tab.DELETE(4);
    v_tab.DELETE(6);
    
    FORALL i IN v_tab.first .. v_tab.last SAVE EXCEPTIONS
        INSERT INTO t 
        VALUES v_tab(i);
EXCEPTION
    WHEN dml_errors THEN
        FOR i IN v_tab.first .. v_tab.last LOOP
            dbms_output.put_line('INSERT '||SQL%BULK_ROWCOUNT(i)||' on iteration'||i);
        END LOOP;
        errors := SQL%BULK_EXCEPTIONS.COUNT;
        dbms_output.put_line('----------------- ERROR --------------------');
        dbms_output.put_line('Number of error is '||errors); --에러건수
      
        -- 에러난 곳
        FOR i IN 1 .. errors LOOP
            dbms_output.put_line('Error index : '||SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
            dbms_output.put_line('실제 오류 정보 : '||SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
        END LOOP;
END;
/

SELECT * FROM t;

 

# 특정한 값 출력

DECLARE
    TYPE t_type IS TABLE OF t%rowtype INDEX BY pls_integer;
    v_tab t_type;
    
    TYPE t1_type IS TABLE OF number INDEX BY pls_integer;
    v1_tab t1_type;
BEGIN
    SELECT employee_id, last_name
    BULK COLLECT INTO v_tab 
    FROM hr.employees
    WHERE rownum <= 10;
    
    v1_tab(1) := 3;
    v1_tab(2) := 8;
    v1_tab(3) := 9;
    
    FOR i IN v1_tab.first .. v1_tab.last LOOP
        dbms_output.put_line(v_tab(v1_tab(i)).id||' '||v_tab(v1_tab(i)).name);
    END LOOP;
END;
/

 

# 특정한 값을 다른 테이블에 로드
- type 설정 주의할 것

DECLARE
    TYPE t_type IS TABLE OF t%rowtype INDEX BY PLS_INTEGER;
    v_tab t_type;
    
    TYPE t1_type IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER;
    v1_tab t1_type; 
BEGIN
    SELECT employee_id, last_name
    BULK COLLECT INTO v_tab 
    FROM hr.employees
    WHERE rownum <= 10;
    
    -- 많은 값들 중에서 특정한 값 -> 배열
    v1_tab(1) := 3; --index num
    v1_tab(2) := 8;
    v1_tab(3) := 9;
    
    FORALL i IN VALUES OF v1_tab
        INSERT INTO t
        VALUES v_tab(i);
END;
/

SELECT * FROM t;

 

# 문제점 ) update 한 결과 -> select문 으로 확인

DECLARE
    v_id  number := 100;
    v_sal_after number;
BEGIN 
    UPDATE hr.employees
    SET salary = salary*1.1
    WHERE employee_id = v_id;
    
    -- 문제) update 한 결과 -> select문 으로 확인
    SELECT salary
    INTO v_sal_after
    FROM hr.employees
    WHERE employee_id = v_id;
    
    dbms_output.put_line(v_id||' '||v_sal_after);
    ROLLBACK; 
END;
/

 

# update한 결과를 select문 없이 바로 확인. 

DECLARE
    v_id  number := 100;
    v_sal_after number;
    v_name varchar2(30);
    
BEGIN 
    UPDATE hr.employees
    SET salary = salary*1.1
    WHERE employee_id = v_id
    RETURNING last_name, salary INTO v_name, v_sal_after; --update 한 결과물 
    
    dbms_output.put_line(v_id||' '||v_name||' '||v_sal_after);
    ROLLBACK; 
END;
/

SELECT employee_id, last_name, salary FROM hr.employees WHERE employee_id =100;

 

 

# 레코드 변수 선언

DECLARE
    v_id  number := 100;
    TYPE recode_type IS RECORD (name varchar2(30), sal number);
    v_rec recode_type;
BEGIN 
    UPDATE hr.employees
    SET salary = salary*1.1
    WHERE employee_id = v_id
    RETURNING last_name, salary INTO v_rec;
    
    dbms_output.put_line(v_id||' '||v_rec.name||' '||v_rec.sal);
    ROLLBACK; 
END;
/

SELECT employee_id, last_name, salary FROM hr.employees WHERE employee_id =100;

 

# FOR문

DECLARE
    v_id  number := 20;
    TYPE record_type IS RECORD (name varchar2(30), sal number);
    TYPE tab_type IS TABLE OF record_type;
    v_tab tab_type;
BEGIN 
    UPDATE hr.employees
    SET salary = salary*1.1
    WHERE department_id = v_id
    RETURNING last_name, salary BULK COLLECT INTO v_tab;
    
    FOR i IN v_tab.first..v_tab.last LOOP
        dbms_output.put_line(v_tab(i).name||' '||v_tab(i).sal);
    END LOOP;
    
    ROLLBACK; 
END;
/

SELECT employee_id, last_name, salary FROM hr.employees WHERE employee_id =100;