15일차 Chapter 2 - Functions

# 함수 예제 1

create or replace function uf_first return clob

is

 v clob;

begin

 v := '

SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO

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

      7369 SMITH      CLERK           7902 80/12/17        800                    20

      7499 ALLEN      SALESMAN        7698 81/02/20       1600        300         30

      7521 WARD       SALESMAN        7698 81/02/22       1250        500         30

      7566 JONES      MANAGER         7839 81/04/02       2975                    20

      7654 MARTIN     SALESMAN        7698 81/09/28       1250       1400         30

      7698 BLAKE      MANAGER         7839 81/05/01       2850                    30

      7782 CLARK      MANAGER         7839 81/06/09       2450                    10

      7788 SCOTT      ANALYST         7566 82/12/09       3000                    20

      7839 KING       PRESIDENT            81/11/17       5000                    10

      7844 TURNER     SALESMAN        7698 81/09/08       1500          0         30

      7876 ADAMS      CLERK           7788 83/01/12       1100                    20

      7900 JAMES      CLERK           7698 81/12/03        950                    30

      7902 FORD       ANALYST         7566 81/12/03       3000                    20

      7934 MILLER     CLERK           7782 82/01/23       1300                    10

14 개의 행이 선택되었습니다.

SQL>

사랑해요, 여러분들.. ㅎㅎ';

return v;

end;

/

  * 함수 활용 1 - select-list에 사용 가능 (SQL 문)

set long 2000000

set heading off

col ret format a100

select uf_first as ret from dual;

  * 함수 활용 2 - exec를 이용(environment)

exec dbms_output.put_line(uf_first);

  * 함수 활용 3 - 바인드 변수 사용

var g_ret clob

exec :g_ret := uf_first

print g_ret

exec :g_ret := uf_first || 'aaa'

print g_ret

    - 만약에 uf_first가 프로시저였다면

exec uf_first(:g_ret)

exec :g_ret := :g_ret || 'aaa'

  * 함수 활용 4 - pl/sql 블럭내에서 사용

declare

 v clob;

begin

 v := uf_first;

 p(v);

-- 또는

--  p(uf_first);

end;

/

# 함수 예제 2

set heading on

  

select deptno, max(sal) 

from emp

group by deptno;

  

  ↓ ↓ ↓ 

create index emp_deptno_sal_idx 

on emp (deptno, sal);

create or replace function uf_dept_sal_max(p_deptno emp.deptno%type) return number

is 

 v_sal emp.sal%type;

begin

 select /*+ index_desc(emp emp_deptno_sal_idx) */

   sal into v_sal

 from emp

 where deptno = p_deptno

 and rownum = 1;

 return v_sal;

end;

/

select deptno, dname, loc, uf_dept_sal_max(deptno) as max_sal

from dept;

# 함수 예제 3

drop table t1 purge;

create table t1 as select * from emp where 1=2;

create or replace function uf_emp_row(a emp.empno%type)

return emp%rowtype

is 

 r emp%rowtype;

begin

 select * into r from emp where empno = a;

 return r;

end;

/

select * from t1;

선택된 레코드가 없습니다.

declare

 emp_rec emp%rowtype;

begin

 emp_rec := uf_emp_row(&sv_empno);

 insert into t1 values emp_rec;

end;

/

Enter value for sv_empno: 7900

select * from t1;

    EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO

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

     7900 JAMES      CLERK           7698 81/12/03        950                    30

# 함수 예제 4 : 도량형 변환

create or replace function uf_pyoung_2_squre_meter(a number) return number

is

begin

 return a * 3.305785;

end;

/

select level as pyoung, uf_pyoung_2_squre_meter(level) as squre_meter

from dual

connect by level <= 100;

# 문제: 급여가 2000 미만이면 Low, 2000 이상 4000 미만이면 Mid, 4000 이상이면 High를 반환하는

함수를 작성하고 emp 테이블의 각 사원에 적용하세요.

create or replace function uf_grade(a number) 

 return varchar2

is

 v_grade varchar2(4);

begin

 v_grade := case when a < 2000 then 'Low'

                 when a < 4000 then 'Mid'

                 else               'High' 

            end;

 return v_grade;

end;

/

col grade format a10

select empno, ename, sal, uf_grade(sal) as grade

from emp;