Oracle 笔记07

存储过程

存储过程是什么？

存储过程（Stored Procedure ）是一组为了完成特定功能的SQL 语句集，经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。
存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。
存储过程是由流控制和SQL 语句书写的过程，这个过程经编译和优化后存储在数据库服务器中，应用程序使用时只要调用即可。
在Oracle 中，若干个有联系的过程可以组合在一起构成程序包。

存储过程与函数的区别：

存储过程的定义：

-- 定义结构
CREATE [OR REPLACE] PROCEDURE procedure_name
(parameter1_name [mode] datatype [DEFAULT|:=value]...)
AS|IS
    -- 声明区
BEGIN
    -- 执行区
EXCEPTION
    --异常处理区
END [procedure_name];

示例：以部门号为参数，查询部门平均工资，并输出比平均工资高的员工信息

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 sal>v_sal)
        LOOP
        DBMS_OUTPUT.PUT_LINE(v_emp.empno||' '||v_emp.ename);
        END LOOP;
END;
--调用上面的存储过程
BEGIN
    show_emp(20);
END;

存储过程的管理：

-- 修改存储过程
CREATE OR REPLACE PROCEDURE procedure_name
...
-- 重新编译存储过程
ALTER PROCEDURE show_emp COMPILE;
-- 删除存储过程
DROP PROCEDURE show_emp;
-- 查看存储过程源代码
SELECT text FROM user_source
WHERE type='PROCEDURE'
AND name='SHOW_EMP'
ORDER BY line;

包

包是什么？

包（Package）是包含一个或多个子程序单元（过程、函数等）的容器
包分为 数据库内置包 以及 用户创建的包
包由 包规范 和 包体构成

包的构成：

包规范：声明了软件包中所有内容，如过程、函数、游标、类型、异常和变量等，其中过程和函数只包含原型信息，不包含实际代码

包体：包含了在包头的过程和函数的实际代码，还可以额外包括在规范中没有声明的各种信息，这些额外声明的信息是该包体私有的，只能在该包体的作用域中使用

创建包规范：

CREATE OR REPLACE PACKAGE package_name
IS|AS
[PRAGMA SERIALLY_REUSABLE]
    -- 声明、定义区
END [package_name];
-- 注：
-- 元素声明的顺序可以随意，但必须先声明后使用
-- 所有元素是可选的
-- 过程和函数的声明只包括原型，不包括具体实现

示例：

CREATE OR REPLACE PACKAGE pkg_emp
AS
    minsal NUMBER;
    maxsal NUMBER;
    e_beyondbound 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 package_name
IS|AS
[PRAGMA SERIALLY_REUSABLE]
    -- 新定义、实现声明
END [package];
-- 注：
-- 包体中函数的过程和原型必须和包规范中的声明完全一致
-- 只有在包规范已创建的条件下，才可以创建包体
-- 如果包规范中不含函数或过程，则可以不用创建包体

示例：

-- 假设已创建了一个名为my_test_pkg的包规范，内含一个 ↓
-- 名为my_test_procedure，参数为num1 NUMBER 的存储过程
-- 创建包体：
CREATE OR REPLACE PACKAGE BODY my_test_pkg
AS
    PROCEDURE my_test_procedure(num1 NUMBER)
    AS
    BEGIN
        DBMS_OUTPUT.PUT_LINE(num1);
    END my_test_procedure;
END my_test_pkg;

包的调用：

• 在包规范声明的任何元素都是公有的，在包外都是可见的
• 在包体中定义而没在包头中声明的元素是私有的，只能在包体中引用

-- 示例，调用上述 my_test_pkg 包
BEGIN
    my_test_pkg.my_test_procedure(100);
END;

包的初始化过程：

只在包第一次被调用时执行
是一个匿名的PL/SQL块，在包体结构的最后，以BEGIN开始
包在第一次被调用时从磁盘读取到共享池，并在整个会话的持续期间保持
在该过程中，可以自动执行一个初始化过程，对软件包进行实例化

包的管理：

-- 重新编译包规范和包体
ALTER PACKAGE pkg_emp COMPILE;
-- 仅编译包规范
ALTER PACKAGE pkg_emp COMPILE SPECIFICATION;
-- 仅编译包体
ALTER PACKAGE pkg_emp COMPILE BODY;
-- 删除包规范和包体
DROP PACKAGE pkg_emp;
-- 仅删除包体
DROP PACKAGE BODY pkg_emp;
-- 查看包规范的源码
SELECT text FROM user_source
WHERE type='PACKAGE'
AND name='MY_TEST_PKG'
ORDER BY LINE;
--- 查看包体源码
SELECT text FROM user_source
WHERE type='PACKAGE BODY'
AND name='MY_TEST_PKG'
ORDER BY LINE;