Oracle的sql*plus是与oracle进行交互的客户端工具。
在sql*plus中，可以运行sql*plus命令与sql*plus语句。
我们通常所说的DML（数据操纵语言）、DDL（数据定义语言）、DCL（数据库控制语言）都是sql*plus语句，它们执行完后，都可以保存在一个被称为sql buffer的内存区域中，并且只能保存一条最近执行的sql语句，我们可以对保存在sql buffer中的sql 语句进行修改，然后再次执行，sql*plus一般都与数据库打交道。除了sql*plus语句，在sql*plus中执行的其它语句我们称之为sql*plus命令。它们执行完后，不保存在sql buffer的内存区域中，它们一般用来对输出的结果进行格式化显示，以便于制作报表。
下面就介绍一下一些常用的sql*plus命令：
1. 执行一个SQL脚本文件
[sql] view plaincopyprint?
- SQL>start file_name
- SQL>@ file_name
2. 对当前的输入进行编辑
[sql] view plaincopyprint?
- SQL>edit
[sql] view plaincopyprint?
- SQL>/
[sql] view plaincopyprint?
- SQL> SPOOL file_name
5. 关闭spool输出
[sql] view plaincopyprint?
- SQL> SPOOL OFF
6．显示一个表的结构
[sql] view plaincopyprint?
- SQL> desc table_name
主要格式化列的显示形式。
该命令有许多选项，具体如下：
[sql] view plaincopyprint?
- COL[UMN] [{ column|expr} [ option ...]]
[sql] view plaincopyprint?
- 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]
[sql] view plaincopyprint?
- COLUMN column_name HEADING column_heading
- r example:
[sql] view plaincopyprint?
- Sql>select * from dept;
- DEPTNO DNAME LOC
- ---------- ---------------------------- ---------
- 10 ACCOUNTING NEW YORK
[sql] view plaincopyprint?
- sql>col LOC heading location
- sql>select * from dept;
- DEPTNO DNAME location
- --------- ---------------------------- -----------
- 10 ACCOUNTING NEW YORK
[sql] view plaincopyprint?
- Sql>select * from emp
- Department name Salary
- ---------- ---------- ----------
- 10 aaa 11
[sql] view plaincopyprint?
- SQL> COLUMN ENAME HEADING 'Employee|Name’
- Sql>select * from emp
- Employee
- Department name Salary
- ---------- ---------- ----------
- 10 aaa 11
- note: the col heading turn into two lines from one line.
[sql] view plaincopyprint?
- FOR[MAT] format
- Sql>select empno,ename,job from emp;
- EMPNO ENAME JOB
- ---------- ---------- ---------
- 7369 SMITH CLERK
- 7499 ALLEN SALESMAN
- 7521 WARD SALESMAN
[sql] view plaincopyprint?
- Sql> col ename format a40
- EMPNO ENAME JOB
- ---------- ---------------------------------------- ---------
- 7369 SMITH CLERK
- 7499 ALLEN SALESMAN
- 7521 WARD SALESMAN
[sql] view plaincopyprint?
- <span style="font-size:18px;"> JUS[TIFY] {L[EFT]|C[ENTER]|C[ENTRE]|R[IGHT]}
- SQL> col ename justify center
- SQL> /
- EMPNO ENAME JOB
- ---------- ---------------------------------------- ---------
- 7369 SMITH CLERK
- 7499 ALLEN SALESMAN
- 7521 WARD SALESMAN</span>
5). 不让一个列显示在屏幕上
[sql] view plaincopyprint?
- NOPRI[NT]|PRI[NT]
- SQL> col job noprint
- SQL> /
- EMPNO ENAME
- ---------- ----------------------------------------
- 7369 SMITH
- 7499 ALLEN
- 7521 WARD
[sql] view plaincopyprint?
- SQL> COLUMN SAL FORMAT $99,990
- SQL> /
- Employee
- Department Name Salary Commission
- ---------- ---------- --------- ----------
- 30 ALLEN $1,600 300
[sql] view plaincopyprint?
- COMM NUL[L] text
- L>COL COMM NUL[L] text
[sql] view plaincopyprint?
- WRA[PPED]|WOR[D_WRAPPED]|TRU[NCATED]
- COL1
- --------------------
- HOW ARE YOU?
- SQL>COL COL1 FORMAT A5
- SQL>COL COL1 WRAPPED
- COL1
- -----
- HOW A
- RE YO
- U?
- SQL> COL COL1 WORD_WRAPPED
- COL1
- -----
- HOW
- ARE
- YOU?
- SQL> COL COL1 WORD_WRAPPED
- COL1
- -----
- HOW A
[sql] view plaincopyprint?
- SQL> COLUMN column_name
[delphi] view plaincopyprint?
- SQL> CLEAR COLUMNS
[sql] view plaincopyprint?
- <span style="font-size:18px;"> BREAK ON break_column
- SQL> BREAK ON DEPTNO
- SQL> SELECT DEPTNO, ENAME, SAL
- FROM EMP
- WHERE SAL < 2500
- ORDER BY DEPTNO;
- DEPTNO ENAME SAL
- ---------- ----------- ---------
- 10 CLARK 2450
- MILLER 1300
- 20 SMITH 800
- ADAMS 1100</span>
9. 在上面屏蔽掉一个列中显示的相同的值的显示中，每当列值变化时在值变化之前插入n个空行。
[sql] view plaincopyprint?
- <span style="font-size:18px;"> BREAK ON break_column SKIP n
- SQL> BREAK ON DEPTNO SKIP 1
- SQL> /
- DEPTNO ENAME SAL
- ---------- ----------- ---------
- 10 CLARK 2450
- MILLER 1300
- 20 SMITH 800
- ADAMS 1100
- </span>
10. 显示对BREAK的设置
[sql] view plaincopyprint?
- SQL> BREAK
11. 删除6、7的设置
[sql] view plaincopyprint?
- SQL> CLEAR BREAKS
12. Set 命令：
该命令包含许多子命令：
[sql] view plaincopyprint?
- SET system_variable value
[sql] view plaincopyprint?
- APPI[NFO]{ON|OFF|text}
- ARRAY[SIZE] {15|n}
- 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] {.|c}
- CMDS[EP] {;|c|ON|OFF}
- COLSEP {_|text}
- COM[PATIBILITY]{V7|V8|NATIVE}
- CON[CAT] {.|c|ON|OFF}
- COPYC[OMMIT] {0|n}
- COPYTYPECHECK {ON|OFF}
- DEF[INE] {&|c|ON|OFF}
- DESCRIBE [DEPTH {1|n|ALL}][LINENUM {ON|OFF}][INDENT {ON|OFF}]
- ECHO {ON|OFF}
- EDITF[ILE] file_name[.ext]
- EMB[EDDED] {ON|OFF}
- ESC[APE] {\|c|ON|OFF}
- FEED[BACK] {6|n|ON|OFF}
- 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] {80|n}
- LOBOF[FSET] {n|1}
- LOGSOURCE [pathname]
- LONG {80|n}
- LONGC[HUNKSIZE] {80|n}
- MARK[UP] HTML [ON|OFF] [HEAD text] [BODY text] [ENTMAP {ON|OFF}] [SPOOL
- {ON|OFF}] [PRE[FORMAT] {ON|OFF}]
- NEWP[AGE] {1|n|NONE}
- NULL text
- NUMF[ORMAT] format
- NUM[WIDTH] {10|n}
- PAGES[IZE] {24|n}
- 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
本文转自博客园xingoo的博客，原文链接：Oracle基础知识-sqlplus使用，如需转载请自行联系原博主。
