上次我们介绍了:SQL/PLUS学习笔记之编辑缓冲区中的当前行命令,本文我们介绍一下SQL/PLUS学习笔记之ECHO和SPOOL的使用,接下来就让我们一起来了解一下这部分内容。
ECHO参数的设置:
SQL> show echo --显示echo的状态
echo OFF --此时echo是OFF状态
SQL> set echo on --设置其为开状态
SQL> show echo
echo ON --已经打开
此时运行脚本的话,脚本中的每条SQL语句或PL/SQL块将会显示在终端,如下运行test脚本:
class="dp-xml">
- 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
使用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语句就生成了。
关于SQL/PLUS学习笔记之ECHO和SPOOL的使用的知识就介绍到这里了,希望本次的介绍能够对您有所收获!