[20251015]建立和完善col_vlist.sql脚本.txt
--//建立一个支持视图的版本，由于oracle没有视图提取字段信息的视图，只能通过desc提取，通过bash shell处理生成需要的脚本。
--//如果有哪个视图支持提取视图字段信息的，希望告知，我再重写该脚本。
$ cat col_vlist.sql
-- Copyright 2023 lfree. All rights reserved.
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms and conditions.
--------------------------------------------------------------------------------
--
-- Name: col_vlist.sql
-- Purpose: display table or view of column_name list.
--
-- Author: lfree
-- Usage:
-- @col_vlist owner.[table_name|view_name] regexp_column_list|column_lists
-- column_list format : 1,2,4,5 or 2-4,7
--
--------------------------------------------------------------------------------
set term off
col 2 new_value 2
col tpt_comment1 new_value _tpt_comment1
col tpt_comment2 new_value _tpt_comment2
select null "2" from dual where 1=2;
select
decode('&2',null,'*',replace(replace('&2',',','p;'),'-',',')) "2"
,CASE WHEN (INSTR (LOWER ('&2'), ',') > 0 or INSTR (LOWER ('&2'), '-') > 0 ) THEN '##' WHEN '&2' IS NULL THEN ' ' ELSE ' ' END tpt_comment1
,CASE WHEN (INSTR (LOWER ('&2'), ',') > 0 or INSTR (LOWER ('&2'), '-') > 0 ) THEN ' ' WHEN '&2' IS NULL THEN '##' ELSE '##' END tpt_comment2
from dual;
set term on
def _desc_tmpfile=&_tpt_tempdir/desc_&_tpt_tempfile..tmp
def _sp_tmpfile=&_tpt_tempdir/sp_&_tpt_tempfile..tmp
set linesize 80
set term off
spool &_desc_tmpfile
set describe depth 1 linenum off indent on
desc &1
set describe depth 1 linenum on indent on
spool off
set term on
set linesize 269
--host sed -n '1,/----------/p' &_desc_tmpfile
--host sed -n '/----------/,$p' &_desc_tmpfile | awk '{print $1}'| egrep -i '&2'
host &_tpt_comment1 (echo SELECT ; sed -n '/----------/,$p;' &_desc_tmpfile | egrep -v -- "----------|^$" | awk '{print tolower($1)}'| egrep -i '&2' | sed -n '1p;2,$s/^/,/p'; echo FROM '&1 ') |tee &_sp_tmpfile
host &_tpt_comment2 (echo SELECT ; sed -n '/----------/,$p;' &_desc_tmpfile | egrep -v -- "----------|^$" | awk '{print tolower($1)}'| sed -n '&2.p' | sed -n '1p;2,$s/^/,/p'; echo FROM '&1 ') |tee &_sp_tmpfile
get &_sp_tmpfile nolist
host &_DELETE &_desc_tmpfile &_sp_tmpfile
--//简单测试：
--//测试前说明一下通过判断参数2是否存在逗号是否是数字顺序，如果仅仅输入1个数字没有逗号会存在问题，可以简单加入,1001规避
--//这个问题，注意这里不能输入0.
SCOTT@book01p> @ col_vlist emp 1
SELECT
FROM emp
SCOTT@book01p> @ col_vlist emp 1,1001
SELECT
empno
FROM emp
SCOTT@book01p> @ col_vlist emp 1-2,5
SELECT
empno
,ename
,hiredate
FROM emp
SCOTT@book01p> @ descv v$database " dbid|supp"
Name Null? Type
------------------------------- -------- ----------------------------
1 DBID NUMBER
30 SUPPLEMENTAL_LOG_DATA_MIN VARCHAR2(8)
31 SUPPLEMENTAL_LOG_DATA_PK VARCHAR2(3)
32 SUPPLEMENTAL_LOG_DATA_UI VARCHAR2(3)
40 SUPPLEMENTAL_LOG_DATA_FK VARCHAR2(3)
41 SUPPLEMENTAL_LOG_DATA_ALL VARCHAR2(3)
52 SUPPLEMENTAL_LOG_DATA_PL VARCHAR2(3)
59 SUPPLEMENTAL_LOG_DATA_SR VARCHAR2(3)
SCOTT@book01p> @ col_vlist v$database 1,30-32,40,41,52,59
SELECT
dbid
,supplemental_log_data_min
,supplemental_log_data_pk
,supplemental_log_data_ui
,supplemental_log_data_fk
,supplemental_log_data_all
,supplemental_log_data_pl
,supplemental_log_data_sr
FROM v$database
SCOTT@book01p> /
DBID SUPPLEME SUP SUP SUP SUP SUP SUP
---------- -------- --- --- --- --- --- ---
1617337831 YES NO NO NO NO NO NO
SCOTT@book01p> @ col_vlist v$database ^dbid|supp
SELECT
dbid
,supplemental_log_data_min
,supplemental_log_data_pk
,supplemental_log_data_ui
,supplemental_log_data_fk
,supplemental_log_data_all
,supplemental_log_data_pl
,supplemental_log_data_sr
FROM v$database
SCOTT@book01p> /
DBID SUPPLEME SUP SUP SUP SUP SUP SUP
---------- -------- --- --- --- --- --- ---
1617337831 YES NO NO NO NO NO NO
