[20251014]建立和完善col_list.sql脚本.txt

[20251014]建立和完善col_list.sql脚本.txt --//增加选择字段顺序号功能。 $ cat col_list.sql -- Copyright 2023 lfree. All rights reserved. -- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms and conditions. -------------------------------------------------------------------------------- -- -- Name:        col_list.sql -- Purpose:     display table of column_name list. -- -- Author:      lfree -- Usage: --     @col_list owner.table_name regexp_column_list|column_lists --     column_list format : 1,2,4,5 -- -------------------------------------------------------------------------------- set term off head off feedback off col 2 new_value 2 col tpt_comment1 new_value _tpt_comment1 col tpt_comment2 new_value _tpt_comment2 select null "2" ,null "3" from dual where 1=2; select  decode('&2',null,'*','&2') "2" ,CASE WHEN INSTR (LOWER ('&2'), ',') > 0 THEN '--' WHEN '&2' IS NULL THEN '--' ELSE '  ' END tpt_comment1 ,CASE WHEN INSTR (LOWER ('&2'), ',') > 0 THEN '  ' WHEN '&2' IS NULL THEN '--' ELSE '--' END tpt_comment2 from dual; set term on def _sp_tmpfile=&_tpt_tempdir/sp_&_tpt_tempfile..tmp spool &_sp_tmpfile SELECT    'SELECT'        || CHR (10)        || ' '        || LISTAGG (LOWER (column_name), CHR (10) || ',') WITHIN GROUP (ORDER BY column_id)        || CHR (10)        || 'FROM &&1'           c80   FROM (  SELECT data_type                 ,column_id                 ,column_name                 ,data_type             FROM dba_tab_cols            WHERE     UPPER (table_name) LIKE                         UPPER (                            CASE WHEN INSTR ('&&1', '.') > 0 THEN SUBSTR ('&&1', INSTR ('&&1', '.') + 1) ELSE '&&1' END) ESCAPE '\'                  AND owner LIKE                         CASE                            WHEN INSTR ('&&1', '.') > 0 THEN UPPER (SUBSTR ('&&1', 1, INSTR ('&&1', '.') - 1))                            ELSE USER                         END ESCAPE '\'                  AND HIDDEN_COLUMN = 'NO'                  &_tpt_comment1 AND REGEXP_LIKE (LOWER (COLUMN_NAME), LOWER ('&&2'))                                  &_tpt_comment2 AND COLUMN_id in ( &&2)         ORDER BY column_id); spool off set head on feedback 6 get &_sp_tmpfile nolist host &_delete &_sp_tmpfile --//缺点只能根据表来建立生成sql语句。如果是视图不行，另外写一个支持视图的版本。 --//还有1个问题就是我通过判断参数2是否存在逗号是否是数字顺序，如果仅仅输入1个数字没有逗号会存在问题，可以简单加入,0规避 --//这个问题或者输入一个很大的数字比如1001代替0. --//简单测试： SCOTT@book01p> @ desc emp            Name                            Null?    Type            ------------------------------- -------- ----------------------------     1      EMPNO                           NOT NULL NUMBER(4)     2      ENAME                                    VARCHAR2(10)     3      JOB                                      VARCHAR2(9)     4      MGR                                      NUMBER(4)     5      HIREDATE                                 DATE     6      SAL                                      NUMBER(7,2)     7      COMM                                     NUMBER(7,2)     8      DEPTNO                                   NUMBER(2) SCOTT@book01p> @ col_vlist emp '' SELECT empno ,ename ,job ,mgr ,hiredate ,sal ,comm ,deptno FROM emp --//没有输入参数2，取出全部字段。 SCOTT@book01p> @ col_list emp 1 SELECT FROM emp --//仅仅一个数字被当作正则表达式。加入1个数字1001后正常。 SCOTT@book01p> @ col_vlist emp 1,1001 SELECT empno FROM emp      SCOTT@book01p> @ col_list emp 1,2,5-6 SELECT  empno ,ename FROM emp --//缺点不支持5-6这样的写法，不过不会报错当作-1。要支持这个功能难度有点大，暂时放弃!! SCOTT@book01p> @ col_list emp 1,2,5,6 SELECT  empno ,ename ,hiredate ,sal FROM emp SCOTT@book01p> 9999 where ename like 'S%' SCOTT@book01p> /      EMPNO ENAME      HIREDATE                   SAL ---------- ---------- ------------------- ----------       7369 SMITH      1980-12-17 00:00:00        800       7788 SCOTT      1987-07-13 00:00:00       3000 SCOTT@book01p> @ col_list emp ^e|date|sal SELECT  empno ,ename ,hiredate ,sal FROM emp SCOTT@book01p> 9999 where sal>=3400; SCOTT@book01p> /      EMPNO ENAME      HIREDATE                   SAL ---------- ---------- ------------------- ----------       7839 KING       1981-11-17 00:00:00       5000