declare type tab_names is table of varchar2(20) index by binary_integer; tab_name tab_names; coun number; str varchar2(100); begin select table_name bulk collect into tab_name from user_tables; for i in tab_name.first..tab_name.last loop str:='select count(*) from '||tab_name(i); execute immediate str into coun; dbms_output.put_line(tab_name(i)||'..........'||coun); end loop; end;
create or replace function x return varchar2 is type t_array is table of number index by binary_integer; type tarray is table of varchar2(10) index by binary_integer; ta t_array; tar tarray; re varchar2(10); n number; na varchar2(10); begin select id bulk collect into ta from (select id,name from xx order by id) group by id; for i in ta.First..ta.last loop dbms_output.put(ta(i)||''); select name bulk collect into tar from xx where id=ta(i); for i in tar.first..tar.last loop dbms_output.put(tar(i)); end loop; dbms_output.put_line(''); end loop; return re; end;
select deptno,max(sal) sal1,max(decode(t,2,sal)) sal2,min(sal) sal3 from (select sal,deptno,t from (select empno,ename,sal,row_number() over(partition by deptno order by sal desc) t,deptno from emp) e where e.t<=3) group by deptno
select max(nn.team) team,min(nn.y) B,max(nn.y)+1 E from (select n2.team,n2.y from (select * from nba) n1 join (select * from nba) n2 on n1.team=n2.team where n1.y=n2.y+1) nn group by (nn.y-rownum) order by B
深色：已答题 浅色：未答题