鸿 网 互 联 www.68idc.cn

当前位置 : 服务器租用 > 网站制作教程 > vbs > >

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

来源:互联网 作者:佚名 时间:2015-10-15 13:51
在实际的项目开发中我们需要通过VB(或其他语言工具)调用Oracle程序包内的存储过程返回结果集.这里以短信运营平台中的一个调用为例来说明这个过程,希望对你有所帮助. 一.使用SQL*Plus创建以下项目: --1.建表("OW_SMP"为方案名称,下同) CREATE TABLE "OW_SMP"."
在实际的项目开发中我们需要通过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;
网友评论
<