搜索
PL/SQL高级编程存储过程函数的概念开发一个存储过程或函数目的是把一个PL／SQL块存进数据库中，并在以后重复使用。例如：创建一个记录用户名和当前日期的无参数过程CREATEPROCEDURElog_executionISBEGININSERTINTOlog_table(user_id,log_date)VALUES(user,sysdate)；END；/可以在不同的环境调用同一个存储过程。注意：语法有区别●在SQL*Plus中记录用户名和当前日期SQL＞EXECUTElog_execution；●从存储过程FIRE_EMP中记录用户名和当前日期CREATEPROCEDUERfire_emp(v_emp_noINemp.empno％TYPE)ISBEGINlog_execution；DELETEFROMempWHEREempno=v_emp_no；END；/存储过程与应用程序的区别概念存储过程应用程序存储位置存储在数据库数据字典中存储在应用程序中调用地方任何数据库工具或应用中都可以调用只能在建立程序的应用中才能调用相互调用不可以调用应用程序可以调用存储过程建立程序的文档存储的位置存储在数据库的数据字典中存储在当前的应用中安全性由数据库提供安全保证，必须通过授权才能使用存储子程序安全性靠应用程序保证，如果能执行应用程序，就能执行该程序。开发存储过程和函数1、开发一个存储过程和函数的步骤（1）用文本编辑器编写一个含有CreateProcedure或CreateFunction语句的PL/SQL脚本文件。（如：c:\procedure1.sql）（2）在SQL*Plus中用命令（如：SQLstartc:\procedure1.sql;）编译脚本文件，调试编译错误。系统就将创建过程或函数的源代码存入数据字典user_source中。（3）编译成功后将编译代码存入数据字典（4）调用存储过程和函数，在运行期间调试存储过程、函数的逻辑错误。创建存储过程的语法注意：IS后面是一个完整的PL/SQL块的三部分（参见第六章），可以定义局部变量、游标等，但不能以DECLARE开始。CREATE[ORREPLACE]PROCEDURE过程名[(参数名[IN|OUT|INOUT]数据类型,…)]{IS|AS}[说明部分]BEGIN语句序列[EXCEPTION出错处理]END[过程名]；★形式参数可以有三种模式----IN、OUT、INOUT。如果没有为形式参数指定模式，那么缺省的模式是IN。类型描述--------------------------------------------------------------------IN(缺省)参数用来从调用环境中向过程传递值OUT参数用来从过程中返回值给调用者INOUT参数既可从调用者向过程中传递值，也可以从过程中返回可能改变了的值给调用者局部变量在过程内部存放值例：下面的过程说明了三种模式参数的区别createorreplaceproceduremodetest(p_inparameterinnumber,p_outparameteroutnumber,p_inoutparameterinoutnumber)isv_localvariablenumber;beginv_localvariable:=p_inparameter;/*正确*/(p_inparameter:=7;错误)v_outparameter:=7;/*正确*/(v_localvariable:=p_outparameter;错误)v_localvariable:=p_inoutparameter;/*正确*/v_inoutparameter:=7;/*正确*/end;/例：下列存储过程给某一指定的员工涨指定数量的工资。Createprocedureraise_salary(emp_idinteger,v_increasereal)isbeginupdateempsetsal=sal+v_increasewhereempno=emp_id;commit;end;/例：下列过程根据给定的员工号返回员工的姓名、工资和奖金等信息。CREATEORREPLACEPROCEDUREquery_emp(v_emp_noINemp.empno％TYPE，V_emp_nameOUTemp.ename％TYPE，v_emp_salOUTemp.sal％TYPE，V_emp_commOUTemp.comm％TYPE)ISBEGINSELECTename，sal，commINTOv_emp_name，v_emp_sal，v_emp_commFROMempWHEREempno=v_emp_no;ENDquery_emp；/例：利用INOUT模式参数，将一个7位数字的电话号码转换成8位数字的电话号码。CREATEORREPLACEPROCEDUREadd_dash(v_phone_noINOUTVARCHAR2)/*字符型的形式参数不指定长度*/ISBEGINv_phone_no:＝SUBSTR(v_phone_no,l,3)||‘-’||SUBSTR(v_phone_no，4，4)；ENDadd_dash；/创建存储函数的语法CREATE[ORREPLACE]FUNCTION函数名[（参数名[IN]数据类型...）]RETURN数据类型{IS|AS}[说明部分]BEGIN语句序列RETURN（表达式）[EXCEPTION例外处理程序]END[函数名]；例：根据员工号获取该员工工资的查询CREATEORREPLACEFUNCTIONget_sal(p_emp_noINemp.empno％TYPE)RETURNNUMBERISv_emp_salemp.sal％TYPE:＝0；BEGINSELECTsalINTOv_emp_salFROMempWHEREempno=p_emp_no;RETURN(v_emp_sal)；ExceptionWhenno_data_foundortoo_many_rowsthenDbms_output.put_line('发生系统错误');WhenothersthenDbms_output.put_line(sqlerrm);ENDget_sal；/例：编写一个函数，计算几个人的平均工资，并在函数体的循环过程中输出结果。CREATEORREPLACEFUNCTIONaverage_sal(v_nINNUMBER(3))RETURNNUMBERISCURSORc_empISSELECTempno，salFROMemp；v_total_salemp.sal％TYPE:=0；v_counternumber;v_emp_noemp.empno%type;BEGINFORr_empINc_empLOOPEXITWHENc_emp％ROWCOUNT＞v_nORc_emp％NOTFOUND；v_total_sal:=v_total_sal+r_emp.sal；v_counter:=c_emp％ROWCOUNT；v_emp_no:=r_emp.empno；DBMS_OUTPUT.PUT_LINE(‘loop=’||v_counter||‘；Empno=’||v_emp_no)；ENDLOOP；RETURN(v_total_sal／v_counter)；ENDaverage_sal；/存储过程与函数的区别存储过程和函数的主要差别有两个：一是返回值的方法不同二是调用方法不同（1）返回值的方法不同●存储函数：有零个或多个参数，但不能有OUT参数。函数只返回一个值，靠RETURN子句返回。●存储过程：有零个或多个参数，过程不返回值，其返回值是靠OUT参数带出来的。（2）调用方法不同调用过程的语句可以作为单独的可执行语句在PL/SQL块中单独出现。如：过程名（实际参数1，实际参数2...）；函数可以在任何表达式能够出现的地方被调用，调用函数的语句不能作为可执行语句单独出现在PL/SQL块中。如：变量名：=函数名（实际参数1，实际参数2...）存储过程和函数中的出错处理存储过程和函数就是一个PL/SQL块，所以在过程函数体内应该考虑出错处理。不管是哪种类型的出错情况，只要在过程和函数体内考虑了出错处理的方法，即使在运行过程中出现了错误，过程和函数都能成功地执行，程序不会被中断。如果在过程和函数体内忽略了出错处理，过程和函数执行时以交互方式通知用户出错，让用户自行处理，程序被中断。例：根据给定的员工号，删除该员工记录。CREATEORREPLACEPROCEDUREfire_emp(p_emp_noINemp.empno％TYPE)ISinvalid_employeeEXCEPTION；（定义错误）BEGINDELETEFROMempWHEREempno=p_emp_no;IFSQL％NOTFOUNDTHENRAISEinvalid_employee；（触发错误）ENDIF；COMMITWORK；EXCEPTIONWHENinvalid_employeeTHEN（处理错误）ROLLBACKWORK；INSERTINTOexception_table(line_nr,line)VALUES(1,‘Employeedoesnotexist.’)；WHENothersTHENDbms_output.put_line(sqlerrm);ENDfire_emp；/存储过程和函数的管理过程和函数的管理命令任务命令创建一个新的过程或函数CREATEPROCEDURE／FUNCTION创建或修改一个已有的过程或函数CREATEORREPLACEPROCEDURE／FUNCTION删除一个已有的过程或函数DROPPROCEDURE／FUNCTION例：删除存储过程FIRE_EMPSQLDROPPROCEDUREFIRE_EMP;查看过程和函数的文档信息存储的信息描述获得的途径源代码过程、函数的文本查看USER_SOURCE数据字典编译代码编译代码（p_code）无法获得编译错误过程、函数的语法错误查看USER_ERRORS数据字典或用SHOWERRORS命令SQLshowerrors;存储过程和函数的调用和测试参数传值一般采用位置对应法向形式参数传值，要求实际参数与形式参数保持次序、类型、个数一致。例：从SQL*Plus命令中通过位置对应法调用HIRE_EMP过程。SQLvariablev_enamevarchar2(12)；SQLEXECUTEhire_emp(9999，:v_ename)；注意：●如果形式参数是IN模式的参数，实际参数可以是一个具体的值，或一个有值的变量。●如果形式参数是OUT模式的参数，实际参数必须是一个变量。当调用过程后，此变量就被赋值了。●如果形式参数是INOUT模式的参数，则实际参数必须是一个预先已经赋值的变量。执行完过程后，该变量被重新赋值。可以调用系统内置的DBMS_OUTPUT包中的过程输出此变量的值，测试过程执行的结果。存储过程和函数的调用方法程序名返回值用法过程无返回值（靠OUT模式参数带出结果）出现在一个完整的可执行语句中函数返回一个值可以在语句中代替变量或表达式的位置调用环境语法调用过程例子调用函数例子SQL*PlusEXECUTE命令SQLexecuteraise_salary(7788,1000);SQLSELECTget_sal(7788)fromdual;另外的存储过程或函数、块直接调用raise_salary(7788,1000);selectget_sal(7788)into变量fromdual;预编译程序（PRO*C）EXECSQL命令EXECSQLraise_salary(7788,1000);EXECSQLselectget_sal(7788)into变量fromdual;各种环境调用过程和函数的语法和例子（1）在PL/SQL块中如何调用●过程的调用例：从PL／SQL块中调用过程QUERY_