231103 PL/SQL 복습 겸 문제

[문제] 사원번호를 입력값으로 받아서 사번, 이름, 부서 이름을 출력하는 프로시저를 생성하세요.

execute id_proc(p_id => 100);

 

CREATE OR REPLACE PROCEDURE id_proc (
	p_id IN NUMBER )
IS 
	v_id number;
    v_name varchar2;
    v_dept_name varchar2;
BEGIN 
	SELECT e.employee_id, e.last_name, d.department_id
    INTO v_id, v_name, v_dept_name
    FROM hr.employees e, hr.departments
    WHERE e.department_id = d.department_id
    AND employee_id = p_id;
    
    dbms_output.put_line('사원번호 : '||v_id||' 사원이름 : '||v_name||' 부서이름 : '||v_dept_name);
    
EXCEPTION
	WHNE no_data_found THNE
    	dbms_output.put_line(p_id||' 사원은 존재하지 않습니다.');
	WHNE others THNE
    	dbms_output.put_line(sqlerrm);
END id_proc;
/

 

호출

execute id_proc(p_id => 100);

 

[문제] 부서코드를 입력하면 사원번호, 이름, 부서이름을 출력하는 프로시저를 생성해주세요.

execute dept_sawon(20) 

201 Hartstein Marketion
202 Fay Maketing

execute dept_sawon(200)

200 부서가 없습니다.

 

방법1) 

문제점 발생; 

v_id 에 200 을 입력했을 경우, 예외처리 구문 로직처리가 되지 않는다.

DECLARE
	CURSOR dept_cursor IS
    	SELECT e.employee_id, e.last_name, d.department_name 
        FROM hr.employees e, hr.depratments d
        WHERE e.department_id = v_id
        AND d.department_id = v_id;
        
        dept_null EXCEPTION; --예외처리 변수
BEGIN
	FOR v_rec IN dept_cursor LOOP
    	IF v_rec.employee_id IS NULL THEN
        	RAISE dept_null;
        ELSE
        	dbms_output.put_line(v_rec.employee_id||' '||v_rec.last_name||' '||v_rec.department_name);
        END IF;
    END LOOP;
EXCEPTION
	WHEN dept_null THEN
    	dbms_output.put_line(v_id||' 부서가 없습니다.');
	WHEN others THEN
    	dbms_output.put_line(sqlerrm);
END;

 

방법2) 

DECLARE
	v_id := 20; 
    CURSOR dept_cursor IS
        SELECT e.employee_id, e.last_name, d.department_name
        FROM hr.employees e, hr.departments d
        WHERE e.department_id = v_id
        AND d.department_id = v_id; 
        
        v_rec dept_cursor%rowtype;
        dpet_null EXCEPTION;
BEGIN
	OPEN dept_cursor;
	LOOP
    	FETCH dept_cursor INTO v_rec;
        IF v_rec.employee_id IS NULL THEN 
        	RAISE dept_null;
        ELSIF dept_cursor%NOTFOUND THEN
        	EIXT;
        ELSE
        	dbms_output.put_line(v_rec.employee_id||' '||v_rec.last_name||' '||v_rec.department_name);
        END IF;
    END LOO;
    CLOSE dept_cursor;
EXCEPTION
	WHEN dept_null THEN
    	dbms_output.put_line(v_id||' 부서가 없습니다.');
	WHEN others THEN
    	dbms_output.put_line(sqlerrm);
END;
/

 

방법3) 

다른 user도 사용하게 하자 => procdure

CREATE OR REPLACE PROCEDUE dept_sawon (v_id IN NUMBER)
IS
	CURSOR dept_cursor IS
        SELECT e.employee_id, e.last_name, d.department_name
        FROM hr.employees e, hr.departments d
        WHERE e.department_id = v_id
        AND d.department_id = v_id; 
        
    v_rec dept_cursor%rowtype; --레코드변수
    dept_null EXCEPTION; --예외처리 변수
BEGIN
    OPEN dept_cursor; --메모리 할당
    LOOP
        FETCH dept_cursor INTO v_rec;
        IF v_rec.employee_id IS NULL THEN -- active set 할때
            RAISE dept_null; -- true이면
        ELSIF dept_cursor%NOTFOUND THEN -- fetch 할 경우가 없을 경우
            EXIT;
        ELSE
            dbms_output.put_line(v_rec.employee_id||' '||v_rec.last_name||' '||v_rec.department_name);
        END IF;
    END LOOP;
    CLOSE dept_cursor;
EXCEPTION
    WHEN dept_null THEN
        dbms_output.put_line(v_id||' 부서가 없습니다.');
    WHEN others THEN
        dbms_output.put_line(sqlerrm);
END dept_sawon;
/

execute dept_sawon(20);
execute dept_sawon(200);

BEGIN
    dept_sawon(20);
    dept_sawon(200);
END;
/

 

[문제] 자신의 부서 평균 급여보다 더 많이 받는 사원은 'yes' 아니면 'no' 를 리턴하는 함수를 작성하세요.

SELECT employee_id, salary, department_id, avg_dept(department_id, salary)
FROM hr.employees;

 

# 예외변수 처리하기 전

CREATE OR REPLACE FUNCTION avg_dept(
	p_id IN number , 
    p_name IN number ) 
    RETURN VARCHAR2
IS
	v_sal number; 
BEGIN
	SELECT salary 
    INTO v_sal 
    FROM hr.employees
    WHERE department_id = p_id;
    
    IF p_sal > v_sal THEN 
    	RETRUN 'YES'; 
    ELSE 
    	RETRUN 'NO';
    END IF;
EXCEPTION
	WHEN NO_DATA_FOUND THEN 
    	dbms_output.put_line(p_id||'데이터가 없습니다.');
END avg_dept;
/

 

호출

BEGIN 
	dbms_output.put_line(avg_dept(300, 1000));
END;
/

 

# 예외변수 처리 후

CREATE OR REPLACE FUNCTION avg_dept(
	p_id IN number , 
    p_name IN number ) 
    RETURN VARCHAR2
IS
	v_sal number; 
    is_null EXCEPTION; 
BEGIN
	SELECT salary 
    INTO v_sal 
    FROM hr.employees
    WHERE department_id = p_id;
    
    IF v_sal IS NULL THEN 
    	RETRUN is_null; 
    ESLIF p_sal > v_sal THEN
    	RETRUN 'YES';
    ELSE 
    	RETRUN 'NO';
    END IF;
EXCEPTION
	WHEN NO_DATA_FOUND THEN 
    	dbms_output.put_line(p_id||'데이터가 없습니다.');
        RETURN NULL;
END avg_dept;
/