ORACLE期末复习笔记

🕺作者： 主页

我的专栏
C语言从0到1
探秘C++
数据结构从0到1
探秘Linux

😘欢迎关注：👍点赞🙌收藏✍️留言

🏇码字不易，你的👍点赞🙌收藏❤️关注对我真的很重要，有问题可在评论区提出，感谢支持！！！

一、体系结构

1. ORACLE特性

1. 数据库应用开发特性
   1. SQL、PL/SQL、JAVA虚拟机，ODBC/JDBC
2. 数据库连接特性
   1. ORACLE NET,OID,连接管理器，应用服务器
3. 分布式数据库特性
4. 数据移动特性
5. 性能特性
   1. 并行化、数据仓库、OLAP
6. 数据库管理特性
   1. 企业管理器OEM、恢复与备份

2. 网格

1. 概念：网格是把整个因特网整合成一台巨大的虚拟超级计算机，实现计算资源、存储资源、数据资源、信息资源、知识资源、专家资源的全面共享。
2. 根本特征：网格的根本特征是资源共享而不是它的规模,透明而且高效率地提供各种服务
3. 关键技术 ：实时应用集群RAC，自动存储管理ASM,Oracle资源管理器，Oracle调度器和Oracle流
4. 网格计算–分布式计算
   

3. ORACLE体系结构

1. 三个主要区：
2. 数据库高速缓存
   1. 存储已从数据文件中检索到的数据块的副本
   2. 能够大幅提高获取和更新数据时的性能
   3. 通过**“最近最少使用算法”** LRU 算法管理
   4. 主块的大小由DB_BLOCK_SIZE确定
   5. 由独立的子高速缓存组成：
   DB_CACHE_SIZE
   DB_KEEP_CACHE_SIZE
   DB_RECYCLE_CACHE_SIZE
   可以通过ALTER SYSTEM SET DB_CACHE_SIZE = 来设置
3. 共享池
   1. 用于存储：
   1. 最近执行的SQL语句
   2. 最近使用的数据定义

  2. 它包括以下两个与性能相关的关键内存结构 
     1. 库高速缓存 
        1. 存储有关最近使用的**SQL** 和**PL/SQL** 语句的信
        2. 启用常用语句共享
        3. 由**“**最近最少使用算法**”** (LRU) 管理
        4. 包括以下两个结构： 
           1. 共享的**SQL** 区
           2. 共享的**PL/SQL** 区
        5. 大小由共享池的大小确定
     2. 数据字典高速缓存 
        1. 数据库中最近使用的定义的集合
        2. 包括与数据库文件、表、索引、列、用户、权限和其它数据库对象相关的信息
        3. 在语法分析阶段，服务器进程会在数据字典中查找用于解析对象名和验证访问的信息
        4. 将数据字典信息高速缓存到内存中，可缩短查询和**DML**的响应时间
        5. 大小由共享池的大小决定
  3. 其大小由**SHARED_POOL_SIZE**参数确定

3. 日志文件缓冲区
   1. 记录对数据库数据块所做的全部更改
   2. 主要用于恢复
   3. 其中记录的更改称作重做条目
   4. 重做条目包含用于重新构造或重做更改的信息
   5. 大小由LOG_BUFFER 定义
4. 其他内存结构
5. 大型共享池
   1. SGA 中的可选内存区
   2. 分担了共享池的一部分工作
   3. 用于：
      1. 共享服务器的会话内存**(****UGA)
      2. I/O 服务器进程
      3. 备份和恢复操作或RMAN
      4. 并行执行消息缓冲区
      5. 将PARALLEL_AUTOMATIC_TUNING设置为TRUE
   4. 不使用LRU 列表
   5. 大小由LARGE_POOL_SIZE确定
6. JAVA池
   1. 存储Java 命令的服务分析要求，为Java命令提供语法分析
   2. 在安装并使用Java 时是必需的
   3. 大小由JAVA_POOL_SIZE参数确定
7. SGA 是动态的，在init .ora文件中设置
8. 大小由SGA_MAX_SIZE 参数指定
9. 由SGA 组件以粒组为单位进行分配和跟踪

SGA（系统全局区）：

SGA是Oracle数据库的共享内存结构，所有的服务器进程和后台进程都可以访问。

包含Oracle 服务器的数据和控制信息, 它是在Oracle 服务器所驻留的计算机的实际内存中得以分配，如果实际内存不够再往虚拟内存中写

SGA主要包含以下几个部分：
数据库缓冲区缓存：用于缓存读取的数据和更新的数据。
共享池：包含库缓存、字典缓存和结果缓存。
REDO日志缓冲区：存储即将写入REDO日志文件的REDO数据。
Java池：供OracleJVM使用的内存区域。
Streams池：供Oracle Streams功能使用的内存区域。
PGA（程序全局区）：

PGA是每个后台进程和服务器进程的私有内存区域。

在服务器进程启动时分配

为连接到Oracle 数据库的每个用户进程保留的内存

在创建进程时分配，终止进程时回收，仅供一个进程使用，不共享

PGA主要包含以下几个部分：
会话内存：存储会话信息。
SQL区域：用作SQL执行时的工作区，例如数据排序等。

4. Oracle工作原理
   1. 在运行Oracle的计算机上启动实例
   2. 运行客户的应用程序在用户进程中运行应用
   3. 客户应用通过Oracle Net连接到服务器
   4. 服务器创建对应连接的服务器进程
   5. 用户运行SQL语句，提交事务
   6. 服务器进程检查共享SQL区，若无则分配SQL区
   7. 服务器进程修改SGA数据，DBWR写入磁盘。在提交之前，LGWR进程在重做日志文件中记录。
   8. 成功提交后，服务器进程通过网络发送消息给应用，否则返回错误信息。
   9. 整个过程中，其他后台进程依然运行，检查中断条件。同时服务器管理其他用户事务，防止访问相同事务产生冲突
5. 应用体系结构
   1. C/S模式：Client通过Network连接DB Server
      1. 客户端不处理任何数据，通过SQL语句提交数据库请求
   2. 多层结构：客户端通过应用服务器访问数据库服务器
      1. 多线程服务器系统
      2. 并行数据库系统
      3. 分布式数据库系统
      4. Oracle WebServer系统

4. 实例instance与数据库database

1. 数据库：信息的物理存储，存在磁盘中
2. 物理概念，包括存储在磁盘上的文件（例如数据文件，控制文件，日志文件）
   1. 数据文件：.DBF存储表、索引及数据结构信息
   2. 日志文件：LOG记录对数据库的所有修改信息，用于恢复
   3. 控制文件：.CTL二进制文件,记录数据库名、文件标识、检查点
   4. 参数文件：SPFILE,定义Oracle 例程的特性，例如它包含调整SGA中一些内存结构大小的参数
   5. 归档文件：是重做日志文件的脱机副本，这些副本可能对于从介质失败中进行恢复很必要。
   6. 密码文件：认证哪些用户有权限启动和关闭Oracle例程
3. 实例：在服务器上运行的软件，是访问数据库的方式和手段
4. 逻辑概念，包括服务器中内存结构（SGA）与进程。
   1. 用户线程（客户端线程）：如sqlplus、WEB（OEM等）、应用程序（例如java程序调用sql）
   2. 服务器进程：响应客户端和数据库服务器之间的请求，用户线程不能直接访问Oracle，必须通过相应的服务器进程访问实例，进而访问数据库
5. 始终打开一个并且只能打开一个数据库
6. 12C：一个instance可以打开多个database
7. 实例的组件
   1. ORACLE实例是SGA和后台进程的集合
   2. 实例的所有进程(包括系统进程和用户进程)共享SGA
8. 进程结构
   1. 用户进程：在数据库用户请求连接到Oracle 服务器时启动
      1. 请求与Oracle 服务器交互的程序
      2. 必须先建立连接
      3. 不与Oracle 服务器直接交互
   2. 服务器进程：与Oracle 实例相连接，在用户建立会话时启动
      1. 直接与Oracle 服务器交互的程序
      2. 执行生成的调用并返回相关结果
      3. 可以是专用服务器
   3. 后台进程：在Oracle 实例启动时启动
      1. 维护并加强物理结构与内存结构之间的关系
      2. 必备的后台进程：
         1. **DBWR **
            1. 数据库写入进程：将数据库高速缓存中修改的块写入数据文件：
            2. 触发时机
               • 出现检查点
               • 脏数据缓冲区达到阈值
               • 没有空闲缓冲区
               • 出现超时
               • 执行了RAC ping 请求
               • 表空间处于OFFLINE状态
               • 表空间处于READ ONLY状态
               • 对表执行DROP或TRUNCATE操作
               • 对表空间执行BEGIN BACKUP操作
            3. DBWR进程可以有多个，上限为20
         2. **PMON **
            1. 进程监视器：服务器进程失败后，执行进程恢复，通过以下方法进行清理
               • 回退事务处理
               • 释放锁
               • 释放其它资源
               • 重新启动已失效的调度程序
         3. CKPT
            1. 检查点：职责包括：
               • 在检查点发信号给DBWR
               • 使用检查点信息更新
               • 数据文件的标头
               • 使用检查点信息更新
         4. **LGWR **
            1. 写日志进程：日志书写器将重做日志写入磁盘
            2. 触发时机
               • 提交时
               • 三分之一填满时
               • 有1 MB 的重做时
               • 每隔三秒
               • DBWR 写入前
            3. 每个实例仅一个LGWR
         5. SMON
            1. 系统监控进程：在失败实例进程启动时执行崩溃恢复
            2. 触发时机
               • 实例恢复
               • 前滚重做日志中的更改
               • 打开数据库供用户访问
               • 回退未提交的事务处理
               • 合并空闲空间
               • 回收临时段
      3. 可选的后台进程：
         1. 归档进程（ARCH）
            1. 可选的后台进程，用于介质失败的恢复
            2. 设置ARCHIVELOG模式时自动归档联机重做日志
            3. 保留数据库的全部更改记录
            4. 原理图
         2. 其它进程
            1. RECO：恢复器进程
            2. Job Queue Processes：工作队列进程
            3. Dnnn：Dispatcher多线程服务器配置使用
            4. LMS：锁管理器服务器进程，用于RAC实例内部死锁
            5. QMNn：队列监视器进程
9. 关系:用户要通过实例来访问磁盘上数据库中的信息。
10. 数据库名一旦创建就不能改变,但是可以改变访问它的实例名
11. 一个数据库可以对应多个实例，一个实例只能对应一个数据库
12. 连接过程
    监听器listener：监听客户发来的服务名
    Oracle服务器：实例，包含多种文件结构、进程结构和内存结构

5. 逻辑结构

1. 表空间tablespace--------是数据库中最大的逻辑划分
2. 每个数据库至少有一个SYSTEM表空间，存放系统表和数据字典
3. 另外还可以包含其他表空间:如USER表空间供一般用户使用。TEMPI临时表空间，存放排序等临时数据
4. 每个表空间由同一磁盘上的一个或多个文件组成，
   这些文件叫数据文件(datafile)。一个数据文件只能属
   于一个表空间。
5. 想修改它的大小时可以使用

ALTER USER user1 QUOTA 100M ON tablespace1

4. 逻辑结构的划分
5. 从大到小依次划分为表空间、段、区、块
   1. 段:数据段、索引段、回退段、临时段
   2. 区是磁盘空间分配的最小单位,一个段中，最小的区的个数是由参数MINEXTENTS来确定
   3. 数据块是数据库中最小的数据组织单位与管理单位，是数据文件磁盘存储空间单位，也是数据库I/O的最小单位,数据块天小由DB BLOCK SIZE参数决定，通常是操作系统块的整数倍

6. 数据库对象—模式对象Schema

1. 直接引用数据库数据的逻辑结构，包括:
   1. 表：table
   2. 视图：view
   3. 聚簇：cluster
   4. 索引：index
   5. 序列生成器：产生序列号
   6. 同义词：别名
   7. 哈希：存储表的方法
   8. 程序单元：存储过程、函数、包
   9. 数据库链等
2. 模式对象的集合构成模式，一个模式对应一个数据库用户，模式名和用户名相同

7. Oracle数据字典

1. 只读类型的表，提供数据库中的以下信息:
   1. 数据库对象的定义
   2. 如何分配和使用空间
   3. 列的默认值
   4. 完整性约束信息
   5. Oracle用户的名称、权限和角色
   6. 审计信息等
2. 以表和视图的形式存放在SYSTEM表空间中
3. 用户通过SQL语句访问数据字典
4. 数据字典包括数据字典基表和数据字典视图
   1. 数据字典基表存储着数据库的基本信息，用户不能直接访问。
   2. 数据字典视图则在基表的基础上建立的视图，专供用户查询获得系统信息
5. 每个视图家族都有三个视图
   1. DBA_XXX：包含数据库所有的对象信息
   2. ALL_XXX：当前用户可以访问的对象信息
   3. USER_XXX：当前用户所拥有的对象信息
   4. 从前往后依次是包含关系
6. 视图家族举例
   | 视图家族 | 描述 |
   | — | — |
   | TABLES | 表信息：表名、所属表空间(tablespace),存储参数(storage parms)和数据行的数量，所属用户名 |
   | USERS | 用户信息：用户名、密码、缺省的和临时的表空间 |
   | TABLESPACES | 表空间信息：表空间名、块大小、联机/脱机状态等等 |
   | TRIGGERS | 触发器信息，类型、事件、触发体(trigger body) |

如对于tables视图家族来说，有DBA_TABLES、ALL_TABLES、USER_TABLES三种视图

select distinct(owner) from dba_tables; \\sys用户下
select table_name from dba_tables where owner='HR'; \\sys用户下

\\其它用户则查询ALL_XXX、USER_XXX

7. 动态性能视图
   1. 记录当前例程的活动信息，由ORACLE自动创建和删除。
      1. 所有的动态性能视图是以V_开始的。一般查询的是同义词视图
      2. 动态性能视图的所有者是SYS，一般只能由特权用户和DBA用户使用
      3. 通过查询V$FIXED_TABLE可以显示所有的动态性能视图
   2. 常用动态性能视图
      1. V$CONTROLFILE：当前数据库所包含的所有控制文件。
      2. V$DATABASE：当前数据库的详细信息。
      3. V$DATAFILE：数据文件的详细信息。
      4. V$DBFILE：数据文件编号及名称。

二、数据库操作

1. 创建数据库前的准备工作

1. 设计数据库的概念结构：ER模型
2. 设计数据库的逻辑结构：关系模式
3. 设计数据库的物理结构：存储，I/O系统
4. 选取全局数据库名(Global Database Name)
5. 数据库在网络结构中的名字和位置
6. 通过设置DB_NAME和DB_DOMAIN两个初始化参数来完成全局数据库名的设置
7. 构成：DB_NAME . DB_DOMAIN
8. 决定字符集(character set)
9. 适应不同的语言文字显示
10. 以数据库字符集的形式存储
    1. 以CHAR数据类型存储的数据
    2. 标识符
    3. 输入并存储SQL、PL/SQL程序源代码
11. 创建数据库时，必须指定数据库字符集
12. 一旦数据库创建后，数据库的字符集就不能改变
13. 可以通过select userenv('language') from dual;来查询

2. 手工创建数据库

1. 创建目录
2. 确定数据库名和实例名
3. 确定DBA认证方式：口令方式、操作系统授权
4. 创建初始化参数文件pfile
   
   
   
   
   
   
   
5. 连接并启动实例
   
   
6. 创建数据库create database
   
   
7. 创建其他表空间
   
8. 运行脚本以创建数据字典
   
9. 其他安装:服务器参数文件spfile、备份数据库等
   

三、SQLPlus使用

1. SQL语言编写规则：

1. 关键字、对象、列名不区分大小写
2. 字符值和日期区分大小写
3. SQL语句以“；”结束

2. 可以用形如SQL>? 命令或help 命令的方式,来查询命令用法，如：

3. 登录命令：

connect [username]/[password]@connect_identifier [as SYSDBA]

//connect_identifier 即net服务名用于确定所连接到的数据库
如 connect sys/pass @orcl as sysdba

4. Describe命令（desc）

1. 功能：查看Table, View, Synonym, package, Function的结构
2. 用法：desc XXX

5. 连接命令

命令	功能	备注
CONNECT	连接到用户
DISCONNECT	只断开会话，不会退出SQL\PLUS
PASSWORD	修改用户口令	例：password SCOTT
EXIT	直接退出SQL\PLUS

6. SQL缓冲区操作命令

1. 注意！！ 缓冲区的SQL命令是没有；结束的

命令	缩写	说明
RUN	R	重新运行上一次SQL语句，会列出缓冲区内容
/	无	重新运行上一次SQL语句
n	无	定位缓冲区中的第n行
APPEND 文本	A 文本	在一行尾部添加文本
CHANGE /旧文本/新文本	C /旧文本/新文本	修改sql buffer中的当前行中，第一个出现的字符串
CHANGE /文本	C/文本	从一行中删除文本
CLEAR BUFFER	CL BUFF	删除全部行
DEL	无	删除缓冲区的当前行
DEL n	无	删除缓冲区的第n行
**DEL ***	无	删除缓冲区的当前行
DEL m n	无	删除缓冲区的第m行到第n行
DEL LAST	无	删除缓冲区的最后一行
INPUT	I	在缓冲区中添加一行或多行
INPUT 文本	I 文本	添加一条文本组成的行
LIST	L	列出SQL*Plus缓冲区中的内容
LIST n	L n或n	列出SQL*Plus缓冲区第n行
LIST	L	列出SQLPlus缓冲区当前行
LIST m n	L m n	列出第m行到第n行
LIST LAST	L LAST	列出缓冲区中的最后一行

7. 文件操作命令

命令	功能	备注
EDIT	打开编辑器，编辑SQL文件，无参数则打开缓冲区	若不存在会提示
SAVE	保存SQL缓冲区的内容到OS文件	默认文件扩展名为.sql
GET	调入脚本文件到SQL缓冲区	例：get d:\sql\get_t1.sql
START	运行SQL脚本文件，与@相同
@	运行SQL脚本文件，等同于START
@@	运行SQL脚本文件，主要用于嵌套在脚本文件中调用其他文件	用在脚本文件中
SPOOL	把SQL*Plus屏幕内容存放到文本文件中，但需要用SPOOL OFF命令关闭spool输出，才会在输出文件中看到输出的内容	如Spool D:\a.txt

8. 格式命令

命令	功能	备注
COLUMN （col）	控制列的显示格式，缩写COL	如改变列标题,列名,显示长度,对齐方式等
BREAK	屏蔽掉一个列中显示的相同的 删除break设置用clear break 显示break设置直接输入break	每当列值变化时在值变化之前插入n个空行。BREAK ON break_column SKIP n
TTITLE	指定页标题	用OFF清除 （ttitle off）
BTITLE	指定页脚注	用OFF清除

1. break举例
2. break on table skip num举例

9. 交互命令

命令	功能	备注
Define	定义字符类型的替代变量，取消定义用undefine 变量名	无定界符，则认为是输入字符类型，定义时要赋值 例：Define no=001
Accept	定义字符、数字、日期类型的替代变量	可以指定输入提示、格式和隐藏输入内容
PROMPT	在屏幕上为变量输入输出提示信息	Accept 变量名 prompt ‘提示信息’
&	地址符号，引用替代变量时必须带有该符号	创建一个临时变量，每当遇到这个临时变量时，都会提示输入一个值
&&	引用变量的符号,不同于&	创建一个持久变量，只在第一次引用时要输入值
VAR[IABLE]	定义绑定变量，EXEC给绑定变量赋值，引用绑定变量要用“：”号	VAR X NUMBER EXEC :X:=168 可用于将sql查询语句中的查询结果放入到程序的变量中
PRINT	输出绑定变量的结果	PRINT X
PAUSE	暂停脚本执行,参数有OFF默认,ON或用户自定义提示语句	当结果超过一页可用set pause on使每页显示完毕后停顿，按enter显示下一页
HOST	执行OS命令	HOST COPY A.TXT B.TXT

1. 替代变量
2. 替代变量定义时要赋初值，若没有定义的话则是作为临时变量使用，没有数据的替代变量会由屏幕提示输入值
   
3. Accept
4. 格式
   1. Accept 变量名 prompt ‘提示语’
   2. Accept 变量名 hide。会使后面的输入变得不可见

10. 显示和设置环境变量

命令或参数	功能	备注
SHOW命令	显示环境变量的值	如show user
SET命令	设置环境变量的值	如set oracle_sid=orcl
ARRAYSIZE	数组提取尺寸，默认15	值大网络开销小，空间占用大
AUTOCOMMIT	设置是否自动提交
COLSEP	设置列间分隔符，默认空格	如: SET COLSEP |
FEEDBACK	返回几行时显示SELECT所处理的行数	默认6
HEADING	设置是否显示列标题	set heading {off|on}

命令或参数	功能	备注
LINESIZE	设置行宽	默认80
PAGESIZE	设置每页行数	默认14
LONG	设置LONG和LOB类型列宽	默认80
SERVEROUTPUT	控制服务器是否输出	调用包DBMS_OUTPUT时有输出
TERMOUT	控制SQL脚本的输出
TIME	设置是否在SQL提示符前显示时间
TIMING	设置是否显示SQL语句执行的时间

SELECT VALUE FROM NLS_SESSION_PARAMETERS WHERE PARAMETER = 'NLS_DATE_FORMAT';
显示当前会话的日期格式

ALTER SESSION SET NLS_DATE_FORMAT = 'yyyy-mm-dd hh24:mi:ss';
修改日期格式

11. Show命令

1. 显示当前环境变量的值： Show all
2. 显示当前在创建函数、存储过程、触发器、包等对象的错误信息：Show error
3. 当创建一个函数、存储过程等出错时，变可以用该命令查看在那个地方出错及相应的出错信息，进行修改后再次进行编译。
4. 显示初始化参数的值：show parameter [parameter_name]
5. 显示数据库的版本： show REL[EASE]
6. 显示SGA的大小：show SGA
7. 显示当前的用户名：show user

四、SQL基础

1. 层次查询：START WITH、CONNECT BY：

1. START WITH：设置起点，用来限制第一层的数据，或者叫根节点数据；以这部分数据为基础来查找第二层数据，然后以第二层数据查找第三层数据以此类推。
2. CONNECT BY：用来指明在查找数据时以怎样的一种关系去查找；比如说查找第二层的数据时用第一层数据某个字段进行匹配，如果这个条件成立那么查找出来的数据就是第二层数据，同理往下递归匹配。
3. PRIOR：表示上一层级的标识符。经常用来对下一层级的数据进行限制。
4. LEVEL：伪列（关键字），代表树形结构中的层级编号（数字序列结果集），这个必须配合CONNECT BY使用，和ROWNUM是同等效果。

SELECT employee_id, employee_name
FROM employees
START WITH employee_id = 1
CONNECT BY PRIOR employee_id = manager_id;

2. 选择查询：CASE…ELSE…END、WHEN…THEN：

1. CASE语句允许你在SQL查询中进行条件判断。
2. WHEN后面跟着判断条件，THEN后面跟着满足该条件时返回的结果。
3. ELSE后面跟着默认的返回结果，当所有WHEN条件都不满足时返回。
4. CASE语句以END结束。

SELECT country,
       SUM (CASE WHEN sex = '1' THEN population ELSE 0 END) AS male_population,
       SUM (CASE WHEN sex = '2' THEN population ELSE 0 END) AS female_population
FROM Table_A
GROUP BY country;

3. 使用日期类型字段

1. 用单引号引用日期值可使用系统默认格式‘dd-mm月-yy’
2. 或者用to_date( )函数进行转换
3. 用NLS_DATE_FORMAT环境变量设置日期显示格式

4. Dual表的用法

1. dual是为了给那些没有特定表对象的SQL语句提供的一个对象，是一个虚拟表，用来构成select的语法规则，Oracle保证dual里面永远只有一条记录，在获取系统时间或测试函数的时候非常有用

5. 调用系统函数

1. 获得当前系统时间
2. 获得一个随机数

6. Rollup查询

1. 在Oracle中，ROLLUP是一个SQL扩展，它用于生成包含小计和总计的报告。ROLLUP是GROUP BY子句的扩展，它可以计算一组列（或维度）的多级小计以及总计。
2. 语法格式

SELECT col1, col2, aggregate(col3)
FROM table_name
GROUP BY ROLLUP(col1, col2);

3. 示例

7. Grouping函数

例子

8. 插入、更新、删除数据

9. 事务控制命令

1. 事务TRANSACTION：相邻两条事务控制命令之间的一组SQL语句
   1. 将缓冲区的改变写入磁盘，保证数据库的一致性、可恢复性
   2. 未显示提交，则在关闭数据库和实例之前提交或回退。
2. 控制命令：
   1. 提交：COMMIT
   2. 回退：ROLLBACK
   3. 设置保存点：SAVEPOINT
3. 显式提交：永久写入磁盘
   1. 命令：SQL> COMMIT;
4. 隐式提交：
   1. DDL语句前后隐含地执行COMMIT
   2. 其他SQL命令如connect，exit，grant等
5. 将自动提交开关设为ON，默认是OFF，则所有INSERT、UPDATE、DELETE执行以后都会立即写入磁盘
   1. 命令为：SQL>SET AUTOCOMMIT ON;
6. 保存点
   1. 用途：可以将事务分成若干部分，每部分之间用SAVEPOINT分割。必要时可以不必回退整个事务
   2. 格式：
      1. SAVEPOINT 保存点名；
      2. ROLLBACK TO 保存点名；
7. 事务和锁
   1. A事务对数据执行DML操作时，会对数据加锁，防止其他事务对同一数据执行操作
   2. 直到COMMIT或rollback之后事务B才能执行操作
8. 事务处理设计
   1. 工作单元:一系列关系紧密的SQL命令组成
      1. 如:对同一个表的insert语句及最后的commit语句构成一个工作单元
   2. 事务的设计要以工作单元为基础
      1. 一般不包含多个工作单元或一个工作单元的部分
      2. 如:对没有关联的多个表的操作可构成一个工作单元
      3. 分类:
         1. 读写事务处理:set transaction read write;
         2. 只读事务处理:set transaction read only;

五、PL\SQL程序设计

1. 概念

1. Oracle自己的过程化语言
2. 特点：面向过程与SQL语言结合
   1. 可以定义变量使用控制语句
   2. 编写过程和函数
   3. 自定义类型和方法
3. 编写运行环境或工具
   1. SQL*Plus
   2. PL/SQL Developer
4. 不能直接写查询语句，查询结果要插入某个变量，然后通过DBMS输出

2. 程序结构

1. 三部分：

DECLARE
--定义部分，可选
BEGIN
--执行部分，必须
EXCEPTION
--异常处理部分，可选
END；/*结束标记*/

2. 从BEGIN开始执行，END后的“；”不可省
3. PL/SQL块的分类
4. 匿名块、命名块、子程序(过程、函数、包)、触发器
5. 举例

set serveroutput on
DECLARE
    TYPE stu_record_type IS RECORD
    (
        ID VARCHAR2(10),
        NAME VARCHAR2(10),
        SEX varchar2(2),
        ADDRESS VARCHAR2(30),
        Academic_Record VARCHAR2(10),
        Active_Record VARCHAR2(10)
    );
    stu_record stu_record_type;
    -- 过程定义
    PROCEDURE PrintStuRecord (x IN stu_record_type) IS
        BEGIN
          -- 输出欢迎信息
          dbms_output.put_line('学号:'||x.ID);
          dbms_output.put_line('姓名:'||x.NAME);
          dbms_output.put_line('性别:'||x.SEX);
          dbms_output.put_line('籍贯:'||x.ADDRESS);
          dbms_output.put_line('学习成绩:'||x.Academic_Record);
          dbms_output.put_line('活动成绩:'||x.Active_Record);
        END PrintStuRecord;
BEGIN
    stu_record.ID:='2001001';
    stu_record.NAME:='李新';
    stu_record.SEX:='m';
    stu_record.ADDRESS:='黑龙江省哈尔滨市';
    stu_record.Academic_Record:='Excellent';
    stu_record.Active_Record:='Good';
    PrintStuRecord(stu_record);
EXCEPTION
   -- 异常处理
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('An error occurred.');
END;

--结果
学号:2001001
姓名:李新
性别:m
籍贯:黑龙江省哈尔滨市
学习成绩:Excellent
活动成绩:Good

3. DBMS_OUTPUT包

1. 系统包：提供的函数和过程可用于输入输出信息,格式：DBMS_OUTPUT.函数/过程名
   1. Enable/Disable过程：激活DBMS_OUTPUT的其他过程和函数，也可用set serveroutput on/off语句设置可用/不可用
   2. Put_line和Put过程：将信息写入缓冲区，并显示在屏幕上。put_line自动带结束符(换行)，而put不带结束符，可用new_line过程在行尾添加结束符(无结束符的行不显示在屏幕)
   3. Get_line和get_lines过程：取得缓冲区的单行/多行信息放入变量中，被获取的信息将不再输出，并返回成功与否的状态信息或实际返回行数，如果没有获得行数则为1，将第一行信息写入line变量中。若要接收多行，需要定义表，且使用get_lines函数
   4. 在SQL*Plus中使用put和put_line过程必须要设置serveroutput，而要在其他应用程序中使用则需要用get_line(s)
   5. new_line过程：创建一个新的空白行

4. 变量和常量

1. 命名规则：
   1. 变量必须以字母(AZ,az)开头，若要以其他字符开头则必须用“”括起来，如“1234”
   2. 其后跟可选的一个或多个字母、数字(0~9)或特殊字符$、# 或_
   3. 变量长度不超过30个字符
   4. 变量名中不能有空格
   5. 变量名不能是关键字
2. 声明格式
   1. 变量名 [CONSTANT] 数据类型 [[NOT NULL]{DEFAULT|:=}]
   2. Constant是常量的标志，必须赋初值
3. 变量类型：标量类型、复合类型、参照类型、LOB类型

5. 数据类型

数据类型	子类	描述
BINARY_INTEGER	NATURAL、 NATURALN、 POSITIVE、POSITIVEN、 SIGNTYPE	存储范围在-2 147 483 647到2 147 483 647之间的有符号整数。使用数学库，NATURAL与NATURALN只存储非负整数，后者不允许存储0；POSITIVE和POSITIVEN只存储正数，后者不允许存储0。SIGNTYPE只存储-1、0和1
NUMBER(精度，比例)	DEC、DECIMAL、DOUBLE PRECISION、FLOAT、INTEGER、INT、NUMBERIC、REAL、SAMLLINT	定点数或浮点数，使用数学库

数据类型	子类	描述
PLS_INTEGER		存储范围在-2 147 483 647到2 147 483 647之间的有符号整数，使用机器算法以进行快速计算
CHAR(长度)	CHARACTER(长度)	存储定长字符串，最大长度为32 767字节；然而，数据库中CHAR最长为2000字节
VARCHAR2(长度)	VARCHAR(长度)、 STRING	存储变长字符串，最大长度为32 767字节；然而，数据库中CHAR最长为4000字节
DATE		存储与时间相关的信息，包括日期、小时、分、秒

数据类型	子类	描述
ROWID		存储数据库表每一行的物理地址
UROWID		存储数据库表中每一行的物理的、逻辑的或外部的(非Oracle的)地址
CLOB		存储大型、单字节字符对象，如长文本
BLOB		存储大型二进制对象，如图像、视频、音频等
BFILE		存储数据库外文件系统管理的LOB的文件指针
BOOLEAN		存储逻辑值(真、假与空)

6. 编程规范

1. 命名规范
   | 类型 | 前缀或后缀 | 举例 |
   | — | — | — |
   | 变量 | v_ | v_sal |
   | 常量 | c_ | c_rate |
   | 游标 | _cursor后缀 | emp_cursor |
   | 异常 | e_ | e_int_error |
   | 表类型 | _table_type后缀 | sal_table_type |
   | 表变量 | _table后缀 | sal_table |
   | 记录类型 | _record_type后缀 | emp_record_type |
   | 记录变量 | _record后缀 | emp_record |

2. 大小写规则

3. SQL关键字用大写,如SELECT、UPDATE

4. PL/SQL关键字用大写，如BEGIN、END

5. 数据类型用大写，如INT、VARCHAR2

6. 标识符和参数用小写，如v_sal

7. 数据库对象和字段用小写，如emp，ename

8. 层次式缩进格式

7. PL/SQL控制结构

1. 顺序结构
2. 选择结构
3. If…then…end if; \ if…then…else…end if ; \ if…then…elsif…then…end if; (注意不是elseif，不用end if结束)

/**
根据输入的编号查询当前工资,再根据部门调整,并显示调整前后的工资数
*/
SET SERVEROUTPUT ON
UNDEFINE no

DECLARE
	v_bmm varchar2(10);
	v_xs number;
BEGIN
	SELECT bmm,xs
	INTO v_bmm,v_xs
	FROM t1,t2
	WHERE t1.bmh=t2.bmh AND bh=&&no;

	DBMS_OUTPUT.PUT_LINE('--------------------------------------');
	DBMS_OUTPUT.PUT_LINE('职员'||'&no'||'所在的部门名是:'||v_bmm);
	DBMS_OUTPUT.PUT_LINE('调整前工资为:￥'||to_char(v_xs));


	IF v_bmm='设计部' THEN
		UPDATE t1 SET xs=xs+1000 WHERE bh=&no;
	ELSIF v_bmm='营销部' THEN
		UPDATE t1 SET xs=xs+2000 WHERE bh=&no;	
	ELSE
		UPDATE t1 SET xs=xs+3000 WHERE bh=&no;
	END IF;

	select xs into v_xs from t1 where bh=&no;

	DBMS_OUTPUT.PUT_LINE('调整后工资为:￥'||to_char(v_xs));
END;
/

3. 循环结构
4. Loop…exit…end loop; \ while-loop…end loop; \ for-in-loop… end loop;

--1.
CREATE TABLE temp1(cola INT); 
DECLARE
 i INT:=1;
 BEGIN
   LOOP
     INSERT INTO temp1 VALUES(i);
   EXIT WHEN i=10;
   i:=i+1;
   END LOOP;
 END;
/
select * from temp1
/

--2.
DECLARE
 i INT:=1;
 BEGIN
   WHILE i<=10 LOOP
     INSERT INTO temp1 VALUES(i);
     i:=i+1;
   END LOOP;
 END;
/
select * from temp1
/

--3.
DECLARE
 i INT:=1;
 BEGIN
   FOR i IN REVERSE 1..10 LOOP
     INSERT INTO temp1 VALUES(i);
     
   END LOOP;
 END;
/
select * from temp1
/

4. NULL结构：空操作，用于捕捉异常，但不做任何处理，然后执行下一块代码

8. 复合变量—记录record(一行不同数据类型的元素)

1. 可以包含多个字段的用户自定义类型，类似结构体类型，可用于定义记录变量
2. 格式

TYPE  记录类型  IS RECORD
（字段1  数据类型  [[NOT NULL]{DEFAULT |:=} 表达式]，
    字段2 …
  ）;    - -定义记录类型
记录变量 记录类型;- -定义记录变量

TYPE emp_record_type IS RECORD
(
    name t1.xm%TYPE,
    name t1.xm%TYPE,
    name t1.xm%TYPE,
);
emp_record emp_record_type;

9. 集合类型—索引表(一列相同数据类型的元素)

1. 类似于数组，包括三种：索引表、嵌套表、变长数组VARRAY
2. 定义索引表类型和变量

TYPE  表类型名  IS  TABLE  OF  元素数据类型
[NOT NULL] INDEX BY 下标数据类型；- - 类型定义
表变量名  表类型名；- - 变量定义

TYPE name_table_type IS TABLE OF TI.xm%TYPE
		INDEX BY BINARY_INTEGER;
name_table name_table_type;

3. 与高级语言中数组的区别：
4. 高级语言中数组下标有界且非负，索引表中元素个数无限，下标可以为负
5. 9i以后，索引表元素下标除了可以用整型数字也可以用字符型
   1. 用字符作为下标类型，则会自动按字符排序

10. 游标

1. 使用情景：n当SELECT语句返回一个(行)值时，可以直接用INTO将结果放到一个变量中
   但若查询返回多行值时，不能直接用INTO赋值给变量，此时需要使用显式游标指向SELECT的结果集。使用游标时每次会从结果集中取一行信息,并向下移动。
2. 格式

CURSOR 名称 IS sql语句;

CURSOR emo_cursor IS
	SELECT xm,xs FROM ti WHERE bmh=&bno;

3. 四个阶段
4. 声明：CURSOR 游标名 IS 查询语句；
5. 打开：OPEN 游标名；
6. 提取：FETCH 游标名 INTO 变量表；
7. 关闭：CLOSE 游标名；

4. 属性
5. %ISOPEN：判断是否打开游标
6. %FOUND：表示查询至少有一条返回行
7. %NOTFOUND：表示查询没有返回行
8. %ROWCOUNT：返回当前提取的行数
9. 提取语句
   1. FETCH…INTO…语句特点：每次只能提取一条记录，所以要结合循环使用

SET SERVEROUTPUT ON
DECLARE
   CURSOR emp_cursor IS 
     SELECT xm,xs FROM t1 WHERE bmh=&bno;
   v_name t1.xm%TYPE;
   v_sal t1.xs%TYPE;
BEGIN
   OPEN emp_cursor;
   LOOP 
      FETCH emp_cursor INTO v_name,v_sal;
   EXIT WHEN emp_cursor%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(v_name||'的薪水为:'||v_sal);   
   END LOOP;
   CLOSE emp_cursor;
END;
/

  2.  FETCH…BULK COLLECT INTO语句：一次可以提取所有数据。加上LIMIT标记可以按要求提取若干行记录 

SET SERVEROUTPUT ON
DECLARE
   CURSOR emp_cursor IS 
     SELECT xm,xs FROM t1 WHERE bmh=&bno;
   TYPE emp_record_type IS RECORD 
     (name t1.xm%TYPE,sal t1.xs%TYPE);
   TYPE emp_table_type IS TABLE OF emp_record_type;
   emp_table emp_table_type;
   i int;
BEGIN
   OPEN emp_cursor;
   FETCH emp_cursor BULK COLLECT INTO emp_table;
   FOR i IN 1..emp_table.COUNT LOOP
       DBMS_OUTPUT.PUT(emp_table(i).name||'的薪水为:'); 
       DBMS_OUTPUT.PUT(emp_table(i).sal); 
       DBMS_OUTPUT.NEW_LINE;
   END LOOP;
   CLOSE emp_cursor;
END;
/

5. 游标变量
6. 显式游标是在定义时指定静态SQL语句，而游标变量是在打开时指向SQL语句，更灵活
7. 游标变量是REFCURSOR类型
8. 定义格式：
   1. 游标变量名 CURSOR IS 查询语句；(绑定查询)
   2. TYPE 游标类型名 IS REF CURSOR ；
   游标变量名 游标类型名；
   3. 打开：OPEN 游标变量名 FOR 查询语句
9. 举例

SET SERVEROUTPUT ON
DECLARE
   TYPE emp_cursor_type IS REF CURSOR;
   emp_cursor emp_cursor_type;   
   TYPE emp_record_type IS RECORD 
     (name t1.xm%TYPE,sal t1.xs%TYPE);
   TYPE emp_table_type IS TABLE OF emp_record_type;
   emp_table emp_table_type;
   i int;
BEGIN
   OPEN emp_cursor FOR SELECT xm,xs FROM t1 WHERE bmh=&bno;
   FETCH emp_cursor BULK COLLECT INTO emp_table;
   FOR i IN 1..emp_table.COUNT LOOP
       DBMS_OUTPUT.PUT(emp_table(i).name||'的薪水为:'); 
       DBMS_OUTPUT.PUT(emp_table(i).sal); 
       DBMS_OUTPUT.NEW_LINE;
   END LOOP;
   CLOSE emp_cursor;
END;
/

11. 过程

1. 格式

CREATE   [OR REPLACE]   PROCEDURE   过程名
[(形参名[{IN|OUT|IN OUT}] 类型，…)]
{IS|AS}
	声明语句
BEGIN
	执行语句
EXCEPTION
	异常处理
END [过程名]；

1. OR REPLACE：若过程已存在则重建该过程
2. IN：输入参数，将实参传递给形参，默认
3. OUT：输出参数，忽略实参值，结束后将形参值传递给实参
4. IN OUT：调用时实参传递给形参，结束后形参传回给实参
5. 在SQL*Plus中调用过程
6. 执行格式：EXEC 过程名[(参数)]；
7. 调用格式：CALL 过程名([参数])；

SET SERVEROUTPUT ON
CREATE OR REPLACE PROCEDURE print_curtime
AS
	v_curtime date;
BEGIN
	SELECT sysdate INTO v_curtime FROM dual;
	DBMS_OUTPUT.PUT_LINE('……');
END print_curtime;

--调用
exec print_curtime
call print_curtime()

3. 本地过程
4. 格式

DECLARE
     PROCEDURE 过程名[(形参表)]
     AS
     过程体；
BEGIN
    过程名[(实参)]；
END；

--示例
SET SERVEROUTPUT ON
DECLARE
	PROCEDURE name
	AS
		v_curtime date;
	BEGIN
		......
	END name
BEGIN
	name;
END;

--3.
CREATE OR REPLACE PROCEDURE add_emp
(eno VARCHAR2,ename VARCHAR2,sal NUMBER,depno VARCHAR2)
IS
BEGIN
   INSERT INTO t1(bh,xm,xs,bmh)
   VALUES(eno,ename,sal,depno);
END add_emp;
/

--4.
CREATE OR REPLACE PROCEDURE scott.query_emp
(eno VARCHAR2,ename OUT VARCHAR2,sal OUT NUMBER)
IS
BEGIN
   SELECT xm,xs INTO ename,sal FROM scott.t1
   WHERE BH=eno;
EXCEPTION
   WHEN NO_DATA_FOUND THEN
     RAISE_APPLICATION_ERROR(-20000,'该职员不存在');
END query_emp;
/

12. 函数

1. 格式

CREATE   [OR REPLACE]   FOUNCTION   函数名
[(形参名[{IN|OUT|IN OUT}] 类型，…)]
RETURN  返回类型
{IS|AS}
	声明语句
BEGIN
	执行语句
EXCEPTION
	异常处理
END [过程名]；

2. 调用
3. 函数不能用EXEC调用
4. 可以用SELECT 函数名(参数) FROM dual；
5. 作为表达式的一部分使用,如:v_a:=函数名(实参)

CREATE OR REPLACE FUNCTION get_user
RETURN VARCHAR2
IS
	v_user VARCHAR2(100);
BEGIN
	SELECT username INTO v_user FROM user_users;
	RETURN v_user;
END;


--调用
1.select get_user from dual;

2.EXEC DBMS_OUTPUT.PUT_LINE(get_user)

3.var v1 varchar2(100)
  EXEC :v1:=get_user

3. 本地函数同本地过程一样

13. 删除过程和函数

1. DROP PROCEDURE 过程名
2. DROP FUNCTION 函数名
3. 也是DDL语句，在执行前后都会隐式执行COMMIT命令

14. 子程序的位置

1. 存储子程序
2. CREATE OR REPLACE语句后，子程序都会保存在数据字典中，以p-code代码形式存储，编译一次多次使用
3. 查看子程序的信息，注意视图中的信息都是大写形式的
   • user_objects视图：存放对象所有信息
   • user_source视图：存放源代码
   • user_errors视图：存放编译错误信息
4. 本地子程序
5. 写在块的声明部分：PROCEDURE 过程名| FUNCTION 函数名
6. 只在块内使用，不能存在共享缓冲区中，要多次使用需要多次编译
7. 示例
8. 查看对象依赖关系
9. 在建立的函数和过程时，访问了其他对象,如表、视图等，两者之间就存在依赖关系
10. 通过user_dependencies视图查看某个对象被其他对象依赖的情况
    1. 如：要查看哪些对象依赖TI表，可以用下面的语句

select name,type from user_dependencies
where referenced_name='T1';

5. 重新编译
6. 当被依赖对象如表T1的结构被修改了，相关依赖对象状态就会变为INVALID。对这些子程序等需要重新编译才能使用
7. 语法

alter procedure/function 名称 compile;

15. 触发器

1. 定义：放在数据库中，被隐含执行的存储过程
2. 分类：
3. DML触发器：在insert、delete、update前后执行的存储过程
4. 替代触发器：instead_of触发器代替DML触发器
5. 系统事件触发器：系统事件发生时触发，如数据库启动或关闭。
6. 注意：
7. 触发代码只能包含查询和操纵语句，不能有DDL和DCL，大小不能超过32K
8. 不能对SYS拥有的对象建立触发器
9. 建立触发器
10. 格式

create [or replace] trigger 触发器名
   触发时间 {before | after}           -- view 中是 instead of
   触发事件 {insert | update | delete} -- dml、ddl、database
on 触发对象                            -- table、view、schema、database 
   触发频率 {for each row}            -- 行级触发器。默认：语句级触发器
   [follows 其它触发器名]              -- 多个触发器执行的 前后顺序
   [when 触发条件]
BEGIN
触发代码；
END;

2. 多种DML语句

create [or replace] trigger 触发器名
   触发时间 {before | after}           -- view 中是 instead of
   触发事件 {insert | update | delete} -- dml、ddl、database
on 触发对象                            -- table、view、schema、database 
   触发频率 {for each row}            -- 行级触发器。默认：语句级触发器
   [follows 其它触发器名]              -- 多个触发器执行的 前后顺序
   [when 触发条件]
BEGIN
	CASE 
		WHEN INSERTING THEN
			....
		WHEN DELETING THEN
			....
			....
	END CASE;
END;

--不管
CREATE OR REPLACE  TRIGGER  触发器名
{BEFORE | AFTER | INSTEAD OF}  触发事件
[WHEN INSERT OR DELETE ...]
ON  对象名
--Referencing_clause ?新旧数据?
[FOR EACH ROW]  --行触发器对每一行都要触发代码

3. 注意：
   1. 多个DML触发器的执行顺序：先语句触发器后行触发器，先BEFORE后AFTER
   2. instead_of只针对视图使用，还要结合FOR EACH ROW使用
   3. 触发语句可以是DECLARE…BEGIN…END;也可以是CALL 存储过程
4. 语句触发器
5. 在表上执行某种DML操作就会自动触发代码。一般用于审计操作确保安全时使用，不能记录列数据的变化

CREATE OR REPLACE TRIGGER tr_sec_emp
BEFORE INSERT OR UPDATE OR DELETE ON t1
BEGIN
   IF to_char(sysdate,'DY','nls_date_language=AMERICAN') IN ('SAT','SUN') THEN
       raise_application_error(-20001,'不能在休息日改变职工信息');
   END IF;
END;
/

6. 行触发器
7. 执行DML操作时，每作用一行就要触发一次。可用于审计数据变化
8. 对新旧数据的引用方式：
   1. 用old修饰符引用旧数据，如 :old.sal
   2. new修饰符引用新数据，如 :new.depno
9. 注意：
   1. 行触发器不能从基表中读取数据，即定义触发器时ON 后面的对象，不能在后面执行代码中查询该对象

CREATE OR REPLACE TRIGGER zy.tr_insert_emp
AFTER INSERT ON zy.emp
FOR EACH ROW
BEGIN   
   UPDATE zy.dep SET empnum=empnum+1 
   WHERE depno=:new.depno;
END;
/

CREATE OR REPLACE TRIGGER zy.tr_delete_emp
AFTER delete ON zy.emp
FOR EACH ROW
BEGIN   
   UPDATE zy.dep SET empnum=empnum-1 
   WHERE depno=:old.depno;
END;
/

7. 替代触发器instead of
8. 只能针对视图，对视图中不能更新的列执行DML操作时使用
9. 查看触发器信息
10. 从数据字典视图中查看

select trigger_name, trigger_type,owner from all_triggers where trigger_name like 'TR%';

16. 自定义程序包(像类)

1. 将相关的存储过程、函数、变量常量、游标等PL/SQL块组合起来，方便程序重用。
2. 程序包分为：包规范(声明，公用)和包体(代码，私有)
3. 创建包规范（函数声明）

CREATE [OR REPLACE] PACKAGE 程序包名
IS|AS
   公用元素声明
END;

2. 创建包体（函数实现）

CREATE [OR REPLACE] PACKAGE  BODY 程序包名
IS|AS
   实际代码
END;

3. 示例
4. 调用包中的过程和函数
5. 调用包中元素的方式：包名.函数或过程名( )
6. 调用包中的过程，直接用exec调用

exec package_bmb.add_bmb('4001','采购部');

3. n调用包中的函数，用select或在表达式中调用

select package_bmb.find_bmh('4001') from dual;
--或
var v_bmm varchar2(20)
exec :v_bmm:=package_bmb.find_bmh('4001')

17. 异常处理

1. 处理PL/SQL块运行出错的情况
2. 预定义异常：
3. 非预定义异常
   1. 当违反预定义约束(如完整性约束)时系统会提示非预定义异常（比如主键为空）
   2. 这些异常只有编号没有名称
   3. 例如

DECLARE
   -- 定义非预定义异常的标识符
   e_fk EXCEPTION;
   -- 把Oracle错误与异常建立关联
   PRAGMA EXCEPTION_INIT (e_fk, -2292);
BEGIN
   DELETE FROM dept WHERE deptno = 10;
EXCEPTION
   -- 捕捉并处理异常
   WHEN e_fk THEN
      DBMS_OUTPUT.PUT_LINE('此部门下有员工,不能删除!');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLCODE || '***' || SQLERRM);
END;

3. 用户自定义异常
   1. 自定义异常与ORACLE错误无关，是程序员自定义的
   方法：声明异常+使用RAISE命令

DECLARE
   -- 定义一个异常
   e_custom EXCEPTION;
BEGIN
   -- 如果满足某个条件，则引发异常
   IF 1 = 1 THEN
      RAISE e_custom;
   END IF;
EXCEPTION
   -- 捕获并处理异常
   WHEN e_custom THEN
      DBMS_OUTPUT.PUT_LINE('自定义异常被触发');
END;

SET SERVEROUTPUT ON
DECLARE
   e_no_emp EXCEPTION;--声明异常
BEGIN
   UPDATE zy.emp SET sal=sal*1.2 WHERE id=&eno;
   IF SQL%NOTFOUND THEN
      RAISE e_no_emp;
   END IF;
EXCEPTION
   WHEN e_no_emp THEN 
      DBMS_OUTPUT.PUT_LINE('没有该职员!');
END;
/

  2.  格式 

EXCEPTION 
WHEN   表达式1 THEN
    语句1；
WHEN   表达式2  THEN
    语句2；
    …
WHEN  OTHERS THEN
    语句N+1；

2. 使用异常函数
3. 块中出现Oracle错误时使用异常函数
   1. SQLCODE：取得错误号
   2. SQLERRM：取得与之相关的错误消息

SET SERVEROUTPUT ON
UNDEFINE v_sal
DECLARE
   v_name zy.emp.name%TYPE;
BEGIN
   SELECT name INTO v_name FROM zy.emp
   WHERE sal=&&v_sal;
   DBMS_OUTPUT.PUT_LINE('职员名为:'||v_name);
EXCEPTION
   WHEN NO_DATA_FOUND THEN 
      DBMS_OUTPUT.PUT_LINE('没有工资为'||&v_sal||'的职员');
   WHEN OTHERS THEN 
      DBMS_OUTPUT.PUT_LINE('错误号:'||SQLCODE);
      DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/

  3.  RAISE_APPLICATION_ERROR过程：自定义从-20 000~ - 20 999错误号和错误消息 
     1.  格式：RAISE_APPLICATION_ERROR(自定义错误号，错误信息[，{TRUE|FALSE}]) 

SET SERVEROUTPUT ON
CREATE OR REPLACE PROCEDURE raise_addr
(eno VARCHAR2)
IS
   v_addr zy.emp.address%TYPE;
BEGIN
   SELECT address INTO v_addr FROM zy.emp
   WHERE id=eno;
   IF v_addr IS NULL THEN 
       RAISE_APPLICATION_ERROR(-20001,'该职员没有地址数据');
   END IF;
EXCEPTION
   WHEN NO_DATA_FOUND THEN 
      DBMS_OUTPUT.PUT_LINE('该职员不存在');
END;
/

六、模式对象

1. 表

1. 常用约束
   | 约束条件 | 关键字 |
   | — | — |
   | 主键 | PRIMARY KEY |
   | 自动增长 | AUTO_INCREMENT |
   | 不为空 | NOT NULL |
   | 唯一 | UNIQUE |
   | 逻辑条件 | CHECK |
   | 默认值 | DEFAUL |

3. 修改表：alter table

4. 增加字段：alter table 表名 add 新字段定义

alter table student add csrq date;

2. 增加主键、外键、check约束、唯一性约束：

ALTER TABLE 表名
ADD CONSTRAINT 约束名
FOREIGN KEY(外键列名)
REFERENCES 另一个表名(主键列名);
--或者
CREATE TABLE 表名(
	字段名 字段类型,
	...
	[CONSTRAINT] [外键名称] FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名)
);

--示例
alter table emp add constraint fk_emp_dept_id foreign key(dept_id) references dept(id);

3. 修改字段：
4. 删除字段：属于SYS的表不能删除其字段
5. 删除表：drop table 表名；注意删除顺序

添加字段：
ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释] [约束];
例：ALTER TABLE emp ADD nickname varchar(20) COMMENT '昵称';

修改数据类型：
ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);
修改字段名和字段类型：
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT 注释] [约束];
例：将emp表的nickname字段修改为username，类型为varchar(30)
ALTER TABLE emp CHANGE nickname username varchar(30) COMMENT '昵称';

删除字段：
ALTER TABLE 表名 DROP 字段名;

修改表名：
ALTER TABLE 表名 RENAME TO 新表名

删除表：
DROP TABLE [IF EXISTS] 表名;
删除表，并重新创建该表：
TRUNCATE TABLE 表名;

2. 索引

1. 索引：一个条目存放一个键值和Rowid
2. 加快查询
3. 自动为有unique或primary key约束的列产生索引
4. 类型

• B-树索引(B-tree index) ：默认类型
• 位映射索引(Bitmap index)：适于不同值数目很少的列
• B-树簇索引(B-tree cluster index)
• 哈希簇索引(Hash cluster index)
• 全局和本地索引(Global and local index)
• 基于函数的索引(Function-based index)
• 域索引(Domain index)

3. 创建索引：
   CREATE [ UNIQUE | FULLTEXT |clustered（聚集索引）] INDEX index_name ON table_name (index_col_name, ...);
   如果不加 CREATE 后面不加索引类型参数，则创建的是常规索引
4. 案例

-- name字段为姓名字段，该字段的值可能会重复，为该字段创建索引
create index idx_user_name on tb_user(name);
-- phone手机号字段的值非空，且唯一，为该字段创建唯一索引
create unique index idx_user_phone on tb_user (phone);
-- 为profession, age, status创建联合索引
create index idx_user_pro_age_stat on tb_user(profession, age, status);
-- 为email建立合适的索引来提升查询效率
create index idx_user_email on tb_user(email);

-- 删除索引
drop index idx_user_email on tb_user;

5. 查询索引信息
6. 通过查询DBA_INDEXES/ALL_INDEXES等视图
7. 查询USER_INDEXES查看用户可以使用的索引
8. 修改与删除索引
9. 修改索引
   1. 修改索引存储参数
   2. 索引改名
   3. 不能为索引增加列
   4.

在Oracle数据库中，索引的存储参数（STORAGE属性）是用来设置索引生成的辅助表的物理参数的1。这些参数可以影响索引的性能和存储效率。

Oracle的全文索引会生成一张或多张辅助表，由于这些表是Oracle自动生成的，用户没有办法直接设置这些表和索引的物理参数，因此Oracle提供了STORAGE属性，专门设置这些辅助表和索引的物理参数1。

例如，你可以使用ALTER INDEX语句来修改索引的存储参数，如下所示：

SQL

ALTER INDEX index_name STORAGE (initial 1m next 512k);
AI 生成的代码。仔细查看和使用。 有关常见问题解答的详细信息.
在这个例子中，initial 1m设置了索引的初始存储空间为1MB，next 512k设置了索引的下一个存储空间为512KB1。

请注意，修改存储参数可能会影响数据库的性能，因此在执行这些操作时应谨慎23。在修改索引之前，最好先备份数据库，以防止数据丢失。如果你不确定如何进行，建议寻求专业人士的帮助。希望这个信息对你有所帮助！

2. 删除索引
   1. 不需要该索引时
   2. 未达到预期优化目标
   3. 索引碎片化较严重可以删除

3. 聚簇Cluster

1. 作为容纳表的容器：将经常被同时访问的多个表按物理位置存储在一起
2. 表簇：由若干共享相同数据块的表构成
3. 同一表簇中各表相关的列称为簇键
4. 簇键用簇索引进行索引
5. 簇键值只存储一次，节省空间
6. 将簇中各表相关的列存放在一个数据块中，能加快连接查询
7. 适合建立表簇的条件：
8. 主要执行查询操作的表，常进行连接查询的多个表
9. 格式

CREATE  CLUSTER   [模式.]表簇名称 (列名1 数据类型 [,列名2 数据类型]…)
[物理属性组句]
[SIZE 数目 [K|M]]
[TABLESPACE 表空间名称]
[存储属性组句];

列名：簇中各表相关的列，即簇键
SIZE：估计簇键的个数及存储空间
如很少记录共用一个键值,则SIZE值要选择较小

5. 三个步骤
6. 前提

如empl表和dep表中都有depno字段，用于连接，则可以把两表生成表簇empl_dep，簇键为depno, Oracle将emp.depno和dep.depno放在同一数据库内

2. 创建表簇

CREATE CLUSTER empl_dep(depno varchar2(4))
SIZE 500
TABLESPACE users
STORAGE(INITIAL 100K
NEXT 200K
MINEXTENTS 2
MAXEXTENTS 20
PCTINCREASE 33);


MINEXTENTS：这个参数定义了在创建对象时要分配的最小区段数。在您的例子中，MINEXTENTS 2表示在创建集群时，至少会分配两个区段。

MAXEXTENTS：这个参数定义了对象可以拥有的最大区段数。在您的例子中，MAXEXTENTS 20表示这个集群最多可以拥有20个区段。

PCTINCREASE：这个参数定义了每个新区段的大小相对于前一个区段的大小的增长百分比。在您的例子中，PCTINCREASE 33表示每个新区段的大小将比前一个区段的大小大33%。

3. 创建簇内表

--先创建dep表，再创建empl表（先主再外）
CREATE TABLE dep
( depno varchar2(4) PRIMARY KEY,
  dname varchar2(10) NOT NULL,
  rs NUMBER
)
CLUSTER empl_dep(depno);

CREATE TABLE empl
(eno varchar2(10) PRIMARY KEY,
 ename varchar2(10) NOT NULL,
 esex varchar2(2),
 depno varchar2(4) NOT NULL REFERENCES dep
)
CLUSTER empl_dep(depno);

4. 创建簇索引

CREATE INDEX empl_dep_index
ON CLUSTER empl_dep
TABLESPACE users;

6. 查看聚簇
7. 在dba_clusters/all_clusters/user_clusters等表或视图中查看聚簇信息

select cluster_name,tablespace_name,key_size,cluster_type
from USER_CLUSTERS;

7. 修改聚簇：alter cluster

ALTER CLUSTER empl_dep SIZE 600;

8. 删除聚簇：drop cluster 聚簇名 [子句]
9. Including tables子句：连带删除簇中的表

4. 视图

1. 视图是虚拟的表，不保存数据，但是可以借助视图查询表中的数据，也可以通过视图对基表的数据做修改
2. 创建视图：create view 视图名 as 查询语句;
3. 查看视图信息：从数据字典中查看
   1. dba_views / all_views / user_views等
4. 修改视图：alter view …;
5. 修改后要重新编译：alter view 视图名 compile;
6. 重建视图：create or replace view as…
7. 删除视图：drop view 视图名;
8. 示例

create view v_yggz
as select
department_id,employee_id,first_name,salary
where salary > 2000

5. 同义词synonym

1. 同义词：对象的别名
2. 定义：从字面上理解就是别名的意思，和视图的功能类似。就是—种映射关系
3. 公有同义词
4. 私有同义词
5. 格式

CREATE [PUBLIC] SYNONYM [模式.]同义词名称
FOR [模式.]数据库对象名称 ;

访问其它数据库时
create synonym table_name for table_name@DB_Link;

3. 示例

create SYNONYM SCCOTT.employee
for SCOTT.emp;

4. 删除同义词 drop [public] synonym table_name
5. 查看所有同义词 select * from dba_synonyms;
6. 好处：节省大量的数据库空间，对不同用户的操作同一张表没有多少差别;扩展的数据库的使用范围，能够在不同的数据库用户之间实现无缝交互;同义词可以创建在不同一个数据库服务器上，通过网络实现连接。
7. 表、视图、序列、过程、函数、程序包，甚至其它同义词都可以创建同义词。

6. 序列sequence

1. 定义：用于产生数字类型的唯一序列号，被多个用户使用
2. 好处
3. 序列不占I/O开销，可提高事务处理吞吐量
4. 序列号可用于作为不重复的ID值插入到表中，方便insert操作
5. 格式

CREATE SEQUENCE [模式.]序列名称
[START WITH 起始数字]
[INCREMENT BY 增量] --可以为负数
[MAXVALUE 最大值|NOMAXVALUE] --默认无最大值
[MINVALUE 最小值|NOMINVALUE] --默认无最小值
[CYCLE|NOCYCLE] --是否循环
[CACHE 数目|NOCACHE]
[ORDER|NOORDER];


[CACHE 数目|NOCACHE]：这个选项用于指定预先在内存中缓存的序列号的数量。如果指定了一个缓存大小，例如50，那么数据库并不会缓存50个单独的值。它只缓存当前的值和缓存中剩余的值的数量。这意味着，存储缓存所需的内存量始终为序列对象的数据类型的两个实例。这可以提高性能，因为每次需要一个新的序列号时，数据库不需要去磁盘上读取。如果指定了NOCACHE，则每次需要新的序列号时，都会进行磁盘I/O操作。如果数据库在使用了一部分缓存的序列号后停止，那么在数据库重新启动并需要一个新的序列号时，将从系统表中读取起始编号。这可能会导致序列中出现间隙，但可以保证不会两次为单个序列对象发出同一个值。

[ORDER|NOORDER]：这个选项用于指定是否要求序列生成的数字是有序的。如果指定了ORDER，在一个RAC环境中，多个实例将协调以生成有序的序列号。这可能会降低性能。如果指定了NOORDER，则每个实例将独立地生成序列号，这可能会导致生成的序列号是无序的，但性能会更好。这个选项通常在需要严格有序的序列号时使用ORDER，在不需要严格有序的情况下使用NOORDER以获得更好的性能。请注意，即使指定了NOORDER，生成的序列号也总是递增的，只是可能不是连续的。

7. OEM中操作模式对象

七、安全管理

1. 用户帐号管理

1. 设置用户帐号是第一道防线，比如
2. 数据库认证：帐号和口令，密码管理
3. 外部认证：操作系统或网络服务，Oracle Net
4. 全局认证：目录服务
5. 代理认证：中间层服务器授权用户
6. 创建用户帐号
7. 格式

CREATE   USER    用户名
IDENTIFIED { BY 密码 | EXTERNALLY | GLOBALLY AS '外部名'}
[ { DEFAULT TABLESPACE 默认表空间名称
  TEMPORARY TABLESPACE临时表空间名称
  QUOTA  { 数目 [ K | M ] | UNLIMITED } ON表空间名称 --给表空间分配大小
  PROFILE 用户配置文件
  PASSWORD EXPIRE --密码是否过期 EXPIRE NEVER永不过期 EXPIRE INTERVAL 数字 DAY 多少天后过期
  ACCOUNT { LOCK | UNLOCK }
 } 
];
--identified：选择认证方式：密码方式(密码以字母开头)、外部认证、全局用户
--外部认证：外部认证是指数据库服务器将用户身份验证任务委托给外部服务（如操作系统或第三方服务）。用户在尝试连接到数据库时，不需要提供数据库密码，因为他们已经通过外部服务进行了身份验证。

--全局用户的外部认证：全局用户是在网络目录服务中定义的数据库用户。全局用户的外部认证允许全局用户通过外部服务进行身份验证，而不是通过数据库。这样，全局用户可以在多个数据库中使用相同的身份凭据进行身份验证。

--全局用户：全局用户是在网络目录服务（如LDAP）中定义的数据库用户。全局用户可以在多个数据库中使用，而无需在每个数据库中创建单独的用户账户。这可以简化用户管理，特别是在大型环境中。

3. 示例
4. 先以具备创建用户权限的帐号登录数据库
5. 用命令创建用户
6. 为用户授予“create session”的权限，用户就可以连接登录到数据库了

create user testuser identified by testuser
default tablespace USERS
temporary tablespace TEMP
quota unlimited on USERS;

grant create session to testuser;

4. 一些小建议
5. 初始建立的数据库用户没有任何权限.不能执行任何数据库操作.
6. 10g以前，如果建立用户时不指定DEFAULT TABLESPACE子句，oracle会将SYSTEM表空间作为用户默认表空间。而10g开始将user表空间作为默认表空间，从而避免了占用系统表空间
7. 如果建立用户时不指定TEMPORARY TABLESPACE子句，oracle会将数据库默认临时表空间temp表空间作为用户的临时表空间。
8. 如果建立用户时没有为表空间指定QUOTA子句，那么用户在特定表空间上的配额为0，用户将不能在相应表空间上建立数据对象。
9. 外部认证方式：使用OS用户登录
10. 外部认证：操作系统认证方式，用户登录时不需要输入用户名和密码，采用conn / [@server]
11. 基本步骤：
    1. 在OS中建立一个用户，该用户属于ora_dba组
    2. Oracle中一些相关参数的设置
    3. 注册表中增加一字符串值
    4. 在Oracle中建立一个同名用户，无需设置密码
    5. 为该用户授权
    6. 注销windows以新建用户登录OS
12. 举例
    1. 在控制面板-管理工具-计算机管理的本地用户和组中，新建或使用已有OS用户xm，在“组”中找到ora_dba组，将xm加入到组中
    操作完即可注销后，以xm登录OS

1. 1. 1. Oracle下的操作
      2. 查看参数，这里的参数一般已经设置好了C:\oracle\product\10.2.0\db_1\NETWORK\ADMIN下的文件sqlnet.ora，其中参数：
         1. SQLNET.AUTHENTICATION_SERVICES= (NTS)：表明用户连接Oracle服务器时使用哪种验证方式，
            • NONE表示Oracle数据库身份验证
            • NTS表示操作系统身份验证，两种方式可以并用
         2. NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)：表明解析客户端连接时所用的主机字符串的方式：
            • TNSNAMES表示采用TNSNAMES.ORA文件来解析
            • ONAMES表示Oracle使用自己的名称服务器来解析
            • HOSTNAME表示使用host文件，DNS，NIS等来解析
      3. 使用SYS用户登录SQLPLUS
      4. 为创建Oracle下同名用户做准备
         1. 查看os_authent_prefix参数的值，若值为OPS$，则用户名要定为OPS$主机名操作系统用户名，若值为空，则用户名只要取与OS用户同名即可，故为了方便，更改为空
         2. 
      5. 修改注册表: 在注册表的HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDB10g_home1下添加一个字符串值AUTH_PREFIX_DOMAIN ，值为false
      6. 创建同名Oracle用户xm
      7. 用户配置文件
      8. 创建用户时可以指定配置文件，若不指定则使用Oracle默认用户配置文件
      9. 用户配置文件作用：
         1. 限制用户可使用系统和数据库的时间、资源数目、密码持有时间等
         2. 通过设置参数实现
         3. 在创建用户时用PROFILE子句指定
      10. 格式

CREATE   PROFILE   用户配置文件名称   LIMIT
[SESSIONS_PER_USER { 数目 | UNLIMITED | DEFAULT } ]
[CPU_PER_SESSION {数目 | UNLIMITED | DEFAULT }]
… ;

SESSIONS_PER_USER { 数目 | UNLIMITED | DEFAULT }：这个选项用于限制每个用户可以拥有的会话数。你可以设置一个具体的数目，或者使用UNLIMITED表示不限制，或者使用DEFAULT表示使用默认设置。
CPU_PER_SESSION {数目 | UNLIMITED | DEFAULT }：这个选项用于限制每个会话可以使用的CPU时间（以秒为单位）。你可以设置一个具体的数目，或者使用UNLIMITED表示不限制，或者使用DEFAULT表示使用默认设置。

     4.  示例 

--例如，你可以使用以下命令创建一个名为profile1的配置文件，该配置文件允许每个用户拥有无限制的会话数，但每个会话的CPU时间不能超过3000秒：

CREATE PROFILE profile1 LIMIT
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION 3000;

  6.  默认的数据库用户 
     - 四个默认用户 
        - SYS：系统数据字典所有基表和用户可访问的视图。不能删除该用户下的表的字段，对该用户下的表不能创建触发器。可以不输入密码登录，要严格限制使用。
        - SYSTEM：DBA用户，可以创建各类对象。要严格限制使用，建议另行创建具备DBA权限的其他用户。
        - SYSMAN：OEM超级管理员用户
        - DBSNMP：在OEM中监视数据库的用户
  7.  修改用户帐号 
     1.  格式 

ALTER   USER   用户名
[IDENTIFIED] { BY 密码 | EXTERNALLY | GLOBALLY AS '外部名'}
[ { DEFAULT TABLESPACE 默认表空间名称
    | TEMPORARY TABLESPACE临时表空间名称
    | QUOTA  { 数目 [ K | M ] | UNLIMITED } ON表空间名称
    | PROFILE 用户配置文件
    | PASSWORD EXPIRE
    | ACCOUNT { LOCK | UNLOCK }
   }
];

  8.  锁定用户帐号与解锁 
     1. 锁定帐户后，用户不能登录到数据库
     2. 创建的新帐号默认状态下是未锁定的
     3. 格式：`ALTER USER 用户名 ACCOUNT { LOCK | UNLOCK };`
  9.  查看数据库中用户的信息 

--用户及配置信息保存在数据字典中

SELECT username ,profile,account_status FROM DBA_USERS ;

2. 权限管理

1. 分类：系统权限和对象权限
2. 授予系统权限命令：

GRANT {系统权限1  [，系统权限2，…] | ALL [PRIVILEGES ] }
TO { 用户名1 [，用户名2，…] | PUBLIC }
[ WITH ADMIN OPTION ] ;

grant create session ,resource to TESTUSER

1. 连接到数据库并创建会话的权限：create session
2. 可以创建、使用、删除对象的权限：resource
3. 系统权限中带有ANY表示可以在所有模式下创建对象，如create any table和create table的差别
4. 撤销系统权限

REVOKE  系统权限1  [，系统权限2，…] | ALL [PRIVILEGES ] }
FROM   { 用户名1 [，用户名2，…] | PUBLIC };

4. 查看系统权限
5. 通过查询数据字典视图XXX_SYS_PRIVS,可以显示用户所具有的系统权限
   1. GRANTEE用于标识权限拥有者(用户或角色), PRIVILEGE用于标识系统权限.
   2. ADMIN_OPTION用于标识系统权限传授选项
      • 其中YES表示可以转授该系统权限，NO表示不能转授该系统权限

此外，数据字典视图system_privilege_map可以显示所有系统权限，session_privs可以显示当前会话所具有的系统权限

5. 对象权限授予
6. 格式

GRANT {对象权限1  [，对象权限2，…] | ALL [ PRIVILEGES ] }
ON { [ 模式名.]数据库对象 [ ( 列名1 [，列名2，…] ) ] | DIRECTORY 目录名称 }
TO { 用户名1 [，用户名2，…] | PUBLIC }
[ WITH GRANT OPTION ]
[ WITH HIERARCHY OPTION];

2. 授予列权限，要在权限名后用( )引用列名
3. 撤销对象权限
4. 撤销对象权限：revoke…on…from 用户名只能从整个对象收回权限，不能从列上收回
5. 查看对象权限
6. 数据字典视图DBA_COL_PRIVS：显示所有用户或角色的列权限信息.
7. 数据字典视图ALL_COL_PRIVS：显示当前用户或PUBLIC的列权限信息;
8. 数据字典视图USER_COL_PRIVS：显示当前用户的列权限信息
9. 

3. 角色管理

1. 角色是权限的命名集合，使用角色的主要目的是简化权限管理
2. 预定义角色：Oracle提供的角色, 在建立数据库,安装数据字典视图和PL/SQL包时建立的,并且每种角色都用于执行一些特定管理任务。
3. CONNECT：基本用户角色
4. RESOURCE：典型应用程序开发人员
5. DBA：管理员组用户
6. 等等
7. 创建角色
8. 格式

CREATE  ROLE  角色名称
[ NOT IDENTIFIED | IDENTIFIED  { BY 密码 | USING [模式.] 包 
  | EXTREMELY | GLOBALLY }
];

NOT IDENTIFIED：指定非验证方式，默认
IDENTIFIED：
BY 密码：建立本地角色
USING [模式].包：创建应用程序角色

2. 示例
3. 给角色授权
4. 格式与为用户授权类似
5. 注意：
   1. 系统权限UNLIMITED TABLESPACE和对象权限的WITH GRANT OPTION 选项不能授予角色
   2. 不能用一条GRANT语句同时授予系统权限和对象权限
   3.

在Oracle数据库中，ADMIN OPTION是一个选项，允许被授权者可以授权、修改、删除这个角色。这意味着，如果你使用ADMIN OPTION为某个用户授予角色，那么这个用户就可以将这个角色授予其他用户。

与ADMIN OPTION有相似功能的是WITH GRANT OPTION。这个选项允许被授权者将某个权限或角色授予其他用户。但是，这两个选项在权限回收时的行为是不同的：

如果使用ADMIN OPTION为某个用户授予角色，然后这个用户将角色授予其他用户，当你收回这个用户的角色时，其他用户仍然拥有这个角色。

如果使用WITH GRANT OPTION为某个用户授予权限或角色，然后这个用户将权限或角色授予其他用户，当你收回这个用户的权限或角色时，其他用户的权限或角色也会被收回。

3. 示例
4. 将角色授予用户或其他角色
5. 命令格式与授予系统权限格式一致
   
6. 启用和禁用角色
7. 启用角色：使角色具有的权限生效
8. 禁用角色：使角色具有的权限临时失效
9. 格式

SET  ROLE 
{ 角色1 [ IDENTIFIED BY 密码 ] [ , 角色2 [ IDENTIFIED BY 密码 ] …]
   | ALL [ EXCEPT 角色1 [, 角色2, … ] ]
   | NONE  
 };


--列出的角色表示启用的
--未列出的角色表示被禁用
--ALL表示全部启用
--NONE表示全部禁用

4. 定义角色时设了密码的，启用时必须输入密码,例如 set role private_role identified by private
5. 修改角色：alter role…
6. 从角色中撤销角色或权限
7. 与撤销系统权限和对象权限一致
8. 查看角色信息
9. 显示用户所具有的角色及默认角色
   1. DBA_ROLE_PRIVS：显示用户或角色所具有的角色信息.
   2. ROLE_ROLE_PRIVS：显示角色具有的其他角色信息
   3. USER_ROLE_PRIVS：显示当前用户所具有的角色.
10. 

4. OEM的安全性

八、存储结构管理

1. 表空间管理

1. 表空间概述
2. 表空间是数据库的逻辑组成部分
   1. 从物理上讲，数据库存储在数据文件中；
   2. 从逻辑上讲，数据库则存储在表空间中。
   3. 一个数据库由多个表空间组成，而一个表空间又由一个或多个数据文件组成
3. 表空间的作用：
   1. 可以控制数据库占用的磁盘空间；
   2. 控制用户所占用的空间配额；
   3. DBA可以部署数据到不同位置，提高I/O性能，有益于备份和恢复操作
4. 典型表空间
5. 系统表空间system tablespace：400MB
   1. 必须具有的默认表空间，包括数据字典、数据库对象、系统回退段
6. 临时表空间temp tablespace：100MB，存在排序段
7. 用户表空间users tablespace：120MB
8. 撤销表空间undo tablespace：250MB
   1. 只能有回退段，使用DBCA创建数据库时，一般会自动建立一个默认的撤销表空间UNDOTBS
9. 索引表空间、工具表空间
10. SYSAUX表空间：系统表空间的辅助表空间，存放产品和特征信息，不能删除和改名该表空间
11. 查看表空间信息
12. 查看数据字典
13. 查看相关表或视图：
    • DBA_TABLESPACES：查看表空间信息
    • DBA_DATA_FILES：显示所有属于表空间的数据文件
    • V$TABLESPACE：从控制文件中查看表空间的名称和数量
    • V$DATAFILE：查看所有数据文件信息，及其对应的表空间
14. 查看表空间的名称、状态、分区分配类型和表空间特性
15. 查看数据文件及其所属表空间，是否自动扩展大小
16. 查看表空间号、名称和是否闪回
17. 查看文件编号，名称，表空间号, 创建时间
18. 创建表空间
19. 用OEM创建
20. 命令方式

CREATE   [UNDO]  TABLESPACE   表空间名
DATAFILE    
‘文件名’   [SIZE 数目 [K|M]]   [REUSE]
[AUTOEXTEND  { ON|OFF [NEXT 数目[K|M]] 
[MAXSIZE {UNLIMITED | 数目 [K|M]}] } ]
[，其他数据文件]  - -可包含多个数据文件，用“，”分隔
[ONLINE | OFFLINE]
[PERMANENT | TEMPORARY]  - -永久|临时
[MANAGEMENT  LOCAL | DICTIONARY]  - -本地管理|字典管理

不设置子句Autoextend Maxsize 具体数目，则采用默认值 UNLIMITED
选项REUSE将重新使用一个已经存在的文件或文件不存在时创建

3. 示例
   
4. 为表空间中的对象设置默认存储值
5. 在表空间中建立对象(如表)时，若没有指定特定参数则按照DEFAULT STORAGE字句中设置的默认存储值存放

CREATE  TABLESPACE  表空间名
DATAFILE   ‘文件名’   SIZE   n K|M
DEFAULT  STORAGE（
INITIAL int  K | M       - -为新对象分配的第一个区的尺寸，单位是字节。只能定义时使用，不能使用ALTER修改。             
NEXT int K | M    - -为新对象分配的第二个区的尺寸，通常与INITIAL相同
MINEXTENTS int         - -创建新对象时为其分配的初始区个数，多为1。
MAXEXTENTS int        - -指定该段最大可以包含的区数目。
MAXEXTENTS UNLIMITED      - -指定该段可以包含的区数目不受限制。                  
PCTINCREASE int       - - 适用于字典管理表空间，指定从第三个区开始，每个区比前一个区尺寸增长的百分比。本地管理则取值为0
）;

7. 修改表空间属性
8. ALTER TABLESPACE 表空间名 属性值;
   • COALESCE：合并
   • RENAME DATAFILE ‘filename’ TO ‘filename’ ：重命名
   • READ {ONLY | WRITE} ：只读、可写
   • PERMANENT | TEMPORARY：表空间生命周期
   • LOGGING | NOLOGGING：日志选项，对大索引可以选择NOLOGGING
   • [NO] FORCE LOGGING：强制日志选项选项
9. 设置表空间脱机/联机：ALTER TABLESPACE 表空间名 OFFLINE [NORMAL|TEMPORARY|IMMEDIATE];
10. 默认为NORMAL，另外两种方式在联机前需要介质恢复 ALTER TABLESPACE 表空间名 ONLINE;
11. 更改表空间联机与否举例
12. 设置表空间只读/可写

ALTER TABLESPACE  表空间名 READ ONLY; --可以删除对象
ALTER TABLESPACE  表空间名 READ WRITE;

2. 修改表空间的可读性前提要求：联机
3. 只读表空间：
   1. 可以删除其中对象DROP，不能创建或修改对象CREATE、ALTER
   2. 不能使system表空间或活动undo表空间只读
4. 在V$DATAFILE视图中查看数据文件的状态
5. 设置只读表空间举例
6. 删除表空间

DROP TABLESPACE  表空间名；

DROP  TABLESPACE  表空间名 
      [
       INCLUDING CONTENTS   - -删除表空间上包含的对象
       [AND DATAFILES]             - -删除数据文件
       [CASCADE CONSTRAINTS]    - -删除相关索引对象
      ];

2. 撤销表空间管理

1. UNDO表空间：用于保存回滚数据库变化所需的信息
2. 撤销：
3. ROLLBACK回退事务
4. 恢复数据库
5. 闪回查询
6. 手工管理方式：用system表空间的回滚段
7. 自动管理：9i以后引入自动撤销管理
8. 初始化参数UNDO_MANAGEMENT=AUTO
9. 创建撤销表空间
10. 创建命令和一般表空间一样
    1. 不能设置PERMANET/TEMPORARY属性
11. 切换撤销表空间
12. 存在多个撤销表空间时，某时刻只有一个处于活动状态
    1. 表空间必须是撤销表空间，且没有被其他实例使用
13. 监视表空间
14. 长时间运行查询引起了“快照过旧”错误
    1. 在V$UNDOSTAT中查看实例每10分钟收集一次的统计数字

3. 区间管理

1. 一个表空间包括
2. 多个数据区间
3. 一个或多个自由区间块
4. 区间分配方式
5. 动态分配：容易导致额外开销，损害I/O性能
6. 手工区间分配
   • 预分配表空间
   • 预分配表
7. 查看表空间的利用率
8. 从dba_data_files和dba_free_space视图中查看

SELECT a.file_id "FileNo",
       a.tablespace_name "Tablespace_name", 
       a.bytes "Bytes", 
       sum(nvl(b.bytes,0)) "Free", 
       sum(nvl(b.bytes,0))/a.bytes*100 "%free"  
FROM dba_data_files a, dba_free_space b 
WHERE a.file_id=b.file_id(+) 
GROUP BY a.tablespace_name,a.file_id,a.bytes 
ORDER BY a.tablespace_name;

4. 整理自由空间碎片
5. 分散较小的自由空间即碎片，会影响系统性能
6. 通过计算fsfi自由空间碎片索引值来选择是否整理碎片
   1. fsfi值=100是理想值，当fsfi<30时需要碎片整理
   2. 由系统监控后台进程SMON自动合并，要求表空间的pctincrease值为非0
   3. 手工合并：ALTER TABLESPACE 表空间名 COALESCE；
7. 举例

REM fsfi value compute
REM fsfi.sql
col fsfi format 999,99
SELECT tablespace_name,
       sqrt(max(blocks)/sum(blocks))*(100/sqrt(sqrt(count(blocks)))) fsfi
FROM dba_free_space
GROUP BY tablespace_name 
ORDER BY 1; --查看空间碎片

ALTER TABLESPACE tablespace_name COALESCE --合并指定表空间钟连续的空闲空间

5. 补充
6. 在Oracle数据库中，可以通过以下方法查看表空间和表的碎片：
   1. 查看表空间碎片：

SELECT a.tablespace_name, 
       trunc(sqrt(max(blocks)/sum(blocks))* (100/sqrt(sqrt(count(blocks)))),2) fsfi 
FROM dba_free_space a,dba_tablespaces b
WHERE a.tablespace_name=b.tablespace_name 
      AND b.contents not in ('TEMPORARY','UNDO','SYSAUX')
GROUP BY A.tablespace_name 
ORDER BY fsfi;

  2. 如果FSFI小于30，则表空间碎片太多12。
  3. 查看表碎片：

SELECT table_name, 
       ROUND ( (blocks * 8), 2) "高水位空间 k", 
       ROUND ( (num_rows * avg_row_len / 1024), 2) "真实使用空间 k", 
       ROUND ( (blocks * 10 / 100) * 8, 2) "预留空间 (pctfree) k", 
       ROUND ( ( blocks * 8 - (num_rows * avg_row_len / 1024) - blocks * 8 * 10 / 100), 2) "浪费空间 k" 
FROM user_tables 
WHERE temporary = 'N' 
ORDER BY 5 DESC;

  4.  这个查询会返回每个表的高水位空间、真实使用空间、预留空间和浪费空间。 
  5.  查看索引碎片： 

SELECT index_name, leaf_blocks, distinct_keys, 
       leaf_blocks/distinct_keys AS fragmentation 
FROM dba_indexes 
WHERE owner = 'SCHEMA_NAME' 
      AND table_name = 'TABLE_NAME';

这个查询会返回每个索引的碎片率。

九、RMAN工具

1. 准备工作
2. 首先设置Oracle为自动归档模式
   1. SQL>alter database archivelog;
   2. 查看
3. 创建恢复表空间
4. 创建恢复用户并授权
   
5. 退出SQLPlus，进入rman，创建恢复目录
6. 退出后重新注册连接目标数据库
7. 选择有恢复目录的连接方式
8. 完全备份
9. 完全恢复(在归档模式下)
10. 模拟介质故障，先关闭数据库和实例，再在安装目录下，删除或移动user01.dbf数据文件
11. 重新打开实例和数据库，用startup mount命令，此时数据库仅装载而未打开，因为缺少一个数据文件
12. RMAN重新连接目标数据库
13. 因为此时目标数据库无法打开，则需如下登录rman
14. 执行恢复命令
15. 导入导出工具——数据泵
16. 导出EXPDP：5种导出模式
    1. 创建导出目录及给用户授权
       1. 创建导出目录，该目录不会自动建立，要在OS中手动创建
    2. 给用户授予读写权限
    3. 可以用select * from dba_directories查看目录是否创建成功
17. 导出举例
18. 导出scott用户的emp表和dept表
19. 导入IMPDP
20. 创建用户及授权
21. 对应于导出，也有五种导入模式
    - 导入表
    - 导入整个模式
    - 导入表空间
    - 导入数据库
    - 导入传输表空间
22. 导入scott用户的emp和dept表到study用户中
    

问答篇

1. 简述Oracle文件系统的物理文件分类和逻辑结构划分
   1. 物理文件分类：数据文件、控制文件、日志文件、归档文件、参数文件、密码文件
   2. 逻辑结构划分：见体系结构第5节
2. Oracle实例的内存结构分为哪两个区域？其中SGA是如何划分的？
   1. 分为SGA（系统全局区）、和PGA（程序全局区）
   2. SGA主要由数据高速缓冲区（Database Buffer Cache）、共享池（Shared Pool）、重做日志缓冲区（Redo Log Buffer）、大型池（Large Pool）、Java池（Java Pool）、流池（Streams Pool）和其他结构（如固定SGA、锁管理等）组成
3. Oracle后台进程中哪些是必选进程？
   1. SMON：系统监控进程
   2. LGWR ：写日志进程
   3. CKPT：检查点
   4. PMON ：进程监视器
   5. DBWR ：数据库写入进程

补充

-- 查看所有用户
SELECT * FROM dba_users;
SELECT * FROM all_users;
SELECT * FROM user_users;

-- 查看用户或角色系统权限 (直接赋值给用户或角色的系统权限)
SELECT * FROM dba_sys_privs;
SELECT * FROM user_sys_privs; -- 查看当前用户所拥有的权限

-- 查看角色 (只能查看登陆用户拥有的角色)所包含的权限
SELECT * FROM role_sys_privs;

-- 查看用户对象权限
SELECT * FROM dba_tab_privs;
SELECT * FROM all_tab_privs;
SELECT * FROM user_tab_privs;

--插入多条数据
INSERT ALL 
INTO table1 (column1, column2) VALUES ('value1', 'value2')
INTO table2 (column1, column2, column3) VALUES ('value1', 'value2', 'value3')
SELECT * FROM dual;

explain plan for select * from StudentInfo where SCORE=160;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

在执行计划的结果中，TABLE ACCESS FULL表示全表扫描，而INDEX UNIQUE SCAN、INDEX RANGE SCAN、INDEX FULL SCAN和INDEX FAST FULL SCAN等表示使用了索引12。