oracle视图传多个参数

oracle视图传多个参数

视图本质是执行固定的SQL代码，是无法传参的。需要借助数据库的一个概念–程序包（存储包）来传参，数据库的程序包和java的类的概念很像，书写格式又雷同于C语言。废话不多说，上示例：

• 程序包创建，类比于创建一个java PO实体，包含属性DEALER_ID，MP_CODE和get、set方法。

-- 1. 声明程序包
CREATE OR REPLACE PACKAGE V_PARAM  IS
    -- ID筛选 , -999 表示无效值，即空值
    ID NUMBER:=-999;
    -- 编码筛选, '-999' 表示无效值，即空值
    CODE VARCHAR(20):='-999';
    FUNCTION SET_ID(P_ID NUMBER) RETURN NUMBER;
    FUNCTION GET_ID RETURN NUMBER;
    FUNCTION SET_CODE(P_CODE VARCHAR) RETURN VARCHAR;
    FUNCTION GET_CODE RETURN VARCHAR;
END V_PARAM;
-- 2. 定义程序包
CREATE OR REPLACE PACKAGE BODY V_PARAM  IS
    FUNCTION SET_ID(P_ID IN NUMBER)
        RETURN NUMBER IS
    BEGIN
        IF P_ID IS NOT NULL AND P_ID > 0 THEN
            ID := P_ID;
        ELSE
            -- 设置无效值
            ID := -999;
        END IF;
        RETURN P_ID;
    END SET_ID;
    FUNCTION GET_ID
        RETURN NUMBER IS
    BEGIN
        RETURN ID;
    END GET_ID;
    FUNCTION SET_CODE(P_CODE IN VARCHAR)
        RETURN VARCHAR IS
    BEGIN
        IF P_CODE IS NOT NULL THEN
            CODE := P_CODE;
        ELSE
            -- 设置无效值
            CODE := '-999';
        END IF;
        RETURN P_CODE;
    END SET_CODE;
    FUNCTION GET_CODE
        RETURN VARCHAR IS
    BEGIN
        RETURN CODE;
    END GET_CODE;
END V_PARAM;

• 创建表

CREATE TABLE TAB_CODE (
    ID NUMBER(18,0),
    CODE VARCHAR(20)
);
INSERT INTO TAB_CODE(id,code) VALUES (1,'a');
INSERT INTO TAB_CODE(id,code) VALUES (1,'b');
INSERT INTO TAB_CODE(id,code) VALUES (1,'c');
INSERT INTO TAB_CODE(id,code) VALUES (2,'a');
INSERT INTO TAB_CODE(id,code) VALUES (2,'b');
INSERT INTO TAB_CODE(id,code) VALUES (2,'c');

• 视图创建

CREATE OR REPLACE FORCE VIEW VIEW_ID_CODE AS
SELECT *
FROM TAB_CODE
WHERE 1 = 1
  -- 1.  V_CODE_PARAM.GET_ID()和V_CODE_PARAM.GET_CODE() 参数必须选一个
  AND (CASE
           WHEN V_PARAM.GET_ID() = -999 AND V_PARAM.GET_CODE() = '-999' THEN 0
           ELSE 1 END) =
      (CASE
           WHEN V_PARAM.GET_ID() = -999 AND V_PARAM.GET_CODE() = '-999' THEN 1
           ELSE 1 END)
  -- 2.  V_CODE_PARAM.GET_ID() 参数存在取参数，参数不存在取所有
  AND DECODE(V_PARAM.GET_ID(), -999, 1, ID) =
      DECODE(V_PARAM.GET_ID(), -999, 1, V_PARAM.GET_ID())
  -- 3. V_CODE_PARAM.GET_CODE() 参数存在取参数，参数不存在取所有
  AND DECODE(V_PARAM.GET_CODE(), '-999', '1', CODE) =
      DECODE(V_PARAM.GET_CODE(), '-999', '1', V_PARAM.GET_CODE());

---

查询示例

• 可通过以下代码查询、设置程序包值。一个客户端共享一个程序包数据。

这里有个奇怪的现象：如果不执行b SQL，下面视图设置值不生效。但是我在真实环境是生效的。

-- a
SELECT V_PARAM.GET_ID(),V_PARAM.GET_CODE() FROM dual;
-- b
SELECT V_PARAM.SET_ID(1),V_PARAM.SET_CODE('a') FROM dual;

• 不指定查询条件

SELECT * FROM VIEW_ID_CODE

查询结果为空，因为程序包当前没有参数，会构建参数中的1的WHERE条件，0 = 1，因此查询结果为空。此条件可以拦截不传参时关联查询笛卡尔积查询。视场景筛选
注： 如果查询有值，那么你可能之前设置过程序包的数值了，所以视图使用了你之前设置的程序包值。

• 指定ID查询

SELECT * FROM VIEW_ID_CODE WHERE V_PARAM.SET_ID(1) = 1 AND V_PARAM.SET_CODE('-999') = '-999';

该结果查询出ID = 1的数据。此SQL设置程序包CODE = ‘-999’，视图会构建查询参数：ID=V_PARAM.GET_ID() AND ‘1’ = '1’的条件。即为系统执行的CODE无效，含义就是只传递ID。
在WHERE设置程序包值时，格式如下：

... WHERE V_PARAM.SET_CODE('设置值1') = '设置值1'

注：如果有需要在where中将值设置为NULL，需要使用V_PARAM.SET_CODE(NULL) IS NULL

• 指定CODE查询

SELECT * FROM VIEW_ID_CODE WHERE V_PARAM.SET_ID(-999) = -999 AND V_PARAM.SET_CODE('a') = 'a';

该结果查询出CODE = ‘a’ 的数据。此SQL设置程序包ID = -999，视图会构建查询参数：1 = 1 AND CODE = V_PARAM.GET_CODE()的条件，即为系统执行的ID无效，含义就是只传递CODE。

• 同时传ID，CODE查询

SELECT * FROM VIEW_ID_CODE WHERE V_PARAM.SET_ID(1) = 1 AND V_PARAM.SET_CODE('a') = 'a';

该结果查询出ID=1并且CODE = ‘a’ 的数据。此SQL设置程序包ID = -999，视图会构建查询参数：ID=V_PARAM.GET_ID() AND CODE = V_PARAM.GET_CODE()的条件，即为系统执行的ID无效，含义就是只传递CODE。