#12_140630_PL_SQL_FUNCTION

# FUNCTION

함수 수행하는 방법  - execute dbms_output.put_line(get_mgr(7788));

문제127. 부서번호를 입력하고 프로시져를 실행하면 해당 부서번호의 평균월급이 출력되는 프로시져를 함수로 변경하시오

CREATE OR REPLACE PROCEDURE avgsal

(p_deptno emp.deptno%type)

IS

 v_avgsal emp.sal%TYPE;

BEGIN

 SELECT AVG(sal) INTO v_avgsal

 FROM EMP

 WHERE deptno = p_deptno;

 dbms_output.put_line('평균월급 ' || v_avgsal);

END;

/ 

CREATE OR REPLACE FUNCTION GET_AVGSAL

(p_deptno IN emp.deptno%type)

RETURN NUMBER

IS

 v_avgsal emp.sal%TYPE;

BEGIN

 SELECT AVG(sal) INTO v_avgsal

 FROM EMP

 WHERE deptno = p_deptno;

RETURN v_avgsal;

END;

/

l 상단의 return number가 추가되고 dbms가 없어지고 return 변수가 하단에 추가된다.

l 함수는 select 구문안에만 가능하다

문제128. 부서번호를 입력하면 해당 부서번호의 부서위치가 출력되게 하는 함수를 만들고 아래와 같이 쿼리하시오

select ename, sal, get_loc(deptno)

from emp;

CREATE OR REPLACE FUNCTION get_loc

(p_deptno IN emp.deptno%type)

RETURN character

IS

 v_loc dept.loc%TYPE;

BEGIN

 SELECT loc INTO v_loc

 FROM DEPT

 WHERE deptno = p_deptno;

RETURN v_loc;

END;

/

retire_cause.txt

문제131. 위의 쿼리를 이용해서 프로시져를 생성하는데 순위를 물어보게 하고 순위를 입력하면 해당 그 순위의 퇴직 사유를 출력하시오

CREATE OR REPLACE PROCEDURE retire_rank

(p_rnk IN number)

IS

 v_cause retire_cause.cause%TYPE;

BEGIN

 SELECT cause INTO v_cause

 FROM (SELECT cause, RANK() OVER(ORDER BY bigcomp desc) big, RANK() OVER(ORDER BY smallcomp desc) small FROM retire_cause)

 WHERE big = p_rnk;

 dbms_output.put_line('퇴직사유 ' || v_cause);

END;

/

문제134. 함수를 생성하는데 사원번호를 입력했을 때 해당사원이 관리자인인지 사원인지를 출력하는 함수를 생성하시오. 그래서 아래와 같이 결과를 출력하시오

select ename, sal, get_mgr(empno)

from emp;

CREATE OR REPLACE FUNCTION get_mgr

(p_empno emp.empno%type)

RETURN CHARACTER

IS

 v_ename emp.ename%TYPE;

 v_char VARCHAR2(20);

BEGIN

SELECT ename, CASE WHEN empno IN (SELECT mgr FROM emp)

THEN '관리자' ELSE '사원' END INTO v_ename, v_char

FROM EMP

WHERE empno = p_empno;

RETURN v_char;

END;

/

문제135. 아래와 같이 결과를 출력하는 함수 get_comp를 생성하시오

create table retire_cause2

as

 select rownum as rn, r.*

 from retire_cause r;

select cause, get_comp(rn)

from retire_cause2;

CREATE OR REPLACE FUNCTION get_comp

(p_rnum IN NUMBER)

RETURN CHARACTER

IS

 v_big  NUMBER(10);

 v_small NUMBER(10);

 v_char VARCHAR2(20);

begin

 SELECT bigcomp, smallcomp INTO v_big, v_small

 FROM retire_cause2

 WHERE rn = p_rnum;

 IF v_big >= v_small THEN

  v_char := '대기업';

 ELSE

  v_char := '중소기업';

 end IF;

RETURN v_char;

END;

/

------------------------------------------------------------------

SELECT * FROM retire_cause2;

CREATE OR REPLACE FUNCTION get_compp

(p_rnum IN NUMBER)

RETURN CHARACTER

IS

 v_char VARCHAR2(20);

BEGIN

SELECT CASE WHEN bigcomp >= smallcomp

 THEN '대기업' ELSE '중소기업' END INTO v_char

FROM retire_cause2

WHERE rn = p_rnum;

RETURN v_char;

END;

문제143. 우리가 그동안 만들었던 함수 리스트를 확인하고 함수를 전부 삭제하시오

SELECT text

FROM user_source

WHERE TYPE='FUNCTION';

SELECT DISTINCT 'drop function '|| NAME ||';'

FROM user_source

WHERE type='FUNCTION';

emp2345.txt

문제215. 증감율을 사용자 정의함수를 생성해서 함수를 가지고 밑의 쿼리를 이용해서 결과를 아래의 그림과 같이 출력하시오. 매개변수를 이용.

select ns, fun2(ns), sy, fun2(sy)

from emp2345;

FROM EMP2345;CREATE OR REPLACE FUNCTION fun_ndl

(p_noodle VARCHAR2,

p_hiredate number)

RETURN NUMBER

IS

 v_ndl NUMBER(10);

BEGIN

SELECT cnt INTO v_ndl FROM(

 select hiredate, ROUND(((ns-lag(ns,1) over(order by hiredate asc))/(LAG(ns,1) OVER(ORDER BY hiredate))*100),1) ns,

 ROUND(((sy-lag(sy,1) over(order by hiredate asc))/(LAG(sy,1) OVER(ORDER BY hiredate))*100),1) sy,

 ROUND(((ot-lag(ot,1) over(order by hiredate asc))/(LAG(ot,1) OVER(ORDER BY hiredate))*100),1) ot,

 ROUND(((pd-lag(pd,1) over(order by hiredate asc))/(LAG(pd,1) OVER(ORDER BY hiredate))*100),1) pd

 FROM EMP2345)

 unpivot(cnt FOR noo IN (ns, sy, ot, pd))

WHERE hiredate=p_hiredate AND UPPER(noo)=UPPER(p_noodle);

RETURN v_ndl;

END;

/

SELECT hiredate, ns 농심, fun_ndL('ns',hiredate) 농심증감율 , sy 삼양, fun_ndL('sy',hiredate) 삼양증감율,

ot 오뚜기, fun_ndL('ot',hiredate) 오뚜기증감율, pd 팔도, fun_ndL('pd',hiredate) 팔도증감율

FROM EMP2345;