子程序

第七章子程序和程序包目标本节课的主要目标就是了解子程序和程序包。

为什么会用到子程序？引入什么是子程序？子程序就是能够接受参数并被其他程序所调用的命名PL/SQL块。

PL/SQL子程序有两种类型，过程和函数。

一般地，过程用于执行一个操作，而函数用于计算一个结果值。

与未命名或匿名PL/SQL块一样，子程序也有声明部分，执行部分和一个可选的异常处理部分。

声明部分包含类型、游标、常量、变量、异常和嵌套子程序的声明。

子程序子程序分为过程(store procedure)和函数(function),是一种特殊的pl/sql语句块。

它以编译好的的形式存储在数据库中，可以被后来的语句块调用。

运行时是编译而不是运行，要调用才会有结果。

DML增、删、改用过程，计算用函数。

过程：没有返回值。

函数：有且只有一个返回值。

子程序作用：1、允许模块化编程2、能够实现较快的执行过程3、能够减少网络流量4、可作为一种安全机制存储过程创建过程的语法CREATE[OR REPLACE]PROCEDURE<procedure name>[(<parameter list>)]IS|AS<local variable declaration>BEGIN<executable statements>[EXCEPTION<exception handlers>]END;存储过程中定义的形式参数，不能指定长度，否则报错。

1.不带参数的存储过程create or replace procedure test1isbegindbms_output.put_line('大家好');end;执行：SQL>set serveroutput onSQL>exec test1;任何的输出都要先执行SQL>set serveroutput on否则就无法看到输出结果，为什么呢？任何的输出在客户端看不见，他直接往oracle服务器上去输出。

执行这句让它在控制台上也输出一份。

2.带输入参数create or replace procedure test2(a in number,b in varchar2)isbegindbms_output.put_line(a);dbms_output.put_line(b);end;调用SQL>exec test2(1,'a');1a3.带有输出参数create or replace procedure add_proc (x in number,y in number,z out number)asbeginz:=x+y;end;调用：SQL>var result number;SQL>exec add_proc(1,3,:result);PL/SQL procedure successfully completed Print result---------4SQL>print result;result---------44带有in out的参数create or replace procedure test4(a in out number,b in out number)astemp number;begindbms_output.put_line(a);dbms_output.put_line(b);temp:=a;a:=b;b:=temp;end;调用：SQL>var n1numberSQL>var n2numberSQL>exec:n1:=1SQL>exec:n2:=3PL/SQL过程已成功完成。

SQL>exec test4(:n1,:n2);13PL/SQL过程已成功完成。

SQL>print n1n2;N1----------100N2----------200SQL>组合应用--设计一个存储过程，接受一个员工的姓名，返回一个员工的工资--另外，如果员工的工资>3000，输出一个‘高薪’的字符串--否则，输出低薪create or replace procedure getnamebysal(v_name emp.ename%type,v_sal out emp.sal%type,v_level out varchar2)is--这里可以定义变量v_temp emp.sal%type;beginselect sal into v_temp from emp where ename=v_name;v_sal:=v_temp;if v_temp>3000thenv_level:='高薪';elsev_level:='低薪';end if;end;--使用pl/saldeclareV_NAME varchar2(20):='SMITH';v_sal emp.sal%type;v_level varchar2(20);begingetnamebysal(v_name,v_sal,v_level);dbms_output.put_line(v_sal);dbms_output.put_line(v_level);end;存储过程之间可以相互调用：CREATE OR REPLACE procedure test8asvalue1varchar(10):='KING';value2number;begintest7(value1,value2);dbms_output.put_line(value2);end;参数的传递位置传递名称传递exec test4(var1=>’a’,var2=>’b’);组合传递查看过程源码select text from user_source where name='TEST4';维护子程序查看编译错误：show errors删除子程序：Drop procedure procedure_name;Drop function function_name;注意：在存储过程中不能够使用select语句直接查询，否则会出现编译错误。

函数和存储过程相似，但是要明确规定返回值。

不带参数--函数create or replace function get_userreturn varchar2--返回值类型，不加长度isv_name emp.ename%type;beginselect ename into v_name from emp where empno='7369';return v_name;end;执行：调用SQL>var v1varchar2(100);SQL>exec:v1:=get_userPL/SQL过程已成功完成。

SQL>print v1V1--------------------------------SCOTT或者select get_user from dual;带参数查找指定员工的工资create or replace function get_sal(e_name in varchar2)return numberasv_sal emp.sal%type;beginselect sal into v_sal from emp where upper(ename)=upper(e_name);return v_sal;exceptionwhen NO_DATA_FOUND THENraise_application_error(-20000,'员工不存在');end;带有out参数如果要返回多个值，可以使用out参数create or replace function get_info(e_name varchar2,title out varchar2)return varchar2asdeptname dept.dname%type;beginselect a.job,b.dname into title,deptnamefrom emp a,dept bwhere a.deptno=b.deptnoand upper(a.ename)=upper(e_name);return deptname;end;调用SQL>var job varchar2(20);SQL>var dname varchar2(20);SQL>exec:dname:=get_info('scott',:job)PL/SQL过程已成功完成。

SQL>print dname jobDNAME--------------------------------RESEARCHJOB--------------------------------ANALYSTSQL>带有in out参数create or replace function testf(num1number,num2in out number)return numberasv_result number(6);beginv_result:=num1/num2;num2:=mod(num1,num2);return v_result;end;调用SQL>var result1numberSQL>var result2numberSQL>exec:result2:=30PL/SQL过程已成功完成。

SQL>exec:result1:=testf(100,:result2) PL/SQL过程已成功完成。

SQL>print result1result2; RESULT1----------3RESULT2----------10。