oracle和db2存储过程实例

create or replace procedure cl_24months_status
is
begin
declare
v_op_num char(20);
v_start_date date;
v_end_date date;
v_count number;
v_count1 number;
v_24months_flag varchar(24);
v_date_opened date;
v_mature_date date;
v_overdue number;
v_status char(1);
v_reportdate date;
v_billing_date1 date;
v_billing_date2 date;
cursor Overdue_Cursor IS
select op_num,date_opened,mature_date,status
from cl_valid_contract
where term_freq<>'5' order by op_num;
begin
open Overdue_Cursor;
select reportdate into v_reportdate from t_etldate;
delete from cl_months24_status_total;
loop
v_24months_flag:='';
v_end_date:=v_reportdate;
fetch  Overdue_Cursor into v_op_num,v_date_opened,v_mature_date,v_status;
------计算需要计算的最早和最晚月份
if add_months(v_end_date,-24)<v_date_opened
then v_start_date:=LAST_DAY(v_date_opened);
else v_start_date:=add_months(v_end_date,-23);
end if;
exit when Overdue_Cursor%notfound;

loop
exit when v_end_date <= v_start_date;

--------计算前23个月的还款状态
select nvl(max(billing_date),last_day(v_start_date))
into v_billing_date1
from cl_valid_balance b
where op_num=v_op_num and last_day(billing_date)=last_day(v_start_date);
select nvl(max(billing_date),last_day(add_months(v_start_date,-1)))
into v_billing_date2
from cl_valid_balance b
where op_num=v_op_num and last_day(billing_date)=last_day(add_months(v_start_date,-1));

select count(*)
into v_count
from cl_valid_balance
where op_num=v_op_num
and billing_date between v_billing_date2+1 and v_billing_date1;
select nvl(max(v_billing_date1-billing_date+1),-1)
into v_count1
from cl_valid_overdue
where op_num=v_op_num and v_billing_date1 between billing_date and actual_date-1;
if v_count=0 and v_count1<=0 then v_24months_flag:=concat(v_24months_flag,'*');
elsif v_count1<=0 then v_24months_flag:=concat(v_24months_flag,'N');
elsif trunc((v_count1-1)/30+1)<8 then v_24months_flag:=concat(v_24months_flag,trunc((v_count1-1)/30+1));
else v_24months_flag:=concat(v_24months_flag,'7');
end if;
v_start_date:=add_months(v_start_date,1);
end loop;

---------计算最后一个月的还款状态
select nvl(max(billing_date),last_day(v_start_date))
into v_billing_date1
from cl_valid_balance b
where op_num=v_op_num and last_day(billing_date)=last_day(v_start_date);
select nvl(max(billing_date),last_day(add_months(v_start_date,-1)))
into v_billing_date2
from cl_valid_balance b
where op_num=v_op_num and last_day(billing_date)=last_day(add_months(v_start_date,-1));
select count(*)
into v_count
from cl_valid_balance
where op_num=v_op_num
and billing_date between v_billing_date2+1 and v_billing_date1;
select nvl(max(v_billing_date1-billing_date+1),-1)
into v_count1
from cl_valid_overdue
where op_num=v_op_num and v_billing_date1 between billing_date and actual_date-1;

if v_status='9'
then v_24months_flag:=concat(v_24months_flag,'C');
elsif v_count=0 and v_count1<=0 then v_24months_flag:=concat(v_24months_flag,'*');
elsif v_count1<=0 then v_24months_flag:=concat(v_24months_flag,'N');
elsif trunc((v_count1-1)/30+1)<8 then v_24months_flag:=concat(v_24months_flag,trunc((v_count1-1)/30+1));
else v_24months_flag:=concat(v_24months_flag,'7');
end if;

insert into cl_months24_status_total values (v_op_num,lpad(v_24months_flag,24,'/'));
commit;
end loop;

close Overdue_Cursor;
end;
end;

 

 

--------------------------------------------

db2:

CREATE PROCEDURE CBRC.SqlType_Procedure (IN etldate TIMESTAMP,
      IN repdate varchar(10),
      IN reptype varchar(20),
      OUT o_err_no int,
                                       OUT o_err_msg varchar(1024))
------------------------------------------------------------------------
-- SQL 存储过程
------------------------------------------------------------------------
P1: BEGIN
DECLARE SQLSTATE CHAR(5);
DECLARE at_end INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
DECLARE ss varchar(1024);
DECLARE sqlstr varchar(1024);
DECLARE v_orgid varchar(20);
DECLARE v_regionid varchar(10);
DECLARE v_curid varchar(3);
DECLARE v_cellformu varchar(1024);
DECLARE v_measureid varchar(1024);
DECLARE v_cellformuidx varchar(1024);
DECLARE v_datarangeid varchar(1024);
DECLARE v_datadate varchar(10);
DECLARE sqlresult varchar(1024);
DECLARE state varchar(1024);
DECLARE r_code           integer default 0;
DECLARE SQLCODE          integer default 0;
DECLARE not_found CONDITION FOR SQLSTATE '02000';
DECLARE st STATEMENT;
DECLARE c1 CURSOR FOR  s1;
DECLARE CONTINUE HANDLER FOR not_found
SET at_end = 1;

--声明出错处理
DECLARE EXIT HANDLER FOR SQLEXCEPTION
          begin
             set r_code=SQLCODE;
             set o_err_no=1;
             set o_err_msg='处理'||state||'出错 '||'错误代码SQLCODE:'||CHAR(r_code);
             insert into t_error values (etldate,reptype,repdate,'sql语句计算',state,o_err_msg);
          end;
         
set state='begin cursor';
SET ss='select replace(replace(replace(replace(replace(cellformu,''' || '@orgid' || ''',orgid),''' || '@regionid' || ''',regionid),''' || '@curid' || ''',curid),''' || '@datarangeid' || ''',datarangeid),''' || '@date' || ''',datadate),MEASUREID,CHAR(CELLFORMUIDX),ORGID,REGIONID,CURID,DATARANGEID,datadate from T_REAL_CELLFORMU WHERE FORMUTYPE=''' || 'S' || '''';
prepare s1 from ss;
OPEN c1;
ins_loop:
loop
set state='fetch cursor';
fetch c1 into v_cellformu,v_measureid,v_cellformuidx,v_orgid,v_regionid,v_curid,v_datarangeid,v_datadate;
IF at_end = 1 THEN
       LEAVE ins_loop;
       END IF;

set state='updatesql';
set sqlstr ='update T_REAL_CELLFORMU set VALUE = (' || v_cellformu || ') where MEASUREID=''' || v_measureid || ''' and char(CELLFORMUIDX)=''' || v_cellformuidx || ''' and ORGID=''' || v_orgid || ''' and REGIONID=''' || v_regionid || ''' and CURID=''' || v_curid || ''' and datarangeid=''' || v_datarangeid || ''' and datadate=''' || v_datadate || '''';
PREPARE st from sqlstr;
EXECUTE st;

 

 

end loop;
close c1;

END P1