# 함수 예제 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;