oracle视图创建参数

最新推荐文章于 2023-05-25 11:09:15 发布

原创最新推荐文章于 2023-05-25 11:09:15 发布 · 319 阅读

0 ·

CC 4.0 BY-SA版权

oracle 专栏收录该内容

7 篇文章

订阅专栏

本文详细介绍了在数据库中如何通过创建参数包来动态设置查询的时间范围，并使用这些参数在视图中筛选和汇总投诉数据。通过具体代码示例，展示了参数包的定义与实现，以及如何在视图中运用这些参数进行复杂的数据筛选。

1.定义包：
create or replace package Complaint_volume_pro_param is
    --参数一
    function set_StartTimeStr(str varchar2) return varchar2;
    function get_StartTimeStr return varchar2;
    --参数二
    function set_EndTimeStr(str varchar2) return varchar2;
    function get_EndTimeStr return varchar2;
end Complaint_volume_pro_param;
2.定义包体
create or replace package body Complaint_volume_pro_param is
    startTimeStr varchar2(20);
    endTimeStr varchar2(20);
    -- startTimeStr
    function set_StartTimeStr(str varchar2) return varchar2 is
    begin
        startTimeStr := str;
        return str;
    end;

    function get_StartTimeStr return varchar2 is
    begin
        return startTimeStr;
    end;
    -- endTimeStr
    function set_EndTimeStr(str varchar2) return varchar2 is
    begin
        endTimeStr := str;
        return str;
    end;

    function get_EndTimeStr return varchar2 is
    begin
        return endTimeStr;
    end;

end Complaint_volume_pro_param;

3.视图中运用
create or replace view Complaint_volume_pro
as
select pc.PNAME,
       pc.PCODE,
       case
           when pc.PCODE in ('600101', '600204', '600301', '600104', '600201', '600105', '600103', '600202', '600203', '600102')
               then '南方大省'
           when pc.PCODE in
                ('600305', '600401', '600205', '600402', '600404', '600403', '600303', '600304', '600302', '600405', '600406')
               then '南方小省'
           when pc.PCODE in ('609903', '609905', '609906', '609904', '609001', '609908', '609907', '609902', '609910', '609909')
               then '北方省'
           end                      area,
       substr(t.ARCHIVE_DATE, 0, 6) month,
       substr(t.ARCHIVE_DATE, 0, 8) daily,
       case
           when to_number(substr(substr(t.ARCHIVE_DATE, 0, 8), 5, 2)) < 4 then '1'
           when to_number(substr(substr(t.ARCHIVE_DATE, 0, 8), 5, 2)) < 7 then '2'
           when to_number(substr(substr(t.ARCHIVE_DATE, 0, 8), 5, 2)) < 10 then '3'
           when to_number(substr(substr(t.ARCHIVE_DATE, 0, 8), 5, 2)) >= 10 then '4'
           end                      quarter,
       count(1)                     nums,
       '0'                          status
from COMPLAINT_WORKSHEET_FULL_NEW t
         left join PROVINCE_CONFIGURE pc on t.SRCSYSID = pc.PCODE
where substr(t.ARCHIVE_DATE, 0, 8) >=
      Complaint_volume_pro_param.get_StartTimeStr()
  and substr(t.ARCHIVE_DATE, 0, 8) <=
      Complaint_volume_pro_param.get_EndTimeStr()
group by substr(t.ARCHIVE_DATE, 0, 8), substr(t.ARCHIVE_DATE, 0, 6), pc.PCODE, pc.PNAME;