Oracle中的Merge函数(批量更新/删除)

所有的MIS系统都存在一个同样的需求，就是对于特定的数据，在一次批量操作过程中，如果数据已经存在，则对存在的数据按照现有情况进行更新，如果不存在，则需要加入数据库。这时，我们就可以考虑采用 Oracle 的 MERGE 函数，其具体用法如下：

 

---

 

MERGE INTO [your table-name] [rename your table here]

    USING

        (

            [write your query here]

        )[rename your query-sql and using just like a table]

    ON

        ([conditional expression here] AND [...]...)

    WHEN

        MATHED

    THEN

        [here you can execute some update sql or something else ]

    WHEN

        NOT MATHED

    THEN

        [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 语句。

 

这时，如果你需要在你的 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();

}