从下面的脚本我得到的project_name需要形成表名称的一部分在我的下一个查询,如何做到这一点在oracle
Query1:
SELECT PROJECT FROM testServer_l WHERE liveConnection LIKE '%test01.azure.cloud%'
上述查询的输出为:91869 90237 87732 103868 96995 102646 98178 100587 99887 90277 68240 103630 92756 83169 90070 96194
Query2:
SELECT COUNT(*) FROM T91869.TotalConnection
表名是'T'
+'ProjectName'
[来自上面的查询],例如T+91869
您将-正如您所说-将T
连接到project
。使用
concat
功能,如select concat('T', project)
- 缺点:
concat
只接受两个参数,所以-如果你必须连接两个以上的参数,你将不得不嵌套函数调用
- 缺点:
- 双管道
||
连接操作符(正如我所做的)- 的好处:连接尽可能多的字符串,因为你想
然而,这还不够,你需要动态SQL.
下面是一个例子:
示例项目表:
SQL> create table t91869 as select * from emp;
Table created.
SQL> create table t90237 as select * from dept;
Table created.
项目列表:
SQL> create table testserver_l as
2 select 91869 project from dual union all
3 select 90237 from dual;
Table created.
程序:
SQL> set serveroutput on
SQL> declare
2 l_cnt number;
3 begin
4 for cur_r in (select 'T' || project as project
5 from testserver_l
6 )
7 loop
8 execute immediate 'select count(*) from ' || cur_r.project into l_cnt;
9 dbms_output.put_line(cur_r.project ||': '|| l_cnt);
10 end loop;
11 end;
12 /
T91869: 14
T90237: 4
PL/SQL procedure successfully completed.
SQL>