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。