大型实用数据库概论Oracle12C 复习笔记

文章整理自周法国老师的《大型实用数据库概论》课程笔记。

1 Oracle基础

与MySQL、MSSQL不同，Oracle数据库系统是美国ORACLE（甲骨文）公司提供的一款分布式大型关系数据库管理系统数据库有“ 表空间 ”组织数据。

1.1 Oracle基础概念

• 数据库是由一个或多个被称为表空间（tablespace）的逻辑存储单位构成。
• 表空间内的逻辑存储单位为段（segment）
• 段又可以继续划分为数据扩展（extent）
• 数据扩展是由一组连续的数据块（data block）构成。

①逻辑存储结构：
数据块：最精细的数据存储粒度是数据块（data block）。一个数据块相当于磁盘上一段连续的物理存储空间。
数据扩展：由一组连续的数据块（data block）构成、比数据块更高一层的数据库逻辑存储结构，用于存储信息。
数据段：当一个段中已有空间已经用完，Oracle为这个段分配新的数据扩展。当一个段中已有空间已经用完，Oracle为这个段分配新的数据扩展。
表空间：表空间是一个或多个数据文件的逻辑表示。 每一个Oracle数据库都包含名为 SYSTEM和SYSAUX 的表空间，她们在数据库创建时由Oracle自动创建。只要数据库处于开启（open）状态，SYSTEM 表空间就一定是联机（online）的；SYSAUX 表空间（tablespace）是 SYSTEM 表空间的一个辅助性表空间。Oracle中很多组件使用 SYSAUX 表空间作为默认的数据存储位置。

②物理存储结构：
数据文件(dbf)：用来存储真实物理数据的，所以数据文件是存储数据的物理概念，一个Oracle数据库可以拥有一个或多个物理的数据文件。每个表空间都是由一个或多个数据文件构成的。一个数据文件只能由一个数据库的一个表空间使用。
控制文件：数据库控制文件（control file）是一个二进制文件，供数据库启动及正常工作时使用。在数据库运行过程中，控制文件会频繁地被Oracle修改，因此数据库处于开启状态时控制文件必须可写。如果控制文件因故不能访问，数据库也将无法正常工作。 每个控制文件只能供一个Oracle数据库使用。
数据字典：Oracle数据库的重要组成部分，用来存放Oracle数据库所有的信息，其用途是用来描述数据的，它由一系列拥有数据库元数据信息的数据字典表和用户可以读取的数据字典视图组成，存放在SYSTEM表空间中。Oracle中的数据字典有静态和动态之分，静态数据字典主要由表和视图组成，静态数据字典中的表是不能直接被访问的，而视图是可以直接访问的；动态数据字典包含了一些潜在的由系统管理员如SYS维护的表和视图，由于这些表和视图在数据库运行时会不断进行更新，故称之为动态数据字典（或者动态性能视图），这些视图提供了关于内存和磁盘的运行情况，所以用户只能对其进行只读访问而不能修改。

1.2 Oracle体系结构

Oracle 数据库服务器由一个数据库和至少一个数据库实例组成。

数据库是磁盘上存储数据的一组文件的集合，这些文件可以独立于数据库实例存在，Oracle的数据库是基于多租户架构的。
$$数据库=重做文件+控制文件+数据文件+临时文件$$
数据库实例则是管理数据库文件的内存结构。一个数据库实例有一组后台进程 和 一个称为系统全局区（SGA）的共享内存区组成。
$$ORACLE实例=进程+进程所使用的内存(SGA)$$

此外，数据库是由后台进程组成。
数据库和实例是紧密相连的，所以我们一般说的 Oracle 数据库，通常指的就是实例和数据库。

1.3 多租户架构

多租户架构（multitenant architecture）使得Oracle数据库可以作为一个包含0个、1个或者多个用户创建的可插拔式数据库（Pluggable Database, PDB）的多租户容器数据库（Container Database, CDB）。

PDB是一个模式、模式对象和非模式对象的便携式集合，以一个非CDB形式展现给Oracle Net客户端。Oracle Database 12c之前的数据库都是非CDB。

容器（container）可以是一个PDB或者root容器（也称为root）。Root容器是一个模式、模式对象和非模式对象的集合，所有的PDB都属于root。

上图中显示了一个拥有4个容器的CDB：root，种子PDB以及2个PDB。每个PDB拥有自己专有的应用，并且由它自己的PDB管理员进行管理。

多租户体系结构的优势：（1）数据库整合优势（2）可管理性优势

1.4 Oracle进程

一个Oracle数据库实例包括三种类型的进程：用户进程、服务器进程和后台进程。服务器进程和后台进程统称为Oracle进程。一般情况下，Oracle进程(服务器)和用户进程(客户端)运行在不同的计算机上。

1.用户进程/ 客户端进程（User Process/ Client Process），可以看做是一些试图连接数据库服务器的软件，例如客户工具。用户进程可以使用Oracle网络服务（Oracle Net Services）与数据库进行通信。
Oracle网络服务是一组通过网络连接协议提供连接的足迹，对于应用开发人员和数据库管理员来说，Oracle网络服务屏蔽了不同硬件平台上设置不同网络的复杂性。

2.服务器进程（Server Process）用于处理连接到该数据库实例的用户进程的请求。用户进程一直都会通过一个服务器进程与数据库进行通信，当Oracle 网络服务接收到用户进程的连接请求后，就会将用户进程路由到一个服务器进程。

服务器进程负责在用户进程和Oracle实例之间调度请求和响应。当用户进程提交某请求后，服务器进程则负责执行该请求，即将数据从磁盘读入缓存，获取请求结果，然后向用户进程返回结果。即使响应出现了某些错误，服务器进程也会把错误信息发给用户进程，以便用户进程进行合适的处理。
可以根据服务器的体系结构，在用户进程和服务器进程之间维护这种连接，以便不需要重新建立连接就可以管理随后的请求。在Oracle系统中，有两种不同的体系结构，即专用服务器模式和共享服务器模式，这两种连接模式都可以连接用户进程和服务器进程。

在专用服务器（Dedicated Server）连接模式下，每个用户进程都关联一个且仅一个服务器进程。专用服务器为用户进程和服务器进程之间提供了一种一对一的映射关系，比如连接到某数据库实例的20个用户进程就相应地关联着20个服务器进程。每一个用户进程仅与它关联的服务器进程之间进行通信，而每一个服务器进程在其会话存在期间专门为与其关联的用户进程提供专用服务。服务器进程在它的系统全局区（PGA）中存储特定的进程信息和用户全局区（UGA）。
在共享服务器（Shared Server）连接模式下，多个用户进程通过网络连接到一个调度程序(服务器代理)，而不是连接到一个服务器进程，该调度程序负责在用户进程与服务器进程之间进行路由。比如，20个用户进程连接到1个调度程序，通过该调度程序，所有用户进程共享同一个或多个服务器进程。

3.Oracle监听器（Listener）是一个运行于Oracle数据库服务器上的进程，其主要任务是监听来自客户应用的连接请求。
客户负责在初始化连接请求中向监听器发送服务名称。该服务名称是一个标识符，它可以唯一地标识客户试图连接的数据库实例。监听器可以接受请求，判断请求是否合法，然后将连接路由到适当的服务器进程中。当然，Oracle监听器不仅能够监听数据库实例，还可以监听其他服务，例如HTTP服务器和IIOP服务器。

4.后台进程（Background Process）是供多进程Oracle数据库使用的附加进程，无论用户是否连接数据库，这些进程都会作为数据库的一部分在运行，每一个后台进程都有自己独立的职责，但也需要与其它进程协同工作。如果这些后台进程崩溃了，数据库也就随之崩溃了。后台进程主要执行操作数据库所需要的维护任务以及最大限度地提高数据库的性能。
Oracle数据库在数据库实例启动时自动运行后台进程，一个数据库实例可以有很多后台进程，并不是所有的后台进程都是一直存在并运行于数据库配置中。有些后台进程是强制的，有些是可选的，还有一些是从属的。

强制的：
①进程监视器（Process Monitor Process, PMON）：进程监视器用于监视其他后台进程。
②监听注册器（Listener Registration Process, LREG）：监听注册器用来注册数据库实例信息以及与Oracle网络监听器关联的调度程序相关的信息
③系统监视器（System Monitor Process, SMON）
④数据库写入器（Database Writer Process, DBW）
⑤日志写入器（Log Writer Process, LGWR）
⑥检查点（Checkpoint Process, CKPT）
⑦可管理性监视器（Manageability Monitor Processes, MMON&MMNL）
⑧恢复器（Recoverer Process, RECO）
可选的：
①归档器（Archive Process, ARCn）：只有在数据库处于归档模式（ARCHIVELOG mode），且自动归档处于开启状态时，这些归档进程才存在。
②作业队列（Job Queue Process, CJQ0 & Jnnn）
③闪回数据归档器（Flashback Data Archive Process, FBDA）
④空间管理协调器（Space Management Coordinator Process, SMCO）
从属的：
①I/O从属进程
②并行执行（Parallel Execute, PX）服务器进程

1.5 内存结构

当Oracle数据库实例开启时，Oracle数据库分配一块内存区域，并启动后台进程。和Oracle数据库相关的基础内存结构主要包括：系统全局区（System Global Area, SGA）、程序全局区（Program Global Area, PGA）、用户全局区（User Global Area, UGA）和软件代码区（Software Code Area）。
1.系统全局区SGA：系统全局区SGA是一组共享的内存结构，称之为SGA组件们，这里面包含了一个Oracle数据库实例的数据和控制信息。所有的服务器进程和后台进程共享SGA。
2.程序全局区PGA：程序全局区PGA是非共享内存区域，它包含的数据和控制信息只能被一个Oracle进程使用。PGA在Oracle 进程启动时分配。每个服务器进程和后台进程都有自己的PGA。所有这些PGA集合起来就是整个数据库实例的PGA，也称作实例PGA。
3.用户全局区UGA：用户全局区UGA是个一个用户会话关联的内存区域。
4.软件代码区：软件代码区是内存的一部分，用来存储正在执行的或可以执行的软件代码。

1.6 文件系统

oracle数据库就是由驻留在服务器的磁盘上的这些操作系统文件组成的。这些文件有控制文件，数据文件和重做日志文件。
1.控制文件：是数据库中的关键性文件，它对数据库的成功启动和正常运行都是至关重要的，因为它存储了在其他地方无法获得的关键信息。（通常是数据库中最小的二进制文件）

2.数据文件：是实际存储插入到数据库中的实际数据的操作系统文件。数据以一种oracle特有的格式被写入到数据文件，其他程序无法读取数据文件中的数据。数据文件的大小与他们所存储的数据量的大小直接相关。
除了SYSTEM表空间之外，任何表空间都可以有联机状态切换为脱机状态。当表空间进入脱机状态时，组成该表空间的数据文件也就进入脱机状态了。可以将表空间的某一个数据文件单独的设置为脱机状态，以便进行数据库的备份或恢复，否则是不能备份的。
3.重做日志文件：当用户对数据库进行修改的时候，实际上是先修改内存中的数据，过一段时间后，再集中将被村中的修改结果成批的写入到上面的数据文件中。Oracle利用“（联机）重做日志文件”随时保存修改结果，即Oracle随时将内存中的修改结果保存到“重做日志文件”中。因此，即使发生故障导致数据库崩溃，Oracle也可以利用重做日志文件中的信息来恢复丢失的数据。Oracle是以循环方式来使用重做日志文件的，所以每个数据库至少需要两个重做日志文件交替使用。
4.其他文件：包括参数文件、口令文件、归档重做日志文件、预警和跟踪文件等。

2 SQL*Plus

SQL* Plus是Oracle数据库的主要SQL* Plus命令、SQL语句、PL/SQL块程序开发与运行环境，在Oracle12c中的SQL*Plus是以CMD命令行方式启动和运行的，没有图形化的用户接口，不区分大小写。
SQL* Plus命令：格式化查询结果、设置选择、编辑及存储SQL命令、以设置查询结果的显示格式，并且可以设置环境选项。
SQL语句：数据定义语言DDL、数据操纵语言DML和数据控制语言DCL以及数据存储语言DSL。SQL * Plus将SQL语句保存在内部缓冲区中。SQL * PLUS命令输入完毕后可直接执行，而当SQL命令输入完毕时，需要在末尾加上分号（；），否则SQL * PLUS不识别。也可以采用换行并输入斜线（/）来表示SQL命令完毕。
PL/SQL块：是Oracle数据库对SQL语句的扩展。把数据操作和查询语句组织在PL/SQL代码的过程性单元中，通过逻辑判断、循环等操作实现复杂的功能或者计算的程序语言。PL/SQL块同样以数据为操作对象，使用PL/SQL块可编写过程、触发器和包等数据库永久对象。

2.1 登录与连接

Sqlplus登录SQL*Plus环境，进入SQL>

sqlplus username/password@[server_ip:port/]server_name [as sysoper|sysdba]

退出SQL*Plus环境，只需要在SQL>提示符下输入

quit

断开数据库连接命令

DISC[ONNECT]

连接数据库命令

CONN[ECT] [username[/password][@server_name] [AS SYSOPER|SYSDBA]]

修改用户密码命令

PASSW[ORD] [username]

2.2 SQL*Plus 编辑命令

在SQL*PLUS中执行的SQL语句或PL/SQL块会临时存放在SQL缓冲区中，直到新的语句将其覆盖。通过SQLPLUS的编辑命令，可以显示、修改和运行这些存放在缓冲区中的语句。
list命令：列出$SQL\ast PLUS$缓冲区中指定的一行或若干行内容，n和m表示行的序号， 表示当前行，LAST表示最末行，省略所有参数的LIST命令表示显示所有行内容。

L[IST] [n|n m|n *|n LAST|*|* n|* LAST|LAST]

n命令:指定缓冲区的当前行。在SQL*PLUS中执行SQL语句时，默认情况下是将最后一行命令作为当前行。

CHANGE命令:用于修改缓冲区当前行中第一次出现的指定文本，仅替换第一次出现的old。

C[HANGE] / old [/ [new]] 

APPEND命令：用于将文本添加到缓冲区中当前行的最后面，其中text为追加到缓冲区中的文本。

A[PPEND] text

DEL命令：用于删除SQL*PLUS缓冲区中指定的一行或若干行内容。

DEL [n|n m|n *|n LAST|*|* n|* LAST|LAST]

INPUT命令：在缓冲区中当前行后面新增一行（在第一行前插入直接输入：0 文本即可）其中text为需要增加的内容，新增的内容text与INPUT之间要留一个空格；如果新添加的行以空格开头，则在命令和 text 间要多输入一个空格；如果要在当前行后面添加多个行，可只输入 INPUT。然后 INPUT 将提示输入每行的文本；要退出INPUT可输入一个空行或一个句点。

I[NPUT] [text] 

EDIT命令：调用操作系统的文本编辑器（Windows操作系统默认的文本编辑器是记事本notepad）编辑缓冲区中的内容或指定的文件。

ED[IT] [file_name[.ext]] 

run和 / 命令：run命令和 / 命令用来执行缓冲区中存储的内容(SQL语句或PL/SQL块),二者的区别是RUN命令执行缓冲区内容时，会先列出缓冲区中的内容；而/命令直接运行缓冲区的内容而不显示出来。

RUN或/

CLEAR BUFFER命令:用来清空缓冲区中的内容。

CL[EAR] BUFFER

2.3 SQL*Plus 文件命令

SQL缓冲区中的内容需要经常执行，那么可以将这些内容保存到外部的SQL脚本文件.

SAVE命令：把SQL*Plus缓冲区中的内容保存到操作系统的一个文件中（SQL脚本）

SAV[E] file_name[.ext] [CRE[ATE]|REP[LACE]|APP[END]]

.ext文件扩展名，缺省为.sql，也可以指定其它扩展名
CRE[ATE],文件不存在会创建一个新文件，缺省选项
REP[LACE],若文件存在，则用当前缓冲区内容替换；否则创建
APP[END],若文件存在，则将当前缓冲区内容追加到文件末尾；否则创建

GET命令：和SAVE命令相反，将指定文件中的内容加载到SQL缓冲区中。

GET file_name[.ext] [LIST|NOLIST] 

其中file_name[.ext]为指定加载的文件，省略扩展名时表示是sql脚本文件；LIST参数表示加载后显示加载的内容（此参数为默认值）；NOLIST参数则禁止显示加载的内容。

START和@命令：用于运行sql脚本文件。

STA[RT]  file_name[.ext] 或者 @  file_name[.ext]

其中file_name[.ext]为要执行的脚本文件名，省略扩展名默认为.sql。

@@命令：和@命令类似，也用于运行sql脚本文件。但@@命令除了在SQL*PLUS里使用外，还可以用在脚本文件里，用来调用与该脚本文件在同一目录下的其它脚本文件。

2.4 SQL*Plus 注释命令

单行注释或行尾注释 --：--符号可以实现在单行里添加注释，–后边的内容均为注释
多行注释或行间注释 /* …… */：/* [注释内容]*/ 符号可以实现添加多行注释语句，或注释行中的一部分。
REMARK行注释：REM[ARK] [text]在脚本中添加一行注释。与前面两种注释符号不同的是，用REMARK命令添加注释必须是在单独的一行中，不能即有注释又包含其他命令。

2.5 SQL*Plus 交互命令

运行SQL脚本，可以根据使用者不同的输入参数得到不同的结果。

替换变量&和&&命令：要使SQL语句动态的获得相关参数，必须要有相应的变量来保存这些参数值。&和&&命令用于在SQL*PLUS中引用这些变量。如果变量为数值型数据，就直接引用该变量；如果变量为字符型或日期型数据，则&变量名或&&变量名必须用单引号括起来；如果变量没有事先定义，则在用&命令引用该变量时，$SQL\ast PLUS$会提示输入该变量的值；&&命令和&命令类似，区别在于&命令所引用的变量只在当前语句中起作用，而&&命令引用的变量会在当前$SQL\ast PLUS$环境中一直生效。

&变量名  或  &&变量名

DEFINE命令:用来定义用户变量并为它赋一个CHAR类型的值，或者列出一个变量或所有变量的值和类型。 该变量在当前SQL*PLUS环境中有效。DEFINE命令所定义的变量，不管其赋值的text内容是否带单引号，其类型都是CHAR类型；如果text的内容里有空格或要区分大小写，则定义时必须用单引号括起来；DEFINE后面带变量名可以显示变量的值；如果DEFINE命令不带任何变量，则显示出所有定义的变量，包括系统变量。

DEF[INE] [variable] | [variable = text]

UNDEF[INE]命令:用于删除一个或多个定义的变量。

UNDEF[INE] variable [, variable[, …]]

PROMPT命令:在屏幕上显示指定的消息或者显示一个空行。一般程序在某个SQL语句之前，作为提示信息来使用。

PRO[MPT] [text]

PAUSE命令:暂停执行后面的语句，并可显示指定的提示文本，然后等待用户按下RETURN键后再继续后面的命令。一般用在多个SQL语句中间，在一个SQL执行结束后，提示下一个语句开始，通常PROMPT和PAUSE命令多用在脚本文件里。

PAU[SE] [text]

ACCEPT命令：用于定义相应的变量来接收用户的屏幕输入。ACCEPT命令比DEFINE命令更加灵活，可以定义CHAR、NUMBER、和DATA类型变量，还可以指定输入提示、输入格式及隐藏输入内容等。ACCEPT命令定义的变量，若省略变量类型参数的话，默认是CHAR类型；ACCEPT命令还可以和PROMPT提示命令结合使用；HIDE参数表示不显示输入的内容。

ACC[EPT] variable [NUM[BER]|CHAR|DATE] [PROMPT text|NOPR[OMPT]] [HIDE] 

2.6 SQL*Plus 绑定变量

在PL/SQL子程序中声明的变量，不能在SQL* Plus中进行显示。通过在 PL/SQL中使用绑定变量时则可从SQL* Plus访问这个绑定变量。
绑定变量是在 SQL*Plus 中创建并可在PL/SQL或SQL中引用的变量。可使用绑定变量保存返回值或者调试PL/SQL子程序。

①定义绑定变量：在SQL*PLUS用VARIABLE命令来定义绑定变量。

VAR[IABLE] 变量名 变量类型;

②引用绑定变量：在SQL语句和PL/SQL程序块中要引用绑定变量，需要在变量名前面加个冒号(:)。

-- 在SQL*Plus中使用`EXECUTE命令`给绑定变量赋值。
EXEC[UTE] :绑定变量名 := 值

-- 在PL/SQL程序块中给绑定变量赋值。
:绑定变量名 := 值;

③显示绑定变量：使用VARIABLE命令显示定义的绑定变量信息。显示绑定变量名及其类型信息；不带任何参数的VARIABLE命令将显示所有定义的绑定变量。

VAR[IABLE] [绑定变量名]

④显示绑定变量的值：使用PRINT命令显示绑定变量的值。显示绑定变量及其值的信息；不带任何参数的PRINT命令将显示所有定义的绑定变量的值。

PRINT [绑定变量名]

2.7 SQL*Plus格式化查询结果

通过SQL* PLUS来生成查询报表，重新设置列标题、重新定义值的显示格式和显示宽度为报表增加头标题和底标题等操作。

(1)格式化列：SQL*PLUS里用COLUMN命令来对查询结果的列部分进行格式化操作。

COL[UMN] [{ column|expr} [ option ...]]

option参数：
①HEA[DING] text
用于改变列的标题显示内容；如果列标题需要换行显示，则在需要换行的地方加‘|’符号分隔。
② FOR[MAT] format
FORMAT参数用于设置列中数据的显示格式。使用FORMAT An可把某个数据类型的宽度改为n；如果指定的宽度比列标题短，SQL*Plus 将列标题换行显示；对于数值列，可以用FORMAT参数指定其显示的形式,表4.1列出了最常用到的几种数字显示格式。

③ALI[AS] alias
ALIAS参数可为列指定一个别名，该别名可以在其它COLUMN命令中被引用。
④ CLE[AR]
CLEAR参数用于清除对指定列所做的格式设置。
⑤ JUS[TIFY] [format]
JUSTIFY参数用来对齐标题。如果没有使用JUSTIFY参数，NUMBER列的标题默认是右对齐，其它列的标题缺省是左对齐。JUSTIFY参数设置的对齐方式有三种：L[EFT] （左对齐）、C[ENTER] （居中对齐）、R[IGHT] （右对齐）。
⑥ NOPRI[NT]|PRI[NT]
用来控制列是否要在屏幕上显示出来（包括列标题和该列的数据）。PRINT表示要显示和打印列，NOPRINT则不显示和打印列

(2)定义页与报告的标题和脚注：SQL* PLUS查询结果的显示通常会包括页标题、列标题、查询结果数据和脚注几个部分。SQL* PLUS提供了TTITLE和BTITLE命令分别对页标题和脚注进行定义和设置。
① TTITLE命令
TTITLE命令用于定义和设置页标题的内容及格式。

TTI[TLE] [printspec [text|variable] ...] [ON|OFF]

其中text|variable表示页标题显示的内容。其中text是标题文本，variable是包含标题文本的用户变量；printspec用来对text的格式进行设置，常用到的参数选项有LE[FT]、CE[NTER]、R[IGHT]、COL n和S[KIP] [n]。LE[FT]、CE[NTER]、R[IGHT]分别表示页标题左对齐、居中对齐和右对齐；COL n表示页标题缩进到当前第n列，这里的列指的是打印的位置而不是表中的列；S[KIP] [n]表示页标题向下跳n行，省略n表示跳1行；0表示回到当前行的开始处；
ON|OFF参数表示开启或关闭页标题显示。定义页标题时SQL*PLUS自动开启页标题显示功能；关闭页标题显示不会删除对它的定义。不带任何参数的TTITLE命令将显示当前页标题的定义内容。
② BTITLE命令
BTITLE命令用于定义和设置页注脚，其用法和TTITLE命令类似。

BTI[TLE] [printspec [text|variable] ...] [ON|OFF]

(3) 存储和打印结果：如果要把查询的结果保存到外存的文件里并打印，可以通过SQL* PLUS里的SPOOL命令来实现。SQL* PLUS里的SAVE命令仅仅是将缓冲区中的SQL语句保存为单独的文件，而SPOOL命令则是将执行的结果也保存到文件中。

SPO[OL] [file_name[.ext]|OFF] [CRE[ATE]|REP[LACE]|APP[END]]

执行SPOOL命令时，先建立一个假脱机文件，并将随后SQL*PLUS屏幕中的所有内容保存到该文件，最后使用SPOOL OFF命令关闭假脱机文件。file_name[.ext]是存放屏幕内容的文件，如果省略后缀，默认后缀是LST；[CREATE]、[REPLACE]、[APPEND]参数的用法参考SAVE命令。

3 Oracle SQL基础(重点)

SQL概述

结构化查询语言SQL包含5个部分：
(1) 数据查询语言(Data Query Language, DQL)
(2) 数据操纵语言(Data Manipulation Language, DML)
(3) 数据定义语言(Data Define language, DDL)
(4) 数据控制语言(Data Control Language, DCL)
(5) 事务控制语言(Transaction Control Language, TCL)

特点：(1) 一体化。(2) 使用方式灵活。(3) 非过程化。(4) 语言简洁，语法简单，好学好用。
总之，SQL是专为数据库而建立的操作命令集，是一门功能齐全、使用简单、操作方便的数据库语言。

本课程的案例模型：

3.1 数据定义DDL

DDL：创建表、修改表、删除表等创建操作，还其它类型对象的相关管理，如视图、索引、序列、同义词以及过程、函数、程序包等。

数据库对象：
Oracle数据库由多个数据库模式组成，每个模式都是一些数据库对象的集合，包括表、视图、索引、序列、同义词、存储过程、函数等。可以通过查询数据字典视图dba_objects、user_objects查询当前数据库和当前用户模式下的对象信息。

DBA身份登录可以使用如下语句查询：当前数据库下的所有对象类型、包含该类型对象的个数：

SELECT object_type, COUNT(object_type)
FROM dba_objects
GROUP BY object_type
ORDER BY object_type;

用户与模式：
用户是指能够连接到数据库的人，用户拥有用户名和密码；模式是一个包含用户所拥有的所有对象的集合。创建用户时，创建该用户的模式，模式是用户拥有的对象，它最初是空的。

基本数据类型：最常用的是字符型(CHAR、VARCHAR2)、数值型(NUMBER)、日期型(DATE)、二进制大对象类型(BLOB)等。

3.1.1 表创建CREATE与删除DELETE

数据库中的每一个表都被一个用户模式所拥有，所以表是一种典型的模式对象。表中的数据是按照行和列的二维结构组成。每一行用来保存关系数据库关系的元组，也称作数据行或记录。每一列用来保存关系的属性，也称为字段。

创建表：使用约束来保证数据库表中数据的完整性，并阻止非法数据进入数据库。

CREATE TABLE dept
        (id			NUMBER(2),
        name		VARCHAR2(20),
        loc			VARCHAR2(15),
        create_date	DATE DEFAULT SYSDATE);

schema是表所属模式名称，和当前用户名称相同，可以省略；table是表名，column是列名，datatype是列数据类型，DEFAULT expr指定列的默认值为expr(若在INSERT语句中省略了该列的值，则使用默认值)。

查看表结构：DESCRIBE不是Oracle SQL的语句，而是Oracle命令，可以使用缩写DESC。由于CREATE TABLE是DDL的语句，该语句执行时会导致事务自动提交。

DESC[RIBE] tablename

完整性约束：

• 实体完整性：主键约束(Primary Key)、唯一值约束(Unique)、非空值约束(NOT NULL)；
• 参照完整性：通过使用外键约束(Foreign Key)；
• 用户自定义完整性：通过使用数据类型(Data Type)、默认值(Default)、检查约束(Check)、空值或非空值约束(NULL|NOT NULL)。

建表 同时 定义约束：

CREATE TABLE tablename(
		column datatype [DEFAULT expr] [column_level_constraint],
			            …
			[table_level_constraint]    
);

schema是表所属模式名称，和当前用户名称相同，可以省略；table是表名，column是列名，datatype是列数据类型，DEFAULT expr指定列的默认值为expr，column_level_constraint用来定义列级约束，table_level_constraint用来定义表级约束；

定义列级约束column_level_constraint：

column [CONSTRAINT constraint_name] constraint_type, …

非空约束NOT NULL：NOT NULL约束只能定义在列级上。非空约束(NOT NULL)确保表中指定列不能包含空值，没有使用NOT NULL约束的列默认包含空值。

唯一约束UNIQUE：UNIQUE约束可以定义为列级约束，也可以定义为表级约束。UNIQUE约束的列或组合列在表中不能出现2行相同的值。UNIQUE约束的列如果没有进行NOT NULL约束的时候允许输入NULL值，并且可以多次输入NULL值，因为Oracle服务器认为空值NULL不等于任何值，即空值NULL永远满足UNIQUE约束。

主键约束PRIMARY KEY：每个表只能定义一个主键约束，主键约束的列或组合列的值要求唯一且非空。每个表只能定义一个主键约束，主键约束的列或组合列的值要求唯一且非空。

外键约束FOREIGN KEY：即可列级，也可表级。
列级约束：

[CONSTRAINT constraint_name ]FOREIGN KEY REFERENCES tablename(mian_column)
[ON DELETE CASCADE|ON DELETE SET NULL]

表级约束：

[CONSTRAINT constraint_name ]FOREIGN KEY(foreign_column) REFERENCES tablename(mian_column) 
[ON DELETE CASCADE|ON DELETE SET NULL]

ON DELETE CASCADE选项指明，在父表中删除一行时，子表中关联的行会级联删除；ON DELETE SET NULL选项指明，在在父表中删除一行时，子表中关联的行会被赋予空值NULL；没有使用任何选项ON DELETE CASCADE或ON DELETE SET NULL的情况下，若子表中有关联的数据，则父表无法进行删除操作。

检查约束CHECK：既可以定义为列级约束，也可以定义为表级约束。定义表中的每一行必须满足的一个条件，一个列上可以定义多个CHECK约束，并且Oracle没有限制CHECK约束的个数。CHECK约束不能定义在伪列上，如CURRVAL、NEXTVAL、LEVEL、ROWNUM等；也不能在调用SYSDATE、UID、USER和USERENV函数时定义CHECK约束

子查询建表：

CREATE TABLE tablename
        [(column[, column[, …]])]
		AS subquery;

其中table是新建表的名字，column和建空表中的意义相同，可以包括列名、默认值、数据类型、长度以及约束情况，subquery是一个SELECT语句，用于将查询结果插入到新建表中。

截断/清空表：TRUNCATE TABLE语句可以删除表中所有行，同时保持表结构不变。TRUNCATE 效率比 DROP TABLE高。

TRUNCATE TABLE tablename

删除表：DROP TABLE语句既可以删除表中数据，同时还会删除表的定义。DROP TABLE把表删除到回收站中，可以通过FLASHBACK闪回操作恢复任何已经删除的表。

DROP TABLE tablename;

FLASHBACK;

3.1.2 修改表结构 ALTER

DML可以修改表中的数据，但不能修改表的结构。想修改表的定义或结构需要使用DDL的语句ALTER TABLE语句，可以添加、删除、修改表中列、约束等的定义。

添加列：

ALTER TABLE tablename ADD(
列名1 类型1 [DEFAULT 默认值],
列名2 类型2 [DEFAULT 默认值],
…
);

修改列：修改已经存在的列的数据类型、长度以及默认值。

ALTER TABLE tablename MODIFY (
列名1 类型1 [DEFAULT 默认值],
列名2 类型2 [DEFAULT 默认值],
…
);

改列名：

ALTER TABLE 表名 RENAME COLUM 列名1 TO 列名2

删除列：

ALTER TABLE 表名 DROP COLUM 列名

逻辑删除列：

ALTER TABLE 表名 SET UNUSED (列名);

使用DROP COLUMN子句删除列会影响数据库的性能，此时可以使用SET UNUSED子句将该列设置为不可用，然后在进行物理删除，这样对数据库的性能影响较小。

添加约束：

ALTER TABLE 表名 ADD CONSTRAINT 约束名 约束类型 (列名);

修改约束：每次仅可以修改一个列的约束。

ALTER TABLE 表名 MODIFY 列名 约束名;

删除约束：每次仅可以删除一个约束。

ALTER TABLE 表名
DROP PRIMARY KEY | UNIQUE (column) | CONSTRAINT 约束名 [CASCADE];

由于每个表上最多只有一个主键约束，故无需事先查询主键约束的名字，直接使用DROP PRIMARY KEY子句即可删除该表上的主键约束；同样，DROP UNIQUE(column)可以删除指定列上的唯一约束；使用DROP CONSTRAINT子句可以删除指定名称的约束;CASCADE选项用于删除与所删除的约束级联相关的约束。

3.2 数据操纵DML

数据操作实现对数据库中的数据进行插入INSERT、删除DELETE以及修改UPDATE操作。

3.2.1 插入INSERT

INSERT INTO 表名 (列名1, 列名2, …) VALUES (值1, 值2, …)  | SELECT子查询;

若VALUES子句中的值包含字符类型和日期类型的常量值，则必须用单引号括起来。如果日期类型的数据不是默认日期格式，则需要使用TO_DATE函数进行强制转换。

3.2.2 更新UPDATE

UPDATE 表名 SET 列名1 = 值1, 列名2= 值2, … | (列名1, 列名2)=SELECT子查询
WHERE 修改条件;

修改条件：标识出需要更新的列，可以由列名、表达式、常量、子查询和比较运算符组成。省略WHERE子句的UPDATE语句，将会修改表中所有行的数据，务必谨慎使用。

3.2.3 删除DELETE

删除表中的行，一次可以删除一行或多行

DELETE 表名 WHERE 删除条件;

3.3 事务控制TCL

Oracle服务器基于事务控制来确保数据的一致性。

事务的执行只有两种结果：要么全部执行，把数据库带入一个新的状态；要么全部不执行，对数据库不做任何修改。

事务是可以把系统带入一个新状态的一系列操作，如果事务被提交，则数据库进入一个新的状态，否则数据库恢复到事务以前的状态。

对事务的操作有两个：提交(COMMIT)和回滚(ROLLBACK)。

COMMIT提交事务时，对数据库所做的修改便永久写入数据库。
ROLLBACK回滚事务时，对数据库所做的修改全部撤销，数据库恢复到操作前的状态。

事务具有四个属性:

原子性（ Atomicity ）：事务要么全部执行，要么全部不执行，不允许部分执行。
一致性（ Consistency ）：事务把数据库从一个一致状态带入另一个一致状态。
独立性（ Isolation ）：一个事务的执行不受其他事务的影响。
持续性（ Durability ）：一旦事务提交，就永久有效，不受关机、系统崩溃等情况的影响。

3.3.1 事物控制语句TCL

TCL事物控制语句：主要有COMMIT、ROLLBACK、SAVEPOINT

事务结束：
①自动终止：当执行一条DDL语句或DCL语句，会进行自动提交，从而隐式结束当前事务。
②主动终止：使用TCL语句COMMIT和ROLLBACK可以显式结束一个事务，使用TCL语句的好处主要有两点，一是可以确保数据的一致性，二是在数据发生永久性改变前可以预览数据的变化。

3.3.2 部分回滚SAVEPOINT

事务中可以通过SAVEPOINT命令设置若干个保存点，这样可以将事务有选择地回滚到某一个保存点。
用户访问数据库时，数据库中的数据是放在缓冲区中的，当前用户可以通过查询操作，浏览对数据操作的结果。

3.3.3 回滚ROLLBACK TO

如果在事务中设置了保存点，并且在事务的最后执行ROLLBACK命令回滚到某个保存点，那么在此保存点之后的DML语句所做的修改将被丢弃，但是在此保存点之前的DML语句所做的修改仍然没有写入数据库，还可以进行提交或回滚。

3.3.4 提交COMMIT

如果在事务的最后执行了COMMIT命令，则对数据的修改将被写入数据库，以前的数据将永久丢失，无法恢复，其他用户都可以浏览修改后的结果，在数据上加的锁被释放，其他用户可以对数据执行新的修改，在事务中设置的所有保存点将被删除。

3.3.5 行锁FOR UPDATE

当一个用户修改表中的数据时，将对被修改的数据加锁，其他用户无法在此期间对该行数据进行修改，直到这个用户提交或回滚这个事务。

在数据查询时，尤其是在对数据库中的数据做统计与分析时，或在自己修改数据时，往往不希望此时有其它会话的用户修改数据。为了避免这种情况的发生，Oracle SQL提供了FOR UPDATE子句锁定当前查询所影响的行来解决此问题。此时，只有当前会话的用户才能对这些数据进行修改操作。

SELECT *|{[DISTINCT] 列名 | expression [alias], … }
FROM 表名
[WHERE 条件]
[FOR UPDATE [NOWAIT|WAIT n]]
[ORDER BY {col[s]|expr|numeric_pos}[ASC|DESC][NULLS FIRST|LAST]];

FOR UPDATE子句要放在WHERE子句的后边，也可以放在ORDER BY子句的后面，用于锁定SELECT语句所影响的行；若用户所查询的行已经被其它会话的用户锁定，则该会话将处于等待状态；若在FOR UPDATE子句中使用选项NOWAIT，若有行被其它会话锁定，则该会话不等待直接返回错误信息“ORA-00054：资源正忙，但指定以NOWAIT方式获取资源，或者超时失效”。WAIT n选项，则指定等待的时间为n秒，若当前会话所要查询的行已经被其它会话锁定，则等待n秒，若在n秒时间内，指定行上的锁被释放，则该语句将正确执行，否则Oracle服务器返回等待超时的错误信息。

当执行了SELECT…FOR UPDATE语句后，Oracle服务器自动对SELECT语句执行的检索结果加锁，结果集中的行仅限当前会话的用户修改，其它会话不能改变此结果集中的任何行，直到当前会话的用户执行了ROLLBACK或COMMIT语句。

3.4 数据查询DQL

投影(Projection)——选列、选择(Selection)——选行、连接(Join)——拼接。

• 选择（Selection）就是从数据库的表中通过一定的条件，把满足条件的行筛选出来；
• 投影（Projection）简单说就是选择表中指定的列；
• 连接（Join）就是把两个表按照给定的条件汇成一张表。

3.4.1 单表查询

SELECT * | [DISTINCT] 列名
FROM 表名
WHERE 条件 [AND ROWNUM < N]
[GROUP BY group_by_expression][HAVING group_condition(s)]
[ORDER BY 列名|列序号 [DESC | ASC]]

[OFFSET n ROW]
[FETCH NEXT [rowcount|percent PERCENT] ROW  ONLY|WITH TIES]

基本的SELECT语句支持列投影以及算术、字符和日期表达式的创建，也能够从结果集中删除重复值。(|)表示或（OR），星号(*)表示所有列。在开发环境中书写SQL语句时，不区分大小写。

语句终止符：SQL develop中通常用分号(;)作为SQL语句的终止符。SQL*Plus开发环境以及脚本文件中也可以使用斜杠(/) ，而不是使用分号作为终止符。

空值NULL：表示没有数据，NULL是一个未分配的、不可用的、未知的、不能使用的值，包含空值的行没有该列的数据。空值不同于数字0和空格，不占用存储空间。空值NULL可以参与运算，包含空值的算术表达式总会得到一个空值，空值不同于数字0, 0不能作为除数，但空值可以作为除数使用，运算的结果仍然得到空值。

列标题：在SQL*Plus和SQL Developer中，都是将检索的列以及参与运算的整个表达式作为列标题来显示。

列别名：为列标题重新起一个名字，尤其对表达式更有意义。列别名紧跟在列名或表达式的后面，以空格作为分隔符即可（也可以在列名和列别名之间使用可选的关键字AS）列别名默认都是以大写字母显示。如果列别名中包含空格或者特殊字符（如#、$）或者要区分大小写，这时需要在列别名上加双引号。

连接运算符 ||：将多个字符串联接起来组成一个。

SELECT employee_id, first_name||last_name “Name”
FROM employees;

限定：
选列限定 称为 投影(Projection)：SELECT 列名
选行限定 称为 选择(Selection)：WHERE 条件表达式

条件表达式的运算符：

运算符优先级：

排序：
ORDER BY子句是对结果进行排序，使用替换变量(&, &&)，可以在运行时通过界面进行交互。ORDER BY子句要放在SELECT语句的最后。ASC升序排序(默认)，DESC降序排序。列序号从1开始。

ORDER BY 列名|列序号 [DESC | ASC]

TOP-N查询：获得查询结果集的前N行，借助一个伪列ROWNUM来实现TOP-N查询的功能。ROWNUM在物理上（查询的目标表）并不存在，是查询过程中自动生成的，给每一行赋一个序号，从1开始，所以称为伪列。

WHERE ROWNUM < n

OFFSET子句：跳过n行开始显示，显示检索结果时从n+1行开始；若指定n<0，被当做0来处理；如果n=NULL，或者超过结果最大行数，就会返回0行；如果n是小数，小数部分会被截断，仅保留整数部分；OFFSET子句省略时，默认n为0，从第一行开始显示。

OFFSET n ROW

FETCH子句：指定返回行的个数 或 返回行的百分比。如果没有使用FETCH子句，那么所有的行都会被返回；

FETCH NEXT [rowcount|percent PERCENT] ROW  ONLY|WITH TIES

• NEXT仅为提供更清晰的语义度，更易于理解，不能省略。
• rowcount指定返回结果的行数；percent PERCENT指定返回结果行数的百分比。rowcount 或 percent PERCENT 选项，可以省略，若省略了此选项，即没有指定返回行数或百分比，则返回1行。
• ONLY或WITH TIES，是必选项，二者必选其一。ONLY将明确返回的行数rowcount或百分比 percent PERCENT；WITH TIES将拥有和最后一行相同排序键值的行都会被提取显示出来；
  WITH TIES选项一般与ORDER BY子句结合使用，没有指定ORDER BY子句的情况下，使用WITH TIES没有意义。

替换变量&，&&：
执行包含替换变量的SELECT语句时，首先检查是否用DEFINE命令定义了该变量，若已定义，则直接使用该变量的值。如果没有定义，需要输入一个值。
同一个替换变量的值会被重复引用多次：为了避免重复输入，Oracle服务器提供替换变量命令&&解决此问题，重复值只需要输入一次即可。

分组：
GROUP BY子句用于将表中的数据划分成多个分组，组函数对每个分组返回汇总信息；GROUP BY必须在WHERE之后，在ORDER BY之前。

GROUP BY 列名

GROUP BY子句中可以包含多个列，数据将在最后的列所确定的分组上进行汇总；如果分组列中具有NULL值，则NULL将作为一个分组返回（如果列中有多行NULL值，他们将分为一组）；

3.4.2 多表查询

多表查询的方式有3种：连接查询 、子查询、集合运算

3.4.2.1 连接查询

连接查询：多表之间存在关联的查询，包括等值连接/内连接(ON、USING、自然、自连接)、不等连接、外连接和交叉连接。

等值联接基于联接的两个表中存在两个或多个值相等的列(基于条件)
不等联接基于不等表达式联接两个表(基于条件)
外联接返回等值条件的同时，也需要返回那些不匹配的行(基于条件)
交叉联接没有任何联接条件，返回的是两个表的笛卡尔积(基于笛卡尔积)

SELECT	table1.column, table2.column, …
FROM	table1
[NATURAL JOIN table2] | --自然连接(连接所有有相同列名的两个表)
[JOIN table2 USING (column_name)] | --USING等值连接(用指定的列进行两个表的等值联接)
[JOIN table2 ON (table1.column_name = table2.column_name)]| --ON等值连接(基于ON等值条件的连接)
[LEFT|RIGHT|FULL [OUTER] JOIN table2 ON (table1.column_name = table2.column_name)]| --外联接(保留左/右/两表的行)
[CROSS JOIN table2]; --交叉连接(返回两个表的笛卡尔积)

①ON等值连接(!)：JOIN ..ON基于联接的两个表中值相等的两个或多个列，一般情况下，等值联接的两个表往往分别包含主键和外键。

SELECT	table1.column, table2.column, …
FROM table1
JOIN table2 ON (table1.column_name = table2.column_name) --等值连接(基于ON等值条件的连接)

②USING等值连接：JOIN ..USING，USING子句和NATURAL JOIN是互斥的，二者不能同时使用。NATURAL JOIN使用所有列名和数据类型都匹配的列，而USING子句仅指定需要使用的列，查询仅返回值在联接的表中所匹配的列上都相等的行。

SELECT table1.column, table2.column, …
FROM table1
JOIN table2 USING (join_column1, …); --等值连接(用指定的列进行两个表的等值联接)

③自然联接(!)：NATURAL JOIN通过使用两个表具有的相同名称的列等值查询，如果使用自然联接的两个表具有多个相同名称的列，则这多个相同名称的列值均要匹配（列名及数据类型）。

SELECT table1.column, table2.column, …
FROM table1
NATURAL JOIN table2; --自然连接(连接所有有相同列名的两个表)

④自联接：在进行联接查询时，有时候需要联接的另一表仍然是该表自身，通常使用JOIN … ON子句实现自联接。

SELECT	table1.column, table2.column, …
FROM table_self table1
JOIN table_self table2 second ON (table1.column_name = table2.column_name) --连接(基于ON自等值条件的连接)

⑤不等联接：基于不等表达式nonequi_expression匹配所连接表中的列值，若不等表达式结果为TRUE，则返回该行结果。使用JOIN ON子句实现不等联接。

SELECT table1.column, table2.column, …
FROM table1 JOIN table2 
ON (nonequi_expression);

⑥外联接：LEFT|RIGHT|FULL JOIN需要返回那些不匹配的行，不匹配的值设为NULL，分为左联接、右联接和全联接，x外连接 保存 x侧表的所有行。

SELECT	table1.column, table2.column, …
FROM	table1
LEFT|RIGHT|FULL JOIN table2 
ON (table1.column_name = table2.column_name)]

其中，关键字LEFT表示左联接，RIGHT表示右联接，FULL表示全联接，OUTER关键字可以缺省。

⑦交叉联接：CROSS JOIN没有任何联接条件，第一个表的每一行都要与第二个表的所有行进行联接，返回的是两个表的笛卡尔积。若表1有m行，表2有n行，则它们的交叉联接有m*n行。

SELECT	table1.column, table2.column, …
FROM	table1
CROSS JOIN table2;

3.4.2.2 子查询 (嵌套查询)

嵌套查询：子查询嵌入到主查询的子句中，用圆括号括起来，外部查询也称作主查询，内部查询就称作子查询，子查询先于主查询进行，主查询使用子查询的结果。子查询可以出现在SQL语句的WHERE子句、FROM子句、SELECT子句、HAVING子句中。

SELECT select_list1
FROM table1
WHERE expr operator
                    (SELECT select_list2
                     FROM table2);

其中，operator主要是比较运算符:

单行运算符 (=, >, >=, <, <=, !=) ,使用单行运算符的子查询称作单行子查询
多行运算符 (IN, ANY, ALL, EXISTS),使用多行运算符的子查询称作多行子查询

单行子查询：仅返回一行结果，在子查询中可以使用组函数。如果子查询返回空，则主查询的结果也为空。

在WHERE子句中使用子查询，对行进行限定：

SELECT select_list1
FROM table1
WHERE expr operator
                    (SELECT select_list2
                     FROM table2);

还可以在HAVING子句中使用子查询，从而对分组进行限定：

SELECT select_list1
FROM table1
WHERE condition(s)
GROUP BY cloums HAVING expr operator
                    (SELECT select_list2
                     FROM table2);

多行子查询：返回多行结果，使用多行运算符 IN，=ANY，>ANY, !=ANY，EXISTS，NOT运算符等。
IN：表示和集合列表中的某个值相同，表达式的值即为TRUE。
ANY：表示某一个，必须和单行运算符一块使用，且出现在单行运算符的后面，如=ANY，>ANY, !=ANY等，，那么<ANY表示比最大的小，>ANY表示比最小的大，=ANY和运算符IN意义相同。
ALL：表示所有的，与ANY类似，必须和单行运算符一块使用，且出现在单行运算符的后面，如>ALL, !=ALL等，那么<ALL表示比最小的小，>ALL表示比最大的大，!=ALL和运算符NOT IN意义相同。
EXISTS：用于检查子查询结果是否存在某些行，如果子查询返回至少一行，则结果为TRUE，否则为FALSE。主查询遍历子查询的结果行，从而得到最终的检索结果。
NOT EXISTS：是EXISTS运算符的否定形式，主查询遍历不在子查询结果集中行。
【建议】：能用EXISTS执行的查询，不要使用IN，并且EXISTS的性能要优于IN。

空值处理：可以在子查询中增加一个条件(WHERE xx IS NOT NULL)，把空值过滤掉。

3.4.2.3 集合运算

集合运算(复合查询)：用于合并两个或多个查询结果集，所用的集合运算符的优先级相同。所用集合运算符(除UNION ALL外)都会自动删除重复行，且对结果集自动升序排序。在显示检索结果时，按照第一个查询SELECT子句中的列名作为列标题。

3.4.3 Oracle函数

每个函数的定义由三部分组成，包括输入参数列表、返回值类型以及实现该功能的程序代码。函数可以嵌套调用，把一个函数的返回值作为另一个函数的输入参数来使用。

分类：单行函数和多行函数(组函数、聚合函数)
区别：单行函数每行都有返回值，多行函数多行统一返回一个值。

3.4.3.1 单行函数

• 作用于查询结果的每一行，每行得到一个返回值！
• 可以用在SELECT语句的SELECT子句、WHERE子句以及ORDER BY子句中；
• 单行函数可以嵌套任意层，嵌套函数的计算顺序是从最内层到最外层。

单行函数的类型：字符函数、数值函数、日期函数、转换函数、一般函数。

DUAL表：Oracle提供的一个公共表DUAL，该表的所有者为SYS用户，其他所有用户都可以访问，DUAL表用来查看函数和计算的运行结果，该表只有一列，列名为DUMMY，结果只有一行，值为X，在一次仅返回一个值（如常量值、伪列的值、或者表达非用户数据表中的值）的时候，使用DUAL表查看结果非常方便。

SELECT 单个返回值的操作 FROM DUAL;

① 字符函数：对字符串进行操作。(字符串拼接可以还可以用 str1 || str2 )

SELECT 字符函数() FROM DUAL;

②数值函数：对数值进行操作。

SELECT 数值函数() FROM DUAL;

③日期函数：日期获取和操作函数。

SELECT 日期函数() FROM DUAL;

日期格式为DD-MON-RR(DD表示2个数字的日；MON表示月份英文单词的前3个字母缩写 或 中文字符集下 x月，如3月；RR表示2个数字的年份)。

日期型数据还可以进行算术运算： 新日期=原日期+/-天数； 天数=日期1-日期2。

④转换函数：提供两种数据类型之间的转换。

(1)隐式数据类型转换：也称为自动数据类型转换，在表达式中，Oracle可以自动执行以下转换：

字符型(VARCHAR2类型和CHAR类型)->数值型(NUMBER类型)
字符型(VARCHAR2类型和CHAR类型)->日期型(DATE类型)

(2)显式数据类型转换：也称为强制数据类型转换，是由用户通过类型转换函数，实现数据从一种类型到另一种类型的转换。

TO_CHAR函数，可以将数值型(NUMBER)数据和日期型(DATE)数据 转换为 字符型数据；
日期->字符：TO_CHAR(date[,'format_model'])



数值->字符：TO_CHAR(number[,'format_model'])

当format_model指定结果小数位数小于number本身所包含的小数位数时会进行四舍五入；当format_model指定的整数部分的宽度小于number本身所包含的整数部分数字个数时，Oracle服务器会以#填充整个转换结果。

TO_NUMBER函数，可以将字符型数据 转换为 数值型(NUMBER)数据；
字符->数值：TO_NUMBER(char[,'format_model'])
其中char是要转换的字符型数据，其必须是表示数值型值的字符串，format_model是指定转换的格式，转换格式必须用单引号括起来。format_model所指定的格式必须匹配char字符串所表示的数值格式，format_model中可以使用的格式元素同常用数值格式元素中相同

TO_DATE函数用来将字符型(VARCHAR2类型和CHAR类型)数据转换为日期型(DATE)数据。
字符->日期：TO_DATE(char[,'format_model'])
其中，char是要转换的字符型数据，其必须是表示日期值的字符串，format_model是指定转换的格式，转换格式必须用单引号括起来。format_model所指定的格式必须匹配char字符串所表示的日期格式，format_model中可以使用的格式元素同TO_CHAR函数说明。format_model可以使用前缀修饰符fx，要求char字符串表示的日期值必须精确匹配format_model指定的格式，甚至连标定符号等分隔符也要相符，并且不能有多余的空格，字符串中的数值型数据的位数也要与format_model中的位数一致，否则Oracle的服务器就会报错。没有使用前缀修饰符fx的时候，Oracle服务器会忽略多余的空格和前置0

⑤一般函数：用于对空值的处理。

• NVL的expr1和expr2两个参数需同类型
• NVL2的expr2和expr3两个参数需同类型，它们和expr1可以不同类型.

⑥条件函数：IF-THEN-ELSE的条件逻辑结构，根据满足的条件来选择返回的行。
(1)CASE表达式允许数据库管理员(DBA)和数据库开发人员在SQL语句中无需调用Oracle的存储过程就可以实现IF-THEN-ELSE逻辑。

CASE expr WHEN comparison_expr1 THEN return_expr1
         [WHEN comparison_expr2 THEN return_expr2
          ……
          WHEN comparison_exprn THEN return_exprn
          ELSE else_expr]
END

其中，expr是搜索的条件(expr可缺省：仅对comparison_exprn 进行判断)，WHEN … THEN …是用来等值比较并判断是否返回的执行分支，多个分支中只有一个分支被执行。

(2)DECODE函数在Oracle SQL中也支持IF-THEN-ELSE逻辑，是CASE表达式的简化形式。DECODE函数不是ANSI SQL的标准函数，是Oracle的专用函数

DECODE(col|expression, search1, result1
      			   [, search2, result2,...,]
      			   [, default])

DECODE函数形式比CASE表达式简单的多，省去了WHEN、THEN、ELSE和END关键字，条件搜索的匹配策略同CASE表达式一致。如：

SELECT employee_id, last_name, job_id, salary, 
       DECODE(job_id, 'IT_PROG', 1.1*salary,
                      'ST_CLERK', 1.15*salary,
                      'SA_REP', 1.2*salary,
                      salary) "PaidSal"
FROM employees;

3.4.3.2 聚合函数(组函数)

SELECT column, group_function(column)
FROM table
[WHERE condition(s)]
[GROUP BY group_by_expression][HAVING group_condition(s)]
[ORDER BY column];

• GROUP BY子句将整张表分成了多个组，组函数作用于分组上，每一组返回一个结果 ，GROUP子句要放在WHERE子句的后面！GROUP BY子句中可以包含多个列，数据将在最后的列所确定的分组上进行汇总；
• 使用HAVING子句限定分组；
• 如果分组列中具有NULL值，则NULL将作为一个分组返回（如果列中有多行NULL值，他们将分为一组）；
• 组函数仅接受一个参数；
• 组函数仅可以出现在查询语句的SELECT和HAVING子句中。
• Oracle的所有组函数都是忽略空值的。
• 组函数可以嵌套，但嵌套调用时，GROUP BY子句是必须的，不可缺省。

3.5 其他对象

除表之外，Oracle数据库模式还有很多类型的对象，其中最常用的主要有视图、序列、索引以及同义词，本部分主要介绍这些对象的创建、修改及删除。

3.5.1 视图

视图是基于表或其它视图的逻辑表，是SELECT语句结果表封装的虚表。通过创建表的视图来显示表的逻辑子集或数据的组合，所以，视图本身不是物理存在的，它并不包含自己的数据，不占用实际的存储空间，只在数据字典中保存其定义。
视图实际上是在一个或多个表上定义的查询，这些表称为基表(base table)。相对于视图所引用的基表来说，视图的作用相对于筛选。

3.5.1.1 创建视图

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW viewanme  [(列名, ...)]
AS 子查询
[WITH CHECK OPTION [CONSTRAINT 约束名]]
[WITH READ ONLY [CONSTRAINT 约束名]];

OR REPLACE选项说明如果视图已经存在，则替换；
FORCE选项说明即使基表不存在也要创建该视图；NOFORCE说明基表不存在就不创建视图，为默认选项；
WITH CHECK OPTION选项说明只有子查询检索的行才能被插入、修改或删除，对视图进行更新操作时必须和子查询的条件一致，否则操作就会终止。
WITH READ ONLY选项说明只能对基表进行只读访问，不能通过视图对基表进行DML语句操作；

子查询可以包含复杂的查询(SELECT)语句，如使用联接、使用分组数据，甚至还可以使用子查询；列名中如果使用了表达式列，则必须为该列指定别名。创建视图的子查询可以使用联接查询、组函数以及使用GROUP BY子句对数据进行分组汇总，这样创建的视图称为复杂视图。

3.5.1.2 修改视图

实质上就是修改视图的定义，即使用CREATE OR REPLACE VIEW 重新定义，而对视图的基表数据不会产生任何影响，但更改视图后，依赖于该视图的所有视图和PL/SQL的程序都将失效。

通过视图修改基表：
简单视图都可以进行更新(包括INSERT、UPDATE和DELETE)操作。

但有如下情况之一，不能通过视图删除基表中的数据：

• 使用了组函数(多行函数，如SUM、MAX、AVG等)
• 使用了GROUP BY子句
• 使用了DISTINCT关键字
• 使用了伪列ROWNUM
• 修改的列由表达式定义

有如下情况之一，不能通过视图往基表中添加数据：

• 使用了组函数(多行函数，如SUM、MAX、AVG等)
• 使用了GROUP BY子句
• 使用了DISTINCT关键字
• 使用了伪列ROWNUM
• 修改的列由表达式定义
• 基表中主键约束和非空约束的列没有出现在视图中

3.5.1.3 删除视图

DROP VIEW [schema.]view [CASCADE CONSTRAINTS];

其中，CASCADE CONSTRAINTS选项用于删除视图时删除约束。

3.5.2 序列

序列(SEQUENCE)是数据库用户创建的的一类多用户共享的对象，能自动产生一组等间隔的连续整数，往往用来生成表的主键值，并保证主键的列值不会重复。

3.5.2.1 创建序列

创建序列需要指定序列名、升序或序列、序号间隔等信息，使用CREATE SEQUENCE语句来创建序列。

CREATE SEQUENCE 序列名
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE n|NOMAXVALUE}]
[{MINVALUE n|NOMINVALUE}]
[{CYCLE|NOCYCLE}]
[{CACHE n|NOCACHE}]

INCREMENT BY n指定相邻两个序列号之间的间隔(递增间隔)，缺省值为1；
START WITH n指定生成的第一个序列号（起始值），缺省值为1；
MAXVALUE n指定序列能生成的最大值(最大值)；
NOMAXVALUE指定升序序列的最大值为1027，降序序列为-1，为缺省选项；
MINVALUE n指定序列生成的最小值；(最小值)
NOMINVALUE指定升序序列的最小值为1，降序序列为-1026，为缺省选项；
CYCLE指定序列号循环使用，当序列达到最大值或最小值仍继续生成序列号，当升序序列达到最大值时，下一个生成的值是最小值，当降序序列达到最小值时，下一个生成的值是最大值；如果生成的序列号用于表的主键，此时一定不要使用CYCLE选项。
NOCYCLE指定序列不循环使用，当序列号在达到最大值或最小值后就不再生成序列号了，为缺省选项；
CACHE n指定预分配的保留在内存中的整数个数，缺省时默认缓存20个，使用缓存可以提高获取序列号的效率；
NOCACHE指定不缓存任何整数，这样可以阻止数据库为序列预分配值，从而避免序列产生不连续的情况。

3.5.2.2 使用序列

序列中的可用资源是其中包含的序列号，序列提供两个伪列NEXTVAL和CURRVAL来访问序列中的序列号。NEXTVAL用于返回序列生成的下一个值，CURRVAL用于返回序列的当前值。
需要注意的是，在第一次引用CURRVAL之前，必须引用过一次序列的NEXTVAL用于初始化序列的值，否则会出现错误提示信息。

3.5.2.3 修改序列

使用ALTER SEQUENCE语句修改序列，但不能修改序列的初值，序列的最小值不能大于当前值，序列的最大值不能小于当前值。

3.5.2.4 删除序列

DROP SEQUENCE sequence_name;

3.5.3 同义词

同义词 相当于是表、视图、索引等模式对象的一个别名。与视图类似，同义词并不占用实际的存储空间，只在数据字典中保存其定义。

3.5.3.1 创建同义词

CREATE [PUBLIC] SYNONYM 别名
FOR object;

其中，PUBLIC创建一个为PUBLIC用户组所拥有的、所有用户都能访问的公有同义词；若缺省PUBLIC选项，则创建一个专有同义词(也称为私有同义词)，仅创建它的用户所拥有。

3.5.3.2 删除同义词

同义词创建后，不能对同义词进行修改或改变它的定义，只能删除。

DROP [PUBLIC] SYNONYM synonym;

其中，PUBLIC选项用于删除公有同义词，只有数据库管理员(DBA)才能删除，非管理员用户只能删除自己用户模式下的同义词。

4 Oracle PLSQL基础(重点)

PL/SQL(SQL 过程语言扩展)具有通常的程序结构(如if-then-else和循环)以及用于用户界面设计的工具，可以像编程语言一样定义变量、常量，编写结构化程序语句结构(如条件判断语句、循环语句)。可以嵌入对SQL的调用，也可以使用SQL将行写回数据库。在PL/SQL代码中还可以对语句操作进行异常处理以及事务控制，以保证数据的准确性。

提交PL/SQL语句时，对PL/SQL进行解析，分别标识出过程语句和SQL语句，将过程语句传递到过程语句执行器执行，将SQL语句传递到SQL语句执行器执行。

PL/SQL与SQL具有相同的数据类型和表达式，并在此基础上有一定的扩展。

4.1 PL/SQL概述

4.1.1 PL/SQL块结构与类型

PL/SQL是结构化程序设计语言，块是PL/SQL程序中最基本的单元，每个PL/SQL块由三部分组成：声明部分、执行部分和异常处理部分。执行部分可以嵌套任意数量的PL/SQL块；

DECLARE(可选)
	-声明部分：声明变量、常量、类型、游标、用户定义的异常等
BEGIN(必需)
	-执行部分：SQL语句和PL/SQL过程语句构成的程序主要部分
EXCEPTION(可选)
	-异常处理部分：当程序出现异常时进行捕获并处理
END; (必需)

PL/SQL程序有三种类型的块组成，分别为匿名块、过程块、函数块。

• 匿名块是未命名的块，在运行时被执行，匿名块不存储在数据库中，如果要再次执行相同的块，则必须重写该块，并在执行时重新进行编译。由于块是匿名的，每次执行后该块就不再存在，开发者不能调用以前写过的匿名块。
• 过程和函数都称作子程序，它们是匿名块的有益补充，是存储在数据库中被命名的PL/SQL块。由于子程序已经命名，且存储在数据库中，开发者可以随时调用它们，可以将子程序声明为过程或函数，通常使用函数执行一些操作，而使用函数进行计算并返回一个值。
• 函数和过程类似，不同的是函数必须有返回值，而过程没有。

4.1.2 输出显示

使用DBMS_OUTPUT包的过程PUT_LINE来显示一行需要输出的信息。

要生成输出，在SQL Developer和SQL*Plus中都可以显示地使用命令SET SERVEROUTPUT ON来设置，不显示输出使用命令SET SERVEROUTPUT OFF进行设置。

4.2 简单PL/SQL匿名块

4.2.1 声明变量

变量主要用来存储数据，并对存储的值进行操作。变量还可以存储任意的PL/SQL对象，如类型、游标和子程序等。声明后的变量可以在执行部分使用，并可以赋予新值；也可以作为参数传递给PL/SQL的子程序，同样也可以用来存储函数返回的结果。

需在PL/SQL块的declare部分进行声明，并为其指定数据类型，声明时还可以为其指定初始值并施加非空约束，变量在使用前必须进行声明。

变量名 类型[(size)] [NOT NULL] [:= | DEFAULT expr];

可以与SQL中的数据类型相同，可以是标量类型、复合类型、引用类型和大对象类型，也可以是PL/SQL扩展的游标或异常类型；可以使用赋值运算符(:=)，也可以使用DEFAULT关键字。
如

DECLARE
    v_name VARCHAR2(20);
BEGIN
    DBMS_OUTPUT.PUT_LINE('My name is: '||v_name);
    v_name := 'Zhang San';
    DBMS_OUTPUT.PUT_LINE('My name is: '||v_name);
END;

%TYPE声明变量
%TYPE就是根据已经声明的变量或数据库中的列来声明一个新变量，使用%TYPE声明变量时，应当在%TYPE前以表名和列名作为前缀修饰符。

• 根据 表中列的类型 来声明变量；

变量名 表名.列名%TYPE;

• 根据已经存在的变量名来声明变量。

变量名 已存在的变量名%TYPE;

如

declare
emp_name   employees.last_name%TYPE;
min_income   income%TYPE;
begin
...
end;

4.2.2 使用函数

Oracle所有函数都可以用在PL/SQL的SQL语句中，但DECODE函数和组函数不能直接用在PL/SQL的 begin 过程语句中，但可以用在SELECT语句中。
如

declare
stringlen INTEGER(5);
content VARCHAR2(80) := 'This is a very long character string';
stringlen := LENGTH(content);
d NUMBER(10, 2) :=  ROUND(MONTHS_BETWEEN(SYSDATE, '15-3月-14'), 2);
begin
...
end;

4.2.3 嵌套PL/SQL块

可以在begin执行部分的任何位置嵌套PL/SQL块，使整个嵌套的PL/SQL看起来像一条语句。
不同块声明的变量，其作用域也不同，PL/SQL块中声明的变量，其作用域就是从变量的声明开始到其所在语句块的结束

DECLARE
    var_outer VARCHAR2(20) := 'OUTER VARIABLE';
BEGIN
  DECLARE
     var_inner VARCHAR2(20) := 'INNER VARIABLE';
  BEGIN
     DBMS_OUTPUT.PUT_LINE(var_inner);
     DBMS_OUTPUT.PUT_LINE(var_outer);
  END;
     DBMS_OUTPUT.PUT_LINE(var_outerr);
END;

如果在内层需要使用外层声明的与内层重名的变量，可以通过对外层PL/SQL块命名的方式来实现：

BEGIN <<qualified_identifier>>
	PL/SQL块
END qualifier_identifier;

如

BEGIN <<outer>>
    ……
    BEGIN;
        DBMS_OUTPUT.PUT_LINE('Date of birth:' || outer.var_birth);
    END;
    ……
END outer;

4.2.4 使用SQL语句

PL/SQL程序中，通过使用SELECT语句检索数据，使用DML语句更新数据库中的数据，使用TCL语句进行事务控制。

• 使用SELECT语句检索数据：

检索的每一个值都必须使用INTO子句保存到指定的PL/SQL变量中，但使用INTO子句的情况下要求检索结果只能有一行！！！，condition由列名、表达式、常量、比较运算符构成，也包含PL/SQL中的变量和常量。

SELECT 查询的列
INTO {PL/SQL变量名 | record }
FROM table
[WHERE condition];

在PL/SQL块的SQL语句中可以使用组函数

DECLARE
    var_sumsal employees.salary%TYPE;
    var_deptno NUMBER NOT NULL := 60;
BEGIN
    SELECT SUM(salary) INTO var_sumsal FROM employees
    WHERE department_id = var_deptno;
    DBMS_OUTPUT.PUT_LINE('部门总工资为：'||var_sumsal);
END;

• 使用INSERT语句往表中添加行数据：

BEGIN
    INSERT INTO departments
    VALUES(dept_id_seq.NEXTVAL, ‘AI Lab’, 110, 1700);
END;

• 使用UPDATE语句修改表中的数据：

BEGIN
    UPDATE departments
    SET manager_id = 117
    WHERE department_id = 380;
END;

• 使用DELETE删除表中的行：

BEGIN
    DELETE FROM departments
    WHERE department_id = 380;
END;

PL/SQL中不能直接使用DDL语句，这些语句成为动态SQL语句，PL/SQL通过使用EXECUTE IMMEDIATE语句执行动态SQL语句

DECLARE
  var_sql VARCHAR(20) := 'DROP TABLE dept2';
BEGIN
  EXECUTE IMMEDIATE var_sql;
END;

PL/SQL中也不能直接执行DCL语句，如GRANT、REVOKE等，也要通过使用动态SQL来执行它们。

4.3 PL/SQL控制结构

逻辑流程包括IF语句、CASE表达式、LOOP循环结构以及CONTINUE语句共四种。

4.3.1 IF语句

IF condition THEN
  statements;
ELSIF condition THEN
  statements;
ELSE
  statements;
END IF;

condition是一个布尔类型变量或能得到TRUE、FALSE或NULL的条件表达式；THEN为条件表达式为TRUE时，引出后续操作的关键字；statements由一条或多条SQL语句或PL/SQL语句组成，可以包含其它的逻辑结构，只有在THEN所关联的IF子句的condition为TRUE时才执行；ELSIF在第一个IF子句中的condition为FALSE或NULL时。

4.3.2 CASE表达式

等值比较：是比较WHEN子句中表达式和选择器的值是否相等。

CASE selector
WHEN expression1 THEN result1
WHEN expression2 THEN result2
…
WHEN expressionN THEN resultN
ELSE result+1
END;

选择器selector只会计算一次，然后顺序执行WHEN子句，比较WHEN子句中expression与选择器selector的值，如果二者相等，那么该与该WHEN子句相关的语句会执行，并且随后所有的WHEN子句均不会计算。如果任何WHEN子句的表达式都不匹配选择器的值，则执行ELSE子句中的语句。
不等比较：只判断WHEN的表达式，CASE表达式顺序执行WHEN子句，遇到第一个搜索条件为TRUE的WHEN子句止。

4.3.3 LOOP循环结构

PL/SQL也支持循环嵌套。

基本循环：在没有总体条件的情况下执行重复的操作LOOP；LOOP是引出循环的关键字；END LOOP是循环结束的标识；LOOP和END LOOP之间的部分为循环体；EXIT是终止循环的关键字，WHEN子句是可选的。lable_name是循环标签，相当于为该循环命名，在循环嵌套时常能用到。

[<<label_name>>]
LOOP
    statement1;
    …
    EXIT [WHEN condition];
END LOOP[ label_name];

FOR循环：根据迭代次数控制循环的执行；用来设置PL/SQL执行循环的次数。计数器counter，是一个隐式声明的整型变量，每执行一次循环体，其值自动增加1或减少1(使用REVERSE关键字)，直到达到上界(upper_bound)或下界(lower_bound)为止；REVERSE使计数器counter每次循环从上界到下界递减，使用REVERSE关键字的情况下，仍然是下界在前，上界在后。

[<<label_name>>]
FOR counter IN [REVERSE] lower_bound .. upper_bound LOOP
    statement1;
    statement2;
    …
END LOOP[ label_name];

DECLARE
    var_dept_name departments.department_name%TYPE;
BEGIN
    FOR var_counter IN 1..3 LOOP
        var_dept_name := 'RearchCenter' || var_count;
        INSERT INTO departments(department_id, department_name)
        VALUES (dept_id_seq.NEXTVAL, var_dept_name);        
  END LOOP;
END;

WHILE循环：根据满足的条件控制循环的执行；当条件不为TRUE（包括FALSE和NULL）时，终止循环。

[<<label_name>>]
WHILE condition LOOP
    statement1;
    statement2;
    …
END LOOP[ label_name];

FOR循环形式上比基本循环和WHILE循环更简洁，如果已知循环执行的次数，则使用FOR循环，如果循环至少执行一次往往使用基本循环，如果已知循环执行所满足的提交，则要使用WHILE循环。

EXIT语句用来终止循环的执行，基本循环中因为没有循环执行的总体条件，必须使用EXIT语句。

4.3.4 CONTINUE语句

EXIT语句值终止循环的执行，和它相当于的是CONTINUE语句，CONTINUE语句是跳过循环体剩余的语句而重新开始下一次循环体的执行，此时的意义同C/C++中的continue语句相同。

LOOP
	…
	CONTINUE WHEN condition;
	…
END LOOP;

在condition值为TRUE时，跳过循环体中其后的所有语句，重新开始下一次循环。

4.4 游标

在PL/SQL块中可以执行SQL语句：

• 从数据库中检索一行数据，并使用INTO 变量将检索到的数据存储到指定的变量。
• 从数据库中检索多行数据，并使用游标指向上下文区域的私有内存区，来cuSELECT语句的结果集。

PL/SQL支持两种类型的游标，隐式游标和显式游标。

4.4.1 隐式游标

是由Oracle服务器创建和管理，用户不能访问，当PL/SQL程序块执行一条SQL SELECT语句或DML语句时就会自动创建一个隐式游标。
使用SQL游标属性，可以检测SQL语句运行的结果：

游标属性尤其是在UPDATE和DELETE语句没有影响任何行时非常有用，因为在这些情况下PL/SQL不会返回异常。

4.4.2 显式游标

显式游标指向 SQL语句多行结果集的存储区。

声明游标放在PL/SQL块的declare部分。

然后在PL/SQL块的begin部分通过指定的语句对检索结果集进行处理。

显式游标具有如下功能：

1.从第一行开始对查询结果逐行处理
2.跟踪当前正在处理的行
3.在PL/SQL块中手动控制显式游标

声明游标：

CURSOR cursor_name IS select语句;

此处仅对游标进行声明，并没有检索游标中的任何行，但在FETCH语句中要使用到INTO子句。
SELECT语句，也可以包含联接、子查询等，还可以在该SELECT语句中使用ORDER BY子句，还可以再SELECT子句中使用变量。

打开游标：
对游标进行操作，需要在PL/SQL的执行部分先打开游标：

OPEN cursor_name;

OPEN语句会解析该游标所关联的SELECT语句，为其动态分配内存，打开游标后，将指针定位到活动结果集的第一行。

提取数据：
打开游标语句执行完成后，就可以使用FETCH语句提取游标指针所指向行(当前行)的数据：

FETCH cursor_name INTO {变量名… | cursor_name%ROWTYPE};

其中%ROWTYPE适用于声明游标所关联的查询的一行；变量用来存储游标当前行数据的变量。

关闭游标：
游标使用完成后，需要将其关闭，以释放活动结果集占用的内存：

CLOSE cursor_name;

游标属性(%XXX)：

只有游标打开之后，才能使用FETCH语句提取行。在提取行之前，可以使用游标属性%ISOPEN判断游标是否已打开，若未打开，则使用OPEN语句打开该游标：

IF NOT cursor_name%ISOPEN THEN
    OPEN cursor_name;
END IF; 	
LOOP
    FETCH cursor_name …

%ROWCOUNT属性获得结果集的总行数，%NOTFOUND属性往往用来判断检索的结果集是否处理完毕。有很多时候，仅仅处理检索结果集的前N行，此时可以将此两个属性结合使用。

循环取整个结果集数据：

LOOP 
	FETCH 游标名称 into 变量
	EXIT WHEN 游标名词&NOTFOUND
END LOOP;

如

DECLARE
    CURSOR emp_cursor IS 
        SELECT employee_id, last_name FROM employees
        WHERE department_id = 60;
    var_empid employees.employee_id%TYPE;
    var_lname employees.last_name%TYPE;
BEGIN
    OPEN emp_cursor;
    LOOP
        FETCH emp_cursor INTO var_empid, var_lname;
        EXIT WHEN emp_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(var_empid || ' ' || var_lname);
    END LOOP;
    CLOSE emp_cursor;
END;

在使用游标处理数据时，可以在FETCH语句中使用记录(结构体)，取一行数据。

一种方法就是根据游标选定的列来定义记录；

另一种更简单方便的方法就是使用%ROWTYPE属性，取，使用游标名做前缀来定义记录。

DECLARE
    CURSOR emp_cursor IS 
        SELECT employee_id, last_name FROM employees
        WHERE department_id = 60;
    emp_record emp_cursor%ROWTYPE; --使用%ROWTYPE取游标emp_cursor指向表的类型，定义记录变量emp_record
BEGIN
    OPEN emp_cursor;
    LOOP
        FETCH emp_cursor INTO emp_record;
        EXIT WHEN emp_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(emp_record.employee_id || ' ' 
|| emp_record.last_name);
    END LOOP;
    CLOSE emp_cursor;

游标FOR循环：
使用FOR循环可以简化对显式游标的处理，FOR循环可以隐式地声明一个游标记录，并自动打开、提取数据、退出并关闭游标：

FOR record_name IN cursor_name LOOP
    statement1;
    statement2;
    ……
END LOOP;

其中，record_name由FOR循环隐式声明，无需在声明部分声明；cursor_name是在声明部分声明的游标名称。循环执行时，自动打开游标，循环每执行一次，自动地从结果集中提取一行，在处理完最后一行时，退出循环，并自动关闭游标。

游标FOR循环可以再次简化，使用子查询的游标FOR循环可以隐式声明一个游标，而无需在PL/SQL的声明部分对游标进行声明：

FOR record_name IN (subquery) LOOP
    ……

其中，record_name是由FOR循环隐式声明的游标记录，subquery是游标关联的查询，这里子查询必须用圆括号括起来。

带参数的游标：
可以将参数传递给游标，也就是说可以在一个PL/SQL块中多次打开和关闭一个显式游标，每次返回一个不同的活动结果集。对于每一次执行，前面的游标都是关闭的，并用一组新参数重新打开它。

游标声明的每一个形参都必须在OPEN语句中具有相应的实参，在声明带参数的游标时，要为形参指定数据类型，但不知道宽度和精度，并在声明游标所关联的SELECT语句中引用参数的名字。

CURSOR cursor_name [(parameter_name datatype, …)] IS
    select_statement;

其中，cursor_name所声明游标的名字，是PL/SQL的标识符；parameter_name是形参的名字；datatype是参数parameter_name的数据类型，标量数据类型；select_statement是游标关联的查询语句，不能使用INTO子句。
在PL/SQL块的执行部分，打开游标时，向游标传递一组参数，然后执行游标所关联的查询。打开带参数游标的一般形式为：

OPEN cursor_name(parameter_value, …);

关闭带参数游标时，不需要传递参数，一般用法如下：

CLOSE cursor_name;

通过游标更新数据：
同时连接到同一个数据库的往往不止一个会话，当用游标打开特定的表时，表中的行可能会被更新，只有在重新打开游标后才能看到更新后的数据。

数据库开发人员也可以在PL/SQL中通过游标修改或删除数据库表中的数据，修改或删除表中数据前需要先在相关的行上加锁。

锁定游标所关联的表中数据，需要在声明游标时使用FOR UPDATE子句：

CURSOR cursor_name IS
    SELECT … FROM … [WHERE …]
    FOR UPDATE [OF column_name(s)][NOWAIT | WAIT n];

此处的FOR UPDATE子句及其各选项的语法及用法与上一章SQL基础中相同。修改或删除游标指向行的数据，必须使用WHERE CURRENT OF子句

通过游标修改数据的用法如下：

UPDATE table
SET column = …
WHERE CURRENT OF cursor_name;

此处cursor_name是已经声明的游标名称，且在声明该游标时使用了FOR UPDATE子句。

删除游标所指向的行，使用如下的语句：

DELETE [FROM] table
WHERE CURRENT OF cursor_name;

上述通过游标对表中数据进行修改和删除操作，必须是在执行FETCH语句提取了游标当前行的数据之后才能执行，否则会返回错误信息“ORA-01410: 无效的 ROWID”。

4.4.3 复合数据

标量类型的变量只能存储一个值，而复合类型的变量可以存储多个标量类型或复合类型的值。PL/SQL中主要有两种类型的复合数据类型：PL/SQL记录和PL/SQL集合。

• PL/SQL记录(结构体) 是由相关但并不同类型的数据组成的逻辑单元，PL/SQL记录可以有不同的类型。如，可以定义一个记录来保存 员工信息，包括存储数值型的员工编号、字符型的员工姓名、日期型的员工入职日期等。将员工信息作为一个整体的逻辑单元。在PL/SQL程序块的声明部分定义记录类型，并由用户定义该类型的变量；记录可以嵌套。

TYPE type_name IS RECORD
    (field_declaration[, field_declaration] …);

字段声明field_declaration如下：

field_name {field_type | variable%TYPE | table.column%TYPE | table%ROWTYPE}
      [[NOT NULL] {:= | DEFAULT } expr]

其中，type_name是为定义的记录类型所起的名字，可以使用该名字声明记录类型的变量；field_name是记录中字段的名字；
field_type是字段的数据类型，可以是PL/SQL支持的任意类型，也可以由类型属性%TYPE、%ROWTYPE来定义；
expr是用来给指定字段初始化的表达式；NOT NULL约束指定该字段的值不能为空值NULL。

定义了记录类型后，就可以定义该记录类型的变量：

变量名 type_name;

引用记录中的字段，需要使用点(.)运算符 引用记录字段：

变量名.fied_name

DECLARE
    TYPE emp_record IS RECORD
        (name employees.last_name%TYPE,
        job employees.job_id%TYPE,
        sal employees.salary%TYPE);
    emp emp_record;
BEGIN
    SELECT last_name, job_id, salary INTO emp
    FROM employees WHERE employee_id = 174;
    DBMS_OUTPUT.PUT_LINE(emp.name || ' ' || emp.job || ' ' || emp.sal);
END;

• PL/SQL集合(数组) 是相同类型数据的集合，PL/SQL提供三种类型的集合，包括索引表INDEX BY(关联数组)、嵌套表和可变数组。如可以定义一个集合存储 所有员工的姓名，任意两个名字之间并没有什么逻辑关系。

4.5 异常处理

4.6 过程与函数

4.6.1 过程PROCEDURE

过程PROCEDURE 是一个可以接收参数的命名PL/SQL块，其声明包含四部分，分别为过程头、声明部分、执行部分和异常处理部分。
过程是 编译后存储在数据库中的 一个模式对象，可以重复被调用，从而提高程序的可重用性和可维护性。存储在数据库中的过程称为存储过程。可以使用MODE返回多个值！！
创建过程：

CREATE [OR REPLACE] PROCEDURE 存储过程名([parameter1 [mode] datatype1, parameter2 [mode] datatype2, …])
AS
	内部变量 类型；
BEGIN
    --actions;
END;

OR REPLACE选项用于覆盖同名的过程，若不存在则创建；parameter是形参的名字，每一个参数都要为其指定参数模式 和 数据类型；
mode是参数模式，共三种，分别为IN、OUT和IN OUT，其中IN是默认模式；IN OUT模式的参数，既能作为输入参数，也可以作为输出参数。
datatype是参数的数据类型，不能为参数指定宽度与精度，可以使用类型属性%TYPE来声明；
IS或AS是创建过程体的关键字，不可缺省；
variable_declaration是声明执行部分中需要用到的局部变量；
actions是过程所实现功能的语句序列，可以包含各种控制结构；

参数用于将数据值从调用环境传递到过程(或子程序)，参数在子程序头处声明，位于子程序名和IS(AS)关键字之间。

过程调用：
可以在PL/SQL程序块中调用该过程，如：

BEGIN
    procedure_name;
END;

也可以使用EXECUTE语句执行过程，如：

EXECUTE procedure_name;

4.6.2 函数 FUNCTION

函数FUNCTION 是一个可以接收参数、能被调用，且必须有返回值的PL/SQL块。通常，使用函数进行特定的计算，并返回计算的结果。函数必须返回一个值到调用环境，而过程不同，过程可以将0个或多个值返回到调用环境。函数的创建形式和过程结构类似，在函数必须使用RETURN子句，用于向调用环境返回计算结果。只能返回一个值！！！

创建函数：

CREATE [OR REPLACE] FUNCTION 函数名
(参数1 [mode] 类型1, 参数2 [mode] 类型2, …)
RETURN 返回类型
AS
    [variable_declaration, …]
BEGIN
    --actions;
    RETURN expression;
END;

RETURN datatype子句标注函数的返回值类型，出现在参数声明和关键字IS(AS)之间；
RETURN expression;语句是执行部分不许包含的语句，至少出现一条，用于向调用环境返回计算结果；其它选项的意义与过程相同。

5 Oracle 安全性

6 Oracle 数据库管理