Question

oracle要导出一张表的数据，应该如何用纯语句来写

比如：
SELECT *
FROM (SELECT SUBSTR(TABLE_NAME, 1, LENGTH(TABLE_NAME) - 5) TABLE_NAME,
SUM(COUNTS) COUNTS
FROM GLMIS_TABLES_COUNTS
WHERE SUBSTR(TABLE_NAME, -4, 4) >= '2000'
AND SUBSTR(TABLE_NAME, -4, 4) <= '2010'
GROUP BY SUBSTR(TABLE_NAME, 1, LENGTH(TABLE_NAME) - 5)
UNION ALL
SELECT TABLE_NAME, COUNTS
FROM GLMIS_TABLES_COUNTS
WHERE SUBSTR(TABLE_NAME, -4, 4) < '2000'
OR SUBSTR(TABLE_NAME, -4, 4) > '2010') SRC
ORDER BY SRC.TABLE_NAME ASC

这样的SQL，要如何通过语句写在SHELL脚本中，让它能实现导出来

Answer 1

写一个脚本,内容如下：
sqlplus -s 用户名/密码 <<EOF
set pagesize 0
set line 100
spool 文件名.txt
SELECT *
FROM (SELECT SUBSTR(TABLE_NAME, 1, LENGTH(TABLE_NAME) - 5) TABLE_NAME,
SUM(COUNTS) COUNTS
FROM GLMIS_TABLES_COUNTS
WHERE SUBSTR(TABLE_NAME, -4, 4) >= '2000'
AND SUBSTR(TABLE_NAME, -4, 4) <= '2010'
GROUP BY SUBSTR(TABLE_NAME, 1, LENGTH(TABLE_NAME) - 5)
UNION ALL
SELECT TABLE_NAME, COUNTS
FROM GLMIS_TABLES_COUNTS
WHERE SUBSTR(TABLE_NAME, -4, 4) < '2000'
OR SUBSTR(TABLE_NAME, -4, 4) > '2010') SRC
ORDER BY SRC.TABLE_NAME ASC;

spool off
exit;
EOF

Answer 2

SQL> spool d:\temp.txt;
SQL> select * from table_name直接就可以在d盘中找到