sql* plus 基本命令
忙完一阵后，可以复习下之前看过的书籍，顺便做下笔记，实践一番，加深印象。
1.Command Definition
remark 注释
set headsep 标题分隔符标识一个字符，用于将标题分为两行或多行
ttitle 设置每页的顶部标题
btitle 设置每页的尾部标题
column 关于列的标题，格式与处理的命令
break on 在报表的各部分插入空格，或者将小计与总计断开
compute sum 计算小计
set linesize 设置最大字符数
set pagesize 设置每页最大行数
set newpage 设置页与页之间的空行数
spool 将屏幕上显示的报表导入一个文件中，以便打印
/**/ 多行 注释
-- 单行注释，类似于remark
set pause 显示页与页之间的停顿
save 将查询结果保存中一个指定的文件中
host 向OS发送命令
start or @ 执行文件中的命令
edit 使用户离开sql * plus 并进入所选的编辑器
define _editor 告知sql * plus 默认的编辑器
exit or quit 退出 SQLPLUS.
2. 示例
rem Bookshelf activity report --注释
set headsep ! --列分割符，默认是|
ttitle 'Checkout Log for 1/1/02-3/31/02' --页顶部标题
btitle 'from the Bookshelf' --页尾标题
column Name format a20 --Name列宽20个字符
column Title format a20 word_wrapped --title列20个字符 可以超过20个字符，不会换行
column DaysOut format 999.99 --DaysOut 5位有效数字
column DaysOut heading 'Days!Out' --列daysout将显示为Days 换行 Out.因为set headsep
break on Name skip 1 on report --如果Name相同，则不用显示 break on Name duplicate 会显示重复的值，必须
--是order by 子句中的列。
compute avg of DaysOut on Name --相同的Name，计算Daysout的平均值
compute avg of DaysOut on report
set linesize 80
set pagesize 60
set newpage 0 --每页开头多少条空白行
set feedback off --关闭回显
set pause 'More . . . ' --设置页与页之间的停顿
set pause on
spool activity.lst
select Name, Title, CheckoutDate, ReturnedDate,
ReturnedDate-CheckoutDate as DaysOut /*Count Days*/
from BOOKSHELF_CHECKOUT
order by Name, CheckoutDate;
spool off
break on 必须有一个相关的Order by ；
compute avg 必须有一个相关的break on.
3 list del append input
SQL> select empno,ename,
2 job,sal
3 deptno
4 from emp;
EMPNO ENAME JOB
---------- ---------- --------- ---
7369 SMITH CLERK
7499 ALLEN SALESMAN
7521 WARD SALESMAN
7566 JONES MANAGER
7654 MARTIN SALESMAN
7698 BLAKE MANAGER
7782 CLARK MANAGER
7788 SCOTT ANALYST
7839 KING PRESIDENT
7844 TURNER SALESMAN
7876 ADAMS CLERK
EMPNO ENAME JOB
---------- ---------- --------- ---
7900 JAMES CLERK
7902 FORD ANALYST
7934 MILLER CLERK
14 rows selected.
SQL> l 2 4
2 job,sal
3 deptno
4* from emp
SQL> del 2
SQL> list
1 select empno,ename,
2 deptno
3* from emp
SQL> l 1
1* select empno,ename,
SQL> c /empno/mgr
1* select mgr,ename,
SQL> l
1 select mgr,ename,
2 deptno
3* from emp
SQL> l 2
2* deptno
SQL> append "deptnumber" --中间应该两个空格
2* deptno"deptnumber"
SQL> input where empno=7900;
SQL> list
1 select mgr,ename,
2 deptno"deptnumber"
3 where empno=7900
4* from emp
SQL> del 3
SQL> l
1 select mgr,ename,
2 deptno"deptnumber"
3* from emp
SQL> input where empno=7900;
SQL> /
MGR ENAME deptnumber
---------- ---------- ----------
7698 JAMES 30
SQL> delete
2 /
delete
*
ERROR at line 1:
ORA-00903: invalid table name
SQL> list
1* delete
SQL> del 1 2
SQL> list
SP2-0223: No lines in SQL buffer.
SQL> del 1 last --从第一行到最后一行
SP2-0026: No lines to delete.
4 保存 save
SQL> save fred.sql
Created file fred.sql
SQL> save fred.sql
SP2-0540: File "fred.sql" already exists.
Use "SAVE filename[.ext] REPLACE".
SQL> save fred.sql replace
Wrote file fred.sql
SQL> select * from emp;
SQL> save fred.sql app
Appended file to fred.sql
fred.sql:
select empno,job from emp
/
select * from emp
/
5 保存环境store
SQL> store set my_settings.sql create|append|replace
Created file my_settings.sql
my_settings.sql:
set appinfo OFF
set appinfo "SQL*Plus"
set arraysize 15
set autocommit OFF
set autoprint OFF
set autorecovery OFF
set autotrace OFF
set blockterminator "."
set cmdsep OFF
set colsep " "
set compatibility NATIVE
set concat "."
set copycommit 0
set copytypecheck ON
set define "&"
set describe DEPTH 1 LINENUM OFF INDENT ON
set echo OFF
set editfile "afiedt.buf"
set embedded OFF
set escape OFF
set feedback 6
set flagger OFF
set flush ON
set heading ON
set headsep "|"
set linesize 80
set logsource ""
set long 80
set longchunksize 80
set markup HTML OFF HEAD "
set newpage 1
set null ""
set numformat ""
set numwidth 10
set pagesize 14
set pause OFF
set recsep WRAP
set recsepchar " "
set serveroutput OFF
set shiftinout invisible
set showmode OFF
set sqlblanklines OFF
set sqlcase MIXED
set sqlcontinue "> "
set sqlnumber ON
set sqlpluscompatibility 10.2.0
set sqlprefix "#"
set sqlprompt "SQL> "
set sqlterminator ";"
set suffix "sql"
set tab ON
set termout ON
set time OFF
set timing OFF
set trimout ON
set trimspool OFF
set underline "-"
set verify ON
set wrap ON
6
SQL> ttitle
ttitle OFF and is the first few characters of the next SELECT statement
SQL> btitle
btitle OFF and is the first few characters of the next SELECT statement
SQL> break
SP2-0015: no break(s) defined
SQL> compute
SP2-0080: no COMPUTES currently defined
SQL> show headsep
headsep "|" (hex 7c)
SQL> show pagesize
pagesize 14
SQL> show linesize
linesize 80
SQL> show newpage
newpage 1
SQL> clear columns
columns cleared
SQL> clear breaks
breaks cleared
SQL> clear computes
computes cleared
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/20648244/viewspace-681663/ ，如需转载，请注明出处，否则将追究法律责任。
