sql——使用SQL生成SQL语句

一、几个SQL*PLUS命令

1、set echo on/off

        set echo on在执行的时候，看到你的SQL语句。set echo off不想在执行的时候看到SQL语句，只想看到输出的结果。

2、set feedback on/off

        feedback就是查询所输入的行数，例如：如果运行的select语句返回30行数据，则feedback运行显示：30 row select。

        set feedback on会显示对行数的计数，set feedback off则在结果输出时不会对行进行计数。

3、set heading on/off

        heading指的是你的select语句的输出结果的头部，例如：last_name、customer_id。set heading on是默认的，off是指在输出的时候不输出列标头。

4、spool filename/off

        spool可以将你的查询结果直接的输入到一个文件中，想要打开spool文件，需要输入：spool filename。如果想要关掉spool文件，应该输入：spool off。

5、start filename

        有一种运行 SQL 语句的方法是创建 SQL 执行文件，在 SQL*PLUS 中 运行 SQL 文件的命令是 START FILENAME。

6、ed filename

ed在oracle中是用来打开文件的命令，当使用ed打开文件，进入全屏的编辑环境，可以使用它来修改你的spool文件。创建脚本的时候，会频繁的使用ed命令。

二、几个SQL*PLUS命令的使用范畴

1、计算所有表的行数

--对一个表中的所有行进行统计
select count(*) from tab1;

--想对工程中所有属于你的表的行数进行计算
select * from cat;

--对select输出结果的每一行进行count(*)计算
set echo off
set feedback off
set heading off
spool cnt.sql
select'select count(*) from '|| table_name ||';'
from cat

spool off
ed cnt.sql

set echo on
set heading on
start cnt.sql

2、为多个用户赋予系统权限

        通过下列方法生成grant语句为用户赋予系统权限和规则。

set echo off
set heading off
set feedback off
spool grant.sql
select'grant connect,resource to'||username||';'
from sys.dba_user
where username not in('sys','system','demo')

spool off
start grants.sql

3、将表的权限赋予给其他用户

        将一个表的权限赋予其它的用户就像从表中选择一行一样地简单，如果你有多个表需要对它们赋予访问规则和用户时，你可以使用 SQL 来为你生成一个脚本。

--为单个表赋予权限
grant select on history to brandon;

--会创建一组 GRANT 语句来为10个表赋予权限
set echo off
set feedback off
set heading off
spool grants.sql
select'grant select on'||table_name||'to brandon';'
from cat

--使用 SPOOL 命令来关闭输出文件,并假定该文件已经被编辑过了
spool off
set echo on
set feedback on
start grants.sql
--grant select on acct_pay to brandon;共有10组语句生成并执行

4、在载入数据时解除对数的约束

        当表已经被删减过或有损坏了，更有可能的是你的表存有如外部关键字之类的引用完整性约束，数据库不会允许你向表中插入不在其它表中存在相关关系的数据。在最初装入数据的视乎不得不解除对表的额约束，当载入成功后恢复约束。

set echo off
set feedback off
set heading off
spool disble.sql
select'alter table'||table_name||'disable constraint'||constraint_name||';'
from sys.dba_constraints
where owner='RYAN'

spool off
set echo off
set feedback on
start disable.sql

5、一次创建多个同义字

        不管是公共的还是私有的，只有 DBA 可以创建共同的同义字，但是任何用户都可以创建私有的同义字。

--为RYAN所拥有的表创建公共同义字
set echo off
set feedback off
set heading off
spool pub_syn.sql
select'create public synonym'||table_name||'for'||owner||'.'||table_name||';'
from sys.dba_tables
where owner='RYAN'

spool off
ed pub_sys.sql
set echo on
set feedback on
start pub_sys.sql

         如果BRANDON 想访问所有为 RYAN 所拥有的表来创建私有同义字时。

connect brandon
set echo off
set feedback off
set heading off
spool priv_syn.sql
select'create synonym'||table_name||'for'||owner||'.'||table_name||';'
from all_tables

spool off
set echo off
set feedback on
start priv_syn.sql

6、为表创建视图

set echo off
set feedback off
set heading off
spool views.sql
select'create view'||table_name||'_view as select *from'||table_name||';'
from cat
spool off
set echo off
set feedback on
start views.sql

7、在一个计划中清除其所有的表的内容

        清除表是在开发环境中出现的工作，为了有效地开发测试数据载入和 SQL 的性能，数据是经常需要重新载入的，这一过程可以确定和清除 BUG 经常测试后的应用程序才会转入生产环境中。

set echo off
set feedback off
set heading off
spool trunc.sql
select'truncate table'||table_name||';'
from all_tables
where owner='RYAN'

spool off
set feedback on
start trunc.sql

8、使用 SQL 来生成 SHELL 脚本

        使用SQL生成其他形式的脚本，例如说shell脚本。oracle RDBMS服务咋子运行在Unix环境下，Unix需要对文件进行更为有效的管理，通过创建脚本，可以很容易的管理数据库文件。

        删除在一个数据库中的表空间时，如果使用 SQL 来删除表空间，与表空间相关的事实的的数据文件都必须在操作系统中才能删除。

--生成SQL脚本删除表空间
set echo off
set feedback off
set heading off
spool drop_ts.sql
select'drop tablespace'||tablespace_name||'including contents;'
from sys.dba_tablespaces

--生成一个脚本文件以在表空间删除后在操作系统中删除数据库文件
spool off
spool rm_files.sh
select'RM-f'||file_name
from sys.dba_data_files