oracle教程07plsql高级01

1、 PL/SQLPL/SQL高级编程高级编程 存储过程函数的概念开发一个存储过程或函数目的是把一个 PLSQL块存进数据库中，并在以后重复使用。例如：创建一个记录用户名和当前日期的 无参数过程CREATE PROCEDURE log_execution ISBEGIN 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 IS 后面是一个完整的后面是一个完整的PL/SQLPL/SQL块的三部分（参见第六章），可以定义局部块的三部分（参见第六章），可以定义局部变量、游标等，但不能以变量、游标等，但不能以 DECLAREDECLARE开始。开始。 CREATE OR REPLACE PROCEDURE 过程名 (参数名 IN | OUT | IN OUT 数据类型, )IS | AS 说明部分

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

5、 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 pr

6、ocedure 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 O

7、UT mTYPE)ISBEGIN SELECT ename，sal，comm INTO v_emp_name，v_emp_sal， v_emp_commFROM 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) /*字符型的形式参数不指定长度*/ISBEGIN v_phone_no:SUBSTR (v_phone_no,l,3) |-|SUBSTR (v

8、_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 NUMBERIS v_emp_sal emp.salTYPE:0；BEGIN SELECT sal INTO

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

10、UMBERIS 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

11、_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

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

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

14、oyee does not exist.)； WHEN others THEN Dbms_output.put_line(sqlerrm);END fire_emp；/存储过程和函数的管理 1 过程和函数的管理命令过程和函数的管理命令 例：删除存储过程FIRE_EMPSQL DROP PROCEDURE FIRE_EMP; 查看过程和函数的文档信息查看过程和函数的文档信息 SQL show errors;SQL show errors;存储过程和函数的调用和测试 参数传值一般采用位置对应法向形式参数传值，要求实际参数与形式参数保持次序、类型、个数一致。例：从 SQL*Plus命令中通过位置对应

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

16、环境调用过程和函数的语法和例子 （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

17、块中调用 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_EMPSET serveroutput ON /*激活DBMS_OUTPUT 系统包*/ ACCEPT p_emp_no PROMPT please enter the employee number： /*接受员工号*/VARIABLE v_emp_na

18、me VARCHAR2（14）； /*定义存放OUT参数输出结果的变量*/VARIABLE v_emp_sal NUMBER；VARIABLE v_emp_comm NUMBER；EXECUTE query_emp(&p_emp_no, ：v_emp_name, ：v_emp_sal, ：v_emp_comm);EXECUTE DBMS_OUTPUT.PUT_LINE(Information for employee：| TO_CHAR(&p_emp_no)；EXECUTE DBMS_OUTPUT.PUT_LINE(The name is：| ：v_emp_name)；EXEC

19、UTE DBMS_OUTPUT.PUT_LINE(the salary is：| TO_CHAR(：v_emp_sal)；EXECUTE DBMS_OUTPUT.PUT_LINE(The commission is：| TO_CHAR(：v_emp_comm)； 函数的调用函数不能作为一条语句单独出现，只能出现在别的过程中，作为别的过程的参数。例：调用函数get_salSQL EXECUTE DBMS_OUTPUT.PUT_LINE(员工号7654的工资是：|get_sal(7654);或者用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关键字*