1 / 82

第二章：过程和函数

第二章：过程和函数. 在第一章中我们介绍了无名块 ----PL/SQL 块。无名块不存储在数据库中，并且不能从其他的 PL/SQL 块中进行调用。在下面的三章中介绍的结构 ---- 过程、函数、包、触发器 ---- 都是有名块，因此不受这些限制的约束。它们可以存储在数据库中，并且可以在需要的任何地方运行。. CREATE PROCEDURE log_execution IS BEGIN INSERT INTO log_table(user_id, log_date) VALUES(user,sysdate) ； END ； /. 1 ．过程

diane
Download Presentation

第二章：过程和函数

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 第二章：过程和函数 在第一章中我们介绍了无名块----PL/SQL块。无名块不存储在数据库中，并且不能从其他的PL/SQL块中进行调用。在下面的三章中介绍的结构----过程、函数、包、触发器----都是有名块，因此不受这些限制的约束。它们可以存储在数据库中，并且可以在需要的任何地方运行。

  2. CREATE PROCEDURE log_execution IS BEGIN INSERT INTO log_table(user_id, log_date) VALUES(user,sysdate)； END； / 1．过程 开发一个存储过程或函数目的是把一个 PL／SQL块存进数据库中，并在以后重复使用。在SQL*Plus或 SVRMGR注册到一个数据库用户后便可创建过程或函数。 例子： 创建一个无参数过程。记录用户名和当前日期

  3. Form level Trigger name： Pre_Insert Text： BEGIN log_execution； END； SQL＞ EXECUTE log_execution； 例子——在不同的环境调用同一个存储过程。 注意：语法有区别 ● 在 SQL*Plus中记录用户名和当前日期。 ● 在Forms中的 Pre_Insert触发子中记录用户名和当前日期。

  4. CREATE PROCEDUER fire_emp(v_emp_no IN emp.empno％TYPE) IS BEGIN log_execution； DELETE FROM emp WHERE empno=v_emp_no； END； ● 从存储过程 FIRE_EMP中记录用户名和当前日期。

  5. SQL＞SELECT user_id， SUBSTR(TO_CHAR(log_date，’DD/MM/YY HH24:MI:SS’),l,20) log_date FROM log_table； SCOTT 04／08／9216：12：54 JOE 04／08／9217：08：32 SCOTT 04／09／9208：05：34 ● 执行 FIRE_EMP过程，并显示表 log中的内容。 USER_ID LOG_DATE

  6. ⑴ 开发一个存储过程的步骤

  7. 步骤如下： ● 用系统编辑器或字处理软件写一个含有 CREATE PROCEDURE或CREATE FUNCTION语句的脚本文件。 ● 在写 CREATE PROCEDURE／FUNCTION语句时，充 分考虑运行时出错的处理。 ● 在 SQL*Plus或 SVRMGR中运行脚本文件，将过程或函 数的源代码编译成编译代码p_code，并把两种代码都存 储到数据库中。 ● 调试编译错误。 ● 在 ORACLE环境中调用开发的函数或过程。 ● 在运行期间调试过程、函数的逻辑错误。

  8. 注释 ● 通常把创建过程或函数的 CREATE PROCEDURE／FUNCTION存到 一个脚本文 件中，以便在编译或运行出错时加以修改。 ● 不论编译是否成功，创建过程／函数命令 CREATE PROCEDURE／FUNCTION都将自 动把其源代码存入数据库中，而编译代码只 有在源代码编译成功后才能存入数据库中。 ● 只有编译代码被存入数据库的那些过程和函 数才能被调用。

  9. ★ 存储过程与Forms中form级的过程的区别 存储过程 SQL*Forms中的过程 ____________________________________________________ 存储在数据库中 存储在Forms的应用中 其文档存储在数据字典中 相应文档可从Forms的应用中获得 在任何数据库工具及应用中 均可调用 只能在Forms的应用中调用 在数据库的安全控制下可被 应用调用 在forms级安全控制下可被Forms 应用调用 不可以调用Forms的过程 可以调用存储过程

  10. ⑵创建过程的语法 CREATE [OR REPLACE] PROCEDURE [模式名.]过程名 [(参数名 [IN | OUT | IN OUT] 数据类型 …)] {IS | AS} [说明部分] BEGIN 语句序列 [EXCEPTION 例外处理] END [过程名]；

  11. 注释： ● 当创建一个已存在的过程时，指明 REPLACE选项。 ● 关键字 AS和 IS均可，它们本身没有区别。 ● PL／SQL块可以从 BEGIN开始，也可以从 局部变量说明开始。 ● PL／SQL块不能以 DECLARE开始。 ● 用 END或 END加上过程名作为PL／SQL块 的结束标志。

  12. ★ 形式参数可以有三种模式----IN、OUT、IN OUT。如果没有为形式参数指定模式，那么缺省的模式是IN。 类型 描述 ------------------------------------------------------ IN(缺省类型)参数 用来从调用环境中向过程传递值 OUT参数 用来从过程中返回值给调用者 IN OUT参数 既可从调用者向过程中传递值，也可以 从过程中返回可能改变了的值给调用者 局部变量 在过程内部存放值 注释： ● 在变量说明时，可以用％TYPE或％ROWTYPE来指定类 型 ● 一般来讲，除非是把全局包变量作为参数，否则禁止在一 个过程中直接修改它们的值。 ● 全局变量和局部变量的作用范围遵循一般的约定。

  13. 例如：下面例子说明了三种模式参数的区别 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; 错误) p_outparameter:=7; (v_localvariable:=p_outparameter; 错误) v_localvariable:=p_inoutparameter; p_inoutparameter:=7; end; /

  14. 例子——为新雇员存储其所有信息。 CREATE OR REPLACE PROCEDURE hire_emp (v_emp_no IN emp.empno％TYPE， v_emp_name IN emp.ename％TYPE， v_emp_job IN emp.job％TYPE， v_mgr_no IN emp.mgr％TYPE， v_emp_hiredate IN emp.hiredate％TYPE， v_emp_sal IN emp.sal％TYPE， v_emp_comm IN emp.comm％ TYPE， v_dept_no IN emp.deptno％TYPE) IS

  15. BEGIN INSERT INTO emp(empno，ename，job，mgr，hiredate， sal，comm，deptno) VALUES(v_emp_no， v_emp_name，v_emp_job ， v_mgr_no， v_emp_hiredate，v_emp_sal，v_emp_comm ，v_dept_no)； COMMIT WORK； END hire_emp： 创建过程时，尽量减少输入参数的个数，对那些可以在过程体中得到的数据，不要把它们用作输入参数。可减少过程对外界的依赖性。

  16. ★ 去掉不必要的输入参数（可用下列方法） ● 利用数据库序列生成器为主关键字创建序号。 (例如：雇员编号；部门号；订单号) ● 从 USER函数中获取用户名 (例如：销售人员；顾客；审计表使用的用户名) ● 从 SYSDATE函数中获取当前日期 (例如：雇用日期；订货日期；开始日期) ● 在适当时候存储缺省值 (例如：某订单的数量一定是从 l开始) ● 在实际中按照一些常识性知识获取变量的值 (例如：新雇员若不是做销售工作，则其奖金为 NULL； 新雇员的部门号与其老板的部门号一样)

  17. 例子：利用上述方法减少了四个输入参数。 CREATE OR REPLACE PROCEDUER hire_emp (v_emp_name IN emp.ename％TYPE， v_emp_job IN emp.job％TYPE， v_mgr_no IN emp.mgr％TYPE， v_emp_sal IN emp.sal％TYPE) IS V_emp_no emp.empno％TYPE； v_emp_hiredate emp.hiredate％TYPE; V_emp_comm emp.comm％TYPE； v_dept_no emp.deptno％TYPE；

  18. BEGIN SELECT S_empno.nextval INTO V_emp_no FROM dual； V_emp_hiredate:=sysdate； IF v_emp_job='SALESMAN' THEN V_emp_comm:=0； ／* for salesperson *／ ELSE V_emp_comm :＝ NULL；／* NULL for non_salesperson*／ END IF； SEIECT deptno ／* Same department as manager*／ INTO V_dept_no FROM emp WHERE empno= v_mgr_no; INSERT INTO emp(empno，ename，job，mgr，hiredate，sal，comm, deptno) VALUES(v_emp_no， v_emp_name，v_emp_job, v_mgr_no， V_emp_hiredate，v_emp_sal，v_emp_comm，v_dept_no)； END hire_emp；

  19. 例如： Create procedure raise_salary (emp_id integer,v_increase real) is begin update emp set sal= sal + v_increase where eno=emp_id ; end ;

  20. 例子——利用过程进行查询处理, 获取一个雇员的信息。 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 emp.comm％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； 注释： ● 请注意所有输出参数在过程体中的用法，总是出现在 select语句中的 INTO关键字后面，或出现在赋值语句的左边。

  21. 例子: 利用 IN OUT参数在调用者和过程之间传递值 将一个7位数字转换成标准格式的电话号码。 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； 注释： ● 改变 IN OUT参数的值有两种方法：一是用赋值语句，一是通过SELECT...INTO语句 ● 如果 IN OUT参数的值在过程中没有被更改，则它返回到调用环境 时值不变。

  22. 2．函数 创建函数的语法： CREATE [OR REPLACE] FUNCTION [模式名.]函数名 [（参数名 [ IN ] 数据类型 ...）] RETURN 数据类型 {IS | AS} [说明部分] BEGIN 语句序列 （RETURN表达式） [EXCEPTION 例外处理程序] END [函数名]；

  23. 例如：利用函数实现一个查询获取某雇员的工资例如：利用函数实现一个查询获取某雇员的工资 CREATE OR REPLACE FUNCTION get_sal (v_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=v_emp_no; RETURN(v_emp_sal)； END get_sal；

  24. 例如： create or replace function classinfo( p_department classes.department%type, p_course classes.course%type) return varchar2 is v_currentstudents number; v_maxstudents number; v_percentfull number;

  25. begin select current_students,max_students into v_currentstudents,v_maxstudents from classes where department=p_course; v_percentfull:=v_currentstudents/v_maxstudents*100; if v_percentfull=200 then return ‘full’; elsif v_percentfull>80 then return ‘some room’; elsif v_percentfull>60 then return ‘more room’; elsif v_percentfull>0 then return ‘lots of room’; else return ‘empty’; end if; end;

  26. 3. 过程和函数中的异常处理 如果在程序中发生了一个错误，那么就要触发一个异常情况（EXCEPTION）。这种异常处理可能是用户定义的或者是系统预定义的。 异常情况种类 处理方法 后果 预定义 在过程中处理异常情况 执行数据库函数或将其写入 数据库表中 预定义 忽略处理过程 以交互方式让用户自行处理 自定义 在过程中定义好处理过程 执行数据库函数或将其写入 数 据库中 自定义 调用 RAISE_APPLICATION_ 以交互方式通知用户，让 ERROR过程 用户自己处理 注释： ● 预定义的异常情况包括预定义的异常情况和以 EXCEPTION_INIT语句命名的异常情况

  27. CREATE OR REPLACE PROCEDURE hire_emp ... SELECT deptno INTO v_dept_no FROM emp WHERE empno=v_mgr_no； ... EXCEPTION WHERE NO_DATA_FOUND THEN ROLLBACK WORK； INSERT INTO exception_table(line_nr，line) VALUES(l,'Manager is not a valid existing employee.') ； END hire_emp； 例子1：下面是一个处理系统预定义的异常情况的例子。 在该例子中，出现异常情况时执行ROLLBACK，并将该异常情况记录到数据库中

  28. SQL＞EXECUTE hire_emp(‘GREEN’，‘CLERK’，9999，1000)； PL／SOL procedure successfully completed． SQL＞ SELECT line FROM exception_table ORDER BY Iine_nr； Manager is not a valid existing employee． LINE

  29. CREATE OR REPLACE PROCEDURE hire_emp ... SELECT deptno INTO v_dept_no FROM emp WHERE empno=v_mgr_no； ... END hire_emp； 例子2——忽略处理系统预定义的异常情况的例子。 在过程内忽略对一个系统预定义的异常情况的处理，可以使用户交互获得异常情况的信息，并在调用环境里用适当的方法进行处理。

  30. SQL＞EXECUTE hire_emp(‘GREEN’，‘CLERK’，9999，1000)； begin hire_emp(‘GREEN’，‘CLERK’，9999，1000)； end； * ERROR at line1： ORA_06551： PL／SQL：Unhandled exception ORA_06512： at LINE l ORA 06512： at HIRE_EMP Line21 ORA 01403： no data found

  31. 例子3----处理用户定义的异常处理的例子

  32. SQL＞ EXECUTE fire_emp(9999)； PL／SQL procedure successfuIIy completed． SSQL＞ SELECT line FROM exception_table ORDER BY line_nr; Employee does not exist． LINE

  33. Create or replace procedure fire_emp(v_emp_no in emp.empno%type) Is Begin Delete from emp where empno=v_emp_no; If sql%notfound then Raise_application_error(-20020,’employee does not exist’); End if; Commit; End; 例四：在用户定义的异常情况中，利用RAISE_APPLICATION_ERROR过程，可以返回给调用者一个特定的错误信息和出错代码。

  34. SQL>EXECUTE fire_emp(9999); Begin fire_emp(9999);end; * error at line 1; ORA-20020: employee does not exist.

  35. 例如： CREATE PROCEDURE RAISE_SALARY (emp_id INTEGER, INCREASE REAL) IS CURRENT_SALARY REAL; SALARY_MISSING EXCEPTION; BEGIN SELECT SAL INTO CURRENT_SALARY FROM EMP WHERE EMPNO=emp_id; IF CURRENT_SALARY IS NULL THEN RAISE SALARY_MISSING; ELSE UPDATE EMP SET SAL=SAL+INCREASE WHERE EMPNO=emp_id; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO EMP_AUDIT VALUES (emp_id, ‘No such number’); WHEN SALARY_MISSING THEN INSERT INTO EMP_AUDIT VALUES (emp_id, ‘SALARY IS NULL’); END RAISE_SALARY;

  36. 4．过程和函数的管理 首先写好过程或函数的 SQL* Plus脚本文件，运行该文件并将其存人数据库中。可以使用一些 SQL命令完成对已存人数据库中的过程和函数进行管理。 任务 命令 禁止使用的场合 创建一个新的过程 CREATE PROCEDURE／ 该过程或函 或函数 FUNCTION 数已经存在 修改一个已有的过 CREATE OR REPLACE 程或函数 PROCEDURE／FUNCTION 删除一个已有的 DROP PROCEDURE／ 该过程或函数过程或函数 FUNCTION 不存在

  37. 注释： ● 除了将函数或过程存入数据库中外，一般还将其存在一个文件中，以便于修改。 ● 在一个 SQL*Plus脚本文件中，不要忘了在文件的最后以斜杠“／”作为结束标志。

  38. ★ 过程和函数的文档信息 利用交互式的命令或 ORACLE提供的过程，可以从相应数据字典视图和用户的表中获得某过程或函数的有用的文档信息和出错记录。 存储的信息 描述 获得的途径 __________________________________________________ 源代码 过程的文本 查看 USER_SOURCE或 用 DESCRIBE命令 语法分析树 表达式的语法分析 无法获得 编译代码 编译代码 无法获得 编译错误 PL/SQL块的语法错误 用 SHOW ERRORS 命令或查看 USER_ERRORS 运行调试信息 用户对变量或表达式 用 DBMS_ OUTPUT 过程的调试信息

  39. 下面是 USER_SOURCE的结构 列名 列的描述 ____________________________________________________ NAME 实体(过程或函数)名 TYPE 实体类型(PROCEDURE或 FUNCTION) LINE 源代码文本的行号 TEXT 相应行的文本 注释： 也可查看数据字典的 ALL_SOURCE,DBA_SOURCE视图，注意它们有 OWNER 列，标明了实体的拥有者。 在SQL*Plus或 SVRMGR中用 DESCRIBE命令可以查看过程的名字及其参数

  40. 语法：查看过程的说明信息 DESCRIBE procedure_name 例子： 用 DESCRIBE查看 LOG_EXECUTION过程的文档信息。 SQL＞DESCRIBE log_exectuion

  41. 过程的编译出错信息可通过查询数据字典视图USER_ERRORS获得。过程的编译出错信息可通过查询数据字典视图USER_ERRORS获得。 USER_ERRORS视图的结构如下： 列 列描述信息 NAME 过程或函数的名字 TYPE 实体的类型（过程或函数） LINE 出现错误的行号 POSITION 行中出错的具体位置 TEXT 错误信息

  42. SQL＞ COL pos FORMAT a4 SQL＞ COL text FORMAT a60 TRUNC SQL＞ SET space 2 SQL＞ SELECT Iine ||‘/’|| position POS，text FROM user_errors WHERE type=‘PROCEDURE’ AND name= ‘LOG EXECUTION’ ORDER BY line； 通过查询表 USER_ERRORS获得 LOG_EXECUTION过程的语法错误情况。

  43. POS TEXT ---------------------------------------------------------------- 3／10 PLS一00103:Encountered the symbol“INTO” when expecting one 4／7 PLS一00103:Encountered the symbol“VALUES” when expecting 4／27 PLS一00103:Encountered the symbol“；” when expecting one

  44. 5．过程和函数的调用 过程和函数一旦被存人数据库，就可从各种 ORACLE环境中(既可以通过命令行方式， 也可以从一个具体应用的代码中)调用它，在命令行方式下要跟入相应的参数。

  45. 调用环境 语法 参数形式 ________________________________________________________ SQL*Plus EXECUTE命令 SQL* Plus的参数替换 (只能使用 IN变量) SVRMGR EXECUTE命令 Forms 直接调用 Forms中的字段 PL／SQL块 直接调用 PL／SQL的局部变量 另外的存储过程 直接调用 PL／SQL的局部变量 或函数 PL／SQL全局包变量 预编译程序 EXEC SQL语法 宿主变量 PL／SQL块 PL／SQL局部变量 ORACLE调用接口 OSQ13语法 宿主变量 (OCI) PL／SQL块 PL／SQL局部变量

  46. 注释： ● 在调用环境中，除变量外，可以用常量代替 IN变量。 ● 用常数或 SQL* Plus的实参做为参数时，参 数的类型只能是IN，不能是 OUT和IN OUT。

  47. 函数与过程的调用方法相同，需要注意的是，函数是要返回值的。函数与过程的调用方法相同，需要注意的是，函数是要返回值的。 结构名 返回值 用法 __________________________________________________ 过程 无 出现在一个完整的可执行语句中 函数 返回一个值 可以在语句中代替变量或表达式 的位置

  48. 例子——从一个 PL／SQL块中调用 GET_SAL函数 DECLARE v_empno NUMBER:＝7654； v_sal NUMBER； BEGIN v_sal:=get_sal(v_empno)； END； ／ 在 SQL* Plus或 SVRMGR中用 EXECUTE命令调用过程。

  49. ACCEPT p_empno PROMPT ‘PIease enter the employee number：’ EXECUTE fire_emp(&p_empno)； SQL＞ EXECUTE scott.fire_emp(7654)； 例子——从 SQL* Plus中调用过程 FIRE_EMP 例子——在一个用户中调用另一个用户的过程。 从 SQL* Plus中调用用户 scott的 FIRE_EMP过程

More Related