Oracle存储函数案例与调用删除

1、创建带有输入参数的存储函数：根据部门编号返回该部门的总工资：

create or replace function func1

(v_deptno in NUMBER)

return NUMBER

is 

        v_sumsal   NUMBER;

begin

        select sum(sal) into v_sumsal from emp where deptno = v_deptno;

        return v_sumsal;

exception
        when no_data_found then

                     dbms_output.put_line('没有此部门！');

        when others then

                      dbms_output.put_line(SQLERRM);

end;

2、创建带有输出参数的存储函数：根据员工号输出员工的姓名和工资，并返回员工的年收入：

create or replace function func2

(v_empno in emp.empno%TYPE,v_name out emp.ename%TYPE,v_sal out emp.sal%TYPE)

return NUMBER

is

       v_salsum NUMBER;

begin

       select ename,sal,(sal+nvl(comm,0))*12 into v_name,v_sal,v_salsum from emp where empno = v_empno;

       return v_salsum;

exception

      when no_data_found then

          dbms_output.put_line('没有此员工号！')；

     when others then

          dbms_output.put_line(SQLERRM);

end;

3、创建带有输入输出参数的存储函数：求两个数的平方和，并输出两个数的平方：

create or replace function func3

(n1 in out NUMBER,n2 in out NUMBER)

return NUMBER

as

begin

       n1 ：= n1*n1;

       n2  :  = n2*n2;

       return n1+n2;

end;

4、存储函数的调用：

（1）调用带有输入参数的函数
BEGIN
  DBMS_OUTPUT.put_line('部门的工资总额'||func1(&no));
END;

（2）调用带有输出参数的函数
DECLARE
   v_name emp.ename%TYPE;
   v_sal  emp.sal%TYPE;
   v_salsum NUMBER;
BEGIN
  v_salsum := func2(&no,v_name,v_sal);
  DBMS_OUTPUT.put_line('姓名：'||v_name);
  DBMS_OUTPUT.put_line('工资：'||v_sal);
  DBMS_OUTPUT.put_line('年收入：'||v_salsum);
END;

（3）调用带有输入输出参数的函数
DECLARE
  v_n1 number :=5;
  v_n2 number :=6;
  v_sum number;
BEGIN
  v_sum := func3(v_n1,v_n2);
  DBMS_OUTPUT.put_line('N1的平方：'||v_n1);
  DBMS_OUTPUT.put_line('N2的平方：'||v_n2);
  DBMS_OUTPUT.put_line('平方和：'||v_sum);
END;

5、删除存储函数
DROP FUNCTION func1;

6、求部门的年收入
CREATE OR REPLACE FUNCTION func4
(v_deptno IN NUMBER DEFAULT 10,v_t IN NUMBER)
RETURN NUMBER
IS
       v_sumsal NUMBER;
BEGIN 
       SELECT SUM((sal+nvl(comm,0))*12)
       INTO v_sumsal
       FROM emp
       WHERE deptno = v_deptno;   
       DBMS_OUTPUT.put_line('测试：'||v_t);    
       RETURN v_sumsal;
EXCEPTION
       WHEN NO_DATA_FOUND THEN
         DBMS_OUTPUT.put_line('没有此部门');
       WHEN OTHERS THEN
         DBMS_OUTPUT.put_line(SQLERRM);
END;

7、调用
declare
         v_totalsal number;
begin
        v_totalsal := func4(v_t=>1);
        dbms_output.put_line(v_totalsal);
end;