存储过程和函数

1、存储过程、函数存储过程、函数 主要内容主要内容 l存储过程 l函数 l包 存储子程序存储子程序 l存储子程序是被命名的PL/SQL块，以编译的形 式存储在数据库服务器中，可以在应用程序中 进行调用，是PL/SQL程序模块化的一种体现。 lPL/SQL中的存储子程序包括存储过程和（存储） 函数两种。 创建存储过程创建存储过程 l图形工具OEM创建 lCREATE OR REPLACE PROCEDURE 过程名 ( 参数名 in | out | in out 数据类型 :=初始值, 参数名 in | out | in out 数据类型 :=初始值, 参数名 in | out | in out 数

2、据类型 :=初始值 ) IS | AS BEGIN EXCEPTION END ; 参数的模式参数的模式 lIN（默认参数模式）表示当过程被调用时，实参值被传递给 形参；在过程内，形参起常量作用，只能读该参数，而不能 修改该参数。 lOUT：在过程内，形参起未初始化的PL/SQL变量的作用，初 始值为NULL，可以进行读/写操作。 lIN OUT表示当过程被调用时，实参值被传递给形参；在过程 内，形参起已初始化的PL/SQL变量的作用，可读可写。 l参数的限制 l在声明形参时，不能定义形参的长度或精度、刻度，它 们是作为参数传递机制的一部分被传递的，是由实参决 定的。 l参数传递方式 l当子程

3、序被调用时，实参与形参之间值的传递方式取决 于参数的模式。IN参数为引用传递，即实参的指针被传 递给形参；OUT，IN OUT参数为值传递，即实参的值被 复制给形参。 l参数默认值 l可以为参数设置默认值，这样存储过程被调用时如果没 有给该参数传递值，则采用默认值。需要注意，有默认 值的参数应该放在参数列表的最后。 简单存储过程简单存储过程 Create or replace procedure p_hello As Begin dbms_output.put_line(hello world!); End; Create or replace procedure show_emp As v_

4、sal emp.sal%type; begin SELECT avg(sal) INTO v_sal FROM emp WHERE deptno=10; DBMS_OUTPUT.PUT_LINE(|average salary is:| v_sal); FOR v_emp IN (SELECT * FROM emp WHERE deptno=10 AND salv_sal) LOOP DBMS_OUTPUT.PUT_LINE(v_emp.empno| | v_emp.ename); END LOOP; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.

5、PUT_LINE(The department doesnt exists!); End; 带输入参数的存储过程带输入参数的存储过程 Create or replace procedure show_emp(p_deptno emp.deptno%type) As v_sal emp.sal%type; begin SELECT avg(sal) INTO v_sal FROM emp WHERE deptno=p_deptno; DBMS_OUTPUT.PUT_LINE(|average salary is:| v_sal); FOR v_emp IN (SELECT * FROM emp

6、WHERE deptno=p_deptno AND salv_sal) LOOP DBMS_OUTPUT.PUT_LINE(v_emp.empno| | v_emp.ename); END LOOP; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(The department doesnt exists!); End; 带输出参数的过程带输出参数的过程 CREATE OR REPLACE PROCEDURE return_deptinfo( p_deptno emp.deptno%TYPE, p_avgsal OUT emp.sa

7、l%TYPE, p_count OUT emp.sal%TYPE) AS BEGIN SELECT avg(sal),count(*) INTO p_avgsal,p_count FROM emp WHERE deptno=p_deptno; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(The department dont exists!); END; 存储过程的调用存储过程的调用 l在sqlplus中调用： execute 过程名(参数1,参数2,) l在程序中调用： 过程名(参数1,参数2,); DECLARE v_avg

8、sal emp.sal%TYPE; v_count NUMBER; BEGIN show_emp(20); return_deptinfo(10,v_avgsal,v_count); DBMS_OUTPUT.PUT_LINE(v_avgsal| | v_count); END; 存储过程的管理存储过程的管理 l修改存储过程 lCREATE OR REPLACE PROCEDURE procedure_name l查看存储过程及其源代码 l查询数据字典视图USER_SOURCE lSELECT name,text FROM user_source WHERE type=PROCEDURE; l重

9、新编译存储过程 lALTER PROCEDURECOMPILE lALTER PROCEDURE show_emp COMPILE; l删除存储过程 lDROP PROCEDURE lDROP PROCEDURE show_emp; 创建函数创建函数 l图形工具OEM创建 lCREATE OR REPLACE FUNCTION 函数名 ( 参数名 in | out | in out 数据类型 :=初始值, 参数名 in | out | in out 数据类型 :=初始值, 参数名 in | out | in out 数据类型 :=初始值 ) RETURN 返回值数据类型 IS | AS BEG

10、IN RETURN 返回值; EXCEPTION END ; l注意 l在函数定义的头部，参数列表之后，必须包含 一个RETURN语句来指明函数返回值的类型， 但不能约束返回值的长度、精度、刻度等。 l在函数体的定义中，必须至少包含一个 RETURN 语句，来指明函数返回值。也可以有 多个RETURN语句，但最终只有一个RETURN语 句被执行。 函数举例：函数举例： CREATE OR REPLACE FUNCTION return_maxsal (p_deptno emp.deptno%TYPE) RETURN emp.sal%TYPE AS v_maxsal emp.sal%TYPE;

11、BEGIN SELECT max(sal) INTO v_maxsal FROM emp WHERE deptno=p_deptno; RETURN v_maxsal; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(The deptno is invalid!); END; 如果需要函数返回多个值，可以使用如果需要函数返回多个值，可以使用OUTOUT或或IN IN OUTOUT模式参数模式参数 CREATE OR REPLACE FUNCTION ret_deptinfo( p_deptno dept.deptno%TYPE, p

12、_num OUT NUMBER, p_avg OUT NUMBER) RETURN dept.dname%TYPE AS v_dname dept.dname%TYPE; BEGIN SELECT dname INTO v_dname FROM dept WHERE deptno=p_deptno; SELECT count(*),avg(sal) INTO p_num,p_avg FROM emp WHERE deptno=p_deptno; RETURN v_dname; END; 函数的调用函数的调用 l在SQL语句中调用函数 l在PL/SQL中调用函数 l注意 l函数只能作为表达式的一

13、部分被调用。 l示例 l通过return_maxsal函数的调用，输出各个部门的最高工资； 通过ret_deptinfo函数调用，输出各个部门名、部门人数及 平均工资。 DECLARE v_maxsal emp.sal%TYPE; v_avgsal emp.sal%TYPE; v_num NUMBER; v_dname dept.dname%TYPE; BEGIN FOR v_dept IN (SELECT DISTINCT deptno FROM emp) LOOP v_maxsal:=ret_maxsal(v_dept.deptno); v_dname:=ret_deptinfo(v_de

14、pt.deptno,v_num,v_avgsal); DBMS_OUTPUT.PUT_LINE(v_dname| |v_maxsal| | v_avgsal| |v_num); END LOOP; END; l函数可以在SQL语句的以下部分调用： lSELECT语句的目标列； lWHERE和HAVING子句； lCONNECT BY，START WITH，ORDER BY，GROUP BY子 句； lINSERT语句的VALUES子句中； lUPDATE语句的SET子句中。 l如果要在SQL中调用函数，那么函数必须符合下列限 制和要求： l在SELECT语句中的函数不能修改（INSERT，UP

15、DATE， DELETE）调用函数的SQL语句中使用的表； l函数在一个远程或并行操作中使用时，不能读/写封装变量； l函数必须是一个存储数据库对象（或存储在包中）； l函数的参数只能使用IN模式； l形式参数类型必须使用数据库数据类型； l返回的数据类型必须是数据库数据类型； 函数的管理函数的管理 l函数的修改 lCREATE OR REPLACE FUNCTION function_name l查看函数及其源代码 l查询数据字典视图USER_SOURCE lSELECT name,text FROM user_source WHERE type=FUNCTION; l函数重编译 lALTE

16、R FUNCTIONCOMPILE lALTER FUNCTION return_maxsal COMPILE; l删除函数 lDROP FUNCTION lDROP FUNCTION return_maxsal; 注意：注意： l函数的调用必须作为表达式的一部分出现，不 能作为单独的语句出现 l过程的调用必须作为独立的语句出现 包包 l包是包含一个或多个子程序单元（过程、函数等）的 容器，可将一些有联系的对象放置在其内部，构成一可将一些有联系的对象放置在其内部，构成一 个个逻辑分组逻辑分组，这些对象包括存储过程、函数、游标、，这些对象包括存储过程、函数、游标、 自定义的类型（例如自定义的类型

17、（例如PL/SQLPL/SQL表和记录）和变量等表和记录）和变量等。 l包是一种全局结构 。 l包类型 l数据库内置包 l用户创建的包 l包构成 l说明部分(包头) l包体 包头包头 CREATE OR REPLACE PACKAGE IS | AS END ; l注意： l元素声明的顺序可以是任意的，但必须先声明 后使用； l所有元素是可选的； l过程和函数的声明只包括原型，不包括具体实 现。 l创建一个包，包括2个变量、2个过程和1个异常。 CREATE OR REPLACE PACKAGE pkg_emp AS minsal NUMBER; maxsal NUMBER; e_beyond

18、bound EXCEPTION; PROCEDURE update_sal( p_empno NUMBER, p_sal NUMBER); PROCEDURE add_employee( p_empno NUMBER,p_sal NUMBER); END pkg_emp; 包体包体 CREATE OR REPLACE PACKAGE BODY IS | AS END ; l注意： l包体中函数和过程的原型必须与包规范中的声明完 全一致； l只有在包规范已经创建的条件下，才可以创建包体； l如果包规范中不包含任何函数或过程，则可以不创 建包体。 包的调用包的调用 l在包规范声明的任何元素是公有的

19、，在包外都是可见 的 l包外：通过包名.元素名形式调用； l包内：直接通过元素名进行调用。 l在包体中定义而没有在包头中声明的元素是私有的， 只能在包体中引用 包重载包重载 l重载子程序必须同名不同参，即名称相同，参数不同。参数不同体现 为参数的个数、顺序、类型等不同。 l如果两个子程序参数只是名称和模式不同，则不能重载。 lPROCEDURE overloadme(parameter1 IN NUMBER); lPROCEDURE overloadme(parameter2 OUT NUMBER); l不能根据两个函数返回类型不同而对它们进行重载。 lFUNCTION overloadme RETURN DATE; lFUNCTION overloadme RETURN NUMBER; l重载子