幻灯片 1

Size: px

Start display at page:

Download "幻灯片 1"

黔暴
5 years ago
Views:

2 目标完成本课后, 您应当能够 : 描述用 SQL 产生 SQL 所解决的问题的类型写一个产生 DROP TABLE 语句的脚本的脚本写一个产生 INSERT INTO 语句的脚本的脚本 D-2 Copyright Oracle Corporation, All rights reserved. 目标在本附录中, 你将学习怎样写一个产生 SQL 脚本的 SQL 脚本中国科学院西安网络中心 2005 Oracle SQL 入门 D-2

3 用 SQL 产生 SQL SQL 数据字典 SQL 脚本 SQL 可以用于生成 SQL 脚本数据字典是包含数据库信息的表和视图的集合由 Oracle 服务器创建并维护 D-3 Copyright Oracle Corporation, All rights reserved. 用 SQL 产生 SQL SQL 可以是一个用于产生另外的 SQL 语句的强大的工具在大多数情况下你要写一个脚本文件你可以利用 SQL 从 SQL 来 : 避免重复的代码从数据字典中访问信息删除或重新创建数据库对象产生包含运行时参数的动态谓词在本课中使用的例子包括从数据字典中选择信息数据字典是包含有关数据库信息的表和视图的集合, 该集合由 Oracle 服务器创建并维护 SYS 是所有数据字典表的所有者存储在数据字典中的信息包括 Oracle 服务器用户的名字授予用户的权限数据库对象的名字表约束和审计信息有 4 种类型的数据字典视图, 每一种都有一个特定的前缀用来表示其所指的用途前缀 USER_ ALL_ DBA_ V$_ 说明包含用户所拥有的对象的详细资料包含用户已经被授予访问权限的对象和用户所拥有的对象的详细资料包含有 DBA 权限的用户所能访问的任何数据库对象的详细资料有关数据库服务器性能和锁的存储信息 ; 仅 DBA 可用中国科学院西安网络中心 2005 Oracle SQL 入门 D-3

创建一个基本的脚本 SELECT 'CREATE TABLE ' table_name '_test ' 'AS SELECT * FROM ' table_name ' WHERE 1=2;' AS "Create Table Script" FROM user_tables; D-4 Copyright Oracle Corporation, 2001.

4 创建一个基本的脚本 SELECT 'CREATE TABLE ' table_name '_test ' 'AS SELECT * FROM ' table_name ' WHERE 1=2;' AS "Create Table Script" FROM user_tables; D-4 Copyright Oracle Corporation, All rights reserved. 基本的脚本幻灯片中的例子用 CREATE TABLE 语句从你所拥有的每一个表中产生一个报告每一个在报告中产生的 CREATE TABLE 语句都包括用表的名字带一个后缀 _test 创建表的语法, 只有现有的表的相应的结构原表名从数据字典视图 USER_TABLES 的 TABLE_NAME 列中获得下一步是增强该报告来使操作过程自动化注 : 你可以查询数据字典表来查看你所拥有的各种数据库对象常用的数据字典视图包括 : USER_TABLES: 显示用户自己的表的描述 USER_OBJECTS: 显示用户所拥有的所有对象 USER_TAB_PRIVS_MADE: 显示所有在用户所拥有的表上的授权 USER_COL_PRIVS_MADE: 显示所有用户所拥有的对象的列上的授权中国科学院西安网络中心 2005 Oracle SQL 入门 D-4

5 控制环境 SET ECHO OFF SET FEEDBACK OFF SET PAGESIZE 0 Set system variables to appropriate values. SPOOL dropem.sql SQL STATEMENT SPOOL OFF SET FEEDBACK ON SET PAGESIZE 24 SET ECHO ON Set system variables back to the default value. D-5 Copyright Oracle Corporation, All rights reserved. 控制环境为了执行生成的 SQL 语句, 你必须在一个 spool 文件中捕获它们然后才可以运行你还必须整理所产生的输出以确保禁止诸如表头反馈信息标题头等等这样一些元素你可以用 isql*plus 命令完成所有这些工作中国科学院西安网络中心 2005 Oracle SQL 入门 D-5

6 完成的画面 SET ECHO OFF SET FEEDBACK OFF SET PAGESIZE 0 SELECT 'DROP TABLE ' object_name ';' FROM user_objects WHERE object_type = 'TABLE' / SET FEEDBACK ON SET PAGESIZE 24 SET ECHO ON D-6 Copyright Oracle Corporation, All rights reserved. 完成的画面用 isql*plus 中的文件输出选项将幻灯片中的命令输出保存到一个名为 dropem.sql 的文件中, 该文件包含下面的数据该文件现在可以从 isql*plus 中通过查找脚本文件装载它, 然后执行它注 : 默认情况下, 在 Windows NT 中, 文件被输出到文件夹 ORACLE_HOME\ORANT\BIN 中教师注释在 isql*plus 版本中, 文件的输出可能包含 html 标记这是一个文档缺陷中国科学院西安网络中心 2005 Oracle SQL 入门 D-6

7 转储表中的内容到一个文件中 SET HEADING OFF ECHO OFF FEEDBACK OFF SET PAGESIZE 0 SELECT 'INSERT INTO departments_test VALUES (' department_id ', ''' department_name ''', ''' location_id ''');' AS "Insert Statements Script" FROM departments / SET PAGESIZE 24 SET HEADING ON ECHO ON FEEDBACK ON D-7 Copyright Oracle Corporation, All rights reserved. 转储表中的内容到一个文件中有时将一个表中的行的值以 INSERT INTO VALUES 语句的格式存入一个文本文件中是有用的在表被意外删除的情况下, 运行该脚本可以重建被删除的表在幻灯片的例子中产生对 DEPARTMENTS_TEST 表的 INSERT 语句, 在 isql*plus 中用文件输出选项将输出捕获到 data.sql 文件中脚本文件的内容如下 : INSERT INTO departments_test VALUES (10, 'Administration', 1700); INSERT INTO departments_test VALUES (20, 'Marketing', 1800); INSERT INTO departments_test VALUES (50, 'Shipping', 1500); INSERT INTO departments_test VALUES (60, 'IT', 1400);... 中国科学院西安网络中心 2005 Oracle SQL 入门 D-7

8 转储表的内容到一个文件中 '''X''' '''' 源 'X' ' 结果 '''' department_name '''' ''', ''' ''');' 'Administration' ',' '); D-8 Copyright Oracle Corporation, All rights reserved. 转储表中的内容到一个文件中 ( 续 ) 你可能已经注意到在幻灯片中有许多单引号一组 4 个单引号在最终的语句中产生一个单引号同样字符和日期值必须放在引号中在一个串中, 为了显示一个单引号, 你需要用另一个单引号做前缀例如, 在幻灯片的第五个例子中, 两边的单引号是对整个字符串的, 第二个单引号作为一个前缀用来显示第三个单引号因此, 结果是一个单引号跟着一个圆括号再跟着一个分号中国科学院西安网络中心 2005 Oracle SQL 入门 D-8

9 产生动态谓词 COLUMN my_col NEW_VALUE dyn_where_clause SELECT DECODE('&&deptno', null, DECODE ('&&hiredate', null, ' ', 'WHERE hire_date=to_date(''' '&&hiredate'',''dd-mon-yyyy'')'), DECODE ('&&hiredate', null, 'WHERE department_id = ' '&&deptno', 'WHERE department_id = ' '&&deptno' ' AND hire_date = TO_DATE(''' '&&hiredate'',''dd-mon-yyyy'')')) AS my_col FROM dual; SELECT last_name FROM employees &dyn_where_clause; D-9 Copyright Oracle Corporation, All rights reserved. 产生动态谓词幻灯片中的例子产生一个 SELECT 语句取回在一个部门中其受雇日期为一个指定日子的所有雇员的数据该脚本动态产生 WHERE 子句注 : 一旦使用了用户变量, 你需要使用 UNDEFINE 命令删除它第一个 SELECT 语句提示你输入部门号如果你没有输入部门号, 部门号由 DECODE 函数作为空值被插入 ; 然后用户被提示输入受雇日期, 如果你没有输入受雇日期, 受雇日期被 DECODE 函数处理为空值, 并且产生的动态 WHERE 子句也是空, 这会引起第二个 SELECT 语句从 EMPLOYEES 表中取回所有行注 :The NEW_V[ALUE] 变量指定一个变量来保持列值, 你可以在 TTITLE 命令中引用该变量, 用 NEW_VALUE 来显示列值或在顶标题中的日期, 你必须在一个带 SKIP PAGE 动作的 BREAK 命令中包含该列变量名不能包含井号 (#) NEW_VALUE 对于 master/detail 报告是有用的, 其中对于每一页有一个新的 master 记录教师注释为了解释这个例子, 运行脚本 demo\dyn.sql 中国科学院西安网络中心 2005 Oracle SQL 入门 D-9

10 产生动态谓词 ( 续 ) 注 : 在这儿, 受雇日期必须以 DD-MON-YYYY 格式输入在前面幻灯片中的 SELECT 语句被解释如下 : IF (<<deptno>> is not entered) THEN ELSE IF (<<hiredate>> is not entered) THEN ELSE END IF return empty string return the string WHERE hire_date = TO_DATE('<<hiredate>>', 'DD-MON-YYYY') IF (<<hiredate>> is not entered) ELSE THEN return the string WHERE department_id = <<deptno>> entered' return the string WHERE deparment_id = <<deptno>> entered AND hire_date = TO_DATE(' <<hiredate>>', 'DD-MON-YYYY') 返回的串成为变量 DYN_WHERE_CLAUSE 的值, 该值将被用在第二个 SELECT 语句中当幻灯片中的第一个例子被执行时, 用户被提示输入 DEPTNO 和 HIREDATE: 产生下面的 MY_COL 值 : 当幻灯片中的第二个例子被执行时, 产生下面的输出 : 中国科学院西安网络中心 2005 Oracle SQL 入门 D-10

11 小结在这个附录中, 您应该已经学会 : 写一个 SQL 脚本产生另一个 SQL 脚本使用脚本文件常用的数据字典捕获输出到一个文件中 D-11 Copyright Oracle Corporation, All rights reserved. 小结 SQL 可以被用于产生 SQL 脚本这些脚本可以被用于避免重复的代码删除或重新创建对象从数据字典中得到帮助和产生包含运行时参数的动态谓词 isql*plus 命令可以被用于捕获由 SQL 语句产生的报告, 并且可以整理产生的输出, 禁止象标题反馈信息等等中国科学院西安网络中心 2005 Oracle SQL 入门 D-11

练习 D 1. 写一个脚本查看你的用户表的结构并选择其中的数据在 SELECT 列表中用连接运算符 ( ) 连接 CHR(10) 在报告中产生一个换行保存脚本的输出到 my_file1.sql 中, 为了保存文件, 为输出选择文件选项并执行代码不要忘记用.sql 扩展名保存文件为了执行 my_file1.

$用 SQL 产生撤销用户权限的 SQL 语句使用数据字典视图 USER_TAB_PRIVS_MADE 和 USER_COL_PRIVS_MADE a. 执行脚本 \Lab\privs.sql 授予 to grant privileges to the user SYSTEM. b.$

13 练习 D 1. 写一个脚本查看你的用户表的结构并选择其中的数据在 SELECT 列表中用连接运算符 ( ) 连接 CHR(10) 在报告中产生一个换行保存脚本的输出到 my_file1.sql 中, 为了保存文件, 为输出选择文件选项并执行代码不要忘记用.sql 扩展名保存文件为了执行 my_file1.sql, 浏览该脚本的位置装载该脚本, 并且执行该脚本 SET PAGESIZE 0 SELECT 'DESC ' table_name CHR(10) 'SELECT * FROM ' table_name ';' FROM user_tables / SET PAGESIZE 24 SET LINESIZE 用 SQL 产生撤销用户权限的 SQL 语句使用数据字典视图 USER_TAB_PRIVS_MADE 和 USER_COL_PRIVS_MADE a. 执行脚本 \Lab\privs.sql 授予 to grant privileges to the user SYSTEM. b. 查询数据字典视图检查权限在例子输出中显示, 注意在 GRANTOR 列中的数据可能非常依赖于 GRANTOR 是谁已经被截断的最后一列是 GRANTABLE 列 COLUMN grantee FORMAT A10 COLUMN table_name FORMAT A10 COLUMN column_name FORMAT A10 COLUMN grantor FORMAT A10 COLUMN privilege FORMAT A10 SELECT * 中国科学院西安网络中心 2005 Oracle SQL 入门 D-13

FROM user_tab_privs_made WHERE grantee = 'SYSTEM'; SELECT * FROM user_col_privs_made WHERE grantee = 'SYSTEM'; c. 产生一个脚本撤销权限保存脚本的输出到 my_file2.sql 文件中, 为了保存文件, 对输出选择文件选项, 并且执行代码不要忘记用.

14 FROM user_tab_privs_made WHERE grantee = 'SYSTEM'; SELECT * FROM user_col_privs_made WHERE grantee = 'SYSTEM'; c. 产生一个脚本撤销权限保存脚本的输出到 my_file2.sql 文件中, 为了保存文件, 对输出选择文件选项, 并且执行代码不要忘记用.sql 扩展名保存文件为了执行 my_file2.sql, 浏览该脚本的位置装载该脚本, 并且执行该脚本 SET VERIFY OFF SET PAGESIZE 0 SELECT 'REVOKE ' privilege ' ON ' table_name ' FROM system;' FROM user_tab_privs_made WHERE grantee = 'SYSTEM' / SELECT DISTINCT 'REVOKE ' privilege ' ON ' table_name ' FROM system;' FROM user_col_privs_made WHERE grantee = 'SYSTEM' / SET VERIFY ON SET PAGESIZE 24 中国科学院西安网络中心 2005 Oracle SQL 入门 D-14

教学目标描述主要数据库对象创建表描述列定义时可用的数据类型改变表的定义删除改名和截断表描述每个 DML 语句插入行到表中更新表中的行从表中删除行描述约束创建

教学目标描述主要数据库对象创建表描述列定义时可用的数据类型改变表的定义删除改名和截断表描述每个 DML 语句插入行到表中更新表中的行从表中删除行描述约束创建 2 第二讲创建和管理表与简单记录操作 Copyright SWFC-CISC, 2013. All rights reserved. 教学目标描述主要数据库对象创建表描述列定义时可用的数据类型改变表的定义删除改名和截断表描述每个 DML 语句插入行到表中更新表