随笔- 15
文章- 0
评论- 8
set line 32767
set heading off
set feedback off
set echo off
SET NEWPAGE none
SET TRIMSPOOL off
SET SQLBLANKLINES off
SET TRIMOUT on
SET TRIMS on
set timing off
SET VER OFF
DEFINE V_TABLE_NAME='ST_RNFL_R'
DEFINE V_OWNER='DISASTER'
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'
COLUMN MYSQL FORMAT A32767
set array 5000
SPOOL 1.SQL
SELECT 'SPOOL &V_TABLE_NAME'||'.SQL' TEXT FROM DUAL;
WITH DM_TBLS AS
(SELECT DECODE(T.DATA_TYPE,
'CHAR',
'''''''''||' || T.COLUMN_NAME || '||''''''''',
'VARCHAR2',
'''''''''||' || T.COLUMN_NAME || '||''''''''',
'DATE',
'''TO_DATE(''''''||to_char(' || T.COLUMN_NAME ||
',''YYYY-MM-DD HH24:MI:SS'')||'''''',''''YYYY-MM-DD HH24:MI:SS'''')''',
T.COLUMN_NAME) COLUMN_NAME,
T.COLUMN_ID,
T.TABLE_NAME,
T.OWNER,
T.COLUMN_NAME COL1,
LAG(COLUMN_ID) OVER(PARTITION BY T.TABLE_NAME ORDER BY T.COLUMN_ID) RN
FROM DBA_TAB_COLUMNS T
WHERE T.TABLE_NAME = UPPER('&V_TABLE_NAME')
AND T.OWNER = UPPER('&V_OWNER'))
SELECT --T.TABLE_NAME,T.OWNER,
'SELECT ''INSERT INTO &V_OWNER . &V_TABLE_NAME VALUES (''||' ||
REPLACE(SUBSTRB(MAX(CHR(64 + LEVEL) || SYS_CONNECT_BY_PATH(T.COLUMN_NAME, '#')),
3),
'#',
'||'',''||') || '||'');'' TEXT FROM ' || T.OWNER || '.' || T.TABLE_NAME || ';' MYSQL
FROM DM_TBLS T
START WITH RN IS NULL
CONNECT BY RN = PRIOR COLUMN_ID
AND TABLE_NAME = PRIOR TABLE_NAME
GROUP BY T.OWNER,
T.TABLE_NAME;
SELECT 'COMMIT;' TEXT FROM DUAL;
SPOOL OFF
@1.SQL
EXIT
set heading off
set feedback off
set echo off
SET NEWPAGE none
SET TRIMSPOOL off
SET SQLBLANKLINES off
SET TRIMOUT on
SET TRIMS on
set timing off
SET VER OFF
DEFINE V_TABLE_NAME='ST_RNFL_R'
DEFINE V_OWNER='DISASTER'
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'
COLUMN MYSQL FORMAT A32767
set array 5000
SPOOL 1.SQL
SELECT 'SPOOL &V_TABLE_NAME'||'.SQL' TEXT FROM DUAL;
WITH DM_TBLS AS
(SELECT DECODE(T.DATA_TYPE,
'CHAR',
'''''''''||' || T.COLUMN_NAME || '||''''''''',
'VARCHAR2',
'''''''''||' || T.COLUMN_NAME || '||''''''''',
'DATE',
'''TO_DATE(''''''||to_char(' || T.COLUMN_NAME ||
',''YYYY-MM-DD HH24:MI:SS'')||'''''',''''YYYY-MM-DD HH24:MI:SS'''')''',
T.COLUMN_NAME) COLUMN_NAME,
T.COLUMN_ID,
T.TABLE_NAME,
T.OWNER,
T.COLUMN_NAME COL1,
LAG(COLUMN_ID) OVER(PARTITION BY T.TABLE_NAME ORDER BY T.COLUMN_ID) RN
FROM DBA_TAB_COLUMNS T
WHERE T.TABLE_NAME = UPPER('&V_TABLE_NAME')
AND T.OWNER = UPPER('&V_OWNER'))
SELECT --T.TABLE_NAME,T.OWNER,
'SELECT ''INSERT INTO &V_OWNER . &V_TABLE_NAME VALUES (''||' ||
REPLACE(SUBSTRB(MAX(CHR(64 + LEVEL) || SYS_CONNECT_BY_PATH(T.COLUMN_NAME, '#')),
3),
'#',
'||'',''||') || '||'');'' TEXT FROM ' || T.OWNER || '.' || T.TABLE_NAME || ';' MYSQL
FROM DM_TBLS T
START WITH RN IS NULL
CONNECT BY RN = PRIOR COLUMN_ID
AND TABLE_NAME = PRIOR TABLE_NAME
GROUP BY T.OWNER,
T.TABLE_NAME;
SELECT 'COMMIT;' TEXT FROM DUAL;
SPOOL OFF
@1.SQL
EXIT
将上述脚本保存为gen_insert.sql,然后以sqlplus user/password@tnsname,就可以轻松地将将oracle表内容导出为insert into语句，oracle执行完后，就可以在目录下找到一个以 表名.sql的文件。
posted @ 2011-07-01 15:36 发仔 阅读(168) 评论(0) 编辑