一个MERGE INTO 的例子

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

);

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7916042/viewspace-908156/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7916042/viewspace-908156/