Oracle Database 11g SQL 开发指南学习笔记：使用SQL*Plus

最新推荐文章于 2021-04-09 12:38:01 发布

金素妍

最新推荐文章于 2021-04-09 12:38:01 发布

阅读量1.2k

点赞数

分类专栏： ORACLE SQL

本文链接：https://blog.csdn.net/jinsuyan123/article/details/11833841

版权

ORACLE SQL 专栏收录该内容

10 篇文章 0 订阅

订阅专栏

1、显示表结构

C:\Users\Administrator>sqlplus store/store_password

SQL*Plus: Release 11.2.0.1.0 Production on 星期四 9月 19 19:34:33 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> desc customers;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 CUSTOMER_ID                               NOT NULL NUMBER(38)
 FIRST_NAME                                NOT NULL VARCHAR2(10)
 LAST_NAME                                 NOT NULL VARCHAR2(10)
 DOB                                                DATE
 PHONE                                              VARCHAR2(12)

SQL>

2、编辑SQL语句

append：在当前行添加字符串

del：删除当前行字符串

change：替换当前行的某个字符串

list：显示sql*plus缓冲区的内容

list x：显示第x行的内容

run：运行缓冲区中的语句

/：也是运行缓冲区中的语句

clear buffer：清除缓冲区

Microsoft Windows [版本 6.1.7601]
版权所有 (c) 2009 Microsoft Corporation。保留所有权利。

C:\Users\Administrator>sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on 星期四 9月 19 19:08:24 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

SQL> connect store/store_password;
已连接。
SQL> select customer_id,first_name,last_name
  2  from customers
  3  where customer_id = 1;

CUSTOMER_ID FIRST_NAME LAST_NAME
----------- ---------- ----------
          1 John       Brown

--显示缓冲区中第1行的数据
SQL> 1;
  1* select customer_id,first_name,last_name
--增加字符串到当前行
SQL> append , dob;
  1* select customer_id,first_name,last_name, dob
--显示sql缓冲区中的内容
SQL> list;
  1  select customer_id,first_name,last_name, dob
  2  from customers
  3* where customer_id = 1
--把customer_id = 1替换为customer_id = 2，需要注意的是两者之间不能有空格
SQL> change /customer_id = 1 /customer_id = 2;
SP2-0023: 未找到字符串
SQL> change /customer_id = 1/customer_id = 2;
  3* where customer_id = 2
--运行缓冲区的sql语句
SQL> run;
  1  select customer_id,first_name,last_name, dob
  2  from customers
  3* where customer_id = 2

CUSTOMER_ID FIRST_NAME LAST_NAME  DOB
----------- ---------- ---------- --------------
          2 Cynthia    Green      05-2月 -68

--运行语句
SQL> /

CUSTOMER_ID FIRST_NAME LAST_NAME  DOB
----------- ---------- ---------- --------------
          2 Cynthia    Green      05-2月 -68


SQL> list
  1  select customer_id,first_name,last_name, dob
  2  from customers
  3* where customer_id = 2
SQL> append and first_name ='Cynthia'
  3* where customer_id = 2and first_name ='Cynthia'
  
SQL> list
  1  select customer_id,first_name,last_name, dob
  2  from customers
  3* where customer_id = 2and first_name ='Cynthia'
  
--删除当前行  
SQL> del
SQL> list
  1  select customer_id,first_name,last_name, dob
  2* from customers
  
SQL> del 1
SQL> del 0
SP2-0226: 行号  无效

SQL> list
  1* from customers
  
SQL> del
SQL> append select customer_id,first_name,last_name,dob
SQL> list
  1* select customer_id,first_name,last_name,dob
SQL> append from customers where customer_id = 2
  1* select customer_id,first_name,last_name,dobfrom customers where customer_id = 2
SQL> run
  1* select customer_id,first_name,last_name,dobfrom customers where customer_id = 2
select customer_id,first_name,last_name,dobfrom customers where customer_id = 2
                                                          *
第 1 行出现错误:
ORA-00923: 未找到要求的 FROM 关键字


SQL> change /dobfrom/dob from
  1* select customer_id,first_name,last_name,dob from customers where customer_id = 2
SQL> /

CUSTOMER_ID FIRST_NAME LAST_NAME  DOB
----------- ---------- ---------- --------------
          2 Cynthia    Green      05-2月 -68

SQL> clear buffer
buffer 已清除
SQL> list
SP2-0223: SQL 缓冲区中不存在行。
SQL> 从 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options 断开

3、保存、检索、运行文件

save：保存文件

get：加载文件

start：加载并运行文件，于@是一样的。

edit：修改文件

spool 文件路径：把sql*plus的输出结构写入到文件中

spool off：停止写入文件

C:\Users\Administrator>sqlplus store/store_password

SQL*Plus: Release 11.2.0.1.0 Production on 星期四 9月 19 19:37:07 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select customer_id,first_name,last_name
  2  from customers
  3  where customer_id = 1;

CUSTOMER_ID FIRST_NAME LAST_NAME
----------- ---------- ----------
          1 John       Brown

--把sql*plus缓冲区的内容保存到文件
SQL> save c:\customer_query.sql
已创建 file c:\customer_query.sql

--把文件的内容，加载到sql*plus的缓冲区
SQL> get c:\customer_query.sql
  1  select customer_id,first_name,last_name
  2  from customers
  3* where customer_id = 1
SQL> /

CUSTOMER_ID FIRST_NAME LAST_NAME
----------- ---------- ----------
          1 John       Brown

--删除缓冲区中的内容
SQL> del
SQL> del
SQL> del

--加载并运行文件
SQL> start c:\customer_query.sql

CUSTOMER_ID FIRST_NAME LAST_NAME
----------- ---------- ----------
          1 John       Brown

--编辑指定文件，但修改后不会体现在sql*plus的缓冲区中
SQL> edit c:\customer_query.sql

SQL> list
  1  select customer_id,first_name,last_name
  2  from customers
  3* where customer_id = 1
  
--将sql*plus缓冲区的内容复制到afiedt.buf的文件中，修改并保存后，文件的内容被复制回缓冲区中  
SQL> edit
已写入 file afiedt.buf

  1  select customer_id,first_name,last_name
  2  from customers
  3* where customer_id = 2
SQL> list
  1  select customer_id,first_name,last_name
  2  from customers
  3* where customer_id = 2
SQL> /

CUSTOMER_ID FIRST_NAME LAST_NAME
----------- ---------- ----------
          2 Cynthia    Green

--把sql*plus的输出结果复制到文件中
SQL> spool c:\customer_query.txt
SQL> select * from dual;

D
-
X

--停止把sql*plus的输出结果复制到文件中
SQL> spool off
SQL>

4、格式化列

foramt：数据的显示格式。

heading：设置标题文本。

justify：调整标题的显示，居左、居中、居右。

wrapped：当要显示的文本超过了format设置的长度时，会自动换行，但有可能出现一个单词会显示在2行上。

word_wrapped：同上，只不过不会跨行显示一个单词。

clear：清除某列的格式。

C:\Users\Administrator>sqlplus store/store_password

SQL*Plus: Release 11.2.0.1.0 Production on 星期五 9月 20 11:18:25 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

--设置product_id的格式为2位数字
SQL> column product_id format 99

--设置name的标题文本为product_name，格式为13位的字符，如果超过13位，那么会换行，单个单词不会跨越两行
SQL> column name heading product_name format a13 word_wrapped

SQL> column description format a13 word_wrapped

--price格式为小数点左右都是2位数，最左边加上货币符号$
SQL> column price format $99.99

SQL> select product_id,name,description,price
  2  from products
  3  where product_id < 6;

PRODUCT_ID product_name  DESCRIPTION     PRICE
---------- ------------- ------------- -------
         1 Modern        A                $.80
           Science       description
                         of modern
                         science

         2 Chemistry     Introduction   $30.00
                         to Chemistry

         3 Supernova     A star         $25.99
                         explodes


PRODUCT_ID product_name  DESCRIPTION     PRICE
---------- ------------- ------------- -------
         4 Tank War      Action movie   $13.95
                         about a
                         future war

         5 Z Files       Series on      $49.99
                         mysterious
                         activities


--清除description列的格式
SQL> column description clear
--设置description列的格式为13位字符，超过13个自动换行，可能会导致一个单词跨越两行
SQL> column description format a13 wrapped
SQL> /

PRODUCT_ID product_name  DESCRIPTION   PRICE
---------- ------------- ------------- -------
         1 Modern        A description    $.80
           Science        of modern sc
                         ience

         2 Chemistry     Introduction   $30.00
                         to Chemistry

         3 Supernova     A star explod  $25.99
                         es

         4 Tank War      Action movie   $13.95

PRODUCT_ID product_name  DESCRIPTION   PRICE
---------- ------------- ------------- -------
                         about a futur
                         e war

         5 Z Files       Series on mys  $49.99
                         terious activ
                         ities

SQL> column description clear
SQL> column description format a13 wordwrapped

--清除price的格式
SQL> column price clear

--设置标题显示为左对齐
SQL> column price justify left format $99.99
SQL> /

PRODUCT_ID product_name  DESCRIPTION   PRICE
---------- ------------- ------------- -------
         1 Modern        A                $.80
           Science       description
                         of modern
                         science

         2 Chemistry     Introduction   $30.00
                         to Chemistry

         3 Supernova     A star         $25.99
                         explodes


PRODUCT_ID product_name  DESCRIPTION   PRICE
---------- ------------- ------------- -------
         4 Tank War      Action movie   $13.95
                         about a
                         future war

         5 Z Files       Series on      $49.99
                         mysterious
                         activities


--清除所有的列格式
SQL> clear columns
columns 已清除

--再次运行，发现输出数据的格式很混乱，所以必须要设置列格式
SQL> /

PRODUCT_ID NAME
---------- ------------------------------
DESCRIPTION                                             PRICE
-------------------------------------------------- ----------
         1 Modern Science
A description of modern science                            .8

         2 Chemistry
Introduction to Chemistry                                  30

         3 Supernova
A star explodes                                         25.99


PRODUCT_ID NAME
---------- ------------------------------
DESCRIPTION                                             PRICE
-------------------------------------------------- ----------
         4 Tank War
Action movie about a future war                         13.95

         5 Z Files
Series on mysterious activities                         49.99

SQL>

5、设置页面大小、设置行大小

clear columns：清除所有的列格式。

set pagesize：一页中包含了多少行数据

set linesize：每一行数据的字符数

SQL> clear columns
columns 已清除

--当SQL*Plus的输出结果超过了，默认的一个页能显示的行数，那么SQL*Plus会再次显示标题。
SQL> /

PRODUCT_ID NAME
---------- ------------------------------
DESCRIPTION                                             PRICE
-------------------------------------------------- ----------
         1 Modern Science
A description of modern science                            .8

         2 Chemistry
Introduction to Chemistry                                  30

         3 Supernova
A star explodes                                         25.99


PRODUCT_ID NAME
---------- ------------------------------
DESCRIPTION                                             PRICE
-------------------------------------------------- ----------
         4 Tank War
Action movie about a future war                         13.95

         5 Z Files
Series on mysterious activities                         49.99


--设置页大小为100行数据，下面的结果没有超过100行，所以标题只显示了一次，这样就整齐多了
SQL> set pagesize 100
SQL> /

PRODUCT_ID NAME
---------- ------------------------------
DESCRIPTION                                             PRICE
-------------------------------------------------- ----------
         1 Modern Science
A description of modern science                            .8

         2 Chemistry
Introduction to Chemistry                                  30

         3 Supernova
A star explodes                                         25.99

         4 Tank War
Action movie about a future war                         13.95

         5 Z Files
Series on mysterious activities                         49.99


--设置一行能显示的字符数为100，不过看着一行数据，还是超过了100个字符
SQL> set linesize 100
SQL> /

PRODUCT_ID NAME                           DESCRIPTION
---------- ------------------------------ --------------------------------------------------
     PRICE
----------
         1 Modern Science                 A description of modern science
        .8

         2 Chemistry                      Introduction to Chemistry
        30

         3 Supernova                      A star explodes
     25.99

         4 Tank War                       Action movie about a future war
     13.95

         5 Z Files                        Series on mysterious activities
     49.99

--再尝试一下，总算显示正常了，在不设置列格式的情况下，可以通过设置pagesize和linesize，让输出结果更容易理解
SQL> set linesize 150
SQL> /

PRODUCT_ID NAME                           DESCRIPTION                                             PRICE
---------- ------------------------------ -------------------------------------------------- ----------
         1 Modern Science                 A description of modern science                            .8
         2 Chemistry                      Introduction to Chemistry                                  30
         3 Supernova                      A star explodes                                         25.99
         4 Tank War                       Action movie about a future war                         13.95
         5 Z Files                        Series on mysterious activities                         49.99

SQL>

6、变量

临时表量

C:\Users\Administrator>sqlplus store/store_password

SQL*Plus: Release 11.2.0.1.0 Production on 星期五 9月 20 13:35:51 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

--&符号用来定义临时变量，当运行下面的查询时,SQL*Plus会提示用户为v_product_id输入一个值
--用户输入值后，会显示原值、新值
SQL> select product_id,name,price
  2  from products
  3  where product_id = &v_product_id;
输入 v_product_id 的值:  1
原值    3: where product_id = &v_product_id
新值    3: where product_id = 1

PRODUCT_ID NAME                                PRICE
---------- ------------------------------ ----------
         1 Modern Science                         .8

--禁止显示原值、新值
SQL> set verify off
SQL> /
输入 v_product_id 的值:  2

PRODUCT_ID NAME                                PRICE
---------- ------------------------------ ----------
         2 Chemistry                              30

--再次控制，以显示原值、新值
SQL> set verify on
SQL> /
输入 v_product_id 的值:  3
原值    3: where product_id = &v_product_id
新值    3: where product_id = 3

PRODUCT_ID NAME                                PRICE
---------- ------------------------------ ----------
         3 Supernova                           25.99

--修改变量定义符号，把&改为#
SQL> set define '#'
SQL> edit
已写入 file afiedt.buf

  1  select product_id,name,price
  2  from products
  3* where product_id = #v_product_id
SQL> /
输入 v_product_id 的值:  4
原值    3: where product_id = #v_product_id
新值    3: where product_id = 4

PRODUCT_ID NAME                                PRICE
---------- ------------------------------ ----------
         4 Tank War                            13.95

SQL> set verify on
SQL> set define '&'

--临时变量可以用来替换列名、表名、列值，注意v_col需要输入2次，因为在语句中用到了2次
SQL> edit
已写入 file afiedt.buf

  1  select name,&v_col
  2  from &v_table
  3* where &v_col = &v_val
  
SQL> /
输入 v_col 的值:  product_type_id
原值    1: select name,&v_col
新值    1: select name,product_type_id
输入 v_table 的值:  products
原值    2: from &v_table
新值    2: from products
输入 v_col 的值:  product_type_id
输入 v_val 的值:  1
原值    3: where &v_col = &v_val
新值    3: where product_type_id = 1

NAME                           PRODUCT_TYPE_ID
------------------------------ ---------------
Modern Science                               1
Chemistry                                    1

--通过使用&&符号，使得重复引用&&v_col时，只需要输入一次值，避免了重复输入
SQL> edit
已写入 file afiedt.buf

  1  select name,&&v_col
  2  from &v_table
  3* where &&v_col = &v_val
  
SQL> /
输入 v_col 的值:  product_type_id
原值    1: select name,&&v_col
新值    1: select name,product_type_id
输入 v_table 的值:  products
原值    2: from &v_table
新值    2: from products
输入 v_val 的值:  1
原值    3: where &&v_col = &v_val
新值    3: where product_type_id = 1

NAME                           PRODUCT_TYPE_ID
------------------------------ ---------------
Modern Science                               1
Chemistry                                    1

SQL>

已定义变量

define：定义变量

accept：定义变量，等待用户输入

undefine：删除变量

--定义变量
SQL> define v_product_id = 7

--显示变量的值
SQL> define v_product_id
DEFINE V_PRODUCT_ID    = "7" (CHAR)

--显示所有变量
SQL> define
DEFINE _DATE           = "20-9月 -13" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "orcl" (CHAR)
DEFINE _USER           = "STORE" (CHAR)
DEFINE _PRIVILEGE      = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1102000100" (CHAR)
DEFINE _EDITOR         = "Notepad" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options" (CHAR)
DEFINE _O_RELEASE      = "1102000100" (CHAR)
DEFINE _RC             = "0" (CHAR)
DEFINE V_COL           = "product_type_id" (CHAR)
DEFINE V_PRODUCT_ID    = "7" (CHAR)

--引用v_product_id,这里是通过临时变量来引用这个变量的，由于变量已经定义，所以不需要输入变量值
SQL> select product_id,name,price
  2  from products
  3  where product_id = &v_product_id;
原值    3: where product_id = &v_product_id
新值    3: where product_id = 7

PRODUCT_ID NAME                                PRICE
---------- ------------------------------ ----------
         7 Space Force 9                       13.49

--通过accept来定义变量、指定变量类型、格式、提示文本
SQL> accept v_customer_id number format 99 prompt 'Customer id:'
Customer id:5

SQL> accept v_date date format 'DD-MON-YYYY' prompt 'Date:'
Date:01-1月-2007

--hide表示隐藏输入值
SQL> accept v_password char prompt 'Password:' hide
Password:
SQL> define
DEFINE _DATE           = "20-9月 -13" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "orcl" (CHAR)
DEFINE _USER           = "STORE" (CHAR)
DEFINE _PRIVILEGE      = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1102000100" (CHAR)
DEFINE _EDITOR         = "Notepad" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options" (CHAR)
DEFINE _O_RELEASE      = "1102000100" (CHAR)
DEFINE V_DATE          = "01-1月-2007" (CHAR)
DEFINE V_PASSWORD      = "12345" (CHAR)
DEFINE V_PRODUCT_ID    = "7" (CHAR)

--删除已定义的变量
SQL> undefine v_customer_id
SQL> undefine v_date
SQL> undefine v_password
SQL> undefine v_product_id


SQL> define v_product_id = 7

--直接引用v_product_id变量，会报错，只能通过&v_product_id来引用
SQL> select v_product_id from dual;
select v_product_id from dual
       *
第 1 行出现错误:
ORA-00904: "V_PRODUCT_ID": 标识符无效


SQL> select &v_product_id from dual;
原值    1: select &v_product_id from dual
新值    1: select 7 from dual

         7
----------
         7

7、创建简单的报表

set echo off：禁止显示SQL*Plus中的sql和命令，也就是运行语句或命令后，不回显

在脚本中使用临时变量

SQL> edit
已写入 file afiedt.buf

  1  set echo off
  2  set verify off
  3  select product_id,name,price
  4  from products
  5* where product_id = &v_product_id
  6
SQL> edit c:\report1.sql

SQL> @c:\report1.sql

PRODUCT_ID NAME                                PRICE
---------- ------------------------------ ----------
         7 Space Force 9                       13.49

在脚本中使用已定义变量

SQL> edit c:\report2.sql

SQL> get c:\report2.sql
  1  set echo off
  2  set verify off
  3  accept v_product_id number format 99 prompt 'Enter product id:'
  4  select product_id,name,price
  5  from products
  6  where product_id = &v_product_id;
  7* undefine v_product_id
  8

SQL> start c:\report2.sql
Enter product id:3

PRODUCT_ID NAME                                PRICE
---------- ------------------------------ ----------
         3 Supernova                           25.99

向脚本中的变量传值

SQL> edit c:\report3.sql

SQL> get c:\report3.sql
  1  set echo off
  2  set verify off
  3  select product_id,name,price
  4  from products
  5* where product_id = &1;
  6

SQL> start c:\report3.sql 3

PRODUCT_ID NAME                                PRICE
---------- ------------------------------ ----------
         3 Supernova                           25.99

SQL> edit c:\report3.sql

SQL> get c:\report3.sql
  1  set echo off
  2  set verify off
  3  select product_id,name,price
  4  from products
  5* where product_id = &1 and price > &2;
  6

--由于有&1和&2，2个临时变量，这里传送了2个参数：3和10.20
SQL> start c:\report3.sql 3 10.20

PRODUCT_ID NAME                                PRICE
---------- ------------------------------ ----------
         3 Supernova                           25.99

SQL>

添加页眉、页脚

SQL> edit c:\report4.sql

SQL> get c:\report4.sql
  1  ttitle left 'Run date:' _DATE center 'Run by the ' SQL.USER ' user' right 'Page: ' FORMAT 999 SQL.PNO skip 2
  2  btitle center 'Thanks for running the report' right 'Page: ' format 999 SQL.PNO
  3  set echo off
  4  set verify off
  5  set pagesize 30
  6  set linesize 70
  7  clear columns
  8  column product_id heading id format 99
  9  column name heading 'Product name' format a20 word_wrapped
 10  column description heading Description format a30 word_wrapped
 11  column price heading Price format $99.99
 12  select product_id,name,description,price
 13  from products;
 14  clear columns
 15  ttitle off
 16* btitle off
 17

SQL> start c:\report4.sql

Run date:20-9月 -13      Run by the STORE user              Page:    1

 id Product name         Description                      Price
--- -------------------- ------------------------------ -------
  1 Modern Science       A description of modern           $.80
                         science

  2 Chemistry            Introduction to Chemistry       $30.00
  3 Supernova            A star explodes                 $25.99
  4 Tank War             Action movie about a future     $13.95
                         war

  5 Z Files              Series on mysterious            $49.99
                         activities

  6 2412: The Return     Aliens return                   $14.95
  7 Space Force 9        Adventures of heroes            $13.49
  8 From Another Planet  Alien from another planet       $12.99
                         lands on Earth

  9 Classical Music      The best classical music        $10.99
 10 Pop 3                The best popular music          $15.99
 11 Creative Yell        Debut album                     $14.99
 12 My Front Line        Their greatest hits             $13.49




                     Thanks for running the report          Page:    1

已选择12行。

计算小计

break on ：根据列值的范围，分隔输出结果

compute sum of ：让SQL*Plus计算一列的值

SQL> edit c:\report5.sql

SQL> get c:\report5.sql
  1  break on product_type_id
  2  compute sum of price on product_type_id
  3  set echo off
  4  set verify off
  5  set pagesize 50
  6  set linesize 70
  7  clear columns
  8  column price heading Price format $999.99
  9  select product_type_id,name,price
 10  from products
 11  order by product_type_id;
 12* clear columns
 13
SQL> start c:\report5.sql

PRODUCT_TYPE_ID NAME                              Price
--------------- ------------------------------ --------
              1 Modern Science                     $.80
                Chemistry                        $30.00
***************                                --------
sum                                              $30.80
              2 Z Files                          $49.99
                Tank War                         $13.95
                Supernova                        $25.99
                2412: The Return                 $14.95
***************                                --------
sum                                             $104.88
              3 Space Force 9                    $13.49
                From Another Planet              $12.99
***************                                --------
sum                                              $26.48
              4 Classical Music                  $10.99
                Pop 3                            $15.99
                Creative Yell                    $14.99
***************                                --------
sum                                              $41.97
                My Front Line                    $13.49
***************                                --------
sum                                              $13.49

已选择12行。

SQL>

8、从SQL*Plus获取帮助信息

SQL> help

 HELP
 ----

 Accesses this command line help system. Enter HELP INDEX or ? INDEX
 for a list of topics.

 You can view SQL*Plus resources at
     http://www.oracle.com/technology/tech/sql_plus/
 and the Oracle Database Library at
     http://www.oracle.com/technology/documentation/

 HELP|? [topic]


SQL> help index

Enter Help [topic] for help.

 @             COPY         PAUSE                    SHUTDOWN
 @@            DEFINE       PRINT                    SPOOL
 /             DEL          PROMPT                   SQLPLUS
 ACCEPT        DESCRIBE     QUIT                     START
 APPEND        DISCONNECT   RECOVER                  STARTUP
 ARCHIVE LOG   EDIT         REMARK                   STORE
 ATTRIBUTE     EXECUTE      REPFOOTER                TIMING
 BREAK         EXIT         REPHEADER                TTITLE
 BTITLE        GET          RESERVED WORDS (SQL)     UNDEFINE
 CHANGE        HELP         RESERVED WORDS (PL/SQL)  VARIABLE
 CLEAR         HOST         RUN                      WHENEVER OSERROR
 COLUMN        INPUT        SAVE                     WHENEVER SQLERROR
 COMPUTE       LIST         SET                      XQUERY
 CONNECT       PASSWORD     SHOW


SQL> help edit

 EDIT
 ----

 Invokes an operating system text editor on the contents of the
 specified file or on the contents of the SQL buffer. The buffer
 has no command history list and does not record SQL*Plus commands.

 ED[IT] [file_name[.ext]]

SQL>

9、自动生成SQL语句

SQL> select 'drop table ' || table_name || ';'
  2  from user_tables;

'DROPTABLE'||TABLE_NAME||';'
------------------------------------------
drop table CUSTOMERS;
drop table PRODUCT_TYPES;
drop table PRODUCTS;
drop table PURCHASES;
drop table EMPLOYEES;
drop table SALARY_GRADES;
drop table PURCHASES_WITH_TIMESTAMP;
drop table PURCHASES_TIMESTAMP_WITH_TZ;
drop table PURCHASES_WITH_LOCAL_TZ;
drop table COUPONS;
drop table PROMOTIONS;
drop table ORDER_STATUS;
drop table PRODUCT_CHANGES;
drop table MORE_PRODUCTS;
drop table MORE_EMPLOYEES;
drop table DIVISIONS;
drop table JOBS;
drop table EMPLOYEES2;
drop table ALL_SALES;
drop table PRODUCT_PRICE_AUDIT;
drop table REG_EXPS;
drop table PIVOT_SALES_DATA;
drop table BINARY_TEST;

已选择23行。

SQL>

10、断开数据库连接，并退出SQL*Plus

disconnect：断开连接，SQL*Plus会自动执行commit。

connect：可以通过connect再次连接数据库。

exit：要退出SQL*Plus，可以输入exit，也会自动执行commit。

SQL> disconnect;
从 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options 断开
SQL>
SQL> connect store/store_password;
已连接。
SQL> exit
从 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options 断开

C:\Users\Administrator>