分享
 
 
 

使用VB调用Oracle程序包内的存储过程返回结果集

王朝oracle·作者佚名  2008-05-31
窄屏简体版  字體: |||超大  

在实际的项目开发中我们需要通过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

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
2023年上半年GDP全球前十五强
 百态   2023-10-24
美众议院议长启动对拜登的弹劾调查
 百态   2023-09-13
上海、济南、武汉等多地出现不明坠落物
 探索   2023-09-06
印度或要将国名改为“巴拉特”
 百态   2023-09-06
男子为女友送行,买票不登机被捕
 百态   2023-08-20
手机地震预警功能怎么开?
 干货   2023-08-06
女子4年卖2套房花700多万做美容:不但没变美脸,面部还出现变形
 百态   2023-08-04
住户一楼被水淹 还冲来8头猪
 百态   2023-07-31
女子体内爬出大量瓜子状活虫
 百态   2023-07-25
地球连续35年收到神秘规律性信号,网友:不要回答!
 探索   2023-07-21
全球镓价格本周大涨27%
 探索   2023-07-09
钱都流向了那些不缺钱的人,苦都留给了能吃苦的人
 探索   2023-07-02
倩女手游刀客魅者强控制(强混乱强眩晕强睡眠)和对应控制抗性的关系
 百态   2020-08-20
美国5月9日最新疫情:美国确诊人数突破131万
 百态   2020-05-09
荷兰政府宣布将集体辞职
 干货   2020-04-30
倩女幽魂手游师徒任务情义春秋猜成语答案逍遥观:鹏程万里
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案神机营:射石饮羽
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案昆仑山:拔刀相助
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案天工阁:鬼斧神工
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案丝路古道:单枪匹马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:与虎谋皮
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:李代桃僵
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:指鹿为马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:小鸟依人
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:千金买邻
 干货   2019-11-12
 
推荐阅读
 
 
 
>>返回首頁<<
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有