231102 PL/SQL PROCEDURE RETURN문, EXCEPTION, RAISE_APPLICATION_ERROR, CASE-WHEN THEN ELSE END CASE;

# RETURN 문 : 프로시저 종료 
# OR REPLACE 옵션 : 프로시저를 삭제, 생성할 경우, 권한은 그대로 생존.

CREATE OR REPLACE PROCEDURE hr.query_emp (
    p_id NUMBER ) 
IS
    v_rec hr.employees%ROWTYPE;
BEGIN
    IF p_id IN (100, 101, 102) THEN -- TRUE 
        RETURN; -- 종료 
    ELSE 
        SELECT * 
        INTO v_rec
        FROM hr.employees
        WHERE employee_id = p_id;
    
        DBMS_OUTPUT.PUT_LINE(v_rec.last_name||' works on '||v_rec.job_id||', earns $'||v_rec.salary||' per month.');
    
    END IF;
    
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('해당 사원이 없습니다');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE(SQLERRM);
END query_emp;
/

# 프로시저 호출

execute hr.query_emp(100);

# e_error EXCEPTION : 에러 변수 선언

CREATE OR REPLACE PROCEDURE hr.query_emp (
    p_id NUMBER ) 
IS 
    v_rec hr.employees%ROWTYPE;
    e_error EXCEPTION; --에러 변수 선언
BEGIN
    IF p_id IN (100, 101, 102) THEN -- TRUE 
        RAISE e_error;
    ELSE -- FALSE 
        SELECT * 
        INTO v_rec
        FROM hr.employees
        WHERE employee_id = p_id;
    
        DBMS_OUTPUT.PUT_LINE(v_rec.last_name||' works on '||v_rec.job_id||', earns $'||v_rec.salary||' per month.');
    
    END IF;
    
EXCEPTION
    WHEN e_error THEN 
        DBMS_OUTPUT.PUT_LINE('조회할 수 없습니다.');
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('해당 사원이 없습니다');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE(SQLERRM);
END query_emp;
/

# 호출

execute hr.query_emp(200);

# RAISE_APPLICATION_ERROR() 변경

CREATE OR REPLACE PROCEDURE hr.query_emp (
    p_id NUMBER ) 
IS 
    v_rec hr.employees%ROWTYPE;
BEGIN
    IF p_id IN (100, 101, 102) THEN -- TRUE 
        RAISE_APPLICATION_ERROR(-20000, '조회할 수 없습니다.');
    ELSE -- FALSE 
        SELECT * 
        INTO v_rec
        FROM hr.employees
        WHERE employee_id = p_id;
    
        DBMS_OUTPUT.PUT_LINE(v_rec.last_name||' works on '||v_rec.job_id||', earns $'||v_rec.salary||' per month.');
    END IF;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('해당 사원이 없습니다');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE(SQLERRM);
END query_emp;
/