资源描述
单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,*,PL/SQL,高级编程,存储过程函数的概念,开发一个存储过程或函数目的是把一个,PL,SQL,块存进数据库中，并在以后重复使用。,例如：,创建一个记录用户名和当前日期的,无参数过程,CREATE PROCEDURE,log_execution,IS,BEGIN,INSERT INTO,log_table(user_id,log_date,),VALUES(user,sysdate,),；,END,；,/,可以在不同的环境调用同一个存储过程。,注意：语法有区别,在,SQL*Plus,中记录用户名和当前日期,SQL,EXECUTE,log_execution,；,从存储过程,FIRE_EMP,中记录用户名和当前日期,CREATE PROCEDUER,fire_emp,(,v_emp_no,IN,emp.empno,TYPE),IS,BEGIN,log_execution,；,DELETE FROM,emp,WHERE,empno,=,v_emp_no,；,END,；,/,存储过程与应用程序的区别,概念,存储过程,应用程序,存储位置,存储在数据库数据字典中,存储在应用程序中,调用地方,任何数据库工具或应用中都可以调用,只能在建立程序的应用中才能调用,相互调用,不可以调用应用程序,可以调用存储过程,建立程序的文档存储的位置,存储在数据库的数据字典中,存储在当前的应用中,安全性,由数据库提供安全保证，必须通过授权才能使用存储子程序,安全性靠应用程序保证，如果能执行应用程序，就能执行该程序。,开发存储过程和函数,1,、开发一个存储过程和函数的步骤,（,1,）用文本编辑器,编写,一个含有,Create Procedure,或,Create Function,语句的,PL/SQL,脚本文件,。（如：,c:procedure1.sql,）,（,2,）在,SQL*Plus,中用命令（如：,SQLstart c:procedure1.sql;,）,编译脚本文件,，调试编译错误。系统就将创建过程或函数的,源代码存入数据字典,user_source,中。,（,3,）编译成功后将,编译代码存入数据字典,（,4,）,调用存储过程和函数,，在运行期间调试存储过程、函数的逻辑错误。,创建存储过程的语法,注意：,IS,后面是一个完整的,PL/SQL,块的三部分（参见第六章），可以定义局部变量、游标等，但不能以,DECLARE,开始。,CREATE OR REPLACE PROCEDURE,过程名,(,参数名,IN,|OUT|IN OUT,数据类型,),IS|AS,说明部分,BEGIN,语句序列,EXCEPTION,出错处理,END,过程名,；,形式参数可以有三种模式,-IN,、,OUT,、,IN OUT,。如果没有为形式参数指定模式，那么缺省的模式是,IN,。,类型 描述,-,IN(,缺省,),参数 用来从调用环境中,向过程传递值,OUT,参数 用来从过程中,返回值给调用者,IN OUT,参数 既可从调用者向过程中传递值，,也可以从过程中返回可能改变了,的值给调用者,局部变量 在过程内部存放值,例：,下面的过程说明了三种模式参数的区别,create or replace procedure,modetest,(,p_inparameter,in number,p_outparameter,out number,p_inoutparameter,in out number),is,v_localvariable,number;,begin,v_localvariable,:=,p_inparameter,;/*,正确*,/,(,p_inparameter,:=7;,错误,),v_outparameter,:=7;/*,正确*,/,(,v_localvariable,:=,p_outparameter,;,错误,),v_localvariable,:=,p_inoutparameter,;/*,正确*,/,v_inoutparameter,:=7;/*,正确*,/,end;,/,例：,下列存储过程给某一指定的员工涨指定数量的工资。,Create procedure,raise_salary,(,emp_id,integer,v_increase,real),is,begin,update,emp,set,sal,=,sal,+,v_increase,where,empno,=,emp_id,;,commit;,end;,/,例：,下列过程根据给定的员工号返回员工的姓名、工资和奖金等信息。,CREATE OR REPLACE PROCEDURE,query_emp,(,v_emp_no,IN,emp.empno,TYPE,，,V_emp_name,OUT,emp.ename,TYPE,，,v_emp_sal,OUT,emp.sal,TYPE,，,V_emp_comm,OUT,m,TYPE),IS,BEGIN,SELECT,ename,，,sal,，,comm,INTO,v_emp_name,，,v_emp_sal,，,v_emp_comm,FROM,emp,WHERE,empno,=,v_emp_no,;,END,query_emp,；,/,例：,利用,IN OUT,模式参数，将一个,7,位数字的电话号码转换成,8,位数字的电话号码。,CREATE OR REPLACE PROCEDURE,add_dash,(,v_phone_no,IN OUT VARCHAR2),/*,字符型的形式参数不指定长度*,/,IS,BEGIN,v_phone_no,:,SUBSTR,(v_phone_no,l,3)|,-,|SUBSTR,(,v_phone_no,，,4,，,4),；,END,add_dash,；,/,创建存储函数的语法,CREATE OR REPLACE FUNCTION,函数名,（参数名,IN,数据类型,.,）,RETURN,数据类型,IS|AS,说明部分,BEGIN,语句序列,RETURN,（表达式）,EXCEPTION,例外处理程序,END,函数名,；,例：,根据员工号获取该员工工资的查询,CREATE OR REPLACE FUNCTION,get_sal,(,p_emp_no,IN,emp.empno,TYPE),RETURN NUMBER,IS,v_emp_sal,emp.sal,TYPE:,0,；,BEGIN,SELECT,sal,INTO,v_emp_sal,FROM,emp,WHERE,empno,=,p_emp_no,;,RETURN(v_emp_sal,),；,Exception,When,no_data_found,or,too_many_rows,then,Dbms_output.put_line,(,发生系统错误,);,When others then,Dbms_output.put_line(sqlerrm,);,END,get_sal,；,/,例：,编写一个函数，计算几个人的平均工资，并在函数体的循环过程中输出结果。,CREATE OR REPLACE FUNCTION,average_sal(v_n,IN NUMBER(3),RETURN NUMBER,IS,CURSOR,c_emp,IS SELECT,empno,，,sal,FROM,emp,；,v_total_sal,emp.sal,TYPE:=0,；,v_counter,number;,v_emp_no,emp.empno%type,;,BEGIN,FOR,r_emp,IN,c_emp,LOOP,EXIT WHEN,c_emp,ROWCOUNT,v_n,OR,c_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,),；,END LOOP,；,RETURN(v_total_sal,v_counter,),；,END,average_sal,；,/,存储过程与函数的区别,存储过程和函数的主要差别有两个：,一是返回值的方法不同,二是调用方法不同,（,1,）返回值的方法不同,存储函数：,有零个或多个参数，但不能有,OUT,参数。函数只返回一个值，靠,RETURN,子句返回。,存储过程：,有零个或多个参数，过程不返回值，其返回值是靠,OUT,参数带出来的。,（,2,）调用方法不同,调用过程的语句,可以作为单独的可执行语句在,PL/SQL,块中单独出现。,如,：过程名（实际参数,1,，实际参数,2.,）；,函数可以在任何表达式能够出现的地方被调用，,调用函数的语句,不能作为可执行语句单独出现在,PL/SQL,块中。,如,：变量名：,=,函数名（实际参数,1,，实际参数,2.,）,存储过程和函数中的出错处理,存储过程和函数就是一个,PL/SQL,块，所以在过程函数体内应该考虑出错处理。,不管是哪种类型的出错情况，只要在过程和函数体内,考虑了出错处理,的方法，即使在运行过程中出现了错误，过程和函数都能,成功地执行,，程序不会被中断。,如果在过程和函数体内,忽略了出错处理,，过程和函数执行时以交互方式通知用户出错，让用户自行处理，,程序被中断,。,例：,根据给定的员工号，删除该员工记录。,CREATE OR REPLACE PROCEDURE,fire_emp(p_emp_no,IN,emp.empno,TYPE),IS,invalid_employee,EXCEPTION,；（定义错误）,BEGIN,DELETE FROM,emp,WHERE,empno,=,p_emp_no,;,IF SQL,NOTFOUND THEN,RAISE,invalid_employee,；（触发错误）,END IF,；,COMMIT WORK,；,EXCEPTION,WHEN,invalid_employee,THEN,（处理错误）,ROLLBACK WORK,；,INSERT INTO,exception_table(line_nr,line,),VALUES(1,Employee does not exist.,),；,WHEN others THEN,Dbms_output.put_line(sqlerrm,);,END,fire_emp,；,/,存储过程和函数的管理,过程和函数的管理命令,任务,命令,创建一个新的过程或函数,CREATE PROCEDURE,FUNCTION,创建或修改一个已有的过程或函数,CREATE OR REPLACE PROCEDURE,FUNCTION,删除一个已有的过程或函数,DROP PROCEDURE,FUNCTION,例：,删除存储过程,FIRE_EMP,SQL DROP PROCEDURE FIRE_EMP;,查看过程和函数的文档信息,存储的信息,描述,获得的途径,源代码,过程、函数的文本,查看,USER_SOURCE,数据字典,编译代码,编译代码（,p_code,）,无法获得,编译错误,过程、函数的语法错误,查看,USER_ERRORS,数据字典,或用,SHOW ERRORS,命令,SQL show errors;,存储过程和函数的调用和测试,参数传值,一般采用,位置对应法,向形式参数传值，要求实际参数与形式参数保持次序、类型、个数一致。,例：,从,SQL*Plus,命令中通过位置对应法调用,HIRE_EMP,过程。,SQL variable,v_ename,varchar2(12),；,SQL EXECUTE,hire_emp(9999,，,:,v_ename,)
展开阅读全文