存储过程、函数简单应用
create or replace procedure avgSalaryForDept(p_deptno in emp.deptno%type) isv_avgSal number(7,2):=0;begin --按照部门计算该部门雇员的平均工资 --单行SELECT ... INTO ... selectavg(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> declare2 tmp varchar2(30);3begin4 raisedSalaryByEmpnoPROC(7369,tmp);5 dbms_output.put_line('结果为： '||tmp);6end;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 ... selectavg(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 is2 Result number;3 v_ename emp.ename%type;4 v_sal emp.sal%type;5begin6 7 --函数的功能是按照雇员编号计算雇员需要交纳的税金： 税金=工资*0.088 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('该雇员不存在...'); 14end tax; 15/函数已创建。SQL> select object_name,object_type from user_objects2where object_type='FUNCTION';OBJECT_NAME OBJECT_TYPE-------------------- --------------------TAX FUNCTIONSQL>--通过数据字典查看函数定义的源码SQL> select object_name,object_type from user_objects2where object_type='FUNCTION';OBJECT_NAME OBJECT_TYPE-------------------- --------------------TAX FUNCTIONSQL> set pagesize 100;SQL> select text from user_source2where name='TAX';TEXT------------------------------------------------------------------------function tax(p_empno emp.empno%type) return number isResult number;v_ename emp.ename%type;v_sal emp.sal%type;begin--函数的功能是按照雇员编号计算雇员需要交纳的税金： 税金=工资*0.08select 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 thendbms_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 isResult number;v_ename emp.ename%type;v_sal emp.sal%type;begin --函数的功能是按照雇员编号计算雇员需要交纳的税金： 税金=工资*0.08select 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 thendbms_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;
页:
[1]