set serveroutput on;
declare
i number :=1;
t_emp1 number;
t_emp2 number;
cursor tes_t1 is select groupid from SYSTEMGROUPMEMBERSHIP group by groupid having groupid not in (1,2);
begin
open tes_t1;
loop
fetch tes_t1 into t_emp1;
exit when tes_t1%notfound;
dbms_output.put_line(i||'aaaaaa'||t_emp1);
i:=i+1;
cursor tes_t2 is select userid from SYSTEMGROUPMEMBERSHIP where groupid=t_emp1;
begin
open tes_t2;
loop
fetch tes_t2 into t_emp2;
exit when tes_t2%notfound;
dbms_output.put_line(t_emp1);
end loop;
close tes_t2;
commit;
end;
end loop;
close tes_t1;
commit;
end;代码如上内层根据外层循环查询结果继续循环操作数据怎么写 初学着
declare
i number :=1;
t_emp1 number;
t_emp2 number;
cursor tes_t1 is select groupid from SYSTEMGROUPMEMBERSHIP group by groupid having groupid not in (1,2);
begin
open tes_t1;
loop
fetch tes_t1 into t_emp1;
exit when tes_t1%notfound;
dbms_output.put_line(i||'aaaaaa'||t_emp1);
i:=i+1;
cursor tes_t2 is select userid from SYSTEMGROUPMEMBERSHIP where groupid=t_emp1;
begin
open tes_t2;
loop
fetch tes_t2 into t_emp2;
exit when tes_t2%notfound;
dbms_output.put_line(t_emp1);
end loop;
close tes_t2;
commit;
end;
end loop;
close tes_t1;
commit;
end;代码如上内层根据外层循环查询结果继续循环操作数据怎么写 初学着
declare
i number :=1;
t_emp1 number;
t_emp2 number;
strsql varchar2(4000); --新增的
cursor tes_t1 is select groupid from SYSTEMGROUPMEMBERSHIP
group by groupid having groupid not in (1,2);
begin
open tes_t1;
loop
fetch tes_t1 into t_emp1;
exit when tes_t1%notfound;
dbms_output.put_line(i||'aaaaaa'||t_emp1);
i:=i+1;
strsql := 'select userid from SYSTEMGROUPMEMBERSHIP where groupid='''||t_emp1||'''';
open tes_t2 for strsql;
loop
fetch tes_t2 into t_emp2;
exit when tes_t2%notfound;
dbms_output.put_line(t_emp1);
end loop;
close tes_t2;
commit;
end loop;
close tes_t1;
commit;
end;
Error on line 0
declare
i number :=1;
t_emp1 number;
t_emp2 number;
strsql varchar2(ORA-06550: 第 17 行, 第 10 列:
PLS-00201: 必须声明标识符 'TES_T2'
ORA-06550: 第 17 行, 第 5 列:
PL/SQL: Statement ignored
ORA-06550: 第 19 行, 第 13 列:
PLS-00201: 必须声明标识符 'TES_T2'
ORA-06550: 第 19 行, 第 7 列:
PL/SQL: SQL Statement ignored
ORA-06550: 第 20 行, 第 17 列:
PLS-00201: 必须声明标识符 'TES_T2'
ORA-06550: 第 20 行, 第 7 列:
PL/SQL: Statement ignored
ORA-06550: 第 23 行, 第 11 列:
PLS-00201: 必须声明标识符 'TES_T2'
ORA-06550: 第 23 行, 第 5 列:
PL/SQL: SQL Statement ignored
--忘了定义下游标,在declare中加上
tes_t2 sys_refcursor;
declare
i number :=1;
t_emp1 number;
t_emp2 number;
strsql varchar2(4000); --新增的
tes_t2 sys_refcursor;
cursor tes_t1 is select groupid from SYSTEMGROUPMEMBERSHIP
group by groupid having groupid not in (1,2);
begin
open tes_t1;
loop
fetch tes_t1 into t_emp1;
exit when tes_t1%notfound;
dbms_output.put_line(i||'aaaaaa'||t_emp1);
i:=i+1;
strsql := 'select userid from SYSTEMGROUPMEMBERSHIP where groupid='''||t_emp1||'''';
open tes_t2 for strsql;
loop
fetch tes_t2 into t_emp2;
exit when tes_t2%notfound;
dbms_output.put_line(t_emp1);
end loop;
close tes_t2;
commit;
end loop;
close tes_t1;
commit;
end;
for x in(select groupid from SYSTEMGROUPMEMBERSHIP group by groupid having groupid not in (1,2))
loop
dbms_output.put_line('groupid:'||x.groupid);
for y in(select userid from SYSTEMGROUPMEMBERSHIP where groupid=x.groupid)
loop
dbms_output.put_line('userid:'||y.userid);
end loop;
end loop;
end;