===========================================================

SQLPLUS中的set小节

作者: jimmyhe1981(http://jimmyhe1981.itpub.net)
发表于: 2006.09.14 21:29
分类: Study note
出处: http://jimmyhe1981.itpub.net/post/19858/207902
---------------------------------------------------------------

在写脚本的时候，往往需要对输出的格式进行规范，我们常常用set语句在设置一些需要的格式规范。下面，列举了几个常用的set ，和他们的应用。

SQL> set echo on---------------------------------------------------设置运行命令是是否显示语句

SQL> set feedback on----------------------------------------------设置显示“已选择XX行”

SQL> set colsep | ---------------------------------------------------设置列与列之间的分割符号

SQL> set pagesize 10-----------------------------------------------设置每一页的行数

SQL> SET SERVEROUTPUT ON-------------------------------设置允许显示输出类似dbms_output

SQL> set heading on------------------------------------------------设置显示列名

SQL> set timing on--------------------------------------------------设置显示“已用时间：XXXX”

SQL> set time on-----------------------------------------------------设置显示当前时间

SQL> set autotrace on-----------------------------------------------设置允许对执行的sql进行分析

00:38:28 SQL> set echo on

00:38:37 SQL> @E:echo.sql

00:38:41 SQL> select status from v$datafile where rownum<6;

STATUS

-------

SYSTEM

ONLINE

已选择5行。

已用时间: 00: 00: 01.08

00:38:43 SQL> set echo off

00:38:58 SQL> @E:echo.sql

STATUS

-------

SYSTEM

ONLINE

已选择5行。

已用时间: 00: 00: 01.09

00:39:08 SQL>

SQL> set feedback on
SQL> set colsep |SQL> set pagesize 10
SQL> set serveroutput onSQL> set heading on
SQL> set timing on
SQL> set time on00:54:11 SQL> SELECT FILE#, STATUS, ENABLED FROM V$DATAFILE;

FILE#|STATUS |ENABLED----------|-------|----------
1|SYSTEM |READ WRITE
2|ONLINE |READ WRITE
3|ONLINE |READ WRITE
4|ONLINE |READ WRITE
5|ONLINE |READ WRITE
6|ONLINE |READ WRITE
7|ONLINE |READ WRITE

FILE#|STATUS |ENABLED----------|-------|----------
9|ONLINE |READ WRITE
10|ONLINE |READ WRITE
11|ONLINE |READ WRITE
12|ONLINE |READ WRITE11 rows selected.

Elapsed: 00:00:01.0700:54:34

SQL> DECLARE

00:55:00 2 BEGIN

00:55:00 3 DBMS_OUTPUT.PUT_LINE('===This is dbms_output.put_line!===');

00:55:00 4 END;

00:55:02 5 /
===This is dbms_output.put_line!===

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.0200:55:05 SQL>

SQL> set autotrace on
SQL> SELECT E.LAST_NAME, D.DEPARTMENT_NAME, D.LOCATION_ID
2 FROM HR.EMPLOYEES E
3 JOIN HR.DEPARTMENTS D
4 USING (DEPARTMENT_ID);

LAST_NAME DEPARTMENT_NAME LOCATION_ID
------------------------- ------------------------------ -----------
King Executive 1700
Kochhar Executive 1700
De Haan Executive 1700
Hunold IT 1400
Ernst IT 1400……

106 rows selected.

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=106 Bytes=318
0)

1 0 HASH JOIN (Cost=5 Card=106 Bytes=3180)
2 1 TABLE ACCESS (FULL) OF 'DEPARTMENTS' (Cost=2 Card=27 Byt
es=513)

3 1 TABLE ACCESS (FULL) OF 'EMPLOYEES' (Cost=2 Card=107 Byte
s=1177)

Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
23 consistent gets
12 physical reads
0 redo size
2801 bytes sent via SQL*Net to client
580 bytes received via SQL*Net from client
9 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
106 rows processed

SQL>

jimmyhe1981 发表于:2006.09.14 21:29 ::分类: ( Study note ) ::阅读:(1648次) :: 评论 (0)

发表评论

«	五月 2012					»
一	二	三	四	五	六	日
	1	2	3	4	5	6
7	8	9	10	11	12	13
14	15	16	17	18	19	20
21	22	23	24	25	26	27
28	29	30	31

«	五月 2012					»
一	二	三	四	五	六	日
	1	2	3	4	5	6
7	8	9	10	11	12	13
14	15	16	17	18	19	20
21	22	23	24	25	26	27
28	29	30	31

«	五月 2012					»
一	二	三	四	五	六	日
	1	2	3	4	5	6
7	8	9	10	11	12	13
14	15	16	17	18	19	20
21	22	23	24	25	26	27
28	29	30	31