Oracle 数据库应用教程课件 第7章

第7章过程、函数和程序包第7章过程、函数和程序包7.1子程序7.2程序包7.3小结习题七上机实验七第7章过程、函数和程序包7.1子程序以前我们写的PL/SQL语句程序都是瞬时的，都没有命名。其缺点是：在每次执行的时候都要被编译，并且不能被存储在数据库中，其他PL/SQL块也无法调用它们。现在我们把命名的PL/SQL块叫做子程序，它们存储在数据库中，可以为它们指定参数，可以在数据库客户端和应用程序中调用。命名的PL/SQL程序包括存储过程和函数。程序包是存储过程和函数的集合。子程序结构与PL/SQL匿名块的相同点在于都由声明、执行、异常三大部分构成，不同之处在于，PL/SQL匿名块的声明可选，而子程序的声明则是必需的。第7章过程、函数和程序包子程序的优点如下：(1)模块化：通过子程序可以将程序分解为可管理的、明确的逻辑模块。(2)可重用性：子程序在创建并执行后，就可以在任何应用程序中使用。(3)可维护性：子程序可以简化维护操作。(4)安全性：用户可以设置权限，保护子程序中的数据，只能让用户提供的过程和函数访问数据。这不仅可以让数据更加安全，同时可保证正确性。子程序有两种类型：过程和函数。其中，过程用于执行某项操作；函数用于执行某项操作并返回值。第7章过程、函数和程序包7.1.1过程1．过程的创建和执行过程可使用createprocedure语句创建，语法如下：createorreplaceprocedure[方案名.]存储过程名[parameterlist]{Is|as}[local_declarations];Beginexecutablestatements[exception][Exception_handlers]End[procedure_name];第7章过程、函数和程序包保留字Is前面的过程定义称为过程头。 过程头包括过程名和具有数据类型的参数列表。过程体包括声明部分、执行部分和异常处理部分。过程体从保留字Is之后开始。其中，声明部分和异常处理部分是可选的； 执行部分至少包含一条语句。这里的Is|as就相当于declare声明部分，除了拥有前面的一个过程声明语句外，其他和以前的匿名PL/SQL块一样。其中，replace表示在创建存储过程中，如果已经存在同名的存储过程，则重新创建；如果没有此关键词，则当数据库中有同名的过程时会报错“ORA-00955号错误：名称已被现有对象占用”。必须将同名的过程删除后才能创建。第7章过程、函数和程序包1)创建不带参数的过程。【例7.1】创建一个过程multiplication，用来实现九九乘法表。SQLcreateorreplaceproceduremultiplication2as3iinteger;4jinteger;5begin6dbms_output.put_line('printmultiplication');7foriin1..9loop8forjin1..9loop9ifi=jthen第7章过程、函数和程序包10dbms_output.put(to_char(j)||'*'||11to_char(i)||'='||to_char(i*j)||'');12endif;13endloop;14dbms_output.put_line('');15endloop;16end;17/过程已创建。出现编译错误的时候可以用showerror或者descuser_errors来调试。第7章过程、函数和程序包2)执行过程创建过程的时候并不会执行过程，必须在这之后调用过程来执行。执行过程的方法有两种：一种是在SQL提示符下，使用execute语句来执行过程；另一种是在匿名块中调用。execute执行过程的语法如下：executeprocedure_name(parameters_list);第7章过程、函数和程序包【例7.2】执行multiplication过程。SQLsetserverouton--将SQL*Plus的输出打开SQLexecutemultiplication--执行过程multiplication用execute命令。printmultiplication1*1=11*2=22*2=41*3=32*3=63*3=91*4=42*4=83*4=124*4=161*5=52*5=103*5=154*5=205*5=251*6=62*6=123*6=184*6=245*6=306*6=361*7=72*7=143*7=214*7=285*7=356*7=427*7=491*8=82*8=163*8=244*8=325*8=406*8=487*8=568*8=641*9=92*9=183*9=274*9=365*9=456*9=547*9=638*9=729*9=81PL/SQL过程已成功完成。第7章过程、函数和程序包【例7.3】在匿名块中调用过程multiplication。SQLbegin2multiplication;3end;4/程序运行结果同上。第7章过程、函数和程序包2．创建带参数的过程调用程序通过参数可向被调用子程序传递值。在上述语法[parameterlist]中，参数的具体形式如下：＜参数1，[方式1]＜数据类型1,＜参数2，[方式2]＜数据类型2,…参数方式有以下三种：(1)IN表示接受值为默认值。(2)OUT表示将值返回给子程序的调用程序。(3) INOUT表示接受值并返回已更新的值。第7章过程、函数和程序包参数的书写格式为：[(参数1IN | OUT | INOUT参数类型,参数2IN | OUT | INOUT参数类型，…)]。参数IN模式是默认模式。如果未指定参数的模式，则认为该参数是IN参数。对于OUT和INOUT参数，必须明确指定，并且这两种类型的参数在返回到调用环境之前必须先赋值。IN参数可以在调用时赋默认值，而OUT参数和INOUT参数不可以。第7章过程、函数和程序包1)创建带IN模式参数的过程【例7.4】创建一个过程，以雇员号为参数查询雇员的姓名和职位。SQLcreateorreplaceprocedurequeryEmpName(sFindNoemp.EmpNo%type)2as3sNameemp.ename%type;4sJobemp.job%type;5begin6selectename,jobintosName,sJobfromemp7whereempno=sFindNo;8dbms_output.put_line('IDis'||sFindNo||'dezhigongnameis'||第7章过程、函数和程序包9sName||'gongzuois'||sJob);10exception11whenno_data_foundthen12dbms_output.put_line('nodata');13whentoo_many_rowsthen14dbms_output.put_line('toomanydata');15whenothersthen16dbms_output.put_line('error');17end;18/过程已创建。第7章过程、函数和程序包【例7.5】执行queryEmpName过程。SQLexecqueryEmpName('7900');IDis7900dezhigongnameisJAMESgongzuoisCLERK同样，也可通过匿名块调用过程queryEmpName。SQLBegin2queryEmpName('7900');3end;4/第7章过程、函数和程序包2)创建带OUT模式参数的过程【例7.6】创建一个过程，以雇员号查询雇员的薪水。SQLcreateorreplaceprocedurequeryEmpSal(sFindNoemp.EmpNo%type,v_saloutemp.sal%type)2as3begin4selectsalintov_salfromemp5whereempno=sFindNo;6dbms_output.put_line('Thesalaryof'||sFindNo||'is:'||v_sal);第7章过程、函数和程序包7exception8whenno_data_foundthen9dbms_output.put_line('nodata');10whentoo_many_rowsthen11dbms_output.put_line('toomanydata');12whenothersthen13dbms_output.put_line('error');14end;/过程已创建。此过程带有一个输入参数sFindNo和输出参数v_sal，程序根据输入参数到表中查询记录，以返回该员工的薪水值。第7章过程、函数和程序包【例7.7】执行queryEmpSal过程。可以声明一个变量，用如下的方式调用该过程。SQLvarsalarynumber;SQLexecqueryEmpSal('7900',:salary);Thesalaryof7900is:950PL/SQL过程已成功完成。第7章过程、函数和程序包另外，也可以从一个匿名的PL/SQL程序中执行上述过程，以显示sal_out变量的输出结果。以下代码可以显示queryEmpSal过程的返回值。Declarevaluenumber;BeginqueryEmpSal(7934,value);DBMS_OUTPUT.PUT_LINE('VALUE的值为'||to_char(value));End;/第7章过程、函数和程序包3)创建带INOUT模式参数的过程【例7.8】创建两个数进行交换的过程。SQLcreateorreplaceprocedureswap(p1INOUTnumber,p2INOUTnumber)2as3tempnumber;4begin5temp:=p1;6p1:=p2;7p2:=temp;8end;9/SQL/过程已创建。第7章过程、函数和程序包【例7.9】执行swap过程。SQLDeclare2N1number:=10;3N2number:=20;4Begin5swap(N1,N2);6DBMS_OUTPUT.PUT_LINE('N1的值是'||N1);7DBMS_OUTPUT.PUT_LINE('N2的值是'||N2);8End;9/N1的值是20N2的值是10PL/SQL过程已成功完成。第7章过程、函数和程序包3．过程的授权只有创建过程的用户和管理员才有使用过程的权限，如以上示例，创建的过程就像创建的表一样，属于当前操作的用户，其他用户如果要调用过程，则需要得到该过程的EXECUTE权限，然后通过点标记(dotnotation)(即“用户名.过程名”)来调用过程(数据字典是user_source)。以下演示如何授权：SQLGRANTEXECUTEONswapTOJohn;SQLGRANTEXECUTEONqueryEmpNameTOPUBLIC;前者将swap过程的执行权限授予John用户，后者将queryEmpName的执行权限授予所有数据库用户。第7章过程、函数和程序包4．删除过程删除存储过程的命令的一般格式如下：DROPPROCEDURE[方案名.]存储过程名;【例7.10】删除过程multiplication。SQLDROPPROCEDUREmultiplication过程已丢弃。第7章过程、函数和程序包7.1.2函数函数与过程相似，也是数据库中存储的已命名PL/SQL程序块。与过程不同的是，函数除了完成一定的功能外，还必须返回一个值。1．创建函数创建函数是指通过RETURN子句指定函数返回值的数据类型。在函数体的任何地方，用户都可以通过RETURNexpression语句从函数返回。第7章过程、函数和程序包定义函数的语法如下：CREATE[ORREPLACE]FUNCTION[方案名.]函数名[paramete