/* Formatted on 2010/12/15 09:59 (Formatter Plus v4.8.7) */
case
select count(*) as t1 from sjz_emp where empcode='00000000' and schoolid='30'
when 0
then
INSERT INTO sjz_emp
(ID, empcode, dptcode, empname, empid, schoolid, emppwd
)
VALUES (0, '2', '2', '2', '2', '2', '2' )
else
update sjz_emp set dptcode='3',empname='3',empid='3',emppwd='3'
where empcode='00000000' and schoolid='30'
end
case
select count(*) as t1 from sjz_emp where empcode='00000000' and schoolid='30'
when 0
then
INSERT INTO sjz_emp
(ID, empcode, dptcode, empname, empid, schoolid, emppwd
)
VALUES (0, '2', '2', '2', '2', '2', '2' )
else
update sjz_emp set dptcode='3',empname='3',empid='3',emppwd='3'
where empcode='00000000' and schoolid='30'
end
--给个merge into的例子
SQL> ed
已写入 file afiedt.buf 1 merge into group_user g
2 using (select 100 z1,202 z2 from dual) tb
3 on(tb.z1=g.z1 and tb.z2=g.z2)
4 when not matched then
5 insert
6* values (tb.z1,tb.z2)
SQL> /1 行已合并。SQL> select * from group_user; Z1 Z2
---------- ----------
100 200
101 201
100 202
----直接用这个,直接明了。。
update sjz_emp
set dptcode = '3', empname = '3', empid = '3', emppwd = '3'
where empcode = '00000000'
and schoolid = '30';
IF (SQL%NOTFOUND) THEN
INSERT INTO sjz_emp
(ID, empcode, dptcode, empname, empid, schoolid, emppwd)
VALUES
(0, '2', '2', '2', '2', '2', '2');
end if;