首页 > 数据库 > Oracle > 正文

一个通过Oracle8i存储过程,返回记录集的程序包(存储过程)

2024-08-29 13:29:19
字体:
来源:转载
供稿:网友



提示:在oracle8i中,如果需要通过存储过程返回结果集, 需要使用游标!

create or replace  package body "sms_pay"."smsmaint"                                                                              
is
 
 --功能描述:查询代理银行交易流水信息 writer: wang haibo 2004-08-24
 procedure getagtbankflow(areacode in varchar2,keyword in varchar2,starttradedate in varchar2,endtradedate in varchar2,re_cursor out t_cursor,ret out number)
   is
   
  isexists number;
  
  strsql varchar2(2048);
  

 begin

  --检查临时表是否已经存在,如果不存在,则创建,否则插入数据 start
 select count(*) into isexists from all_tables where table_name='tempagtbankflow';
 
       
 if isexists=0 then
 
    strsql:='create global temporary table  sms_pay.tempagtbankflow
      (agtid varchar2(13) ,bank_glide number(12),tradetype varchar2(20),trade_money number(14,2),
      tradedate varchar2(10),tradetime varchar2(8),bankname varchar2(30),
      areacode varchar2(30), areaname varchar2(30),state varchar2(30),checkflag number(1),
      statusflag number(1),succflag number(1)) on commit preserve rows';

     --把临时表的创建选项由on commit delete rows改为on commit preserve rows; 
     --否则在调用的时候,回出现ora-08103: object no longer exists
    
     execute immediate strsql;
    
  else
    
     execute immediate 'truncate table sms_pay.tempagtbankflow';
    
  end if;
  --检查临时表是否已经存在,如果不存在,则创建,否则插入数据 end
   
  ---功能:把满足条件的一级代理商转帐信息导入数据库 start
 
  /*strsql:='insert into tempagtbankflow
           select a.agent_mobile,a.bank_glide,
            decode(a.optcode,''100'',''交款交易'',''101'',''交款冲正'',''900'',''抹帐交易'',''类型不明'') as tradetype,a.trade_money,
            to_char(to_date(a.trade_date,''yyyymmdd''),''yyyy-mm-dd'') as tradedate,
            to_char(to_date(a.trade_time,''hh24miss''),''hh24:mi:ss'') as tradetime,b.bankname,c.areacode,c.areaname,
            (case when checkflag=-1 then ''/images/state_rollback.gif'' when succflag=-1 then ''/images/state_cancel.gif'' when statusflag=-1 then ''/images/state_unnormal.gif'' else ''/images/state_normal.gif'' end) as state
           from bank_stream_account a,bankinfoconfig b,areaconfig c,fstagtaccount d
            where a.bank_id=b.bankcode and trim(a.agent_mobile)=d.agtid and c.citycode=d.citycode ';
                   
  if areacode!='0' then   --条件1: 选择所有地区的信息    
     strsql:=strsql || ' and trim(c.areacode)=''' || areacode || ''' ';
  end if;  
    
  if tradedate!=' ' then  --条件2: 选择所有地区的信息,选择所有交易日期的信息

     strsql:=strsql || ' and to_char(to_date(a.trade_date,''yyyymmdd''),''yyyy-mm-dd'')=''' || tradedate || ''' ';       
          
  end if;
 
  if keyword!=' ' then  --条件3: 选择所有地区的信息,选择所有交易日期的信息,选择和查询关键字匹配的信息
    
     strsql:=strsql || '  and (d.agtid like''%' || keyword || '%'' or to_char(a.bank_glide) like ''%' || keyword || '%'' or b.bankname like ''%' || keyword ||'%'') ';
 
  end if; 
 
  execute immediate strsql;    
 
  ---功能:把满足条件的一级代理商转帐信息导入数据库 end
  */
 
  ---功能:把满足条件的二级代理商转帐信息导入数据库 start
   strsql:='insert into tempagtbankflow
                 select a.agent_mobile,a.bank_glide,
                   decode(a.optcode,''100'',''交款交易'',''101'',''交款冲正'',''900'',''抹帐交易'',''类型不明'') as tradetype,a.trade_money,
                   to_char(to_date(a.trade_date,''yyyymmdd''),''yyyy-mm-dd'') as tradedate,
                   to_char(to_date(a.trade_time,''hh24miss''),''hh24:mi:ss'') as tradetime,b.bankname,c.areacode,c.areaname,
                   (case when checkflag=-1 then ''/images/state_rollback.gif'' when succflag=-1 then ''/images/state_cancel.gif'' when statusflag=-1 then ''/images/state_unnormal.gif'' else ''/images/state_normal.gif'' end) as state,
                   a.checkflag,a.statusflag,a.succflag
                 from bank_stream_account a,bankinfoconfig b,areaconfig c,secagtaccount d,fstagtaccount e
                   where a.bank_id=b.bankcode and trim(a.agent_mobile)=d.agtid and d.bossagtid=e.agtid and c.citycode=e.citycode ';
 
  if areacode!='0' then   --条件1: 选择所有地区的信息
    
     strsql:=strsql || ' and trim(c.areacode)=''' || areacode || ''' ';
    
  end if;  
    
  if starttradedate!=' ' then  --条件2: 选择所有地区的信息,选择所有交易开始日期的信息

     strsql:=strsql || ' and to_char(to_date(a.trade_date,''yyyymmdd''),''yyyy-mm-dd'')>=''' || starttradedate || ''' ';       
          
  end if;
 
  if endtradedate!=' ' then  --条件2: 选择所有地区的信息,选择所有交易截止日期的信息

     strsql:=strsql || ' and to_char(to_date(a.trade_date,''yyyymmdd''),''yyyy-mm-dd'')<=''' || endtradedate || ''' ';       
          
  end if;
 
  if keyword!=' ' then  --条件3: 选择所有地区的信息,选择所有交易日期的信息,选择和查询关键字匹配的信息
    
     strsql:=strsql || '  and (d.agtid like''%' || keyword || '%'' or to_char(a.bank_glide) like ''%' || keyword || '%'' or b.bankname like ''%' || keyword ||'%'') ';
 
  end if; 
                
      
  execute immediate strsql;    
    ---功能:把满足条件的二级代理商转帐信息导入数据库 end

 open re_cursor for 'select * from sms_pay.tempagtbankflow order by agtid';  --生成返回结果集的curso
 
 ret:=0;
 return;

 exception

  when others then
  dbms_output.put_line(sqlerrm);
  ret:=999;  
  return;
  end getagtbankflow;
 
 
 
 
  
 --功能描述:查询代理商钱包余额信息 writer: wang haibo 2004-08-24
 procedure getagtbalance(areacode in varchar2,keyword in varchar2,re_cursor out t_cursor,ret out number)
   is
  pfstagtid varchar2(13);
  psecagtid  varchar2(13);
  pagtbalance number(10,2);
  pagtprofit  number(10,2);
  pareacode varchar2(10);
  pareaname varchar2(30);
  pagtcount number(10);
 
  isexists number;
  subagtnumber number;

 type c_getbalance is ref cursor;

  fstcursor c_getbalance;

  strsql varchar2(1024);
 

 begin

  -- 打开游标,根据sql语句获取1级代理商资料,然后根据1级代理商的资料获取2级代理的信息

  if areacode!='0' and keyword=' ' then
  
     strsql:='select a.agtid,'''',a.agtcredit,a.agtprofit,
             b.areacode,b.areaname,0 from sms_pay.fstagtaccount a,sms_pay.areaconfig b
             where a.isactive=1 and a.citycode=b.citycode and b.areacode =''' || areacode ||'''';          
    
       
  elsif (trim(areacode)!='0' and keyword!=' ') then
     strsql:='select a.agtid,'''',a.agtcredit,a.agtprofit,
             b.areacode,b.areaname,0 from sms_pay.fstagtaccount a,sms_pay.areaconfig b
             where a.isactive=1 and a.citycode=b.citycode and b.areacode =''' || areacode ||
             ''' and (b.areaname like ''%' || trim(keyword) || '%'' or  a.agtid like ''%' || trim(keyword) || '%'')';
 
  elsif (trim(areacode)='0' and keyword=' ') then
   
     strsql:='select a.agtid,'''',a.agtcredit,a.agtprofit,
             b.areacode,b.areaname,0 from sms_pay.fstagtaccount a,sms_pay.areaconfig b
             where a.isactive=1 and a.citycode=b.citycode';               
 
  else
     strsql:='select a.agtid,'''',a.agtcredit,a.agtprofit,
             b.areacode,b.areaname,0 from sms_pay.fstagtaccount a,sms_pay.areaconfig b
             where a.isactive=1 and a.citycode=b.citycode
             and (b.areaname like ''%' || keyword || '%'' or  a.agtid like ''%' || keyword || '%'')';
   
  end if;
 
  
  open fstcursor for strsql;

  --检查临时表是否已经存在,如果不存在,则创建,否则插入数据 start
 select count(*) into isexists from all_tables where trim(table_name)='tempagtbalance';

 if isexists=0 then
    strsql:='create global temporary table  sms_pay.tempagtbalance
      (fstagtid varchar2(13) ,secagtid  varchar2(13),parentid  varchar2(13),
      agtbalance number(10,2),agtprofit number(10,2),areacode varchar2(10),
      areaname varchar2(30),agtcount number(10)) on commit preserve rows';

     --把临时表的创建选项由on commit delete rows改为on commit preserve rows; 
     --否则在调用的时候,回出现ora-08103: object no longer exists
     --dbms_output.put_line(strsql);
     execute immediate strsql;
  else
     execute immediate 'truncate table sms_pay.tempagtbalance';
  end if;
  --检查临时表是否已经存在,如果不存在,则创建,否则插入数据 end
      
 loop

  fetch fstcursor into pfstagtid,psecagtid, pagtbalance,pagtprofit,pareacode, pareaname, pagtcount ;
   
  exit when fstcursor%notfound;

  --1:写1级代理商查询信息
  strsql:='insert into tempagtbalance(fstagtid,secagtid, parentid,agtbalance,agtprofit,areacode, areaname, agtcount)
           values(''' || pfstagtid || ''',null,null,' || pagtbalance || ',' || pagtprofit || ',''' || pareacode || ''','''
           || pareaname || ''',' || pagtcount || ')';
   
    execute immediate strsql;
   
  --2:写2级代理商查询信息
  strsql:='insert into tempagtbalance select null,agtid,''' || pfstagtid ||''',agtcredit,agtprofit ,''' || pareacode || ''',''' || pareaname || ''',0 from secagtaccount where trim(bossagtid)=''' || pfstagtid || '''';
  
  execute immediate strsql;
           
    --3:更新1级代理的下级别代理商数量
    strsql:='select count(*) from tempagtbalance where secagtid is not null and parentid=''' || pfstagtid || '''';
    --dbms_output.put_line(strsql);
    execute immediate strsql  into subagtnumber;
    --select count(*) into subagtnumber from tempagtbalance where secagtid is not null and parentid=pfstagtid;
   
    dbms_output.put_line(subagtnumber);
       
    strsql:='update tempagtbalance set agtcount=' || subagtnumber || ' where secagtid is null and fstagtid=''' || pfstagtid || '''';
    execute immediate strsql;
   
    --dbms_output.put_line(strsql);
    --update tempagtbalance set agtcount=subagtnumber where secagtid is null and fstagtid=pfstagtid;
      
 end loop;
 
     
 close fstcursor;

 open re_cursor for 'select * from sms_pay.tempagtbalance';
 
 ret:=0;
 return;

 exception

  when others then
  dbms_output.put_line(sqlerrm);
  ret:=999;  
  return;
  end getagtbalance;
 
 
 
end ;

 
发表评论 共有条评论
用户名: 密码:
验证码: 匿名发表