我尝试循环一些表并运行select,如下所示:
set serveroutput on
declare
type tables_names is table of varchar2(30);
type selectTable is table of varchar2(30);
tName tables_names;
sTableName selectTable;
begin;
tName := tables_names('PERIOD','SETTING','RAP','LOG');
sTableName := selectTable('m_table1','m_table2','m_table3','m_table4','m_table5');
for i in 1..tName.count loop
for j in 1..sTableName.count loop
select col10, count(*) from user.sTableName(j)
where table_name = tName(i) group by col10;
end loop;
end loop;
end;
我收到错误:PL / SQL:ORA-00933。
您能告诉我如何正确运行PL / SQL过程以显示选择的结果吗?
更新:查找结果
通常,要实现此目的,我需要在select的下面运行:
select column_name,
count(*) as countColumn
from user.m_table1 where table_name = 'PERIOD' group by column_name;
select column_name,
count(*) as countColumn
from user.m_table2 where table_name = 'PERIOD' group by column_name;
Oracle抱怨(ORA-00933)命令未正确结束。这可能是因为BEGIN后面有分号;另外,您缺少INTO子句。
我不确定m_table1,m_table2,...与哪个PERIOD,SETTING,...相对?这些是表名?那么其他那些值是什么?
无论如何:这是一个示例,显示了如何执行类似的操作-计算表中的行。尝试根据您的情况进行调整,或者-可能-添加更多信息,以便我们知道您在做什么。
SQL> set serveroutput on
SQL> declare
2 tname sys.odcivarchar2list := sys.odcivarchar2list();
3 l_cnt number;
4 l_str varchar2(200);
5 begin
6 tname := sys.odcivarchar2list('EMP', 'DEPT');
7
8 for i in 1 .. tname.count loop
9 l_str := 'select count(*) from ' || tname(i);
10 execute immediate l_str into l_cnt;
11 dbms_output.put_line(tname(i) ||': '|| l_cnt);
12 end loop;
13 end;
14 /
EMP: 14
DEPT: 4
PL/SQL procedure successfully completed.
SQL>
[编辑:添加了GROUP BY选项]
干得好; 由于EMP和DEPT共享DEPTNO列,因此我选择了GROUP BY列。
SQL> declare
2 tname sys.odcivarchar2list := sys.odcivarchar2list();
3 type t_job is record (deptno varchar2(20), cnt number);
4 type t_tjob is table of t_job;
5 l_tjob t_tjob := t_tjob();
6 l_str varchar2(200);
7 begin
8 tname := sys.odcivarchar2list('EMP', 'DEPT');
9
10 for i in 1 .. tname.count loop
11 l_str := 'select deptno, count(*) from ' || tname(i) ||' group by deptno';
12 execute immediate l_str bulk collect into l_tjob;
13
14 for j in l_tjob.first .. l_tjob.last loop
15 dbms_output.put_Line('Table ' || tname(i) || ': Deptno ' || l_tjob(j).deptno||
16 ': number of rows = '|| l_tjob(j).cnt);
17 end loop;
18
19 end loop;
20 end;
21 /
Table EMP: Deptno 30: number of rows = 6
Table EMP: Deptno 20: number of rows = 5
Table EMP: Deptno 10: number of rows = 3
Table DEPT: Deptno 10: number of rows = 1
Table DEPT: Deptno 20: number of rows = 1
Table DEPT: Deptno 30: number of rows = 1
Table DEPT: Deptno 40: number of rows = 1
PL/SQL procedure successfully completed.
SQL>
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句