表group_user 字段 z1,z2 数据如下:
group_user
-------------------------
z1 z2
-------------------------
100 200
101 201
现在给Z1,Z2传递值,当表group_user中存在与其匹配的值时不执行任何操作;若不存在则插入这条数据
比如:
z1=100
z2=202执行语句得到:-------------------------
z1 z2
-------------------------
100 200
101 201
100 202这语句怎么写呢?
insert into group_user (z1,z2) values('100','202') where not exists(select z2 from group_user
where z1='100' and z2='202')上面代码是错的,大概意思这样,不会写了,请大家帮忙看一下哦
group_user
-------------------------
z1 z2
-------------------------
100 200
101 201
现在给Z1,Z2传递值,当表group_user中存在与其匹配的值时不执行任何操作;若不存在则插入这条数据
比如:
z1=100
z2=202执行语句得到:-------------------------
z1 z2
-------------------------
100 200
101 201
100 202这语句怎么写呢?
insert into group_user (z1,z2) values('100','202') where not exists(select z2 from group_user
where z1='100' and z2='202')上面代码是错的,大概意思这样,不会写了,请大家帮忙看一下哦
create or replace procedure p_insert(vz1 varchar2, vz2 varchar2) is
n_found number;
begin
select count(1) into n_found from group_user where z1 = vz1 and z2 = vz2;
if n_found > 0 then
insert into group_user(z1,z2) values (vz1, vz2);
end if;
end;然後每次調用該過程
begin
p_insert('100','202');
end;
已写入 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
不是的只能存储或者匿名块
SQL> edi
已写入 file afiedt.buf 1 merge into group_user a using(select &a z1,&b z2 from dual) b on(a.z1=b.z1 and a.z2=b.z2)
2 when not matched then
3* insert(a.z1,a.z2) values(b.z1,b.z2)
SQL> /
输入 a 的值: 100
输入 b 的值: 202
原值 1: merge into group_user a using(select &a z1,&b z2 from dual) b on(a.z1=b.z1 and a.z2=b.z2)
新值 1: merge into group_user a using(select 100 z1,202 z2 from dual) b on(a.z1=b.z1 and a.z2=b.z2)1 行已合并。SQL> commit
2 /提交完成。SQL> select * from group_user
2 / Z1 Z2
---------- ----------
100 200
101 201
100 202--过程
create or replace procedure p_in(v_z1 group_user.z1%type,v_z2 group_user.z2%type)
as
v_count number;
begin
select count(*) into v_count from group_user where z1=v_z1 and z2=v_z2;
if v_count=0 then
insert into group_user values(v_z1,v_z2);
commit;
else
dbms_output.put_line('已经存在此数据');
end if;
exception
when others then
dbms_output.put_line('参数格式问题');
end;
select '100','202' from dual where not exists(select 1 from group_user
where z1='100' and z2='202')
merge into 9i就有了吧