`

Oracle中的Merge函数

OracleSQLJDBC工作 
阅读更多
所有的MIS系统都存在一个同样的需求，就是对于特定的数据，在一次批量操作过程中，如果数据已经存在，则对存在的数据按照现有情况进行更新，如果不存在，则需要加入数据库。这时，我们就可以考虑采用 Oracle 的 MERGE 函数，其具体用法如下：
sql 代码
  1. MERGE INTO [your table-name] [rename your table here]   
  2.     USING   
  3.         (   
  4.             [write your query here]   
  5.         )[rename your query-sql and using just like a table]   
  6.     ON  
  7.         ([conditional expression here] AND [...]...)   
  8.     WHEN  
  9.         MATHED   
  10.     THEN  
  11.         [here you can execute some update sql or something else ]   
  12.     WHEN  
  13.         NOT MATHED   
  14.     THEN  
  15.         [execute something else here ! ]  
 
 
下面我再进行详细的说明：
上述代码格式中的加粗字体表示为 Oracle 关键字，[]以及其中的文字均是说明，在实际使用中不应有 [ words ] 出现。要注意（）[圆括号]也是程序的组成部分。
为了能够使问题与实际问题更加贴切，不妨假设我们现在要给计算机系某个班的学生批量录入学生成绩。但是，录入时，如果学生的成绩已经存在时，老师只想对成绩进行修改，而如果成绩不存在则直接添加到库中。我们就老师的这些需求来构造一个执行语句。
 
sql 代码
  1. DEFINE TABLE :    
  2.     SCORE :  using for save the students' score informations   
  3.     STUDENTS : the base information of students   
  4. DEFINE COLUMNS :    
  5.     STUNO : the students' ID in the University   
  6.     STUNAME : students' name  
  7.     COURSENAME : course name  
  8.     COURSESCORE : the study-results of the reference course   
  9.     CLASSNAME : where the students study in  
  10.     STUGRADE : the students grade   
  11.     TERMNAME : the term which the reference course studied   
  12. NOW BEAGIN TO WRITE DOWN THE STATEMENT HERE BLOW THIS LINE !    
  13.     
  14. MERGE INTO SCORE S   
  15.     USING   
  16.         (   
  17.             SELECT A.*,B.*,? MYSCORE FROM SCORE A,STUDENT B   
  18.             WHERE  
  19.                 A.CLASSNO=? AND A.GRADE=?    
  20.                 AND A.TERMNAME=? AND A.COURSENAME=?   
  21.                 A.STUNO=B.STUNO(+)   
  22.         )X   
  23.     ON  
  24.         (S.STUNO=X.STUNO)   
  25.     WHEN  
  26.         MATHED   
  27.     THEN  
  28.         UPDATE SET COURSESCORE=X.MYSCORE   
  29.     WHEN  
  30.         NOT MATHED   
  31.     THEN  
  32.         INSERT  
  33.             (   
  34.                 STUNO,STUNAME,COURSENAME,COURSESCORE,   
  35.                 CLASSNAME,STUGRADE,TERMNAME   
  36.             )   
  37.         VALUES  
  38.             (   
  39.                 X.STUNO,X.STUNAME,X.COURSENAME,X.MYSCORE,   
  40.                 X.CLASSNAME,X.STUGRADE,X.TERMNAME   
  41.             );   
 
注意到 MERGE 语句在最后的“；”（分号），这仅仅代表 MERGE 为一条完整的 SQL 语句。同时，要说明一下 USING 语句下方的 SQL 语句。这个语句仅仅是为了给后面语句的执行做准备性的工作，因此，如果你需要的数据仅仅是通过参数传入的那些值的话你就不需要再利用传入进来的参数在重新从库中查询。在 Oracle 的系统表中，有张 Dual 表，这样，你便可以使用 “select [your arguments] from dual ”的方式来构建这里的 SQL 语句，其中 [your arguments] 是你得到的一系列的参数，由于Dual表是系统表，因此可以大幅提升SQL的执行效率。
 
这时，如果你需要在你的 Java 程序中使用上述方法执行相应操作，则仅需要将其放入一个 for 循环中即可。由于是批量更新数据，因此，如果你不想对中间出现异常的数据进行提交，导致数据的不完整，则可以考虑使用 Java 的事务回滚机制。具体示例代码如下：
 
java 代码
  1. public yourMethod(statement,...){   
  2.     try{   
  3.         Connection conn=...;   
  4.         PreparedStatement ps=...;   
  5.         Resultset rs=...;   
  6.         conn.setAutoCommit(false);   
  7.         for(int i=0;i<...;i++){   
  8.             //add your code here !    
  9.             ......   
  10.             ps.addBatch();   
  11.         }   
  12.         ps.executeBatch();   
  13.         conn.commit();   
  14.     }catch(Exception e){   
  15.         try{   
  16.             conn.rollback();   
  17.         }catch(Exception el){}   
  18.     }   
  19. }  
 
这时，你会发现，在代码中直接使用 Merge 时，代码会变的非常复杂，首先是 SQL 的拼接变得非常复杂，接下来便是程序写完后的查错。因此，自然而然就会想到使用存储过程。接下来，我们来看看如何使用存储过程实现 Merge 调用过程。
 
Oracle 存储过程定义格式如下：
 
sql 代码
  1.     
  2. CREATE OR REPLACE PROCEDURE PRO_YOUR_PROCEDURE (   
  3.     ELEMENT_01     IN      ELEMENT_TYPE,  --COMMENTS   
  4.      .......       ....        .....            ....   
  5.     ELEMENT_0S     OUT     ELEMENT_TYPE,  --COMMENTS   
  6.       ....         ...         ...           ....   
  7. )   
  8. AS  
  9.     ARGUMENT_01    ARGUMENT_TYPE(ARGUMENT_RANGE);   
  10.     ...................   
  11. BEGIN  
  12.     MERGE INTO YOUR_TABLE_NAEM [RENAEM_YOUR_TABLE_HERE]   
  13.     --AND YOUR CODE HERE !   
  14. END;   
  15. EXCEPTION   
  16. WHEN  
  17.     OTHERS   
  18. THEN  
  19.     RAISE_APPLICATION_ERROR(-20003,[YOUR EXCEPITON MESSAGE HERE !]);   
  20.     
  21. END;   
  22. COMMIT;--IF YOUR WANT , JUST DO SO !    
  23. END PRO_YOUR_PROCEDURE;  
其中，[RAISE_APPLICATION_ERROR(-20003,[YOUR EXCEPITON MESSAGE HERE !]);]中的“-20003”是 Oracle 提供的用于用户进行错误自定义的扩充代码。其值可以随便定义，但是也有范围： -20000 到-20999的负整数。
 
接下来就是如何来在 Java 程序中调用你的存储过程。Oracle为了方便开发人员调用其存储过程，开发了一个 [ OracleCallableStatement ]  位于 oracle.jdbc 包内。
 
核心代码如下：
 
java 代码
  1. OracleCallableStatement cal = null;   
  2. cal=(OracleCallableStatement)conn.getMetaData().getConnection().prepareCall("call PRO_......");   
  3. ........   
  4. .............   
  5. .......   
  6. for(………………){   
  7.        …………   
  8.     cal.setDouble(i,ARGUMENTS);   
  9.     …………   
  10. 　　cal.executeUpdate();   
  11. }  
从中可以看出，OracleCallableStatement对象事实上承担了PreparedStatement对象的使命，当然如果需要的话，能够支持事务回滚是个不错的主意，关于这个问题，可以参考上面直接使用 Merge 函数的 Java 代码。
 

相关推荐

Magicbox
Global site tag (gtag.js) - Google Analytics