立即用$$执行

Question

我有一个返回1列1行的长查询：

查询1:

select test_query from (
SELECT 
LISTAGG('...') ... AS xx,
LISTAGG('"' || c.COLUMN_NAME || '"', ', ') WITHIN GROUP(ORDER BY c.COLUMN_NAME) AS column_list
.....
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE TABLE_NAME = xx);

输出如下所示。它有一个我要运行的查询。请注意，它由引号组成。原始查询1在LISTAGG中也有引号。

输出：

test_query   
select "col1", "col2" from stg.new          

我想扩展查询A，这样我就可以选择并运行test_query，并返回test_query的最终输出，而不是查询A。我尝试将execute immediate与$$:一起使用

execute immediate 
$$select test_query from (
SELECT 
LISTAGG('...') ... AS xx,
LISTAGG('"' || c.COLUMN_NAME || '"', ', ') WITHIN GROUP(ORDER BY c.COLUMN_NAME) AS column_list
.....
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE TABLE_NAME = xx)$$;

但不是运行CCD_ 4，而是输出与Query1相同的内容。

我还能尝试什么？

Answer 1

下面是一个尝试转换SQL模板的例子。您可以扩展它来添加整个SQL语句：

execute immediate
$$
declare 
rs resultset default (
select test_query from (
SELECT 
LISTAGG('...') ... AS xx,
LISTAGG('"' || c.COLUMN_NAME || '"', ', ') WITHIN GROUP(ORDER BY c.COLUMN_NAME) AS column_list
.....
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE TABLE_NAME = xx);
);
c cursor for rs;
sqlStatement string;
rsFinal resultset;
begin
for rowContents in c do
sqlStatement := rowContents.SQL_STATEMENT;
end for;
rsFinal := (execute immediate :sqlStatement);
return table(rsFinal);
end;
$$
;

如果由于上下文等原因而不起作用，这里有一个完全独立的示例：

create or replace transient table T1 as
select 'Hello world' as s;
create or replace transient table SQL_TO_RUN as
select 'select S from T1 SQL_STATEMENT' SQL_STATEMENT
;
execute immediate
$$
declare 
rs resultset default (select SQL_STATEMENT from SQL_TO_RUN);
c cursor for rs;
sqlStatement string;
rsFinal resultset;
begin
for rowContents in c do
sqlStatement := rowContents.SQL_STATEMENT;
end for;
rsFinal := (execute immediate :sqlStatement);
return table(rsFinal);
end;
$$
;

输出：

s
你好世界