oracle 禁止查询列,oracle 排除(exclude)字段查询表

如果一张表有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.

「喜欢文章，快来给作者赞赏墨值吧」 赞赏

【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源(墨天轮)，文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论

请登录后发表评论