--创建参数包

create or replace package view_risk is

  function set_depcode(depcode varchar2) return varchar2;
  function get_depcode return varchar2;
  function set_dmonth(dmonth varchar2) return varchar2;
  function get_dmonth return varchar2;
 
end view_risk;

--创建包体，写赋值方法
create or replace package body view_risk is

  depcodeValue varchar2(5000);
  dmonthValue varchar2(5000);

--depcode
  function set_depcode(depcode varchar2) return varchar2 is
  begin
    depcodeValue := depcode;
    return depcode;
  end;
  function get_depcode return varchar2 is
  begin
    return depcodeValue;
  end;
  
  --dmonth
  function set_dmonth(dmonth varchar2) return varchar2 is
  begin
    dmonthValue := dmonth;
    return dmonth;
  end;
  function get_dmonth return varchar2 is
  begin
    return dmonthValue;
  end;

end view_risk;

---创建视图
Create  or replace  View view_risk_month AS

---视图的sql部分按照你的需求去写，要用的参数就用包体的get方法去取

SELECT C_LOANACCOUNTCODE, c_projectcode,MAX(ID) as id,C_DEPCODE,c_risk_level,c_projectname,C_LOANACCOUNTNAME,c_bistype FROM trisk_apply_his 
  inner join （
  
select REGEXP_SUBSTR(view_risk.get_depcode(), '[^,]+', 1, LEVEL, 'i') AS ADDUSERCD 
  from DUAL
  CONNECT BY LEVEL <= LENGTH(view_risk.get_depcode()) - LENGTH(replace(view_risk.get_depcode(), ',', '')) + 1
  ）on instr(',' || c_depcode || ',', ',' || ADDUSERCD || ',') > 0 
  WHERE trisk_apply_his.c_flow_status = 2  AND D_FLOW_FINISH_DATE < to_date(view_risk.get_dmonth(),'yyyy-mm-dd')+1
  group by C_LOANACCOUNTCODE, c_projectcode,C_DEPCODE,c_risk_level,c_projectname,C_LOANACCOUNTNAME,c_bistype
  

  
  ------------ 查询视图
  select * from view_risk_month where view_risk.set_depcode('0_100001,0_100002,0_zsyjysj
') ='0_100001,0_100002,0_zsyjysj' AND view_risk.set_dmonth('2023-05-24')='2023-05-24'