oracle查询所有表和所有数据的方法可能许多刚刚接触oracle数据库的新人都不太了解,下面就为您详细介绍oracle查询所有表的方法,供您参考。
- SELECT t.data_type
- FROM user_tab_columns t
- GROUP BY t.data_type;
- -- Created on 2010-8-26 by RONGZHI_LI
- DECLARE
- CURSOR ot(namelike IN VARCHAR2)
- IS
- (SELECT owner AS uname,
- object_name AS tname
- FROM all_objects
- WHERE object_type='TABLE'
- AND owner LIKE namelike
- );
- CURSOR tc( username IN all_tab_columns.owner%TYPE, tablename IN all_tab_columns.table_name%type)
- IS
- (SELECT t.OWNER AS uname,
- t.table_name AS tab,
- t.column_name AS col,
- t.data_type data
- FROM all_tab_columns t
- WHERE t.owner = username
- AND t.table_name = tablename
- );
- sqlstr VARCHAR2(200);
- findsqlstr VARCHAR2(200);
- foundcount NUMBER;
- data_type VARCHAR2(100):='';
- logic_symbol VARCHAR2(10) :='=';
- data_value VARCHAR2(100);
- --自行设定
- name_like VARCHAR2(100):='%HS_%';
- find_type VARCHAR2(100):='varchar2';--'varchar2';number
- find_value VARCHAR2(100):='工行';
- use_like BOOLEAN :=true;
- BEGIN
- data_type := upper(find_type);
- data_value :=find_value;--先给默认值
--1.判断类型,是否为字符串
- IF data_type = 'VARCHAR2' THEN -- 对于字符串特别判定
- data_value := ''''||find_value||'''';--注意:pl/sql显示' '为''
- END IF;
--2.判断是否使用like 这个应该跟varchar2类型配合
- IF use_like THEN
- logic_symbol:=' like ';
- data_value := '''%'||find_value||'%''';--注意:pl/sql显示' '为''
- END IF;
- --
- FOR aot IN ot(name_like)
- LOOP
- FOR atc IN tc(aot.uname,aot.tname)
- LOOP
- foundcount :=0;
- IF atc.DATA = data_type THEN
- sqlstr := 'select count(*) from '|| atc.uname ||'.' || atc.tab || ' where ' || atc.col
- || logic_symbol || data_value;
- --dbms_output.put_line(sqlstr);--debug用
- EXECUTE immediate sqlstr INTO foundcount;
- IF foundcount > 0 THEN
- findsqlstr := 'select * from ' || atc.uname ||'.' || atc.tab || ' where ' || atc.col ||
- logic_symbol || data_value||';--'||foundcount;
- dbms_output.put_line(findsqlstr);
- END IF;
- END IF;
- END LOOP;
- END LOOP;
- dbms_output.put_line('查找结束');
- END;
以上就是oracle查询所有表的方法介绍。
【编辑推荐】