使用VB調用Oracle程式包內的預存程序返回結果集

來源:互聯網
上載者:User
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



相關文章

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。