|
在实际的项目开发中我们需要通过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,
&n [1] [2] 下一页
没有相关教程
|
注：本站部分文章源于互联网，版权归原作者所有！如有侵权，请原作者与本站联系，本站将立即删除！ 本站文章除特别注明外均可转载，但需注明出处！ [MinTao学以致用网] 
