create or replace procedure p_demo(vname varchar2,vaddr varchar2)
as
n number;
vid number;
begin
select count(*) into n from table1 where name=vname;
if n>0 then
select id1 into vid from table1 where name=vname;
if vid is null then
return;
end if;
else
return;
end if;
select count(*) into n from table2 where id2=vid;
if n>0 then
return;
else
insert into table2 values(vid,vaddr);
end if;
commit;
end p_demo;
/
as
n number;
vid number;
begin
select count(*) into n from table1 where name=vname;
if n>0 then
select id1 into vid from table1 where name=vname;
if vid is null then
return;
end if;
else
return;
end if;
select count(*) into n from table2 where id2=vid;
if n>0 then
return;
else
insert into table2 values(vid,vaddr);
end if;
commit;
end p_demo;
/
v_id1 table1.id1%type;
v_name table1.name%type;
v_id2 table2.id2%type;
cursor c_id1 is
select id1
from table1
where name = v_name;
type refcursor is ref cursor return table2.address%type;
c_id2 refcursor;
begin
v_name := p_name;
for v_id1 in c_id1 loop
update table2
set id2 = v_id1
where id2 = v_id1;
if sql%notfound then
insert into table2 values(v_id1, p_add);
end if;
end loop;
end test;晕死,发现自己方法好笨,学习
(name varchar2,
address varchar2
) is
v_id1 varchar2(6);
v_count integer;
v_name varchar2(60);
v_address varchar2(100);
begin
v_name:=name;
v_address:=address;
select id1 into v_id1 from table1 where name=v_name;
if v_id1 is null then
dbms_output.put_line('为空');
else
select count(*) num into v_count from table2 where id2=v_id1;
if v_count=0 then
insert into table2(id2,address) values(v_id1,v_address);
commit;
end if;
end if;
end proc1;
C_address varchar2
)
as
tmp_id1 varchar2(10);
tmp_address varchar2(20);
begin
select nvl(max(a1),'0') into tmp_id1 from a where a2 = C_name;
if tmp_id1 = '0' then
return;
end if;
select nvl(max(b2),'0') into tmp_address from b where b1=tmp_id1;
if tmp_address = '0' then
insert into b(b1,b2)values(C_name,C_address);
commit;
else
return;
end if;
exception
when others then
rollback;
return;
end;
insert into b(b1,b2)values(C_name,C_address);
——>
insert into b(b1,b2)values(tmp_id1,C_address);
2 c_name varchar2(10);
3 c_address varchar2(20);
4 begin
5 c_name:='赵六';
6 c_address:='广州';
7 prc_a(c_name,c_address);
8 end;
9 /PL/SQL procedure successfully completed
SQL> select b1,b2 from b;B1 B2
---------- ----------
4 广州
1 上海
2 北京
3 天津SQL>
我将你的代码改成:
create or replace procedure prc_a(C_name varchar2,
C_address varchar2
)
as
tmp_id1 varchar2(10);
tmp_address varchar2(20);
begin
select nvl(max(id1),'0') into tmp_id1 from table1 where id1 = C_name;
if tmp_id1 = '0' then
return;
end if;
select nvl(max(id2),'0') into tmp_address from table2 where id2=tmp_id1;
if tmp_address = '0' then
insert into table2 values(tmp_id1,C_address);
commit;
else
return;
end if;
exception
when others then
rollback;
return;
end prc_a;执行之:
declare
c_name varchar2(10);
c_address varchar2(20);
begin
c_name:='赵六';
c_address:='广州';
prc_a(c_name,c_address);
end;
/
但是执行结果是table1中有(4,'赵六')这条记录,而table2中却没有(4,'广州'),不知道为什么