在實際的項目開發中我們需要通過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' BEFOREINSERT 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_NowByMinutein 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_RCompleteTimeEndout tRCompleteTimeEnd, v_RCompleteHourBegin out tRCompleteHourBegin, v_RCompleteHourEndout tRCompleteHourEnd, v_RequestTime;;;;;out tRequestTime, v_RoadBy;;out tRoadBy, v_SendTargetDesc;;out tSendTargetDesc, v_FeeValueout 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_NowByMinutein 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_RCompleteTimeEndout tRCompleteTimeEnd, v_RCompleteHourBegin out tRCompleteHourBegin, v_RCompleteHourEndout tRCompleteHourEnd, v_RequestTime;;;;;out tRequestTime, v_RoadBy;;out tRoadBy, v_SendTargetDesc;;out tSendTargetDesc, v_FeeValueout 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_RCompleteTimeEnd,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=nothingEnd Sub