oracle存储子程序、存储过程、视图创建、使用.ppt

存储子程序是被命名的PL/SQL块，以编译的形式存储在数据库服务器中，可以在应用程序中进行调用，是PL/SQL程序模块化的一种体现。 PL/SQL中的存储子程序包括存储过程和（存储）函数两种。 存储子程序是以独立对象的形式存储在数据库服务器中，因此是一种全局结构，与之对应的是局部子程序，即嵌套在PL/SQL块中的局部过程和函数，其存储位置取决于其所在的父块的位置。 16.6.1 存储过程 存储过程的创建 存储过程的调用 存储过程的管理 （1）存储过程的创建 基本语法 CREATE [OR REPLACE] PROCEDURE procedure_name (parameter1_name [mode] datatype [DEFAULT|:=value] [, parameter2_name [mode] datatype [DEFAULT|:=value],…]) AS|IS /*Declarative section is here */ BEGIN /*Executable section is here*/ EXCEPTION /*Exception section is here*/ END[procedure_name]; 参数说明 参数的模式 IN（默认参数模式）表示当过程被调用时，实参值被传递给形参；在过程内，形参起常量作用，只能读该参数，而不能修改该参数；当子程序调用结束返回调用环境时，实参没有被改变。IN模式参数可以是常量或表达式。 OUT表示当过程被调用时，实参值被忽略；在过程内，形参起未初始化的PL/SQL变量的作用，初始值为NULL，可以进行读/写操作；当子程序调用结束后返回调用环境时，形参值被赋给实参。OUT模式参数只能是变量，不能是常量或表达式。 IN OUT表示当过程被调用时，实参值被传递给形参；在过程内，形参起已初始化的PL/SQL变量的作用，可读可写；当子程序调用结束返回调用环境时，形参值被赋给实参。IN OUT模式参数只能是变量，不能是常量或表达式。 参数的限制 在声明形参时，不能定义形参的长度或精度、刻度，它们是作为参数传递机制的一部分被传递的，是由实参决定的。 参数传递方式 当子程序被调用时，实参与形参之间值的传递方式取决于参数的模式。IN参数为引用传递，即实参的指针被传递给形参；OUT，IN OUT参数为值传递，即实参的值被复制给形参。 参数默认值 可以为参数设置默认值，这样存储过程被调用时如果没有给该参数传递值，则采用默认值。需要注意，有默认值的参数应该放在参数列表的最后。 创建一个存储过程，以部门号为参数，查询该部门的平均工资，并输出该部门中比平均工资高的员工号、员工名。 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(p_deptno|| ||average salary is:|| v_sal); FOR v_emp IN (SELECT * FROM emp 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 doesn’’t exists!); END show_emp; 通常，存储过程不需要返回值，如果需要返回一个值可以通过函数调用实现。但是，如果希望返回多个值，可以使用OUT或IN OUT模式参数来实现。 创建一个存储过程，以部门号为参数，返回该部门的人数和最高工资。 CREATE OR REPLACE PROCEDURE return_deptinfo( p_deptno emp.deptno%TYPE, p_avgsal OUT emp.sal%TYPE, p_count OUT emp.sal%TYPE) AS BEGIN SELECT avg(sal),c