Oracle的sql*plus是與oracle進行交互的客戶端工具
我們通常所說的DML
除了sql*plus語句
下面就介紹一下一些常用的sql*plus命令
SQL>start file_name
SQL>@ file_name
我們可以將多條sql語句保存在一個文本文件中
SQL>edit
SQL>/
SQL> SPOOL file_name
在屏幕上的所有內容都包含在該文件中
SQL> SPOOL OFF
只有關閉spool輸出
SQL> desc table_name
主要格式化列的顯示形式
該命令有許多選項
COL[UMN] [{ column|expr} [ option
Option選項可以是如下的子句:
ALI[AS] alias
CLE[AR]
FOLD_A[FTER]
FOLD_B[EFORE]
FOR[MAT] format
HEA[DING] text
JUS[TIFY] {L[EFT]|C[ENTER]|C[ENTRE]|R[IGHT]}
LIKE { expr|alias}
NEWL[INE]
NEW_V[ALUE] variable
NOPRI[NT]|PRI[NT]
NUL[L] text
OLD_V[ALUE] variable
ON|OFF
WRA[PPED]|WOR[D_WRAPPED]|TRU[NCATED]
COLUMN column_name HEADING column_heading
For example:
Sql>select * from dept;
DEPTNO DNAME LOC
sql>col LOC heading location
sql>select * from dept;
DEPTNO DNAME location
Sql>select * from emp
Department name Salary
SQL> COLUMN ENAME HEADING
Sql>select * from emp
Employee
Department name Salary
note: the col heading turn into two lines from one line
FOR[MAT] format
Sql>select empno
EMPNO ENAME JOB
Sql> col ename format a
EMPNO ENAME JOB
JUS[TIFY] {L[EFT]|C[ENTER]|C[ENTRE]|R[IGHT]}
SQL> col ename justify center
SQL> /
EMPNO ENAME JOB
對於NUMBER型的列
NOPRI[NT]|PRI[NT]
SQL> col job noprint
SQL> /
EMPNO ENAME
SQL> COLUMN SAL FORMAT $
SQL> /
Employee
Department Name Salary Commission
COMM NUL[L] text
SQL>COL COMM NUL[L] text
WRA[PPED]|WOR[D_WRAPPED]|TRU[NCATED]
COL
HOW ARE YOU?
SQL>COL COL
SQL>COL COL
COL
HOW A
RE YO
U?
SQL> COL COL
COL
HOW
ARE
YOU?
SQL> COL COL
COL
HOW A
SQL> COLUMN column_name
SQL> CLEAR COLUMNS
BREAK ON break_column
SQL> BREAK ON DEPTNO
SQL> SELECT DEPTNO
FROM EMP
WHERE SAL <
ORDER BY DEPTNO;
DEPTNO ENAME SAL
MILLER
ADAMS
BREAK ON break_column SKIP n
SQL> BREAK ON DEPTNO SKIP
SQL> /
DEPTNO ENAME SAL
MILLER
ADAMS
SQL> BREAK
SQL> CLEAR BREAKS
該命令包含許多子命令
SET system_variable value
system_variable value 可以是如下的子句之一
APPI[NFO]{ON|OFF|text}
ARRAY[SIZE] {
AUTO[COMMIT]{ON|OFF|IMM[EDIATE]|n}
AUTOP[RINT] {ON|OFF}
AUTORECOVERY [ON|OFF]
AUTOT[RACE] {ON|OFF|TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
BLO[CKTERMINATOR] {
CMDS[EP] {;|c|ON|OFF}
COLSEP {_|text}
COM[PATIBILITY]{V
CON[CAT] {
COPYC[OMMIT] {
COPYTYPECHECK {ON|OFF}
DEF[INE] {&|c|ON|OFF}
DESCRIBE [DEPTH {
ECHO {ON|OFF}
EDITF[ILE] file_name[
EMB[EDDED] {ON|OFF}
ESC[APE] {\|c|ON|OFF}
FEED[BACK] {
FLAGGER {OFF|ENTRY |INTERMED[IATE]|FULL}
FLU[SH] {ON|OFF}
HEA[DING] {ON|OFF}
HEADS[EP] {||c|ON|OFF}
INSTANCE [instance_path|LOCAL]
LIN[ESIZE] {
LOBOF[FSET] {n|
LOGSOURCE [pathname]
LONG {
LONGC[HUNKSIZE] {
MARK[UP] HTML [ON|OFF] [HEAD text] [BODY text] [ENTMAP {ON|OFF}] [SPOOL
{ON|OFF}] [PRE[FORMAT] {ON|OFF}]
NEWP[AGE] {
NULL text
NUMF[ORMAT] format
NUM[WIDTH] {
PAGES[IZE] {
PAU[SE] {ON|OFF|text}
RECSEP {WR[APPED]|EA[CH]|OFF}
RECSEPCHAR {_|c}
SERVEROUT[PUT] {ON|OFF} [SIZE n] [FOR[MAT] {WRA[PPED]|WOR[D_
WRAPPED]|TRU[NCATED]}]
SHIFT[INOUT] {VIS[IBLE]|INV[ISIBLE]}
SHOW[MODE] {ON|OFF}
SQLBL[ANKLINES] {ON|OFF}
SQLC[ASE] {MIX[ED]|LO[WER]|UP[PER]}
SQLCO[NTINUE] {> |text}
SQLN[UMBER] {ON|OFF}
SQLPRE[FIX] {#|c}
SQLP[ROMPT] {SQL>|text}
SQLT[ERMINATOR] {;|c|ON|OFF}
SUF[FIX] {SQL|text}
TAB {ON|OFF}
TERM[OUT] {ON|OFF}
TI[ME] {ON|OFF}
TIM
From:http://tw.wingwit.com/Article/program/Oracle/201311/17115.html