大剑
天天加班,哪有时间写博客 ---- 专注于ERP软件开发技术，关注Oracle相关技术

VB,VBA 利用数组向ORACLE传参

glave posted @ 2009年2月03日 23:40 in ORACLE with tags AddTable VB VBA oracle 数组传参 , 4198 阅读

起因:

         在对多条数据进行更新操作的时候,会频繁的调用INSERT和UPDATE语句,这样导致运行速度会很慢.

         ORACLE数据库提供了MERGE方法,大大加快了处理速度,而且只需做一次操作,但是如何将大批量数据传递给ORACLE呢?

解决办法:

         采用数组的形式,数据的每一列即为一个数组.

实例:   

  1. '定义部分
  2.  Const ORAPARM_INPUT = 1
  3.  Const ORATYPE_VARCHAR2 = 1
  4.  Dim OraPArray(4)
  5.  Dim lArrayIndex As Long
  6.  
  7. '数组大小
  8.  OraDB.Parameters.Add "inSize", 4, ORAPARM_INPUT
  9. '定义数组
  10. OraDB.Parameters.AddTable "HPL", ORAPARM_INPUT, ORATYPE_VARCHAR2, lDays, 5
  11.     ' caltype
  12. OraDB.Parameters.AddTable "CALTYPE", ORAPARM_INPUT, ORATYPE_VARCHAR2, lDays, 6
  13.     ' ymd
  14. OraDB.Parameters.AddTable "YMD", ORAPARM_INPUT, ORATYPE_VARCHAR2, lDays, 10
  15.     ' wkkbn :1,2
  16. OraDB.Parameters.AddTable "WKKBN", ORAPARM_INPUT, ORATYPE_VARCHAR2, lDays, 1
  17.  
  18. Set OraPArray(0) = OraDB.Parameters("HPL")
  19. Set OraPArray(1) = OraDB.Parameters("CALTYPE")
  20. Set OraPArray(2) = OraDB.Parameters("YMD")
  21. Set OraPArray(3) = OraDB.Parameters("WKKBN")
  22.  
  23. '数组赋值
  24. For li_row = 1 To 10000 
  25.         OraPArray(0).put_Value ls_Hpl, lArrayIndex
  26.         OraPArray(1).put_Value ls_calType, lArrayIndex
  27.         OraPArray(2).put_Value ls_ymd, lArrayIndex
  28.         OraPArray(3).put_Value ls_wkkbn, lArrayIndex
  29.        
  30.         lArrayIndex = lArrayIndex + 1
  31. Next
  32.      
  33.     ls_sql = " MERGE INTO F_ACTY.M0030 m0030 " & _
  34.              " USING (SELECT  :CALTYPE  caltype," & _
  35.                             " :YMD   ymd, " & _
  36.                             " :WKKBN  wkkbn, " & _
  37.                             " :HPL hpl " & _
  38.                      "FROM DUAL ) m0030b " & _
  39.              " ON (m0030.caltype = m0030b.caltype  and " & _
  40.                     " m0030.ymd =  m0030b.ymd and " & _
  41.                     " m0030.hpl =  m0030b.hpl ) " & _
  42.             " WHEN MATCHED THEN Update SET m0030.wkkbn = m0030b.wkkbn " & _
  43.             " WHEN NOT MATCHED THEN INSERT ( m0030.caltype, m0030.ymd, m0030.hpl,m0030.wkkbn ) " & _
  44.                                   " Values ( m0030b.caltype , m0030b.ymd ,m0030b.hpl , m0030b.wkkbn ) "
  45.     'Oracle DB处理
  46.     OraDB.ExecuteSQL (ls_sql)
  47.    
  48.     '消除
  49.     OraDB.Parameters.Remove "CALTYPE"
  50.     OraDB.Parameters.Remove "YMD"
  51.     OraDB.Parameters.Remove "WKKBN"
  52.     OraDB.Parameters.Remove "HPL"

 

Small_feed 评论 (2)
1
[回复]
Avatar_small
www.aka.ms 说:
2023年7月31日 23:50

Puede generar rápidamente dichas copias de seguridad utilizando aka.ms/phonelinkQRC , que sincroniza directamente su teléfono inteligente con su computadora y ayuda a mantener la información sincronizada al transferir ciertas modificaciones que permite de uno a otro a través de Microsoft Phone Link. www.aka.ms La aplicación Phone Link le permite acceder a cualquier cosa en su teléfono Android desde su escritorio. Puede vincular su teléfono Android a una computadora/laptop con Windows 10/11 y permitirle ver y responder mensajes de texto de Android.

2
[回复]
Avatar_small
SCERT Sikkim 3rd Cl 说:
2023年8月01日 18:42

SCERT Sikkim Follows NCERT Curriculum These Textbooks are Updated as per the Syllabus Prescribed by SCERT Sikkim. Students of 3rd Class Should follow Prescribed Textbooks while Preparing for Exam.Our SCERT Sikkim 3rd Class Book 2024 Team Refer to the Respective Subject Textbook while Preparing the Final Important questions. Students Best Practice Study Materiel about Textbooks are the Fact that they are so Comprehensible that it does not require the aid of a Subject Literate.SCERT Sikkim once Publishes the Sikkim Elementary School Textbooks 2024 Other Study materials on the official web site, we will update the Information on this page.


登录 *


loading captcha image...
(输入验证码)
or Ctrl+Enter
搜索
分类
热门文章
功能

Host by is-Programmer.com | Power by Chito 1.3.3 beta

沪ICP备07005989号