[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
