在实际的项目开发中我们需要通过VB(或其他语言工具)调用Oracle程序包内的存储过程返回结果集这里以短信运营平台中的一个调用为例来说明这个过程希望对你有所帮助
一使用SQL*Plus创建以下项目:
建表(OW_SMP为方案名称下同)
CREATE TABLE OW_SMPSM_Send_SM_List(
SerialNo INT PRIMARY KEY 序列号
ServiceID VARCHAR() 服务ID（业务类型）
SMContent VARCHAR() 短信内容
SendTarget VARCHAR() 发送目标
Priority SMALLINT 发送优先级
RCompleteTimeBegin DATE 要求完成日期（开始）
RCompleteTimeEnd DATE 要求完成日期（结束）
RCompleteHourBegin SMALLINT 要求完成时间（开始）
RCompleteHourEnd SMALLINT 要求完成时间（结束）
RequestTime DATE 发送请求时间
RoadBy SMALLINT 发送通道（GSM模块短信网关）
SendTargetDesc VARCHAR() 发送目标描述
FeeValue FLOAT 本条短信信息费用（单位分）
Pad VARCHAR()
Pad VARCHAR()
Pad VARCHAR()
Pad VARCHAR()
Pad VARCHAR()
);
建立自增序列
Create sequence OW_SMPSENDSNO;
CREATE OR REPLACE TRIGGER OW_SMPBFINERT_SM_SEND BEFORE
INSERT ON SM_SEND_SM_LIST
FOR EACH ROW begin
select SendSNonextval into :newserialno from dual;
end;
插入数据
Insert SM_Send_SM_List (SMCOntent) values(Happy New Year To Jakcy!);
Insert SM_Send_SM_List (SMCOntent) values(Happy New Year To Wxl!);
建立程序包和包体
CREATE OR REPLACE PACKAGE OW_SMPOW_SMP_PACKAGE
is
type tSerialNo is table of sm_send_sm_listSerialNo%type
index by binary_integer;
type tServiceID is table of sm_send_sm_listServiceID%type
index by binary_integer;
type tSMContent is table of sm_send_sm_listSMContent%type
index by binary_integer;
type tSendTarget is table of sm_send_sm_listSendTarget%type
index by binary_integer;
type tPriority is table of sm_send_sm_listPriority%type
index by binary_integer;
type tRCompleteTimeBegin is table of sm_send_sm_listRCompleteTimeBegin%type
index by binary_integer;
type tRCompleteTimeEnd is table of sm_send_sm_listRCompleteTimeEnd%type
index by binary_integer;
type tRCompleteHourBegin is table of sm_send_sm_listRCompleteHourBegin%type
index by binary_integer;
type tRCompleteHourEnd is table of sm_send_sm_listRCompleteHourEnd%type
index by binary_integer;
type tRequestTime is table of sm_send_sm_listRequestTime%type
index by binary_integer;
type tRoadBy is table of sm_send_sm_listRoadBy%type
index by binary_integer;
type tSendTargetDesc is table of sm_send_sm_listSendTargetDesc%type
index by binary_integer;
type tFeeValue is table of sm_send_sm_listFeeValue%type
index by binary_integer;
type tPad is table of sm_send_sm_listPad%type
index by binary_integer;
type tPad is table of sm_send_sm_listPad%type
index by binary_integer;
type tPad is table of sm_send_sm_listPad%type
index by binary_integer;
type tPad is table of sm_send_sm_listPad%type
index by binary_integer;
type tPad is table of sm_send_sm_listPad%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_Pad out tPad
v_Pad out tPad
v_Pad out tPad
v_Pad out tPad
v_Pad out tPad
v_Count out tCount
;
end;
/
CREATE OR REPLACE PACKAGE BODY OW_SMPOW_SMP_PACKAGE
is
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_Pad out tPad
v_Pad out tPad
v_Pad out tPad
v_Pad out tPad
v_Pad out tPad
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)
and RowNum<;
smcount number default ;
begin
for sm in sendsm_cur
loop
v_SerialNo(smcount):=smSerialNo;
v_ServiceID(smcount):=smServiceID;
v_SMContent(smcount):=smSMContent;
v_SendTarget(smcount):=smSendTarget;
v_Priority(smcount):=smPriority;
v_RCompleteTimeBegin(smcount):=smRCompleteTimeBegin;
v_RCompleteTimeEnd(smcount):=smRCompleteTimeEnd;
v_RCompleteHourBegin(smcount):=smRCompleteHourBegin;
v_RCompleteHourEnd(smcount):=smRCompleteHourEnd;
v_RequestTime(smcount):=smRequestTime;
v_RoadBy(smcount):=smRoadBy;
v_SendTargetDesc(smcount):=smSendTargetDesc;
v_FeeValue(smcount):=smFeeValue;
v_Pad(smcount):=smPad;
v_Pad(smcount):=smPad;
v_Pad(smcount):=smPad;
v_Pad(smcount):=smPad;
v_Pad(smcount):=smPad;
