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

DEFINE TABLE : SCORE : using for save the students' score informations STUDENTS : the base information of students DEFINE COLUMNS : STUNO : the students' ID in theUniversity STUNAME : students' name COURSENAME : course name COURSESCORE : t

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

 

 

[1] [2]