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

);