Oracle Database 10g：SQL 基础 II 电子演示 D17111CN20 版本 2.0 2006 年 11 月 D47507 ® 版权所有 © 2006，Oracle。保留所有权利。 免责声明 本文档包含专有权信息，并受版权法和其它知识产权法的保护。您可以复制和打印本文档供在 Oracle 培训课程中单独使用。不得以任何方式修改或变更本文档。除了在依照版权法中制定的“合理使用” 范围内使用本文档外，在未经 Oracle 明确授权的情况下，您不得以全部或部分的形式使用、共享、 下载、上载、复制、打印、显示、展示、再版、发布、许可、张贴、传播或散布本文档。 本文档中包含的信息如有更改，恕不另行通知。如果您在本文档中发现任何问题，请书面通知：Oracle University, 500 Oracle Parkway, Redwood Shores, California 94065 USA.。不能保证本文档中没有 错误。 有限权利声明 如果将本文档交付给美国政府或代表美国政府使用本文档的任何人，请适用下列通知中的规定： 美国政府权利 美国政府使用、修改、再版、发行、展示、显示或公开这些培训资料的权利必须遵守 Oracle 许可协议 和（或）美国政府合同法中适用的条款。 商标声明 Oracle、JD Edwards、PeopleSoft 和 Siebel 是 Oracle 公司和（或）其分公司的注册商标。其它名称 可能是其各自拥有者的商标。 作者 Chaitanya Koratamaddi Priya Vennapusa 技术撰稿人和审稿人 Claire Bennett Brian Boxx Zarko Cesljas Laurent Dereac Nancy Greenberg Angelika Krupp Malika Marghadi Priya Nathan Bryan Roberts Lata Shivaprasad Naoko Susuki 编辑 Nita Pavitran Atanu Raychaudhuri 制图员 Sanjeev Sharma 出版商 Sujatha Nagendra Sheryl Domingue 版权所有 © 2006，Oracle。保留所有权利。 简介 版权所有 © 2006，Oracle。保留所有权利。 课程目标 学完本课后，应能完成下列工作： • 列出课程目标 • 描述课程中使用的示例表 版权所有 © 2006，Oracle。保留所有权利。 课程目标 学完本课程后，应能完成以下工作： • 使用高级 SQL 数据检索技术从数据库表中检索数据 • 在模拟实际情况的练习中应用高级技术 版权所有 © 2006，Oracle。保留所有权利。 课程概览 在本课程中，您将使用高级 SQL 数据检索技术，如： • 日期时间函数 • ROLLUP、CUBE 运算符和 GROUPING SETS • 分层查询 • 相关子查询 • 多表插入 • Merge 操作 • 外部表 • 正则表达式 版权所有 © 2006，Oracle。保留所有权利。 EMPLOYEES DEPARTMENTS COUNTRIESREGIONS LOCATIONS 课程应用 版权所有 © 2006，Oracle。保留所有权利。 小结 在本课中，您应该已经掌握了下列内容： • 课程目标 • 课程中使用的示例表 版权所有 © 2006，Oracle。保留所有权利。 控制用户访问 版权所有 © 2006，Oracle。保留所有权利。 课程目标 学完本课后，应能完成以下工作： • 区分系统权限和对象权限 • 授予表的权限 • 在数据字典中查看权限 • 授予角色 • 区分权限和角色 版权所有 © 2006，Oracle。保留所有权利。 控制用户访问 数据库管理员 用户 用户名和口令 权限 版权所有 © 2006，Oracle。保留所有权利。 权限 • 数据库安全性： – 系统安全性 – 数据安全性 • 系统权限：获得对数据库的访问权限 • 对象权限：处理数据库对象的内容 • 方案：对象的集合，例如表、视图和序列的集合 版权所有 © 2006，Oracle。保留所有权利。 系统权限 • 可用的系统权限超过 100 个 • 数据库管理员对诸如以下任务具有高级别的系统权限： – 创建新用户 – 删除用户 – 删除表 – 备份表 版权所有 © 2006，Oracle。保留所有权利。 创建用户 DBA 可以使用 CREATE USER 语句创建用户 CREATE USER USER1 IDENTIFIED BY USER1; User created. CREATE USER user IDENTIFIED BY password; 版权所有 © 2006，Oracle。保留所有权利。 用户系统权限 • 创建了用户之后，DBA 可以为该用户授予特定的系统 权限 • 例如，应用程序开发人员可以具有以下系统权限： – CREATE SESSION – CREATE TABLE – CREATE SEQUENCE – CREATE VIEW – CREATE PROCEDURE GRANT privilege [, privilege...] TO user [, user| role, PUBLIC...]; 版权所有 © 2006，Oracle。保留所有权利。 授予系统权限 DBA 可以为用户授予特定的系统权限 GRANT create session, create table, create sequence, create view TO scott; Grant succeeded. 版权所有 © 2006，Oracle。保留所有权利。 什么是角色 不使用角色分配权限 使用角色分配权限 权限 用户 经理 版权所有 © 2006，Oracle。保留所有权利。 创建角色和为角色授予权限 • 创建角色： • 为角色授予权限： • 将角色授予用户： CREATE ROLE manager; Role created. GRANT create table, create view TO manager; Grant succeeded. GRANT manager TO BELL, KOCHHAR; Grant succeeded. 版权所有 © 2006，Oracle。保留所有权利。 更改口令 • DBA 会创建用户帐户，并初始设置口令 • 可以通过使用 ALTER USER 语句来更改口令 ALTER USER HR IDENTIFIED BY employ; User altered. 版权所有 © 2006，Oracle。保留所有权利。 对象权限 对象权限 表 视图 序列 过程 ALTER √√ DELETE √√ EXECUTE √ INDEX √ INSERT √√ REFERENCES √ SELECT √√ √ UPDATE √√ 版权所有 © 2006，Oracle。保留所有权利。 对象权限 • 对象权限因对象而异 • 对于对象，所有者具有全部权限 • 所有者可以将对其对象的特定权限授予给其他用户 GRANT object_priv [(columns)] ON object TO {user|role|PUBLIC} [WITH GRANT OPTION]; 版权所有 © 2006，Oracle。保留所有权利。 授予对象权限 • 授予对 EMPLOYEES 表的查询权限： • 为用户和角色授予更新特定列的权限： GRANT select ON employees TO sue, rich; Grant succeeded. GRANT update (department_name, location_id) ON departments TO scott, manager; Grant succeeded. 版权所有 © 2006，Oracle。保留所有权利。 传递权限 • 授予用户权限以传递权限： • 允许系统中的所有用户查询 Alice 的 DEPARTMENTS 表中的数据： GRANT select, insert ON departments TO scott WITH GRANT OPTION; Grant succeeded. GRANT select ON alice.departments TO PUBLIC; Grant succeeded. 版权所有 © 2006，Oracle。保留所有权利。 确认授予的权限 数据字典视图 说明 ROLE_SYS_PRIVS 授予给角色的系统权限 ROLE_TAB_PRIVS 授予给角色的表权限 USER_ROLE_PRIVS 用户可以访问的角色 USER_TAB_PRIVS_MADE 授予的对用户对象的对象权限 USER_TAB_PRIVS_RECD 授予给用户的对象权限 USER_COL_PRIVS_MADE 授予的对用户对象的列的对象权限 USER_COL_PRIVS_RECD 授予给用户的对特定列的对象权限 USER_SYS_PRIVS 授予给用户的系统权限 版权所有 © 2006，Oracle。保留所有权利。 撤消对象权限 • 使用 REVOKE 语句可以撤消授予给其他用户的权限。 • 使用 WITH GRANT OPTION 子句授予给其他用户的权限 也会被撤消。 REVOKE {privilege [, privilege...]|ALL} ON object FROM {user[, user...]|role|PUBLIC} [CASCADE CONSTRAINTS]; 版权所有 © 2006，Oracle。保留所有权利。 撤消对象权限 用户 Alice 撤消了授予给用户 Scott 的对 DEPARTMENTS 表 的 SELECT 和 INSERT 权限。 REVOKE select, insert ON departments FROM scott; Revoke succeeded. 版权所有 © 2006，Oracle。保留所有权利。 小结 在本课中，您应该已经学会了使用语句控制对数据库和数据 库对象的访问权限。 Statement Action CREATE USER 创建用户（通常由 DBA 执行） GRANT 授予其他用户访问对象的权限 CREATE ROLE 创建权限的集合（通常 由 DBA 执行） ALTER USER 更改用户的口令 REVOKE 删除用户对某个对象的 权限 版权所有 © 2006，Oracle。保留所有权利。 练习 1：概览 本练习包含以下主题： • 为其他用户授予对您的表的权限 • 使用授予给您的权限来修改其他用户的表 • 创建同义词 • 查询与权限相关的数据字典视图 版权所有 © 2006，Oracle。保留所有权利。 管理方案对象 版权所有 © 2006，Oracle。保留所有权利。 课程目标 学完本课后，应能完成以下工作： • 添加约束条件 • 创建索引 • 使用 CREATE TABLE 语句创建索引 • 创建基于函数的索引 • 删除列并将列设置为 UNUSED • 执行 FLASHBACK 操作 • 创建和使用外部表 版权所有 © 2006，Oracle。保留所有权利。 ALTER TABLE 语句 使用 ALTER TABLE 语句可以执行以下操作： • 添加新列 • 修改现有的列 • 定义新列的默认值 • 删除列 版权所有 © 2006，Oracle。保留所有权利。 ALTER TABLE 语句 使用 ALTER TABLE 语句可以添加、修改或删除列： ALTER TABLE table ADD (column datatype [DEFAULT expr] [, column datatype]...); ALTER TABLE table MODIFY (column datatype [DEFAULT expr] [, column datatype]...); ALTER TABLE table DROP (column); 版权所有 © 2006，Oracle。保留所有权利。 添加列 • 可以使用 ADD 子句添加列： • 新添加的列将显示在最后： … ALTER TABLE dept80 ADD (job_id VARCHAR2(9)); Table altered. 版权所有 © 2006，Oracle。保留所有权利。 修改列 • 可以更改列的数据类型、大小和默认值。 • 更改默认值只会影响表的后续插入。 ALTER TABLE dept80 MODIFY (last_name VARCHAR2(30)); Table altered. 版权所有 © 2006，Oracle。保留所有权利。 删除列 使用 DROP COLUMN 子句可以从表中删除不再需要的列： ALTER TABLE dept80 DROP COLUMN job_id; Table altered. 版权所有 © 2006，Oracle。保留所有权利。 SET UNUSED 选项 • 使用 SET UNUSED 选项可以将一列或多列标记为“不 使用”。 • 使用 DROP UNUSED COLUMNS 选项可以删除已标记为 “不使用”的列。 ALTER TABLE SET UNUSED(); ALTER TABLE SET UNUSED COLUMN ; OR ALTER TABLE DROP UNUSED COLUMNS; 版权所有 © 2006，Oracle。保留所有权利。 添加约束条件语法 使用 ALTER TABLE 语句可以完成以下任务： • 添加或删除约束条件，但不修改它的结构 • 启用或禁用约束条件 • 使用 MODIFY 子句添加 NOT NULL 约束条件 ALTER TABLE ADD [CONSTRAINT ] type (); 版权所有 © 2006，Oracle。保留所有权利。 添加约束条件 向 EMP2 表添加一个 FOREIGN KEY 约束条件，指明经理 必须已经作为一名有效的雇员而存在于 EMP2 表中。 ALTER TABLE emp2 modify employee_id Primary Key; Table altered. ALTER TABLE emp2 ADD CONSTRAINT emp_mgr_fk FOREIGN KEY(manager_id) REFERENCES emp2(employee_id); Table altered. 版权所有 © 2006，Oracle。保留所有权利。 ON DELETE CASCADE 删除父关键字时也会删除子行： ALTER TABLE Emp2 ADD CONSTRAINT emp_dt_fk FOREIGN KEY (Department_id) REFERENCES departments ON DELETE CASCADE); Table altered. 版权所有 © 2006，Oracle。保留所有权利。 延迟约束条件 约束条件可以具有以下属性： • DEFERRABLE or NOT DEFERRABLE • INITIALLY DEFERRED or INITIALLY IMMEDIATE ALTER TABLE dept2 ADD CONSTRAINT dept2_id_pk PRIMARY KEY (department_id) DEFERRABLE INITIALLY DEFERRED 在创建时延迟约束条件 ALTER SESSION SET CONSTRAINTS= IMMEDIATE 为会话更改所有 约束条件 SET CONSTRAINTS dept2_id_pk IMMEDIATE 更改特定约束 条件的属性 版权所有 © 2006，Oracle。保留所有权利。 删除约束条件 • 从 EMP2 表中删除经理约束条件： • 删除 DEPT2 表上的 PRIMARY KEY 约束条件，并删除 EMP2.DEPARTMENT_ID 列上的相关 FOREIGN KEY 约 束条件： ALTER TABLE emp2 DROP CONSTRAINT emp_mgr_fk; Table altered. ALTER TABLE dept2 DROP PRIMARY KEY CASCADE; Table altered. 版权所有 © 2006，Oracle。保留所有权利。 禁用约束条件 • 执行 ALTER TABLE 语句的 DISABLE 子句可以停用 完整性约束条件。 • 应用 CASCADE 选项可以禁用相关的完整性约束条件。 ALTER TABLE emp2 DISABLE CONSTRAINT emp_dt_fk; Table altered. 版权所有 © 2006，Oracle。保留所有权利。 启用约束条件 • 使用 ENABLE 子句可以激活表定义中当前禁用的完整性 约束条件。 • 如果启用了 UNIQUE 关键字或 PRIMARY KEY 约束条件， 则系统会自动创建一个 UNIQUE 或 PRIMARY KEY 索引。 ALTER TABLE emp2 ENABLE CONSTRAINT emp_dt_fk; Table altered. 版权所有 © 2006，Oracle。保留所有权利。 级联约束条件 • CASCADE CONSTRAINTS 子句是和 DROP COLUMN 子句 一起使用的 • CASCADE CONSTRAINTS 子句将删除引用了已删除列 上定义的主键和唯一关键字的所有引用完整性约束条件 • CASCADE CONSTRAINTS 子句还会删除在已删除列上定 义的所有多列约束条件 版权所有 © 2006，Oracle。保留所有权利。 级联约束条件 示例： ALTER TABLE emp2 DROP COLUMN employee_id CASCADE CONSTRAINTS; Table altered. ALTER TABLE test1 DROP (pk, fk, col1) CASCADE CONSTRAINTS; Table altered. 版权所有 © 2006，Oracle。保留所有权利。 索引概览 索引的创建方法有以下两种： • 自动创建 – PRIMARY KEY 创建 – UNIQUE KEY 创建 • 手动创建 – CREATE INDEX 语句 – CREATE TABLE 语句 版权所有 © 2006，Oracle。保留所有权利。 CREATE INDEX 与 CREATE TABLE 语句配合使用 CREATE TABLE NEW_EMP (employee_id NUMBER(6) PRIMARY KEY USING INDEX (CREATE INDEX emp_id_idx ON NEW_EMP(employee_id)), first_name VARCHAR2(20), last_name VARCHAR2(25)); Table created. SELECT INDEX_NAME, TABLE_NAME FROM USER_INDEXES WHERE TABLE_NAME = 'NEW_EMP'; 版权所有 © 2006，Oracle。保留所有权利。 基于函数的索引 • 基于函数的索引就是基于表达式的索引。 • 索引表达式是用表列、常数、SQL 函数和用户定义的 函数构建的。 CREATE INDEX upper_dept_name_idx ON dept2(UPPER(department_name)); Index created. SELECT * FROM dept2 WHERE UPPER(department_name) = 'SALES'; 版权所有 © 2006，Oracle。保留所有权利。 删除索引 • 使用 DROP INDEX 命令可以从数据字典中删除索引： • 从数据字典中删除 UPPER_DEPT_NAME_IDX 索引： • 要删除索引，您必须是该索引的所有者或者具有 DROP ANY INDEX 权限： DROP INDEX upper_dept_name_idx; Index dropped. DROP INDEX index; 版权所有 © 2006，Oracle。保留所有权利。 DROP TABLE … PURGE DROP TABLE dept80 PURGE; 版权所有 © 2006，Oracle。保留所有权利。 FLASHBACK TABLE 语句 • 可以还原对表的意外修改 – 使表还原到以前的状态 – 优点：易于使用、高可用性和执行速度快 – 可以在本地执行 • 语法： • FLASHBACK TABLE[schema.]table[, [ schema.]table ]... TO { TIMESTAMP | SCN } expr [ { ENABLE | DISABLE } TRIGGERS ]; 版权所有 © 2006，Oracle。保留所有权利。 FLASHBACK TABLE 语句 DROP TABLE emp2; Table dropped FLASHBACK TABLE emp2 TO BEFORE DROP; Flashback complete … SELECT original_name, operation, droptime, FROM recyclebin; 版权所有 © 2006，Oracle。保留所有权利。 外部表 版权所有 © 2006，Oracle。保留所有权利。 创建外部表目录 创建一个 DIRECTORY 对象，该对象与外部数据源所在的 文件系统的目录相对应。 CREATE OR REPLACE DIRECTORY emp_dir AS '/…/emp_dir'; GRANT READ ON DIRECTORY emp_dir TO hr; 版权所有 © 2006，Oracle。保留所有权利。 创建外部表 CREATE TABLE ( , … ) ORGANIZATION EXTERNAL (TYPE DEFAULT DIRECTORY ACCESS PARAMETERS (… ) ) LOCATION ('') ) REJECT LIMIT [0 | | UNLIMITED]; 版权所有 © 2006，Oracle。保留所有权利。 使用 ORACLE_LOADER 创建外部表 CREATE TABLE oldemp ( fname char(25), lname CHAR(25)) ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY emp_dir ACCESS PARAMETERS (RECORDS DELIMITED BY NEWLINE NOBADFILE NOLOGFILE FIELDS TERMINATED BY ',' (fname POSITION ( 1:20) CHAR, lname POSITION (22:41) CHAR)) LOCATION ('emp.dat')) PARALLEL 5 REJECT LIMIT 200; Table created. 版权所有 © 2006，Oracle。保留所有权利。 查询外部表 SELECT * FROM oldemp emp.datOLDEMP 版权所有 © 2006，Oracle。保留所有权利。 小结 在本课中，您应该已经学会如何： • 添加约束条件 • 创建索引 • 使用索引创建主键约束条件 • 使用 CREATE TABLE 语句创建索引 • 创建基于函数的索引 • 删除列并将列设置为 UNUSED • 执行 FLASHBACK 操作 • 创建和使用外部表 版权所有 © 2006，Oracle。保留所有权利。 练习 2：概览 本练习包含以下主题： • 更改表 • 添加列 • 删除列 • 创建索引 • 创建外部表 版权所有 © 2006，Oracle。保留所有权利。 处理大型数据集 版权所有 © 2006，Oracle。保留所有权利。 课程目标 学完本课后，应能完成以下工作： • 使用子查询处理数据 • 描述多表 INSERT 的功能 • 使用下列类型的多表 INSERT – 无条件 INSERT – 转换 INSERT (Pivoting INSERT) – 条件 ALL INSERT – 条件 FIRST INSERT • 合并表中的行 • 跟踪一段时间内对数据的更改 版权所有 © 2006，Oracle。保留所有权利。 使用子查询处理数据 可以在数据操纵语言 (DML) 语句中使用子查询完成以下 任务： • 将数据从一个表复制到另一个表中 • 从内嵌视图中检索数据 • 根据另一个表中的值更新表中的数据 • 根据另一个表中的行删除表中的行 版权所有 © 2006，Oracle。保留所有权利。 从其它表中复制行 • 编写包含子查询的 INSERT 语句。 • 不要使用 VALUES 子句。 • 使 INSERT 子句中的列数与子查询中的列数相匹配。 INSERT INTO sales_reps(id, name, salary, commission_pct) SELECT employee_id, last_name, salary, commission_pct FROM employees WHERE job_id LIKE '%REP%'; 33 rows created. 版权所有 © 2006，Oracle。保留所有权利。 通过将子查询用作目标来进行插入 INSERT INTO (SELECT employee_id, last_name, email, hire_date, job_id, salary, department_id FROM empl3 WHERE department_id = 50) VALUES (99999, 'Taylor', 'DTAYLOR', TO_DATE('07-JUN-99', 'DD-MON-RR'), 'ST_CLERK', 5000, 50); 1 row created. 版权所有 © 2006，Oracle。保留所有权利。 通过将子查询用作目标来进行插入 请验证结果。 … SELECT employee_id, last_name, email, hire_date, job_id, salary, department_id FROM empl3 WHERE department_id = 50; 版权所有 © 2006，Oracle。保留所有权利。 通过将子查询用作源来检索数据 … SELECT a.last_name, a.salary, a.department_id, b.salavg FROM employees a JOIN(SELECT department_id, AVG(salary) salavg FROM employees GROUP BY department_id) b ON a.department_id = b.department_id AND a.salary > b.salavg; 版权所有 © 2006，Oracle。保留所有权利。 使用子查询更新两列 更新雇员 114 的职务和薪金，使其分别与雇员 205 的职务 和雇员 168 的薪金相匹配： UPDATE empl3 SET job_id = (SELECT job_id FROM employees WHERE employee_id = 205), salary = (SELECT salary FROM employees WHERE employee_id = 168) WHERE employee_id = 114; 1 row updated. 版权所有 © 2006，Oracle。保留所有权利。 根据另一个表更新行 在 UPDATE 语句中使用子查询，以根据另一个表中的值更新 表中的行： UPDATE empl3 SET department_id = (SELECT department_id FROM employees WHERE employee_id = 100) WHERE job_id = (SELECT job_id FROM employees WHERE employee_id = 200); 1 row updated. 版权所有 © 2006，Oracle。保留所有权利。 根据另一个表删除行 在 DELETE 语句中使用子查询，以根据另一个表中的值删除 表中的行： DELETE FROM empl3 WHERE department_id = (SELECT department_id FROM departments WHERE department_name LIKE '%Public%'); 1 row deleted. 版权所有 © 2006，Oracle。保留所有权利。 在 DML 语句中使用 WITH CHECK OPTION 关键字 • 子查询用于标识 DML 语句的表和列。 • WITH CHECK OPTION 关键字会禁止对不在子查询中的 行进行更改。 INSERT INTO (SELECT employee_id, last_name, email, hire_date, job_id, salary FROM empl3 WHERE department_id = 50 WITH CHECK OPTION) VALUES (99998, 'Smith', 'JSMITH', TO_DATE('07-JUN-99', 'DD-MON-RR'), 'ST_CLERK', 5000); INSERT INTO * ERROR at line 1: ORA-01402: view WITH CHECK OPTION where-clause violation 版权所有 © 2006，Oracle。保留所有权利。 显式默认值功能概览 • 使用显式默认值功能，可将 DEFAULT 关键字用作需要 列默认值的列的值。 • 增加此功能是为了符合 SQL:1999 标准。 • 此功能允许用户控制对数据应用默认值的位置和时间。 • 显式默认值可用在 INSERT 和 UPDATE 语句中。 版权所有 © 2006，Oracle。保留所有权利。 使用显式默认值 • DEFAULT 与 INSERT 配合使用： • DEFAULT 与 UPDATE 配合使用： UPDATE deptm3 SET manager_id = DEFAULT WHERE department_id = 10; INSERT INTO deptm3 (department_id, department_name, manager_id) VALUES (300, 'Engineering', DEFAULT); 版权所有 © 2006，Oracle。保留所有权利。 多表 INSERT 语句概览 Table_a Table_b Table_c INSERT ALL INTO table_a VALUES(…,…,…) INTO table_b VALUES(…,…,…) INTO table_c VALUES(…,…,…) SELECT … FROM sourcetab WHERE …; 版权所有 © 2006，Oracle。保留所有权利。 多表 INSERT 语句概览 • 可以使用 INSERT…SELECT 语句作为一条 DML 语句的 一部分将行插入到多个表中。 • 可在数据仓库系统中使用多表 INSERT 语句，将数据 从一个或多个操作源传送到一组目标表。 • 在以下方面，多表 INSERT 语句提供了重要的性能 改进： – 一条 DML 语句与多条 INSERT…SELECT 语句 – 单条 DML 与通过使用 IF...THEN 语法执行多次插入 的过程 版权所有 © 2006，Oracle。保留所有权利。 多表 INSERT 语句的类型 多表 INSERT 语句有以下不同类型： • 无条件 INSERT • 条件 ALL INSERT • 条件 FIRST INSERT • 转换 INSERT (Pivoting INSERT) 版权所有 © 2006，Oracle。保留所有权利。 多表 INSERT 语句 • 语法： • conditional_insert_clause: INSERT [ALL] [conditional_insert_clause] [insert_into_clause values_clause] (subquery) [ALL] [FIRST] [WHEN condition THEN] [insert_into_clause values_clause] [ELSE] [insert_into_clause values_clause] 版权所有 © 2006，Oracle。保留所有权利。 无条件 INSERT ALL • 在 EMPLOYEES 表中，选择 EMPLOYEE_ID 大于 200 的雇员的 EMPLOYEE_ID、HIRE_DATE、SALARY 和 MANAGER_ID 值。 • 通过使用多表 INSERT，可以将这些值插入到 SAL_HISTORY 和 MGR_HISTORY 表中。 INSERT ALL INTO sal_history VALUES(EMPID,HIREDATE,SAL) INTO mgr_history VALUES(EMPID,MGR,SAL) SELECT employee_id EMPID, hire_date HIREDATE, salary SAL, manager_id MGR FROM employees WHERE employee_id > 200; 12 rows created. 版权所有 © 2006，Oracle。保留所有权利。 条件 INSERT ALL • 在 EMPLOYEES 表中，选择 EMPLOYEE_ID 大于 200 的雇员的 EMPLOYEE_ID、HIRE_DATE、SALARY 和 MANAGER_ID 值。 • 如果 SALARY 高于 $10,000，则使用条件多表 INSERT 语句将这些值插入到 SAL_HISTORY 表中。 • 如果 MANAGER_ID 大于 200，则使用条件多表 INSERT 语句将这些值插入到 MGR_HISTORY 表中。 版权所有 © 2006，Oracle。保留所有权利。 条件 INSERT ALL INSERT ALL WHEN SAL > 10000 THEN INTO sal_history VALUES(EMPID,HIREDATE,SAL) WHEN MGR > 200 THEN INTO mgr_history VALUES(EMPID,MGR,SAL) SELECT employee_id EMPID,hire_date HIREDATE, salary SAL, manager_id MGR FROM employees WHERE employee_id > 200; 4 rows created. 版权所有 © 2006，Oracle。保留所有权利。 条件 INSERT FIRST • 从 EMPLOYEES 表中选择 DEPARTMENT_ID、 SUM(SALARY)和 MAX(HIRE_DATE)。 • 如果 SUM(SALARY)大于 $25,000，则通过使用条件 FIRST 多表 INSERT 将这些值插入到 SPECIAL_SAL 中。 • 如果第一条 WHEN 子句的计算结果为真，则跳过此行的 后续 WHEN 子句。 • 对于不满足第一个 WHEN 条件的行，根据 HIRE_DATE 列中的值使用条件多表 INSERT，将其插入到 HIREDATE_HISTORY_00、HIREDATE_HISTORY_99 或 HIREDATE_HISTORY 表中。 版权所有 © 2006，Oracle。保留所有权利。 条件 INSERT FIRST INSERT FIRST WHEN SAL > 25000 THEN INTO special_sal VALUES(DEPTID, SAL) WHEN HIREDATE like ('%00%') THEN INTO hiredate_history_00 VALUES(DEPTID,HIREDATE) WHEN HIREDATE like ('%99%') THEN INTO hiredate_history_99 VALUES(DEPTID, HIREDATE) ELSE INTO hiredate_history VALUES(DEPTID, HIREDATE) SELECT department_id DEPTID, SUM(salary) SAL, MAX(hire_date) HIREDATE FROM employees GROUP BY department_id; 12 rows created. 版权所有 © 2006，Oracle。保留所有权利。 转换 INSERT (Pivoting INSERT) • 假定您从非关系数据库表 SALES_SOURCE_DATA 中得 到了以下格式的一组销售记录： EMPLOYEE_ID、WEEK_ID、SALES_MON、 SALES_TUE、 SALES_WED、SALES_THUR、 SALES_FRI • 您希望以下列更为典型的关系格式将这些记录存储在 SALES_INFO 表中： EMPLOYEE_ID、WEEK、SALES • 使用转换 INSERT (Pivoting INSERT)，将非关系数据 库表中的这组销售记录转换为关系格式。 版权所有 © 2006，Oracle。保留所有权利。 转换 INSERT (Pivoting INSERT) INSERT ALL INTO sales_info VALUES (employee_id,week_id,sales_MON) INTO sales_info VALUES (employee_id,week_id,sales_TUE) INTO sales_info VALUES (employee_id,week_id,sales_WED) INTO sales_info VALUES (employee_id,week_id,sales_THUR) INTO sales_info VALUES (employee_id,week_id, sales_FRI) SELECT EMPLOYEE_ID, week_id, sales_MON, sales_TUE, sales_WED, sales_THUR,sales_FRI FROM sales_source_data; 5 rows created. 版权所有 © 2006，Oracle。保留所有权利。 MERGE 语句 • 提供了有条件地更新数据或将数据插入到数据库表中的 功能 • 如果行存在，则执行 UPDATE，如果行为新行，则执行 INSERT： – 避免单独的更新 – 改进性能并提高易用性 – 在数据仓库应用程序中很有用 版权所有 © 2006，Oracle。保留所有权利。 MERGE 语句语法 通过使用 MERGE 语句，可以有条件地插入或更新表中的行。 MERGE INTO table_name table_alias USING (table|view|sub_query) alias ON (join condition) WHEN MATCHED THEN UPDATE SET col1 = col_val1, col2 = col2_val WHEN NOT MATCHED THEN INSERT (column_list) VALUES (column_values); 版权所有 © 2006，Oracle。保留所有权利。 合并行 在 EMPL3 表中插入或更新行以与 EMPLOYEES 表相匹配。 MERGE INTO empl3 c USING employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET c.first_name = e.first_name, c.last_name = e.last_name, ... c.department_id = e.department_id WHEN NOT MATCHED THEN INSERT VALUES(e.employee_id, e.first_name, e.last_name, e.email, e.phone_number, e.hire_date, e.job_id, e.salary, e.commission_pct, e.manager_id, e.department_id); 版权所有 © 2006，Oracle。保留所有权利。 合并行 MERGE INTO empl3 c USING employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET ... WHEN NOT MATCHED THEN INSERT VALUES...; TRUNCATE TABLE empl3; SELECT * FROM empl3; no rows selected SELECT * FROM empl3; 107 rows selected. 版权所有 © 2006，Oracle。保留所有权利。 跟踪数据中的更改 检索到的行的版本 SELECT … 版权所有 © 2006，Oracle。保留所有权利。 闪回版本查询示例 SELECT salary FROM employees3 WHERE employee_id = 107; UPDATE employees3 SET salary = salary * 1.30 WHERE employee_id = 107; COMMIT; SELECT salary FROM employees3 VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE WHERE employee_id = 107; 1 2 3 版权所有 © 2006，Oracle。保留所有权利。 VERSIONS BETWEEN 子句 SELECT versions_starttime "START_DATE", versions_endtime "END_DATE", salary FROM employees VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE WHERE last_name = 'Lorentz'; 版权所有 © 2006，Oracle。保留所有权利。 小结 在本课中，您应该已经学会如何： • 使用 DML 语句和控制事务处理 • 描述多表 INSERT 的功能 • 使用以下类型的多表 INSERT： – 无条件 INSERT – 转换 INSERT (Pivoting INSERT) – 条件 ALL INSERT – 条件 FIRST INSERT • 合并表中的行 • 使用子查询处理数据 • 跟踪一段时间内对数据的更改 版权所有 © 2006，Oracle。保留所有权利。 练习 3：概览 本练习包含以下主题： • 执行多表 INSERT • 执行 MERGE 操作 • 跟踪行版本 版权所有 © 2006，Oracle。保留所有权利。 通过分组相关数据生成报表 版权所有 © 2006，Oracle。保留所有权利。 课程目标 学完本课后，应能完成以下工作： • 使用 ROLLUP 操作生成小计值 • 使用 CUBE 操作生成交叉报表值 • 使用 GROUPING 函数确定由 ROLLUP 或 CUBE 创建的 行值 • 使用 GROUPING SETS 生成单个结果集 版权所有 © 2006，Oracle。保留所有权利。 分组函数概述 • 分组函数将对行集进行运算，为每个组提供一个结果。 • 示例： SELECT [column,] group_function(column). . . FROM table [WHERE condition] [GROUP BY group_by_expression] [ORDER BY column]; SELECT AVG(salary), STDDEV(salary), COUNT(commission_pct),MAX(hire_date) FROM employees WHERE job_id LIKE 'SA%'; 版权所有 © 2006，Oracle。保留所有权利。 GROUP BY 子句概述 • 语法： • 示例： SELECT department_id, job_id, SUM(salary), COUNT(employee_id) FROM employees GROUP BY department_id, job_id ; SELECT [column,] FROM table [WHERE condition] [GROUP BY group_by_expression] [ORDER BY column]; group_function(column). . . 版权所有 © 2006，Oracle。保留所有权利。 HAVING 子句概述 • 使用 HAVING 子句指定要显示的组。 • 可以根据限制条件进一步限制组。 SELECT [column,] group_function(column)... FROM table [WHERE condition] [GROUP BY group_by_expression] [HAVING having_expression] [ORDER BY column]; 版权所有 © 2006，Oracle。保留所有权利。 使用 ROLLUP 和 CUBE 运算符的 GROUP BY • 将 ROLLUP 或 CUBE 与 GROUP BY 配合使用可以通过交 叉引用列生成超级汇总行。 • ROLLUP 分组将生成一个结果集，其中包含常规分组的 行和小计值。 • CUBE 分组将生成一个结果集，其中包含来自 ROLLUP 的行和交叉报表行。 版权所有 © 2006，Oracle。保留所有权利。 ROLLUP 运算符 • ROLLUP 是对 GROUP BY 子句的扩展。 • 使用 ROLLUP 操作可以生成累计的汇总，如小计。 SELECT [column,] group_function(column). . . FROM table [WHERE condition] [GROUP BY [ROLLUP] group_by_expression] [HAVING having_expression]; [ORDER BY column]; 版权所有 © 2006，Oracle。保留所有权利。 ROLLUP 运算符：示例 SELECT department_id, job_id, SUM(salary) FROM employees WHERE department_id < 60 GROUP BY ROLLUP(department_id, job_id); 3 1 2 版权所有 © 2006，Oracle。保留所有权利。 CUBE 运算符 • CUBE 是对 GROUP BY 子句的扩展。 • 可以在单个 SELECT 语句中使用 CUBE 运算符生成交叉 报表值。 SELECT [column,] group_function(column)... FROM table [WHERE condition] [GROUP BY [CUBE] group_by_expression] [HAVING having_expression] [ORDER BY column]; 版权所有 © 2006，Oracle。保留所有权利。 CUBE 运算符：示例 SELECT department_id, job_id, SUM(salary) FROM employees WHERE department_id < 60 GROUP BY CUBE (department_id, job_id) ; 1 2 3 4 版权所有 © 2006，Oracle。保留所有权利。 GROUPING 函数 GROUPING 函数： • 与 CUBE 或 ROLLUP 运算符配合使用。 • 用于查找在一行中生成小计的组。 • 用于区分存储的 NULL 值和 ROLLUP 或 CUBE 创建的 NULL 值。 • 返回 0 或 1。 SELECT [column,] group_function(column) .. , GROUPING(expr) FROM table [WHERE condition] [GROUP BY [ROLLUP][CUBE] group_by_expression] [HAVING having_expression] [ORDER BY column]; 版权所有 © 2006，Oracle。保留所有权利。 GROUPING 函数：示例 1 2 3 SELECT department_id DEPTID, job_id JOB, SUM(salary), GROUPING(department_id) GRP_DEPT, GROUPING(job_id) GRP_JOB FROM employees WHERE department_id < 50 GROUP BY ROLLUP(department_id, job_id); 版权所有 © 2006，Oracle。保留所有权利。 GROUPING SETS • GROUPING SETS 语法用于定义同一查询中的多个分组。 • 计算 GROUPING SETS 子句中指定的所有分组，并使用 UNION ALL 操作合并各个分组的结果。 • GROUPING SETS 的功能： – 只需要对基表执行一次操作即可完成分组计算。 – 无需编写复杂的 UNION 语句。 – GROUPING SETS 子句包含的元素越多，性能就越好。 版权所有 © 2006，Oracle。保留所有权利。 GROUPING SETS：示例 … 1 2 SELECT department_id, job_id, manager_id,avg(salary) FROM employees GROUP BY GROUPING SETS ((department_id,job_id), (job_id,manager_id)); … 版权所有 © 2006，Oracle。保留所有权利。 组合列 • 组合列是多个列的集合，可以将其视为一个单元。 ROLLUP (a, , d) • 在 GROUP BY 子句内使用括号对列进行分组，以便在 计算 ROLLUP 或 CUBE 操作时将这些组视为一个单元。 • 在与 ROLLUP 或 CUBE 配合使用时，组合列需要跳过 某些级别的汇总。 (b, c) 版权所有 © 2006，Oracle。保留所有权利。 组合列：示例 … 3 4 1 2 SELECT department_id, job_id, manager_id, SUM(salary) FROM employees GROUP BY ROLLUP( department_id,(job_id, manager_id)); 版权所有 © 2006，Oracle。保留所有权利。 级联分组 • 级联分组提供了一种简洁的方法来生成有用的分组组合。 • 要指定级联分组集，可以用逗号分隔多个分组集、 ROLLUP 和 CUBE 操作，以使 Oracle 服务器将它们 组合到一个 GROUP BY 子句中。 • 结果为每个 GROUPING SET 的交叉单元分组。 GROUP BY GROUPING SETS(a, b), GROUPING SETS(c, d) 版权所有 © 2006，Oracle。保留所有权利。 级联分组：示例 … … … 1 2 3 4 5 SELECT department_id, job_id, manager_id, SUM(salary) FROM employees GROUP BY department_id, ROLLUP(job_id), CUBE(manager_id); 版权所有 © 2006，Oracle。保留所有权利。 小结 在本课中，您应该已经学会了如何： • 使用 ROLLUP 操作生成小计值 • 使用 CUBE 操作生成交叉报表值 • 使用 GROUPING 函数确定由 ROLLUP 或 CUBE 创建的 行值 • 使用 GROUPING SETS 语法定义同一查询中的多个分组 • 使用 GROUP BY 子句以不同的方式组合表达式： – 组合列 – 级联分组集 版权所有 © 2006，Oracle。保留所有权利。 练习 4：概览 本练习包含以下内容： • 使用 ROLLUP 运算符 • 使用 CUBE 运算符 • 使用 GROUPING 函数 • 使用 GROUPING SETS 版权所有 © 2006，Oracle。保留所有权利。 管理不同时区中的数据 版权所有 © 2006，Oracle。保留所有权利。 课程目标 学完本课后，应能使用以下日期时间函数： • TZ_OFFSET • FROM_TZ • TO_TIMESTAMP • TO_TIMESTAMP_TZ • TO_YMINTERVAL • TO_DSINTERVAL • CURRENT_DATE • CURRENT_TIMESTAMP • LOCALTIMESTAMP • DBTIMEZONE • SESSIONTIMEZONE • EXTRACT 版权所有 © 2006，Oracle。保留所有权利。 时区 -08:00 该图像表示格林尼治时间为 12:00 时， 各个时区的时间。 -05:00 +02:00 +10:00 +07:00 版权所有 © 2006，Oracle。保留所有权利。 TIME_ZONE 会话参数 可将 TIME_ZONE 可设置为： • 绝对偏移量 • 数据库时区 • 操作系统本地时区 • 命名区域 ALTER SESSION SET TIME_ZONE = '-05:00'; ALTER SESSION SET TIME_ZONE = dbtimezone; ALTER SESSION SET TIME_ZONE = local; ALTER SESSION SET TIME_ZONE = 'America/New_York'; 版权所有 © 2006，Oracle。保留所有权利。 CURRENT_DATE、CURRENT_TIMESTAMP 和 LOCALTIMESTAMP • CURRENT_DATE： – 返回系统中的当前日期 – 数据类型为 DATE • CURRENT_TIMESTAMP： – 返回系统中的当前时间戳 – 数据类型为 TIMESTAMP WITH TIME ZONE • LOCALTIMESTAMP： – 返回用户会话中的当前时间戳 – 数据类型为 TIMESTAMP 版权所有 © 2006，Oracle。保留所有权利。 CURRENT_DATE 显示会话时区中的当前日期和时间。 ALTER SESSION SET TIME_ZONE = '-8:0'; SELECT SESSIONTIMEZONE, CURRENT_DATE FROM DUAL; ALTER SESSION SET TIME_ZONE = '-5:0'; SELECT SESSIONTIMEZONE, CURRENT_DATE FROM DUAL; ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS'; 版权所有 © 2006，Oracle。保留所有权利。 CURRENT_TIMESTAMP 显示会话时区中的当前日期和零点几秒时间。 ALTER SESSION SET TIME_ZONE = '-8:0'; SELECT SESSIONTIMEZONE, CURRENT_TIMESTAMP FROM DUAL; ALTER SESSION SET TIME_ZONE = '-5:0'; SELECT SESSIONTIMEZONE, CURRENT_TIMESTAMP FROM DUAL; 版权所有 © 2006，Oracle。保留所有权利。 LOCALTIMESTAMP • 显示会话时区中的当前日期和时间，其值的数据类型为 TIMESTAMP。 • LOCALTIMESTAMP 将返回一个 TIMESTAMP 值，而 CURRENT_TIMESTAMP 将返回一个 TIMESTAMP WITH TIME ZONE 值。 ALTER SESSION SET TIME_ZONE = '-5:0'; SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM DUAL; ALTER SESSION SET TIME_ZONE = '-8:0'; SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM DUAL; 版权所有 © 2006，Oracle。保留所有权利。 DBTIMEZONE 和 SESSIONTIMEZONE • 显示数据库时区的值： • 显示会话时区的值： SELECT DBTIMEZONE FROM DUAL; SELECT SESSIONTIMEZONE FROM DUAL; 版权所有 © 2006，Oracle。保留所有权利。 TIMESTAMP 数据类型 • TIMESTAMP 数据类型是对 DATE 数据类型的扩展。 • 该数据类型可以存储 DATE 数据类型的年、月和日， 以及小时、分钟、秒值和零点几秒值。 • TIMESTAMP 的变体有： – TIMESTAMP [(fractional_seconds_precision)] – TIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONE – TIMESTAMP [(fractional_seconds_precision)] WITH LOCAL TIME ZONE 版权所有 © 2006，Oracle。保留所有权利。 TIMESTAMP 数据类型 与 TIMESTAMP 数据类型相同，其值中 还包含时区偏移量 TIMESTAMP WITH LOCAL TIME ZONE 与 TIMESTAMP 数据类型相同，还包括 以下类型： TIMEZONE_HOUR 和 TIMEZONE_MINUTE 或 TIMEZONE_REGION TIMESTAMP WITH TIME ZONE 精确到零点几秒的年、月、日、小时、 分钟、秒TIMESTAMP 字段数据类型 版权所有 © 2006，Oracle。保留所有权利。 TIMESTAMP 字段 TIMEZONE_MINUTE –12 到 14TIMEZONE_HOUR 00 到 59.9(N)，其中 9(N) 为精度SECOND 00 到 59MINUTE 00 到 23HOUR 01 到 31DAY 01 到 12MONTH –4712 到 9999（不包括 0 年）YEAR 有效值日期时间字段 00 到 59 版权所有 © 2006，Oracle。保留所有权利。 DATE 和 TIMESTAMP 之间的区别 -- when hire_date is of type DATE SELECT hire_date FROM emp5; ALTER TABLE emp5 MODIFY hire_date TIMESTAMP; SELECT hire_date FROM emp5; BA … … 版权所有 © 2006，Oracle。保留所有权利。 TIMESTAMP WITH TIME ZONE 数据类型 • TIMESTAMP WITH TIME ZONE 是 TIMESTAMP 的一种 变体，其值中包含时区偏移量。 • 时区偏移量是本地时间与 UTC 之间的差值，以小时和 分钟表示。 • 可以用以下格式指定此数据类型： TIMESTAMP[(fractional_seconds_precision)] WITH TIME ZONE 版权所有 © 2006，Oracle。保留所有权利。 TIMESTAMP WITH TIMEZONE：示例 CREATE TABLE web_orders (ord_id number primary key, order_date TIMESTAMP WITH TIME ZONE); INSERT INTO web_orders values (ord_seq.nextval, current_date); SELECT * FROM web_orders; 版权所有 © 2006，Oracle。保留所有权利。 TIMESTAMP WITH LOCAL TIMEZONE • TIMESTAMP WITH LOCAL TIME ZONE 是 TIMESTAMP 的另一种变体，其值中包括时区偏移量。 • 将存储在数据库中的数据规范化为数据库时区。 • 时区偏移量不会被存储为列数据的一部分。 • Oracle 数据库将返回用户本地会话时区中的数据。 • 可以按以下格式指定 TIMESTAMP WITH LOCAL TIME ZONE 数据类型： TIMESTAMP[(fractional_seconds_precision)] WITH LOCAL TIME ZONE 版权所有 © 2006，Oracle。保留所有权利。 TIMESTAMP WITH LOCAL TIMEZONE：示例 CREATE TABLE shipping (delivery_time TIMESTAMP WITH LOCAL TIME ZONE); INSERT INTO shipping VALUES(current_timestamp + 2); SELECT * FROM shipping; ALTER SESSION SET TIME_ZONE = 'EUROPE/LONDON'; SELECT * FROM shipping; 版权所有 © 2006，Oracle。保留所有权利。 INTERVAL 数据类型 • INTERVAL 数据类型用于存储两个日期时间值之间的差值 • 间隔分为两种类型： – 年-月 – 日-时间 • 间隔的精度： – 是构成间隔的字段的实际子集 – 可以用间隔限定符指定 精确到零点几秒的天、小时、分钟、秒INTERVAL DAY TO SECOND 年、月INTERVAL YEAR TO MONTH 字段数据类型 版权所有 © 2006，Oracle。保留所有权利。 INTERVAL 字段 00 到 59.9(N)，其中 9(N) 为精度SECOND 00 到 59MINUTE HOUR DAY MONTH YEAR INTERVAL 字段 00 到 23 任意正整数或负整数 00 到 11 任意正整数或负整数 有效的间隔值 版权所有 © 2006，Oracle。保留所有权利。 INTERVAL YEAR TO MONTH 数据类型 INTERVAL YEAR TO MONTH 可以使用 YEAR 和 MONTH 日期 时间字段存储时间段。 例如： INTERVAL YEAR [(year_precision)] TO MONTH '312-2' assigned to INTERVAL YEAR(3) TO MONTH Indicates an interval of 312 years and 2 months '312-0' assigned to INTERVAL YEAR(3) TO MONTH Indicates 312 years and 0 months '0-3' assigned to INTERVAL YEAR TO MONTH Indicates an interval of 3 months 版权所有 © 2006，Oracle。保留所有权利。 INTERVAL YEAR TO MONTH：示例 CREATE TABLE warranty (prod_id number, warranty_time INTERVAL YEAR(3) TO MONTH); INSERT INTO warranty VALUES (123, INTERVAL '8' MONTH); INSERT INTO warranty VALUES (155, INTERVAL '200' YEAR(3)); INSERT INTO warranty VALUES (678, '200-11'); SELECT * FROM warranty; 版权所有 © 2006，Oracle。保留所有权利。 INTERVAL DAY TO SECOND 数据类型 INTERVAL DAY TO SECOND (fractional_seconds_precision)将存储以天数、 小时数、分钟数和秒数表示的时间段。 例如： INTERVAL DAY[(day_precision)] TO Second INTERVAL '6 03:30:16' DAY TO SECOND Indicates an interval of 6 days 3 hours 30 minutes and 16 seconds INTERVAL '6 00:00:00' DAY TO SECOND Indicates an interval of 6 days and 0 hours, 0 minutes and 0 seconds 版权所有 © 2006，Oracle。保留所有权利。 INTERVAL DAY TO SECOND 数据类型：示例 CREATE TABLE lab ( exp_id number, test_time INTERVAL DAY(2) TO SECOND); INSERT INTO lab VALUES (100012, '90 00:00:00'); INSERT INTO lab VALUES (56098, INTERVAL '6 03:30:16' DAY TO SECOND); SELECT * FROM lab; 版权所有 © 2006，Oracle。保留所有权利。 EXTRACT • 显示 SYSDATE 中的 YEAR 组成部分。 • 对于 MANAGER_ID 为 100 的雇员，显示其 HIRE_DATE 中的 MONTH 组成部分。 SELECT last_name, hire_date, EXTRACT (MONTH FROM HIRE_DATE) FROM employees WHERE manager_id = 100; SELECT EXTRACT (YEAR FROM SYSDATE) FROM DUAL; 版权所有 © 2006，Oracle。保留所有权利。 TZ_OFFSET • 显示时区美国/东部的时区偏移量： • 显示时区加拿大/育空的时区偏移量： • 显示时区欧洲/伦敦的时区偏移量： SELECT TZ_OFFSET('US/Eastern') FROM DUAL; SELECT TZ_OFFSET('Canada/Yukon') FROM DUAL; SELECT TZ_OFFSET('Europe/London') FROM DUAL; 版权所有 © 2006，Oracle。保留所有权利。 使用 FROM_TZ 进行 TIMESTAMP 转换 • 将 TIMESTAMP 值 '2000-03-28 08:00:00' 显示为 TIMESTAMP WITH TIME ZONE 值。 • 将 TIMESTAMP 值 '2000-03-28 08:00:00' 显示为澳大 利亚/北部时区区域的 TIMESTAMP WITH TIME ZONE 值。 SELECT FROM_TZ(TIMESTAMP '2000-03-28 08:00:00','3:00') FROM DUAL; SELECT FROM_TZ(TIMESTAMP '2000-03-28 08:00:00', 'Australia/North') FROM DUAL; 版权所有 © 2006，Oracle。保留所有权利。 使用 TO_TIMESTAMP 和 TO_TIMESTAMP_TZ 将字符串转换为 TIMESTAMP • 将字符串 '2000-12-01 11:00:00' 显示为 TIMESTAMP 值： • 将字符串 '1999-12-01 11:00:00 -8:00' 显示为 TIMESTAMP WITH TIME ZONE 值： SELECT TO_TIMESTAMP ('2000-12-01 11:00:00', 'YYYY-MM-DD HH:MI:SS') FROM DUAL; SELECT TO_TIMESTAMP_TZ('1999-12-01 11:00:00 -8:00', 'YYYY-MM-DD HH:MI:SS TZH:TZM') FROM DUAL; 版权所有 © 2006，Oracle。保留所有权利。 使用 TO_YMINTERVAL 进行时间间隔转换 显示一个日期，该日期比在 DEPARTMENT_ID 为 20 的部门 中工作的雇员的聘用日期晚一年零两个月。 SELECT hire_date, hire_date + TO_YMINTERVAL('01-02') AS HIRE_DATE_YMININTERVAL FROM employees WHERE department_id = 20; 版权所有 © 2006，Oracle。保留所有权利。 使用 TO_DSINTERVAL：示例 TO_DSINTERVAL：将字符串转换为 INTERVAL DAY TO SECOND 数据类型 SELECT last_name, TO_CHAR(hire_date, 'mm-dd-yy:hh:mi:ss') hire_date, TO_CHAR(hire_date + TO_DSINTERVAL('100 10:00:00'), 'mm-dd-yy:hh:mi:ss') hiredate2 FROM employees; … 版权所有 © 2006，Oracle。保留所有权利。 夏令时 • 四月的第一个星期日 – 时间将从 01:59:59 a.m. 跳到 03:00:00 a.m. – 02:00:00 a.m. 到 02:59:59 a.m. 之间的值无效 • 十月的最后一个星期日 – 时间将从 02:00:00 a.m. 跳到 01:00:01 a.m. – 由于 01:00:01 a.m. 和 02:00:00 a.m. 被访问过两次， 所以它们之间的值是不确定的 版权所有 © 2006，Oracle。保留所有权利。 小结 在本课中，您应该已经学会了如何使用下列函数： • TZ_OFFSET • FROM_TZ • TO_TIMESTAMP • TO_TIMESTAMP_TZ • TO_YMINTERVAL • CURRENT_DATE • CURRENT_TIMESTAMP • LOCALTIMESTAMP • DBTIMEZONE • SESSIONTIMEZONE • EXTRACT 版权所有 © 2006，Oracle。保留所有权利。 练习 5：概览 在此练习中，您将练习使用日期时间函数。 版权所有 © 2006，Oracle。保留所有权利。 使用子查询检索数据 版权所有 © 2006，Oracle。保留所有权利。 课程目标 学完本课后，应能完成以下工作： • 编写多列子查询 • 在 SQL 中使用标量子查询 • 使用相关子查询解决问题 • 使用相关子查询更新和删除行 • 使用 EXISTS 和 NOT EXISTS 运算符 • 使用 WITH 子句 版权所有 © 2006，Oracle。保留所有权利。 多列子查询 主查询 WHERE (MANAGER_ID, DEPARTMENT_ID) IN 子查询 100 90 102 60 124 50 主查询的每一行都会与多行和多列子查询的值进行比较。 版权所有 © 2006，Oracle。保留所有权利。 列比较 涉及到子查询的多列比较可以是： • 不成对比较 • 成对比较 版权所有 © 2006，Oracle。保留所有权利。 成对比较子查询 显示与名字为“John”的雇员在同一经理领导下，并在同 一部门中工作的雇员的详细资料。 SELECT employee_id, manager_id, department_id FROM employees WHERE (manager_id, department_id) IN (SELECT manager_id, department_id FROM employees WHERE first_name = 'John') AND first_name <> 'John'; 版权所有 © 2006，Oracle。保留所有权利。 不成对比较子查询 显示与名字为“John”的雇员在同一经理领导下，并在同 一部门中工作的雇员的详细资料。 SELECT employee_id, manager_id, department_id FROM employees WHERE manager_id IN (SELECT manager_id FROM employees WHERE first_name = 'John') AND department_id IN (SELECT department_id FROM employees WHERE first_name = 'John') AND first_name <> 'John'; 版权所有 © 2006，Oracle。保留所有权利。 标量子查询表达式 • 标量子查询表达式是从一行中只返回一个列值的子查询。 • 标量子查询可以用于： – DECODE 和 CASE 的条件和表达式部分 – SELECT 语句中除 GROUP BY 之外的所有子句 版权所有 © 2006，Oracle。保留所有权利。 标量子查询：示例 • CASE 表达式中的标量子查询： • ORDER BY 子句中的标量子查询： SELECT employee_id, last_name, (CASE WHEN department_id = (SELECT department_id FROM departments WHERE location_id = 1800) THEN 'Canada' ELSE 'USA' END) location FROM employees; 20 SELECT employee_id, last_name FROM employees e ORDER BY (SELECT department_name FROM departments d WHERE e.department_id = d.department_id); 版权所有 © 2006，Oracle。保留所有权利。 相关子查询 相关子查询用于进行逐行处理。对于外部查询的每一行， 每个子查询都执行一次。 从外部查询中 获取候选行 使用内部查询中的值 确定候选行是否符合要求 使用候选行值 执行内部查询 版权所有 © 2006，Oracle。保留所有权利。 相关子查询 子查询引用了父查询中的某个表中的一列。 SELECT column1, column2, ... FROM table1 WHERE column1 operator (SELECT column1, column2 FROM table2 WHERE expr1 = .expr2); outer outer 版权所有 © 2006，Oracle。保留所有权利。 使用相关子查询 查找所得薪金高于其所在部门的平均薪金的所有雇员。 每次处理外部查询中 的行时，都会计算内 部查询。 SELECT last_name, salary, department_id FROM employees outer WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = outer.department_id); 版权所有 © 2006，Oracle。保留所有权利。 使用相关子查询 显示那些至少已调换过两次职务的雇员的详细资料。 SELECT e.employee_id, last_name,e.job_id FROM employees e WHERE 2 <= (SELECT COUNT(*) FROM job_history WHERE employee_id = e.employee_id); 版权所有 © 2006，Oracle。保留所有权利。 使用 EXISTS 运算符 • EXISTS 运算符可以测试在子查询的结果集中是否存 在行。 • 如果找到子查询行值，则会发生以下情况： – 不在内部查询中继续进行搜索 – 将条件标记为 TRUE • 如果未找到子查询行值，则会发生以下情况： – 该条件标记为 FALSE – 在内部查询中继续进行搜索 版权所有 © 2006，Oracle。保留所有权利。 查找至少有一名直属下级的雇员 SELECT employee_id, last_name, job_id, department_id FROM employees outer WHERE EXISTS ( SELECT 'X' FROM employees WHERE manager_id = outer.employee_id); 版权所有 © 2006，Oracle。保留所有权利。 查找没有任何雇员的所有部门 … SELECT department_id, department_name FROM departments d WHERE NOT EXISTS (SELECT 'X' FROM employees WHERE department_id = d.department_id); 版权所有 © 2006，Oracle。保留所有权利。 相关 UPDATE 使用相关子查询，可以根据一个表中的行，更新另一个表中 的行。 UPDATE table1 alias1 SET column = (SELECT expression FROM table2 alias2 WHERE alias1.column = alias2.column); 版权所有 © 2006，Oracle。保留所有权利。 使用相关 UPDATE • 通过添加一个存储部门名称的列，可以使 EMPL6 表 非标准化。 • 使用相关 UPDATE 填充该表。 ALTER TABLE empl6 ADD(department_name VARCHAR2(25)); UPDATE empl6 e SET department_name = (SELECT department_name FROM departments d WHERE e.department_id = d.department_id); 版权所有 © 2006，Oracle。保留所有权利。 相关 DELETE 使用相关子查询可以根据一个表中的行删除另一个表中的行。 DELETE FROM table1 alias1 WHERE column operator (SELECT expression FROM table2 alias2 WHERE alias1.column = alias2.column); 版权所有 © 2006，Oracle。保留所有权利。 使用相关 DELETE 使用相关子查询可以仅删除 EMPL6 表中那些同时还存在于 EMP_HISTORY 表中的行。 DELETE FROM empl6 E WHERE employee_id = (SELECT employee_id FROM emp_history WHERE employee_id = E.employee_id); 版权所有 © 2006，Oracle。保留所有权利。 WITH 子句 • 如果某个查询块在一个复杂的查询内多次出现，则使用 WITH 子句，可以在 SELECT 语句中重复使用该查询块。 • WITH 子句将检索查询块的结果，并将其存储在用户的 临时表空间中。 • WITH 子句可以提高性能。 版权所有 © 2006，Oracle。保留所有权利。 WITH 子句：示例 使用 WITH 子句编写一个查询，显示那些薪金总额大于各个 部门平均薪金的部门的部门名称和薪金总额。 版权所有 © 2006，Oracle。保留所有权利。 WITH 子句：示例 WITH dept_costs AS ( SELECT d.department_name, SUM(e.salary) AS dept_total FROM employees e JOIN departments d ON e.department_id = d.department_id GROUP BY d.department_name), avg_cost AS ( SELECT SUM(dept_total)/COUNT(*) AS dept_avg FROM dept_costs) SELECT * FROM dept_costs WHERE dept_total > (SELECT dept_avg FROM avg_cost) ORDER BY department_name; 版权所有 © 2006，Oracle。保留所有权利。 小结 在本课中，您应该已掌握了以下内容： • 多列子查询返回多个列 • 多列比较可以是成对比较，也可以是不成对比较 • 多列子查询还可用在 SELECT 语句的 FROM 子句中 版权所有 © 2006，Oracle。保留所有权利。 小结 • 当子查询必须为每个候选行返回不同的结果时，相关子 查询很有用。 • EXIST 运算符是一个布尔运算符，用于测试某个值是 否存在。 • 相关子查询可以与 SELECT、UPDATE 和 DELETE 语句 配合使用。 • 如果某一查询块出现多次，则可使用 WITH 子句在 SELECT 语句中重复使用该查询块。 版权所有 © 2006，Oracle。保留所有权利。 练习 6：概览 本练习包含以下主题： • 创建多列子查询 • 编写相关子查询 • 使用 EXISTS 运算符 • 使用标量子查询 • 使用 WITH 子句 版权所有 © 2006，Oracle。保留所有权利。 分层检索 版权所有 © 2006，Oracle。保留所有权利。 课程目标 学完本课后，应能完成以下工作： • 解释分层查询的概念 • 创建树状结构报表 • 格式化分层数据 • 从树状结构中去除分支 版权所有 © 2006，Oracle。保留所有权利。 EMPLOYEES 表中的示例数据 … 版权所有 © 2006，Oracle。保留所有权利。 自然树结构 De Haan King Hunold EMPLOYEE_ID = 100（父节点） MANAGER_ID = 100（子节点） Whalen Kochhar Higgins Mourgos Zlotkey Rajs Davies Matos Gietz Ernst Lorentz Hartstein Fay Abel Taylor Grant Vargas 版权所有 © 2006，Oracle。保留所有权利。 分层查询 WHERE condition: expr comparison_operator expr SELECT [LEVEL], column, expr... FROM table [WHERE condition(s)] [START WITH condition(s)] [CONNECT BY PRIOR condition(s)] ; 版权所有 © 2006，Oracle。保留所有权利。 遍历树 • 指定必须满足的条件 • 接受任何有效的条件 使用 EMPLOYEES 表，从姓氏为 Kochhar 的雇员开始。 起始点 ...START WITH last_name = 'Kochhar' START WITH column1 = value 版权所有 © 2006，Oracle。保留所有权利。 遍历树 使用 EMPLOYEES 表，自上而下进行遍历。 方向 自上而下 Column 1 = 父关键字 Column 2 = 子关键字 自下而上 Column 1 = 子关键字 Column 2 = 父关键字 CONNECT BY PRIOR column1 = column2 ... CONNECT BY PRIOR employee_id = manager_id 版权所有 © 2006，Oracle。保留所有权利。 遍历树：自下而上 SELECT employee_id, last_name, job_id, manager_id FROM employees START WITH employee_id = 101 CONNECT BY PRIOR manager_id = employee_id ; 版权所有 © 2006，Oracle。保留所有权利。 遍历树：自上而下 SELECT last_name||' reports to '|| PRIOR last_name "Walk Top Down" FROM employees START WITH last_name = 'King' CONNECT BY PRIOR employee_id = manager_id ; … 版权所有 © 2006，Oracle。保留所有权利。 使用 LEVEL 伪列排列行的等级 Whalen 第 1 级 根/父节点 第 3 级 父节点/子节点/叶节点 第 4 级 叶节点 De Haan King Hunold Kochhar Higgins Mourgos Zlotkey Rajs Davies Matos Gietz Ernst Lorentz Hartstein Fay Abel Taylor Grant Vargas 版权所有 © 2006，Oracle。保留所有权利。 使用 LEVEL 和 LPAD 格式化分层报表 创建一个显示公司管理级别的报表，从最高级别开始，并依 次缩进下面的每个级别。 COLUMN org_chart FORMAT A12 SELECT LPAD(last_name, LENGTH(last_name)+(LEVEL*2)-2,'_') AS org_chart FROM employees START WITH first_name='Steven' AND last_name='King' CONNECT BY PRIOR employee_id=manager_id 版权所有 © 2006，Oracle。保留所有权利。 修剪分支 使用 WHERE 子句 删除一个节点。 使用 CONNECT BY 子句 删除一个分支。 WHERE last_name != 'Higgins'CONNECT BY PRIOR employee_id = manager_id AND last_name != 'Higgins' Kochhar Higgins Gietz Whalen Kochhar HigginsWhalen Gietz 版权所有 © 2006，Oracle。保留所有权利。 小结 在本课中，您应该已经学会： • 使用分层查询来查看表中各行之间的分层关系 • 指定查询的方向和起始点 • 通过修剪删除节点或分支 版权所有 © 2006，Oracle。保留所有权利。 练习 7：概览 本练习包含以下主题： • 区分分层查询和非分层查询 • 遍历树 • 通过使用 LEVEL 伪列生成一个缩进格式的报表 • 修剪树结构 • 对输出进行排序 版权所有 © 2006，Oracle。保留所有权利。 正则表达式支持 版权所有 © 2006，Oracle。保留所有权利。 课程目标 完成本课后，您应可以在 SQL 中使用正则表达式支持来 根据正则表达式搜索、匹配和替换字符串。 版权所有 © 2006，Oracle。保留所有权利。 正则表达式：概览 ABC 描述用于搜索和处理 的简单模式和复杂模 式的方法 支持正则表达式 的几个新函数 对 SQL 和 PL/SQL 字符串类型提供多语 言的正则表达式支持 版权所有 © 2006，Oracle。保留所有权利。 元字符 指定对等的分类[==] 匹配一次或多次+ 匹配零次或一次? 除了 NULL 之外，匹配受支持的字符集中的任何字符. 分组表达式，将其当作单个子表达式处理() 向后引用表达式\n 指定字符类并匹配该类中的任何字符[: :] 可能有 4 种不同的含义：1. 代表自身。2. 引用下一个字符。3. 引入运算符。 4 .不做任何处理。\ 说明符号 指定一个排序元素，如多字符元素 至少匹配 m 次但不超过 n 次 正好匹配 m 次 将匹配列表的表达式括起来，表示匹配列表中包含的任何一个表达式 匹配行的开始/行的结尾 指定备选匹配项的变换运算符 匹配零次或多次 [..] {m,n} {m} [ ] ^/$ | * 版权所有 © 2006，Oracle。保留所有权利。 使用元字符 Problem: Find 'abc' within a string: Solution: 'abc' Matches: abc Does not match: 'def' Problem: To find 'a' followed by any character, followed by 'c' Meta Character: any character is defined by '.' Solution: 'a.c' Matches: abc Matches: adc Matches: alc Matches: a&c Does not match: abb Problem: To find one or more occurrences of 'a' Meta Character: Use'+' sign to match one or more of the previous characters Solution: 'a+' Matches: a Matches: aa Does not match: bbb 1 2 3 版权所有 © 2006，Oracle。保留所有权利。 正则表达式函数 根据正则表达式模式搜索给定的字符串，然 后返回找到匹配的位置 REGEXP_INSTR 在给定的字符串中搜索正则表达式模式，然 后返回匹配的子字符串 REGEXP_SUBSTR 搜索正则表达式模式，并使用替换字符串进 行替换 REGEXP_REPLACE 与 LIKE 运算符相似，但是它执行正则表达 式匹配，而不执行简单的模式匹配 REGEXP_LIKE 说明函数名称 版权所有 © 2006，Oracle。保留所有权利。 REGEXP 函数语法 REGEXP_LIKE (srcstr, pattern [,match_option]) REGEXP_INSTR (srcstr, pattern [, position [, occurrence [, return_option [, match_option]]]]) REGEXP_SUBSTR (srcstr, pattern [, position [, occurrence [, match_option]]]) REGEXP_REPLACE(srcstr, pattern [,replacestr [, position [, occurrence [, match_option]]]]) 版权所有 © 2006，Oracle。保留所有权利。 SELECT first_name, last_name FROM employees WHERE REGEXP_LIKE (first_name, '^Ste(v|ph)en$'); 执行基本搜索 版权所有 © 2006，Oracle。保留所有权利。 检查模式的存在 SELECT street_address, REGEXP_INSTR(street_address,'[^[:alpha:]]') FROM locations WHERE REGEXP_INSTR(street_address,'[^[:alpha:]]')> 1; 版权所有 © 2006，Oracle。保留所有权利。 SELECT REGEXP_SUBSTR(street_address , ' [^ ]+ ') "Road" FROM locations; 提取子字符串示例 … 版权所有 © 2006，Oracle。保留所有权利。 SELECT REGEXP_REPLACE( country_name, '(.)', '\1 ') "REGEXP_REPLACE" FROM countries; 替换模式 … 版权所有 © 2006，Oracle。保留所有权利。 正则表达式和检查约束条件 ALTER TABLE emp8 ADD CONSTRAINT email_addr CHECK(REGEXP_LIKE(email,'@'))NOVALIDATE ; INSERT INTO emp8 VALUES (500,'Christian','Patel', 'ChrisP2creme.com', 1234567890, '12-Jan-2004', 'HR_REP', 2000, null, 102, 40) ; 1 2 版权所有 © 2006，Oracle。保留所有权利。 小结 在本课中，您应该已经学会了如何在 SQL 和 PL/SQL 中使 用正则表达式支持，以便使用正则表达式搜索、匹配和替换 字符串。 版权所有 © 2006，Oracle。保留所有权利。 练习 8：概览 在本练习中，您将练习如何使用正则表达式。 版权所有 © 2006，Oracle。保留所有权利。 编写高级脚本 版权所有 © 2006，Oracle。保留所有权利。 课程目标 学完本附录后，应能完成以下工作： • 说明哪类问题可以用 SQL 生成 SQL 的方法来解决 • 编写一个脚本，用于生成 DROP TABLE 语句的脚本 • 编写一个脚本，用于生成 INSERT INTO 语句的脚本 版权所有 © 2006，Oracle。保留所有权利。 使用 SQL 生成 SQL • SQL 可用于生成 SQL 格式的脚本。 • 数据字典： – 是包含数据库信息的表和视图的集合 – 由 Oracle 服务器创建和维护 SQL 脚本 SQL 数据字典 版权所有 © 2006，Oracle。保留所有权利。 创建基本脚本 SELECT 'CREATE TABLE ' || table_name || '_test ' || 'AS SELECT * FROM ' || table_name ||' WHERE 1=2;' AS "Create Table Script" FROM user_tables; 版权所有 © 2006，Oracle。保留所有权利。 控制环境 将系统变量设置为适当的值 将系统变量设置回默认值 SQL STATEMENT SET ECHO OFF SET FEEDBACK OFF SET PAGESIZE 0 SET FEEDBACK ON SET PAGESIZE 24 SET ECHO ON 版权所有 © 2006，Oracle。保留所有权利。 完整的结构图 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 版权所有 © 2006，Oracle。保留所有权利。 将表的内容转储到文件 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 版权所有 © 2006，Oracle。保留所有权利。 将表的内容转储到文件 源 '''X''' '''' ''''||department_name||'''' ''', ''' ''');' 结果 'X' ' 'Administration' ',' '); 版权所有 © 2006，Oracle。保留所有权利。 生成动态谓词 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; 版权所有 © 2006，Oracle。保留所有权利。 小结 在本附录中，您应该已掌握了下列内容： • 编写一个 SQL 脚本，以生成另一个 SQL 脚本 • 脚本文件经常使用数据字典 • 在文件中捕获输出 版权所有 © 2006，Oracle。保留所有权利。 Oracle 结构层次组件 版权所有 © 2006，Oracle。保留所有权利。 课程目标 学完本附录后，应能完成以下工作： • 描述 Oracle 服务器的结构层次及其主要组件 • 列出将用户连接到 Oracle 实例时涉及的结构 • 列出处理下列内容时涉及的各个阶段： – 查询 – 数据操纵语言 (DML) 语句 – 提交 版权所有 © 2006，Oracle。保留所有权利。 Oracle 数据库体系结构：概览 Oracle 数据库由两个主要组件组成： • 数据库或物理结构 • 实例或内存结构 版权所有 © 2006，Oracle。保留所有权利。 数据库物理体系结构 控制文件 联机重做日志文件 口令文件参数文件 归档日志文件 数据文件 版权所有 © 2006，Oracle。保留所有权利。 控制文件 • 包含物理数据库结构信息 • 应多路复用以避免文件丢失 • 在 MOUNT 阶段读取控制文件 控制文件 版权所有 © 2006，Oracle。保留所有权利。 重做日志文件 • 记录对数据库进行的更改 • 应多路复用以避免文件丢失 重做日志缓冲区 日志 写进程 LGWR 组 1 组 2 组 3 版权所有 © 2006，Oracle。保留所有权利。 表空间和数据文件 • 表空间由一个或多个数据文件组成 • 数据文件仅归属于一个表空间 USERS 表空间 数据文件 1 数据文件 2 版权所有 © 2006，Oracle。保留所有权利。 段、区和块 • 段存在于表空间中 • 段由区的集合组成 • 区是数据块的集合 • 数据块会被映射到 OS 块 段 区 数据块 OS 块 版权所有 © 2006，Oracle。保留所有权利。 Oracle 实例管理 系统 显示器 SMON 数据库 写进程 DBW0 日志 写进程 LGWR 进程 显示器 PMON 归档进程 ARC0 SGA 共享池 大型池流池 检查点 CKPT Java 池 数据库 缓冲区高速缓存 重做日志 缓冲区 版权所有 © 2006，Oracle。保留所有权利。 Oracle 内存结构 Java 池 数据库 缓冲区高速缓存 重做日志 缓冲区 共享池 大型池 SGA 流池 服务器 进程 1 PGA 服务器 进程 2 PGA 后台 进程 PGA 版权所有 © 2006，Oracle。保留所有权利。 Oracle 进程 系统 显示器 SMON 数据库 写进程 DBW0 检查点 CKPT 日志 写进程 LGWR 进程 显示器 PMON 归档进程 ARC0 服务器 进程 服务器 进程 服务器 进程 服务器 进程 系统全局区 (SGA) 后台进程 版权所有 © 2006，Oracle。保留所有权利。 其他关键物理结构 归档 日志文件 参数文件 口令文件 数据库 版权所有 © 2006，Oracle。保留所有权利。 处理 SQL 语句 • 使用以下进程连接到实例： – 用户进程 – 服务器进程 • 使用的 Oracle 服务器组件取决于 SQL 语句的类型： – 查询将返回一些行 – 数据操纵语言 (DML) 语句将记录更改 – 提交会确保执行事务处理恢复 • 某些 Oracle 服务器组件不参与 SQL 语句处理 版权所有 © 2006，Oracle。保留所有权利。 连接到实例 用户 服务器 服务器用户 客户机 用户 服务器 Oracle 服务器 服务器应用程序服务器 浏览器 版权所有 © 2006，Oracle。保留所有权利。 处理查询 • 分析： – 搜索相同的语句 – 检查语法、对象名和权限 – 锁定分析期间使用的对象 – 创建并存储执行计划 • 执行：确定所选的行 • 提取：将行返回给用户进程 版权所有 © 2006，Oracle。保留所有权利。 共享池 • 库高速缓存包含 SQL 语句文本、经过分析的代码和执 行计划 • 数据字典高速缓存包含表、列以及其它对象定义和权限 • 共享池的大小由 SHARED_POOL_SIZE 确定 数据字典 高速缓存 库高速缓存 共享池 版权所有 © 2006，Oracle。保留所有权利。 数据库缓冲区高速缓存 • 存储最近使用的块 • 根据 DB_BLOCK_SIZE 确定缓冲区的大小 • 由 DB_BLOCK_BUFFERS 定义缓冲区的数量 数据库缓冲区 高速缓存 版权所有 © 2006，Oracle。保留所有权利。 程序全局区 (PGA) • 不共享 • 只有服务器进程可以写入 • 包含： – 排序区 – 会话信息 – 游标状态 – 堆栈空间 服务器进程 PGA 版权所有 © 2006，Oracle。保留所有权利。 处理 DML 语句 UPDATE employees ... SGA 重做日志 缓冲区 数据库 缓冲区 高速缓存 共享池用户进程 服务器 进程 数据库 数据文件 控制文件 重做 日志文件 1 1 2 34 版权所有 © 2006，Oracle。保留所有权利。 重做日志缓冲区 • 它的大小由 LOG_BUFFER 确定 • 记录实例中所作的更改 • 是连续使用的缓冲区 • 是一种循环缓冲区 数据库缓冲区 高速缓存 版权所有 © 2006，Oracle。保留所有权利。 回退段 回退段 DML 语句 旧映象 新映象 表 版权所有 © 2006，Oracle。保留所有权利。 COMMIT 处理 1 3 4 SGA 重做 日志 缓冲区 数据库 缓冲区 高速缓存 LGWR 2用户进程 服务器 进程 数据库 数据文件 控制文件 重做 日志文件 共享池 实例 版权所有 © 2006，Oracle。保留所有权利。 小结 在本附录中，您应该已经学会如何： • 确定数据库文件：数据文件、控制文件和联机重做日志 • 描述 SGA 内存结构：DB 缓冲区高速缓存、共享 SQL 池和重做日志缓冲区 • 说明主要的后台进程：DBW0、LGWR、CKPT、PMON、 SMON 和 ARC0 • 列出 SQL 处理步骤：分析、执行和提取 版权所有 © 2006，Oracle。保留所有权利。 使用 SQL Developer 版权所有 © 2006，Oracle。保留所有权利。 课程目标 学完本附录后，应能完成以下工作： • 列举 Oracle SQL Developer 的主要功能 • 安装 Oracle SQL Developer • 确定 Oracle SQL Developer 的菜单项 • 创建数据库连接 • 管理数据库对象 • 使用 SQL 工作表 • 执行 SQL 语句和 SQL 脚本 • 创建和保存报表 版权所有 © 2006，Oracle。保留所有权利。 什么是 Oracle SQL Developer • Oracle SQL Developer 是一种图形工具，可以提高 工作效率并简化数据库开发任务。 • 使用标准的 Oracle 数据库验证可以连接到任何目标 Oracle 数据库方案。 SQL Developer 版权所有 © 2006，Oracle。保留所有权利。 主要功能 • 用 Java 进行开发 • 支持 Windows、Linux 和 Mac OS X 平台 • 使用 JDBC 瘦驱动程序进行默认连接 • 不需要安装程序 • 可以连接到任意 Oracle 数据库版本 9.2.0.1 和更高版本 • 与 JRE 1.5 捆绑在一起 版权所有 © 2006，Oracle。保留所有权利。 安装 SQL Developer 下载 Oracle SQL Developer 工具包，并将其解压缩到您的 计算机上的任意目录中。 版权所有 © 2006，Oracle。保留所有权利。 SQL Developer 的菜单 1 2 3 4 5 6 版权所有 © 2006，Oracle。保留所有权利。 创建数据库连接 • 要使用 SQL Developer，必须至少有一个数据库连接。 • 可以创建和测试以下情况中的连接： – 多个数据库 – 多个方案 • SQL Developer 会自动导入在系统的 tnsnames.ora 文件中定义的任何连接。 • 可以将连接导出到 XML 文件。 • 所创建的每个附加数据库连接都会在连接导航器层次结 构中列出。 版权所有 © 2006，Oracle。保留所有权利。 创建数据库连接 版权所有 © 2006，Oracle。保留所有权利。 浏览数据库对象 使用数据库导航器可以执行以下操作： • 浏览数据库方案中的许多对象 • 快速查看对象的定义 版权所有 © 2006，Oracle。保留所有权利。 创建方案对象 • SQL Developer 支持通过以下方式创建任意方案对象： – 在 SQL 工作表中执行 SQL 语句 – 使用上下文菜单 • 可以通过使用编辑对话框或某个上下文相关的菜单来编 辑对象。 • 查看用来作出调整（如创建一个新对象或编辑一个现有 的方案对象）的 DDL。 版权所有 © 2006，Oracle。保留所有权利。 创建新表：示例 版权所有 © 2006，Oracle。保留所有权利。 使用 SQL 工作表 • 使用 SQL 工作表可以输入并执行 SQL、PL/SQL 和 SQL *Plus 语句。 • 指定可以由与工作表相关的数据库连接处理的所有操作。 版权所有 © 2006，Oracle。保留所有权利。 使用 SQL 工作表 1 2 3 4 5 6 7 8 版权所有 © 2006，Oracle。保留所有权利。 执行 SQL 语句 使用“Enter SQL Statement（输入 SQL 语名）”框可以 输入一条或多条 SQL 语句。 版权所有 © 2006，Oracle。保留所有权利。 查看执行计划 版权所有 © 2006，Oracle。保留所有权利。 格式化 SQL 代码 格式化之前 格式化之后 版权所有 © 2006，Oracle。保留所有权利。 使用片段 片段可能是语法或示例的代码段。 版权所有 © 2006，Oracle。保留所有权利。 使用片段：示例 插入一个片段 编辑片段 版权所有 © 2006，Oracle。保留所有权利。 使用 SQL*Plus • SQL 工作表并不支持所有的 SQL*Plus 语句。 • 可以从 SQL Developer 中调用 SQL*Plus 命令行接口。 版权所有 © 2006，Oracle。保留所有权利。 数据库报表 SQL Developer 将提供有关数据库及其对象的大量预定义 报表。 版权所有 © 2006，Oracle。保留所有权利。 创建用户定义的报表 创建并保存用户定义的报表以供反复使用。 版权所有 © 2006，Oracle。保留所有权利。 小结 在本附录中，您应该已经学会了如何使用 SQL Developer 执行以下任务： • 浏览、创建和编辑数据库对象 • 在 SQL 工作表中执行 SQL 语句和脚本 • 创建和保存自定义的报表