我要写一个动态查询，它将从另一个表中读取表名。
我的第一个表包含表名:(tabel:all_table_names)
| table_name | first_table |
|---|
| second_table |
| third_table |
使用双引号创建的表比较棘手。在Oracle中，我们通常不这样做。为什么?因为Oracle将表名以大写形式存储在数据字典中，但您可以以任何方式引用它们。但是，如果使用双引号，则必须在每次引用该表时使用它们，并与创建表时使用的字母大小写完全匹配。我认为问题太多了。
例如:这个表是用双引号和空格创建的:
SQL> create table "first table" as select * from dept where deptno = 10;
Table created.
该表是"正常"创建的
SQL> create table second_table as select empno, ename, job, sal from emp where deptno = 20;
Table created.
这个表包含了你感兴趣的表列表:
SQL> create table list_of_tables as
2 select 'first table' as table_name from dual union all
3 select 'second_table' from dual;
Table created.
SQL> select * from list_of_tables;
TABLE_NAME
------------
first table
second_table
现在，如果您检查名称包含TABLE的表，在我的模式中您将得到
SQL> select tname from tab where upper(tname) like '%TABLE%';
TNAME
------------------------------
first table --> note this table, created using double quotes
TABLE_NAME
TABLE_1
SECOND_TABLE
LIST_OF_TABLES
SQL>
选择first table:
SQL> select * from first table;
select * from first table
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
SQL> select * From FIRST TABLE;
select * From FIRST TABLE
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
SQL> select * from "FIRST TABLE";
select * from "FIRST TABLE"
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select * from "first table";
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
SQL>
只有最后一个，加上双引号和小写(因为这是创建表的方式)。
与SECOND_TABLE:
SQL> select * from second_table union all
2 select * from SECONd_TabLE union all
3 select * from SECOND_TABLE union all
4 select * from "SECOND_TABLE";
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7369 SMITH CLERK 800
7566 JONES MANAGER 2975
7788 SCOTT ANALYST 3000
7876 ADAMS CLERK 1100
<snip>
看到了?无论它是如何被引用的
你想做的事情不能在SQL中完成-你需要PL/SQL和动态SQL。当您选择使用双引号和混合大小写时，您将不得不再次使用双引号(参见第8行):
SQL> set serveroutput on
SQL>
SQL> declare
2 l_str varchar2(200);
3 begin
4 for cur_r in (select table_name
5 from list_of_tables
6 )
7 loop
8 l_str := 'select * from ' || '"' || cur_r.table_name || '"';
9 dbms_output.put_line(l_str);
10 end loop;
11 end;
12 /
select * from "first table"
select * from "second_table"
PL/SQL procedure successfully completed.
这些SELECT语句有效吗?部分;first table可以，但第二个不行，因为名称为second_table(小写)的表不存在:
SQL> select * from "first table";
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
SQL> select * from "second_table";
select * from "second_table"
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL>
如果您应用例如UPPER函数，那么您将破坏第一个表:
SQL> declare
2 l_str varchar2(200);
3 begin
4 for cur_r in (select table_name
5 from list_of_tables
6 )
7 loop
8 l_str := 'select * from ' || '"' || upper(cur_r.table_name) || '"';
9 dbms_output.put_line(l_str);
10 end loop;
11 end;
12 /
select * from "FIRST TABLE"
select * from "SECOND_TABLE"
PL/SQL procedure successfully completed.
SQL> select * from "FIRST TABLE";
select * from "FIRST TABLE"
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select * from "SECOND_TABLE";
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7369 SMITH CLERK 800
7566 JONES MANAGER 2975
7788 SCOTT ANALYST 3000
7876 ADAMS CLERK 1100
7902 FORD ANALYST 3000
SQL>
SQL> declare
2 l_str varchar2(200);
3 begin
4 for cur_r in (select table_name
5 from list_of_tables
6 )
7 loop
8 l_str := 'select * from ' || case when instr(cur_r.table_name, ' ') > 0 then '"' end
9 || cur_r.table_name || case when instr(cur_r.table_name, ' ') > 0 then '"' end;
10 dbms_output.put_line(l_str);
11 end loop;
12 end;
13 /
select * from "first table"
select * from second_table
PL/SQL procedure successfully completed.
SQL> select * from "first table";
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
SQL> select * from second_table;
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7369 SMITH CLERK 800
7566 JONES MANAGER 2975
7788 SCOTT ANALYST 3000
7876 ADAMS CLERK 1100
7902 FORD ANALYST 3000
SQL>
现在它们都工作了。
然而，这还不够。我只是在屏幕上显示select语句，并使用复制/粘贴来执行它们。我猜你不会做这件事。问题是:一旦你写了这些select，你会做什么?如果表包含不同的列列表，则不能仅将select *转换为局部变量，也不能使用数组，因为必须在list_of_tables表中创建与表一样多的数组。动态执行(第11行)(但是-没有"输出"(任何类型):
SQL> declare
2 l_str varchar2(200);
3 begin
4 for cur_r in (select table_name
5 from list_of_tables
6 )
7 loop
8 l_str := 'select * from ' || case when instr(cur_r.table_name, ' ') > 0 then '"' end
9 || cur_r.table_name || case when instr(cur_r.table_name, ' ') > 0 then '"' end;
10 dbms_output.put_line(l_str);
11 execute immediate l_str;
12 end loop;
13 end;
14 /
select * from "first table"
select * from second_table
PL/SQL procedure successfully completed.
SQL>
因此，您最终可能会创建一个函数，该函数返回ref cursor并向其传递有效的表名:
SQL> create or replace function f_tab (par_table_name in varchar2)
2 return sys_refcursor
3 is
4 rc sys_refcursor;
5 begin
6 open rc for 'select * from ' || dbms_assert.sql_object_name(par_table_name);
7 return rc;
8 end;
9 /
Function created.
SQL> select f_tab('"first table"') from dual;
F_TAB('"FIRSTTABLE"'
--------------------
CURSOR STATEMENT : 1
CURSOR STATEMENT : 1
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
SQL> select f_tab('SECOND_TABLE') from dual;
F_TAB('SECOND_TABLE'
--------------------
CURSOR STATEMENT : 1
CURSOR STATEMENT : 1
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7369 SMITH CLERK 800
7566 JONES MANAGER 2975
7788 SCOTT ANALYST 3000
7876 ADAMS CLERK 1100
7902 FORD ANALYST 3000
SQL>