ANBOB™

如果一张表有30个字段，40个,100个…，反正就是不少字段时，如果你查询部分字段数据，是不是要一个个字段名都写上，有没有一种指明排除几个字段查询呢？今天看有人问这个问题，随便做一下。

SQL>CONN anbob/anbob

SQL> create table test_cols(id int);

Table created.

SQL> declare
2  v_sql varchar2(2000);
3  begin
4  for i in 1..20 loop
5   v_sql:='alter table test_cols add id'||i||' int';
6  execute immediate v_sql;
7  end loop;
8  end;
9  /

PL/SQL procedure successfully completed.

SQL> desc test_cols
Name                                                                                Null?    Type
----------------------------------------------------------------------------------- --------
ID                                                                                           NUMBER(38)
ID1                                                                                          NUMBER(38)
ID2                                                                                          NUMBER(38)
ID3                                                                                          NUMBER(38)
ID4                                                                                          NUMBER(38)
ID5                                                                                          NUMBER(38)
ID6                                                                                          NUMBER(38)
ID7                                                                                          NUMBER(38)
ID8                                                                                          NUMBER(38)
ID9                                                                                          NUMBER(38)
ID10                                                                                         NUMBER(38)
ID11                                                                                         NUMBER(38)
ID12                                                                                         NUMBER(38)
ID13                                                                                         NUMBER(38)
ID14                                                                                         NUMBER(38)
ID15                                                                                         NUMBER(38)
ID16                                                                                         NUMBER(38)
ID17                                                                                         NUMBER(38)
ID18                                                                                         NUMBER(38)
ID19                                                                                         NUMBER(38)
ID20

sql> CREATE OR REPLACE FUNCTION select_exclude (tabname VARCHAR2, exc_cols VARCHAR2)
-- parame tablename 表名
-- parame exc_cols 排除字段名 格式如'col1,col2,co3'
-- Copyright  zhangweizhao  www.anbob.com 2011-5-5
-- describe 排除字段查询

RETURN sys_refcursor
IS
type_cur     sys_refcursor;
v_sql        VARCHAR2 (4000);
v_cols       VARCHAR2 (1000);
v_exc_cols   VARCHAR2 (1000);
BEGIN
v_exc_cols := UPPER ('''' || REPLACE (exc_cols, ',', ''',''') || '''');
--select regexp_replace('tab1,tab2,tab3','([^,]+)','''\1''') from dual
v_sql :=
'select  cols  from (
SELECT wmsys.wm_concat (column_name) cols  FROM (SELECT   column_name  FROM all_tab_cols '
|| 'WHERE table_name = '''
|| UPPER (tabname)
|| ''' AND column_name NOT IN ('
|| v_exc_cols
|| ')    ORDER BY column_id))';

EXECUTE IMMEDIATE v_sql
INTO v_cols;

v_sql := 'select  ' || v_cols || '  from ' || tabname;

-- dbms_output.put_line(v_sql);
OPEN type_cur FOR v_sql;

RETURN type_cur;
END;

SQL> set linesize 4000
SQL> set wrap off
SQL> select select_exclude('test_cols','id11,id6') from dual;

SELECT_EXCLUDE('TEST
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

ID        ID1        ID2        ID3        ID4        ID5        ID7        ID8        ID9       ID10       ID12       ID13       ID14       ID15  ID16        ID17       ID18       ID19       ID20
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1          2
3          4
5          6
5                     6
5                     6
5                     6
5                     6

7 rows selected.

打赏

微信扫一扫，打赏作者吧～