Oracle打印用户下所有的表的表名,字段名,以及前十条数据
工作中遇到这样一个需求
打印出scott用户下所有表的表名,字段名,以及每张表的前十条数据。
分析
1.先在user_tables循环打印表名
2.在1的条件下打印字段名
3.在1的条件下,紧跟2打印
效果图
PL/SQL代码
(不知道怎么设置代码格式,显示有点问题,直接全部复制到plsqldevelop上跑就可以了)
--SP_LAJI_01为主存储过程,先跑FC_LAJI_01,再跑SP_LAJI_04,最后跑SP_LAJI_01
create or replace procedure SP_LAJI_01
as
cursor cur1 is select table_name from user_tables;
cursor cur2 is select table_name,COLUMN_NAME from user_tab_columns ;
begin
for x in cur1 loop
dbms_output.put_line(x.table_name);
for y in cur2 loop
if x.table_name=y.table_name then
dbms_output.put(y.COLUMN_NAME||' ');
end if;
end loop;
dbms_output.put_line('');
SP_LAJI_04(x.table_name);
end loop;
end;
------
call SP_LAJI_01();
-----
CREATE OR REPLACE PROCEDURE SP_LAJI_04(TNAME VARCHAR2) AS
V_SQL VARCHAR2(3000);
CUR1 SYS_REFCURSOR;
TYPE COLCON IS TABLE OF VARCHAR2(100)/*USER_TAB_COLUMNS.TABLE_NAME%TYPE*/;
COLS COLCON;
col_length NUMBER;
BEGIN
OPEN CUR1 FOR
SELECT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE TABLE_NAME = TNAME;
FETCH CUR1 BULK COLLECT
INTO COLS;
CLOSE CUR1;
col_length:=cols.last;
V_SQL := 'declare
cur_cursor sys_refcursor;
c_rowtemp ' || TNAME || '%rowtype;
collength number:='||col_length||';
begin
open cur_cursor for select * from ' || TNAME ||
' where rownum<=10;
loop
fetch cur_cursor into c_rowtemp;
exit when cur_cursor%notfound;'||FC_LAJI_01(TNAME)||'
dbms_output.put_line('' '');
end loop;
end; ';
EXECUTE IMMEDIATE V_SQL;
END;
---------
CALL SP_LAJI_04('EMP');
---------
CREATE OR REPLACE FUNCTION FC_LAJI_01(tname VARCHAR2) RETURN VARCHAR2
AS
CUR1 SYS_REFCURSOR;
TYPE COLCON IS TABLE OF USER_TAB_COLUMNS.TABLE_NAME%TYPE;
COLS COLCON;
i NUMBER:=1;
mess VARCHAR2(3000);
BEGIN
OPEN CUR1 FOR
SELECT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE TABLE_NAME = TNAME;
FETCH CUR1 BULK COLLECT
INTO COLS;
CLOSE CUR1;
WHILE i<=cols.last LOOP
-- dbms_output.put_line(i);
mess:=mess||'dbms_output.put(c_rowtemp.' || COLS(i) || '||'' '' );';
i:=i+1;
END LOOP;
RETURN mess;
END;