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