存储过程、函数简单应用
?
create or replace procedure avgSalaryForDept(p_deptno in emp.deptno%type) isv_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_cursorisselect distinct deptno from emp order by deptno asc;begin for idx in emp_cursor loop --执行存储过程 avgSalaryForDept(idx.deptno); end loop;end; --带有输出模式的参数--需要按照给定的雇员编号更新雇员的工资，工资上调10%，如果更新成功，显示输出OK--否则输出FAILEDcreate or replace procedure raisedSalaryByEmpnoPROC(p_empno in emp.empno%type,o_result out varchar2) isbegin --执行更新 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--------------------------------OKSQL> select * from emp where empno=7369; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-12月-80 10890 20SQL>--通过验证发现7369号雇员的薪水已经涨了。 --第二种情况，从PLSQL环境调用 SQL> declare 2 tmp varchar2(30); 3 begin 4 raisedSalaryByEmpnoPROC(7369,tmp); 5 dbms_output.put_line('结果为： '||tmp); 6 end; 7 /结果为： OKPL/SQL 过程已成功完成。SQL> select * from emp where empno=7369; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-12月-80 11979 20SQL> --调用存储过程，如果从SQL Plus环境中调用，可以有三种传参方法--1、按照位置传参--2、按照名字传参--3、混合型传参create or replace procedure avgSalaryForDept(p_deptno in emp.deptno%type) isv_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 FUNCTIONSQL>--通过数据字典查看函数定义的源码SQL> select object_name,object_type from user_objects 2 where object_type='FUNCTION';OBJECT_NAME OBJECT_TYPE-------------------- --------------------TAX FUNCTIONSQL> 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.152PL/SQL 过程已成功完成。SQL> print v_result; V_RESULT---------- 1054.152SQL>SQL> exec :v_result:=tax(1000);该雇员不存在...BEGIN :v_result:=tax(1000); END;*第 1 行出现错误:ORA-06503: PL/SQL: 函数未返回值ORA-06512: 在 "SCOTT.TAX", line 14ORA-06512: 在 line 1SQL>函数存在缺陷，原因是因为在异常处理过程中，没有返回值。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---------- -1SQL>--第二种情况：从PLSQL环境调用--我们可以考虑使用游标declare cursor emp_cursorisselect 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;