create or replace procedure test11(pname in varchar2, pno in varchar2) is -----sce---------------------不可逆反应 cursor c1 is select substr(b.name, 4) c1, substr(d.name, 4) c2 from newreaction a, enzyme b, newreaction c, enzyme d where a.id = b.id and c.id = d.id and b.type = 'enzyme' and d.type = 'enzyme' and a.pname = pname and c.pname = pname and a.proid = c.subid and a.pno = pno and c.pno = pno and a.pno = b.pno and c.pno = d.pno and (a.type = 'irreversible' or c.type = 'irreversible'); ---sce----------------------------可逆反应 cursor c2 is select substr(b.name, 4) c1, substr(d.name, 4) c2 from newreaction a, enzyme b, newreaction c, enzyme d where a.id = b.id and c.id = d.id and a.name <> c.name and b.type = 'enzyme' and d.type = 'enzyme' and a.pname = pname and c.pname = pname and a.pno = pno and c.pno = pno and a.pno = b.pno and c.pno = d.pno and (a.proid = c.subid or a.proid = c.proid or a.subid = c.subid or a.subid = c.proid) and (a.type = 'reversible' and c.type = 'reversible'); begin for rec in c1 loop insert into test11 values (rec.c1, rec.c2); end loop; for rec in c2 loop insert into test11 values (rec.c1, rec.c2); end loop; commit; end test11;
create or replace procedure test11(pname in varchar2, pno in varchar2) is
-----sce---------------------不可逆反应
cursor c1 is
select substr(b.name, 4) c1, substr(d.name, 4) c2
from newreaction a, enzyme b, newreaction c, enzyme d
where a.id = b.id
and c.id = d.id
and b.type = 'enzyme'
and d.type = 'enzyme'
and a.pname = pname
and c.pname = pname
and a.proid = c.subid
and a.pno = pno
and c.pno = pno
and a.pno = b.pno
and c.pno = d.pno
and (a.type = 'irreversible' or c.type = 'irreversible');
---sce----------------------------可逆反应
cursor c2 is
select substr(b.name, 4) c1, substr(d.name, 4) c2
from newreaction a, enzyme b, newreaction c, enzyme d
where a.id = b.id
and c.id = d.id
and a.name <> c.name
and b.type = 'enzyme'
and d.type = 'enzyme'
and a.pname = pname
and c.pname = pname
and a.pno = pno
and c.pno = pno
and a.pno = b.pno
and c.pno = d.pno
and (a.proid = c.subid or a.proid = c.proid or a.subid = c.subid or
a.subid = c.proid)
and (a.type = 'reversible' and c.type = 'reversible');
begin
for rec in c1 loop
insert into test11 values (rec.c1, rec.c2); end loop;
for rec in c2 loop
insert into test11 values (rec.c1, rec.c2);
end loop;
commit;
end test11;
Compilation errors for PROCEDURE SYS.TEST11Error: PL/SQL: ORA-04044: 此处不允许过程, 函数, 程序包或类型Line: 38Text: insert into test11 values (rec.c1, rec.c2);Error: PL/SQL: SQL Statement ignoredLine: 38Text: insert into test11 values (rec.c1, rec.c2);Error: PL/SQL: ORA-04044: 此处不允许过程, 函数, 程序包或类型Line: 42Text: insert into test11 values (rec.c1, rec.c2);Error: PL/SQL: SQL Statement ignoredLine: 42Text: insert into test11 values (rec.c1, rec.c2);