SQL/PLUS学习笔记

来源：互联网发布：数据挖掘岗很难进吗编辑：程序博客网时间：2024/05/22 08:12

说明：在学习小布老师的视频课程的实践中，整理的笔记，备忘，自己忘记的时候看一下！

1. SQL Buffer

       (1).只存储一条SQL命令；

       (2).save命令将当前的SQL Buffer中的SQL命令输出到磁盘上的一个指定文件名的文件里面；

               eg: SQL>save c:/hr 回车后再C盘生成文件hr.sql文件

       (3).list命令：显示当前SQL缓冲区中的SQL命令；

       (4).run和/(slash-斜线号)，运行SQL Buffer中的SQL命令，前者先显示要执行的SQL命令再

       显示运行结果，而后者直接显示运行结果；

       (5).要想在sqlplus的终端中显示打印结果，必须设置参数：serveroutput

       SQL> show serveroutput
               serveroutput OFF
      可以看出serveroutput是关闭的，通过以下命令设置你为打开状态：

      SQL> set serveroutput on
       SQL> show serveroutput
       serveroutput ON SIZE UNLIMITED FORMAT WORD_WRAPPED

       eg:
           SQL> declare
              2     x varchar2(15) :='Hello World !';
              3 begin
              4     dbms_output.put_line(x);
              5 exception
              6 when others then
              7     dbms_output.put_line('Error!');
              8 end;
              9 .
           SQL> list
              1 declare
              2     x varchar2(15) :='Hello World !';
              3 begin
              4     dbms_output.put_line(x);
              5 exception
              6 when others then
              7     dbms_output.put_line('Error!');
              8* end;

              上面的.表示PL/SQL块输入结束，但是并不执行；

              输入list命令后会将SQL Buffer中的该语句块显示出来；

       如上面所示最后一行用*标记，也是当前行。

       再次输入/(slash)回车后执行；会将Hello World！打印出来：
       SQL> /
       Hello World !

       PL/SQL 过程已成功完成。



2. 运行操作系统命令

       (1).SQL> host + 操作系统命令；

       (2).host的等价命令：Windows下是 $; Linux下是！;

3. pause命令

       语法：set pau[se] {on | off | text}

       SQL> show pau
       PAUSE 为 OFF --暂停功能被关闭

       SQL> show pagesize
       pagesize 14 --分页显示，每页的记录数为14条

       eg:

       SQL> set pau on
       SQL> show pau
       PAUSE 为 ON 并设为 " "
       SQL> select * from t_1; --回车后会暂停，再回车显示第一页14条记录；再回车下一页；


                ID NAME
       ---------- --------------------
                 1 Taowei
                 2 Taowei
                 3 Taowei
                 4 Taowei
                 5 Taowei
                 6 Taowei
                 7 Taowei
                 8 Taowei
                 9 Taowei
                10 Taowei
                13 kk

       eg:

       SQL> set pau '$$$$$$$$$$$$$$$$$$$$$$$$' --以'$$$$$$$$$$$$$$$$$$$$$$$$'分隔每页
       SQL> select * from t_1 order by id;
       $$$$$$$$$$$$$$$$$$$$$$$$

                ID NAME
       ---------- --------------------
                 1 Taowei
                 2 Taowei
                 3 Taowei
                 4 Taowei
                 5 Taowei
                 6 Taowei
                 7 Taowei
                 8 Taowei
                 9 Taowei
                10 Taowei
                11 boobooke
       $$$$$$$$$$$$$$$$$$$$$$$$

4. SQL的自动提交功能

       SQL的自动提交功能默认情况下是关闭的，这样更加合理，以免误操作时可以rollback回滚；

       通过设置autocommit参数可以打开SQL的自动提交功能：

       eg:

       SQL> show auto
       autocommit OFF
       SQL> set auto on
       SQL> insert into t_1 values(51,'Heihei');

       已创建 1 行。

       提交完成。

       否则当前用户插入的记录在未commit的情况下指示保存在内存里面，也只有当前用户能select

       其他用户是看不到的；除非commit了。

5. 编辑脚本

       敲入define命令可以看到SQL/PLUS可以设置的环境变量

       SQL> define
       DEFINE _DATE           = "14-8月 -11" (CHAR)
       DEFINE _CONNECT_IDENTIFIER = "orcl" (CHAR)
       DEFINE _USER           = "EMC" (CHAR)
       DEFINE _PRIVILEGE      = "" (CHAR)
       DEFINE _SQLPLUS_RELEASE = "1002000100" (CHAR)
       DEFINE _EDITOR         = "Notepad" (CHAR)
       DEFINE _O_VERSION      = "Oracle Database 10g Enterprise Edition Release 10.2.0.
       1.0 - Production
       With the Partitioning, OLAP and Data Mining options" (CHAR)
       DEFINE _O_RELEASE      = "1002000100" (CHAR)

       其中_editor可以设置默认的脚本编辑器，当设置好以后可以输入命令:SQL> edit file.sql

       来编辑脚本文件，在未指定路径的情况下文件保存在操作系统的当前路径下。也可以通过切换

       到操作系统命令环境下，编辑好脚本，然后用@，@@或start + 脚本的路径来执行。

       eg:

       SQL> edit file --file.sql文件默认生成在当前目录下，或者先切换到操作系统，编辑好

       脚本后执行，扩展名缺省为sql；

       SQL> $
       Microsoft Windows XP [版本 5.1.2600]
       (C) 版权所有 1985-2001 Microsoft Corp.

       C:\Documents and Settings\hawk>dir
       驱动器 C 中的卷是 C
       卷的序列号是 4D28-FE45

       C:\Documents and Settings\hawk 的目录

       2011-01-09 00:50    <DIR>          .
       2011-01-09 00:50    <DIR>          ..
       2011-01-09 00:31    <DIR>          「开始」菜单
       2011-01-09 01:18    <DIR>          My Documents
       2011-01-09 01:18    <DIR>          Favorites
       2011-01-09 00:31    <DIR>          桌面
       2011-01-09 15:17    <DIR>          .myeclipse
       2011-01-09 15:17    <DIR>          .m2
       2011-06-20 14:52               128 .myeclipse.properties
       2011-08-14 10:45                 8 MagicEmotions.idx
       2011-01-18 09:58                25 .pulse2.locator
       2011-01-20 20:58    <DIR>          EurekaLog
       2011-08-11 10:23           225,685 sqlnet.log
       2011-05-03 09:29    <DIR>          workspace
       2011-08-13 13:30                89 afiedt.buf
       2011-05-29 23:59    <DIR>          Tracing
       2011-08-14 23:46                18 file.sql --在里面输入select * from t_1;后保存
                       6 个文件        225,953 字节
                      11 个目录 8,195,407,872 可用字节

       C:\Documents and Settings\hawk>exit --输入exit命令返回到sqlplus

       SQL> @file --运行脚本，下面是执行结果；另外输入@@file或者start file是一样的

                ID NAME
       ---------- --------------------
                 1 Taowei
                 2 Taowei
                 3 Taowei
                 4 Taowei
                 5 Taowei
                 6 Taowei
                 7 Taowei
                 8 Taowei
                 9 Taowei
                10 Taowei
                13 kk


       编辑缓冲区中的当前行(current line)的命令:

       (1).change (enable you to change the current line)

               首先用list命令变换当前行(或者直接输入行号，回车也可改变当前行):

               SQL> l --显示缓冲区中内容
                  1 select * from t1
                  2 where id>1
                  3* and id<2 --*号为默认的当前行
               SQL> l 2 --list 2将当前行变为第二行，但是这并不影响list的当前行(注意)
                  2* where id>1

               SQL> l
                  1 select *
                  2 from t1
                  3 where
                  4 id<90 --现在想把第四行的90改成85
                  5* and id>80
               SQL> l 4 --先改变当前行到第四行
                  4* id<90
               SQL> c/90/85 --change命令的用法
                  4* id<100
               SQL> l --用list命令显示发现90已经变成了85，这是可以用slash或run运行该新的SQL语句
                  1 select *
                  2 from t1
                  3 where
                  4 id<85
                  5* and id>80
               SQL> run
                  1 select *
                  2 from t1
                  3 where
                  4 id<85
                  5* and id>80

                        ID NAME
               ---------- --------------------
                        81 Testing
                        82 Testing
                        83 Testing
                        84 Testing

       (2).append(追加)

               首先用list命令变换当前行(或者直接输入行号，回车也可改变当前行):

               SQL> l
                  1 select id,name
                  2 from t1
                  3 where
                  4 id<85 --在后面添加and name='Oracle'
                  5* and id>80
               SQL> 4 --改变当前行
                  4* id<85
               SQL> a and name='Oracle'    --注意这里a和and之间有两个空格，如果只有一个的话
                  4* id<85 and name='Oracle' --85和and会挨在一起
               SQL> l --list显示文本已追加
                  1 select id,name
                  2 from t1
                  3 where
                  4 id<85 and name='Oracle'
                  5* and id>80
               SQL> /

                        ID NAME
               ---------- --------------------
                        83 Oracle

       (3).input命令

               在当前行后面插入一个新行(to insert a new line after the current line)

               首先用list命令变换当前行(或者直接输入行号，回车也可改变当前行)

       定位到你要插入新行，然后i + 要插入的文本:

            eg:
                   (这个比较特殊)在第一行前面加一段注释/* this is a testing demo! */
            SQL> 0 /* this is a testing demo! */ --就在第一行前面加上了该注释

               SQL> l
                  1 select id,name
                  2 from t1
                  3 where
                  4 id<85 --在第四行的下面插入一个新行
                  5* and id>80
               SQL> 4 --先变换当前行为第四行
                  4* id<85
               SQL> i --this is a demo! --input + text(要插入的文本)
               SQL> l
                  1 select id,name
                  2 from t1
                  3 where
                  4 id<85
                  5 --this is a demo!
                  6* and id>80
               SQL> /

                        ID NAME
               ---------- --------------------
                        81 Testing
                        82 Testing
                        83 Oracle
                        84 Testing

               SQL> 5 --将当前行改为5，在其后加一行，添加新的查询条件and name='Oracle'
                  5* --this is a demo!
               SQL> i and name='Oracle' --插入
               SQL> l
                  1 select id,name
                  2 from t1
                  3 where
                  4 id<85
                  5 --this is a demo!
                  6 and name='Oracle'
                  7* and id>80
               SQL> /

                        ID NAME
               ---------- --------------------
                        83 Oracle

       (4).del删除行命令

               a.del n --删除第n行
               b.del n m --删除从n到m行
               c.del n * --删除从第n行到当前行
               d.del n last --删除第n行到最后一行

6. (1).ECHO

       如果想在终端将每执行的一条SQL语句都打印出来需要设置echo参数

       SQL> show echo --显示echo的状态
       echo OFF --此时echo是OFF状态
       SQL> set echo on --设置其为开状态
       SQL> show echo
       echo ON --已经打开

       此时运行脚本的话，脚本中的每条SQL语句或PL/SQL块将会显示在终端，如下运行test脚本：
       SQL> @test --其下面均为终端显示的内容
       SQL> select *from t1 where rownum<2;

                ID NAME
       ---------- --------------------
                 1 Testing

       SQL>
       SQL> truncate table t1;

       Table truncated.

       SQL>
       SQL> begin
          2
          3 for i in 1 .. 1000 loop
          4
          5          insert into t1 values(i,'Testing');
          6 end loop;
          7 commit;
          8
          9 end;
       10 /

       PL/SQL procedure successfully completed.

       SQL>
       SQL> select count(*)from t1;

          COUNT(*)
       ----------
              1000




   (2).使用SPOOL保存查询的结果集

               SQL> spool outputfile --默认在当前路径下生成outputfile.lst文件
               SQL> select * from t1 where rownum<3;

                        ID NAME
               ---------- --------------------
                         1 Testing
                         2 Testing

               SQL> spool off   --终止，此时这些内容全部被写入文件outputfile中

               [oracle@localhost ~]$ cat outputfile.lst --查看文件内容如下：
               SQL> select * from t1 where rownum<3;

                        ID NAME
               ---------- --------------------
                         1 Testing
                         2 Testing

               SQL> spool off

               这个功能可以帮助生成一些动态的批量处理的脚本，比方说删除用户emcd下的

       满足某些条件的表：

               SQL> show user
               USER is "EMCD"
               SQL> spool droptable.sql
               SQL> select 'drop table'||objec_name from user_objects where object_type='TABLE';
               select 'drop table'||objec_name from user_objects where object_type='TABLE'
                                     *
               ERROR at line 1:
               ORA-00904: "OBJEC_NAME": invalid identifier


               SQL> l
                  1* select 'drop table'||objec_name from user_objects where object_type='TABLE'
               SQL> c/objec_/object_
                  1* select 'drop table'||object_name from user_objects where object_type='TABLE'
               SQL> /

               'DROPTABLE'||OBJECT_NAME
               --------------------------------------------------------------------------------
               drop tableT1
               drop tableBIN$qokoVJ6g4HHgQAB/AQAzMg==$0
               drop tableTOAD_PLAN_TABLE

               SQL> l
                  1* select 'drop table'||object_name from user_objects where object_type='TABLE'
               SQL> c/'drop table'/'drop table '
                  1* select 'drop table '||object_name from user_objects where object_type='TABLE'
               SQL> /

               'DROPTABLE'||OBJECT_NAME
               --------------------------------------------------------------------------------
               drop table T1
               drop table BIN$qokoVJ6g4HHgQAB/AQAzMg==$0
               drop table TOAD_PLAN_TABLE

               SQL> spool off --结束输入
               SQL> !

               [oracle@localhost ~]$ cat droptable.sql --查看输出内容，如下所示：

               SQL> select 'drop table'||objec_name from user_objects where object_type='TABLE';
               select 'drop table'||objec_name from user_objects where object_type='TABLE'
                                     *
               ERROR at line 1:
               ORA-00904: "OBJEC_NAME": invalid identifier


               SQL> l
                  1* select 'drop table'||objec_name from user_objects where object_type='TABLE'
               SQL> c/objec_/object_
                  1* select 'drop table'||object_name from user_objects where object_type='TABLE'
               SQL> /

               'DROPTABLE'||OBJECT_NAME
               --------------------------------------------------------------------------------
               drop tableT1
               drop tableBIN$qokoVJ6g4HHgQAB/AQAzMg==$0
               drop tableTOAD_PLAN_TABLE

               SQL> l
                  1* select 'drop table'||object_name from user_objects where object_type='TABLE'
               SQL> c/'drop table'/'drop table '
                  1* select 'drop table '||object_name from user_objects where object_type='TABLE'
               SQL> /

               'DROPTABLE'||OBJECT_NAME
               --------------------------------------------------------------------------------
               drop table T1
               drop table BIN$qokoVJ6g4HHgQAB/AQAzMg==$0
               drop table TOAD_PLAN_TABLE

               SQL> spool off

               这样动态删除某些表的SQL语句就生成了。

   (3).启动sqlplus时就运行一个脚本：

              [oracle@localhost ~]$ sqlplus emcd@oracle_120 @test