Oracle——函数

1.语法格式

create [or replace] fuction name
 [(parameter,...)]
return datatype
as|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 过程已成功完成。