.Net程序员学用Oracle系列(7)：视图、函数、过程、包

《.Net程序员学用Oracle系列：导航目录》

本文大纲

• 1、视图
  • 1.1、创建视图
• 2、函数
  • 2.1、创建函数
  • 2.2、调用函数
• 3、过程
  • 3.1、创建过程
  • 3.2、调用过程
• 4、包
  • 4.1、创建包/包体
  • 4.2、调用包/包体
• 5、总结
  • 5.1、删除数据库对象
  • 5.2、编译数据库对象

除表以外，Oracle 还提供了视图、函数、存储过程、包/包体、序列、触发器、作业等对象，以满足数据库业务需要。对大多数应用程序而言，这些对象都只会少量用用，本文也只介绍创建和删除这些对象的基本 SQL 语法，不会深入探讨这些对象的详细语法和具体应用。

视图

创建视图

实际操作过程中，我发现 Oracle 视图有一个问题，就是不大方便注释，每次写好的注释执行之后再打开视图定义所有注释就全都没了。后来我发现把注释写到末尾就不会被清除，但这样总感觉乖乖的，而且我没见谁这么用过，我也很少用，目前还不知道有没有其它问题。创建视图示例：

CREATE OR REPLACE VIEW v_staff2 AS
SELECT t1.staff_id,t1.staff_name,t1.dept_code,t2.enum_name dept_name,t1.gender,
       t1.birthday,EXTRACT(YEAR FROM SYSDATE)-EXTRACT(YEAR FROM t1.birthday) age,
       t1.edu_bg,t1.base_salary,t1.post_salary,base_salary+post_salary fixed_salary
FROM demo.t_staff t1
LEFT JOIN demo.t_field_enum t2 ON t1.dept_code=t2.enum_code AND t2.field_code='DEPT'
WHERE t1.is_disabled=0
-- 在职员工档案视图
;

查询视图的方法与查询表的方法完全相同，就不再赘述。

函数

创建函数

CREATE OR REPLACE FUNCTION fn_today2
RETURN DATE IS
  v_today DATE;
-- 返回当前日期（为了方便演示，假定当前日期始终为 fn_today）
BEGIN
  v_today:=TO_DATE('2017-01-10','yyyy-mm-dd');
  RETURN v_today;
END;

调用函数

在 Oracle 中调用函数，大概有三种方式。如调用函数 fn_today，示例：

-- 方式一
SELECT fn_today res FROM DUAL; -- res：2017-01-10
-- 方式二
BEGIN
  DBMS_OUTPUT.PUT_LINE('res：'||TO_CHAR(fn_today,'yyyy-mm-dd')); -- res：2017-01-10
END;
-- 方式三
DECLARE
  v_today DATE;
BEGIN
  v_today:=fn_today;
  DBMS_OUTPUT.PUT_LINE('res：'||TO_CHAR(fn_today,'yyyy-mm-dd')); -- res：2017-01-10
END;

过程

创建过程

创建一个带自制事物过程，示例：

CREATE OR REPLACE PROCEDURE sp_sync_staff90
AS
  v_sql VARCHAR2(200); -- SQL语句
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  v_sql:='TRUNCATE TABLE t_staff_young'; -- 清空 90 后职员表
  EXECUTE IMMEDIATE v_sql;           -- PLSQL 中不能直接执行 DDL 语句
  COMMIT;

  INSERT INTO t_staff_young
  SELECT t1.staff_id,t1.staff_name,t1.dept_code,t1.gender
  FROM t_staff t1
  WHERE t1.birthday>=TO_DATE('1990-01-01','yyyy-mm-dd');
  COMMIT;
END;

创建一个带返回值过程，示例：

CREATE OR REPLACE PROCEDURE sp_staff_status
(
  p_staff_id NUMBER, -- 职员ID
  p_result OUT VARCHAR2 -- 返回职员状态信息
)
AS
  v_staff_status NUMBER(1);
BEGIN
  IF (p_staff_id IS NULL OR p_staff_id<0) THEN
    p_result:='查无此员工！';
  ELSE
    SELECT t.is_disabled INTO v_staff_status FROM demo.t_staff t WHERE t.staff_id=p_staff_id;
  END IF;

  -- 如果用户没有对应权限则给出具体提示
  IF v_staff_status=0 THEN
    p_result:='该员工在职！';
  ELSE
    p_result:='该员工已离职！';
  END IF;
END;

调用过程

在 PLSQL 的 SQL 窗口中调用无参过程的示例：

BEGIN
  sp_sync_staff90; -- 这里必须加分号，否则有语法错误
END;

在 PLSQL 的 SQL 窗口中调用有参数过程的示例：

DECLARE res VARCHAR2(100);
BEGIN
  sp_staff_status(2,res);
  DBMS_OUTPUT.PUT_LINE('res：'||res); -- res：该员工在职！
END;

包

我有到网上去查询 Oracle 中包的用途，结论是：它就像一个容器，可以将一组逻辑相关过程、函数、变量、常量和游标等 PLSQL 程序设计元素放到一起。包由包规范和包体两个部分组成，包规范用于定义公用的常量、变量、过程和函数，包体用户存放过程和函数的定义。

关于包的好处，网上大多是从程序模块化管理的角度来阐述的，比如说方便查询和维护过程和函数等等。我本人从未系统学过 Oracle，原本也不知道 Oracle 中包的存在，后来为写一个触发器来实现在一个批量操作（可能是新增、修改或删除）之后，同时更新另一个表中两个不同字段，更新的条件也不同，条件里需要对第一个表中的数据做聚合操作。如果我没记错的话，应该是添加可以更新，修改和删除不可以更新，如果更新语句执行之后立即提交，语法上又通不过（后来我分析这本身也是矛盾的），总之怎么写都不对。我告诉经理这条路可能走不通，然后把我的分析给他讲了一下，他也觉得我说的有道理。接下来我们开始查资料、找解决方案，经理先找到包变量的用法，并做了个测试认为可行，于是我也照模画样，把已经删掉或改过的数据外键保存到包变量中，再到触发器中去取，发现果然可行，我也这么偶然的接触到 Oracle 中的包。

创建包/包体

创建一个包含包变量的包规范，示例：

CREATE OR REPLACE PACKAGE pkg_staff AS -- 这里还可以用 IS 代替 AS
  staff_id NUMBER(10);     -- 包变量，职员ID
  staff_name VARCHAR2(20); -- 包变量，职员名称
END;

创建一个包含函数的包规范，示例：

CREATE OR REPLACE PACKAGE pkg_case AS
  FUNCTION fn_today RETURN DATE; -- 定义函数 fn_today
END;

创建包规范 pkg_case 的包体，示例：

CREATE OR REPLACE PACKAGE BODY pkg_case AS
  FUNCTION fn_today
  RETURN DATE IS
    v_today DATE;
  -- 返回当前日期（为了方便演示，假定当前日期始终为 fn_today）
  BEGIN
    v_today:=TO_DATE('2017-01-10','yyyy-mm-dd');
    RETURN v_today;
  END;
END;

注意：必须先创建包规范，然后再创建包体。

调用包/包体

调用包里的数据库对象与调用普通的数据库对象方式类似，唯一的区别就是要带上包名前缀（包名.包成员名），调用 pkg_case 包里的 fn_today 函数，示例：

SELECT pkg_case.fn_today FROM DUAL; -- res:2017-01-10

总结

删除数据库对象

删除视图、函数、过程、包等数据库对象的语法相似，示例：

DROP VIEW v_staff2;
DROP FUNCTION fn_today2;
DROP PROCEDURE sp_sync_staff90;
DROP PACKAGE [BODY] pkg_staff;

编译数据库对象

视图、函数、过程、包等数据库对象依赖于基础表的存在而存在，当基础表进行了结构上的修改后，有可能会对这些数据库对象产生影响，有时候这些对象甚至会直接挂掉（在 PLSQL Developer 里看的话，对象名左上角会有一个红色的星号），所以需要对相关数据库对象重新进行编译。

只要拥有 ALTER ANY PROCEDURE 权限，就可以编译视图、函数、过程、包等数据库对象，编译语法大致相似，示例示例

ALTER VIEW v_staff COMPILE;                    -- 编译 v_staff 视图
ALTER FUNCTION fn_today COMPILE;               -- 编译 fn_today 函数
ALTER PROCEDURE sp_staff_status COMPILE;       -- 编译 sp_staff_status 过程
ALTER PACKAGE pkg_case COMPILE [PACKAGE|BODY]; -- 编译 pkg_case 包

有时候挂掉的过程太多，如果挨个手动编译效率太低，可以用下面这个过程实现批量编译。

CREATE OR REPLACE PROCEDURE compile_invalid_views(
  p_owner VARCHAR2 -- 所有者（schema）名称
)
-- 功能：批量编译某个用户下所有状态无效的过程
AS
  v_sql VARCHAR2(400);
BEGIN
  FOR invalid_procs IN(
    SELECT t.object_name FROM all_objects t
    WHERE t.status='INVALID' AND t.object_type='PROCEDURE' AND t.owner=UPPER(p_owner) -- 这里不能加分号，加了会有语法错误
  ) LOOP
    v_sql:='ALTER PROCEDURE '||invalid_procs.object_name||' COMPILE';

    BEGIN
      EXECUTE IMMEDIATE v_sql;
      EXCEPTION
        WHEN OTHERS THEN
          dbms_output.put_line(SQLERRM);
    END;
  END LOOP;
END;

如果要编译其它类型对象，只需把过程中的'PROCEDURE'换成对应类型即可。

《.Net程序员学用Oracle系列：导航目录》

本文声明：如果您认为这篇文章还可以或对您有帮助，请点击文章末尾的“推荐”按钮。欢迎转载、演绎或用于商业目的，但必须保留本文的署名韩宗泽，并且要在明显位置给出原文链接！本人初写博客，水平有限，若有不当之处，敬请批评指正，谢谢！