熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

使用VB調用Oracle程序包內的存儲過程返回結果集

2013-11-13 16:20:46  來源: Oracle 

  在實際的項目開發中我們需要通過VB(或其他語言工具)調用Oracle程序包內的存儲過程返回結果集這裡以短信運營平台中的一個調用為例來說明這個過程希望對你有所幫助
  
  使用SQL*Plus創建以下項目:
  建表(OW_SMP為方案名稱下同)
  
  CREATE TABLE OW_SMPSM_Send_SM_List(
    SerialNo INT PRIMARY KEY 序列號
    ServiceID VARCHAR() 服務ID(業務類型)
    SMContent VARCHAR() 短信內容
    SendTarget VARCHAR() 發送目標
    Priority SMALLINT 發送優先級
    RCompleteTimeBegin DATE 要求完成日期(開始)
    RCompleteTimeEnd DATE 要求完成日期(結束)
    RCompleteHourBegin SMALLINT 要求完成時間(開始)
    RCompleteHourEnd SMALLINT 要求完成時間(結束)
    RequestTime DATE 發送請求時間
    RoadBy SMALLINT 發送通道(GSM模塊短信網關)
    SendTargetDesc VARCHAR() 發送目標描述
    FeeValue FLOAT 本條短信信息費用(單位分)
    Pad VARCHAR()
    Pad VARCHAR()
    Pad VARCHAR()
    Pad VARCHAR()
    Pad VARCHAR()
  );
  建立自增序列
  Create sequence OW_SMPSENDSNO;
  CREATE OR REPLACE TRIGGER OW_SMPBFINERT_SM_SEND BEFORE
  INSERT ON SM_SEND_SM_LIST
    FOR EACH ROW begin
   select SendSNonextval into :newserialno from dual;
  end;
  插入數據
  Insert SM_Send_SM_List (SMCOntent) values(Happy New Year To Jakcy!);
  Insert SM_Send_SM_List (SMCOntent) values(Happy New Year To Wxl!);
  建立程序包和包體
  
  CREATE OR REPLACE PACKAGE OW_SMPOW_SMP_PACKAGE
        is
     type tSerialNo is table of sm_send_sm_listSerialNo%type
      index by binary_integer;
     type tServiceID is table of sm_send_sm_listServiceID%type
      index by binary_integer;
     type tSMContent is table of sm_send_sm_listSMContent%type
      index by binary_integer;
     type tSendTarget is table of sm_send_sm_listSendTarget%type
      index by binary_integer;
     type tPriority is table of sm_send_sm_listPriority%type
      index by binary_integer;
     type tRCompleteTimeBegin is table of sm_send_sm_listRCompleteTimeBegin%type
      index by binary_integer;
     type tRCompleteTimeEnd is table of sm_send_sm_listRCompleteTimeEnd%type
      index by binary_integer;
     type tRCompleteHourBegin is table of sm_send_sm_listRCompleteHourBegin%type
      index by binary_integer;
     type tRCompleteHourEnd is table of sm_send_sm_listRCompleteHourEnd%type
      index by binary_integer;
     type tRequestTime is table of sm_send_sm_listRequestTime%type
      index by binary_integer;
     type tRoadBy is table of sm_send_sm_listRoadBy%type
      index by binary_integer;
     type tSendTargetDesc is table of sm_send_sm_listSendTargetDesc%type
      index by binary_integer;
     type tFeeValue is table of sm_send_sm_listFeeValue%type
      index by binary_integer;
     type tPad is table of sm_send_sm_listPad%type
      index by binary_integer;
     type tPad is table of sm_send_sm_listPad%type
      index by binary_integer;
     type tPad is table of sm_send_sm_listPad%type
      index by binary_integer;
     type tPad is table of sm_send_sm_listPad%type
      index by binary_integer;
     type tPad is table of sm_send_sm_listPad%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_Pad out tPad
          v_Pad out tPad
          v_Pad out tPad
          v_Pad out tPad
          v_Pad out tPad
          v_Count out tCount
          ;
  end;
  /
  CREATE OR REPLACE PACKAGE BODY OW_SMPOW_SMP_PACKAGE
        is
     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_Pad out tPad
          v_Pad out tPad
          v_Pad out tPad
          v_Pad out tPad
          v_Pad out tPad
          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)
           and RowNum<;
           
       smcount number default ;
     begin
       for sm in sendsm_cur
       loop
           v_SerialNo(smcount):=smSerialNo;
           v_ServiceID(smcount):=smServiceID;
           v_SMContent(smcount):=smSMContent;
           v_SendTarget(smcount):=smSendTarget;
           v_Priority(smcount):=smPriority;
           v_RCompleteTimeBegin(smcount):=smRCompleteTimeBegin;
           v_RCompleteTimeEnd(smcount):=smRCompleteTimeEnd;
           v_RCompleteHourBegin(smcount):=smRCompleteHourBegin;
           v_RCompleteHourEnd(smcount):=smRCompleteHourEnd;
           v_RequestTime(smcount):=smRequestTime;
           v_RoadBy(smcount):=smRoadBy;
           v_SendTargetDesc(smcount):=smSendTargetDesc;
           v_FeeValue(smcount):=smFeeValue;
           v_Pad(smcount):=smPad;
           v_Pad(smcount):=smPad;
           v_Pad(smcount):=smPad;
           v_Pad(smcount):=smPad;
           v_Pad(smcount):=smPad;

From:http://tw.wingwit.com/Article/program/Oracle/201311/18179.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.