用于多个插入/更新的SQL前缀

Question

我准备了一个脚本，updates/insert新值到几个表。而不是使用硬编码的值，我想使用前缀，这将允许我引用这个模式作为变量。

示例如下。我想用abc

这样的值来代替SKC

insert into SKC.type_table (TYPE_CD, STATUS_CD, CREATED_BY, CREATION_DATE, UPD_USER_ID, LAST_UPDATE_DATE)

insert into SKC.type_df (TYPE_CD, LANGUAGE_CD, TYPE_DESCRIPTION, CREATED_BY, CREATION_DATE, UPD_USER_ID, LAST_UPDATE_DATE)

Answer 1

如果您预先知道所有的表名，您可以使用multitable insert:

create table templ (
id int
);

create procedure generate_tabs(num int)
as
begin
for i in 1..num loop
execute immediate 'create table t_' || to_char(i, 'TM9')
|| ' as select * from templ where 1 = 0';

end loop;
end;/

begin
generate_tabs(5);
end;/

insert all
when tab_name = 1 then into t_1
values(val)
when tab_name = 2 then into t_2
values(val)
when tab_name = 3 then into t_3
values(val)
when tab_name = 4 then into t_4
values(val)
when tab_name = 5 then into t_5
values(val)
select
level as tab_name,
level as val
from dual
connect by level < 5

select 't_1' as src, t_1.*
from t_1
union all
select 't_2' as src, t_2.*
from t_2
union all
select 't_3' as src, t_3.*
from t_3
union all
select 't_4' as src, t_4.*
from t_4
union all
select 't_5' as src, t_5.*
from t_5

SRC | ID:——| -:T_1 | 1T_2 | 2T_3 | 3t_4 | 4

db<此处小提琴>

Answer 2

从脚本中删除所有模式名称，并在运行脚本之前设置默认模式名称。例如，如果您在SQL*Plus:

中运行脚本

alter session set current_schema = abc;
@your_script.sql
alter session set current_schema = skc;
@your_script.sql