PLSQL高级编程

2、创建存储过程的语法
CREATE [OR REPLACE] PROCEDURE 过程名 [(参数名 [IN | OUT | IN OUT] 数据类型, …)] {IS | AS} [说明部分] BEGIN 语句序列 [EXCEPTION 出错处理] END [过程名]；
注意：IS 后面是一个完整的PL/SQL块的三 部分（参见第六章），可以定义局部变量、 游标等，但不能以 DECLARE开始。
3、创建存储函数的语法 CREATE [OR REPLACE] FUNCTION 函数名 [（参数名 [ IN ] 数据类型 ...）] RETURN 数据类型 {IS | AS} [说明部分] BEGIN 语句序列 RETURN （表达式） [EXCEPTION 例外处理程序] END [函数名]；
例：下面的过程说明了三种模式参数的区别 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;
4、存储过程与函数的区别 存储过程和函数的主要差别有两个： 一是返回值的方法不同 二是调用方法不同 （1）返回值的方法不同 ● 存储函数：有零个或多个参数，但不能 有OUT参数。函数只返回一个值，靠 RETURN子句返回。 ● 存储过程：有零个或多个参数，过程不 返回值，其返回值是靠OUT参数带出来的。
例：根据员工号获取该员工工资的查询
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 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、IN OUT。如果没有为形式参数指定模式，那么缺 省的模式是IN。
类型 描述 -------------------------------------------------------------------IN(缺省)参数 用来从调用环境中向过程传递值 OUT参数 用来从过程中返回值给调用者 IN OUT参数 既可从调用者向过程中传递值， 也可以从过程中返回可能改变了 的值给调用者 局部变量 在过程内部存放值
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； / 例：在SQL*PLUS中调用该过程删除9999号员工 SQL> EXECUTE fire_emp(9999)； PL／SQL procedure successfully completed 即调用过程成功。虽然在EMP表中根本没有员工号 为9999的员工，但过程成功执行，没有被中断。
例：下列存储过程给某一指定的员工涨指定 数量的工资。 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 ; /
5、存储过程和函数中的出错处理 存储过程和函数就是一个PL/SQL块，所以在 过程函数体内应该考虑出错处理。 不管是哪种类型的出错情况，只要在过程和 函数体内考虑了出错处理的方法，即使在运 行过程中出现了错误，过程和函数都能成功 地执行，程序不会被中断。 如果在过程和函数体内忽略了出错处理，过 程和函数执行时以交互方式通知用户出错， 让用户自行处理，程序被中断。
可以在不同的环境调用同一个存储过程。 注意：语法有区别 ● 在 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； /
一、存储过程与应用程序的区别
概念 存储位置 调用地方 相互调用 存储过程 存储在数据库数据字典中 任何数据库工具或应用中 都可以调用 不可以调用应用程序 应用程序 存储在应用程序中 只能在建立程序的 应用中才能调用 可以调用存储过程 存储在当前的应用 中 安全性靠应用程序 保证，如果能执行 应用程序，就能执 行该程序。
第七章：PL/SQL高级编程 第一节 存储过程函数的概念 第二节 包
第三节 触发器
第一节 存储过程函数的概念
开发一个存储过程或函数目的是把一个 PL／ SQL块存进数据库中，并在以后重复使用。 例如：创建一个记录用户名和当前日期的 无参数过程
CREATE PROCEDURE log_execution IS BEGIN INSERT INTO log_table(user_id, log_date) VALUES(user,sysdate)； END； /
三、存储过程和函数的管理
1TE PROCEDURE／ FUNCTION 函数 创建或修改一个已有 CREATE OR REPLACE 的过程或函数 PROCEDURE／FUNCTION
删除一个已有的过程 DROP PROCEDURE／ FUNCTION 或函数
例：编写一个函数，计算几个人的平均工资， 并在函数体的循环过程中输出结果。 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;
建立程序的 存储在数据库的数据字典 文档存储的 中 位置 安全性 由数据库提供安全保证， 必须通过授权才能使用存 储子程序
二、开发存储过程和函数
1、开发一个存储过程和函数的步骤
（1）用文本编辑器编写一个含有Create Procedure或Create Function语句的PL/SQL 脚本文件。（如：c:\procedure1.sql） （2）在 SQL*Plus中用命令（如：SQL>start c:\procedure1.sql;）编译脚本文件，调试编 译错误。系统就将创建过程或函数的源代码存 入数据字典user_source中 。 （3） 编译成功后将编译代码存入数据字典 （4） 调用存储过程和函数，在运行期间调试 存储过程、函数的逻辑错误。
例：利用 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； /
（2）调用方法不同 调用过程的语句可以作为单独的可执行语句 在PL/SQL块中单独出现。 如：过程名（实际参数1，实际参数2...）； 函数可以在任何表达式能够出现的地方被调 用，调用函数的语句不能作为可执行语句单 独出现在PL/SQL块中。 如：变量名：=函数名（实际参数1，实际参 数2...）
SQL> show errors;
四、存储过程和函数的调用和测试
1、参数传值
一般采用位置对应法向形式参数传值，要求 实际参数与形式参数保持次序、类型、个数 一致。 例：从 SQL*Plus命令中通过位置对应法调 用 HIRE_EMP过程。 SQL> variable v_ename varchar2(12)； SQL> EXECUTE hire_emp(9999，:v_ename)；
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； /
例：根据给定的员工号，删除该员工记录。
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；
例：删除存储过程FIRE_EMP SQL> DROP PROCEDURE FIRE_EMP;
2、查看过程和函数的文档信息
存储的信息 描述 获得的途径
源代码
编译代码 编译错误
过程、函数 的文本
查看 USER_SOURCE 数据字典
无法获得 编译代码 （p_code） 过程、函数 的语法错误 查看 USER_ERRORS 数据字典 或用 SHOW ERRORS 命令