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  ISFUNCTION SET_ID(P_ID IN NUMBER)RETURN NUMBER ISBEGINIF P_ID IS NOT NULL AND P_ID > 0 THENID := P_ID;ELSE-- 设置无效值ID := -999;END IF;RETURN P_ID;END SET_ID;FUNCTION GET_IDRETURN NUMBER ISBEGINRETURN ID;END GET_ID;FUNCTION SET_CODE(P_CODE IN VARCHAR)RETURN VARCHAR ISBEGINIF P_CODE IS NOT NULL THENCODE := P_CODE;ELSE-- 设置无效值CODE := '-999';END IF;RETURN P_CODE;END SET_CODE;FUNCTION GET_CODERETURN VARCHAR ISBEGINRETURN 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 (CASEWHEN V_PARAM.GET_ID() = -999 AND V_PARAM.GET_CODE() = '-999' THEN 0ELSE 1 END) =(CASEWHEN V_PARAM.GET_ID() = -999 AND V_PARAM.GET_CODE() = '-999' THEN 1ELSE 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。

oracle视图传多个参数