Oracle——函數

標籤：for   nvl   員工   image   過程   ram   全域   .com   load   

1.文法格式

create [or replace] fuction name [(parameter,...)]return datatypeas|is(local declarations)begin  statement;  return return_values;end name;

 

2.建立名為ANNUAL_COMP的函數，通過接收兩個變數（某個員工的月工資pi_sal和獎金pi_comm）返回年薪。該函數中要求進行空值處理（即工資和獎金為null時都視為0）。

（1）建立並調用函數ANNUAL_COMP，傳遞工資和獎金列的值，這兩個值允許為空白，但是該函數應該仍能返回一個非空年薪。使用下面的公式定義：年薪=(工資*12)+獎金

（2）要求顯示20號部門所有的僱員編號、姓名、工資、獎金以及年薪（年薪要求調用函數獲得）。

SQL> create or replace function annual_comp  2  (pi_sal number,pi_comm number)  3   return number  4  as  5   annual_sal number;  6  begin  7    annual_sal:=nvl(pi_sal,0)*12+nvl(pi_comm,0);  8    return annual_sal;  9  end annual_comp; 10  /函數已建立。SQL> select empno,ename,sal,comm,annual_comp(sal,comm)  2  from emp  3  where deptno=20;     EMPNO ENAME             SAL       COMM ANNUAL_COMP(SAL,COMM)---------- ---------- ---------- ---------- ---------------------      7369 SMITH             800                             9600      7566 JONES            2975                            35700      7788 SCOTT            3000                            36000      7876 ADAMS            1100                            13200      7902 FORD             3000                            36000

注意：

   ?函數參數：只能用in參數

   ?函數可以有多個return語句，但執行一個return語句

3.使用函數的方法

   ?將函數的傳回值賦給一個變數或全域變數

   ?在select語句中使用

4.刪除函數的文法

drop function function_name;

5.建立名為valid_deptno的函數，已知部門號，判斷該部門是否存在與dept部門表中。

SQL> create or replace function valid_deptno  2  (v_deptno dept.deptno%type)  3   return boolean  4  as  5   v_count number;  6  begin  7    select count(*) into v_count from dept where deptno=v_deptno;  8    if v_count=0 then  9      return false; 10    else 11     return true; 12    end if; 13  end; 14  /函數已建立。SQL> declare  2    flag boolean;  3  begin  4    flag:=valid_deptno(4);  5    if flag then  6       dbms_output.put_line(‘該部門存在‘);  7    else  8       dbms_output.put_line(‘該部門不存在‘);  9    end if; 10  end; 11  /該部門存在PL/SQL 過程已成功完成。SQL> declare  2    flag boolean;  3  begin  4    flag:=valid_deptno(100);  5    if flag then  6       dbms_output.put_line(‘該部門存在‘);  7    else  8       dbms_output.put_line(‘該部門不存在‘);  9    end if; 10  end; 11  /該部門不存在PL/SQL 過程已成功完成。

 

Oracle——函數