Oracle数据库SQLPLUS中几个常用set语句

7/28/2008来源:Oracle教程人气:3685

在大家写脚本的时候,我们常常用set语句在设置一些需要的格式规范。下面,列举了几个常见set语句的应用。


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


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


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


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


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


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


ONLINE


ONLINE


ONLINE


已选择5行。


已用时间: 00: 00: 01.08


00:38:43 SQL> set echo off


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


STATUS


——-


SYSTEM


ONLINE


ONLINE


ONLINE


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>