PL/SQL - DAY 8

BUMSOO·2024년 7월 11일
PL/SQL오라클 DBA

BULK COLLECT INTO

  • 명시적 커서의 fetch절, RETURNING 에도 사용 가능하다.

<FETCH 절>

DECLARE
    CURSOR emp_cur(p_id number) IS
        SELECT *
        FROM hr.employees
        WHERE department_id = p_id;
    
    TYPE tab_type IS TABLE OF emp_cur%rowtype;
    v_rec tab_type;
    
    TYPE v_array_type IS TABLE OF number;
    v_array v_array_type := v_array_type(30,40);

BEGIN
    FOR i IN v_array.first..v_array.last LOOP
        dbms_output.put_line('----------------');
        OPEN emp_cur(v_array(i));
        FETCH emp_cur BULK COLLECT INTO v_rec;
        CLOSE emp_cur;
        FOR i in v_rec.first..v_rec.last LOOP
            dbms_output.put_line(v_rec(i).last_name);
        END LOOP;
    END LOOP;
    
END;
/

<RETURNING 절>

BEGIN
    UPDATE hr.employees
    SET salary = salary*1.1
    WHERE department_id = 30
    RETURNING last_name,salary BULK COLLECT INTO v_array;  
    
    FOR i IN v_array.first..v_array.last LOOP
        dbms_output.put_line(v_array(i).a ||' ' || v_array(i).b);
    END LOOP;
    ROLLBACK;
END;
/

FORALL

  • PL/SQL엔진에서 SQL 엔진사이의 문맥전환을 줄이는 문이다.
  • 배열변수에 있는 값을 이용해서 DML문장을 반복해서 수행할 경우 문맥전환이 발생할때 성능상 문제가 발생해서 이를 해결하고자 FORALL문을 이용하면 PLSQL엔진이 DML문을 바인드 하여 SQL엔진한테 보내면 문맥전환이 한번으로 끝난다.
  • FORALL 문 안에는 DML 문만 가능하다.

sql%bulk_rowcount

개별로 DML문에 영향을 받은 행 수 반환

DECLARE
    TYPE id_type IS TABLE OF number;
    v_num id_type := id_type(10,20,30);

BEGIN
    FORALL i IN v_num.first..v_num.last  -- 배열변수에 있는 값을 한번에 전달
    -- 암시적 커서가 돌아가고 있다.
        DELETE FROM hr.emp WHERE department_id = v_num(i);
        
    
    dbms_output.put_line(sql%rowcount); -- DML문으로 영향을 받은 전체 건수가 나옴
    
    FOR j IN v_num.first..v_num.last LOOP
         dbms_output.put_line(sql%bulk_rowcount(j) || 'row count'); --bulk_rowcout 개별 처리된 row건수 확인
    END LOOP;
    ROLLBACK;
END;
/

Exception Handling(예외처리)

  • 실행중에 발생한 PL/SQL 오류이다.
  • 오라클에 의해 암시적으로 발생한다.
  • 프로그램에 의해 명시적으로 발생할 수 있다.

예외 처리에 지침

  • 예외 처리 부분은 EXCEPTOIN 절로 시작한다.
  • 여러 예외 처리기를 사용할 수 있다.
  • 실행중에 발생한 오류에 따른 하나의 처리기만 수행하고 끝난다.
  • WHEN others THEN 절은 마지막 절이다.

미리정의된 오라클 서버 오류

  • 오류이름으로만 예외처리 가능
DECLARE
    v_id number :=300 ;
    v_rec hr.employees%rowtype;

BEGIN
    SELECT *
    INTO v_rec
    FROM hr.employees
    WHERE employee_id = v_id;
    
    dbms_output.put_line(v_rec.employee_id || ' ' ||v_rec.last_name);
    
EXCEPTION 
    WHEN no_data_found THEN -- 오류번호로는 예외처리를 할 수 없고 오류 이름으로만 처리할 수 있다.
        dbms_output.put_line(v_id || '사원은 존재하지 않습니다.');

END;
/

DECLARE
    v_id number :=20 ;
    v_rec hr.employees%rowtype;

BEGIN
    SELECT *
    INTO v_rec
    FROM hr.employees
    WHERE department_id = v_id;
    
    dbms_output.put_line(v_rec.employee_id || ' ' ||v_rec.last_name);
    
EXCEPTION 
    WHEN no_data_found THEN -- 오류번호로는 예외처리를 할 수 없고 오류 이름으로만 처리할 수 있다.
        dbms_output.put_line(v_id || '사원은 존재하지 않습니다.');
        
    WHEN others THEN
        dbms_output.put_line(sqlcode);
        dbms_output.put_line(sqlerrm); 
END;
/

sqlcode

  • 오류 코드에 대한 숫자값을 반환한다.
  • 0 : 예외가 발생하지 않음
  • 1 : 유저가 정의한 예외사항 발생
  • 100 : NO_DATA_FOUND 예외사항
  • 음수 : 오라클 서버 오류 번호

sqlerrm

  • 오류번호와 연관된 메시지를 반환

미리 정의 되지 않은 오라클 서버 오류

  • 고유한 예외이름을 선언
  • 선언된 예외이름이랑 오류번호랑 붙이는 작업 후 사용
DECLARE
    pk_error EXCEPTION; -- EXCEPTION 이름 선언
    PRAGMA EXCEPTION_INIT(pk_error,-2292); -- 오라클의 오류 번호와 내가 선언한 exception 이름을 붙이는 작업
BEGIN

    DELETE FROM hr.departments WHERE department_id = 10;

EXCEPTION
    WHEN pk_error THEN
        dbms_output.put_line('참조무결성 제약조건을 위반하여 삭제할 수 없습니다.');
END;
/

오류에 따른 transaction

자동 transaction rollback

  • 오류로 인한 프로그램의 비정상 종료가 발생되면 프로그램 안에서 수행하고 있던 transaction은 자동 rollback된다.
BEGIN
    UPDATE hr.employees
    SET salary = salary*1.1
    WHERE employee_id = 100;
   
    
    DELETE FROM hr.departments WHERE department_id = 10; -- 오류발생
    
END;
/

예외처리 후 미종료된 transanction

  • 오류에 따른 예외사항 처리를 수행 한 경우 오류 앞단에 수행하고 있던 transaction은 진행중인 상태로 되어 있기 때문에 꼭 예외처리 구문에서 commit or rollback 처리를 수행해야 한다.
DECLARE
    pk_error EXCEPTION;
    PRAGMA EXCEPTION_INIT(pk_error,-2292); 
BEGIN
    UPDATE hr.employees
    SET salary = salary*1.1
    WHERE employee_id = 100;
    
    DELETE FROM hr.departments WHERE department_id = 10;
    
EXCEPTION
    WHEN pk_error THEN
        dbms_output.put_line('참조무결성 제약조건을 위반하여 삭제할 수 없습니다.');
    --commit 또는 rollback;
END;    
/

오류발생 구문 뒤에 구문을 반드시 수행해야 할 경우

  • 중첩 BLOCK을 사용하여 예외사항 처리 후에도 뒤에 쿼리를 수행하도록 처리
  • 서브 BLOCK에 오류가 발생하였는데 서브 BLOCK안에 EXCEPTION절이 없다면 메인BLOCK의 EXCEPTION절로 빠져나가 예외사항 처리 후 프로그램 종료
DECLARE
    pk_error EXCEPTION;
    PRAGMA EXCEPTION_INIT(pk_error,-2292); 
BEGIN
    UPDATE -- transaction 시작
    
    --중첩 BLOCK 수행
    	BEGIN  
        	DELETE --오류 발생 ORA-2292
    
    	EXCEPTION
        	WHEN pk_error THEN
        	dbms_output.put_line('참조무결성 제약조건을 위반하여 삭제할 수 없습니다.');
    	END;
    
     SELECT -- 수행 가능
     DML -- 수행 가능
EXCEPTION
    WHEN otehrs THEN
        ...
    --commit 또는 rollback;
END;    
/

유저 정의 예외사항

  • RAISE 예외사항이름 으로 강제 예외처리
DECLARE
    e_invalid EXCEPTION; -- 사용자가 정의한 예외변수
BEGIN
    UPDATE hr.employees
    SET salary = salary*1.1 
    WHERE employee_id = 300;
    
    IF sql%NOTFOUND THEN
        RAISE e_invalid; -- 사용자가 발생시킨 예외사항
    END IF;
EXCEPTION
    WHEN e_invalid THEN
        dbms_output.put_line('사원번호를 체크해주세요.');
END;
/

RAISE_APPLICATION_ERROR

  • 유저 정의 오류 메시지를 실행하는 프로시저 프로그램
  • 강제 프로그램 비정상적인 종료
  • 인수값으로 (오류번호,오류메시지, false(true))
    • 오류번호(필수) : -20000~-20999 범위
    • 오류메시지(필수) : 최대 2048byte 문자열
    • false : 기본값, 오라클의 오류가 내가 만든 오류로 출력된다.
    • true : 오라클의 오류와 내가 만든 오류가 같이 출력된다.
  BEGIN
    UPDATE hr.employees
    SET salary = salary*1.1 
    WHERE employee_id = 300;
    
    IF sql%NOTFOUND THEN
        RAISE_APPLICATION_ERROR(-20000,'수정된 데이터가 없습니다.');
    END IF;
END;
/

<true 옵션>

DECLARE
    v_id number :=300 ;
    v_rec hr.employees%rowtype;
BEGIN
    SELECT *
    INTO v_rec
    FROM hr.employees
    WHERE employee_id = v_id;
    
    dbms_output.put_line(v_rec.employee_id || ' ' ||v_rec.last_name);
    
EXCEPTION 
    WHEN no_data_found THEN -- 오류번호로는 예외처리를 할 수 없고 오류 이름으로만 처리할 수 있다.
        RAISE_APPLICATION_ERROR(-20000,'조회 데이터가 없습니다',true);
END;
/

기본 오라클 오류와 내가 만든 오류가 둘다 나온걸 볼 수 있다.

FORALL절 안에서 오류상황

  • FORALL 절안에서 오류가 발생했더라도 해당 오류부분 이후도 수행 하고 싶을때 사용
  • FORALL..IN.. SAVE EXCEPTIONS
  • FORALL 문안에서 어떤 오류가 발생 할지 모르니깐 오류 선언 할때는 무조건 -24381 번호로 오류 선언을 해야함 
DECLARE
    TYPE num_type IS TABLE OF number;
    v_num num_type := num_type(10,11,0,12,30,0,20,199,2,0);
    dml_error EXCEPTION;
    PRAGMA EXCEPTION_INIT(dml_error,-24381);
BEGIN
    FORALL i IN v_num.first..v_num.last SAVE EXCEPTIONS -- SAVE EXCEPTONS를 해야 오류난 부분 이후를 수행한다.
        DELETE FROM hr.emp WHERE salary > 500000/v_num(i); --24381, FORALL 문안에서 고정된 오류번호 

EXCEPTION
    WHEN dml_error THEN
       FOR i IN v_num.first..v_num.last LOOP
            dbms_output.put_line(v_num(i) || ' ' ||sql%bulk_rowcount(i));
        END LOOP;
       ROLLBACK;
END;
/

sql%bulk_exceptions.count

  • FORALL문 수행시 save exceptions한 수를 리턴
  • 오류 발생한 수
DECLARE
    TYPE num_type IS TABLE OF number;
    v_num num_type := num_type(10,11,0,12,30,0,20,199,2,0);
    dml_error EXCEPTION;
    PRAGMA EXCEPTION_INIT(dml_error,-24381);
BEGIN
    FORALL i IN v_num.first..v_num.last SAVE EXCEPTIONS -- SAVE EXCEPTONS를 해야 오류난 부분을 저장
        DELETE FROM hr.emp WHERE salary > 500000/v_num(i); --24381, FORALL 문안에서 고정된 오류번호 
    
EXCEPTION
    WHEN dml_error THEN
        .put_line('number of errors is '||sql%bulk_exceptions.count); -- FORALL문에서 오류난 갯수를 리턴해줌
    ROLLBACK;
END;
/

sql%bulk_exceptions(오류발생수).error_index

  • 오류발생한 배열의 인덱스 번호를 리턴

-sql%bulk_exceptions(오류발생수).error_code

  • 실제 발생한 오류 번호를 리턴
  • 프로시저 실행 시 양수 값을 리턴하기 때문에 꼭 앞에 -를 붙여야함

sqlerrm(-sql%bulk_exceptions(오류발생수).error_code)

  • 해당오류 번호로 나온 오류메시지 리턴
DECLARE
    TYPE num_type IS TABLE OF number;
    v_num num_type := num_type(10,11,0,12,30,0,20,199,2,0);
    dml_error EXCEPTION;
    PRAGMA EXCEPTION_INIT(dml_error,-24381);
BEGIN
    FORALL i IN v_num.first..v_num.last SAVE EXCEPTIONS -- SAVE EXCEPTONS를 해야 오류난 부분을 저장
        DELETE FROM hr.emp WHERE salary > 500000/v_num(i); --24381, FORALL 문안에서 고정된 오류번호 
    
EXCEPTION
    WHEN dml_error THEN
        dbms_output.put_line('number of errors is '||sql%bulk_exceptions.count); -- FORALL문에서 오류난 갯수를 리턴해줌
        FOR j in 1..sql%bulk_exceptions.count LOOP
            dbms_output.put_line(sql%bulk_exceptions(j).error_index); -- 배열의 어떤 인덱스 때문에 오류가 발생 한지 파악
            dbms_output.put_line(-sql%bulk_exceptions(j).error_code); -- 실제 divisor is equal to zero의 오류번호는 -1476
            dbms_output.put_line(sqlerrm(-sql%bulk_exceptions(j).error_code)); -- 오류코드번호의 오류메시지 리턴
        END LOOP;
    ROLLBACK;
END;
/

profile
BUMSOO
이전 포스트

PL/SQL - DAY 7

다음 포스트

PL/SQL - DAY 9

0개의 댓글