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]