科技行者

行者学院 转型私董会 科技行者专题报道 网红大战科技行者

知识库

知识库 安全导航

至顶网软件频道使用VB调用Oracle程序包内的存储过程返回结果集

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

  • 扫一扫
    分享文章到微信

  • 扫一扫
    关注官方公众号
    至顶头条

  在实际的项目开发中我们需要通过VB(或其他语言工具)调用Oracle程序包内的存储过程返回结果集.这里以短信运营平台中的一个调用为例来说明这个过程,希望对你有所帮助.      一.使用SQL*Plus创建以下项目:   --1.建表("OW_S

作者:中国IT实验室 来源:中国IT实验室 2007年10月6日

关键字: 开发 数据库 ORACLE

  • 评论
  • 分享微博
  • 分享邮件

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

查看本文来源

    • 评论
    • 分享微博
    • 分享邮件
    邮件订阅

    如果您非常迫切的想了解IT领域最新产品与技术信息,那么订阅至顶网技术邮件将是您的最佳途径之一。

    重磅专题
    往期文章
    最新文章