试试刚学到的新技巧：(10G)
WITH
s AS (SELECT SUBSTR(SYS_CONNECT_BY_PATH('SELECT '''||TABLE_NAME||''' AS TABLE_NAME,MAX(ID) AS MAX_ID FROM '||TABLE_NAME,' UNION ALL '),12)
AS sql_text
FROM (
SELECT TABLE_NAME,ROWNUM AS RN FROM USER_TAB_COLUMNS WHERE COLUMN_NAME = 'ID' AND DATA_TYPE='NUMBER'
)
WHERE CONNECT_BY_ISLEAF=1
START WITH RN=1
CONNECT BY RN = PRIOR RN+1
)
,x AS
(
SELECT DBMS_XMLGEN.getxmltype (sql_text) xml_string
FROM s
)
SELECT table_name,max_id
FROM x,
XMLTABLE
(
'$x2/ROWSET/ROW' PASSING xml_string AS "x2"
COLUMNS TABLE_NAME VARCHAR2(30),MAX_ID NUMBER
) x2
;
注意这是我的环境，我有一些表具有ID字段，我用动态SQL把MAX(ID)取出来。你要相应修改为RQ列名。
|
