oracle教程07plsql高级01.ppt

1、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中记录用户名和当前日期

2、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;）编译脚本文件，调

3、试编译错误。系统就将创建过程或函数的源代码存入数据字典user_source中 。 （3） 编译成功后将编译代码存入数据字典 （4） 调用存储过程和函数，在运行期间调试存储过程、函数的逻辑错误。,创建存储过程的语法,注意：IS 后面是一个完整的PL/SQL块的三部分（参见第六章），可以定义局部变量、游标等，但不能以 DECLARE开始。,CREATE OR REPLACE PROCEDURE 过程名 (参数名 IN | OUT | IN OUT 数据类型, ) IS | AS 说明部分 BEGIN 语句序列 EXCEPTION 出错处理 END 过程名；, 形式参数可以有三种模式-IN、OUT

4、、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_localva

5、riable 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_i

6、ncrease 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，

7、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_das

8、h； /,创建存储函数的语法,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

9、 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 CURS

10、OR 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=

11、| 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.）；

12、函数可以在任何表达式能够出现的地方被调用，调用函数的语句不能作为可执行语句单独出现在PL/SQL块中。 如：变量名：=函数名（实际参数1，实际参数2.）,存储过程和函数中的出错处理,存储过程和函数就是一个PL/SQL块，所以在过程函数体内应该考虑出错处理。 不管是哪种类型的出错情况，只要在过程和函数体内考虑了出错处理的方法，即使在运行过程中出现了错误，过程和函数都能成功地执行，程序不会被中断。 如果在过程和函数体内忽略了出错处理，过程和函数执行时以交互方式通知用户出错，让用户自行处理，程序被中断。,例：根据给定的员工号，删除该员工记录。 CREATE OR REPLACE PROCEDURE

13、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,

14、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

15、v_ename varchar2(12)； SQL EXECUTE hire_emp(9999，:v_ename)；,注意： 如果形式参数是IN模式的参数，实际参数可以是一个具体的值，或一个有值的变量。 如果形式参数是OUT模式的参数，实际参数必须是一个变量。当调用过程后，此变量就被赋值了。 如果形式参数是IN OUT模式的参数，则实际参数必须是一个预先已经赋值的变量。执行完过程后，该变量被重新赋值。 可以调用系统内置的DBMS_OUTPUT包中的过程输出此变量的值，测试过程执行的结果。,存储过程和函数的调用方法,各种环境调用过程和函数的语法和例子,（1） 在PL/SQL块中如何调用 过程的调

16、用 例：从 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

17、 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 系统包*/ ACCEPT p_emp_no PROMPT please enter the employee number： /*接受员工号*/ VARIABLE v_emp_name VARCHAR2（14）； /*定义存

18、放OUT参数输出结果的变量*/ VARIABLE v_emp_sal NUMBER； VARIABLE v_emp_comm NUMBER； EXECUTE query_emp( EXECUTE DBMS_OUTPUT.PUT_LINE(Information for employee：| TO_CHAR( 或者用SELECT语句查询函数的结果。 SQL SELECT GET_SAL(7654) FROM DUAL；,在PRO*C中如何调用过程,例：从一个 pro* C程序中调用过程 FIRE_EMP。 void run_fire_emp() EXEC SQL BEGIN DECLARE SECTION； int empno； EXEC SQL END DECLARE SECTION ； printf(nPlease enter the employee number：) ； scanf(d，&empno)； EXEC SQL fire_emp(:empno)； /*在过程名前面加EXEC SQL关键字*/ return； ,存储过程和函数的安全性,存储过程、函数及包是数据字典中的对象，因此它们有特