251111 TIL

EXCEPTION(예외)

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

예외 처리

• EXCEPTION 키워드로 시작
• 여러 예외 처리기를 사용할 수 있음
• 블록을 종료하기 전 하나의 처리기만 실행
• WHEN OTHERS THEN 마지막절

DECLARE
    ...
BEGIN
    ...
EXCEPTION
    WHEN no_data_found then
        ...
    WHEN too_many_rows then
        ...
    WHEN OTHERS THEN
        ...
END;
/

Predefined Exception(미리 정의된 오라클 서버 오류)

• 오라클 오류 번호에 따른 예외 사항 이름이 생성되어 있음

오류 보고 -
ORA-01403: no data found

• ORA-01403 : 오류 번호
• no data found : 오류 메시지

https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/predefined-exceptions.html

• non predefined exception에 대해 오류명을 지정해줘야 함

프로그램 비정상 종료 시 자동 롤백

DROP TABLE hr.test PURGE;
CREATE TABLE hr.test(id number, name varchar2(30));

DECLARE
    v_rec employees%rowtype;
BEGIN
    INSERT INTO hr.test(id, name) VALUES(1, 'ORACLE'); -- 트랜잭션 시작

    SELECT *
    INTO v_rec
    FROM hr.employees
    WHERE department_id = 20; -- ORA-01422 -> 롤백

    dbms_output.put_line(v_rec.employee_id || ' ' || v_rec.last_name);
END;
/

SELECT * FROM hr.test;

예외 처리를 통한 프로그램 정상 종료

DECLARE
    v_rec employees%rowtype;
BEGIN
    INSERT INTO hr.test(id, name) VALUES(1, 'ORACLE'); -- 트랜잭션 시작

    SELECT *
    INTO v_rec
    FROM hr.employees
    WHERE department_id = 20; -- ORA-01422 -> 롤백

    dbms_output.put_line(v_rec.employee_id || ' ' || v_rec.last_name);
EXCEPTION
    WHEN too_many_rows THEN
        dbms_output.put_line('여러 행 인출');
END;
/

-- 프로그램 정상 종료 -> 트랜잭션 살아있음
SELECT * FROM hr.test;
ROLLBACK;

• 프로그램 실행 중에 오류가 발생했더라도 예외 사항 처리기를 수행했기 때문에 프로그램은 정상적으로 종료
• 트랜잭션은 진행 중인 상태로 있음
• 트랜잭션을 제어하는 작업을 꼭 수행해줘야 함(COMMIT, ROLLBACK)

예외 정보 조회 함수

DECLARE
    v_rec employees%rowtype;
BEGIN
    SELECT *
    INTO v_rec
    FROM hr.employees
    WHERE department_id = 20;

    dbms_output.put_line(v_rec.employee_id || ' ' || v_rec.last_name);
EXCEPTION
    WHEN others THEN
        dbms_output.put_line('오류가 발생해서 프로그램 종료');
        dbms_output.put_line(sqlcode);
        dbms_output.put_line(sqlerrm);
END;
/

• SQLCODE : 오류 번호를 리턴하는 함수
  • 0 : 예외가 발생하지 않음
  • 1 : 유저가 정의한 예외사항
  • +100 : no_data_found -1403
  • 음수 : 오라클 서버에서 발생한 오류
• SQLERRM : 오류 번호, 오류 메시지를 리턴하는 함수

Non Predefined Exception 처리

BEGIN
    DELETE FROM hr.departments WHERE department_id = 10;
EXCEPTION
    WHEN others THEN
        dbms_output.put_line(sqlcode); -- -2292
        dbms_output.put_line(sqlerrm); -- ORA-02292: integrity constraint (HR.EMP_DEPT_FK) violated - child record found
END;
/

• 오라클 오류 번호에 따른 예외 사항 이름이 없는 경우

예외 정의

DECLARE
    pk_error EXCEPTION; -- 예외사항 이름 선언
    PRAGMA EXCEPTION_INIT(pk_error, -2292); -- 내가 만든 예외사항 이름과 오라클 오류 번호를 연결하는 지시어
BEGIN
    DELETE FROM hr.departments WHERE department_id = 10;
EXCEPTION
    WHEN pk_error THEN
        dbms_output.put_line('PK 값을 참조하고 있는 자식 행들이 있습니다.');
    WHEN others THEN
        dbms_output.put_line(sqlcode);
        dbms_output.put_line(sqlerrm);
END;
/

User Defined Exceptions(사용자 정의 예외)

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('수정된 데이터가 없습니다.');
        dbms_output.put_line(sqlcode);
        dbms_output.put_line(sqlerrm);
END;
/

RAISE_APPLICATION_ERROR 프로시저

• 유저가 정의한 오류번호 메시지를 실행하는 프로그램
• 이 프로시저를 수행하는 순간 프로그램은 비정상적인 종료
• 오류 번호 : -20000 ~ -20999
• 오류 메시지 : 2048byte
• FALSE(기본값) : 내가 만든 오류 번호 메시지만 출력
• TRUE : 오라클 오류 번호 메시지도 함께 출력

문제

-- [문제15] 배열변수안에 있는 사원번호(100,300,102,105)를 기준으로 그 사원의 employee_id, last_name, 출력하는 프로그램 작성해주세요. 만약에 사원이 존재하지 않은 경우에도 처리를 한 후 다음 사원을 처리하도록해주세요.
/*
100 King
300사원은 존재하지 않습니다.
102 De Haan
105 Austin
*/

DECLARE
    TYPE id_type IS TABLE OF number;
    v_id id_type := id_type(100, 300, 102, 105);
BEGIN
    FOR i IN v_id.first..v_id.last LOOP
        DECLARE
            TYPE rec_type IS RECORD(
                id employees.employee_id%type,
                name employees.last_name%type
            );
            v_rec rec_type;
        BEGIN
            SELECT employee_id, last_name
            INTO v_rec
            FROM hr.employees
            WHERE employee_id = v_id(i);
            dbms_output.put_line(v_rec.id || ' ' || v_rec.name);
        EXCEPTION
            WHEN no_data_found THEN
                dbms_output.put_line(v_id(i) || ' 사원은 존재하지 않습니다');
        END;
    END LOOP;
END;
/

익명 블록(anonymous block)

• 이름이 없는 블록(프로그램)
• 데이터베이스에 객체로 저장되지 않습니다.
• 매번 수행 시 컴파일해야 한다.(익명 블록 프로그램이 shared pool 메모리 안에 library cache에 없으면)
• 다른 응용프로그램에서 호출할 수 없음
• 값을 반환하지 않음(return 값을 수행할 수 없음)
  • 익명 블록 구조에서 값을 반환하려면 외부에서 선언된 바인드 변수(입력, 전달) 사용

서브 프로그램(subprogram)

• 이름이 있는 블록(프로그램)
• 데이터베이스에 객체로 저장
• 한 번만 컴파일
• 다른 응용 프로그램에서 호출 가능
• 값을 반환할 수 있음
• 파라미터 사용 가능(입력, 전달)
• 프로시저(procedure), 함수(function), 패키지(package)
• create procedure 시스템 권한 필요
• 서브 프로그램에서는 bind 변수 사용 불가

프로시저(procedure)

• 특정 작업을 수행하는 서브 프로그램
• 데이터베이스에 스키마 객체로 저장할 수 있음
• 모듈식, 확장성, 재사용성, 유지 관리 용이성이 증대

CREATE OR REPLACE PROCEDURE procedure_name(
    parameter [mode] datatype,
    ...
    parameter [mode] datatype
)
IS[AS]
    변수, 상수, 명시적 커서, 사용자가 정의한 예외사항
BEGIN
EXCEPTION
END;

CREATE PROCEDURE 권한 확인

SELECT * FROM session_privs;

파라미터가 없는 프로시저

CREATE OR REPLACE PROCEDURE emp_proc
IS
    v_rec employees%rowtype;
BEGIN
    SELECT *
    INTO v_rec
    FROM hr.employees
    WHERE employee_id = 100;

    dbms_output.put_line(v_rec.employee_id || ' ' || v_rec.last_name);
EXCEPTION
    WHEN no_data_found THEN
        RAISE_APPLICATION_ERROR(-20000, '사원은 존재하지 않습니다.', TRUE);
END;
/

프로시저 호출

-- execute로 호출
execute emp_proc;
exec emp_proc;

-- 익명 블록에서 호출
BEGIN
    emp_proc;
END;
/

파라미터가 있는 프로시저

CREATE OR REPLACE PROCEDURE emp_proc(p_id IN number)
IS
    v_rec employees%rowtype;
BEGIN
    SELECT *
    INTO v_rec
    FROM hr.employees
    WHERE employee_id = p_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;
/

exec emp_proc(100)

프로시저 소스 조회

SELECT text FROM user_source WHERE name = 'EMP_PROC' ORDER BY line;

• set pagesize n : 페이지 사이즈 설정

프로시저 구조 조회

파라미터

• 파라미터는 데이터 값을 호출 환경에서 프로시저 또는 프로시저에서 호출 환경으로 전송하는데 사용

1. 형식 매개 변수(formal parameter)

• 서브 프로그램 사양의 파라미터 리스트에 선언된 로컬 변수
• 데이터 타입만 사용해야 함(SIZE 지정 X)
• parameter mode
  • IN : 호출 환경에서 값을 프로시저(함수)로 전달, 상수로 동작
  • OUT : 프로시저에서 값을 호출 환경으로 전달, 변수로 동작
  • IN OUT :

2. 실제 매개 변수(actual parameter)

• 실제 파라미터를 실제 인수(argument)라고도 함
• 호출 서브 프로그램의 파라미터 리스트에 사용되는 숫자, 리터럴 값, 변수, 표현식
• 실제 매개변수 값은 형식 매개 변수에 대응되게 입력하면 됨

IN 모드

-- 사원번호, 급여 인상 비율을 입력 값으로 받아서 급여를 수정하는 프로그램
CREATE OR REPLACE PROCEDURE raise_salary(p_id IN number, p_pct IN number)
IS
BEGIN
    UPDATE hr.employees
    SET salary = salary * (1 + p_pct / 100)
    WHERE employee_id = p_id;
END raise_salary;
/

desc raise_salary;
/*
인수 이름 유형     In/Out 기본값?    
----- ------ ------ ------- 
P_ID  NUMBER IN     unknown 
P_PCT NUMBER IN     unknown 
*/

-- 100번 사원 급여 10% 인상
SELECT salary FROM hr.employees WHERE employee_id = 100; --24000
exec raise_salary(100, 10);
SELECT salary FROM hr.employees WHERE employee_id = 100; -- 26400
ROLLBACK;

-- 전체 사원 급여 10% 인상
BEGIN  
    FOR i IN (SELECT employee_id FROM hr.employees) LOOP
        raise_salary(i.employee_id, 10);
    END LOOP;
    ROLLBACK;
END;
/

OUT 모드

CREATE OR REPLACE PROCEDURE emp_proc(p_id IN number, p_name OUT varchar2, p_sal OUT number)
IS
BEGIN
    SELECT last_name, salary
    INTO p_name, p_sal
    FROM hr.employees
    WHERE employee_id = p_id;
EXCEPTION
    WHEN no_data_found THEN
        RAISE_APPLICATION_ERROR(-20000, '사원은 존재하지 않습니다.', TRUE);
END;
/

desc emp_proc;
/*
인수 이름  유형       In/Out 기본값?    
------ -------- ------ ------- 
P_ID   NUMBER   IN     unknown 
P_NAME VARCHAR2 OUT    unknown 
P_SAL  NUMBER   OUT    unknown 
*/

-- 호출
var b_name varchar2(30);
var b_sal number;

exec emp_proc(100, :b_name, :b_sal);

print b_name b_sal;

-- 익명 블록에서 호출
DECLARE
    v_name varchar2(30);
    v_sal number;
BEGIN
    emp_proc(100, v_name, v_sal);
    dbms_output.put_line(v_name || ' ' || v_sal);
END;
/