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