如何在字符串中插入动态SQL oracle变量

Question

我有一个动态查询：

declare
p_sql clob;
begin
​for naprav in (select naprav_name from naprav)
​ ​ ​loop​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​
​ ​ ​ ​ ​ ​ for pokaz in (select * from pokaz)
​ ​ ​ ​ ​ ​ ​loop
​ ​ ​ ​ ​ ​ ​ ​ ​for group_level in (select * from group_level)
​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​loop
​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ p_sql := '
​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ insert into from_cost_lab_with_love
​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ with cte as (
​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ select
​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ' ||​ pokaz.i_id_pokaz || ' as i_id_pokaz,
​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ' ||​ pokaz.pokaz_measure || ' as pokaz_measure,
​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ' ||​ pokaz.pokaz_describe || ' as "Описание показателя",
​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ' ||​ naprav.naprav_name || ' as "Блок данных",
​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ' ||​ group_level.level_name || ' as level_name,
​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ' ||​ group_level.tb_id || ' as tb_id,
​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ' ||​ group_level.gosb_id || ' as gosb_id,
​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ' ||​ group_level.org_id || ' as org_id,
​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ' ||​ pokaz.pokaz_formula || ' as fact_amt,' --показатель
​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ||​ pokaz.pokaz_formula || '  as fc_amt_1,
​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ row_number() over(' || group_level.partition_column || ' order by BE) as rnk
​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ 0 as plan_amt,
​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ TO_DATE(CONCAT(CONCAT(FINANSOVYJ_GOD,CONCAT(''0'',KVARTAL*3 - 3)),''01''), ''yyyymmdd'') as period_begin,​ --начало квартала
​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ LAST_DAY(TO_DATE(CONCAT(CONCAT(FINANSOVYJ_GOD,CONCAT(''0'',KVARTAL*3)),''01''), ''yyyymmdd'')) as period_end, --конец квартала
​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ LAST_DAY(TO_DATE(CONCAT(CONCAT(FINANSOVYJ_GOD,CONCAT(''0'',MESJATS)), ''01''), ''yyyymmdd'')) as rep_date, --дата измерений
​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ TO_DATE(CURRENT_DATE, ''dd.mm.yy'') as load_date,
​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ 0 as execution
​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ from erp_trips
​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ )
​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ select *
​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ from cte
​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ where rnk=1;';
​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ dbms_output.put_line(p_sql);
​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​execute immediate p_sql using group_level.partition_column;
​ ​ ​ ​ ​ ​ ​ ​end loop;
​ ​ ​ ​ ​end loop;
​ ​ ​end loop;
end;

它有一个野外pokaz。pokaz_formula，该字段取自pokaz表，该字段的值之一为pokaz。pokaz_formula：

sum(distinct RASHODY) over(:1)/count(distinct KOD_TSZ) over(:1)

dbms_output:

insert into from_cost_lab_with_love
with cte as (
select
'99business_trips_01' as i_id_pokaz,
'руб.' as pokaz_measure,
'Командировочные расходы, руб.' as "Описание показателя",
'Командировки' as "Блок данных",
'gosb' as level_name,
BE/100 as tb_id,
GOSB as gosb_id,
'' as org_id,
sum(distinct RASHODY) over(:1) as fact_amt,sum(distinct RASHODY) over(:1) as fc_amt_1,
row_number() over(partition by BE, GOSB order by BE) as rnk,
0 as plan_amt,
TO_DATE(CONCAT(CONCAT(FINANSOVYJ_GOD,CONCAT('0',KVARTAL*3 - 3)),'01'), 'yyyymmdd') as period_begin,  --начало квартала
LAST_DAY(TO_DATE(CONCAT(CONCAT(FINANSOVYJ_GOD,CONCAT('0',KVARTAL*3)),'01'), 'yyyymmdd')) as period_end, --конец квартала
LAST_DAY(TO_DATE(CONCAT(CONCAT(FINANSOVYJ_GOD,CONCAT('0',MESJATS)), '01'), 'yyyymmdd')) as rep_date, --дата измерений
TO_DATE(CURRENT_DATE, 'dd.mm.yy') as load_date,
0 as execution
from erp_trips
)
select *
from cte
where rnk=1 and gosb_id <> 'ПЦП'
order by tb_id, gosb_id

当这个值在p_sql变量的循环中被替换时，值：1应该被group_level表列中的值替换，就像我的代码中一样。但是编译器发誓(缺少右括号(我该如何解决这个问题？

Answer 1

与不能绑定表名一样，也不能绑定分析函数的部分。

示例-ORA-00903: invalid table name失败

begin
execute immediate 'insert into tab1(col) select col from :1' using 'TAB2';
end;
/

同样适用于您尝试绑定partition by子句

begin
execute immediate 'insert into tab1(col) select max(col) over (:1)  from tab2' using 'partition by col2';
end;
/

正如您在ORA-00907: missing right parenthesis中观察到的那样，此操作失败

可以做，但可以肯定的是您不想做的是在PARTITION BY子句中绑定一个值

begin
execute immediate 'insert into tab1(col) select max(col) over (partition by :1)  from tab2' using 'col2';
end;
/

这是有效的，但行为方式与通过子句提交分区相同，因为它对应于partition by 'col2'，即在固定值上进行分区会导致表中所有行的一个分区。

因此，您正在寻找的解决方案很可能是通过子句将分区连接到insert语句中：

execute immediate 'insert into tab1(col) select max(col) over (' || 
group_level.partition_column || /* concatenate the partition clause in the SQL */
')  from tab2';

它将产生类似的东西

insert into tab1(col) select max(col) over (partition by col2)  from tab2

执行此操作时，您应该始终检查SQL注入。