oracle中的merge函数 转

oracle中的merge函数
例如：table1( col_1a,col_1b,col_1c )
          table2( col_2a,col_2b,col_2c )

我想update table1，当col_1a=col_2a时将col_2b和col_2c赋值给col_1b和col_1c。
merge into table a using table b
on a.col=b.col
when matched then
update set a.col=b.col
a.col1=b.col1
很简单就解决了

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