declare
tablename varchar2(5);
type empcursorref is ref cursor ;
type deptcursorref is ref cursor;
empcursor empcursorref;
deptcursor deptcursorref;
emprow emp%rowtype;
deptrow dept%rowtype;
begin
tablename:='&x';
case tablename
when 'emp' then
open empcursor for select * from emp where sal>(select avg(sal) from emp where depeno='10');
loop
fetch empcursor into emprow;
exit when empcursor%notfound;
dbms_output.put_line(emprow.ename||' ||emprow.empno);
end loop;
close empcursor;
else
open deptcursor for select deptno, count(*) count from emp group by deptno;
loop
fetch deptcursor into deptrow;
exit when deptcursor%notfound;
dbms_output.putline(deptcursor.deptno||' '||deptcursor.count);
end loop;
close deptcursor;
end case;
end;
那位大侠帮我看一下 表 为 emp和deptno
tablename varchar2(5);
type empcursorref is ref cursor ;
type deptcursorref is ref cursor;
empcursor empcursorref;
deptcursor deptcursorref;
emprow emp%rowtype;
deptrow dept%rowtype;
begin
tablename:='&x';
case tablename
when 'emp' then
open empcursor for select * from emp where sal>(select avg(sal) from emp where depeno='10');
loop
fetch empcursor into emprow;
exit when empcursor%notfound;
dbms_output.put_line(emprow.ename||' ||emprow.empno);
end loop;
close empcursor;
else
open deptcursor for select deptno, count(*) count from emp group by deptno;
loop
fetch deptcursor into deptrow;
exit when deptcursor%notfound;
dbms_output.putline(deptcursor.deptno||' '||deptcursor.count);
end loop;
close deptcursor;
end case;
end;
那位大侠帮我看一下 表 为 emp和deptno
SQL> create or replace procedure pro_select(table_name_in in varchar2)
2 as
3 begin
4 if table_name_in='emp' then
5 for v_e1 in (select ename,empno from emp
6 where sal>(
7 select avg(sal) from emp
8 where deptno='10'))
9 loop
10 dbms_output.put_line(v_e1.ename||' '||v_e1.empno);
11 end loop;
12 else
13 for v_e2 in (select deptno,count(*) cnt from emp group by deptno)
14 loop
15 dbms_output.put_line(v_e2.deptno||' '||v_e2.cnt);
16 end loop;
17 end if;
18 exception
19 when others then
20 dbms_output.put_line(sqlerrm);
21 end pro_select;
22 /
Procedure created
SQL> exec pro_select('emp');
PL/SQL procedure successfully completed
SQL> set serveroutput on;
SQL> exec pro_select('emp');
JONES 7566
SCOTT 7788
KING 7839
FORD 7902
PL/SQL procedure successfully completed
SQL> exec pro_select('dept');
10 3
20 5
30 6
PL/SQL procedure successfully completed
SQL>
--for循环类似游标的处理,避免了使用游标带来的开销,
--练习游标,另外找例子吧
SQL> select ename,empno from emp
2 where sal>(
3 select avg(sal) from emp
4 where deptno='10')
5 /
ENAME EMPNO
---------- -----
JONES 7566
SCOTT 7788
KING 7839
FORD 7902
SQL> select deptno,count(*) cnt from emp group by deptno;
DEPTNO CNT
------ ----------
10 3
20 5
30 6