sql server 视图的字段 可以当条件查询吗 sql视图里面可以传参数吗

admin2024-05-30  17

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。


本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明原文出处。如若内容造成侵权/违法违规/事实不符,请联系SD编程学习网:675289112@qq.com进行投诉反馈,一经查实,立即删除!