存储过程、函数简单应用

 

create or replace procedure avgSalaryForDept(p_deptno in emp.deptno%type) is
v_avgSal number(7,2):=0;
begin

     --按照部门计算该部门雇员的平均工资
     
     --单行SELECT ... INTO ...
     select  avg(nvl(sal,0)) into v_avgSal from emp where deptno=p_deptno;
     dbms_output.put_line(p_deptno||' 部门的平均工资为： '||v_avgSal);
     --异常情况
     exception when no_data_found then
     dbms_output.put_line(p_deptno||' 不存在...');
     dbms_output.put_line(sqlcode||' --- '|| Sqlerrm);
  
end avgSalaryForDept;

--执行

--可以通过游标调用一次列出所有部门的情况

declare cursor emp_cursor
is
select distinct deptno from emp order by deptno asc;
begin
   for idx in emp_cursor loop
      --执行存储过程
       avgSalaryForDept(idx.deptno);
   end loop;
end;

 

--带有输出模式的参数

--需要按照给定的雇员编号更新雇员的工资，工资上调10%，如果更新成功，显示输出OK
--否则输出FAILED

create or replace procedure raisedSalaryByEmpnoPROC(p_empno in emp.empno%type,
o_result out varchar2) is
begin
    --执行更新
    update emp set sal=sal*1.1 where empno=p_empno;
    if(sql%found) then
       o_result:='OK';
       commit;
    else
       o_result:='FAILED';
       rollback;
    end if;
end raisedSalaryByEmpnoPROC;

--调用，区分：从SQL Plus环境调用还是从PLSQL环境调用

--第一种情况，从SQL Plus环境调用
SQL> variable tmp varchar2(30);
SQL> exec raisedSalaryByEmpnoPROC(7369,:tmp);

PL/SQL 过程已成功完成。

SQL> print tmp;

TMP
--------------------------------
OK

SQL> select * from emp where empno=7369;

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-12月-80          10890                    20

SQL>

--通过验证发现7369号雇员的薪水已经涨了。

 

--第二种情况，从PLSQL环境调用

 

SQL> declare
  2     tmp varchar2(30);
  3  begin
  4     raisedSalaryByEmpnoPROC(7369,tmp);
  5     dbms_output.put_line('结果为： '||tmp);
  6  end;
  7  /
结果为： OK

PL/SQL 过程已成功完成。

SQL>  select * from emp where empno=7369;

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-12月-80          11979                    20

SQL>

 

--调用存储过程，如果从SQL Plus环境中调用，可以有三种传参方法
--1、按照位置传参
--2、按照名字传参
--3、混合型传参


create or replace procedure avgSalaryForDept(p_deptno in emp.deptno%type) is
v_avgSal number(7,2):=0;
begin

     --按照部门计算该部门雇员的平均工资
     
     --单行SELECT ... INTO ...
     select  avg(nvl(sal,0)) into v_avgSal from emp where deptno=p_deptno;
     dbms_output.put_line(p_deptno||' 部门的平均工资为： '||v_avgSal);
     --异常情况
     exception when no_data_found then
     dbms_output.put_line(p_deptno||' 不存在...');
     dbms_output.put_line(sqlcode||' --- '|| Sqlerrm);
  
end avgSalaryForDept;

--执行调用的情况


--按照位置传参
exec avgSalaryForDept(10);

--按照名字传参

exec avgSalaryForDept(p_deptno=>30);

 




 

SQL> create or replace function tax(p_empno emp.empno%type) return number is
  2    Result number;
  3    v_ename emp.ename%type;
  4    v_sal emp.sal%type;
  5  begin
  6    
  7    --函数的功能是按照雇员编号计算雇员需要交纳的税金： 税金=工资*0.08
  8    select ename,sal into v_ename,v_sal from emp where empno= p_empno;
  9    result:=v_sal*0.08;
 10    dbms_output.put_line(v_ename||' 需要缴纳的税金为： '||result);
 11    return(Result);
 12    exception when no_data_found then
 13    dbms_output.put_line('该雇员不存在...');
 14  end tax;
 15  /

函数已创建。


SQL> select object_name,object_type from user_objects
  2  where object_type='FUNCTION';

OBJECT_NAME          OBJECT_TYPE
-------------------- --------------------
TAX                  FUNCTION

SQL>

--通过数据字典查看函数定义的源码

SQL> select object_name,object_type from user_objects
  2  where object_type='FUNCTION';

OBJECT_NAME          OBJECT_TYPE
-------------------- --------------------
TAX                  FUNCTION

SQL> set pagesize 100;
SQL> select text from user_source
  2  where name='TAX';

TEXT
------------------------------------------------------------------------
function tax(p_empno emp.empno%type) return number is
  Result number;
  v_ename emp.ename%type;
  v_sal emp.sal%type;
begin

  --函数的功能是按照雇员编号计算雇员需要交纳的税金： 税金=工资*0.08
  select ename,sal into v_ename,v_sal from emp where empno= p_empno;
  result:=v_sal*0.08;
  dbms_output.put_line(v_ename||' 需要缴纳的税金为： '||result);
  return(Result);
  exception when no_data_found then
  dbms_output.put_line('该雇员不存在...');
end tax;

已选择14行。

SQL>


--执行调用

--第一种情况：从SQL Plus环境调用


SQL> variable v_result number;
SQL> exec :v_result:=tax(7369);
SMITH 需要缴纳的税金为： 1054.152

PL/SQL 过程已成功完成。

SQL> print v_result;

  V_RESULT
----------
  1054.152

SQL>


SQL> exec :v_result:=tax(1000);
该雇员不存在...
BEGIN :v_result:=tax(1000); END;

*
第 1 行出现错误:
ORA-06503: PL/SQL: 函数未返回值
ORA-06512: 在 "SCOTT.TAX", line 14
ORA-06512: 在 line 1


SQL>

函数存在缺陷，原因是因为在异常处理过程中，没有返回值。

create or replace function tax(p_empno emp.empno%type) return number is
  Result number;
  v_ename emp.ename%type;
  v_sal emp.sal%type;
begin
  
  --函数的功能是按照雇员编号计算雇员需要交纳的税金： 税金=工资*0.08
  select ename,sal into v_ename,v_sal from emp where empno= p_empno;
  result:=v_sal*0.08;
  dbms_output.put_line(v_ename||' 需要缴纳的税金为： '||result);
  return(Result);
  exception when no_data_found then
  dbms_output.put_line('该雇员不存在...');
  result:=-1;
  return result;
end tax;


SQL>  exec :v_result:=tax(1000);
该雇员不存在...

PL/SQL 过程已成功完成。

SQL> print v_result;

  V_RESULT
----------
        -1

SQL>


--第二种情况：从PLSQL环境调用


--我们可以考虑使用游标

declare cursor emp_cursor
is
select ename, empno from emp order by empno asc;
v_result number(7,2);
begin
   
   for idx in emp_cursor loop
    v_result:=tax(idx.empno);
     dbms_output.put_line('****** '||idx.ename||' 需要缴纳的税金为： '||v_result);
   end loop;

end;