在大家寫(xiě)腳本的時(shí)候，我們常常用set語(yǔ)句在設(shè)置一些需要的格式規(guī)范。下面，列舉了幾個(gè)常見(jiàn)set語(yǔ)句的應(yīng)用。

SQL> set echo on—————————————————設(shè)置運(yùn)行命令是是否顯示語(yǔ)句

SQL> set feedback on———————————————-設(shè)置顯示“已選擇XX行”

SQL> set colsep | —————————————————設(shè)置列與列之間的分割符號(hào)

SQL> set pagesize 10———————————————–設(shè)置每一頁(yè)的行數(shù)

SQL> SET SERVEROUTPUT ON——————————-設(shè)置允許顯示輸出類似dbms_output.putline

SQL> set heading on————————————————設(shè)置顯示列名

SQL> set timing on————————————————–設(shè)置顯示“已用時(shí)間：XXXX”

SQL> set time on—————————————————–設(shè)置顯示當(dāng)前時(shí)間

SQL> set autotrace on———————————————–設(shè)置允許對(duì)執(zhí)行的sql進(jìn)行分析

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行。

已用時(shí)間: 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行。

已用時(shí)間: 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>