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

[1] [2] [3] [4] [5] [6] [7] 下一页