当前位置：首页 > news >正文

数据库-oracle-包-视图传参

news 2025/5/20 6:11:36

并发下可能不准确

-- 修改包规范
CREATE OR REPLACE PACKAGE sczz.p_view_param IS

    function set_n(n varchar2) return varchar2;
    function get_n return varchar2;
    function set_ny(ny varchar2) return varchar2;
    function get_ny return varchar2;
    function set_nyr(nyr varchar2) return varchar2;
    function get_nyr return varchar2;

    -- 新增zyc参数 --
    function set_zyc(p_zyc varchar2) return varchar2; -- 设置值
    function get_zyc return varchar2;                   -- 获取值
END p_view_param;
/

-- 修改包体
CREATE OR REPLACE PACKAGE BODY sczz.p_view_param IS
    -- 原有变量 --

    g_n     varchar2(10);
    g_ny    varchar2(10);
    g_nyr   varchar2(10);

    -- 新增zyc存储变量 --
    g_zyc   varchar2(20); -- 根据实际需要调整长度

    -- 原有函数实现 --
    function set_n(n varchar2) return varchar2 is
    begin
        g_n := n;
        return g_n;
    end;

    function get_n return varchar2 is
    begin
        return g_n;
    end;

    function set_ny(ny varchar2) return varchar2 is
    begin
        g_ny := ny;
        return g_ny;
    end;

    function get_ny return varchar2 is
    begin
        return g_ny;
    end;

    function set_nyr(nyr varchar2) return varchar2 is
    begin
        g_nyr := nyr;
        return g_nyr;
    end;

    function get_nyr return varchar2 is
    begin
        return g_nyr;
    end;

    -- 新增zyc函数实现 --
    function set_zyc(p_zyc varchar2) return varchar2 is
    begin
        g_zyc := p_zyc;       -- 存储参数值
        return g_zyc;         -- 返回设置后的值
    end;

    function get_zyc return varchar2 is
    begin
        return g_zyc;         -- 返回存储的值
    end;

END p_view_param;

使用

@Select("select * from sczz.v_scjk_xhccjpjk where sczz.p_view_param.set_nyr('${nyr}')='${nyr}' ")
List<Map<String, Object>> selectClxxByRq(@Param("nyr") String nyr);

create view V_SCJK_XHCCJPJK as
select rw.id,
                           rw.gddm,
                           (select GDMC from common.GD_GDXX where gddm = rw.GDDM) as GDMC,
                           mx.ccsx as label,
                           --decode(rw.sfrkyj,'1',to_char(rw.jhjcsj, 'yyyy-MM-dd hh24:mi'),'')
                           to_char(rw.jhjcsj, 'yyyy-MM-dd hh24:mi') as start_time,
                            --decode(rw.sfckyj,'1',to_char(rw.jhccsj, 'yyyy-MM-dd hh24:mi'),'')
                            to_char(rw.jhccsj, 'yyyy-MM-dd hh24:mi') as end_time,
                           to_char(sjjcsj, 'yyyy-MM-DD hh24:mi') as start_time_sj,
                           to_char(sjccsj, 'yyyy-MM-DD hh24:mi') as end_time_sj,
                           rw.sfdl
                      from sczz.jh_jcrw rw, sczz.jh_ccsx_mx mx
                     where rw.id = mx.jcrwid(+)
                       and to_char(rw.jhccsj, 'yyyy-MM-dd') = sczz.p_view_param.get_nyr
                       order by label