sql* plus 基本命令

最新推荐文章于 2020-04-01 15:45:51 发布

congzhuyi6534

最新推荐文章于 2020-04-01 15:45:51 发布

阅读量156

点赞数

忙完一阵后，可以复习下之前看过的书籍，顺便做下笔记，实践一番，加深印象。

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 " body {font:10pt Arial,Helvetica,sans-serif; color:black; background:White;} p {font:10pt Arial,Helvetica,sans-serif; color:black; background:White;} table,tr,td {font:10pt Arial,Helvetica,sans-serif; color:Black; background:#f7f7e7; padding:0px 0px 0px 0px; margin:0px 0px 0px 0px;} th {font:bold 10pt Arial,Helvetica,sans-serif; color:#336699; background:#cccc99; padding:0px 0px 0px 0px;} h1 {font:16pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; border-bottom:1px solid #cccc99; margin-top:0pt; margin-bottom:0pt; padding:0px 0px 0px 0px;} h2 {font:bold 10pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; margin-top:4pt; margin-bottom:0pt;} a {font:9pt Arial,Helvetica,sans-serif; color:#663300; background:#ffffff; margin-top:0pt; margin-bottom:0pt; vertical-align:top;}SQL*Plus Report" BODY "" TABLE "border='1' width='90%' align='center' summary='Script. output'" SPOOL OFF ENTMAP ON PRE OFF
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

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