单位：分） 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
