先把sql中pubs数据库的authors、titles、titleauthor三张表传到oracle中,执行下面的饿语句,总是出现重复的记录,但如果去掉内层的for循环,数据又不准确,谁知道是什么问题呢?
===========================
declare
cursor yb is select au_id from authors;
a authors.au_id%type;
begin
open yb;
loop
exit when yb%notfound;
fetch yb into a;
dbms_output.put_line('===='||a||'=====');
declare
cursor yb1(b authors.au_id%type) is select title,price from authors a,titles b,titleauthor c where
b.title_id=c.title_id and c.au_id=a.au_id and a.au_id=b;
c titles.title%type;
d titles.price%type;
begin
open yb1(a);
loop
exit when yb1%notfound;
fetch yb1 into c,d;
dbms_output.put_line(c||' '||d);
end loop;
close yb1;
end;
end loop;
end;
===========================
declare
cursor yb is select au_id from authors;
a authors.au_id%type;
begin
open yb;
loop
exit when yb%notfound;
fetch yb into a;
dbms_output.put_line('===='||a||'=====');
declare
cursor yb1(b authors.au_id%type) is select title,price from authors a,titles b,titleauthor c where
b.title_id=c.title_id and c.au_id=a.au_id and a.au_id=b;
c titles.title%type;
d titles.price%type;
begin
open yb1(a);
loop
exit when yb1%notfound;
fetch yb1 into c,d;
dbms_output.put_line(c||' '||d);
end loop;
close yb1;
end;
end loop;
end;
declare
cursor yb is select au_id from authors;
a authors.au_id%type;
begin
open yb;
loop
fetch yb into a;
exit when yb%notfound;
dbms_output.put_line('===='||a||'=====');
declare
cursor yb1(b authors.au_id%type) is select title,price from authors a,titles b,titleauthor c where
b.title_id=c.title_id and c.au_id=a.au_id and a.au_id=b;
c titles.title%type;
d titles.price%type;
begin
open yb1(a);
loop
fetch yb1 into c,d;
exit when yb1%notfound;
dbms_output.put_line(c||' '||d);
end loop;
close yb1;
end;
end loop;
end;
/