CREATE OR REPLACE PROCEDURE check_salary
(p_job IN varchar2, p_sal IN number)
IS
    v_min_sal number;
    v_max_sal number;
BEGIN
    SELECT min_salary, max_salary
    INTO v_min_sal, v_max_sal
    FROM hr.jobs
    WHERE job_id = upper(p_job);
    
    IF p_sal NOT BETWEEN v_min_sal AND v_max_sal THEN
        RAISE_APPLICATION_ERROR(-20000,'Invalid salary $'||p_sal||'. Salaries for job '||p_job||
                                ' must be between $'||v_min_sal||' and $'||v_max_sal);
    END IF;
    
END check_salary;
/
/* 호출 */
execute check_salary('IT_PROG', 20000)
-- ORA-20000: Invalid salary $20000. Salaries for job IT_PROG must be between $4000 and $10000

 

 

/* 테이블 내 트리거 체크 */
SELECT * FROM user_triggers WHERE table_name = 'EMPLOYEES';
/* 트리거에서 프로시저 호출 */
CREATE OR REPLACE TRIGGER check_salary_trg
AFTER
INSERT OR UPDATE OF salary ON hr.employees
FOR EACH ROW
BEGIN
    check_salary(:new.job_id, :new.salary);
END;
/

||

/* BEGIN~END -> CALL 문 */
CREATE OR REPLACE TRIGGER check_salary_trg
AFTER
INSERT OR UPDATE OF salary ON hr.employees
FOR EACH ROW
CALL check_salary(:new.job_id, :new.salary) -- 세미콜론(;) 제외
/
/* 호출 */
UPDATE hr.employees
SET salary = 6000
WHERE employee_id = 115;
-- ORA-20000: Invalid salary $6000. Salaries for job PU_CLERK must be between $2500 and $5500

 

 

 

 

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

DROP TABLE test PURGE;
CREATE TABLE test (name char(10));

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

CREATE OR REPLACE PROCEDURE p1
IS
BEGIN
    INSERT INTO test(name) VALUES ('첫번째');  -- 2
    p2;     -- 3
END;
/
/* 호출 */
EXECUTE p1  -- 1
SELECT * FROM hr.test;

>> P1 호출 > P1_INSERT 실행 > P1_P2 호출 > P2_INSERT 실행 > P2_COMMIT > 1,2 둘다 영구저장

 

TRUNCATE TABLE test;
- DELETE   : ROLLBACK 사용 가능. UNDO 공간 필요(휴지통)
- TRUNCATE : ROLLBACK 불가. 



CREATE OR REPLACE PROCEDURE p2
IS
    PRAGMA AUTONOMOUS_TRANSACTION;  -- 4 : 앞에꺼는 대기, 트랜잭션 다시 시작
BEGIN
    INSERT INTO test(name) VALUES ('두번째');  -- 5
    COMMIT;     -- 6 트랜잭션 종료
END;
/

CREATE OR REPLACE PROCEDURE p1
IS
BEGIN
    INSERT INTO test(name) VALUES ('첫번째');  -- 2 트랜잭션 시작
    p2;     -- 3
END;
/
/* 호출 */
EXECUTE p1  -- 1
SELECT * FROM hr.test;
>> P1 호출 > P1_INSERT 실행 > P1_P2 호출 > P2_PRAGMA AUTONOMOUS_TRANSACTION 적용
> P1_INSERT 대기 + P2_INSERT 실행 > P2_COMMIT

ROLLBACK;
SELECT * FROM hr.test;
> 1은 대기상태에서 종료되어 2번만 영구저장

 



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;
BEGIN
    INSERT INTO trigger_log (id,name,log_day) VALUES (:new.id,:new.name,:new.day);
    COMMIT;
END;
/

INSERT INTO trigger_tab(id,name,day) VALUES (1,user,default);
SELECT * FROM hr.trigger_tab;
SELECT * FROM hr.trigger_log;

ROLLBACK;
SELECT * FROM hr.trigger_tab;
SELECT * FROM hr.trigger_log;
>> trigger_tab 테이블에 INSERT 작업 후 트리거 작동
>> PRAGMA AUTONOMOUS_TRANSACTION 적용 - trigger_log 테이블에 INSERT + COMMIT
>> ROLLBACK하면 trigger_tab은 미저장.
>> 영구저장을 하던말던 로그남기기

 

 

 

- SQL 엔진 : SQL문을 구문 분석하고실행하는 엔진. 경우에 따라 PL/SQL엔진에 데이터를 반환한다.
            (명시적 커서를 사용할 때 결과집합을 변수에 로드하는 FETCH 시점)
- PL/SQL 엔진 : 프로시저문을 실행하지만 SQL문은 SQL엔진에 전달한다. -> 문맥전환

DECLARE
    CURSOR emp_cur IS
        SELECT * 
        FROM hr.employees 
        WHERE department_id = 20;   -- 2명
    
    v_rec emp_cur%rowtype;

BEGIN
    OPEN emp_cur;   -- SQL엔진
    FETCH emp_cur INTO v_rec;   -- PL/SQL엔진
        DBMS_OUTPUT.PUT_LINE(v_rec.last_name);
    FETCH emp_cur INTO v_rec;
        DBMS_OUTPUT.PUT_LINE(v_rec.last_name);
    CLOSE emp_cur;
END;
/
DECLARE
    CURSOR emp_cur IS
        SELECT * 
        FROM hr.employees 
        WHERE department_id = 20;   -- 2명
    
    v_rec emp_cur%rowtype;
BEGIN
    OPEN emp_cur;
    LOOP
        FETCH emp_cur INTO v_rec;
            EXIT WHEN emp_cur%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_rec.last_name);
    END LOOP;
    CLOSE emp_cur;
END;
/

 

↓
# BULK COLLECT INTO : 결과 집합을 PL/SQL엔진에 전달하기 전에 SQL엔진이 이를 대량으로 바인드할 수 있도록 하는 절이다.

DECLARE
    TYPE tab_type IS TABLE OF hr.employees%rowtype;
    v_tab tab_type;
BEGIN
    SELECT *
    BULK COLLECT INTO v_tab     -- 배열변수 사용, 문맥전환 1번에 끝
    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 emp_cur IS
        SELECT * 
        FROM hr.employees 
        WHERE department_id = 20;
    
    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;
    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;
/

 

 

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

BEGIN   --PL/SQL엔진
    DELETE FROM hr.emp WHERE department_id = 10;    -- SQL엔진
    DELETE FROM hr.emp WHERE department_id = 20;    -- SQL엔진
    DELETE FROM hr.emp WHERE department_id = 30;    -- SQL엔진
END;
/
실행계획 상수값고정 -> 쉐어링 불가
ROLLBACK;
↓
DECLARE
    TYPE nullist IS TABLE OF number;
    v_num nullist := nullist(10,20,30);
BEGIN
    DELETE FROM hr.emp WHERE department_id = v_num(1);
    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT||' rows deleted.');
    
    DELETE FROM hr.emp WHERE department_id = v_num(2);
    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT||' rows deleted.');
    
    DELETE FROM hr.emp WHERE department_id = v_num(3);
    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT||' rows deleted.');
END;
/
> 변수처리 -> 실행계획 쉐어링 가능. 단, 값의 분포도에 따라 쉐어링 여부 상이함.

ROLLBACK;
↓
DECLARE
    TYPE nullist IS TABLE OF number;
    v_num nullist := nullist(10,20,30);
BEGIN   --PL/SQL엔진
    FOR i IN v_num.FIRST..v_num.LAST LOOP   --PL/SQL엔진
        DELETE FROM hr.emp WHERE department_id = v_num(i);    -- SQL엔진
        DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT||' rows deleted.');
    END LOOP;
END;
/
> 문맥전환 3번 발생 -> 반복문 내 SQL문이 잦을수록 성능 저하

ROLLBACK;
↓
# FORALL 문 : PL/SQL 엔진이 대량으로 바인드해서 SQL 엔진에 전달하는 문.

DECLARE
    TYPE nullist IS TABLE OF number;
    v_num nullist := nullist(10,20,30);
BEGIN   --PL/SQL엔진
    FORALL i IN v_num.FIRST..v_num.LAST
        DELETE FROM hr.emp WHERE department_id = v_num(i);  -- DML문만 가능
    
    FOR i IN v_num.FIRST..v_num.LAST LOOP
        DBMS_OUTPUT.PUT_LINE(SQL%BULK_ROWCOUNT(i)||' rows deleted.');
    END LOOP;
END;
/
> 문맥전환 1번

ROLLBACK;
DECLARE
    TYPE nullist IS TABLE OF number;
    v_num nullist := nullist(10,20,30,40,50);

BEGIN
    FORALL i IN v_num.FIRST..v_num.LAST
        UPDATE hr.emp
        SET salary = salary * 1.1
        WHERE department_id = v_num(i);
    
    FOR i IN v_num.FIRST..v_num.LAST LOOP
        DBMS_OUTPUT.PUT_LINE(SQL%BULK_ROWCOUNT(i)||' rows updated.');
    END LOOP;
END;
/

ROLLBACK;


SQL%ROWCOUNT : 작업행 건수
SQL%BULK_ROWCOUNT(i) : FORALL문을 통해 영향을 받은 행의 건수

 

 

/* 테이블 생성*/
CREATE TABLE t(id int, name varchar2(128));

desc t
이름   널? 유형            
---- -- ------------- 
ID      NUMBER(38)    
NAME    VARCHAR2(128)

 

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
    FROM hr.employees
    WHERE rownum <= 10; -- 10건
    
    FOR i IN v_tab.FIRST..v_tab.LAST LOOP
        INSERT INTO t(id,name) VALUES (v_tab(i).id,v_tab(i).name);
    END LOOP;
END;
/
>> 문맥전환 10건 발생
SELECT * FROM hr.t;

rollback;
↓
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
    FROM hr.employees
    WHERE rownum <= 10;
    
    FORALL i IN v_tab.FIRST..v_tab.LAST
        INSERT INTO t VALUES v_tab(i);  -- t 테이블 AND v_tab 배열 = 구조 동일
END;
/
>> 문맥전환 1건 발생
SELECT * FROM hr.t;

rollback;

 

INSERT INTO 테이블 VALUES 배열(i) : 테이블과 배열의 구조가 동일할 경우 사용가능.

 

 

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
    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;
/
-- ORA-22160: element at index [2] does not exist
>> FORALL 사용하여 작업하는데 중간에 갭 발생으로 인한 오류
↓
DECLARE
    /* 2차원 배열 선언 */
    TYPE t_type IS TABLE OF t%rowtype INDEX BY pls_integer;
    v_tab t_type;
    
    dml_errors EXCEPTION;
    /* FORALL문 사용시 무조건 에러번호 -24381 만 사용 */
    PRAGMA EXCEPTION_INIT(dml_errors,-24381);
    errors number;

BEGIN
    SELECT employee_id, last_name
    BULK COLLECT INTO v_tab
    FROM hr.employees
    WHERE rownum <= 10;
    
    v_tab.DELETE(2);
    v_tab.DELETE(4);
    v_tab.DELETE(6);    --> 인덱스 번호에 갭 발생
    
    /* SAVE EXCEPTIONS : 오류는 세이브하고 다음 진행. FORALL문이 끝나면 EXCEPTION 실행 */
    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
            /* SQL%BULK_ROWCOUNT(i) : i번째 항목 실행 시 영향을 받은 행의 건수 */
            DBMS_OUTPUT.PUT_LINE('INSERT '||SQL%BULK_ROWCOUNT(i)||' row(s)'||' on iteration '||i);
        END LOOP;

    /* SQL%BULK_EXCEPTIONS.COUNT : 현재 발생한 오류 건수 */
    errors := SQL%BULK_EXCEPTIONS.COUNT;
    DBMS_OUTPUT.PUT_LINE('Number of errors is '||errors);
    
    FOR i IN 1..errors LOOP
        /* SQL%BULK_EXCEPTIONS(i).ERROR_INDEX : 현재 발생한 오류의 인덱스 번호 */
        DBMS_OUTPUT.PUT_LINE('Error index : '||SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
        /* SQL%BULK_EXCEPTIONS(i).ERROR_CODE : 현재 발생한 오류의 코드번호 */
        DBMS_OUTPUT.PUT_LINE('실제 오류 정보 : '||SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
    END LOOP;
END;
/

ROLLBACK;

 

- SAVE EXCEPTIONS : 오류는 세이브하고 다음 진행. FORALL문이 끝나면 EXCEPTION 실행
- SQL%BULK_EXCEPTIONS.COUNT : 현재 발생한 오류의 총 건수
- SQL%BULK_EXCEPTIONS(i).ERROR_INDEX : 현재 발생한 오류의 인덱스 번호
- SQL%BULK_EXCEPTIONS(i).ERROR_CODE : 현재 발생한 오류의 코드번호

 

 

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;
/
↓
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;
    v1_tab(2) := 8;
    v1_tab(3) := 9; --> 값을 인덱스 번호로 사용할꺼라 pls_integer 타입이여야 한다.
    
    FORALL i IN VALUES OF v1_tab    -- 배열 안의 값 = i IN (3,8,9) = 방번호로 사용
        INSERT INTO t VALUES v_tab(i);
END;
/

SELECT * FROM hr.t;

ROLLBACK;


- FORALL i IN VALUES OF 배열 : 배열 안의 값을 i로 사용한다.

 

 

DECLARE
    v_id number := 100;
    v_sal_after number;

BEGIN
    UPDATE hr.employees
    SET salary = salary * 1.1
    WHERE employee_id = v_id;
    
    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;
/
>> UPDAT 작업 + 수정 후 확인을 위한 SELECT 작업
↓
/* 기존값 체크 */
select salary from hr.employees where employee_id = 100;

DECLARE
    v_id number := 100;
    v_sal_after number;

BEGIN
    UPDATE hr.employees
    SET salary = salary * 1.1
    WHERE employee_id = v_id
    RETURNING salary INTO v_sal_after;      -- 칼럼명 작성, 전체조회(*)는 불가
    
    DBMS_OUTPUT.PUT_LINE(v_id||' '||v_sal_after);
    rollback;
END;
/
>> UPDATE와 수정 후 조회를 한번에 해결
DECLARE
    v_id number := 100;
    
    TYPE record_type IS RECORD (name varchar2(30), sal number);
    v_rec record_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;
/
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  -- 여러 작업행 발생 -> INTO 사용 불가
    RETURNING last_name, salary BULK COLLECT INTO v_tab;
    
    FOR i IN v_tab.first..v_tab.last LOOP
        DBMS_OUTPUT.PUT_LINE(v_id||' '||v_tab(i).name||' '||v_tab(i).sal);
    END LOOP;
    rollback;
END;
/

 

 

 

BEGIN
    check_salary(:new.job_id, :new.salary);
END;

↓

CALL check_salary(:new.job_id, :new.salary) -- 세미콜론(;) 제외

 

 

PRAGMA AUTONOMOUS_TRANSACTION : 선언 시 독립 트랜잭션(Autonomous Transaction)이 수행된다.

 

 

DELETE   : ROLLBACK 사용 가능. UNDO 공간 필요(휴지통)
TRUNCATE : ROLLBACK 불가.
DROP : Object 삭제

 

 

FORALL i IN FIRST..LAST
    DML 문만 가능 ;
 : PL/SQL 엔진이 대량으로 바인드해서 SQL 엔진에 전달하는 문.

 

FORALL i IN FIRST..LAST SAVE EXCEPTIONS
: 오류는 세이브하고 다음 진행. FORALL문이 끝나면 EXCEPTION 실행

 

 

PRAGMA EXCEPTION_INIT(에러명,-24381) : FORALL문 사용시 무조건 에러번호 -24381 만 사용

 

 

FORALL i IN VALUES OF 배열 : 배열 안의 값을 i로 사용

 

 

INSERT INTO 테이블 VALUES 배열(i) : 테이블과 배열의 구조가 동일할 경우 사용가능.

 

 

SELECT ~
INTO : 1개 행만 가능

BULK COLLECT INTO 배열변수
: 결과 집합을 PL/SQL엔진에 전달하기 전에 SQL엔진이 이를 대량으로 바인드할 수 있도록 하는 절이다.

 

 

UPDATE + SELECT, DELETE+SELECT, INSERT+SELECT
↓

DML작업 - 결과 1행

RETURNING 칼럼 INTO 변수;

: DML 작업 후 조회를 한번에 해결. 전체조회(*)는 불가

DML작업 - 결과 여러행
RETURNING 칼럼 BULK COLLECT INTO 배열;

 

 

값을 인덱스 번호로 사용 시 pls_integer 타입이여야 한다.
TYPE t1_type IS TABLE OF pls_integer INDEX BY pls_integer

 

 

SQL%ROWCOUNT : 작업행 건수
SQL%BULK_ROWCOUNT(i) :FORALL문을 통해 영향을 받은 행의 건수
SQL%BULK_EXCEPTIONS.COUNT : 현재 발생한 오류의 총 건수
SQL%BULK_EXCEPTIONS(i).ERROR_INDEX : 현재 발생한 오류의 인덱스 번호
SQL%BULK_EXCEPTIONS(i).ERROR_CODE : 현재 발생한 오류의 코드번호

 

 

저작자표시

'Oracle SQL' 카테고리의 다른 글

23 PL/SQL TRIGGER  (0) 2023.11.07
22 PL/SQL PACKAGE, TRIGGER  (1) 2023.11.06
21 PL/SQL PACKAGE  (1) 2023.11.03
20 PL/SQL PROCEDURE, FUNCTION  (0) 2023.11.02
19 PL/SQL PROCEDURE  (0) 2023.11.01
'Oracle SQL'의 다른글
  • 현재글24 PL/SQL Autonomous Transaction

티스토리툴바