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

dn001 2009-06-02 00:26:26

在大家写脚本的时候，我们常常用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

已选择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

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>