使用VB调用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' 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