Oracle 笔记07

发布于 2020-07-01  253 次阅读


存储过程

存储过程是什么?

存储过程(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;


何为BS?B-big,S-shui。