Oracle常用plsql

String aggr

http://www.oracle-base.com/articles/misc/string-aggregation-techniques.php

   

SELECT

'SELECT '

||

COLUMNLISTS

||' FROM ' || TBL.TABLE_NAME || ';'AS SELECTSQL,

'SELECT COUNT(*) AS CNT FROM ' || TBL.TABLE_NAME || ';' COUNTSQL

FROM user_tables TBL

JOIN

(

SELECT

TABLE_NAME,

LISTAGG(column_name,',') within group(order by column_id) as columnlists

FROM

USER_TAB_COLUMNS

WHERE TABLE_NAME LIKE 'TMM_S_SH%'

AND TABLE_NAME NOT LIKE 'TMM_S_SH%_BAK'

GROUP BY TABLE_NAME

) TC

ON TBL.TABLE_NAME=TC.TABLE_NAME

WHERE (

TBL.TABLE_NAME LIKE 'TMM_S_SH%'

AND TBL.TABLE_NAME NOT LIKE 'TMM_S_SH%_BAK'

)

;

 

SELECT

TABLE_NAME,

LISTAGG(column_name,',') within group(order by column_id) as columnlists

FROM

USER_TAB_COLUMNS

WHERE TABLE_NAME LIKE 'TMM_S_SH%'

AND TABLE_NAME NOT LIKE 'TMM_S_SH%_BAK'

GROUP BY TABLE_NAME;

   

   

---check the oracle version with sql

select * from v$version;

SELECT * FROM USER_TABLES WHERE ROWNUM<10;

select * from user_tab_columns where rownum<10;

 

 

SELECT

chr(34) || COLUMN_NAME || chr(34) || ','

FROM

USER_TAB_COLUMNS

WHERE TABLE_NAME='TMM_S_SH_VENDOR_SUPPORT';

   

INSERT INTO TABLENAME

SELECT 1,

test

q'[sqlwithsinglequote]'

FROM DUAL;

   

SET SERVEROUTPUT ON;

DECLARE

V_ROWS_COUNT INT :=0;

V_ERRORMSG_OUT VARCHAR2(2000) :='';

CURSOR TMM_SH_TABLES

IS

SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_Name LIKE 'T_%_T_%';

BEGIN

FOR TABLE_REC IN TMM_SH_TABLES

LOOP

EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || TABLE_REC.TABLE_NAME INTO V_ROWS_COUNT;

DBMS_OUTPUT.put_line ('SELECT COUNT(*) AS CNT FROM ' || TABLE_REC.TABLE_NAME || ';---' || V_ROWS_COUNT);

--EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || TABLE_REC.TABLE_NAME ;

--SP_NAME(PARA_NAME => TABLE_REC.TABLE_NAME ,BATCH_NUMBER => 1,ERRMSG => V_ERRORMSG_OUT);

END LOOP;

END;