Function 사용법

▣ 기본 구문

function(userdefinedFunction,BuiltinFunction(groupFunction,singleRowFunction);

Ex)

SQL> select power(2,3) from dual;

   

POWER(2,3)

----------

8

▣ userdefinedFunction 생성(입력받은 숫자 2개를 더하는 function)

SQL> create or replace function f1(s1 number,s2 number)

2 return number

3 as

4 begin

5 return s1+s2;

6 end;

7 /

   

Function created.

   

SQL> sav f1

Created file f1.sql

SQL> @f1

   

Function created.

   

SQL> select f1(10,3) from dual;

   

F1(10,3)

----------

13

   

▶ 세율이 3.3%라고 가정하자. 세금을 구하는 function을 만드시오. 단, 급여액이 1000이하는 면세

SQL> r

1 create or replace function tax(s number)

2 return number

3 as

4 begin

5 if s>1000 then

6 return s*0.033;

7 else

8 return 0;

9 end if;

10* end;

   

Function created.

   

SQL> sav tax

Created file tax.sql

SQL> select ename,sal,tax(sal) "TAX" from emp;

   

ENAME SAL TAX

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

SMITH 800 0

ALLEN 1600 52.8

WARD 1250 41.25

JONES 2975 98.175

MARTIN 1250 41.25

BLAKE 2850 94.05

CLARK 2450 80.85

SCOTT 3000 99

KING 5000 165

TURNER 1500 49.5

ADAMS 1100 36.3

   

ENAME SAL TAX

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

JAMES 950 0

FORD 3000 99

MILLER 1300 42.9

   

14 rows selected.