在實際的項目開發中我們需要通過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
