Oracle 视图传参详解（通过 全局变量 实现）

1 结论

通过 package 中的 '全局变量' 来实现
(1) 设置 set()
(2) 获取 get()

2 查询演示

-- 查询 聘用日期范围 内的员工信息
select *
  from scott.vw_emp t
 where scott.pkg_view_param.set_start_date('1980-12-17') = 'OK'
   and scott.pkg_view_param.set_end_date('1981-4-2') = 'OK';

查询结果：

与以下同等：(视图查询时，不用关注 ‘hiredate’ 这个字段)

select * 
  from scott.emp t
 where t.hiredate >= date '1980-12-17'
   and t.hiredate < date '1981-04-02' + 1;

3 源码

3.1 创建 view

-- 日期参数视图，字符、数值 参数同理
CREATE OR REPLACE VIEW scott.vw_emp AS
SELECT e.empno, e.ename, e.job, e.hiredate
  FROM scott.emp e
 WHERE e.hiredate >= scott.pkg_view_param.get_start_date()
   AND e.hiredate < scott.pkg_view_param.get_end_date() + 1;

3.2 创建 package

package head：

CREATE OR REPLACE PACKAGE scott.pkg_view_param IS
   FUNCTION set_start_date(i_start_date VARCHAR2) RETURN VARCHAR2;
   FUNCTION set_end_date(i_end_date VARCHAR2) RETURN VARCHAR2;
   FUNCTION set_id(i_id NUMBER) RETURN VARCHAR2;
   FUNCTION set_name(i_name VARCHAR2) RETURN VARCHAR2;

   FUNCTION get_start_date RETURN DATE;
   FUNCTION get_end_date RETURN DATE;
   FUNCTION get_id RETURN NUMBER;
   FUNCTION get_name RETURN VARCHAR2;
END pkg_view_param;
/

package body：

CREATE OR REPLACE PACKAGE BODY scott.pkg_view_param IS
   -- 全局变量
   g_start_date DATE;
   g_end_date   DATE;
   g_id         NUMBER;
   g_name       VARCHAR2(100);

   -- 时间参数
   FUNCTION set_start_date(i_start_date VARCHAR2) RETURN VARCHAR2 IS
   BEGIN
      g_start_date := to_date(i_start_date, 'YYYY-MM-DD');
      RETURN 'OK'; -- 自定义 or RETURN i_start_date   
   END set_start_date;

   FUNCTION set_end_date(i_end_date VARCHAR2) RETURN VARCHAR2 IS
   BEGIN
      g_end_date := to_date(i_end_date, 'YYYY-MM-DD');
      RETURN 'OK';
   END set_end_date;

   -- 数值参数
   FUNCTION set_id(i_id NUMBER) RETURN VARCHAR2 IS
   BEGIN
      g_id := i_id;
      RETURN 'OK';
   END set_id;

   -- 字符参数
   FUNCTION set_name(i_name VARCHAR2) RETURN VARCHAR2 IS
   BEGIN
      g_name := i_name;
      RETURN 'OK';
   END;

   ----- get() -----
   FUNCTION get_start_date RETURN DATE IS
   BEGIN
      RETURN g_start_date;
   END get_start_date;

   FUNCTION get_end_date RETURN DATE IS
   BEGIN
      RETURN g_end_date;
   END get_end_date;

   FUNCTION get_id RETURN NUMBER IS
   BEGIN
      RETURN g_id;
   END get_id;

   FUNCTION get_name RETURN VARCHAR2 IS
   BEGIN
      RETURN g_name;
   END get_name;
END pkg_view_param;
/

3.3 emp 表

• Oracle 内置表，如下截图：