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

VB,VBA 利用数组向ORACLE传参

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

起因:

         在对多条数据进行更新操作的时候,会频繁的调用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"

 

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.

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.

Avatar_small
click here 说:
2024年12月08日 17:41

Windows 10 is the latest version

Avatar_small
corrida-langueux 说:
2024年12月08日 17:42

I am happy to find this post very useful for me, as it contains lot of information. I always prefer to read the quality content and this thing I found in you post. Thanks for sharing 

Avatar_small
website 说:
2024年12月08日 18:19

I like your website. Your website provides good information which helps me. Thanks for sharing your blog. Now I'm coming to my place and I can not do it. I can not do it.

Avatar_small
evokorea 说:
2024年12月08日 18:21

The bounce house has numerous synonyms. It is also called an inflatable castle, CITs (closed inflatable trampolines), jumping castle, bouncing house, jumpers, bouncy house, jumping castle, moonwalks, bouncy castle, or moon bounce. This is an inflatable building and structure and related items rented out for functions, church festivals, village fees, schools, etc. 

Avatar_small
View data 说:
2024年12月08日 18:22

KIMKOO is Specialized in Manufacturing Mattress Machines. You Can Find the Most Complete Mattress Machines and Euipments From Here.

Avatar_small
read more 说:
2024年12月08日 18:25

now for inspirational entertainment. Lewabo is an online Christian inspirational movie streaming website. Enjoy Action, Thrillers, Romance, Family drama, Kids, Faith, Suspense, Comedy. An entertainment platform for the whole family. Enjoy hundreds of inspiring and uplifting films, african films, american films, nigerian nollywood films, south african films, christian movies. Lewabo is good content entertainment.

Avatar_small
get more info 说:
2024年12月08日 18:26

I can see that you are an expert at your field! I am launching a website soon, and your information will be very useful for me.. Thanks for all your help and wishing you all the success in your business. 

Avatar_small
casinohunter24 说:
2024年12月08日 18:27

KIMKOO is Specialized in Manufacturing Mattress Machines. You Can Find the Most Complete Mattress Machines and Euipments From Here.

Avatar_small
totodiya 说:
2024年12月08日 18:31

thank you for penning this

Avatar_small
securitytoto 说:
2024年12月08日 18:31

We believe in customer satisfaction. Our Leather Jackets are delicately and uniquely tailored, and the comfort of our customers is always prioritizing. We guarantee the quality of our product and take full responsibility to satisfy our customer’s needs

Avatar_small
website 说:
2024年12月08日 18:32

Good day very cool web site!! Man .. Excellent .. Superb .. I’ll bookmark your web site and take the feeds additionally…I am glad to find a lot of useful information right here in the put up, we want develop more strategies in this regard, thanks for sharing.

Avatar_small
meoghyugo 说:
2024年12月08日 18:32

First off when you think about buying a can add to the life of the crossbow

Avatar_small
References 说:
2024年12月08日 18:35

I am so happy to read this. This is the kind of manual that needs to be given and not the random misinformation like on the other blogs..I am an entrepreneur.  The Bullpen is a co working space. We provide office for rent in DHA Karachi. Created to allow you to conduct business and nurture connections vital to your success. We guide you with the best way to rent a

Avatar_small
information 说:
2024年12月08日 18:36

I am grateful on getting such wonderful information. I also wrote a blog on How to download AOL Desktop Gold . This software provides enhanced security to the emails of the users. If you have any problem with this application, please contact us to get instant and legitimate support from our team.

Avatar_small
Find out 说:
2024年12月08日 18:36

I finally found what I was looking for! I’m so happy. Your article is what I’ve been looking for for a long time. I’m happy to find you like this. Could you visit my website if you have time? I’m sure you’ll find a post of interest that you’ll find interesting.

Avatar_small
here 说:
2024年12月08日 18:38

Hey what a brilliant post I have come across and believe me I have been searching out for this similar kind of post for past a week and hardly came across this. Thank you very much and will look for more postings from you.

Avatar_small
information 说:
2024年12月08日 18:39

First off when you think about buying a crossbow do not get sticker shock when you see a hunting crossbow selling for upwards of $2,000. Prices vary depending on the styles, make, model, construction, weight, accessories, safety features and materials that they are made from. Crossbows can be intimidating but they are also a fun hunting accessory. Price also plays an important part in the quality and performance and can add to the life of the crossbow

Avatar_small
온카맨 说:
2024年12月08日 18:39

KIMKOO is Specialized in Manufacturing Mattress Machines. You Can Find the Most Complete Mattress Machines and Euipments From Here.

Avatar_small
View data 说:
2024年12月08日 18:43

now for inspirational entertainment. Lewabo is an online Christian inspirational movie streaming website. Enjoy Action, Thrillers, Romance, Family drama, Kids, Faith, Suspense, Comedy. An entertainment platform for the whole family. Enjoy hundreds of inspiring and uplifting films, african films, american films, nigerian nollywood films, south african films, christian movies. Lewabo is good content entertainment.

Avatar_small
totomtfriends 说:
2024年12月08日 18:44

Design and implementation of interior decoration, which includes parts such as design, construction and implementation of kitchen cabinets, wardrobes, Corian plates, Marmonite, granite sinks, residential, office and commercial renovation.

Avatar_small
good data 说:
2024年12月08日 18:45

remarkable article. You have got superbly articulated it. Readers revisit simplest if they observed something useful. So the middle system is to offer fee to the readers. Additionally, name could be very important.

Avatar_small
토토사이트 说:
2024年12月08日 18:54

The bounce house has numerous synonyms. It is also called an inflatable castle, CITs (closed inflatable trampolines), jumping castle, bouncing house, jumpers, bouncy house, jumping castle, moonwalks, bouncy castle, or moon bounce. This is an inflatable building and structure and related items rented out for functions, church festivals, village fees, schools, etc. 


登录 *


loading captcha image...
(输入验证码)
or Ctrl+Enter