文章深入解析了Oracle数据库的逻辑与物理结构，涵盖数据块、区间、段、表空间等关键组件。进一步讲解SQL语言的应用，包括子查询、聚合函数的高级用法，以及游标与存储过程、PL/SQL编程技巧，为数据库管理和数据操作提供了全面指导。
Oracle笔记
oracle数据库基本结构
逻辑结构
逻辑结构主要是从数据库使用者的角度考查数据库的组成
ORACLE的逻辑结构是由一个或多个表空间组成
说明：
- 一个数据库由若干表空间组成
- 一个表空间由逻辑对象组成
- 一个逻辑对象由多个数据段组成
- 一个数据段由一批数据区间组成
- 一个数据区间由若干个连续的数据块组成
- 一个数据块对应一个或多个物理块
数据块（Data Block）
是数据库使用的I/O最小单元，又称逻辑块或ORACLE块，常用大小为2KB或4KB（是OS块的倍数）
一个数据块对应一个或多个物理块，块的大小由参数DB_BLOCK_SIZE确定（init.ora）
区间（Extent）
是数据库存储空间分配的逻辑单位。
一个区间由一组数据块组成，区间是由段分配的，分配的第一个区间称初始区间，以后分配的区间称增量区间
段（Segment）——ORACLE的逻辑结构
是表空间中一个指定类型的逻辑存储结构
由一个或多个区间组成，段将占用并增长存储空间
表空间（Table spaces）
数据库的逻辑组成部分，对应一个或多个数据文件
表空间的大小是它所对应的数据文件大小的总和
注意：
- 一个表空间只能同时属于一个database
- 一个表空间可能包含一个或多个段，但段不能跨越表空间
物理结构
数据文件
用于物理存储ORACLE数据库数据，如：系统数据、用户数据等
特点：
- 每一个数据文件只与一个数据库相联系
- 在oracle数据库中，至少要包含一个数据文件
- 在数据库空间不足时，数据文件具有自动扩展的特性
- 一个或多个数据文件构成数据库的逻辑存储单元叫做表空间（table space）
数据库文件或数据文件：
- 它们是物理操作系统文件，它们包括诸如用户数据、系统数据等所有数据库数据
- 数据文件中的数据以块为单位
- 任何数据库创建时至少包含一个数据文件
日志文件
重做日志文件（ReDo Log File）：
记录所有对数据库数据的修改，以备恢复数据时使用
- 每一个数据库包含多个日志文件
- 日志文件以循环方式进行写操作
- 每一个日志文件成员对应一个物理文件
- 工作模式为：归档模式和非归档模式
日志切换（Log Switch）
为实现日志文件的循环使用而设置的
出现日志切换的情况：
- 当一个日志文件被填满时
- 关闭数据库时
- DBA手动转换日志开关
日志文件写操作图示：
注意：
- 只有在归档模式下系统才会备份日志文件，当oracle切换日志时备份日志文件，oracle缺省状态是非归档模式
- 只有在归档模式下日志文件才能用于恢复，否则在线日志并未备份
- 日志文件通常有镜像，因此通常是日志文件组，每个组中可以有多个镜像文件
控制文件
一个较小的二进制文件，用于记录和维护数据库的物理结构
描述信息：
- 数据库名称及创建时间
- 数据文件的位置及大小
- 重做日志文件的位置及大小
- 日志序列号
要点注意：
- 在打开和存取数据库时都访问该文件
- 数据库至少包含一个控制文件
- 镜像控制文件
- 记录控制文件名及路径的参数为：CONTROL_FILES
控制文件把Oracle引导到数据库文件的其他部分，启动一个实例时，Oracle从参数文件中读取控制文件的名字和位置
安装数据库时，Oracle从控制文件中读取数据文件的列表并打开其中的每个文件
参数文件
一个文本文件，可直接使用操作系统下的文本编辑器对其内容进行修改。该文件只在建立数据库或启动实例时才被访问，在修改该文件之前必须关闭实例
文件类型：
- 初始参数文件：init.ora
- 生成参数文件：initSID.ora config.ora
任意一个实例都对应一个初始化文件init.ora，它决定实例的大小及组成参数
参数文件的作用：
- 确定存储结构的大小
- 设置数据库的全部缺省值
- 设置数据库的区间
- 设置数据库的各种物理属性
- 优化数据库性能
参数文件中的影响oracle数据库实例功能的设置：
- 数据库文件的定位
- Oracle用来缓存从磁盘上读取的数据的内存数量
- Oracle用来缓存SQL语句的执行计划，PL/SQL过程和数据字典信息以便与它们不必从磁盘连续读取的内存的数量
- 默认的优化程序的选择
和数据库文件相关，执行两个重要的功能：
- 为数据库指出控制文件
- 为数据库指出归档日志的目标
参数文件中参数的数据类型：
- 整型参数，如DB_BLOCK_SIZE = 2048——数据块大小
- 字符型参数，如DB_NAME = ‘ora9‘——数据库名称
体系结构
- 物理结构：数据文件、日志文件、控制文件、参数文件
- 系统全局区（System Global Area）：共享池、数据缓冲区、日志缓冲区
- 进程：用户进程、服务器进程、后台进程
Oracle的内存结构：
- 使用计算机的内存存放常用的信息和所有调度运行的程序
- 系统全局区（SGA）：
- 由所有用户进程共享的一块内存区域
- 数据缓冲区：存储最近从数据库中读取出来的数据块
- 重做日志缓冲区：记录事务
- 共享池：程序高速缓冲区
- 程序全局区（PGA）：排序区、会话信息、堆栈空间
数据缓冲区（Database Buffer Cache）
用于存储最近从数据库中读取出来的数据块
DB_BLOCK_SIZE：确定数据块的大小，一般为2K或4K，对于大数据块的数据库，此参数值为物理块的倍数
DB_nK_CACHE_SIZE：定义非标准数据块的大小
重做日志缓冲区（Redo Log Buffer）
由一条条重做记录组成，每条记录记载了被修改数据块的位置和变化后的数据
LOG_BUFFER：确定日志缓冲区的大小
共享池
- SQL程序的高速缓冲区
- 存放的是所有通过SQL语法分析并准备执行的SQL语句
- 主要由库高速缓存和数据字典高速缓存组成
SHARED_POOL_SIZE：定义其大小
Oracle的进程结构
- 用户进程：向服务器进程提出操作请求
- 当用户运行一个应用程序时，就建立一个用户进程
- 后台进程：多进程系统中，使用的一些附加进程
- 为所有数据块用户异步完成各种事务
- 服务器进程：接受用户进程发出的请求并完成其响应的操作
- 服务器进程主要包括：分析、优化和执行SQL语句，将硬盘数据读到系统全局区（SGA），将SQL语句的结果返回给用户进程，完成系统的维护等
主要的后台进程
- DBWR(Database Writer) 数据库写进程
- LGWR(Log Writer) 日志写进程
- CKPT(Checkpoint process) 检查点写进程
- SMON(System Monitor) 系统监控进程
- PMON(Process Monitor) 进程监控进程
- ARCH(Archive Process) 归档进程
- RECO 恢复进程
- LCKn 封锁进程
DBWR
- 将数据缓冲区中所有修改过的缓冲块数据写到数据文件中，并使用LRU算法（最近最少使用算法）来保持缓冲区中的数据块为最近使用的，以减少I/O次数。该进程在启动实例时自动启动。
- 通过设置DB_Writer_processes定DBWR个数
LGWR
- 将日志缓冲区中的所有记录项写到日志文件中。
- 该进程在启动实例时自动启动。
- LGWR进行写操作的情况：
- 用户进程提交一个事务（Commit）
- 重做日志缓冲区达到1/3区间
- 在DBWR进程将脏缓冲区写入到数据文件之前
- 出现超时（3秒钟内未活动，则进行一次写操作。）
- Oracle总是“先日志后修改”
检查点（Checkpoint）
- 在检查点出现期间，DBWR进程将数据缓冲区中的所有脏缓冲块写到数据文件中，LGWR将日志缓冲区中的所有记录项写到日志文件中，以确保上一个检查点至今修改过的所有数据块都被写到磁盘上。
检查点进程CKPT(Checkpointer)
- 在控制文件中记录检查点。参数CHECKPOINT_PROCESS 确定了检查点的启动/不启动状态。
- 若CKPT进程不启动，则该进程的工作将由LGWR进程代劳。（如果数据库的数据文件过多，这样操作会降低系统性能。）
- 其进程主要用于同步数据库的数据文件、控制文件和重做日志。
SMON进程
- 用于执行例程恢复、合并空间碎片并释放临时段
- 主要包括：
- REDO
- 打开数据库
- UNDO
PMON进程
- 监视服务器进程的执行，并且在服务器进程失败时清除该服务器进程。
- 主要执行以下操作：
- 回退用户的当前事务
- 释放服务器进程所占用的所有表锁和行锁
- 释放用户所占用的其他资源
数据库结构
ORACLE数据库中的数据逻辑存储在表空间中，物理存储在数据文件中。
Oracle Server工作原理
- 访问oracle数据库时，必须首先连接到Oracle Server，才能执行各种数据库访问操作。
- 连接到oracle Server
- 应用程序的所有SQL操作都是由服务器进程来执行的，用户进程只是发送SQL语句并接收SQL语句的结果
处理查询操作
- 客户端运行Select语句时，首先送到oracle server，由服务器进程处理Select，包括：
- 解析：检查SQL语句的语法和语义，生成SQL语句的执行计划。存放在共享池中。
- 执行：服务器进程按SQL语句执行计划执行SQL。
- 提取数据：将被选择行数据返回到客户端
小结
- Oracle数据库是当今世界流行的数据库管理系统之一，在大中型应用中被广泛的采用。到目前为止Oracle的最高版本是11g(Grid) 。
- Oracle数据库的结构可分为逻辑结构和物理结构两部分，逻辑结构是由表空间、逻辑对象、数据段等组成，物理结构是由若干物理文件组成（数据文件，日志文件 ，控制文件 ，参数文件）
- 当客户端运行Select语句时，首先送到oracle server，Oracle服务器再对该语句进行解析，执行，提取数据等操作，最后将结果传会客户端。
Oracle数据库及其管理
创建数据库
使用数据库配置助手创建数据库
方法一：编写SQL PLUS脚本并运行
灵活性大，但要求创建者对Oracle9i数据库创建的语法和参数有深入的了解，初学者一般选择使用后一种方法。
创建数据库命令中参数意义：
- “database_name”：创建的数据库名称。
- “CONTROLFILES REUSE”：重用已经存在的控制文件。
- “LOGFILE”：指定重做日志文件名和重做日志组名。
- “MAXLOGFILS”：最大的重做日志组数量。
- “MAXLOGMEMBERS”：每个重做日志组中最大的日志文件数。
- “MAXLOGHISTORY”：可以自动归档的最大日志文件数量。
- “database_name”：创建的数据库名称。
- “CONTROLFILES REUSE”：重用已经存在的控制文件。
- “LOGFILE”：指定重做日志文件名和重做日志组名。
- “MAXLOGFILS”：最大的重做日志组数量。
- “MAXLOGMEMBERS”：每个重做日志组中最大的日志文件数。
- “MAXLOGHISTORY”：可以自动归档的最大日志文件数量。
- “MAXDATAFILES”： 数据文件的最大数量。
- “MAXINSTANCES”： 数据库中可以同时打开的例程数。
- “ARCHIVELOG”： 采用归档模式。
- “NOARCHIVELOG”： 不采用归档模式。
- “CHARACTER SET”：指定使用的字符集。
- “DATAFILE”： 指定数据文件名。
- “AUTOEXTEND”： 子句用于指定数据文件自动扩展。
方法二：使用Oracle 9i的数据库配置助手(Database Configuration Assistant)
步骤：
- 选择操作
- 选择模板
- 数据库标识窗口
- 数据库特性
- 数据库连接选项
- 初始化参数
- 数据库存储
- 创建选项
(Oracle Database Configuration Assistant)数据库配置助手具有4种功能：
- 创建数据库；
- 在现有数据库中配置数据库选项；
- 删除数据库；
- 管理数据库模板。
创建数据库的模板有四种：
- Data Warehouse(数据仓库)；
- General Purpose(通用数据库)；
- New Database(新数据库)；
- Transaction Processing(事务处理)。
使用SQL命令修改数据库
修改数据库的语法格式：
- ALTER DATABASE database_name
- [ARCHIVELOG]
- [NOARCHIVELOG]
- [RENAME FILE ‘path\filename’ , …n TO ‘path\re_filename’,…n]
- [CREATE DATAFILE ‘data_filename’
- [AS ‘path\filename’ [SIZE n [K | M]] [REUSE]], …n]
- DATAFILE ‘path\filename’ [ONLINE | OFFLINE [DROP]
- | RESIZE n [K | M]
- | AUTOEXTEND OFF
- | ON [NEXT n [K | M] [MAXSIZE UMLIMITED | n [K | M]]]
修改数据库命令中参数意义：
- RENAME FILE：重命名数据文件、临时文件或重做日志文件成员。该子句只在控制文件中对文件重命名，而不在操作系统上对其真正重命名。在指定该子句前，必须按操作系统对文件名的约定指定每个文件名。
- CREATE DATAFILE：创建新的空数据文件替代旧数据文件。可用该子句重新创建没有备份的丢失数据文件。data_filename必须作为或曾作为数据库部分的一个文件。AS子句指定新数据文件的名字和大小。如果忽略AS子句，Oracle用data_filename定义的名字和大小创建新文件。
- DATAFLE对数据文件联机有影响：其它参数和关键字与CREATE DATABASE 的参数和关键字的含义相同，请参照CREATE DATABASE参数和关键字的说明。
重要概念
数据库名（db_name)
用于区分数据的内部标识，是以二进制方式存储于数据库控制文件中的参数，在数据安装或创建之后将不得修改
数据库安装完成后，该参数被写入数据库参数文件pfile中，格式如下：
db_name=“orcl” #(不允许修改)
如果一个服务器程序中创建了两个数据库，则有两个数据库名。其控制参数数据分属在不同的pfile中控制着相关的数据库。
数据库实例名（instance_name)
用于和操作系统之间的联系，用于对外部连接时使用。在操作系统中要取得与数据库之间的交互，必须使用数据库实例名，在安装或创建数据库之后，实例名可以被修改
数据库安装完成后，该实例名被写入数据库参数文件pfile中，格式如下：
db_name=“orcl” #(不允许修改)
instance_name=orcl #(可以修改,可以与db_name相同也可不同)
数据库名与实例名之间的关系
- 数据库名与实例名之间的关系一般是一一对应关系
- 用两个标识确定一个数据库，用户和实例相连接。
- 但在8i、9i的并行服务器结构中，数据库与实例之间不存在一一对应关系，而是一对多关系，(一个数据库对应多个实例，同一时间内用户只与一个实例相联系，当某一实例出现故障，其它实例自动服务，以保证数据库安全运行。)
操作系统环境变量（ORACLE_SID)
instance_name参数是ORACLE数据库的参数，此参数可以在参数文件中查询到，而ORACLE_SID（System Identity 系统标识符）参数则是操作系统环境变量
操作系统环境变量ORACLE_SID用于和操作系统交互，在操作系统中要想得到实例名，就必须使用ORACLE_SID
在数据库安装之后，ORACLE_SID早期被用于定义数据库参数文件的名称。如：
admin/DB_NAME/pfile/init$ORACLE_SID.ora
查看ORACLE_SID
在注册表中查看SID的值：
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0\ORACLE_SID
在创建了多个数据库后，可以通过修改Oracle_SID 的值改变目前默认的登录数据库。
数据库域名
在分布式数据库系统中，不同版本的数据库服务器之间，不论运行的操作系统是unix或是windows，各服务器之间都可以通过数据库链路进行远程复制，数据库域名主要用于oracle分布式环境中的复制
全局数据库名
全局数据库名=数据库名+数据库域名
数据库服务名
从oracle9i版本开始，引入了一个新的参数，即数据库服务名。参数名是SERVICE_NAME。
如果数据库有域名，则数据库服务名就是全局数据库名；否则，数据库服务名与数据库名相同
修改默认登录Net服务名
在注册表中添加local项使其值为Net服务名，添加位置：
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0
在创建了多个Net服务名时，可以通过修改修改local 的值改变目前默认的登录的服务名。
TNS
完整定义：transparence Network Substrate透明网络底层
注：
- 监听服务是它重要的一部分，不是全部，如果想访问某个服务器，必须要设置TNS
- ORCAL不能自动列举出网内的服务器，需要通过读取TNS配置文件才能列出经过配置的服务器名
- 配置文件名一般为：tnsnames.ora 存取路径：$ORACLE_HOME\network\admin\tnsnames.ora
创建和管理表空间
在OEM中创建表空间
用鼠标右键单击“表空间”文件夹，从快捷菜单中选择“创建”功能，激活创建表空间对话框。对话框包括两个标签页，一般信息标签页和存储标签页。通过回答对话框的各种配置需求即可创建所需的表空间。
表空间：就像一个文件夹，是存储数据库对象的容器。表空间由数据文件组成，这些数据文件是数据库实际存放数据的地方，数据库的所有系统数据和用户数据都必须存放在数据文件中
一个数据库可以有多个表空间，也可以只有一个“SYSTEM”表空间（系统默认创建，用于存储系统信息）。一般地，用户数据应该存放在单独的表空间中，所以必须创建和使用自己的表空间
在OEM中管理表空间和数据文件
在OEM中，选择需要管理的表空间或数据文件，单击鼠标右键，从快捷菜单中选择“查看/编辑详细资料”选项，打开相应的对话框。要想修改各个参数，只需双击相应的单元格，然后输入新的参数，按“应用”按钮即可。
可执行的操作：
- 修改表空间和数据文件的参数或状态
- 添加数据文件和设置数据文件的存储增量及最大容量
- 删除表空间或数据文件
查看表空间：
在OEM中，单击“存储”容器前面的“+”号可以展开显示存储管理所包括的内容；再单击“表空间”容器前面的“+”号，可以展开显示该数据库中所有表空间；如果用鼠标左键单击“表空间”，将在右面的显示栏中列出所有表空间的名称、类型、大小以及使用情况。
使用SQL命令创建和管理表空间
创建表空间
使用CREATE TABLESPACE可以生成表空间，表空间生成时可以指定扩展区，如果没有扩展区，Oracle自动生成一个字典管理表空间(Dictionary-Manager Tablespaces)。
要使数据库具有更多的功能，就需要为用户创建另外的数据文件和表空间，按照应用系统实际需求而定。
创建表空间－简单示例
create tablespace u1_tablespace
datafile 'u1_datafile1' size 10M
default storage(
initial 50k
next 50k
minextents 2
maxextents 50
pctincrease 0)
offline;
修改表空间
某些情况下可能需要对表空间进行修改，如使它处于离线状态或是进行数据库的在线备份等操作时。你可以使用ALTER TABLESPACE命令对表空间进行修改
修改表空间：
alter tablespace u1_tablespace
rename datafile 'u1_datafile1' to 'u1_dbf1';
alter tablespace u1_tablespace online;
使用表空间：
create table t(t1 char(10)) tablespace u1_tablespace;
查询表空间：
describe dba_tablespaces; --查看系统存储表空间的系统表结构
select tablespace_name,status from dba_tablespaces;
select file_name,file_id,tablespace_name,bytes from dba_data_files;
小结
- 创建数据库可以用向导或SQL语句来实现，通常我们使用向导创建数据库，操作并不复杂。一个数据库对应至少一个实例，操作系统通过Oracle_SID环境变量来查找Oracle实例。
- 表空间可以在OEM中创建或管理，也可以使用SQL语句实现管理表空间。定义用户时可以指定其缺省表空间。
SQL语言复习
SQL的形式
交互式SQL
- 一般DBMS都提供联机交互工具
- 用户可直接键入SQL命令对数据库进行操作
- 由DBMS来进行解释
嵌入式SQL
- 能将–SQL语句嵌入到高级语言（宿主语言）
- 使应用程序充分利用SQL访问数据库的能力、宿主语言的过程处理能力
- 一般需要预编译，将嵌入的SQL语句转化为宿主语言编译器能处理的语句
SQL语言主要组成部分
数据定义语言（DDL）
创建用户：
Create user 用户名 identified by 密码 [default tablespace 缺省表空间]
[temporary tablespace 临时表空间];
给用户赋连接权：
Grant connect to 用户名;
用户权限
具有CONNECT角色的用户：可以登录数据库，执行数据查询和操纵。即可以执行ALTER TABLE，CREATE VIEW，CREATE INDEX，DROP TABLE，DROP VIEW，DROP INDEX，GRANT，REVOKE，INSERT，SELECT，UPDATE，DELETE，AUDIT，NOAUDIT等操作
建表权
如果只是给用户赋予建表权，如：grant create table to user1，这样用户还是不能成功建表，提示错误是对表空间没有操作权。
grant create table to u1 是指u1拥有了建表的权限 ,oracle 也知道了他有默认的表空间,但默认的表空间没有给他分配使用权利（使用空间）,所以出错.
给用户赋空间使用权：使用quota（配额）给用户赋予表空间使用权
alter user u1 quota 200M on 表空间1; --给他200M 空间
alter user u1 quota unlimited on 表空间1;--让他随意使用表空间1;
收回权限：
Revoke create table from u1;
用角色赋建表权
RESOURCE角色可以创建表，即执行CREATE TABLE操作。创建表的用户将拥有对该表的所有权限。
Create table 权限和create any table 权限是有区别的，后者可以建立其他用户下的表格。如用U1登录，可以这样建表
Create table system.t (t1 int);
--建立其他用户（如U1）的表格，表格的所有权应归属于表格的拥有者（U1），而不是创建者。即他不能对该表格进行权限外操作。
若想将权限赋予所有用户，可以使用Public角色。如：
Grant select on sc to public;
--执行后，所有用户将拥有SC表的查询权
建立表结构
定义基本表：
--CREATE TABLE <表名>(<列定义>[{,<列定义>,<表约束>}])
--表名：
--列定义：列名、列数据类型、长度、是否允许空值等。
--定义完整性约束：列约束和表约束
--[CONSTRAINT<约束名>] <约束定义>
Create Table Student (
sno char(10) primary key ,
sname varchar2(20) not null,
sage smallint,
ssex char(2),
sdept varchar2(20))
复制基本表
--oracle中复制表
Create table t as select * from student;
--只复制表结构：
Create table t1 as select * from student where 1=2;
--SQLserver中复制表
Select * into t from student;
查看表达式的值
--Oracle中查看表达式的值
--Oracle中可以通过Dual表查看常量或表达式的值：
Select sysdate from Dual;
Select dbms_random.value(1,10) from dual; --产生1到10的随机数
--SQL Server中查看表达式的值
Select getdate();
删除表结构
用SQL删除关系（表）：
--将整个关系模式（表结构）彻底删除
--表中的数据也将被删除
--(与表格相关的外码约束也一起删除)
Drop Table r ( Cascade Constraints );
Drop Table student ( Cascade Constraints );
修改表结构
增加表中的属性：
--向已经存在的表中添加属性
--allow null （新添加的属性要允许为空）
--已有的元组中该属性的值被置为Null
Alter Table t Add A D
Alter Table student Add phone char(16);
修改表中的某属性（某列）
--修改属性及相应的数据
Alter Table t modify (A D)
Alter Table Student modify sage char(3)
删除表中的某属性（某列）
--删除表中的某属性(某列)
--去除属性及相应的数据
Alter Table t Drop column A
Alter Table Student Drop column sage
练习题：
--1、为Student表添加籍贯（50个长度的变长字符串）列。
alter table student add JiGuan verchar(50)
--2、将Student表中的“籍贯”列的类型精度改为100。
alter table student modify JiGuan verchar(100)
--3、删除Student表的“籍贯”列。
alter table student drop column JiGuan
--4、删除这三张表。
drop table sc;
drop table student;
drop table course;
数据操纵语言（DML）
增删改
向表中添加数据（insert）
--用SQL的插入语句，向数据库表中添加数据
--按关系模式的属性顺序
Insert Into Student Values ( ‘01001’, ‘张三’,27, ‘M’, ‘CS’ )
--按指定的属性顺序，也可以只添加部分属性（非Null属性为必需）
Insert Into Student ( sno, sname, sage) Values ( ‘01002’, ‘李四’, 26 )
删除数据（delete）
- 只能对整个元组操作，不能只删除某些属性上的值
- 只能对一个关系起作用，若要从多个关系中删除元组，则必须对每个关系分别执行删除命令
Delete From r Where P
--从关系 r 中删除满足P的元组，只是删除数据，而不是删除定义（表结构）
--删除单个元组
--例：删除学号为01001的学生的基本信息
Delete From Student
Where sno = ‘01001’
--删除多个元组
--删除选课而未参加考试的学生的选课信息
Delete From SC
Where grade is null
--删除整个关系中的所有数据
--例：删除所有学生的选课信息
Delete From SC
数据更新
--改变符合条件的某个（某些）元组的属性值
--例：将01001学生转入MA系
Update Student
Set sdept = 'MA'
Where sno = '01001'
--例：所有学生年龄加1
Update Student
Set sage = sage + 1
练习题：
--1、为Student（10行以上），Course（8行以上），SC（25行以上）表添加记录。
--2、为Student表添加列“班级号” 。
alter table student add classnum varchar(50)
--3、为学生填写班级号（数字）。
update student
set classnum = '1'
--4、将每个同学的班级号前面加上“T”。
update student
set classnum = 'T' + classnum
--5、删除班级号前面的“T”。
update student
set classnum = replace(classnum,'T','');
--6、删除班级号前面的“T”。
update student
set classnum = concat(rtrim(classnum),'T');
--rtim(classnum)用于将varchar类型的classnum存储时后面多余的空格去掉，防止溢出
--7、删除班级号为空的学生。
delete from student
where classnum is null
--8、删除成绩低于50分的学生的选课信息。
delete from sc
where grade < 50
数据查询
数据查询是数据库应用的核心功能
基本结构：
Select col1, col2, ..., coln
From r1, r2, ..., rm
Where P
例：select sname from student
星号与属性列表
--星号*：按关系模式中属性的顺序排列
--显示列出属性名：按用户顺序排列
select * from student
改名：
为结果集中的某个属性改名，使结果集更具可读性
select sno as '学号',cno as '课程号',grade '成绩' from sc
select sno,sname,to_char(sysdate,'yyyy')-sage as birthday_Year from student
where子句
查询满足指定条件的元组可以通过where子句实现
运算符：
like：
- 字符匹配：like、not like
- 通配符：
- %：匹配任意字符串
- _：匹配任意一个字符
- 大小写敏感
--例：列出姓张的学生的学号、姓名。
Select sno，sname
From Student
Where sname LIKE ‘张%’
--例：列出张姓且单名的学生的学号、姓名。
Select sno，sname
From Student
Where sname LIKE ‘张_’
from子句
列出被查询的关系（表）
--例：列出所有学生的学号、姓名、课号、成绩。
Select Sudent.sno，sname，SC.cno，grade
From student，SC
Where Student.sno = SC.sno
元组变量：
为from子句中的关系定义，方便关系名的引用
--例：列出所有学生的学号、姓名、课号、成绩。
Select T.sno，sname，cno，grade
From Student T，SC S --不能使用as关键字
Where T.sno = S.sno
order by子句
指定结果集中元组的排列次序，排序会耗时
ASC升序（缺省值），DESC降序
--例：列出CS系中的男生的学号、姓名、年龄，并按年龄进行排列（升序）
Select sno, sname,sage
From Student
Where sdept = ‘CS’ and ssex=‘m’
Order By sage ASC
比较查询：
--查询成绩最高的5位同学的学号，课程号，成绩
--错误示范
Select * from sc
where rownum<=5
Order by grade desc
--其中Rownum为Oracle表格中记录逻辑顺序的列。
--在Oracle中查询的顺序是，先查询，后排序，这样将先查询到结果，再在结果中排序输出，因此该查询应改写为：
Select * from (select * from sc order by grade desc) a
where rownum<=5
rownum——表格中的逻辑标识
表格中的逻辑标识，也就是说表格的记录顺序变了，Rownum会一起变
--若想查询成绩的前10到15名的学生学号，下面这样写是找不到记录的：
Select * from (Select * from sc order by grade) where rownum<=15 and rownum>=10;
--应写为：
Select * from(
select rownum rn,a.*
from (select * from sc order by grade desc) a )
where rn<=15 and rn>=10
rowid——表格中的物理标识
表格中的物理位置标识，表格的记录顺序改变了，rowid的值也不会改变
练习
--查询选修‘c05’课程，并且年龄不大于26岁的学生的学号和成绩，并按成绩降序排列。 （只选出第3个学生？）
Select * from
(select rownum rn ,a.* from
(Select student.sno, grade
from sc , student
where cno=‘C05’ and sage<=26 and sc.sno=student.sno
order by grade desc)
a)
where rn=3;
用户输入——&
--查询学号为输入值的学生的选课成绩
select * from sc where sno = &msno;
练习
--1、查询MA系的女同学。
select * from student where sdept = 'MA' and ssex = 'F';
--2、查询CS系姓李的学生选修的课程，列出学号，课程号和成绩。
select sc.sno,cno,grade
from sc,student
where sname like '李%' and sdept = 'CS'
--3、查询选修了数据库课程的学生的学号，成绩，按成绩降序排列。
select sno,grade
from sc
where cno = (select cno from course where cname = '数据库')
order by grade
--4、找出学分为4分以上的课程的选修情况，列出学号，课程名，成绩。
select * from sc
where cno in (select cno from course where ccredit > 4)
--5、检索数据库的成绩在90分以上的学生的学号和姓名。
select sno,sname
from sc
where grade > 90 and cno = (select cno from course where cname = '数据库')
子查询
子查询是嵌套在另一查询中的 Select-From-Where 表达式（Where/Having）
- SQL允许多层嵌套，由内而外地进行分析，子查询的结果作为父查询的查找条件
- 可以用多个简单查询来构成复杂查询，以增强SQL的查询能力
- 子查询中不使用 Order By 子句，Order By子句只能对最终查询结果进行排序
单值比较：
**返回单值的子查询，只返回一行一列**
父查询与单值子查询之间用比较运算符进行连接
--找出与01001同龄的学生
Select *
From Student
Where sage = (
Select sage
From Student
Where sno = ‘01001’ )
多值成员
子查询返回多行一列
运算符：in，all，some（或any），exists
in用来测试多值中的成员
若值与子查询返回集中的某一个相等，则返回true
--查询选修‘C01’课程的学生的学号、姓名。
Select sno,sname
From Student
Where sno IN (
Select sno From SC
Where cno = 'C01' )
多值比较
- 父查询与多值子查询之间的比较用All来连接
- 值s比子查询返回集R中的每个都大时，s>All R 为True
- All表示所有
-
all、< all、<=all、>=all、<> all
- <> all 等价于 not in
-
--找出年龄最小的学生
Select * From Student
Where sage <= all (
Select sage From Student )
多值比较some/any：多行一列
- 父查询与多值子查询之间的比较需用Some/Any来连接,值s比子查询返回集R中的某一个都大时返回true
- s > Some R为True 或s > Any R为True
- Some(早期用Any)表示某一个（任意一个）
-
some、< some、<=some、>=some、<> some
- = some 等价于 in、<> some 不等价于 not in
-
--检索选修C2课程号的学生姓名(用IN代替=ANY)
select sname from student
where sno in
(select sno from sc
where cno='C02')
练习（使用嵌套查询）
--1、查询和数据库相同学分的课程。
select cno from course
where ccredit = (select ccredit from course where cname = '数据库')
--2、查询选修了数据库课程的学生的学号。
select sno from sc
where cno = (select cno from course where cname = '数据库')
--3、查询成绩最高的学生的姓名。
select sname from student
where sno in (
select sno from (select * from sc order by grade desc) a
where rownum=1)
--4、查询CS系成绩最高的学生的学号、姓名以及成绩。
select * from sc
where grade >= all(
select grade from sc
where sno in (
select sno from student
where sdept = 'CS')) and sno in (
select sno from student
where sdept = 'CS');
select * from (
select * from sc
where sno in (
select sno from student
where sdept = 'CS')
order by grade desc) a
where rownum = 1
--5、查询数据库课程成绩最高的学生的姓名。
select sname from student,course,sc
where grade >= all (
select grade from sc where cno in (
select cno from course
where cname = '数据库'))
and cname = '数据库'
聚合函数
把一列中的值进行聚合运算，返回单值的函数
五个预定义的聚合函数
- 平均值：Avg( )
- 总和： Sum( )
- 最小值：Min( )
- 最大值：Max( )
- 计数： Count( ) 返回所选列中不为NULL的数据个数
group by：
将查询结果集按某一列或多列的值分组，值相等的为一组，一个分组以一个元组的形式出现
只有出现在Group By子句中的属性，才可出现在Select子句中
--统计各系学生的人数。
select sdept，count(*) as stu_count
from Student
group By sdept
having:
针对聚合函数的结果值进行筛选（选择），它作用于分组计算结果集
跟在Group By子句的后面
--例：列出具有两门（含）以上不及格的学生的学号、不及格的课目数。
select sno，count(sno) From SC
where grade < 60
group By sno
having count(sno) >= 2
Having 与 Where的区别
- Where 决定哪些元组被选择参加运算，作用于关系中的元组
- Having 决定哪些分组符合要求，作用于分组
- 聚合函数的条件关系必须用Having，Where中不应出现聚合函数
聚合函数对Null的处理
Count：不计
Sum：不将其计入
Avg：具有 Null 的元组不参与
Max / Min：不参与
--找出具有最高平均成绩的学号及平均成绩
select sno ,avg(grade)
from SC
group By sno
having avg(grade) >= all
(select avg(grade)
from SC
group By sno)
练习——group by
--1、查询每门课程的平均成绩。
--2、查询每门课程不及格的学生人数。
--3、查询每个系学生的最高成绩。
--4、查询平均分在75以上的课程。
--5、查询女同学中成绩最高的学生所在的系。
--6、查询选课人数最多的课程。
子查询——存在判断Exists
- Exists + 子查询用来判断该子查询是否返回元组
- 当子查询的结果集非空时，Exists为True
- 当子查询的结果集为空时，Exists为False
- 不关心子查询的具体内容，因此用 Select *
子查询——相关子查询
具有外部引用的子查询，称为相关子查询
外层元组的属性作为内层子查询的条件
--列出选修了C01课程的学生的学号、姓名
felect sno,sname
from student
where exists (
select * from sc
where sc.sno = student.sno and cno = 'C01')
--等价于
select student.sno,sname
from student,sc
where sc.sno=student.sno and cno='c01'
--列出得过100分的学生的学号、姓名
select sno,sname
from student
where exists (
select *
from sc
where sc.sno = student.sno and grade = 100)
--列出没有选C01课程的学生的学号、姓名
select sno,sname
from student
where not exists (
select *
from sc
where sno = student.sno and cno = 'C01')
--多行插入：插入一个集合
--例:给CS系的学生开设必修课C05，建立选课信息
insert into sc
select sno,'c05',null
from student
where sdept = 'CS'
--给CS系的学生开设必修课C05，建立选课信息
insert into sc(sno,cno)
select sno,'c05' from student
where sdept = 'cs' and not exists (
select * from sc
where sno=student.sno and cno='c05')
数据更新
--将选修C05课程的学生的成绩改为该课的平均成绩
update sc
set grade = (
select avg(grade)
from sc
where cno = 'C05')
where cno = 'C05'
连接问题
在SQL语句中，在FROM子句中提供了一种称之为连接的子句，连接分为内连接和外连接，外连接又可分为左外连接、右外连接和全外连接
内连接
内连接是指包括符合条件的每个表的记录，也称之为全记录操作。
--查询并显示各个学生的学号，所学课程及课程成绩。
select sc.sno,course.cname,sc.grade
from sc,course
where sc.cno=course.cno
--采用内连接方式，则命令如下：
select sc.sno,course.cname,grade
from sc inner join course on sc.cno=course.cno
外连接
是指把两个表分为左右两个表。
右外连接是指连接满足条件右侧表的全部记录。
--右外连接：
--将student表中的所有行都返回
select student.sno,sname,cno,grade
from sc right jion student on sc.sno = student.cno
--在Oracle 中等价下面的写法：
select student.sno,sname,cno,grade
from sc,student
where student.sno=sc.sno(+)
左外连接是指连接满足条件左侧表的全部记录。
--左外连接：
--将SC表中的所有行都返回
select student.sno,sname,cno,grade
from sc left join student on sc.sno= student.sno
--在Oracle 中等价下面的写法：
select student.sno,sname,cno,grade
from sc,student
where student.sno(+)=sc.sno
全外连接是指连接满足条件表的全部记录。
--全外连接：
--将Student,SC两张表中的所有行都返回
select student.sno,sname,cno,grade
from sc full join student on sc.sno= student.sno
数据控制语言（DCL）
用来设置或者更改数据库用户或角色权限的语句
包括grant、revoke、deny等语句
视图（view）
视图并不是真实存在的基础表而是一个虚拟表
创建视图
create [Or Replace] View v
as
<query expression>
[with check option]
With check option选项表示对视图更新时必须满足查询语句中的条件
--计算机系的花名册
create Or replace view cs_stu as
select sno,sname,ssex,sdept
from student
where sdept = ‘CS’
with check option
–视图名可以出现在任何关系名可以出现的地方
--例：列出计算机系的男生
select sno,sname
from CS_Stu
where ssex = ‘M’
--例：建立学生平均成绩视图
create View avg_grade(sno,avg) as
select sno,avg(grade)
from SC
group By sno
使用视图
--例：找出平均成绩大于等75的学生
select *
from avg_grade
where avg >= 75
约束
5种基本的约束：
- 主键约束（primary key constraint）
- 唯一性约束（unique constraint）
- 检查约束（check constraint）
- 缺省约束（default constraint）
- 外部键约束（foreign key constraint）
约束的检查功能：
- 立即执行的约束(Immediate constraints)：语句执行完后立即检查是否违背完整性约束
- 延迟执行的约束(Deferred constrainsts)：完整性检查延迟到整个事务执行结束后进行
创建和删除约束：
- Alter table sc add constraint fk_sc_sno foreign key(sno) references student(sno) deferrable;–定义该约束为可延迟约束并设置为延迟约束
- 当定义约束时使用了Deferrable选项后，就可以设置该约束为立即执行约束还是延迟约束，若定义约束时没有Deferrable选项，则不能将其设置为延迟约束。
Set constraint fk_sc_sno immediate
Set constraint fk_sc_sno deferred
练习——约束
--1、为Student表的Sage（小于30），Ssex（M或F，缺省为M）添加约束。
--2、为SC表的sno（外码），cno（外码），grade（1到100分）添加约束。
--3、为Course表的Cname（唯一），Credit（1到5）添加约束。
为Oracle对象添加注释信息
使用Comment语句可以为表，列等添加注释信息
--例：现建立一表T如下
create table t(t1 int,t2 int);
--为表建立注释：
comment on table t is ‘这是例子表格’;
--为表中的列建立注释：
comment on column t.t1 is ‘这是第一列’;
可以通过user_tab_comments 系统视图来查看表格的注释信息
通过user_col_comments 或 Describe来查看表中列的注释信息
索引
在数据库中，索引使数据库程序无须对整个表进行扫描，就可以在其中找到所需数据。
数据库中的索引是某个表中一列或者若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。
在SQL Server中索引分为聚集索引（Clustered）和非聚集索引（Unclustered）。使用Create Index创建索引。
在Oracle中Create Index没有Clustered选项，即，不能创建聚集索引。Oracle使用索引组织表（Index-Organization Table IOT）来完成聚集索引功能。
Oracle的索引组织表（Index-Organization Table IOT)
数据按主码存储和排序，同索引结构一样，不过在IOT中数据直接存储于主码后面。
索引组织表的适用情况：
- 代码查找表。
- 经常通过主码访问的表。
- 构建自己的索引结构。
- 加强数据的共同定位，要数据按特定顺序物理存储。
- 经常用between…and…对主码或唯一码进行查询。数据物理上分类查询。如一张订单表，按日期装载数据，想查单个客户不同时期的订货和统计情况。
索引组织表创建语法：
SQL> create table t2
(x int primary key, y char(2000) default rpad(‘a’,2000,‘d’), z date)
organization index --表示创建的表类型是IOT
nocompress --同索引的压缩选项一样，表示是否对相同索引条目值进行压缩存储
pctthreshold 50 --当行的大小超过块大小的百分比时，超过列数据存储至溢出段
including y --IOT中每行including指定列前边的列都存储到索引块中，其余列存储到溢出块中
overflow --IOT中行太大时允许设置另一溢出段来保存溢出的数据，同行迁移相似
Table created.
Decode函数和Sign函数
Decode函数
具体语法格式：
DECODE(input_value,value,result[,value,result…][,default_result])
其中：
- input_value 试图处理的数值。DECODE函数将该数值与一系列的序偶相比较，以决定最后的返回结果 value 是一组成序偶的数值。如果输入数值与之匹配成功，则相应的结果将被返回。对应一个空的返回值，可以使用关键字NULL于之对应
- result 是一组成序偶的结果值
- default_result 未能与任何一序偶匹配成功时，函数返回的默认值。
select sno,ssex,decode(trim(ssex),'m','男','f','女','待定') 性别
from Student
Sign函数
具体语法格式:
sign(V1-V2)
V1减去V2是个正数则sign函数返回1，负数为-1，0为0
select sno,cno,
decode(sign(grade-60),1,'及格',-1,'不及格','未考试') 是否合格
from sc;
select sno 学号,cno 课程号,
(case when grade>=90 then '优'
when grade>=80 then '良'
when grade>=70 then '中'
else '及格'
end) as 成绩级别
from sc
PL/SQL块
PL/SQL简介
PL/SQL的基本结构
PL/SQL块
PL/SQl是结构化的语言，其程序结构的基本单位是‘块’（Block）
PL/SQL块的三个部分：
- 声明部分
- 执行部分
- 异常处理部分
[DECLARE]
declaration statements
BEGIN
executable statements
[EXCEPTION]
exception statements
END
注意：
- PL/SQL块中的每一条语句都必须以分号结束
- SQL语句可以多行，但分号表示语句的结束。一行中可以有多条SQL语句，他们之间以分号分隔
- 每一个PL/SQL块由BEGIN或DECLARE开始，以END结束
块的命名和匿名
PL/SQL程序块可以是一个命名的程序块也可以是一个匿名程序块
匿名程序块可以用在服务器端也可以用在客户端。
执行部分：
- 执行部分包含了所有的语句和表达式，执行部分以关键字BEGIN开始，将以关键字END结束
- 分号分隔每一条语句，使用赋值操作符’='或SELECT INTO给每个变量赋值
- 执行部分的错误将在异常处理部分解决
- 在执行部分中可以使用另一个PL/SQL程序块，这种程序块被称为嵌套块
- 所有的SQL数据操作语句都可以用于执行部分，但PL/SQL块不能在屏幕上显示SELECT语句的输出，SELECT语句必须包括一个INTO子串或者是游标的一部分
- 执行部分使用的变量和常量必须首先在声明部分声明，执行部分必须至少包括一条可执行语句，NULL是一条合法的可执行语句，事物控制语句COMMIT和ROLLBACK可以在执行部分使用
- 数据定义语言(Data Definition language)不能在执行部分中使用，DDL语句与EXECUTE IMMEDIATE一起使用或者是DBMS_SQL调用。
块的执行
SQL*Plus中匿名的PL/SQL块的执行是在PL/SQL块后输入“/”来执行
命名的程序与匿名程序的执行不同，执行命名的程序块必须使用execute关键字
PL/SQL变量和常量
DECLARE
ORDER_NO NUMBER(3);
CUST_NAME VARCHAR2(20);
ORDER_DATE DATE;
EMP_NO INTEGER:=25;
PI CONSTANT NUMBER:=3.1416;
BEGIN
NULL;
END;
变量
声明变量：
variable_name datatype [(WIDTH)] [:= DEFAULT expression]
给变量赋值
通常给变量赋值有两种方式：一是直接给变量赋值；二是通过SQL SELECT INTO给变量赋值。
PL/SQL数据类型：
常量
常量与变量相似，但常量的值在程序内部不能改变，常量的值在被定义时赋予，声明方式与变量相似，但必须包括关键字CONSTANT
有效字符集
PL/SQL标识符的最大长度是30个字符，并且不区分字母的大小写
- 所有的大写和小写英文字母
- 数字：0-9
- 符号：( ) + - * / < > = ! ; : . ’ @ % ," # & _ { } ? [ ]
运算符
PL/SQL支持的运算符包括算术运算符、和关系运算符和逻辑操作符
流程控制
条件结构
if条件判断逻辑结构
--单分支
IF condition THEN
statement;
END IF;
--双分支
IF condition THEN
statements_1;
ELSE
statements_2;
END IF;
--多分支
IF condition1 THEN
statements_1
ELSIF condition2 THEN
statements_2
ELSE
statements_3
END IF;
case多分支
CASE 表达式（或变量）
WHEN 表达式1 THEN 语句1;
WHEN 表达式2 THEN 语句2;
WHEN 表达式3 THEN 语句3;
WHEN 表达式4 THEN 语句4;
ELSE 值5;
END CASE;
将case的值赋值给var变量
VAR:= CASE selector
WHEN V1 THEN AV1;
WHEN V2 THEN AV2;
…………
ELSE AVn;
END;
循环控制LOOP
LOOP循环语句是其中最基本的
LOOP语句格式：
LOOP
statements
END LOOP;
这种循环语句是没有终止的，如果不人为控制的话，其中的statements将会无限地执行。一般可以通过加入EXIT语句来终结该循环
LOOP
[statement1]
[statement2]
…………
EXIT WHEN (condition);
END LOOP;
循环控制WHILE
WHILE … LOOP有一个条件与循环相联系，如果条件为TRUE，则执行循环体内的语句，如果结果为FALSE，则结束循环
WHILE (condition) LOOP
[statement1]
[statement2]
………
END LOOP;
循环控制FOR
FOR counter IN [REVERSE] start_range .. end_range LOOP
statements;
END LOOP;
counter是一个隐式声明的变量，初始值是start_range，第二个值是start_range+1(步长)，直到end_range。如果使用了REVERSE关键字，那么范围将是一个降序
跳转语句GOTO
GOTO LABEL;
- 执行GOTO语句时，控制会立即转到由标签标记的语句
- PL/SQL中对GOTO语句有一些限制，即在块、循环和IF条件结构中使用GOTO语句时不能从外层跳转到内层
嵌套
程序块的内部有另一个程序块的情况
注意：
- 定义在最外部程序块中的变量可以在所有子块中使用
- 如果在字块中定义了与外部程序块变量相同的变量名，在执行子块时将使用子块中定义的变量
- 子块中定义的变量不能被父块引用（局部变量）
- GOTO语句不能由父块跳到子块中
异常处理
PL/SQL的异常
异常处理块包含了与异常相关的错误发生以及当错误发生时要进行要进行执行和处理的代码
异常部分的语法：
BEGIN
EXCEPTION
WHEN excep_name1 THEN
……
WHEN excep_name2 THEN
……
WHEN OTHERS THEN
……
END;
预定义异常
预定义异常是由运行系统产生的
例如：出现被0除时，PL/SQl就会产生一个预定义的ZERO_DIVIDE异常
自定义异常
用户在自己应用程序中创建可触发及可处理的自定义异常
调用异常处理需要使用Raise语句
异常情态的传播：指的是当在程序块的声明、执行、异常部分分别出现异常情态时，或在本块中没有相应的异常处理器时会将这个异常情态传播到哪里，会去激发那个块中的处理器
DECLARE
SALARY_CODE VARCHAR2(1);
INVALID_SALARY_CODE EXCEPTION;
BEGIN
SALARY_CODE:='X';
IF SALARY_CODE NOT IN('A', 'B', 'C') THEN
RAISE INVALID_SALARY_CODE;
END IF;
EXCEPTION WHEN INVALID_SALARY_CODE THEN
DBMS_OUTPUT.PUT_LINE('INVALID SALARY CODE');
END;
处理PL/SQl的异常
设置set serveroutput on可以将环境变量serveroutput设置为打开状态，从而使得pl/sql程序能够在脚本输出中输出结果
DECLARE
X NUMBER;
BEGIN
X:= 'yyyy';--Error Here
EXCEPTION WHEN VALUE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('Value Error');
END;
EXCEPTION WHEN first_exception THEN
…
WHEN second_exception THEN
…
WHEN OTHERS THEN
/*OTHERS异常处理器必须排在最后，它处理所有没有明确列出的异常。*/
END;
在PL/SQL中单条记录的查询：
DECLARE
v_sno varchar(10);
v_sname VARCHAR2(20);
BEGIN
SELECT sno,sname
INTO v_sno,v_sname
FROM student WHERE sno=‘001’;
DBMS_OUTPUT.PUT_LINE(TO_CHAR(v_sno)||'.'|| v_sname);
EXCEPTION WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NO DATA_FOUND');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('TOO_MANY_ROWS');
END;
常用内置函数
数字函数
| 函数 | 描述 |
|---|---|
| ABS(n) | 求n的绝对值 |
| EXP(n) | 求n的指数 |
| MOD(m,n) | 求m除以n的余数 |
| CELL(n) | 返回大于等于n的最小整数 |
| FLOOR(n) | 返回小于等于n的最大整数 |
| ROUND(n,m) | 对n做四舍五入处理，保留m位 |
| TRUNC(n,m) | 对n做截断处理，保留m位 |
| SQRT(n) | 求n的平方根 |
| SIGN(n) | n的值为正数、0或负数时分别返回1，0和-1 |
字符函数
| 函 数 | 描 述 |
|---|---|
| LOWER(char) | 将字符串char中的所有大写字母转换为小写字母 |
| UPPER(char) | 将字符串char中的所有小写字母转换为大写字母 |
| RPAD(string,len[,pad]) LPAD(string,len[,pad]) | 在string右（左）侧填充pad指定的字符串直到len指定长度，若未指定pad，则默认为空格。 |
| CONCAT(char1,char2) | 连接字符 |
| SUBSTR(char,start,length) | 返回字符串表达式char中从第start开始的length个字符 |
| LENGTH(char) | 返回字符串表达式char的长度 |
| LTRIM(char) | 去掉字符串表达式char后面的空格 |
| ASCII(char) | 取char的ASCII值 |
| CHAR(number) | 取number的ASCII字符 |
| REPLACE(char,str1,str2) | 将字符串中所有str1换成str2 |
| INSTR(char1,char2,start,times) | 在char1字符串中搜索char2字符串，start为执行搜索操作的起始位置，times为搜索次数 |
日期函数
| 函数 | 描述 |
|---|---|
| SYSDATE | 返回系统当前日期和时间 |
| NEXT_DAY(day,char) | 返回day指定的日期之后并满足char指定条件的第一个日期，char所指条件只能为星期几 |
| LAST_DAY (day) | 返回day日期所指定月份中最后一天所对应的日期 |
| ADD_MONTHS(day,n) | 返回day日期在n个月后(n为正数)或前(n为负数)的日期 |
| MONTHS_BETWEEN(day1,day2) | 返回day1日期和day2日期之间相差的月份 |
| ROUND(day [,fmt]) | 按照fmt指定格式对日期数据day做舍入处理，默认舍入到日 |
| TRUNC(day [,fmt]) | 按照fmt指定格式对日期数据day做截断处理，默认截断到日 |
数据类型转换函数
| 函数 | 描述 |
|---|---|
| TO_CHAR | 将一个数字或日期转换为字符串 |
| TO_NUMBER | 将字符型数据转换为数字型数据 |
| TO_DATE | 将字符型数据转换为日期型数据 |
| CONVERT | 将一个字符串从一种字符集转换为另一种字符 集 |
| CHARTOROWID | 将一个字符串转换为ROWID数据类型 |
| ROWIDTOCHAR | 将一个ROWID数据类型数据转换为字符串 |
| HEXTORAW | 将一个十六进制字符串转换为RAW数据类型 |
| RAWTOHEX | 将一个RAW类型的二进制数据转换为十六进制表 达的字符串 |
| TO_MULTI_BYTE | 将一个单字节字符串转换为多字节字符串 |
| TO_SINGLE_BYTE | 将一个多字节字符串转换为单字节字符串 |
集合函数
| 函数 | 描述 |
|---|---|
| AVG | 计算一列值的平均值 |
| COUNT | 统计一列中值的个数 |
| MAX | 求一列值中的最大数 |
| MIN | 求一列值中的最小数 |
| SUM | 计算一列值的总和 |
| STDDEV | 计算一列值的标准差 |
| VARIANCE | 计算一列值的方差 |
游标
一个游标就是一个指向保存有多行SQL查询结果集的工作区的句柄
Oracle打开一个工作区来保存多行查询的结果集。游标就是给这个工作区命的名称，并能用于处理由多行查询而返回的记录行
当服务器上执行了一个查询后，查询返回的记录集存放在上下文相关区，通过游标上的操作可以把这些记录检索到客户端的应用程序
游标分显式游标和隐式游标
声明游标
语句格式：
cursor cursor_name is select statement
声明游标的作用：
- 给游标命名
- 将一个查询与游标关联起来
打开游标
语句格式：
open cursor_name;
打开游标的作用：
- 打开游标将激活查询并识别活动集，可是在执行游标取回命令之前，并没有真正取回记录
- OPEN命令初始化了游标指针，使其指向活动集的第一条记录
使用游标的基本方法
游标被打开后，直到关闭之前，取回到活动集的所有数据都是静态的
declare
cursor csr_s is
select sname,cno
from student,sc
where student.sno=sc.sno
order by 1;
v_sname varchar2(20);
v_cno varchar2(30);
begin
open csr_s;
/*...<Process the cursor resultset>...*/
null;
end;
注：
- 游标忽略所有在游标打开之后，对数据执行SQL的 DML命令(INSERT、UPDATE、DELETE)，因此只有在需要时才打开它，要刷新活动集，只需关闭并重新打开游标即可
- 不要打开已经打开的游标，否则会产生异常
从游标中取回数据
FETCH命令以每次一条记录的方式取回活动集中的记录。FETCH命令每执行一次，游标前进到活动集的下一条记录
fetch命令的语句格式：
fetch cuesor_name into record_list;
执行FETCH命令后，活动集中的结果被取回到PL/SQL变量中，以便在PL/SQL块中使用。每取回一条记录，游标的指针就移向活动集的下一条记录
使用游标的基本方法：
Fetch cursor_name into v1,v2,…,vn;
Or
Fetch cursor_name into cursor_name%rowtype;
Or
Fetch cursor_name into table_name%rowtype
declare
cursor csr_s is
select sname,cno
from student,sc
where student.sno=sc.sno
order by 1;
v_sname varchar2(20);
v_cno varchar2(30);
begin
open csr_s;
/*...<Process the cursor resultset>...*/
fetch csr_s into v_sname,v_cno;
end;
关闭游标
close语句关闭以前打开的游标，使得活动集不确定
close语句格式：
close curcor_name;
使用游标的基本方法：
DECLARE
CURSOR csr_s IS
SELECT sname,cno
FROM student,sc
WHERE student.sno=sc.sno
ORDER by 1;
v_s csr_s%rowtype;
BEGIN
OPEN csr_s;
Fetch csr_s into v_s;
Close csr_s;
END;
注意：
- 所有记录行都处理完后，必须关闭游标，这样会释放分配给游标的资源
- 一定要关闭打开的游标，因为ORACLE中定义了打开游标的最大数目，在Oracle9i中默认为50
游标与循环
使用游标的基本方法：
DECLARE
CURSOR C1 IS
SELECT VIEW_NAME FROM ALL_VIEWS WHERE ROWNUM <= 10
ORDER BY VIEW_NAME;
VNAME VARCHAR2(40);
BEGIN
OPEN C1;
FETCH C1 INTO VNAME;
WHILE C1%FOUND LOOP
DBMS_OUTPUT.PUT_LINE(TO_CHAR(C1%ROWCOUNT) || ' ' || VNAME);
FETCH C1 INTO VNAME;
END LOOP;
CLOSE C1;
END;
注意：
- While loop开始之前的第一个FETCH语句对确保WHILE LOOP循环的条件值为TRUE是非常必要的
- 使用%found属性来检查，如果没有前面的fetch语句，那么%found属性就不会为真（即使有记录存在）
- %notfound属性的值和%found值一样都为NULL
- 与LOOP ……END LOOP对比，后者的EXIT判断语句之前要有一FETCH语句
使用游标的基本方法——使用游标for循环：
DECLARE
CURSOR C1 IS
SELECT VIEW_NAME FROM ALL_VIEWS
WHERE ROWNUM <= 10
ORDER BY VIEW_NAME;
BEGIN
FOR I IN C1 LOOP
DBMS_OUTPUT.PUT_LINE(I.VIEW_NAME);
END LOOP;
EXCEPTION
WHEN OTHERS THEN NULL;
END;
注意：
- FOR LOOP游标的索引不需要声明，它有PL/SQL隐式的定义为cursor%rowtype类型
- 当需要在一个游标中无条件的处理所有行，用FOR LOOP语句是很好的
游标的使用技巧
带参数的光标：
可以在游标名称后面 ，用括号括起参数的名称和类型来定义游标的参数。这些参数叫形参。实参由OPEN语句传递到形参中
DECLARE
CURSOR C1(VIEW_PATTERN VARCHAR2) IS
SELECT VIEW_NAME
FROM ALL_VIEWS
WHERE VIEW_NAME LIKE VIEW_PATTERN||'%'
AND ROWNUM<=10
ORDER BY VIEW_NAME;
VNAME VARCHAR2(40);
BEGIN
FOR I IN C1('USER_AR') LOOP
DBMS_OUTPUT.PUT_LINE(I.VIEW_NAME);
END LOOP;
DBMS_OUTPUT.PUT_LINE(‘’);
FOR I IN C1('USER') LOOP
DBMS_OUTPUT.PUT_LINE(I.VIEW_NAME);
END LOOP;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('AAA');
END;
select for update游标
在仅仅需要修改检索到的那些记录行，而不需要取出这些行值的情况下可以用SELECT FOR UPDATE游标来更新由游标检索到的那些记录行
DECLARE
CURSOR csr_1 IS
SELECT * FROM sec_hrc_tab FOR UPDATE OF hrc_descr;
v_hrc_descr VARCHAR2(20);
BEGIN
FOR idx IN csr_1 LOOP
v_hrc_descr :=UPPER(idx.hrc_descr);
UPDATE sec_hrc_tab
SET hrc_descr =v_hrc_descr
WHERE CURRENT OF csr_1;
END LOOP;
COMMIT;
END;
工作机制
- 首先为游标检索到的行加锁
- 对游标标识的每一行，游标会更改该行的特定列（无需再次扫描表格——与update语句对比）
优势：
- 在打开游标并标识要更新的结果集中的行之后就立即加锁
- 不必为更新操作而对表格进行第二次取值
注意：
- 在游标的循环外部必须进行commit操作，否则不会释放锁
隐式游标
又叫SQL游标
说明：
- 在作DML语句时，会自动的打开一个SQL游标指向相应的工作区
- 隐式游标在DML语句操作完毕后自动关闭，所以不需要，也不能用OPEN，FETCH，CLOSE语句操作隐式游标
隐式游标的属性：
- sql%found 说明DML是否影响到行（在定义任何DML之前使用该属性，其值都为NULL）
- sql%isopen 是否打开，这个值永远是FALSE，因为DML执行完后会自动关闭SQL游标
- sql%notfound 说明DML在修改行时是否失败。（在定义任何DML之前使用该属性，其值都为NULL）
- sql%rowcount 指明DML执行完后影响的行数。（初值为0）
游标变量——动态游标
使用游标变量可以在存储过程之间传递结果集。在运行时，一个游标变量能与多个查询相关联
游标变量的定义：
- 定义一个ref cursor类型的指针和该类型的变量名
type rc is ref cursor;
v_rc rc;
--其中REF关键字表示定义了一个CURSOR类型的指针。
--REF TYPE 表示定义了指向TYPE类型的指针。
说明：
- 没有指明游标返回类型的游标变量称为弱REF CURSOR类型。它能指向任何一个具有任意多列的SELECT查询
- 也可以为游标指明返回类型，这样的游标变量称为强REF CURSOR
- 为游标指明返回类型：Type rc is ref cursor return sc%rowtype
使用游标变量
步骤：
- 分配内存
- 为查询打开该游标变量
- 把结果取到变量中或记录中
- 关闭游标
--打开游标：
open v_rc for select * from sc;
--取结果：
fetch v_rc into v1,v2,v3;
--Or
fetch v_rc into sc%rowtype
declare
type rc is ref cursor;
v_rc rc;
rS sc%rowtype;
begin
open v_rc for select * from sc;
loop
fetch v_rc into rS;
exit when v_rc%notfound;
/*...Process the individual records */
null;
END LOOP;
CLOSE rc;
END;
PL/SQL中提供一个SYS_REFCURSOR类型，该类型定义了一个普通的弱游标类型：
declare
v_rc sys_reefcursor;
sc_rec sc%rowtype;
begin
open v_rc for select * from sc;
……………………
close v_rc;
end;
小结
- 游标是指向查询的“指针”，当定义静态游标时并没有打开查询，只有打开游标（Open）时才执行查询，一旦游标被打开，查询结果不再改变，若原始表中改变了数据，只需要关闭游标再打开游标，查询将被刷新
- 静态游标的定义在Declare中，在Begin后打开，在使用完后关闭
- 动态游标是在Declare中定义为游标类型（ref cursor），在Begin后使用Open Cursor_name for select statement 指定查询并打开游标
- 隐式游标（SQL游标）只能在DML语句后使用，它是自动打开和关闭的
- 读取游标中的内容使用fetch语句，利用循环遍历游标中的数据
练习
--n1、查询CS系的学生的基本信息。对齐打印输出。
--2、查询某学生的选课信息，打印课程号，成绩。并在最后一行打印（学生姓名）同学的平均分为：平均分。
--3、查询某学生可以选的课程信息（该生没有选，同时课程也没有选满），打印课程号，课程名，学分，最大选课人数，当前选课人数。并在最后一行打印该生目前获得的学分。
--注意：1、当前选课人数可以暂时不打印，有时间再加上这一列。2、请设计输出。
存储过程和包
（存储）过程
概念：
过程就是高级程序设计语言中的模块的概念，将一些内部联系的命令组成一个个过程，通过参数在过程之间传递数据是模块化设计思想的重要内容。
语法结构：
CREATE OR REPLACE PROCEDURE 过程名
IS
--声明语句段;
BEGIN
--执行语句段;
EXCEPTION
--异常处理语句段;
END;
过程的特点：
- 过程是有名称的程序块，is关键字代替了无名块的Declare，因此在使用过程中不能使用Declare子句
- create or replace:创建或替代,or replace 在创建表和序列时不能使用
- 创建过程并不会直接输出结果，只是和创建其他数据库对象一样，是一个数据定义命令
创建过程实例：
--接收输出
set serveroutput on
create or replace procedure tempuser.tempprocedure
is
tempdate tempuser.testtable.currentdate%type;
--tempuser.：为其他用户创建过程
begin
select currentdate into tempdate from testtable
where recordnumber=88;
dbms_output.put_line(to_char(tempdate));
end;
查询过程
方法一：使用项目管理器
登录【企业管理器】，在【管理目标导航树】里选择【数据库】/【ORA】/【方案】/【源类型】/【过程】选项。
方法二：使用sql语句
使用系统表查询：select text from user_source where name=‘P1’
执行过程
要执行创建的过程，必须通过主程序来调用过程。
set serveroutput on
begin
tempprocedure;
end;
--测试窗口中执行过程（execute：执行）
execute p1；
带参数的过程
参数类型
- in参数：读入参数，主程序向过程传递参数值，在过程中不能被赋值，可省
- out参数：读出参数，过程向主程序传递参数值，不能为其传入值，不可省
- in out参数：双向参数，过程与主程序双向交流数据，会降低程序的可读性，最不常用
实例：
CREATE OR REPLACE PROCEDURE show_line
(ip_line_length IN NUMBER,
ip_separator IN VARCHAR2)
IS
actual_line VARCHAR2(150);
BEGIN
FOR idx in 1..ip_line_length LOOP
actual_line :=actual_line ||ip_separator;
END LOOP;
DBMS_OUTPUT.PUT_LINE(actual_line);
EXCEPTION WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
END;
使用带参数的过程
DECLARE
v_length NUMBER :=50;
v_separator VARCHAR2(1):='=';
BEGIN
show_line(ip_line_length=>v_length,
ip_separator=>v_separator);
END;
函数
函数的创建和使用：
- 函数的定义与过程的定义主要的不同就在于签名。函数的签名比过程的签名多了一个Return子句，该子句指定了函数的返回值的类型。
- 定义函数时必须指定其返回类型
实例：
CREATE OR REPLACE FUNCTION f_line
(ip_line_length IN NUMBER,
ip_separator IN VARCHAR2)
RETURN VARCHAR2
IS
actual_line VARCHAR2(150);
BEGIN
FOR idx in 1..ip_line_length LOOP
actual_line :=actual_line ||ip_separator;
END LOOP;
RETURN (actual_line);
EXCEPTION WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
RETURN (null);
END;
使用函数：
函数必须在表达式中或打印中使用
DECLARE
v_length NUMBER :=50;
v_separator VARCHAR2(1):='=';
BEGIN
dbms_output.put_line(f_line(ip_line_length=>v_length,
ip_separator=>v_separator));
END;
返回结果的存储过程:
CREATE OR REPLACE PROCEDURE show_line2
(ip_line_length IN NUMBER,
ip_separator IN VARCHAR2,
op_line OUT VARCHAR2)
IS
actual_line VARCHAR2(150);
BEGIN
FOR idx in 1..ip_line_length LOOP
actual_line :=actual_line ||ip_separator;
END LOOP; op_line :=actual_line;
EXCEPTION WHEN OTHERS THEN
dbms_output.put_line(SQLERRM); op_line :=null;
END;
调用返回结果的存储过程：
DECLARE
v_length NUMBER :=50;
v_separator VARCHAR2(1):='=';
v_line VARCHAR2(150);
BEGIN
show_line2(v_length,v_separator,v_line);
dbms_output.put_line(v_line);
END;
定义带缺省值的存储过程：
CREATE OR REPLACE PROCEDURE show_line3
(ip_line_length IN NUMBER DEFAULT 50,
ip_separator IN VARCHAR2 DEFAULT '=')
IS
actual_line VARCHAR2(150);
BEGIN
FOR idx in 1..ip_line_length LOOP
actual_line :=actual_line ||ip_separator;
END LOOP;
DBMS_OUTPUT.PUT_LINE(actual_line);
EXCEPTION WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
END;
练习：
--1、作一存储过程和函数，完成下面的功能：
--2、输入姓名，课程名，成绩
--3、该过程完成对SC表的插入或修改操作，若插入成功，返回成功信息，若该选课信息已经存在，则修改其成绩为输入的成绩，若遇系统错误，返回错误信息。
函数的应用实例：
--传入学生学号，得出学生的相关信息
create or replace function f_get_strM(vsno varchar2) return varchar2 is
v_strM varchar2(300);
begin
select rpad('学号：' || sno ,12, ' ')||rpad('姓名：' || sname ,15, ' ') ||rpad( '系别：' || sdept,20,' ') into v_strm
from student where sno = vsno;
return(v_strm);
exception
when no_data_found then v_strm := '没有对应的学生';
return(v_strm);
--可能的others情况：定义的长度不够
when others then v_strm := sqlerrm; return(v_strm);
end;
--在查询中调用上面函数
select f_get_strm(sno) 学生基本信息 from student;
存储过程权限：
--在System下创建如下过程
create or replace procedure p1
is
vsname varchar2(20);
begin
select sname into vsname from student where sno='001';
dbms_output.put_line(vsname);
end;
--创建u1用户
create user u1 identified by u1;
grant connect to u1;
--在System下创建如下过程
--为u1用户赋执行p1的权限
Grant execute on p1 to u1;
--U1在没有对Student的访问权时，是否能通过执行P1，查看查询结果？
动态sql
简介
静态sql与动态sql：
- 静态sql
- Oracle编译PL/SQL程序块为前期联编，即sql语句在程序编译时就已经确定
- 动态sql
- Oracle编译PL/SQL程序块为后期联编，即SQL语句只有在运行阶段才能建立
使用动态sql的原因：
当查询条件为用户输入时，那么Oracle的SQL引擎就无法在编译期对该程序语句进行确定，只能在用户输入一定的查询条件后才能提交给SQL引擎进行处理。（执行DDL语句）
语句结构：
Excute immediate 动态SQL语句
into 输出参数列表
using 绑定参数列表
语句说明：
- 动态SQL是指DDL和不确定的DML（即带参数的DML）
- 绑定参数列表为输入参数列表，即其类型为in类型，在运行时刻与动态SQL语句中的参数（可以理解为函数里面的形式参数）进行绑定。
- 输出参数列表为动态SQL语句执行后返回的参数列表。
- 由于动态SQL是在运行时刻进行确定的，所以相对于静态而言，其更多的会损失一些系统性能来换取其灵活性。
实例：
--设数据库中表 STUDENT
--其数据为如下：
--SNO SNAME SAGE
--001 S01 21
--002 S02 21
--003 S03 22
--要求：
--1．创建该表并输入相应的数据。
--2．根据SNO可以查询到SNAME和SAGE的信息。
--3．根据大于SAGE的查询相应的信息。
--根据要求，可使用动态SQL创建三个过程实现：
--1. 创建该表并输入相应的数据。
create or replace procedure create_table as
begin
execute immediate
'create table STUDENT(SNO varchar2(3),SNAME
varchar2(20),SAGE number) '; --动态SQL
execute immediate
'insert into STUDENT values (''001'', ''S01'',21) ';
execute immediate
'insert into STUDENT values (''002'', ''S02'',21) ';
execute immediate
'insert into STUDENT values (''003'',''S03'',22) ';
Commit;
end create_table;
--2．根据SNO可以查询到SNAME和SAGE的信息
create or replace procedure find_info(v_sno varchar2) as
v_SNAME varchar2(20);v_SAGE number;
Begin
execute immediate
'select SNAME,SAGE from STUDENT
where trim(SNO)=:1' --占位符
into v_SNAME,v_SAGE --动态SQL查询
using v_sno;
dbms_output.put_line(v_SNAME ||to_char(v_SAGE));
Exception When others then
dbms_output.put_line('找不到相应数据');
end find_info;
--3．根据大于SAGE的查询相应的信息。
create or replace procedure f_STU(p_SAGE number) as
r_STU STUDENT%rowtype;
type c_type is ref cursor;
c1 c_type;
begin
open c1 for 'select * from STUDENT where SAGE >= :1'
using p_SAGE;
loop
fetch c1 into r_STU;
exit when c1%notfound;
dbms_output.put_line('AGE大于'|| to_char(p_SAGE) || '为:');
dbms_output.put_line('SNO为'||to_char(r_stu.sno) ||'其SNAME为:'||r_STU.sname);
end loop;
close c1;
end f_stu;
注意：
- 建表语句写在动态sql中时，往表中插入数据也必须写在动态sql中，否则会编译错误，因为在过程中不执行动态sql，此时尚未建表
构造动态sql
构造动态sql语句并执行返回结果集的方法：
- 方法一：在数据访问层构造sql语句
- 需要将整个SQL的构造过程放在DataAccess层，业务逻辑发生变化，修改不方便，而且每次查询需要传递给数据库很长的查询字符串，传递参数的效率也不高。
- 方法二：在存储过程中构造动态sql语句并执行
- 只需要传递给存储过程一些参数，使用游标返回数据。参数传递效率较高，而且业务逻辑在存储过程中，调整比较方便。该方法关键的在下面的语句：Open C_Projects For v_SQL;它直接使用游标打开构造的查询字符串即可。
注意事项：
- 构造的SQL语句最后不能带有分号；
- SQL语句中对于字符和字符串的条件需要用单引号包括起来
- 最重要：动态SQL语句需要防止SQL注入攻击。最简单的办法是：只允许一个关键词查询，将关键词中的所有空格去掉。对于多关键词，需要将他们用空格拆开，再构造。
触发器
触发器类似于过程或函数，因为它们都是拥有声明、执行和异常处理过程的带名PL/SQL块。
- 触发器必须存储在数据库中并且不能被块进行本地化声明
- 对于过程而言，可以从另一个块中通过过程调用显式的执行一个过程，同时在调用时可以传递参数；对于触发器而言，当出发时间发生时就会显式的执行该触发器，并且触发器不接受参数
- 触发器事件是在数据库表上执行的DML（insert,update或delete）操作
可以使用触发器做一下事情：
- 维护不可能在表创建时刻通过声明性约束进行的复杂的完整性约束限制。
- 通过记录所进行的修改以及谁进行了修改来审计表中的信息。
- 当表被修改的时候，自动给其他需要执行操作的程序发信号。
触发器包括的部分：触发器名、触发事件和触发器主体
- 触发器名：触发器名的名字控件不同于其他子程序的名字空间，触发器可以使用和表或过程相同的名字；但是，在一个数据模式中，一个名字仅能用于一个触发器
- 触发器事件：触发器事件决定了触发器的类型；触发器可以按照INSERT、UPDATE或DELETE操作进行定义，并且它们也可以点火进行行操作或语句操作。触发器也可以对多种类型的触发语句点火。
触发器的语法结构：
完整的过程结构如下：
CREATE OR REPLACE TRIGGER trigger_name
{before|after|instead of}
{Insert [or delete] [or update [of col1,…coln]}
on {table_name|view_name}
[for each row[when(condition)]]
[DECLARE]
BEGIN
执行语句段;
EXCEPTION
异常处理语句段;
END;
--实例
CREATE OR REPLACE
TRIGGER TR_INSERT_UPDATE_GRADE_SC AFTER INSERT OR UPDATE OF grade ON SC
FOR EACH ROW
BEGIN
if inserting then --插入时触发
dbms_output.put_line('Insert');
end if;
if updating then --修改Grade时触发
dbms_output.put_line('Update');
end if;
END;
注意事项：
- 一个 FOR EACH ROW 执行指定操作的触发器为操作修改的每一行都调用一次（行级触发器）。若不带该参数则表示该触发器为语句级触发器，语句级触发器只在插入，修改或删除时执行一次，无论插入，修改，删除影响的是单行还是多行。
- SELECT 并不更改任何行，因此不能创建 SELECT 触发器。
- 触发器和某一指定的表格有关，当该表格被删除时，任何与该表有关的触发器同样会被删除。
- 在一个表上的每一个动作只能有一个触发器与之关联。
在行级触发器中使用:new和:old
触发语句所处理的每一行都点火一次行级触发器。
在触发器的内部，可以访问当前正在被处理的行。这是通过“伪记录”— :old和:new实现的。:old和:new不是真实的记录。尽管从语法上讲，它们的处理方式和记录是一样的，但实际上它们不是，所以它们就被称为伪记录。
它们的类型是：
Triggering_table%rowtype
注意
- INSERT语句没有对:old进行定义，DELETE语句没有对:new进行定义。如果在INSERT中使用了:old或者在DELETE中使用:new，PL/SQL编译器将不会产生错误。但是它们的取值都将是NULL。
- 尽管:new和:old在语法上被作为R0WTYPE的记录进行处理，但是实际上它们并不是记录。结果，许多通常对记录是有效的操作对于:new和:old是无效的。
内置函数RAISE_APPLICATlON_ERROR
用户定义消息从块中传递到调用环境中的方式和Oracle错误是一样的。
RAISE_APPLICATlON_ERROR的语法如下：
RAISE_APPLICATlON_ERROR(ErrorNumber,ErrorMessage,[keep_errors])
这里ErrNumber是从-20,000到-20,999之间的参数
ErrorMessage是与此错误相关的正文，ErrorMessage必须是不多于512个字节
keep_errors是一个布尔值，keep_errors是可选曲。如果keep_errors是TRUE，那么新的错误将被添加到已经引发的错误列表中(如果有的话)。如果它是FALSE(这是缺省的设置)，那么新的错误将替换错误的当前列表。
触发器实例：
--修改STUDENT表数据时，限制不能修改学生的系别（不能修改CS系学生的系别）。
create or replace trigger tri_student_update
before update of sdept on student
for each row
when (old.sdept=‘CS’) --old前没有“:”
begin
(-20000,'不能修改CS系学生的系别！');
end;
instead-of触发器
Instead-of触发器仅可以定义在视图上，并且它们可以替代点火它们的DML语句进行点火
Instead-of触发器必须是行级的。Instead-of触发器是必要的，因为定义触发器的视图可能基于联结(join)并且并非所有的联结都是可更新的。该触发器便可以按照所需的方式执行更新。例如：
定义下面的视图：
create or replace view stu_avg as select sno,avg(grade) stu_avg from sc group by sno;
若执行下面语句：
delete from stu_avg where sno=‘001’;
则报错：
ORA-01732: 此视图的数据操纵操作非法
创建触发器：
create or replace trigger stu_avg_delete
instead of delete on stu_avg
for each row
begin
delete from sc where sno=:old.sno;
end;
再执行删除语句，则删除成功。
对触发器的限制
触发器的主体是一个PL/SQL块。在PL/SQL块中可以使用的所有语句在触发器主体中都是合法的，但是要受到下面限制的约束：
- 触发器不能使用事务控制语句：COMMIT，ROLLBACK或SAVEPOINT。触发器作为触发语句执行的一部分被点火，它和触发语句在同一个事务中。当触发语句被提交或撤回提交时，触发器的操作也相应被提交或撤回提交。
- 由触发器主体调用的任何过程或函数都不能使用事务控制语句。
- 触发器主体不能声明任何LONG或LONG RAW变量。而且，:new和:old不能指向定义触发器的表中的LONLONG或LONG RAW列。
- 触发器主体可以访问的表有所限制。根据触发器类型以及在表上的约束限制的不同，表可能也会变化。
查询、删除和禁止触发器
在user_triggers视图中可以查询触发器：
select * from user_triggers where trigger_name=upper(‘stu_avg_delete’);
删除触发器：
Drop trigger stu_avg_delete;
禁止和打开触发器：
alter trigger stu_avg_delete disable｜enable;
禁止和打开一个表的所有触发器：
alter table r enable|disable all triggers;
当包或者子程序存储在数据字典中时，存储的除了该对象的源代码还有经过编译的p-code。但是对于触发器就不是这样的。在数据字典中唯一存储的是触发器的源代码,而不是p-code。结果，每次当从数据字典中重新读出触发器时，必须要进行编译。
触发器点火次序
当DML语句被执行时就会点火触发器。下面给出了执行DML语句的顺序：
- 执行BEFORE语句级触发器(如果有的话)
- 对于受语句影响的每一行：
- 执行BEFORE行级触发器(如果有的话)
- 执行DML语句。
- 执行AFTER行级触发器(如果有的话)
- 执行AFTER语句级触发器(如果有的话)
触发器实例：
CREATE OR REPLACE TRIGGER ai_org_trig
AFTER INSERT ON org_tab
FOR EACH ROW
BEGIN
UPDATE sec_hrc_audit
SET num_rows =num_rows+1
WHERE hrc_code =:NEW.hrc_code;
IF (SQL%NOTFOUND) THEN
INSERT INTO sec_hrc_audit VALUES (:NEW.hrc_code,1);
END IF;
END;
CREATE OR REPLACE TRIGGER ai_org_trig_statement
AFTER INSERT ON org_tab
BEGIN
FOR idx IN (SELECT hrc_code,COUNT(*) cnt
FROM org_tab
GROUP BY hrc_code) LOOP
UPDATE sec_hrc_audit SET num_rows =idx.cnt
WHERE hrc_code =idx.hrc_code;
IF (SQL%NOTFOUND) THEN
INSERT INTO sec_hrc_audit VALUES (idx.hrc_code,idx.cnt);
END IF;
END LOOP;
END;
变化表
触发器主体可以访问的表和列上有一些限制。在定义这些限制以前，必须先要认识变化表和限制表。
**“变化表”**是被DML语句正在修改的表。对于触发器而言，它就是定义触发器的表。需要作为DELETE CASCADE参考完整性限制的结果进行更新的表也是变化的。
DELETE CASCADE：级联删除
alter table sc add constraint fk_sc_sno foreign key(sno) references student(sno) on delete cascade;
限制表
“限制表”是可能需要对参考完整性限制执行读操作的表。
比如，对于SC表上的操作进行触发器定义时，SC为变化表，Student和Course表为限制表。（假设SC表上定义了两个外键）
行级触发器主体中的SQL语句不允许进行：
- 读取或修改触发语句的任何变化表。这些表也包括触发表自己。
- 读取或修改触发表的限制表中的主键、唯一值列或外键列。但是如果需要的话，可以修改其他的列。
这些限制法则适用于所有的行级触发器。仅当在执行DELETE CASCADE操作时启动语句触发器时，它们才适用于语句触发器。
消除变化表错误：
触发表仅仅对于行级触发器是变化的。这就是说，我们不能在行级触发器中查询它，但是可以在语句级触发器中查询它。但是，我们不能简单地将行级触发器转换为语句触发器．因为我们需要在触发器主体中使用:new, :old的取值。解这个问题的方法是创建两个触发器：一个行级触发器，另一个是语句级触发器。在行级触发器中，我们记录 :new, :old的取值，但是不对该表进行查询。在语句级触发器中执行查询并使用行级触发器记录下的数值。
约束
约束的检查功能：
- 立即执行的约束(Immediate constraints)：语句执行完后立即检查是否违背完整性约束
- 延迟执行的约束(Deferred constrainsts)：完整性检查延迟到整个事务执行结束后进行
创建和删除约束示例：
Alter table sc add constraint fk_sc_sno foreign key(sno) references student(sno) deferrable;--定义该约束为可延迟约束并设置为延迟约束
--当定义约束时使用了Deferrable选项后，就可以设置该约束为立即执行约束还是延迟约束，若定义约束时没有Deferrable选项，则不能将其设置为延迟约束。
Set constraint fk_sc_sno immediate
Set constraint fk_sc_sno deferred
总结
- 触发器是一种特殊的过程，他不能被显式的调用，而是自动触发
- 有三类触发器：分别为插入触发器，删除触发器和修改触发器；对于视图有INSTEAD OF触发器。
- 触发器分为行级触发器和语句级触发器
- 变化表不能在行级触发器中读或写
- 触发器中可以自行抛出异常，使用的语句为： RAISE_APPLICATlON_ERROR
扫一扫
481
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
