dml通用sql脚本

Question

我有一个表测试，它包含所有的表名、column_names和标志。我想用如下所述的flg逻辑从当前模式中的所有表中创建“插入测试”的通用脚本: flg应该是filler1 insert 'N‘之前所有列的'Y’，目前我正在硬编码为'N‘。

drop table test;
CREATE TABLE TEST (TABLE_NM VARCHAR2(10) ,COL_NM VARCHAR2(10) ,  FLG CHAR(1));

CREATE TABLE XYZ (FNAME VARCHAR2(10) , LNAME VARCHAR2(10) , PID NUMBER , FILLER1 VARCHAR2(10) , FILLER2 VARCHAR2(10) );

SELECT TABLE_NAME , COLUMN_NAME
from ALL_TAB_COLUMNS
WHERE table_name='XYZ'
order by COLUMN_ID;

脚本查询：

 select 'INSERT INTO TEST (TABLE_NM,COL_NM,FLG)VALUES ('''||TABLE_NAME||
    ''','''||COLUMN_NAME||''',''N'''||')'
    from ALL_TAB_COLUMNS
    where table_name='XYZ'
    order by COLUMN_ID;

预期产出

 INSERT INTO TEST (TABLE_NM,COL_NM,FLG)VALUES ('XYZ','FNAME','Y');
    INSERT INTO TEST (TABLE_NM,COL_NM,FLG)VALUES ('XYZ','LNAME','Y');
    insert into TEST (TABLE_NM,COL_NM,FLG)VALUES ('XYZ','PID','Y');
    INSERT INTO TEST (TABLE_NM,COL_NM,FLG)VALUES ('XYZ','FILLER1','N');
    insert into TEST (TABLE_NM,COL_NM,FLG)VALUES ('XYZ','FILLER2','N');

我怎样才能做到这一点？提前谢谢。

Answer 1

如果您在所有这些单引号中迷失了方向，我建议您使用CHR(39) (这是单引号)。

下面是一个例子。

SQL> create table test (table_nm varchar2(10),
  2                     col_nm   varchar2(10),
  3                     flg      char(1));

Table created.

SQL> create table xyz (fname     varchar2(10),
  2                    lname     varchar2(10),
  3                    pid       number,
  4                    filler1   varchar2(10),
  5                    filler2   varchar2(10),
  6                    some_col  number);

Table created.

CTE FILLER1_POS返回FILLER1列的位置(即column_id)；它在CASE中用于区分ID低于(或高于) FILLER1列的列。

SQL> with filler1_pos as
  2    (select column_id fid_pos
  3     from all_tab_columns
  4     where table_name = 'XYZ'
  5       and column_name = 'FILLER1'
  6    )
  7  select 'insert into test (table_nm, col_nm, flg) values (' ||
  8     chr(39) || a.table_name  || chr(39) ||', '||
  9     chr(39) || a.column_name || chr(39) ||', '||
 10     chr(39) || case when a.column_id < f.fid_pos then 'Y'
 11                     else 'N'
 12                end           || chr(39) ||');' result
 13    from all_tab_columns a join filler1_pos f on 1 = 1
 14    where a.table_name = 'XYZ'
 15    order by a.column_id;

RESULT
--------------------------------------------------------------------------------
insert into test (table_nm, col_nm, flg) values ('XYZ', 'FNAME', 'Y');
insert into test (table_nm, col_nm, flg) values ('XYZ', 'LNAME', 'Y');
insert into test (table_nm, col_nm, flg) values ('XYZ', 'PID', 'Y');
insert into test (table_nm, col_nm, flg) values ('XYZ', 'FILLER1', 'N');
insert into test (table_nm, col_nm, flg) values ('XYZ', 'FILLER2', 'N');
insert into test (table_nm, col_nm, flg) values ('XYZ', 'SOME_COL', 'N');

6 rows selected.

SQL>

让我们运行这些插入并检查结果：

SQL> insert into test (table_nm, col_nm, flg) values ('XYZ', 'FNAME', 'Y');

1 row created.

SQL> insert into test (table_nm, col_nm, flg) values ('XYZ', 'LNAME', 'Y');

1 row created.

SQL> insert into test (table_nm, col_nm, flg) values ('XYZ', 'PID', 'Y');

1 row created.

SQL> insert into test (table_nm, col_nm, flg) values ('XYZ', 'FILLER1', 'N');

1 row created.

SQL> insert into test (table_nm, col_nm, flg) values ('XYZ', 'FILLER2', 'N');

1 row created.

SQL> insert into test (table_nm, col_nm, flg) values ('XYZ', 'SOME_COL', 'N');

1 row created.

SQL> select * from test;

TABLE_NM   COL_NM     F
---------- ---------- -
XYZ        FNAME      Y
XYZ        LNAME      Y
XYZ        PID        Y
XYZ        FILLER1    N
XYZ        FILLER2    N
XYZ        SOME_COL   N

6 rows selected.

SQL>

Answer 2

这能做你想做的事吗？

SELECT 'INSERT INTO TEST (TABLE_NM,COL_NM,FLG)VALUES (''' ||
       TABLE_NAME ||
       ''',''' ||
       COLUMN_NAME ||
       ''',''' ||
       CASE WHEN SUBSTR(COLUMN_NAME,1,6) = 'FILLER' THEN 'N' ELSE 'Y' END ||
       ''');'        
  FROM ALL_TAB_COLUMNS
 WHERE table_name='XYZ'
 ORDER BY COLUMN_ID;

Answer 3

一种选择可能是将decode与sign一起用作：

SELECT 'INSERT INTO TEST (TABLE_NM,COL_NM,FLG)VALUES ('''||TABLE_NAME||
    ''','''||COLUMN_NAME||''','''||decode(sign(u.column_id-3),1,'N','Y')||''');'
  FROM USER_TAB_COLUMNS U
 WHERE table_name='XYZ'
 ORDER BY COLUMN_ID;

http://sqlfiddle.com/#!4/1d5c5/4