調用oracle程式包內的預存程序返回結果集

在實際的項目開發中我們需要通過vb(或其他語言工具)調用oracle程式包內的預存程序返回結果集.這裡以簡訊運營平台中的一個調用為例來說明這個過程,希望對你有所協助.

--一.使用sql*plus建立以下項目:
--1.建表("ow_smp"為方案名稱,下同)

create table "ow_smp"."sm_send_sm_list"(
    serialno int primary key,   --序號
    serviceid varchar(50),      --服務id（業務類型）
    smcontent varchar(1000),     --簡訊內容
    sendtarget varchar(20),      --發送目標   
    priority smallint,       --發送優先順序
    rcompletetimebegin date,    --要求完成日期（開始）
    rcompletetimeend date,     --要求完成日期（結束）
    rcompletehourbegin smallint,    --要求完成時間（開始）
    rcompletehourend smallint,     --要求完成時間（結束）
    requesttime date,      --發送請求時間
    roadby smallint,       --發送通道（0：gsm模組，1：

簡訊網關）
    sendtargetdesc varchar(100),    --發送目標描述
    feevalue float,        --本條簡訊資訊費用（

單位：分）
    pad1 varchar(50),
    pad2 varchar(100),
    pad3 varchar(200),
    pad4 varchar(500),
    pad5 varchar(1000)
);
--2.建立自增序列
create sequence "ow_smp"."sendsno";
create or replace trigger "ow_smp"."bfinert_sm_send" before
insert on "sm_send_sm_list"
    for each row begin
select sendsno.nextval into :new.serialno from dual;
end;
--3.插入資料
insert sm_send_sm_list (smcontent) values(happy new year to jakcy!);
insert sm_send_sm_list (smcontent) values(happy new year to wxl!);
--4.建立程式包和包體

create or replace package "ow_smp"."ow_smp_package"            
            is
      type tserialno is table of sm_send_sm_list.serialno%type
        index by binary_integer;
      type tserviceid is table of sm_send_sm_list.serviceid%type
        index by binary_integer;
      type tsmcontent is table of sm_send_sm_list.smcontent%type
        index by binary_integer;
      type tsendtarget is table of sm_send_sm_list.sendtarget%type
        index by binary_integer;
      type tpriority is table of sm_send_sm_list.priority%type
        index by binary_integer;
      type trcompletetimebegin is table of sm_send_sm_list.rcompletetimebegin%type
        index by binary_integer;
      type trcompletetimeend is table of sm_send_sm_list.rcompletetimeend%type
        index by binary_integer;         
      type trcompletehourbegin is table of sm_send_sm_list.rcompletehourbegin%type
        index by binary_integer;
      type trcompletehourend is table of sm_send_sm_list.rcompletehourend%type
        index by binary_integer;     
      type trequesttime is table of sm_send_sm_list.requesttime%type
        index by binary_integer;    
      type troadby is table of sm_send_sm_list.roadby%type
        index by binary_integer;   
      type tsendtargetdesc is table of sm_send_sm_list.sendtargetdesc%type
        index by binary_integer;
      type tfeevalue is table of sm_send_sm_list.feevalue%type
        index by binary_integer;
      type tpad1 is table of sm_send_sm_list.pad1%type
        index by binary_integer;      
      type tpad2 is table of sm_send_sm_list.pad2%type
        index by binary_integer;      
      type tpad3 is table of sm_send_sm_list.pad3%type
        index by binary_integer;      
      type tpad4 is table of sm_send_sm_list.pad4%type
        index by binary_integer;      
      type tpad5 is table of sm_send_sm_list.pad5%type
        index by binary_integer;
      type tcount is table of number
        index by binary_integer;

       procedure getsendsm
              (v_nowbyminute   in number,
               v_serialno      out tserialno,
               v_serviceid     out tserviceid,
               v_smcontent     out tsmcontent,
               v_sendtarget     out tsendtarget,
               v_priority      out tpriority,
               v_rcompletetimebegin out trcompletetimebegin,
               v_rcompletetimeend   out trcompletetimeend,
               v_rcompletehourbegin out trcompletehourbegin,
               v_rcompletehourend   out trcompletehourend,
               v_requesttime        out trequesttime,
               v_roadby             out troadby,
               v_sendtargetdesc     out tsendtargetdesc,
               v_feevalue           out tfeevalue,
               v_pad1               out tpad1,
               v_pad2               out tpad2,
               v_pad3               out tpad3,
               v_pad4               out tpad4,
               v_pad5               out tpad5,
               v_count            out tcount
               );

end;
/
create or replace package body "ow_smp"."ow_smp_package"       
            is
      procedure getsendsm --獲得前1000條在指定時間內的待發簡訊
              (v_nowbyminute   in number,
               v_serialno      out tserialno,
               v_serviceid     out tserviceid,
               v_smcontent     out tsmcontent,
               v_sendtarget     out tsendtarget,
               v_priority      out tpriority,
               v_rcompletetimebegin out trcompletetimebegin,
               v_rcompletetimeend   out trcompletetimeend,
               v_rcompletehourbegin out trcompletehourbegin,
               v_rcompletehourend   out trcompletehourend,
               v_requesttime        out trequesttime,
               v_roadby             out troadby,
               v_sendtargetdesc     out tsendtargetdesc,
               v_feevalue           out tfeevalue,
               v_pad1               out tpad1,
               v_pad2               out tpad2,
               v_pad3               out tpad3,
               v_pad4               out tpad4,
               v_pad5               out tpad5,
               v_count            out tcount)
               
      is
          cursor sendsm_cur is
                  select * from sm_send_sm_list
                  where rcompletehourbegin<=v_nowbyminute and

rcompletehourend>=v_nowbyminute and (rcompletetimebegin is null or

rcompletetimebegin<=sysdate)
                  and (rcompletetimeend is null or rcompletetimeend>=sysdate-1)
                  and rownum<1001;
                 
          smcount number default 1;
      begin
          for sm in sendsm_cur
          loop
                  v_serialno(smcount):=sm.serialno;
                  v_serviceid(smcount):=sm.serviceid;
                  v_smcontent(smcount):=sm.smcontent;
                  v_sendtarget(smcount):=sm.sendtarget;
                  v_priority(smcount):=sm.priority;
                  v_rcompletetimebegin(smcount):=sm.rcompletetimebegin;
                  v_rcompletetimeend(smcount):=sm.rcompletetimeend;
                  v_rcompletehourbegin(smcount):=sm.rcompletehourbegin;
                  v_rcompletehourend(smcount):=sm.rcompletehourend;
                  v_requesttime(smcount):=sm.requesttime;
                  v_roadby(smcount):=sm.roadby;
                  v_sendtargetdesc(smcount):=sm.sendtargetdesc;
                  v_feevalue(smcount):=sm.feevalue;
                  v_pad1(smcount):=sm.pad1;
                  v_pad2(smcount):=sm.pad2;
                  v_pad3(smcount):=sm.pad3;
                  v_pad4(smcount):=sm.pad4;
                  v_pad5(smcount):=sm.pad5;                 
                  if smcount=1 then
                    select count(*)
                    into v_count(smcount)
                    from sm_send_sm_list
                    where rcompletehourbegin<=v_nowbyminute and

rcompletehourend>=v_nowbyminute and (rcompletetimebegin is null or

rcompletetimebegin<=sysdate)
                    and (rcompletetimeend is null or rcompletetimeend>=sysdate-1)
                    and rownum<1001;
                  end if;
                  smcount:= smcount + 1;
          end loop;       
      end;
end;
/

二.使用vb調用ow_smp_package.getsendsm預存程序:

sub getsendsm()
dim cmd as new adodb.command
dim rs as new adodb.recordset
cmd.activeconnection = getconnection獲得資料庫連接
cmd.commandtext = "{call ow_smp_package.getsendsm(?,{resultset

1000,v_serialno,v_serviceid,v_smcontent,v_sendtarget,v_priority,v_rcompletetimebegin,v_rcomp

letetimeend,v_rcompletehourbegin,v_rcompletehourend,v_requesttime,v_roadby,v_sendtargetdesc,

v_feevalue,v_pad1,v_pad2,v_pad3,v_pad4,v_pad5,v_count})}"
cmd.commandtype = adcmdtext
cmd.parameters.append .createparameter("v_nowbyminute", adinteger, adparaminput, , 900)
     
rs.cursortype = adopenstatic
rs.locktype = adlockreadonly
set rs.source = cmd
rs.open  
while not rs.eof
      msgbox "sendsm data:serialno: " & rs("v_serialno") & ",smcontent: " & rs

("v_smcontent") & ",count: " & rs("v_count")
      對結果集的處理在這裡增加代碼
      rs.movenext
   wend
   rs.close  
   set rs=nothing
   set cmd=nothing
end sub