oracle视图传参

傷城~ 2022-04-10 07:20 600阅读 0赞

1、对要传的参数写set、get方法

create or replace package vm_param  is
       function set_userid(user_ids varchar2) return varchar2;
       function get_userid  return varchar2;
        function set_SHIFT_NAME(SHIFT_NAMES varchar2) return varchar2;
       function get_SHIFT_NAME  return varchar2;
       function set_begintime(begintimes varchar2) return varchar2;
       function get_begintime  return varchar2;
        function set_endtime(endtimes varchar2) return varchar2;
       function get_endtime  return varchar2;
        function set_shift_id(shift_ids varchar2) return varchar2;
       function get_shift_id  return varchar2;
    end vm_param  ;
    //我这里写了5个，类型都是varchar2，分别是user_ids 、SHIFT_NAMES 、begintimes 、endtimes 、shift_ids 
    
    //vm_param  为名字。可以根据这个调用参数（个人感觉像实体类生成get、set方法，可以实体.下面的属性）

2、第二步个人觉得是对这几个参数写一个函数，

//名字和上面的要一样
    create or replace package body vm_param is
    //定义字段类型长度
           user_id varchar2(100);
           SHIFT_NAME varchar2(100);
           begintime varchar2(100);
           endtime varchar2(100);
           shift_id varchar2(500);
           function set_userid(user_ids varchar2) return varchar2 is
           begin
             user_id:=user_ids;
             return user_id;
            end;
           function get_userid return varchar2 is
           begin
             return user_id;
           end;
    
           function set_SHIFT_NAME(SHIFT_NAMES varchar2) return varchar2 is
           begin
             SHIFT_NAME:=SHIFT_NAMES;
             return SHIFT_NAME;
            end;
           function get_SHIFT_NAME return varchar2 is
           begin
             return SHIFT_NAME;
           end;
    
                 function set_begintime(begintimes varchar2) return varchar2 is
           begin
             begintime:=begintimes;
             return begintime;
            end;
           function get_begintime return varchar2 is
           begin
             return begintime;
           end;
    
                 function set_endtime(endtimes varchar2) return varchar2 is
           begin
             endtime:=endtimes;
             return endtime;
            end;
           function get_endtime return varchar2 is
           begin
             return endtime;
           end;
           
                       function set_shift_id(shift_ids varchar2) return varchar2 is
           begin
             shift_id:=shift_ids;
             return shift_id;
            end;
           function get_shift_id return varchar2 is
           begin
             return shift_id;
           end;
       end vm_param;

3、将参数放到视图中，

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI0NTIxNDMx_size_16_color_FFFFFF_t_70][]

4、查询视图时传入参数

![20181228163543248.png][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI0NTIxNDMx_size_16_color_FFFFFF_t_70]: /images/20220402/d5d74552562649f7baf919036c71f60a.png
[20181228163543248.png]: /images/20220402/5f48268d90154d1b8629ba5bc41a859e.png