资源描述
单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,*,PL/SQL高级编程,存储过程函数的概念,开发一个存储过程或函数目的是把一个 PLSQL块存进数据库中，并在以后重复使用。,例如：,创建一个记录用户名和当前日期的,无参数过程,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.empnoTYPE),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.empnoTYPE，,V_emp_name OUT emp.enameTYPE，,v_emp_sal OUT emp.salTYPE，,V_emp_comm OUT mTYPE),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.empnoTYPE),RETURN NUMBER,IS,v_emp_sal emp.salTYPE: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.salTYPE:=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_empNOTFOUND；,v_total_sal:=v_total_sal+r_emp.sal；,v_counter:=c_empROWCOUNT；,v_emp_no:=r_emp.empno；,DBMS_OUTPUT.PUT_LINE(loop=|,v_counter|；Empno=|v_emp_no)；,END LOOP；,RETURN(v_total_salv_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.empnoTYPE),IS,invalid_employee EXCEPTION；（定义错误）,BEGIN,DELETE FROM emp WHERE empno=p_emp_no;,IF SQLNOTFOUND 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；,/,存储过程和函数的管理,过程和函数的管理命令,例：,删除存储过程FIRE_EMP,SQL DROP PROCEDURE FIRE_EMP;,查看过程和函数的文档信息,SQL show errors;,存储过程和函数的调用和测试,参数传值,一般采用位置对应法向形式参数传值，要求实际参数与形式参数保持次序、类型、个数一致。,例：从 SQL*Plus命令中通过位置对应法调用 HIRE_EMP过程。,SQL variable v_ename varchar2(12)；,SQL EXECUTE,hire_emp(9999，:v_ename)；,注意：,如果形式参数是IN模式的参数，实际参数可以是一个具体的值，或一个有值的变量。,如果形式参数是OUT模式的参数，实际参数必须是一个变量。当调用过程后，此变量就被赋值了。,如果形式参数是IN OUT模式的参数，则实际参数必须是一个预先已经赋值的变量。执行完过程后，该变量被重新赋值。,可以调用系统内置的DBMS_OUTPUT包中的过程输出此变量的值，测试过程执行的结果。,存储过程和函数的调用方法,各种环境调用过程和函数的语法和例子,（1）在PL/SQL块中如何调用,过程的调用,例：从 PLSQL块中调用过程QUERY_EMP。执行过程后，输出变量测试结果。,DECLARE,V_empno emp.empno%type:=7654;,V_ename emp.ename%type;,V_sal emp.sal%type;,V_comm m%type;,Begin,Query_emp(v_empno,v_ename,v_sal,v_comm);,Dbms_output.put_line(v_ename|v_sal|,|v_comm);/*测试结果*/,End;,/,函数的调用,例：从一个 PLSQL块中调用 GET_SAL函数。,DECLARE,v_empno NUMBER:7654；,v_sal NUMBER；,BEGIN,v_sal:=get_sal(v_empno)；,DBMS_OUTPUT.PUT_LINE(v_sal)；,/*测试结果*/,END；,/,在SQL*PLUS中如何调用,过程的调用,例：从SQL*PLUS中调用过程QUERY_EMP,SET serveroutput ON /*激活DBMS_OUTPUT
展开阅读全文