• 1. Oracle 数据库开发
  • 2. 课程目标理解 Oracle 数据库体系结构 熟练掌握 SQL 命令和 SQL 函数 使用 Oracle 的各种数据库对象 使用 PL/SQL 编写存储过程 掌握基本的数据库配置和管理
  • 3. 第一章Oracle 结构体系
  • 4. 目标 了解 Oracle 体系结构的各种组件 掌握 Oracle 中的基本用户管理 了解 Oracle 的工具 了解 Oracle 的安装和卸载
  • 5. 人民是推动历史车轮前进的动力 需求是推动软件技术前进的动力
  • 6. 初始状态:硬件 + 程序程序的共性(稳定)成分:计算资源管理操作系统应用程序产生了:分离出了:软件硬件操作系统
  • 7. 初始状态:硬件 + 操作系统 + 应用程序程序的共性(稳定)成分:数据管理产生了:数据库管理系统操作系统操作系统数据存储文件数据库管理系统应用程序
  • 8. Oracle 数据库简介 2-1对象关系型的数据库管理系统 (ORDBMS) 在管理信息系统、企业数据处理、因特网及电子商务等领域使用非常广泛 在数据安全性与数据完整性控制方面性能优越 跨操作系统、跨硬件平台的数据互操作能力Oracle数据库的主要特点 支持多用户、大事务量的事务处理 数据安全性和完整性控制 支持分布式数据处理 可移植性
  • 9. Oracle 数据库简介 2-2 Oracle数据库基于客户端/服务器技术客户端应用程序通过向服务器请求并接收信息的方式与数据库进行交互。它充当用户与数据库之间的接口请求 响应数据库服务器对数据库表进行最佳管理,处理多个客户端对同一数据的并发访问。全面地保持数据完整性,并控制数据库访问权限等安全性需求网络服务器
  • 10. Oracle 10g体系结构Oracle 服务器 Oracle 数据库 Oracle 实例Oracle 数据库是一个数据的集合,该集合被视为一个逻辑单元管理数据库的后台进程和内存结构的集合称为 Oracle 实例 数据库的体系结构是指数据库的组成、工作过程与原理,以及数据在数据库中的组织与管理机制 Oracle服务器提供开放、全面和综合的信息管理,它由Oracle数据库和Oracle实例组成
  • 11. Oracle 主要组件 实 例 内存结构 后台进程 PMON SMON DBWR LGWR CKPT 其他 数据库数据文件数据文件数据文件控制文件控制文件日志文件日志文件参数文件归档日志文件口令文件SGA 用户进程服务器进程PGA共享池数据缓冲区日志缓冲区
  • 12. Oracle 实例 Oracle 实例是后台进程和内存结构的集合Oracle 实例 系统全局区 后台进程 分配 启动
  • 13. Oracle 数据库Oracle 数据库由操作系统文件组成,这些文件为数据库信息提供实际物理存储区 Oracle 数据库包括逻辑结构和物理结构物理结构 逻辑结构 物理结构包含数据库中的一组操作系统文件。逻辑结构指数据库创建之后形成的逻辑概念之间的关系Oracle 数据库
  • 14. 会话会话是用户与 Oracle 服务器的单个连接 当用户与服务器建立连接时创建会话 当用户与服务器断开连接时关闭会话启动 Oracle 实例使用SQL*Plus连接 至数据库创建用户进程创建服务器进程提交 SQL 查询
  • 15. Oracle 实例内存结构系统全局区 (SGA) 程序全局区 (PGA) Oracle 实例启动时分配系统全局区当服务器进程启动时分配程序全局区Oracle 的内存结构包含以下两个内存区: 内存区
  • 16. 系统全局区 2-1数据库信息存储于SGA,由多个数据库进程共享共享池 数据缓冲区 日志缓冲区 SGA的内存结构
  • 17. 系统全局区 2-2共享池 共享池是对SQL、PL/SQL程序进行语法分析、编译、执行的内存区域 共享池由库缓存和数据字典缓存组成。 共享池的大小直接影响数据库的性能。数据缓冲区 用于存储从磁盘数据文件中读入的数据,所有用户共享。 服务器进程将读入的数据保存在数据缓冲区中,当后续的请求需要这些数据时可以在内存中找到,不需要再从磁盘读取,提高了读取速度。 数据缓冲区的大小对数据库的读取速度有直接的影响。日志缓冲区 日志记录数据库的所有修改信息,日志信息首先产生于日志缓冲区。 当日志缓冲区的日志数据达到一定数量时,由后台进程将日志数据写入日志文件中。 相对来说,日志缓冲区对数据库的性能影响较小。
  • 18. 程序全局区程序全局区(PGA)包含单个服务器进程所需的数据和控制信息PGA是在用户进程连接到数据库并创建一个会话时自动分配的,保存每个与Oracle 数据库连接的用户进程所需的信息PGA为非共享区,只能单个进程使用,当一个用户会话结束,PGA释放
  • 19. Oracle 实例进程结构Oracle 实例有几种不同类型的进程,它们是: 实例进程用户进程 服务器进程 后台进程 用户进程是一个需要与Oracle服务器进行交互的程序。当用户运行一个应用程序准备向数据库服务器发送请求时,即创建了用户进程服务器进程用于处理连接到该实例的用户进程的请求。当用户连接至 Oracle 数据库实例创建会话时,即产生服务器进程 后台进程是Oracle数据库为了保持最佳系统性能和协调多个用户请求而设置的。 Oracle 实例启动时即创建一系列后台进程
  • 20. 后台进程 2-1后台进程PMON实例的各种后台进程是:SMONDBWRLGWRCKPT其他
  • 21. 后台进程 2-2PMON 进程监控进程清理出现故障的进程。 释放所有当前挂起的锁定。 释放故障进程使用的资源。SMON 系统监控进程在实例失败之后,重新打开数据库时自动恢复实例。 整理数据文件的自由空间,将相邻区域结合起来。 释放不再使用的临时段。DBWR 数据写入进程管理数据缓冲区,将最近使用过的块保留在内存中。 将修改后的缓冲区数据写入数据文件中。 LGWR 日志写入进程负责将日志缓冲区中的日志数据写入日志文件。 系统有多个日志文件,该进程以循环的方式将数据写入文件。
  • 22. Oracle 物理组件物理组件就是Oracle数据库所使用的操作系统物理文件。物理文件可分为三类:物理组件数据文件 控制文件日志文件数据文件用于存储数据库数据,如表、索引数据等。控制文件是记录数据库物理结构的二进制文件。日志文件记录对数据库的所有修改信息,用于故障恢复
  • 23. Oracle 逻辑组件 3-1数据库的逻辑结构是从逻辑的角度分析数据库的组成。Oracle 的逻辑组件包括:数据库 表空间 段 区 数据块 模式
  • 24. Oracle 逻辑组件 3-2表空间是数据库中最大的逻辑单位,一个 Oracle 数据库至少包含一个表空间,就是名为SYSTEM的系统表空间。 每个表空间是由一个或多个数据文件组成的,一个数据文件只能与一个表空间相关联。 表空间的大小等于构成该表空间的所有数据文件大小之和。CREATE TABLESPACE tablespacename DATAFILE ‘filename’ [SIZE integer [K|M]] [AUTOEXTEND [OFF|ON]];创建表空间的语法是:
  • 25. Oracle 逻辑组件 3-3段段是构成表空间的逻辑存储结构,段由一组区组成。 按照段所存储数据的特征,将段分为四种类型,即数据段、索引段、回退段和临时段。区区为段分配空间,它由连续的数据块组成。 当段中的所有空间已完全使用时,系统自动为该段分配一个新区。 区不能跨数据文件存在,只能存在于一个数据文件中。数据块数据块是Oracle服务器所能分配、读取或写入的最小存储单元。 Oracle服务器以数据块为单位管理数据文件的存储空间。 模式模式是对用户所创建的数据库对象的总称。 模式对象包括表、视图、索引、同义词、序列、过程和程序包等。
  • 26. Oracle 网络配置 2-1Oracle产品安装完成后,服务器和客户端都需要进行网络配置才能实现网络连接。 服务器端配置监听器,客户端配置网络服务名。tnsnames.oraOracle 客户端listener.oraOracle 服务器
  • 27. Oracle 网络配置 2-2服务器端监听器配置信息包括监听协议、地址及其他相关信息。 配置信息保存在名为listener.ora的文件中。在安装服务器软件时自动配置一个监听器 客户端的网络服务名配置信息包括服务器地址、监听端口号和数据库SID等,与服务器的监听器建立连接。配置信息保存在名为tnsnames.ora的文件中 Oracle中的 Net Configuration Assistant和Net Manager工具都能用来配置监听器和网络服务名
  • 28. Oracle 查询工具 Oracle 查询工具SQL*PlusiSQL*PlusPL/SQLOracle 提供的工具非常容易使用。Oracle 的查询工具包括:SQL*Plus是Oracle最常用的工具之一,用于接受和执行SQL命令以及PL/SQL块。 iSQL*Plus可以执行能用SQL*Plus完成的所有任务。该工具的优势在于能通过浏览器访问它。 PL/SQL 是 SQL 的扩展。PL/SQL 结合了SQL语言的数据操纵能力和过程语言的流程控制能力
  • 29. Oracle 企业管理器 Oracle 企业管理器为管理员提供了图形化的用户界面,并集成了管理数据库的工具
  • 30. Oracle 默认用户只有用合法的用户帐号才能访问Oracle数据库 Oracle 有几个默认的数据库用户 Oracle 默认用户SYSSYSTEMSCOTT数据库中所有数据字典表和视图都存储在 SYS 模式中。SYS用户主要用来维护系统信息和管理实例。SYSTEM 是默认的系统管理员,该用户拥有Oracle管理工具使用的内部表和视图。通常通过SYSTEM用户管理数据库用户、权限和存储等SCOTT用户是Oracle 数据库的一个示范帐户,在数据库安装时创建
  • 31. 创建新用户要连接到Oracle数据库,就需要创建一个用户帐户 每个用户都有一个默认表空间和一个临时表空间 CREATE USER命令用于创建新用户CREATE USER username IDENTIFIED BY password [DEFAULT TABLESPACE tablespace] [TEMPORARY TABLESPACE tablespace];CREATE USER 命令的语法是:CREATE USER MARTIN IDENTIFIED BY martinpwd DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;创建一个名称为 martin 的用户,其密码为 martinpwd
  • 32. 授予权限 3-1权限指的是执行特定命令或访问数据库对象的权利 权限有两种类型,系统权限和对象权限 系统权限允许用户执行某些数据库操作,如创建表就是一个系统权限 对象权限允许用户对数据库对象(如表、视图、序列等)执行特定操作 角色是一组相关权限的组合,可以将权限授予角色,再把角色授予用户,以简化权限管理。
  • 33. 授予权限 3-2GRANT 命令可用于为用户分配权限或角色 GRANT CONNECT TO MARTIN; CONNECT角色允许用户连接至数据库, 并创建数据库对象GRANT RESOURCE TO MARTIN; RESOURCE角色允许用户使用 数据库中的存储空间GRANT CREATE SEQUENCE TO MARTIN; 此系统权限允许用户在当前模式中创建 序列,此权限包含在CONNECT角色中
  • 34. 授予权限 3-3授予用户 MARTIN 操作TEST表对象的权限 GRANT SELECT ON TEST TO MARTIN; 允许用户查询 TEST 表的记录GRANT UPDATE ON TEST TO MARTIN; 允许用户更新 TEST 表中的记录GRANT ALL ON TEST TO MARTIN; 允许用户插入、删除、更新和查询 TEST 表中的记录
  • 35. 更改和删除用户ALTER USER 命令可用于更改口令 ALTER USER MARTIN IDENTIFIED BY martinpass;修改 MARTIN 用户的密码DROP USER 命令用于删除用户 DROP USER MARTIN CASCADE; 删除 MARTIN 用户模式
  • 36. Oracle 的安装和卸载通过运行Oracle Universal Installer来安装和卸载Oracle数据库选择 Oracle 9i Database 9.2.0.1.0 来安装数据库服务器。 选择 Oracle 9i Client 9.2.0.1.0 来安装 Oracle 客户端 启动Universal Installer,选择“卸装产品…”,选中要卸载的组件
  • 37. 使用 Oracle 数据库的开发流程服务器端安装 Oracle 服务器软件创建数据库(安装时自动创建)配置监听器(安装时自动配置)启动Oracle实例(自动启动服务)安装 Oracle 客户端软件配置网络服务名以新用户登录 Oracle提交 SQL 查询创建新用户并授权创建用户表空间客户端
  • 38. Windows 中的 Oracle 服务 3-1Oracle 9i的每个实例在Windows中都作为一项服务启动 服务是在 Windows 注册表中注册的可执行进程,由 Windows 操作系统管理 “服务”对话框中的各项 Oracle 服务如图所示:
  • 39. Windows 中的 Oracle 服务 3-2OracleHOME_NAMETNSListener该服务启动数据库服务器的监听器,监听器接受来自客户端应用程序的连接请求 若监听器未启动,则客户端将无法连接到数据库服务器OracleServiceSID该服务启动系统标识符为SID的数据库实例,其中 SID 是在安装 Oracle 9i 时输入的数据库名称OracleHOME_NAMEAgent Oracle企业管理器组件智能代理服务,此智能代理用于执行作业和监控 Oracle 服务或对象的性能 在使用Oracle管理服务器网络的环境中必须启动此服务
  • 40. Windows 中的 Oracle 服务 3-3OracleHOME_NAMEHTTPServer该服务用于启动Oracle HTTP服务器,它提供文档信息、基于Web的企业管理器和iSQL*Plus等服务OracleHOME_NAMEManagementServer该服务启动Oracle管理服务器,它用于维护管理控制台对各个被管理服务器节点之间的集中的、智能的和分布式的控制 该服务必须经过企业管理器配置向导配置完成后才创建
  • 41. 总结Oracle 服务器由Oracle 数据库和 Oracle 实例组成 Oracle 实例由系统全局区内存结构和用于管理数据库的后台进程组成 Oracle 中用于访问数据库的主要查询工具有 SQL*Plus、iSQL*Plus 和 PL/SQL Oracle 企业管理器是用于管理、诊断和调整多个数据库的工具 Oracle 中的 SYSTEM 用户和 SYS 用户具有管理权限,而 SCOTT 用户只有基本的权限 Oracle 服务在 Windows 注册表中注册,并由 Windows 操作系统管理
  • 42. 第二章SQL 查询和 SQL 函数
  • 43. Oracle 服务器由Oracle 数据库和 Oracle 实例组成 Oracle 实例由系统全局区内存结构和用于管理数据库的后台进程组成 Oracle 中用于访问数据库的主要查询工具有 SQL*Plus、iSQL*Plus 和 PL/SQL Oracle 企业管理器是用于管理、诊断和调整多个数据库的工具 Oracle 中的 SYSTEM 用户和 SYS 用户具有管理权限,而 SCOTT 用户只有基本的权限 回顾
  • 44. 目标了解 Oracle 数据类型 了解数据定义语言和数据操纵语言 了解事务控制语言和数据控制语言 掌握 SQL 操作符和 SQL 函数
  • 45. SQL 简介 2-1SQL 是 Structured Query Language(结构化查询语言)的首字母缩写词 SQL 是数据库语言,Oracle 使用该语言存储和检索信息 表是主要的数据库对象,用于存储数据通过 SQL可以实现与 Oracle 服务器的通信SELECT ename FROM Emp;发送 SQL 查询Oracle 服务器enameBLAKESMITH ALLEN DAVID MARTIN发送命令输出到用户端用户
  • 46. SQL 简介 2-2SQL 支持下列类别的命令: 数据定义语言(DDL) 数据操纵语言(DML) 事务控制语言(TCL) 数据控制语言(DCL)数据定义语言 CREATEALTERDROP数据操纵语言 INSERTSELECTDELETEUPDATE事务控制语言COMMITSAVEPOINTROLLBACK数据控制语言 GRANTREVOKE
  • 47. Oracle 数据类型 5-1创建表时,必须为各个列指定数据类型 以下是 Oracle 数据类型的类别:数据类型字符数值日期时间RAW/LONG RAWLOB
  • 48. Oracle 数据类型5-2字符数据类型CHARVARCHAR2LONG当需要固定长度的字符串时,使用 CHAR 数据类型。 CHAR 数据类型存储字母数字值。 CHAR 数据类型的列长度可以是 1 到 2000 个字节。VARCHAR2数据类型支持可变长度字符串 VARCHAR2数据类型存储字母数字值 VARCHAR2数据类型的大小在1至4000个字节范围内LONG 数据类型存储可变长度字符数据 LONG 数据类型最多能存储 2GB
  • 49. Oracle 数据类型 5-3数值数据类型 可以存储整数、浮点数和实数 最高精度为 38 位 数值数据类型的声明语法: NUMBER [( p[, s])] P表示精度,S表示小数点的位数日期时间数据类型存储日期和时间值,包括年、月、日,小时、分钟、秒 主要的日期时间类型有: DATE - 存储日期和时间部分,精确到整个的秒 TIMESTAMP - 存储日期、时间和时区信息,秒值精确到小数点后6位
  • 50. Oracle 数据类型 5-4RAW 数据类型用于存储二进制数据 RAW 数据类型最多能存储 2000 字节 LONG RAW 数据类型用于存储可变长度的二进制数据 LONG RAW 数据类型最多能存储 2 GBLOBLOB 称为“大对象”数据类型,可以存储多达 4GB 的非结构化信息,例如声音剪辑和视频文件等 LOB 数据类型允许对数据进行高效、随机、分段的访问BLOBCLOBBFILECLOB 即 Character LOB(字符 LOB),它能够存储大量字符数据BLOB 即 Binary LOB(二进制 LOB),可以存储较大的二进制对象,如图形、视频剪辑和声音文件 BFILE 即 Binary File(二进制文件),它用于将二进制数据存储在数据库外部的操作系统文件中
  • 51. Oracle 数据类型 5-5 Oracle 中伪列就像一个表列,但是它并没有存储在表中 伪列可以从表中查询,但不能插入、更新和删除它们的值 常用的伪列有ROWID和ROWNUMROWID 是表中行的存储地址,该地址可以唯一地标识数据库中的一行,可以使用 ROWID 伪列快速地定位表中的一行ROWNUM 是查询返回的结果集中行的序号,可以使用它来限制查询返回的行数
  • 52. 数据定义语言数据定义语言用于改变数据库结构,包括创建、更改和删除数据库对象 用于操纵表结构的数据定义语言命令有: CREATE TABLE ALTER TABLE TRUNCATE TABLE DROP TABLE
  • 53. 数据操纵语言 数据操纵语言用于检索、插入和修改数据 数据操纵语言是最常见的SQL命令 数据操纵语言命令包括: SELECT INSERT UPDATE DELETE
  • 54. DML – SELECT 命令 2-1利用现有的表创建表 语法: CREATE TABLE AS SELECT column_names FROM ;SQL> CREATE TABLE newitemfile AS SELECT * FROM itemfile;SQL> CREATE TABLE newitemfile1 AS SELECT itemcode, itemdesc, qty_hand FROM itemfile;SQL> CREATE TABLE newitemfile2 AS SELECT * FROM itemfile WHERE 1 = 2;
  • 55. DML – SELECT 命令 2-2SQL> SELECT DISTINCT vencode FROM vendor_master;选择无重复的行 在SELECT子句,使用DISTINCT关键字使用列别名 为列表达式提供不同的名称 该别名指定了列标题SQL> SELECT itemcode, itemdesc, max_level, max_level*2 AS NEW_MAXLEVEL FROM itemfile;SQL> SELECT itemcode,itemdesc, max_level, max_level* 2 “New Maximum Level” FROM itemfile;
  • 56. DML – INSERT 命令 2-1插入日期类型的值 日期数据类型的默认格式为“DD-MON-RR” 使用日期的默认格式 使用TO_DATE函数转换INSERT INTO my_table (date_col) VALUES (TO_DATE('2005-10-18', 'YYYY-MM-DD'));INSERT INTO order_master VALUES('o001', '12-5月-05', 'V002', 'c', '25-5月-05');
  • 57. DML – INSERT 命令 2-2插入来自其它表中的记录 语法: INSERT INTO [(cloumn_list)] SELECT column_names FROM ;SQL> INSERT INTO newvendor_master SELECT * FROM vendor_master; SQL> INSERT INTO newvendor_master(vencode,venname) SELECT vencode, venname FROM vendor_master;
  • 58. 事务控制语言事务是最小的工作单元,作为一个整体进行工作 保证事务的整体成功或失败,称为事务控制 用于事务控制的语句有: COMMIT - 提交并结束事务处理 ROLLBACK - 撤销事务中已完成的工作 SAVEPOINT – 标记事务中可以回滚的点SQL> UPDATE order_master SET del_date = ‘30-8月-05’ WHERE orderno <= ’o002’; SQL> SAVEPOINT mark1; SQL> DELETE FROM order_master WHERE orderno = ‘o002’; SQL> SAVEPOINT mark2; SQL> ROLLBACK TO SAVEPOINT mark1; SQL> COMMIT;
  • 59. 数据控制语言数据控制语言为用户提供权限控制命令 用于权限控制的命令有: GRANT 授予权限 REVOKE 撤销已授予的权限SQL> GRANT SELECT, UPDATE ON order_master TO MARTIN;SQL> GRANT UPDATE(qty_hand, re_level) ON itemfile TO MARTIN;SQL> GRANT SELECT ON vendor_master TO accounts WITH GRANT OPTION;SQL> REVOKE SELECT, UPDATE ON order_master FROM MARTIN;
  • 60. SQL 操作符SQL 操作符集合操作符逻辑操作符比较操作符算术操作符连接操作符Oracle 支持的 SQL 操作符分类如下:
  • 61. 算术操作符算术操作符用于执行数值计算 可以在SQL语句中使用算术表达式,算术表达式由数值数据类型的列名、数值常量和连接它们的算术操作符组成 算术操作符包括加(+)、减(-)、乘(*)、除(/)SQL> SELECT itemdesc, max_level - qty_hand avble_limit FROM itemfile WHERE p_category='spares';SQL > SELECT itemdesc, itemrate*(max_level - qty_hand) FROM itemfile WHERE p_category='spares';
  • 62. 比较操作符 比较操作符用于比较两个表达式的值 比较操作符包括 =、!=、<、>、<=、>=、BETWEEN…AND、IN、LIKE 和 IS NULL等SQL> SELECT itemdesc, re_level FROM itemfile WHERE qty_hand < max_level/2;SQL> SELECT orderno FROM order_master WHERE del_date IN (‘06-1月-05’,‘05-2月-05');SQL> SELECT vencode,venname,tel_no FROM vendor_master WHERE venname LIKE 'j___s';
  • 63. 逻辑操作符SQL> SELECT * FROM order_master WHERE odate > ‘10-5月-05' AND del_date < ‘26-5月-05’;显示 2005-5月-10 至 2005-5月-26的订单信息逻辑操作符用于组合多个计较运算的结果以生成一个或真或假的结果。 逻辑操作符包括与(AND)、或(OR)和非(NOT)。
  • 64. 集合操作符集合操作符将两个查询的结果组合成一个结果集合操作符UNIONUNION ALLINTERSECTMINUSINTERSECT 操作符只返回两个查询的公共行。SQL> SELECT orderno FROM order_master INTERSECT SELECT orderno FROM order_detail;SQL> SELECT orderno FROM order_master MINUS SELECT orderno FROM order_detail;MINUS 操作符返回从第一个查询结果中排除第二个查 询中出现的行。
  • 65. 连接操作符连接操作符用于将多个字符串或数据值合并成一个字符串SQL> SELECT (venname|| ' 的地址是 ' ||venadd1||' '||venadd2 ||' '||venadd3) address FROM vendor_master WHERE vencode='V001';通过使用连接操作符可以将表中 的多个列合并成逻辑上的一行列
  • 66. 操作符的优先级SQL 操作符的优先级从高到低的顺序是: 算术操作符 --------最高优先级 连接操作符 比较操作符 NOT 逻辑操作符 AND 逻辑操作符 OR 逻辑操作符 --------最低优先级
  • 67. SQL 函数Oracle 提供一系列用于执行特定操作的函数 SQL 函数带有一个或多个参数并返回一个值 以下是SQL函数的分类:SQL 函数单行函数分析函数分组函数
  • 68. 单行函数分类单行函数对于从表中查询的每一行只返回一个值 可以出现在 SELECT 子句中和 WHERE 子句中 单行函数可以大致划分为: 日期函数 数字函数 字符函数 转换函数 其他函数
  • 69. 日期函数日期函数对日期值进行运算,并生成日期数据类型或数值类型的结果 日期函数包括: ADD_MONTHS MONTHS_BETWEEN LAST_DAY ROUND NEXT_DAY TRUNC EXTRACT
  • 70. 字符函数 2-1 函数 输入 输出Initcap(char) Select initcap(‘hello’) from dual;Hello Lower(char) Select lower(‘FUN’) from dual;fun Upper(char) Select upper(‘sun’) from dual;SUN Ltrim(char,set) Select ltrim( ‘xyzadams’,’xyz’) from dual; adamsRtrim(char,set) Select rtrim(‘xyzadams’,’ams’) from dual; xyzad Translate(char, from, to) Select translate(‘jack’,’j’ ,’b’) from dual; back Replace(char, searchstring,[rep string]) Select replace(‘jack and jue’ ,’j’,’bl’) from dual;black and blue Instr (char, m, n) Select instr (‘worldwide’,’d’) from dual; 5 Substr (char, m, n) Select substr(‘abcdefg’,3,2) from dual; cd Concat (expr1, expr2) Select concat (‘Hello’,’ world’) from dual; Hello world字符函数字符函数接受字符输入并返回字符或数值
  • 71. 字符函数 2-2SQL> SELECT CHR(67) FROM dual;以下是一些其它的字符函数: CHR和ASCII LPAD和RPAD TRIM LENGTH DECODESQL> SELECT LPAD(‘function’,15,’=’) FROM dual;SQL> SELECT TRIM(9 from 9999876789999) FROM dual;SQL> SELECT LENGTH('frances') FROM dual;SQL> SELECT vencode, DECODE(venname,'frances','Francis') name FROM vendor_master WHERE vencode='v001';
  • 72. 数字函数数字函数接受数字输入并返回数值结果 函数 输入 输出Abs(n) Select abs(-15) from dual; 15Ceil(n) Select ceil(44.778) from dual; 45Cos(n) Select cos(180) from dual; -.5984601 Cosh(n) Select cosh(0) from dual; 1Floor(n) Select floor(100.2) from dual; 100Power(m,n) Select power(4,2) from dual; 16 Mod(m,n) Select mod(10,3) from dual; 1Round(m,n) Select round(100.256,2) from dual; 100.26 Trunc(m,n) Select trunc(100.256,2) from dual; 100.25 Sqrt(n) Select sqrt(4) from dual; 2 Sign(n)Select sign(-30) from dual;-1数字函数
  • 73. 转换函数转换函数将值从一种数据类型转换为另一种数据类型 常用的转换函数有: TO_CHAR TO_DATE TO_NUMBERSELECT TO_CHAR(sysdate,'YYYY"年"fmMM"月"fmDD"日" HH24:MI:SS') FROM dual;    SELECT TO_CHAR(itemrate,‘C99999’) FROM itemfile; SELECT TO_DATE(‘2005-12-06’ , ‘yyyy-mm-dd’) FROM dual;SELECT TO_NUMBER('100') FROM dual;
  • 74. 其它函数以下是几个用来转换空值的函数: NVL NVL2 NULLIFSELECT itemdesc, NVL(re_level,0) FROM itemfile;SELECT itemdesc, NVL2(re_level,re_level,max_level) FROM itemfile;SELECT itemdesc, NULLIF(re_level,max_level) FROM itemfile;
  • 75. 分组函数分组函数基于一组行来返回结果 为每一组行返回一个值AVGMINMAXSUMCOUNTSELECT AVG(re_level) FROM itemfile WHERE p_category='accessories';SELECT MAX(max_level) FROM itemfile;SELECT SUM(itemrate*max_level) FROM itemfile;SELECT COUNT(*) FROM itemfile;SELECT COUNT(itemrate) FROM itemfile; SELECT COUNT(DISTINCT qty_hand) FROM itemfile;分组函数
  • 76. GROUP BY和HAVING子句GROUP BY子句 用于将信息划分为更小的组 每一组行返回针对该组的单个结果 HAVING子句 用于指定 GROUP BY 子句检索行的条件SELECT p_category, MAX(itemrate) FROM itemfile GROUP BY p_category;SELECT p_category, MAX(itemrate) FROM itemfile GROUP BY p_category HAVING p_category NOT IN ('accessories');
  • 77. 分析函数 2-1分析函数根据一组行来计算聚合值 用于计算完成聚集的累计排名、移动平均数等 分析函数为每组记录返回多个行分析函数DENSE_RANKRANKROW_NUMBER
  • 78. 分析函数 2-2以下三个分析函数用于计算一个行在一组有序行中的排位,序号从1开始 ROW_NUMBER 返回连续的排位,不论值是否相等 RANK 具有相等值的行排位相同,序数随后跳跃 DENSE_RANK 具有相等值的行排位相同,序号是连续的 SELECT ename, job, deptno, sal, ROW_NUMBER() OVER (ORDER BY sal DESC) AS SAL_RANK FROM SCOTT.EMP;SELECT deptno, ename, sal, comm, RANK() OVER (PARTITION BY deptno ORDER BY sal DESC, comm) RANK FROM emp; SELECT d.dname, e.ename, e.sal, DENSE_RANK() OVER (PARTITION BY e.deptno ORDER BY e.sal DESC) AS DENRANK FROM emp e, dept d WHERE e.deptno = d.deptno;
  • 79. 总结SQL 是通用的数据库语言 SQL 命令可分为数据定义语言、数据操纵语言、事务控制语言和数据控制语言 Oracle 支持的数据类型包括字符、数值、日期时间、RAW 和 LOB 等 SQL 支持的操作符包括算术、比较、逻辑、集合和连接操作符 SQL 函数可大致分为单行函数、聚合函数和分析函数
  • 80. 第三章 锁和表分区
  • 81. 回顾SQL 是数据库语言,Oracle 使用该语言在数据库中存储和检索信息 Oracle 支持各种数据类型,如 VARCHAR2、NUMBER、LONG、RAW 和 DATE 等 数据操纵语言用于查询和修改表中的数据 事务控制语言管理事务的一致性 SQL 操作符包括算术、比较、逻辑、集合和连接操作符 SQL 函数可以大致分为单行函数、聚合函数和分析函数
  • 82. 目标理解锁定的概念 了解和使用表分区
  • 83. 锁的概念 2-1锁是数据库用来控制共享资源并发访问的机制。 锁用于保护正在被修改的数据 直到提交或回滚了事务之后,其他用户才可以更新数据
  • 84. 锁的概念 2-2修改表修改表拒绝访问Toy_IDNamePriceT001Barbie20T002GI Joe45
  • 85. 锁定的优点一致性 - 一次只允许一个用户修改数据 完整性 - 为所有用户提供正确的数据。如果一个用户进行了修改并保存,所做的修改将反映给所有用户并行性 -允许多个用户访问同一数据Toy_IDNamePriceT001Barbie20T002GI Joe45修改表中 的数据查看表中的数据允许访问
  • 86. 表级锁行级锁锁的类型锁的类型
  • 87. 行级锁 3-1Toy_IDNamePriceT001Barbie20T002GI Joe45更新 T002 行更新 T001 行行被锁定对正在被修改的行进行锁定。其他用户可以访问除被锁定的行以外的行允许访问
  • 88. 行级锁 3-2行级锁是一种排他锁,防止其他事务修改此行 在使用以下语句时,Oracle会自动应用行级锁: INSERT UPDATE DELETE SELECT … FOR UPDATE SELECT … FOR UPDATE语句允许用户一次锁定多条记录进行更新 使用COMMIT或ROLLBACK语句释放锁
  • 89. 行级锁 3-3SELECT … FOR UPDATE语法:  SELECT … FOR UPDATE [OF columns] [WAIT n | NOWAIT];SQL> SELECT * FROM order_master WHERE vencode=’V002’ FOR UPDATE OF odate, del_date; SQL> UPDATE order_master SET del_date=’28-8月-05’ WHERE vencode=’V002’; SQL> COMMIT;SQL> SELECT * FROM order_master WHERE vencode=’V002’ FOR UPDATE WAIT 5;SQL> SELECT * FROM order_master WHERE vencode=’V002’ FOR UPDATE NOWAIT;
  • 90. 表级锁 3-1Toy_IDNamePriceT001Barbie20T002GI Joe45修改表中的行 更新表 拒绝访问锁定整个表,限制其他用户对表的访问。
  • 91. 表级锁 3-2表级锁类型行共享行排他共享使用命令显示地锁定表,应用表级锁的语法是: LOCK TABLE table_name IN mode MODE;共享行排他排他
  • 92. 表级锁 3-3行共享 (ROW SHARE) – 禁止排他锁定表 行排他(ROW EXCLUSIVE) – 禁止使用排他锁和共享锁 共享锁(SHARE) 锁定表,仅允许其他用户查询表中的行 禁止其他用户插入、更新和删除行 多个用户可以同时在同一个表上应用此锁 共享行排他(SHARE ROW EXCLUSIVE) – 比共享锁更多的限制,禁止使用共享锁及更高的锁 排他(EXCLUSIVE) – 限制最强的表锁,仅允许其他用户查询该表的行。禁止修改和锁定表
  • 93. 死锁当两个事务相互等待对方释放资源时,就会形成死锁 Oracle会自动检测死锁,并通过结束其中的一个事务来解决死锁 右边是一个死锁的例子T1T2lock (D1)时间lock (D2)lock (D2)lock (D1)等待等待……
  • 94. 表分区允许用户将一个表分成多个分区 用户可以执行查询,只访问表中的特定分区 将不同的分区存储在不同的磁盘,提高访问性能和安全性 可以独立地备份和恢复每个分区NameAddressStateCountryJohn Smith34th Rd.CaliforniaUSMicheal ClarkeLK Rd. New YorkUSJack Jones9th RoadCaliforniaUSBob Simmons12th StreetNew YorkUSJim Taylor53rd RoadNew YorkUS更新表只访问 P1P1 分区P2 分区
  • 95. 表分区的类型 9-1分区方法范围分区散列分区列表分区复合分区
  • 96. 表分区的类型 9-2范围分区 以表中的一个列或一组列的值的范围分区 范围分区的语法: PARTITION BY RANGE (column_name) ( PARTITION part1 VALUE LESS THAN(range1), PARTITION part2 VALUE LESS THAN(range2), ... [PARTITION partN VALUE LESS THAN(MAXVALUE)] );
  • 97. 表分区的类型 9-3SQL> CREATE TABLE Sales ( Product_ID varchar2 (5), Sales_Cost number (10) ) PARTITION BY RANGE (Sales_Cost) ( PARTITION P1 VALUES LESS THAN (1000), PARTITION P2 VALUES LESS THAN (2000), PARTITION P3 VALUES LESS THAN (3000) );根据 Sales_Cost 创建分区分区的名称包含销售成本低于1000 的所有产品的值 SQL> CREATE TABLE SALES2 ( PRODUCT_ID VARCHAR2(5), SALES_DATE DATE NOT NULL, SALES_COST NUMBER(10)) PARTITION BY RANGE (SALES_DATE) ( PARTITION P1 VALUES LESS THAN (DATE ‘2003-01-01’), PARTITION P2 VALUES LESS THAN (DATE ‘2004-01-01’), PARTITION P3 VALUES LESS THAN (MAXVALUE) );范围分区示例
  • 98. 表分区的类型 9-4散列分区 允许用户对不具有逻辑范围的数据进行分区 通过在分区键上执行HASH函数决定存储的分区 将数据平均地分布到不同的分区 散列分区语法 PARTITION BY HASH (column_name) PARTITIONS number_of_partitions; 或 PARTITION BY HASH (column_name) ( PARTITION part1 [TABLESPACE tbs1], PARTITION part2 [TABLESPACE tbs2], ... PARTITION partN [TABLESPACE tbsN]);
  • 99. 表分区的类型 9-5散列分区示例SQL> CREATE TABLE Employee ( Employee_ID varchar2 (5), Employee_Name varchar2(20), Department varchar2 (10) ) PARTITION BY HASH (Department) ( Partition D1, Partition D2, Partition D3 ); 在表 Employee上创建分区键 Department分区的名称创建 3 个分区SQL> CREATE TABLE EMPLOYEE ( EMP_ID NUMBER(4), EMP_NAME VARCHAR2(14), EMP_ADDRESS VARCHAR2(15), DEPARTMENT VARCHAR2(10) ) PARTITION BY HASH (DEPARTMENT) PARTITIONS 4; 
  • 100. 表分区的类型 9-6列表分区 允许用户将不相关的数据组织在一起 列表分区的语法:PARTITION BY LIST (column_name) ( PARTITION part1 VALUES (values_list1), PARTITION part2 VALUES (values_list2), ... PARTITION partN VALUES (DEFAULT) );
  • 101. 表分区的类型 9-7SQL> CREATE TABLE Employee ( Emp_ID number (4), Emp_Name varchar2 (14), Emp_Address varchar2 (15) ) PARTITION BY LIST (Emp_Address) ( Partition north values (‘芝加哥'), Partition west values (‘旧金山’, ‘洛杉矶'), Partition south values (‘亚特兰大’, ‘达拉斯’, ‘休斯顿'), Partition east values (‘纽约’, ‘波斯顿') );包含住在芝加哥的职员的记录根据职员住址在表上创建的列表分区分区的名称列表分区示例
  • 102. 表分区的类型 9-8复合分区 范围分区与散列分区或列表分区的组合 复合分区的语法: PARTITION BY RANGE (column_name1) SUBPARTITION BY HASH (column_name2) SUBPARTITIONS number_of_partitions ( PARTITION part1 VALUE LESS THAN(range1), PARTITION part2 VALUE LESS THAN(range2), ... PARTITION partN VALUE LESS THAN(MAXVALUE) );
  • 103. 表分区的类型 9-9SQL> CREATE TABLE SALES ( PRODUCT_ID VARCHAR2 (5), SALES_DATE DATE NOT NULL, SALES_COST NUMBER (10) ) PARTITION BY RANGE (SALES_DATE) SUBPARTITION BY HASH (PRODUCT_ID) SUBPARTITIONS 5 ( PARTITION S1 VALUES LESS THAN (TO_DATE(‘01/4月/2001', 'DD/MON/YYYY')), PARTITION S2 VALUES LESS THAN (TO_DATE(‘01/7月/2001', 'DD/MON/YYYY')), PARTITION S3 VALUES LESS THAN (TO_DATE(‘01/9月/2001', 'DD/MON/YYYY')), PARTITION S4 VALUES LESS THAN (MAXVALUE) );创建的四个范围分区的名称在表的 Sales_Date 列中创建范围分区在表的 Product_ID 列创建散列子分区在每个范围分区中 创建 5 个散列子分区复合分区示例
  • 104. 操纵已分区的表 在已分区的表中插入数据与操作普通表完全相同,Oracle会自动将数据保存到对应的分区 查询、修改和删除分区表时可以显式指定要操作的分区 INSERT INTO SALES3 VALUES (‘P001’, ’02-3月-2001', 2000); INSERT INTO SALES3 VALUES (‘P002’, ’10-5月-2001', 2508); INSERT INTO SALES3 VALUES (‘P003’, ’05-7月-2001', 780); INSERT INTO SALES3 VALUES (‘P004’, ’12-9月-2001', 1080);SELECT * FROM SALES3 PARTITION (P3);DELETE FROM SALES3 PARTITION (P2);
  • 105. 分区维护操作 分区维护操作修改已分区表的分区。 分区维护的类型: 计划事件 - 定期删除最旧的分区 非计划事件 - 解决应用程序或系统问题 分区维护操作有: 添加分区 删除分区 截断分区 合并分区 拆分分区
  • 106. 维护分区 2-1添加分区 – 在最后一个分区之后添加新分区SQL> ALTER TABLE SALES ADD PARTITION P4 VALUES LESS THAN (4000);删除分区 – 删除一个指定的分区,分区的数据也随之删除SQL> ALTER TABLE SALES DROP PARTITION P4;截断分区 – 删除指定分区中的所有记录 SQL> ALTER TABLE SALES TRUNCATE PARTITION P3;
  • 107. 维护分区 2-2合并分区 - 将范围分区或复合分区的两个相邻分区连接起来SQL> ALTER TABLE SALES MERGE PARTITIONS S1, S2 INTO PARTITION S2;拆分分区 - 将一个大分区中的记录拆分到两个分区中SQL> ALTER TABLE SALES SPLIT PARTITION P2 AT (1500) INTO (PARTITION P21, PARTITION P22);
  • 108. 总结锁用于保护多用户环境下被修改的数据 锁分为两种级别,即行级锁和表级锁 表分区允许将一个表划分成几部分,以改善大型应用系统的性能 分区方法包括范围分区、散列分区、复合分区和列表分区 分区维护操作包括添加、删除、截断、合并和拆分分区
  • 109. 第四章数据库对象
  • 110. 回顾锁用于保护多用户环境下被修改的数据 锁分为两种级别,即行级锁和表级锁 表分区允许将一个表划分成几部分,以改善大型应用系统的性能 分区方法包括范围分区、散列分区、复合分区和列表分区 分区维护操作包括添加、删除、截断、合并和拆分分区
  • 111. 目标使用同义词 使用序列 创建视图 创建索引
  • 112. 数据库对象简介 Oracle 数据库对象又称模式对象 数据库对象是逻辑结构的集合,最基本的数据库对象是表 其他数据库对象包括:数据库对象  同义词序列视图索引
  • 113. 同义词 3-1同义词是现有对象的一个别名。 简化SQL语句 隐藏对象的名称和所有者 提供对对象的公共访问 同义词共有两种类型: 同义词私有同义词公有同义词私有同义词只能在其模式内访问,且不能与当前模式的对象同名。公有同义词可被所有的数据库用户访问。
  • 114. 同义词 3-2CREATE SYNONYM emp FOR SCOTT.emp;SCOTT.emp 的别名模式名表名私有同义词公有同义词CREATE PUBLIC SYNONYM emp_syn FOR SCOTT.emp;同义词名称
  • 115. 同义词 3-3创建或替换现有的同义词CREATE OR REPLACE SYNONYM emp_syn FOR SCOTT.emp;替换现有的同义词SQL> DROP SYNONYM emp; SQL> DROP PUBLIC SYNONYM emp_syn; 删除同义词
  • 116. 序列序列是用于生成唯一、连续序号的对象 序列可以是升序的,也可以是降序的 使用CREATE SEQUENCE语句创建序列SQL> CREATE SEQUENCE toys_seq START WITH 10 INCREMENT BY 10 MAXVALUE 2000 MINVALUE 10 NOCYCLE CACHE 10;指定第一个序号从 10 开始指定序号之间的间隔为 10表示序列的最大值为 2000表示序列的最小值为 10在达到最大值后停止生成下一个值指定内存中预先分配的序号数
  • 117. 访问序列通过序列的伪列来访问序列的值 NEXTVAL 返回序列的下一个值 CURRVAL 返回序列的当前值SQL> INSERT INTO toys (toyid, toyname, toyprice) VALUES ( toys_seq.NEXTVAL, ‘TWENTY’, 25); SQL> INSERT INTO toys (toyid, toyname, toyprice) VALUES ( toys_seq.NEXTVAL, ’MAGIC PENCIL’, 75);指定序列的下一个值SQL> SELECT toys_seq.CURRVAL FROM dual;检索序列的当前值
  • 118. 更改和删除序列SQL> ALTER SEQUENCE toys_seq MAXVALUE 5000 CYCLE;使用ALTER SEQUENCE语句修改序列, 不能更改序列的START WITH参数使用DROP SEQUENCE语句删除序列SQL> DROP SEQUENCE toys_seq;
  • 119. 视图视图以经过定制的方式显示来自一个或多个表的数据 视图可以视为“虚拟表”或“存储的查询” 创建视图所依据的表称为“基表” 视图的优点有: 提供了另外一种级别的表安全性 隐藏的数据的复杂性 简化的用户的SQL命令 隔离基表结构的改变 通过重命名列,从另一个角度提供数据
  • 120. 创建视图 3-1studnostudnamestudmarkssubnostudcaste1Rob452Open2James334SC3Jesica405OpenStud_detailsStud_viewstudnostudnamesubno1Rob22James43Jesica5创建视图CREATE VIEW stud_view AS SELECT studno, studname, subno FROM Stud_details;
  • 121. 创建视图 3-2创建视图的语法: CREATE [OR REPLACE] [FORCE] VIEW view_name [(alias[, alias]...)] AS select_statement [WITH CHECK OPTION] [WITH READ ONLY];
  • 122. 创建视图 3-3使用 WITH CHECK OPTION 选项创建视图CREATE OR REPLACE VIEW pause_view AS SELECT * FROM order_master WHERE ostatus = 'p' WITH CHECK OPTION CONSTRAINT chk_pv;使用 ORDER BY 子句创建视图CREATE OR REPLACE VIEW ord_ven AS SELECT * FROM vendor_master ORDER BY venname;创建带有错误的视图CREATE FORCE VIEW ven AS SELECT * FROM venmaster;
  • 123. 联接视图 2-1StudnoStudnameSubmrksSubno1Rob4522James3343Jesica404SubnoSubname2English4Maths5ScienceStud_detailsSub_detailsStudnoStudnameSubmrksSubname1Rob45English2James33Maths3Jesica40MathsCREATE VIEW Stud_sub_view AS SELECT Studno, Studname, Submrks, Subname FROM Stud_details, Sub_Details WHERE Stud_details.Subno=Sub_details.Subno;Stud_sub_view联接视图
  • 124. 联接视图 2-2创建外联接视图CREATE VIEW ven_ord_outj_view AS SELECT vm.vencode, venname, orderno, odate, ostatus FROM vendor_master vm, order_master om WHERE vm.vencode = om.vencode(+);SELECT vm.vencode, venname, orderno, odate, ostatus FROM vendor_master vm LEFT OUTER JOIN order_master om ON vm.vencode = om.vencode;
  • 125. 视图上的DML语句在视图上也可以使用修改数据的DML语句,如INSERT、UPDATE和DELETE 视图上的DML语句有如下限制: 只能修改一个底层的基表 如果修改违反了基表的约束条件,则无法更新视图 如果视图包含连接操作符、DISTINCT 关键字、集合操作符、聚合函数或 GROUP BY 子句,则将无法更新视图 如果视图包含伪列或表达式,则将无法更新视图
  • 126. 键保留表StudnoStudnameSubmrksSubno1Rob4522James3343Jesica404SubnoSubname2English4Maths5ScienceStud_detailsSub_detailsStudnoStudnameSubmarksSubnoSubname1Rob452English2James334Maths3Jesica404Maths键保留表因为 Studno 既是 Stud_details 中的主键, 也是联接结果中的主键 Stud_details 为什么 是键保留表?联接视图
  • 127. 视图中可以使用单行函数、分组函数和表达式 使用DROP VIEW语句删除视图视图中的函数CREATE VIEW item_view AS SELECT itemcode, LOWER(itemdesc) item_desc FROM itemfile; SQL> DROP VIEW toys_view;
  • 128. 索引 3-1索引是与表相关的一个可选结构 用以提高 SQL 语句执行的性能 减少磁盘I/O 使用 CREATE INDEX 语句创建索引 在逻辑上和物理上都独立于表的数据 Oracle 自动维护索引
  • 129. 索引 3-2索引有各种类型,除了标准索引外,还有一些特殊类型的索引:索引的类型基于函数的索引反向键索引位图索引唯一索引组合索引
  • 130. 索引 3-3SQL> CREATE INDEX item_index ON itemfile (itemcode) TABLESPACE index_tbs;创建标准索引重建索引SQL> ALTER INDEX item_index REBUILD; 删除索引SQL> DROP INDEX item_index;
  • 131. 唯一索引SQL> CREATE UNIQUE INDEX item_index ON itemfile (itemcode);唯一索引确保在定义索引的列中没有重复值 Oracle 自动在表的主键列上创建唯一索引 使用CREATE UNIQUE INDEX语句创建唯一索引
  • 132. 组合索引SQL> CREATE INDEX comp_index ON itemfile(p_category, itemrate);组合索引是在表的多个列上创建的索引 索引中列的顺序是任意的 如果 SQL 语句的 WHERE 子句中引用了组合索引的所有列或大多数列,则可以提高检索速度
  • 133. 反向键索引反转索引列键值的每个字节 通常建立在值是连续增长的列上,使数据均匀地分布在整个索引上 创建索引时使用REVERSE关键字反向键索引SQL> CREATE INDEX rev_index ON itemfile (itemcode) REVERSE;SQL> ALTER INDEX rev_index REBUID NOREVERSE;
  • 134. 位图索引SQL> CREATE BITMAP INDEX bit_index ON order_master (orderno);位图索引适合创建在低基数列上 位图索引不直接存储ROWID,而是存储字节位到ROWID的映射 减少响应时间 节省空间占用
  • 135. 索引组织表 2-1SQL> CREATE TABLE ind_org_tab ( vencode NUMBER(4) PRIMARY KEY, venname VARCHAR2(20) ) ORGANIZATION INDEX;索引组织表的数据存储在与其关联的索引中 索引中存储的是行的实际数据,而不是ROWID 基于主键访问数据 CREATE TABLE 命令与 ORGANIZATION INDEX 子句一起用于创建索引组织表
  • 136. 索引组织表 2-2普通表与索引组织表的比较普通表索引组织表ROWID 唯一地标识行主键唯一地标识行隐式的 ROWID 列没有隐式的 ROWID 列基于 ROWID 的访问基于主键的访问顺序扫描返回所有行完全索引扫描返回所有行,并按主键顺序排列支持分区不支持分区
  • 137. 基于函数的索引SQL> CREATE INDEX lowercase_idx ON toys (LOWER(toyname));基于一个或多个列上的函数或表达式创建的索引 表达式中不能出现聚合函数 不能在LOB类型的列上创建 创建时必须具有 QUERY REWRITE 权限SQL> SELECT toyid FROM toys WHERE LOWER(toyname)='doll';
  • 138. 索引中的分区可以将索引存储在不同的分区中 与分区有关的索引有三种类型: 局部分区索引 - 在分区表上创建的索引,在每个表分区上创建独立的索引,索引的分区范围与表一致 全局分区索引 - 在分区表或非分区表上创建的索引,索引单独指定分区的范围,与表的分区范围或是否分区无关 全局非分区索引 - 在分区表上创建的全局普通索引,索引没有被分区
  • 139. 获取索引的信息SQL> SELECT INDEX_NAME, TABLE_NAME, COLUMN_NAME FROM USER_IND_COLUMNS ORDER BY INDEX_NAME, COLUMN_POSITION;与索引有关的数据字典视图有: USER_INDEXES - 用户创建的索引的信息 USER_IND_PARTITIONS - 用户创建的分区索引的信息 USER_IND_COLUMNS - 与索引相关的表列的信息
  • 140. 总结同义词是现有数据库对象的别名 序列用于生成唯一、连续的序号 视图是基于一个或多个表的虚拟表 索引是与表相关的一个可选结构,用于提高 SQL 语句执行的性能 索引类型有标准索引、唯一索引、反向键索引、位图索引和基于函数的索引 索引组织表基于主键访问数据
  • 141. 第五章使用 PL/SQL
  • 142. 回顾同义词是现有数据库对象的别名 序列用于生成唯一、连续的序号 视图是基于一个或多个表的虚拟表 索引是与表相关的一个可选结构,用于提高 SQL 语句执行的性能 索引类型有标准索引、唯一索引、反向键索引、位图索引和基于函数的索引 索引组织表基于主键访问数据
  • 143. 目标理解 PL/SQL 功能和特点 了解数据类型及其用法 理解逻辑比较 理解控制结构 掌握错误处理
  • 144. PL/SQL 简介PL/SQL 是过程语言(Procedural Language)与结构化查询语言(SQL)结合而成的编程语言 PL/SQL 是对 SQL 的扩展 支持多种数据类型,如大对象和集合类型,可使用条件和循环等控制结构 可用于创建存储过程、触发器和程序包,给SQL语句的执行添加程序逻辑 与 Oracle 服务器和 Oracle 工具紧密集成,具备可移植性、灵活性和安全性
  • 145. PL/SQL 的优点 2-1支持 SQL,在 PL/SQL 中可以使用: 数据操纵命令 事务控制命令 游标控制 SQL 函数和 SQL 运算符 支持面向对象编程 (OOP) 可移植性,可运行在任何操作系统和平台上的Oralce 数据库 更佳的性能,PL/SQL 经过编译执行用户将整个语句块发送给 OracleOracleProcedure Begin ProcedureCall SQL Command … End
  • 146. PL/SQL 的优点 2-2 PL/SQLSQL与 SQL 紧密集成,简化数据处理。 支持所有 SQL 数据类型 支持 NULL 值 支持 %TYPE 和 %ROWTYPE 属性类型安全性,可以通过存储过程限制用户对数据的访问
  • 147. PL/SQL 的体系结构PL/SQL 引擎驻留在 Oracle 服务器中 该引擎接受 PL/SQL 块并对其进行编译执行将PL/SQL 块发送给 Oracle 服务器用户执行过程语句引擎将 SQL 语句发送给SQL 语句执行器Oracle 服务器PL/SQL引擎SQL 语句 执行器过程语句 执行器执行 SQL 语句将结果发送给用户
  • 148. PL/SQL 块简介PL/SQL 块是构成 PL/SQL 程序的基本单元 将逻辑上相关的声明和语句组合在一起 PL/SQL 分为三个部分,声明部分、可执行部分和异常处理部分 [DECLARE declarations] BEGIN executable statements [EXCEPTION handlers] END;DECLARE qty_on_hand NUMBER(5); BEGIN SELECT quantity INTO qty_on_hand FROM Products WHERE product = '芭比娃娃' FOR UPDATE OF quantity; IF qty_on_hand > 0 THEN UPDATE Products SET quantity = quantity + 1 WHERE product = '芭比娃娃'; INSERT INTO purchase_record VALUES ('已购买芭比娃娃', SYSDATE); END IF; COMMIT; EXCEPTION /* 异常处理语句 */ WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('出错:'|| SQLERRM); END;声明部分定义变量、游标和自定义异常包含 SQL 和 PL/SQL 语句的可执行部分指定出现错误时需要执行的操作
  • 149. 变量和常量 1-2 PL/SQL 块中可以使用变量和常量 在声明部分声明,使用前必须先声明 声明时必须指定数据类型,每行声明一个标识符 在可执行部分的 SQL 语句和过程语句中使用 声明变量和常量的语法:identifier [CONSTANT] datatype [NOT NULL] [:= | DEFAULT expr]; 给变量赋值有两种方法: 使用赋值语句 := 使用 SELECT INTO 语句
  • 150. 变量和常量 2-2DECLARE icode VARCHAR2(6); p_catg VARCHAR2(20); p_rate NUMBER; c_rate CONSTANT NUMBER := 0.10; BEGIN ... icode := 'i205'; SELECT p_category, itemrate * c_rate INTO p_catg, p_rate FROM itemfile WHERE itemcode = icode; ... END;
  • 151. 数据类型PL/SQL 支持的内置数据类型数据类型LOB类型标量类型属性类型%ROWTYPE数字字符布尔型日期时间BFILEBLOBCLOBNCLOB%TYPE提供某个变量或数据库表列的数据类型提供表示表中一行的记录类型 存储非结构化数据块
  • 152. 数字数据类型指定数值的存储格式数字数据类型NUMBERBINARY_INTEGERPLS_INTEGERNATURALNATURALLNPOSITIVEPOSITIVENDECIMALFLOATINTEGERREALSIGNTYPE存储有符号整数,所需存储空间少于NUMBER类型值存储整数、实数和浮点数存储有符号整数,可使算术计算快速而有效
  • 153. 字符数据类型字符数据类型包括: CHAR VARCHAR2 LONG RAW LONG RAW PL/SQL 的数据类型与 SQL数据类型的比较数据类型SQL类型PL/SQL类型CHAR1..20001..32767LONG1..2GB1..32760LONG RAW1..2GB1..32760RAW1..20001..32767VARCHAR21..40001..32767
  • 154. 日期时间和布尔数据类型日期时间类型 存储日期和时间数据 常用的两种日期时间类型 DATE TIMESTAMP 布尔数据类型 此类别只有一种类型,即BOOLEAN类型 用于存储逻辑值(TRUE、FALSE和NULL) 不能向数据库中插入BOOLEAN数据 不能将列值保存到BOOLEAN变量中 只能对BOOLEAN变量执行逻辑操作
  • 155. LOB 数据类型 2-1用于存储大文本、图像、视频剪辑和声音剪辑等非结构化数据。 LOB 数据类型可存储最大 4GB的数据。 LOB 类型包括: BLOB 将大型二进制对象存储在数据库中 CLOB 将大型字符数据存储在数据库中 NCLOB 存储大型UNICODE字符数据 BFILE 将大型二进制对象存储在操作系统文件中
  • 156. LOB 数据类型 2-2LOB 类型的数据库列仅存储定位符,该定位符指向大型对象的存储位置 DBMS_LOB程序包用于操纵 LOB 数据SET SERVEROUTPUT ON DECLARE clob_var CLOB; amount INTEGER; offset INTEGER; output_var VARCHAR2(100); BEGIN SELECT chapter_text INTO clob_var FROM my_book_text WHERE chapter_id=5; amount := 24; -- 要读取的字符数 offset := 1; -- 起始位置 DBMS_LOB.READ(clob_var,amount,offset,output_var); DBMS_OUTPUT.PUT_LINE(output_var); END; /从表中选择 CLOB 定位符 到 clob_var变量中从CLOB数据中读取24个字符存储到 output_var 变量中显示读到的信息
  • 157. 属性类型用于引用数据库列的数据类型,以及表示表中一行的记录类型 属性类型有两种: %TYPE - 引用变量和数据库列的数据类型 %ROWTYPE - 提供表示表中一行的记录类型 使用属性类型的优点: 不需要知道被引用的表列的具体类型 如果被引用对象的数据类型发生改变,PL/SQL 变量的数据类型也随之改变icode itemfile.itemcode%TYPE; emp_rec scott.emp%ROWTYPE;
  • 158. 布尔表达式的结果为TRUE、FALSE或NULL,通常由逻辑运算符AND、OR和NOT连接 布尔表达式有三种类型: 数字布尔型 字符布尔型 日期布尔型逻辑比较逻辑比较用于比较变量和常量的值,这些表达式称为布尔表达式 布尔表达式由关系运算符与变量或常量组成关系运算符说明=比较两个变量是否相等,如果值相当,则返回 True<>, !=比较两个变量,如果不相等,则返回 True<比较两个变量,检查值 1 是否小于值 2>比较两个变量,检查值 1 是否大于 值 2<=比较两个变量,检查变量 1 是否小于等于变量 2>=比较两个变量,检查变量 1 是否大于等于变量 2
  • 159. 控制结构PL/SQL 支持的流程控制结构: 条件控制 IF 语句 CASE 语句 循环控制 LOOP 循环 WHILE 循环 FOR 循环 顺序控制 GOTO 语句 NULL 语句
  • 160. 条件控制 2-1IF 语句根据条件执行一系列语句,有三种形式:IF-THEN、IF-THEN-ELSE 和 IF-THEN-ELSIFDECLARE icode VARCHAR2(4); irate NUMBER; BEGIN icode := 'i203'; SELECT itemrate INTO irate FROM itemfile WHERE itemcode = icode; IF irate > 200 THEN UPDATE itemfile SET itemrate = itemrate - 200 WHERE itemcode = icode; ELSE UPDATE itemfile SET itemrate = itemrate - 50 WHERE itemcode = icode; END IF; DBMS_OUTPUT.PUT_LINE('itemrate='|| irate); END;
  • 161. 条件控制 2-2BEGIN CASE ‘&grade’ WHEN ’A’ THEN DBMS_OUTPUT.PUT_LINE(’优异’); WHEN ’B’ THEN DBMS_OUTPUT.PUT_LINE (优秀’); WHEN ’C’ THEN DBMS_OUTPUT.PUT_LINE (’良好’); WHEN ’D’ THEN DBMS_OUTPUT.PUT_LINE (’一般’); WHEN ’F’ THEN DBMS_OUTPUT.PUT_LINE (’较差’); ELSE DBMS_OUTPUT.PUT_LINE (’没有此成绩’); END CASE; END;CASE 语句用于根据单个变量或表达式与多个值进行比较 执行 CASE 语句前,先计算选择器的值
  • 162. 循环控制循环控制用于重复执行一系列语句 循环控制语句包括: LOOP、EXIT 和 EXIT WHEN 循环控制的三种类型: LOOP - 无条件循环 WHILE - 根据条件循环 FOR - 循环固定的次数LOOP sequence_of_statements END LOOP;WHILE condition LOOP sequence_of_statements END LOOP;FOR counter IN [REVERSE] value1..value2 LOOP sequence_of_statements END LOOP;
  • 163. 顺序控制顺序控制用于按顺序执行语句 顺序控制语句包括: GOTO 语句 - 无条件地转到标签指定的语句 NULL 语句 - 什么也不做的空语句DECLARE qtyhand itemfile.qty_hand%type; relevel itemfile.re_level%type; BEGIN SELECT qty_hand,re_level INTO qtyhand,relevel FROM itemfile WHERE itemcode = 'i201'; IF qtyhand < relevel THEN GOTO updation; ELSE GOTO quit; END IF; <> UPDATE itemfile SET qty_hand = qty_hand + re_level WHERE itemcode = 'i201'; <> NULL; END;
  • 164. 动态 SQL动态 SQL 是指在PL/SQL程序执行时生成的 SQL 语句 编译程序对动态 SQL 不做处理,而是在程序运行时动态构造语句、对语句进行语法分析并执行 DDL 语句命令和会话控制语句不能在 PL/SQL 中直接使用,但是可以通过动态 SQL 来执行 执行动态 SQL 的语法: EXECUTE IMMEDIATE dynamic_sql_string [INTO define_variable_list] [USING bind_argument_list];DECLARE sql_stmt VARCHAR2(200); emp_id NUMBER(4) := 7566; emp_rec emp%ROWTYPE; BEGIN EXECUTE IMMEDIATE 'CREATE TABLE bonus1 (id NUMBER, amt NUMBER)'; sql_stmt := 'SELECT * FROM emp WHERE empno = :id'; EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id; END;
  • 165. 错误处理 2-1在运行程序时出现的错误叫做异常 发生异常后,语句将停止执行,控制权转移到 PL/SQL 块的异常处理部分 异常有两种类型: 预定义异常 - 当 PL/SQL 程序违反 Oracle 规则或超越系统限制时隐式引发 用户定义异常 - 用户可以在 PL/SQL 块的声明部分定义异常,自定义的异常通过 RAISE 语句显式引发
  • 166. 错误处理 2-2处理预定义异常 处理用户定义异常DECLARE ordernum VARCHAR2(5); BEGIN SELECT orderno INTO ordernum FROM order_master; EXCEPTION WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE ('返回多行'); END;DECLARE invalidCATEGORY EXCEPTION; category VARCHAR2(10); BEGIN category := '&Category'; IF category NOT IN ('附件','顶盖','备件') THEN RAISE invalidCATEGORY; ELSE DBMS_OUTPUT.PUT_LINE('您输入的类别是'|| category); END IF; EXCEPTION WHEN invalidCATEGORY THEN DBMS_OUTPUT.PUT_LINE('无法识别该类别'); END;
  • 167. RAISE_APPLICATION_ERROR 过程 用于创建用户定义的错误信息 可以在可执行部分和异常处理部分使用 错误编号必须介于 –20000 和 –20999 之间 错误消息的长度可长达 2048 个字节 引发应用程序错误的语法: RAISE_APPLICATION_ERROR(error_number, error_message);引发应用程序错误DECLARE rate itemfile.itemrate%TYPE; rate_exception EXCEPTION; BEGIN SELECT NVL(itemrate,0) INTO rate FROM itemfile WHERE itemcode = 'i207'; IF rate = 0 THEN RAISE rate_exception; ELSE DBMS_OUTPUT.PUT_LINE('项费率为:' || rate); END IF; EXCEPTION WHEN rate_exception THEN RAISE_APPLICATION_ERROR(-20001, '未指定项费率'); END;
  • 168. 总结PL/SQL 是一种可移植的高性能事务处理语言 PL/SQL 引擎驻留在 Oracle 服务器中 PL/SQL 块由声明部分、可执行部分和异常处理部分组成 PL/SQL 数据类型包括标量数据类型、LOB 数据类型和属性类型 控制结构包括条件控制、循环控制和顺序控制 PL/SQL 支持动态 SQL 运行时出现的错误叫做异常 异常可以分为预定义异常和用户定义的异常
  • 169. 第六章 游标管理
  • 170. 回顾PL/SQL 是一种可移植的高性能事务处理语言 PL/SQL 引擎驻留在 Oracle 服务器中 PL/SQL 块由声明部分、可执行部分和异常处理部分组成 PL/SQL 支持的数据类型包括标量数据类型、LOB 数据类型和属性类型 控制结构包括条件控制、循环控制和顺序控制 PL/SQL 支持动态 SQL 运行时出现的错误叫做异常 异常可以分为预定义异常和用户定义的异常
  • 171. 目标掌握游标管理技巧
  • 172. 游标简介 2-1Oracle 服务器执行 PL/SQL 程序内存单元保存到游标中一次处理一行检索行提取行
  • 173. 游标简介 2-2逐行处理查询结果,以编程的方式访问数据 游标的类型:隐式游标REF 游标显式游标在 PL/SQL 程序中执行DML SQL 语句时自动创建隐式游标。 显式游标用于处理返回多行的查询。REF 游标用于处理运行时才能确定的动态 SQL 查询的结果游标类型
  • 174. 隐式游标 4-1在PL/SQL中使用DML语句时自动创建隐式游标 隐式游标自动声明、打开和关闭,其名为 SQL 通过检查隐式游标的属性可以获得最近执行的DML 语句的信息 隐式游标的属性有: %FOUND – SQL 语句影响了一行或多行时为 TRUE %NOTFOUND – SQL 语句没有影响任何行时为TRUE %ROWCOUNT – SQL 语句影响的行数 %ISOPEN - 游标是否打开,始终为FALSE
  • 175. 隐式游标 4-2SQL> SET SERVEROUTPUT ON SQL> BEGIN UPDATE toys SET toyprice=270 WHERE toyid= 'P005'; IF SQL%FOUND THEN DBMS_OUTPUT.PUT_LINE(‘表已更新'); END IF; END; /只有在 DML 语句影响一行 或多行时,才返回 True
  • 176. 隐式游标 4-3SQL> SET SERVEROUTPUT ON SQL> DECLARE v_TOYID TOYS.ID%type := '&TOYID'; v_TOYNAME TOYS.NAME%Type := '&TOYNAME'; BEGIN UPDATE TOYS SET NAME = v_TOYNAME WHERE toyid=v_TOYID; IF SQL%NOTFOUND THEN DBMS_OUTPUT.PUT_LINE('编号未找到。'); ELSE DBMS_OUTPUT.PUT_LINE(‘表已更新'); END IF; END; /如果 DML 语句不影响任何行,则返回 True
  • 177. 隐式游标 4-4SQL> SET SERVEROUTPUT ON SQL> BEGIN UPDATE vendor_master SET venname= 'Rob Mathew' WHERE vencode='V004'; DBMS_OUTPUT.PUT_LINE (SQL%ROWCOUNT); END; /返回 DML 语句影响的行数
  • 178. SELECT INTO 语句 2-1SQL> SET SERVEROUTPUT ON SQL> DECLARE empid VARCHAR2(10); desig VARCHAR2(10); BEGIN empid:= '&Employeeid'; SELECT designation INTO desig FROM employee WHERE empno=empid; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('职员未找到'); END; /如果没有与SELECT INTO语句中的条件匹配的行,将引发NO_DATA_FOUND异常
  • 179. SELECT INTO 语句 2-2SQL> SET SERVEROUTPUT ON SQL> DECLARE empid VARCHAR2(10); BEGIN SELECT empno INTO empid FROM employee; EXCEPTION WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE('该查询提取多行'); END; /如果 SELECT INTO 语句返回多个值, 将引发TOO_MANY_ROWS异常
  • 180. 显式游标 2-1显式游标在 PL/SQL 块的声明部分定义查询,该查询可以返回多行 显式游标的操作过程: 数据库打开游标30George344Roger245James1Stud_mrksStud_namestud_no提取行变量 关闭游标
  • 181. 显式游标 2-2SQL>SET SERVER OUTPUT ON SQL>DECLARE my_toy_price toys.toyprice%TYPE; CURSOR toy_cur IS SELECT toyprice FROM toys WHERE toyprice<250; BEGIN OPEN toy_cur; LOOP FETCH toy_cur INTO my_toy_price; EXIT WHEN toy_cur%NOTFOUND; DBMS_OUTPUT.PUT_LINE ('TOYPRICE=:玩具单价=:'||my_toy_price); END LOOP; CLOSE toy_cur; END;声明游标打开游标提取行关闭游标
  • 182. 带参数的显式游标声明显式游标时可以带参数以提高灵活性 声明带参数的显式游标的语法如下: CURSOR ( ) IS select_statement;SQL> SET SERVEROUTPUT ON SQL> DECLARE desig VARCHAR2(20); emp_code VARCHAR2(5); empnm VARCHAR2(20); CURSOR emp_cur(desigparam VARCHAR2) IS SELECT empno, ename FROM employee WHERE designation=desig; BEGIN desig:= '&desig'; OPEN emp_cur(desig); LOOP FETCH emp_cur INTO emp_code,empnm; EXIT WHEN emp_cur%NOTFOUND; DBMS_OUTPUT.PUT_LINE(emp_code||' '||empnm); END LOOP; CLOSE emp_cur; END;
  • 183. 使用显式游标更新行 2-1允许使用游标删除或更新活动集中的行 声明游标时必须使用 SELECT … FOR UPDATE语句 CURSOR IS SELECT statement FOR UPDATE; UPDATE SET WHERE CURRENT OF 更新的语法DELETE FROM WHERE CURRENT OF 删除的语法
  • 184. 使用显式游标更新行 2-2SQL> SET SERVEROUTPUT ON SQL> DECLARE new_price NUMBER; CURSOR cur_toy IS SELECT toyprice FROM toys WHERE toyprice<100 FOR UPDATE OF toyprice; BEGIN OPEN cur_toy; LOOP FETCH cur_toy INTO new_price; EXIT WHEN cur_toy%NOTFOUND; UPDATE toys SET toyprice = 1.1*new_price WHERE CURRENT OF cur_toy; END LOOP; CLOSE cur_toy; COMMIT; END;
  • 185. 循环游标 2-1循环游标用于简化游标处理代码 当用户需要从游标中提取所有记录时使用 循环游标的语法如下: FOR IN LOOP END LOOP;
  • 186. 循环游标 2-2SQL> SET SERVER OUTPUT ON SQL> DECLARE CURSOR mytoy_cur IS SELECT toyid, toyname, toyprice FROM toys; BEGIN FOR toy_rec IN mytoy_cur LOOP DBMS_OUTPUT.PUT_LINE( ‘玩具编号:'||' ' ||toy_rec.toyid||' ' ||‘玩具名称:'||' '||toy_rec.toyname||' ' ||‘玩具单价:'||' '||toy_rec.toyprice); END LOOP; END;
  • 187. REF 游标和游标变量 3-1REF 游标和游标变量用于处理运行时动态执行的 SQL 查询 创建游标变量需要两个步骤: 声明 REF 游标类型 声明 REF 游标类型的变量 用于声明 REF 游标类型的语法为: TYPE IS REF CURSOR [RETURN ];
  • 188. REF 游标和游标变量 3-2TYPE my_curtype IS REF CURSOR RETURN stud_det%ROWTYPE; order_cur my_curtype; 声明强类型的 REF 游标打开游标变量的语法如下: OPEN cursor_name FOR select_statement;TYPE my_ctype IS REF CURSOR; stud_cur my_ctype;声明弱类型的 REF 游标
  • 189. REF 游标和游标变量 3-3SQL> DECLARE TYPE toys_curtype IS REF CURSOR RETURN toys%ROWTYPE; toys_curvar toys_curtype; toys_rec toys%ROWTYPE; BEGIN OPEN toys_curvar FOR SELECT * FROM toys; FETCH toys_curvar INTO toys_rec; ... CLOSE toys_curvar; END;声明REF游标类型声明游标变量
  • 190. 游标变量的优点和限制游标变量的功能强大,可以简化数据处理。 游标变量的优点有: 可从不同的 SELECT 语句中提取结果集 可以作为过程的参数进行传递 可以引用游标的所有属性 可以进行赋值运算 使用游标变量的限制: 不能在程序包中声明游标变量 FOR UPDATE子句不能与游标变量一起使用 不能使用比较运算符
  • 191. 使用游标变量执行动态 SQL可以使用游标变量执行动态构造的 SQL 语句 打开执行动态 SQL 的游标变量的语如下: OPEN cursor_name FOR dynamic_sqlstring [USING bind_argument_list]; DECLARE r_emp emp%ROWTYPE; TYPE c_type IS REF CURSOR; cur c_type; p_salary NUMBER; BEGIN p_salary := 2500; OPEN cur FOR 'select * from emp where sal>:1 order by sal desc' USING p_salary; DBMS_OUTPUT.PUT_LINE('薪水大于'|| p_salary ||'的员工有:'); LOOP FETCH cur INTO r_emp; EXIT WHEN cur%NOTFOUND; DBMS_OUTPUT.PUT_LINE('编号:'|| r_emp.empno || ' 姓名:' || r_emp.ename|| ' 薪水:' || r_emp.sal ); END LOOP; CLOSE cur; END;
  • 192. 总结游标用于处理查询结果集中的数据 游标类型有:隐式游标、显式游标和 REF 游标 隐式游标由 PL/SQL 自动定义、打开和关闭 显式游标用于处理返回多行的查询 显式游标可以删除和更新活动集中的行 要处理结果集中所有记录时,可使用循环游标 在声明 REF 游标时,不需要将 SELECT 语句与 其关联
  • 193. 第七章子程序和程序包
  • 194. 回顾游标用于处理查询结果集中的数据 游标类型有:隐式游标、显式游标和 REF 游标 隐式游标由 PL/SQL 自动定义、打开和关闭 显式游标用于处理返回多行的查询 显式游标可以删除和更新活动集中的行 要处理结果集中所有记录时,可使用循环游标 在声明 REF 游标时,不需要将 SELECT 语句与 其关联
  • 195. 目标创建和使用子程序 创建和使用程序包
  • 196. 子程序 2-1命名的 PL/SQL 块,编译并存储在数据库中。 子程序的各个部分: 声明部分 可执行部分 异常处理部分(可选) 子程序的分类: 过程 - 执行某些操作 函数 - 执行操作并返回值
  • 197. 子程序 2-2子程序的优点: 模块化 将程序分解为逻辑模块 可重用性 可以被任意数目的程序调用 可维护性 简化维护操作 安全性 通过设置权限,使数据更安全
  • 198. 过程 8-1过程是用于完成特定任务的子程序 例如:前往售票厅询问关于车票的信息 排队等候 在柜台购买车票
  • 199. 过程 8-2创建过程的语法: CREATE [OR REPLACE] PROCEDURE [()] IS|AS BEGIN [EXCEPTION ] END;创建过程,可指定运行过程需传递的参数处理异常 包括在过程中要执行的语句
  • 200. 过程 8-3CREATE OR REPLACE PROCEDURE find_emp(emp_no NUMBER) AS empname VARCHAR2(20); BEGIN SELECT ename INTO empname FROM EMP WHERE empno = emp_no; DBMS_OUTPUT.PUT_LINE('雇员姓名是 '|| empname); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE ('雇员编号未找到'); END find_emp; /
  • 201. 过程 8-4过程参数的三种模式: IN 用于接受调用程序的值 默认的参数模式 OUT 用于向调用程序返回值 IN OUT 用于接受调用程序的值,并向调用程序返回更新的值
  • 202. 过程 8-5SQL> CREATE OR REPLACE PROCEDURE itemdesc(item_code IN VARCHAR2) IS v_itemdesc VARCHAR2(5); BEGIN SELECT itemdesc INTO v_itemdesc FROM itemfile WHERE itemcode = item_code; DBMS_OUTPUT.PUT_LINE(item_code|| '项目的说明为'||v_itemdesc); END; / SQL> SET SERVEROUTPUT ON SQL> EXECUTE itemdesc('i201');执行过程的语法: EXECUTE procedure_name(parameters_list);
  • 203. 过程 8-6SQL> CREATE OR REPLACE PROCEDURE test( value1 IN VARCHAR2, value2 OUT NUMBER ) IS identity NUMBER; BEGIN SELECT ITEMRATE INTO identity FROM itemFile WHERE itemcode = value1; IF identity < 200 THEN value2:=100; END IF; END;DECLARE value1 VARCHAR2(5) := 'i202'; value2 NUMBER; BEGIN test (value1, value2); DBMS_OUTPUT.PUT_LINE('value2 的值为' || TO_CHAR(value2)); END; /
  • 204. 过程 8-7CREATE OR REPLACE PROCEDURE swap(p1 IN OUT NUMBER, p2 IN OUT NUMBER) IS v_temp NUMBER; BEGIN v_temp := p1; p1 := p2; p2 := v_temp; END; /SQL> SET SERVEROUT ON SQL> DECLARE num1 NUMBER := 100; num2 NUMBER := 200; BEGIN swap(num1, num2); DBMS_OUTPUT.PUT_LINE('num1 = ' || num1); DBMS_OUTPUT.PUT_LINE('num2 = ' || num2); END; /
  • 205. 过程 8-8将过程的执行权限授予其他用户: 删除过程:SQL> GRANT EXECUTE ON find_emp TO MARTIN; SQL> GRANT EXECUTE ON swap TO PUBLIC;SQL> DROP PROCEDURE find_emp;
  • 206. 函数 4-1函数是可以返回值的命名的 PL/SQL 子程序。 创建函数的语法: CREATE [OR REPLACE] FUNCTION [(param1,param2)] RETURN IS|AS [local declarations] BEGIN Executable Statements; RETURN result; EXCEPTION Exception handlers; END;
  • 207. 函数 4-2定义函数的限制: 函数只能接受 IN 参数,而不能接受 IN OUT 或 OUT 参数 形参不能是 PL/SQL 类型 函数的返回类型也必须是数据库类型 访问函数的两种方式: 使用 PL/SQL 块 使用 SQL 语句
  • 208. 函数 4-3创建函数: 从 SQL 语句调用函数: CREATE OR REPLACE FUNCTION fun_hello RETURN VARCHAR2 IS BEGIN RETURN '朋友,您好'; END; /SQL> SELECT fun_hello FROM DUAL;
  • 209. 函数 4-4CREATE OR REPLACE FUNCTION item_price_range (price NUMBER) RETURN VARCHAR2 AS min_price NUMBER; max_price NUMBER; BEGIN SELECT MAX(ITEMRATE), MIN(ITEMRATE) INTO max_price, min_price FROM itemfile; IF price >= min_price AND price <= max_price THEN RETURN '输入的单价介于最低价与最高价之间'; ELSE RETURN '超出范围'; END IF; END; /DECLARE P NUMBER := 300; MSG VARCHAR2(200); BEGIN MSG := item_price_range(300); DBMS_OUTPUT.PUT_LINE(MSG); END; /
  • 210. 过程和函数的比较过 程函 数作为 PL/SQL 语句执行作为表达式的一部分调用在规格说明中不包含 RETURN 子句必须在规格说明中包含 RETURN 子句不返回任何值必须返回单个值可以包含 RETURN 语句,但是与函数不同,它不能用于返回值必须包含至少一条 RETURN 语句
  • 211. 自主事务处理 2-1自主事务处理 主事务处理启动独立事务处理 然后主事务处理被暂停 自主事务处理子程序内的 SQL 操作 然后终止自主事务处理 恢复主事务处理 PRAGMA AUTONOMOUS_TRANSACTION 用于标记子程序为自主事务处理
  • 212. 自主事务处理 2-2自主事务处理的特征: 与主事务处理的状态无关 提交或回滚操作不影响主事务处理 自主事务处理的结果对其他事务是可见的 能够启动其他自主事务处理
  • 213. 程序包程序包是对相关过程、函数、变量、游标和异常等对象的封装 程序包由规范和主体两部分组成声明程序包中公共对象。包括类型、变量、常量、异常、游标规范和子程序规范等声明程序包私有对象和实现在包规范中声明的子程序和游标程序包规范主体
  • 214. 创建程序包 2-1程序包规范 CREATE [OR REPLACE] PACKAGE package_name IS|AS [Public item declarations] [Subprogram specification] END [package_name];程序包主体 CREATE [OR REPLACE] PACKAGE BODY package_name IS|AS [Private item declarations] [Subprogram bodies] [BEGIN Initialization] END [package_name];
  • 215. 创建程序包 2-2CREATE OR REPLACE PACKAGE pack_me IS PROCEDURE order_proc (orno VARCHAR2); FUNCTION order_fun(ornos VARCHAR2) RETURN VARCHAR2; END pack_me; /CREATE OR REPLACE PACKAGE BODY pack_me AS PROCEDURE order_proc (orno VARCHAR2) IS stat CHAR(1); BEGIN SELECT ostatus INTO stat FROM order_master WHERE orderno = orno; …… END order_proc; FUNCTION order_fun(ornos VARCHAR2) RETURN VARCHAR2 IS icode VARCHAR2(5); ocode VARCHAR2(5); BEGIN …… END order_fun; END pack_me; /
  • 216. 程序包的优点模块化 更轻松的应用程序设计 信息隐藏 新增功能 性能更佳
  • 217. 程序包中的游标 2-1游标的定义分为游标规范和游标主体两部分 在包规范中声明游标规范时必须使用 RETURN 子句指定游标的返回类型 RETURN子句指定的数据类型可以是: 用 %ROWTYPE 属性引用表定义的记录类型 程序员定义的记录类型
  • 218. 程序包中的游标 2-2SQL> CREATE OR REPLACE PACKAGE cur_pack IS CURSOR ord_cur(vcode VARCHAR2) RETURN order_master%ROWTYPE; PROCEDURE ord_pro(vcode VARCHAR2); END cur_pack; /SQL> CREATE OR REPLACE PACKAGE BODY cur_pack AS CURSOR ord_cur(vcode VARCHAR2) RETURN order_master%ROWTYPE IS SELECT * FROM order_master WHERE VENCODE=vcode; PROCEDURE ord_pro(vcode VARCHAR2) IS or_rec order_master%ROWTYPE; BEGIN OPEN ord_cur(vcode); LOOP FETCH ord_cur INTO or_rec; EXIT WHEN ord_cur%NOTFOUND; DBMS_OUTPUT.PUT_LIne(’返回的值为' || or_rec.orderno); END LOOP; END ord_pro; END cur_pack; /
  • 219. 有关子程序和程序包的信息USER_OBJECTS 视图包含用户创建的子程序和程序包的信息 USER_SOURCE 视图存储子程序和程序包的源代码SELECT object_name, object_type FROM USER_OBJECTS WHERE object_type IN ('PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY');SELECT line, text FROM USER_SOURCE WHERE NAME='TEST';
  • 220. 总结子程序是命名的 PL/SQL 块,可带参数并可在需要时随时调用 有两种类型的PL/SQL子程序,即过程和函数 过程用户执行特定的任务,函数用于执行任务并返回值 程序包是对相关类型、变量、常量、游标、异常、过程和函数等对象的封装 程序包由两部分组成,即包规范和包主体 使用程序包的优点是:模块化、更轻松的程序设计、信息隐藏、新增功能以及性能更佳
  • 221. 第八章触发器和内置程序包
  • 222. 回顾子程序是命名的 PL/SQL 块,存储在数据库中,可带参数并可在需要时随时调用 有两种类型的PL/SQL子程序,即过程和函数 过程用户执行特定的任务,函数用于执行任务并返回值 程序包是对相关类型、变量、常量、游标、异常、过程和函数等对象的封装 程序包由两部分组成,即包规范和包主体 使用程序包的优点是:模块化、更轻松的程序设计、信息隐藏、新增功能以及性能更佳
  • 223. 目标理解和应用触发器 了解内置程序包
  • 224. 触发器触发器是当特定事件出现时自动执行的存储过程 特定事件可以是执行更新的DML语句和DDL语句 触发器不能被显式调用 触发器的功能: 自动生成数据 自定义复杂的安全权限 提供审计和日志记录 启用复杂的业务逻辑
  • 225. 创建触发器的语法CREATE [OR REPLACE] TRIGGER trigger_name AFTER | BEFORE | INSTEAD OF [INSERT] [[OR] UPDATE [OF column_list]] [[OR] DELETE] ON table_or_view_name [REFERENCING {OLD [AS] old / NEW [AS] new}] [FOR EACH ROW] [WHEN (condition)] pl/sql_block;
  • 226. 触发器的组成部分 3-1触发器由三部分组成: 触发器语句(事件) 定义激活触发器的 DML 事件和 DDL 事件 触发器限制 执行触发器的条件,该条件必须为真才能激活触发器 触发器操作(主体) 包含一些 SQL 语句和代码,它们在发出了触发器语句且触发限制的值为真时运行
  • 227. 触发器的组成部分 3-2 SQL> CREATE OR REPLACE TRIGGER trig_sal AFTER UPDATE OF empsal ON salary_records …触发器语句为 salary_records 表创建 trig-sal 触发器在更新 emp_sal 列之后激活触发器触发器限制SQL> … FOR EACH ROW WHEN (NEW.empsal>OLD.empsal) DECLARE Sal_diff NUMBER; … 只有在WHEN子句中的条件得到满足时,才激活trig_sal 触发器触发器操作SQL> … BEGIN sal_diff:=:NEW.empsal-:OLD.empsal; DBMS_OUTPUT.PUT_LINE(‘工资差额:’sal_diff); END;如果WHEN子句中的条件得到满足,将执行BEGIN 块中的代码
  • 228. 触发器的组成部分 3-3Oracle 数据库更新表保存更新激活触发器AFTER 触发器的工作原理BEFORE 触发器的工作原理更新表激活触发器保存更新Oracle 数据库
  • 229. 创建触发器CREATE OR REPLACE TRIGGER aiu_itemfile AFTER INSERT ON itemfile FOR EACH ROW BEGIN IF (:NEW.qty_hand = 0) THEN DBMS_OUTPUT.PUT_LINE('警告:已插入记录,但数量为零'); ELSE DBMS_OUTPUT.PUT_LINE(‘已插入记录'); END IF; END; /
  • 230. 触发器类型 6-1触发器的类型有:触发器类型模式(DDL) 触发器DML 触发器数据库级 触发器语句级触发器行级触发器INSTEAD OF触发器
  • 231. 触发器类型 6-2DDL 触发器 数据库级触发器 DML 触发器 语句级触发器 行级触发器 INSTEAD OF 触发器在模式中执行 DDL 语句时执行在发生打开、关闭、登录和退出数据库等系统事件时执行在对表或视图执行DML语句时执行无论受影响的行数是多少,都只执行一次对DML语句修改的每个行执行一次用于用户不能直接使用 DML 语句修改的视图
  • 232. 触发器类型 6-3行级触发器SQL> CREATE TABLE TEST_TRG (ID NUMBER, NAME VARCHAR2(20)); SQL> CREATE SEQUENCE SEQ_TEST; SQL> CREATE OR REPLACE TRIGGER BI_TEST_TRG BEFORE INSERT OR UPDATE OF ID ON TEST_TRG FOR EACH ROW BEGIN IF INSERTING THEN SELECT SEQ_TEST.NEXTVAL INTO :NEW.ID FROM DUAL; ELSE RAISE_APPLICATION_ERROR(-20020, '不允许更新ID值!'); END IF; END; /
  • 233. 触发器类型 6-4SQL> CREATE OR REPLACE TRIGGER trgdemo AFTER INSERT OR UPDATE OR DELETE ON order_master BEGIN IF UPDATING THEN DBMS_OUTPUT.PUT_LINE(‘已更新 ORDER_MASTER 中的数据'); ELSIF DELETING THEN DBMS_OUTPUT.PUT_LINE(‘已删除 ORDER_MASTER 中的数据'); ELSIF INSERTING THEN DBMS_OUTPUT.PUT_LINE(‘已在 ORDER_MASTER 中插入数据'); END IF; END; /语句级触发器
  • 234. 触发器类型 6-5SQL> CREATE OR REPLACE TRIGGER upd_ord_view INSTEAD OF UPDATE ON ord_view FOR EACH ROW BEGIN UPDATE order_master SET vencode=:NEW.vencode WHERE orderno = :NEW.orderno; DBMS_OUTPUT.PUT_LINE(‘已激活触发器'); END; /INSTEAD OF 触发器
  • 235. 触发器类型 6-6SQL> CREATE TABLE dropped_obj ( obj_name VARCHAR2(30), obj_type VARCHAR2(20), drop_date DATE); SQL> CREATE OR REPLACE TRIGGER log_drop_obj AFTER DROP ON SCHEMA BEGIN INSERT INTO dropped_obj VALUES( ORA_DICT_OBJ_NAME, ORA_DICT_OBJ_TYPE, SYSDATE); END; /模式触发器
  • 236. 启用和禁用触发器 删除触发器启用、禁用和删除触发器SQL> ALTER TRIGGER aiu_itemfile DISABLE;SQL> ALTER TRIGGER aiu_itemfile ENABLE;SQL> DROP TRIGGER aiu_itemfile;
  • 237. 查看有关触发器的信息SQL> SELECT TRIGGER_NAME FROM USER_TRIGGERS WHERE TABLE_NAME='EMP'; SQL> SELECT TRIGGER_TYPE, TRIGGERING_EVENT, WHEN_CLAUSE FROM USER_TRIGGERS WHERE TRIGGER_NAME = 'BIU_EMP_DEPTNO';USER_TRIGGERS 数据字典视图包含有关触发器的信息
  • 238. 内置程序包 8-1扩展数据库的功能 为 PL/SQL 提供对 SQL 功能的访问 用户 SYS 拥有所有程序包 是公有同义词 可以由任何用户访问
  • 239. 内置程序包 8-2一些内置程序包:程序包名称说明STANDARD和DBMS_STANDARD定义和扩展PL/SQL语言环境DBMS_LOB提供对 LOB数据类型进行操作的功能DBMS_OUTPUT处理PL/SQL块和子程序输出调试信息DBMS_RANDOM提供随机数生成器DBMS_SQL允许用户使用动态 SQLDBMS_XMLDOM用DOM模型读写XML类型的数据DBMS_XMLPARSERXML解析,处理XML文档内容和结构DBMS_XMLQUERY提供将数据转换为 XML 类型的功能DBMS_XSLPROCESSOR提供XSLT功能,转换XML文档UTL_FILE用 PL/SQL 程序来读写操作系统文本文件
  • 240. 内置程序包 8-3SQL> SET SERVEROUTPUT ON SQL> BEGIN DBMS_OUTPUT.PUT_LINE('打印三角形'); FOR i IN 1..9 LOOP FOR j IN 1..i LOOP DBMS_OUTPUT.PUT('*'); END LOOP for_j; DBMS_OUTPUT.NEW_LINE; END LOOP for_i; END; /DBMS_OUTPUT包显示 PL/SQL 块和子程序的调试信息。
  • 241. 内置程序包 8-4DBMS_LOB 包提供用于处理大型对象的过程和函数 DBMS_XMLQUERY 包用于将查询结果转换为 XML 格式
  • 242. 内置程序包 8-5SQL> DECLARE result CLOB; xmlstr VARCHAR2(32767); line VARCHAR2(2000); line_no INTEGER := 1; BEGIN result := DBMS_XMLQuery.getXml('SELECT empno, ename FROM employee'); xmlstr := DBMS_LOB.SUBSTR(result,32767); LOOP EXIT WHEN xmlstr IS NULL; line := SUBSTR(xmlstr,1,INSTR(xmlstr,CHR(10))-1); DBMS_OUTPUT.PUT_LINE(line_no || ':' || line); xmlstr := SUBSTR(xmlstr,INSTR(xmlstr,CHR(10))+1); line_no := line_no + 1; END LOOP; END; /
  • 243. 内置程序包 8-6SQL> SET SERVEROUTPUT ON SQL> DECLARE l_num NUMBER; counter NUMBER; BEGIN counter:=1; WHILE counter <= 10 LOOP l_num := DBMS_RANDOM.RANDOM; DBMS_OUTPUT.PUT_LINE(l_num); counter:=counter+1; END LOOP; END; /DBMS_RANDOM 包可用来生成随机整数
  • 244. 内置程序包 8-7SQL> CREATE DIRECTORY TEST_DIR AS 'C:\DEVELOP'; SQL> GRANT READ, WRITE ON DIRECTORY TEST_DIR TO SCOTT;UTL_FILE 包用于读写操作系统文本文件 操作文件的一般过程是打开、读或写、关闭 UTL_FILE 包指定文件路径依赖于 DIRECTORY 对象
  • 245. 内置程序包 8-8SQL> SET SERVEROUTPUT ON SQL> DECLARE input_file UTL_FILE.FILE_TYPE; input_buffer VARCHAR2(4000); BEGIN input_file := UTL_FILE.FOPEN( 'TEST_DIR', 'employees.xml', 'r'); LOOP UTL_FILE.GET_LINE(input_file,input_buffer); DBMS_OUTPUT.PUT_LINE(input_buffer); END LOOP; UTL_FILE.FCLOSE(input_file); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('------------------'); END; /
  • 246. 总结触发器是当特定事件出现时自动执行的存储过程 触发器分为 DML 触发器、DDL 触发器和数据库级触发器三种类型 DML 触发器的三种类型包括行级触发器、语句级触发器和 INSTEAD OF 触发器 一些常用的内置程序包: DBMS_OUTPUT 包输出 PL/SQL 程序的调试信息 DBMS_LOB 包提供操作 LOB 数据的子程序 DBMS_XMLQUERY 将查询结果转换为 XML 格式 DBMS_RANDOM 提供随机数生成器 UTL_FILE 用于读写操作系统文本文件
  • 247. 第九章备份与恢复简介
  • 248. 回顾触发器是当特定事件出现时自动执行的存储过程 触发器分为 DML 触发器、DDL 触发器和数据库级触发器三种类型 DML 触发器的三种类型包括行级触发器、语句级触发器和 INSTEAD OF 触发器 一些常用的内置程序包: DBMS_OUTPUT 包输出 PL/SQL 程序的调试信息 DBMS_LOB 包提供操作 LOB 数据的子程序 DBMS_XMLQUERY 将查询结果转换为 XML 格式 DBMS_RANDOM 提供随机数生成器 UTL_FILE 用于读写操作系统文本文件
  • 249. 目标 了解数据库备份与恢复的基础知识 理解在 Oracle 环境中可能发生的故障类型 掌握导出和导入实用程序 了解数据库归档模式
  • 250. 备份与恢复简介 备份是数据库中数据的副本,它可以保护数据在出现意外损失时最大限度的恢复 Oracle数据库的备份包括以下两种类型: 备份物理备份逻辑备份物理备份是对数据库的操作系统物理文件 (如数据文件、控制文件和日志文件等)的备份逻辑备份是对数据库逻辑组件 (如表、视图和存储过程等数据库对象)的备份
  • 251. 故障类型故障类型 语句故障介质故障实例故障用户进程故障导致数据库操作中止的故障包括四种类型: 在执行 SQL 语句过程中发生的逻辑故障可导致语句故障。如果用户编写的 SQL 语句无效,就会发生逻辑故障当用户程序出错而无法访问数据库时发生用户进程故障。导致用户进程故障的原因是异常断开连接或异常终止进程 当 Oracle 的数据库实例由于硬件或软件问题而无法 继续运行时,就会发生实例故障在数据库无法正确读取或写入某个数据库文件时, 会发生介质故障
  • 252. 导出和导入实用程序 4-1导出和导入实用程序用于实施数据库的逻辑备份和恢复 导出实用程序将数据库中的对象定义和数据备份到一个操作系统二进制文件中 导入实用程序读取二进制导出文件并将对象和数据载入数据库中
  • 253. 导出和导入实用程序 4-2导出和导入实用程序的特点有: 可以按时间保存表结构和数据 允许导出指定的表,并重新导入到新的数据库中 可以把数据库迁移到另外一台异构服务器上 在两个不同版本的Oracle数据库之间传输数据 在联机状态下进行备份和恢复 可以重新组织表的存储结构,减少链接及磁盘碎片
  • 254. 导出和导入实用程序 4-3使用以下三种方法调用导出和导入实用程序:调用导出和导入程序 命令行参数 参数文件 交互提示符 在命令行指定执行程序的参数和参数值。以交互的方式提示用户逐个输入参数的值。 允许用户将运行参数和参数值存储在参数文件中, 以便重复使用参数
  • 255. 导出和导入实用程序 4-4导出和导入数据库对象的四种模式是:导出导入模式完全数据库 表空间表用户导出和导入整个数据库中的所有对象导出和导入一个或多个指定的表或表分区导出和导入一个用户模式中的所有对象导出和导入一个或多个指定的表空间中的所有对象
  • 256. 导出实用程序 2-1导出实用程序有以下常用命令参数:参数说明USERID确定执行导出实用程序的用户名和口令BUFFER确定导出数据时所使用的缓冲区大小,其大小用字节表示FILE指定导出的二进制文件名称,默认的扩展名是.dmpFULL指定是否以全部数据库方式导出,只有授权用户才可使用此参数OWNER要导出的数据库用户列表HELP指定是否显示帮助消息和参数说明ROWS确定是否要导出表中的数据TABLES按表方式导出时,指定需导出的表和分区的名称PARFILE指定传递给导出实用程序的参数文件名TABLESPACES按表空间方式导出时,指定要导出的表空间名
  • 257. 导出实用程序 2-2exp scott/tiger@accp file=scott_back owner=scott按用户方式导出数据exp scott/tiger@accp tables=(emp, dept) file=scott_back_tab按表方式导出数据exp system/aptech parfile='C:\parameters.txt'使用参数文件导出数据exp system/aptech@accp tablespaces=(users) file=tbs_users按表空间方式导出数据
  • 258. 导出实用程序 2-2exp scott/tiger@accp file=scott_back owner=scott按用户方式导出数据exp scott/tiger@accp tables=(emp, dept) file=scott_back_tab按表方式导出数据exp system/aptech parfile='C:\parameters.txt'使用参数文件导出数据exp system/aptech@accp tablespaces=(users) file=tbs_users按表空间方式导出数据
  • 259. 导入实用程序 2-1导入实用程序有如下常用命令参数:参数说明USERID指定执行导入的用户名和密码BUFFER指定用来读取数据的缓冲区大小,以字节为单位COMMIT指定是否在每个数组(其大小由BUFFER参数设置)插入后进行提交FILE指定要导入的二进制文件名FROMUSER指定要从导出转储文件中导入的用户模式TOUSER指定要将对象导入的用户名。FROMUSER与TOUSER可以不同FULL指定是否要导入整个导出转储文件TABLES指定要导入的表的列表ROWS指定是否要导入表中的行PARFILE指定传递给导入实用程序的参数文件名,此文件可以包含这里列出的所有参数IGNORE导入时是否忽略遇到的错误,默认为NTABLESPACES按表空间方式导入,列出要导入的表空间名
  • 260. 导入实用程序 2-2imp accp/accp@accp file=item_back.dmp ignore=y full=y将整个文件导入数据库imp system/oracle parfile='C:\parameters.txt'使用参数文件导入数据imp system/aptech@accp file=scott_back fromuser=scott touser=martin tables=(emp,dept)将scott用户的表导入到martin用户
  • 261. 数据库归档方式 3-1Oracle 数据库可以运行在两种归档方式: 非归档日志方式 归档日志方式 非归档日志方式可以避免实例故障,但无法避免介质故障。在此方式下,数据库只能实施冷备份 归档日志方式产生归档日志,用户可以使用归档日志完全恢复数据库
  • 262. 数据库归档方式 3-2非归档日志方式下数据库的工作原理:表空间脱机 备份表空间恢复表空间 表空间联机
  • 263. 数据库归档方式 3-3归档日志方式下数据库的工作原理:日志文件1填满 清空日志文件 2 向日志文件2写入信息准备向日志文件 2 写入信息 备份日志文件2
  • 264. 配置归档日志方式 4-1配置数据库在归档日志方式下运行,包括以下三个步骤: 确保数据库当前不处于归档方式 设置相关数据库初始化参数 在归档日志方式下启动数据库
  • 265. 配置归档日志方式 4-2查看当前数据库的归档方式
  • 266. 配置归档日志方式 4-3设置相关启动参数
  • 267. 配置归档日志方式 4-4以归档方式启动数据库
  • 268. 自动归档和手动归档 归档日志方式下的数据库自动归档 手动归档 数据库的日志归档方式有两种:手动归档允许用户手动归档非活动日志文件文件的已填充组自动归档对非活动日志文件文件进行自动归档
  • 269. 获取归档日志信息SQL> SELECT DEST_ID,DEST_NAME,STATUS,DESTINATION FROM V$ARCHIVE_DEST WHERE STATUS='VALID';目标位置名称目标位置的状态归档日志文件的路径可以通过数据字典视图查看归档日志信息 V$ARCHIVE_DEST - 显示当前所有归档日志存储位置及其状态 V$ARCHIVE_LOG - 显示历史归档日志信息SQL> SELECT DEST_ID,NAME,ARCHIVED FROM V$ARCHIVED_LOG;目标位置编号归档日志文件名 及完整路径是否已归档
  • 270. 总结数据库备份用于保护数据库中的数据,有两种类型的备份:物理备份和逻辑备份 导致数据库操作中止的故障类型有:语句故障、用户进程故障、实例故障和介质故障 Oracle 提供导出和导入实用程序实施数据库的逻辑备份 导出导入实用程序有四种工作模式:完全数据库、表、用户和表空间 数据库可在两种方式下运行:非归档日志方式和归档日志方式