231106 PL/SQL 복습 겸 문제

[문제] 사원번호를 입력값으로 받아서 사원의 last_name 을 출력해주세요. 

hit) 패키지: 호출방식, 오버로드화(타입), exception(예외처리), 암시적커서

 

# 호출방식

execute emp_find.find(100)

execute dbms_output.put_line(emp_find.find(100));

select employee_id, emp_find.find(employee_id)
from employee_id;

 

내가 쓴 코드)

create or replace package emp_find
is 
    type num_tab_type is table of number index by PLS_INTEGER;
    type var_tab_type is table of varchar2(30) index by PLS_INTEGER;
    
    PROCEDURE find( tab out num_tab_type , emp_id IN number ); 
    PROCEDURE find( tab out var_tab_type , emp_id IN number ); 
end emp_find;
/

create or replace package body emp_find
is 
    PROCEDURE find( tab out num_tab_type, emp_id in number ) 
    is 
        v_id number;
        v_name varchar2(30);
    begin
        select employee_id, last_name
        into v_id, v_name
        from hr.employees
        where employee_id = emp_id;
        
    exception
        when no_data_found then 
            dbms_output.put_line(emp_id||'해당 사원은 존재하지 않습니다.');
        when others then 
            dbms_output.put_line(sqlerrm);
    end find;
        
    procedure find(tab out var_tab_type, emp_id in number) 
    is 
        v_id number;
        v_name varchar2(30);
    begin
        select employee_id, last_name
        into v_id, v_name
        from hr.employees
        where employee_id = emp_id;
        
        dbms_output.put_line(v_id||' '||v_name);
    exception
        when no_data_found then
            dbms_output.put_line(emp_id||'해당 사원은 존재하지 않습니다.');
        when others then 
            dbms_output.put_line(sqlerrm);
    end find;
    
end emp_find;
/

 

정답)

CREATE OR REPLACE PACKAGE emp_find IS
    PROCEDURE find ( p_id IN NUMBER );
    FUNCTION find ( p_id IN NUMBER ) RETURN VARCHAR2;
END emp_find;
/

CREATE OR REPLACE PACKAGE BODY emp_find
is 
    PROCEDURE find ( p_id IN NUMBER ) 
    IS 
        v_name VARCHAR2(30);
    BEGIN 
        SELECT last_name
        INTO v_name
        FROM hr.employees
        WHERE employee_id = p_id;
        
        dbms_output.put_line(v_name);
    EXCEPTION
        WHEN no_data_found THEN 
            dbms_output.put_line(p_id||' 사원은 존재하지 않습니다.');
        WHEN others THEN
            RAISE_APPLICATION_ERROR(-20000, sqlerrm);
    END find;
    
    FUNCTION find ( p_id IN NUMBER ) 
        RETURN VARCHAR2
    IS 
        v_name VARCHAR2(30);
    BEGIN 
        SELECT last_name
        INTO v_name
        FROM hr.employees
        WHERE employee_id = p_id;
        
        RETURN v_name;
    EXCEPTION
        WHEN no_data_found THEN 
            --dbms_output.put_line(p_id||' 사원은 존재하지 않습니다.');
            RETURN null;
        WHEN others THEN
            RETURN null;
            --RAISE_APPLICATION_ERROR(-20000, sqlerrm);
    END find;
    
END emp_find;
/

EXECUTE emp_find.find(100);
EXECUTE dbms_output.put_line(emp_find.find(100));
SELECT employee_id, emp_find.find(employee_id) FROM hr.employees;

select * from user_errors;
SELECT * FROM user_source WHERE name = 'EMP_FIND' AND type = 'PACKAGE';
SELECT * FROM user_source WHERE name = 'EMP_FIND' AND type = 'PACKAGE BODY';
SELECT * FROM user_objects WHERE object_name = 'EMP_FIND';