[20251015]建立和完善col_vlist.sql脚本.txt

[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