I
目录
第二章 基本的SQL SELECT语句............................................................................................. 1 第三章 限制行和对数据排序 ...................................................................................................... 2 第四章 联接多个表....................................................................................................................... 3 迪卡尔连接 ......................................................................................................................... 3 相等连接 ............................................................................................................................. 3 不等连接 ............................................................................................................................. 3 自我连接 ............................................................................................................................. 4 外部连接 ............................................................................................................................. 4 集合运算符 ......................................................................................................................... 4 第五章 单行函数 ........................................................................................................................... 5 大小写转换函数 ................................................................................................................ 5 字符串处理函数 ................................................................................................................ 5 数字函数 ............................................................................................................................. 6 Date函数 ............................................................................................................................ 6 其它函数 ............................................................................................................................. 7 NESTRING函数................................................................................................................ 9 DUAL表 ............................................................................................................................. 9 第六章 组函数 ............................................................................................................................. 10 第七章 子查询 ............................................................................................................................. 11 单行子查询 ....................................................................................................................... 11 多行子查询 ....................................................................................................................... 11 多列子查询 ....................................................................................................................... 12 NULL值 ........................................................................................................................... 13 嵌套的子查询................................................................................................................... 13 第八章 表的创建和维护 ............................................................................................................ 15 表设计 ............................................................................................................................... 15 创建表 ............................................................................................................................... 16 修改现有的表................................................................................................................... 17 删除表 ............................................................................................................................... 19 第九章 约束 ................................................................................................................................. 20 创建约束 ........................................................................................................................... 20 使用Primary Key约束 .................................................................................................. 20 使用Foreign Key约束 .................................................................................................. 21 使用Unique约束 ............................................................................................................ 21 使用Check约束 .............................................................................................................. 21 使用Not NULL约束 ........................................................................................................ 22 在创建表的过程中包括约束.......................................................................................... 22 查看约束 ........................................................................................................................... 22
PL/SQL——目录
II
禁用约束 ........................................................................................................................... 23 删除约束 ........................................................................................................................... 23 第十章 数据操作 ......................................................................................................................... 24 插入新行 ........................................................................................................................... 24 修改现有的行................................................................................................................... 25 替换变量 ........................................................................................................................... 25 事务控制语句................................................................................................................... 25 删除行 ............................................................................................................................... 25 表锁 ................................................................................................................................... 26 第十一章 视图 ............................................................................................................................. 27 创建视图 ........................................................................................................................... 27 创建复杂视图................................................................................................................... 27 删除视图 ........................................................................................................................... 28 创建内联视图................................................................................................................... 28 第十五章 PL/SQL简介 .............................................................................................................. 29 基本结构 ........................................................................................................................... 30 声明部分 ........................................................................................................................... 30 可执行部分 ....................................................................................................................... 31 执行控制 ........................................................................................................................... 31 循环语句 ........................................................................................................................... 32 第十六章 游标和异常................................................................................................................. 34 游标 ................................................................................................................................... 34 异常处理 ........................................................................................................................... 36
PL/SQL——第二章 基本的SQL SELECT语句
1
第二章 基本的SQL SELECT语句
命令说明 查看表的所有列的命令 查看表的一列的命令 查看表的多个列的命令 基本的语法结构 SELECT * FROM tablename ； SELECT columnname FROM tablename ； SELECT columnname，columnname，„„ FROM tablename ； SELECT columnname [AS] alias FROM tablename ； 例子 select * from books ; select title from books ; select title , pubdate from books ; select title AS titles from books ; 或 select title titles from books ; select retail-cost from books ; select distinct title from books ; 或 select unique title from books ; 在显示时向一列指定别名的命令 在检索时执行数学运算的命令 消除输出中的重复记录的命令 SELECT arithmetic expression FROM tablename ； SELECT DISTINCT columnname FROM tablename ； 或 SELECT UNIQUE columnname FROM tablename ； SELECT columnname | | columnname FROM tablename ； DESCRIBE tablename 在显示时对列内容执行串联命令 察看表的结构的命令
select firstname | | lastname from customers ; DESCRIBE books PL/SQL——第三章 限制行和对数据排序
2
第三章 限制行和对数据排序
语法 元素 WHERE子句 ORDER BY 子句 = > < <>、!=、^= <= >= 指定一个搜索条件 指定结果的显示顺序 数学比较运算符 相等运算符—需要记录数据与搜索值完全相等 “大于”运算符—需要记录大于搜索值 “小于”运算符—需要记录小于搜索值 “不等于”运算符—需要记录与搜索值不相等 “小于或等于”运算符—需要记录小于或等于搜索值 “大于或等于”运算符—需要记录大于或等于搜索值 其他比较运算符 [NOT] BETWEEN x AND y 搜索指定值范围内的记录 [NOT] IN （x，y，„„） 搜索与列表中的某个项目相等的记录 [NOT] LIKE IS [NOT] NULL % _ AND OR 搜索符合一种搜索模式的记录—与统配符一起使用 搜索指定列包含NULL值的记录 统配符 百分号表示任意数量的字符 下划线表示指定位置刚好有一个字符 逻辑运算符 将两个条件结合在一起—记录必须满足这两个条件 只需要记录满足其中一个搜索条件 说明 PL/SQL——第四章 联接多个表
3
第四章 联接多个表
A表
A_ID（或ID） B表 B_ID（或ID） P表 P_Name
Min Max B_name C_ID A_name A_Price 迪卡尔连接（A表每个记录与B表每个纪录配对。A*B）
1. select A_ID , B_ID
from A , B
2. select A_ID , B_ID
from A cross join B
相等连接（A表中某列与B表中某列相等）
1. select A . A_name , B . B_name
from A , B
where A . A_ID = B . B_ID
2. select A . A_name , B . B_name
from A join B on A . A_ID = B . B_ID
如果A表与B表关联的列名相等，则可以用Natural Join（此时不能使用修饰符） select A_name , B_name
from A Natural Join B 或：
select A_name , B_name
from A Join B using （ID）
不等连接（不存在可以关联的相同行，即一定范围内的连接）
1. select A_name , P_name
PL/SQL——第四章 联接多个表
4
from A , P where A_Price
between Min and Max
2. select A_name , P_name
from A join P
on A_Price between Min and Max
自我连接（同一张表内的自身连接）
1. select r . B_ID , c . C_ID
from B r, B c
where r . B_ID =c . C_ID
2. select r . B_ID , c . C_ID
from B r join B c on r . B_ID =c . C_ID
外部连接（连接查询的结果中包括存在与一个表中但是另一个
表中没有相应行的纪录）
1. select A . A_name , B . B_name 左外连接
from A , B
where A . A_ID = B . B_ID（+）
2. select A . A_name , B . B_name 左外连接
from A left（left/right/all） outer join B on A . A_ID = B . B_ID
集合运算符（）
Select * from A_name
Union （Union / Union all / Intersect / Minus） Select * from B_name
Union：返回结合的select语句的结果，删除重复的纪录。
Union All：返回结合的select语句的结果，不删除重复的纪录。 Intersect：只返回两个select语句中都包含的行。
Minus：删除第一个查询中也存在的第二个查询的结果，只显示由第一个查询返回的行。
PL/SQL——第五章 单行函数
5
第五章 单行函数
大小写转换函数
1. Lower函数：全部转小写
Lower（c）：C代表字符串
Select Lower (pnl_id) from wp_pnlstat
2. Upper函数：全部转大写
Upper（c）：C代表字符串
select Upper (pnl_id) from wp_pnlstat
3. Initcap函数：首字母大写，其余小写
Initcap（c）：C代表字符串
select Initcap (pnl_id) from wp_pnlstat
字符串处理函数
1. SUBSTR函数：返回字符串一部分
Substr（c，p，l）：C代表字符串，P表示要提取的起始字符，L表示在查询结果中返回的字符串长度。
Select Substr (pnl_id , 1 , 3) from wp_pnlstat
2. LENGTH函数：返回字符串长度
Length（c）：C代表字符串
select Length (pnl_id) from wp_pnlstat
3. LPAD和RPAD函数：使用一个特定的符号（甚至是一个空格）来填充字符串左边
（或者右边）的区域 LPAD（c，l，s）：C代表字符串，L表示填充“之后”字符串的长度，S表示用于填充的符号或字符
select LPAD (pnl_id , 15 , 'A') from wp_pnlstat
4. LTRIM和RTRIM函数：从一组数据的左侧（右侧）删除特定的字符串
LTRIM（c，s）：C代表字符串，S表示将从数据左侧删除的字符串 select LTRIM (pnl_id , 'T') from wp_pnlstat
5. Replace函数：查找制定字符串的具体值，如果找到了，则用另一组字符替换
Replace（c，s，r）：C代表将搜索的数据或列，S代表想要查找的字符串，R表示将替换S的字符串
PL/SQL——第五章 单行函数
6
select Replace (pnl_id , 'T35' , 'A') from wp_pnlstat
6. Concat函数：串联两个列中的数据
Concat（c1，c2）：C1表示在串联中包括的第一个项，C2表示在串联中包括的第二个项
select Concat (pnl_id , serial_no) from wp_pnlstat
数字函数
1. Round函数：将数字字段舍入到指定的精度
Round（n，p）：N表示将要舍入的数字数据或字段，P表示舍入位数 Select Round(retail , 1) from books
P为0表示舍入到没有小数字，P为-1表示舍入到十位。
2. Trunc函数：截断数字数据
Trunc（n，p）：N表示要截断的数字数据或字段，P表示删除或截断的数字位置 Select Trunc (retail , 1) from books
Date函数
1. Months_Between函数：确定两个日期之间的月数
Months_Between（d1，d2）：D1，D2是所使用的两个日期 Select Months_Between (orderdate , pubdate) from books
2. Add_Months函数：增加月数
Add_Months（d，m）：D表示计算的起始日期，M表示在该日期上增加的月数 Select Add_Months (orderdate , 60) from books
3. Next_Day函数：确定在指定日期之后一周中的下一个特定日期
Next_Day（d，day）：D表示开始日期，Day表示将要确定的一周中的某一天 Select Next_Day (orderdate , 'MONDAY') from books
4. TO_Date函数：将输入的日期转换成Oracle9i使用的默认格式（两位的天数、三个
字母的月份简写、两位年份） TO_Date（d，f）：D表示开始日期，F是所输入的日期格式 Select TO_Date ('04/05/06' , 'MM/DD/YY') from books 日期格式 元素 MONTH 说明 全部写出的月份名称--添加空格，APRIL 例子 PL/SQL——第五章 单行函数
7
达到9个自负的总宽度 MON MM RM D DD DDD DAY DY YYYY YYY、YY、Y YEAR B．C．或A．D．
月份名称的前三个字母简写 月份的两位数字值 罗马数字的月份 一周中某一天的数值 一个月某一天的数值 一年中某一天的数值 一周中某一天的名称--添加空格，达到9个字符的总长度 一周中某一天的三个字母间歇 APR 04 IV 星期四就是4 28 12月31就是365 Wednesday WED 显示4位的年份 2004 现实年份的最后三位、两位、一位 2004年为004、04、4 全部写出年份 表示公元前或公元后 TWO THOUSAND FOUR 2004 A．D． 其它函数
1. NVL函数：用来使用一个值替代现有的NULL值
NVL（x，y）：X是一个NULL值，Y表示用来替代它的值 Select NVL (orderdate , '07-APR-03') from books
2. NVL2函数：允许使用不同的选项替代现有的NULL值
NVL2（x，y，z）：X是一个NULL值，Y表示当X不是NULL时所替换的数据，Z表示当X是NULL时所替换的数据。
Select NVL2 (orderdate , 'Shipped' , 'Not Shipped') from books
3. TO_Char函数：将日期和数字转换为格式化的字符串
TO_Char（n，f）：N是将要格式化的日期或数字，F是将使用的格式模型 Select TO_Char (orderdate , 'MONTH DD,YYYY') from books
如果想去除填充的空格，可在数据模型开始处出入fm，如fmMONTH。 格式 元素 MONTH MON MM RM 说明 全部写出的月份名称--添加空格，达到9个自负的总宽度 月份名称的前三个字母简写 月份的两位数字值 罗马数字的月份 APRIL APR 04 IV 例子 PL/SQL——第五章 单行函数
8
D DD DDD DAY DY YYYY YYY、YY、Y YEAR B．C．或A．D． SS SSS MI HH或HH12 HH24 A．M．或P．M． 一周中某一天的数值 一个月某一天的数值 一年中某一天的数值 一周中某一天的名称--添加空格，达到9个字符的总长度 一周中某一天的三个字母间歇 显示4位的年份 星期四就是4 28 12月31就是365 Wednesday WED 2004 现实年份的最后三位、两位、一位 2004年为004、04、4 全部写出年份 TWO THOUSAND FOUR 表示公元前或公元后 时间元素 秒 午夜过后的秒数 分钟 小时 小时 表示上午或下午的时间 0到59之间的值 0到86399之间的值 0到59之间的值 1到12之间的值 1到23之间的值 A．M．（中午之前）或P．M．（中午之后） 2004 A．D． 9 0 $ ． ， ，．（标点符号） “string” TH SP SPTH 数字元素 一系列9表示显示的宽度（不显示99999 没有意义的前导0） 显示没有意义的前导0 显示一个浮动美元符号 表示显示的小数字数 在指定的位置显示一个逗号 其它元素 显示指定的标点符号 现实双引号内的字符串 序号 完整写出数字 完整写出序号 0009999 $99999 999．99 9，999 DD，YYYY=24，2001 “of the year”YYYY=of the year 2001 DDTH=8th DDSP=EIGHT DDSPTH=EIGHTH
4. DECODE函数：接受一个制定的值，并将这个值与一个列表中的值进行比较，如
果查找了相同的值，则返回指定结果。如果没有查找相同的值，则返回默认的结果。如果没有定义默认结果，则返回NULL作为结果。 DECODE（v，l1，r1，l2，r2，„„，d）：V是正搜索的值，L1表示列表中的第一个至，如果L1和V相等，那么R1表示将返回的结果，一次类推，如果没有找到
PL/SQL——第五章 单行函数
9
相等的值，那么D是将返回的默认结果。
Select DECODE (state, 'CA', .8 , 'FA', .7 , 0) from books
5. SOUNDEX函数：引用单词的语音或表示。
SoundEX（c）：C是所引用的字符串。
Select lastname , SoundEX(lastname) from books
结果：
LastName SOUN -------------------------- NGUYEN N250 NELSON N425
后面跟3个数字的字母“N”是列出的姓氏的语音表示。这个字母表示姓氏实际的第一个字母，而三个数字表示其余字母产生的语音。
NESTRING函数
任何单行函数都可以嵌套在另一个单行函数内部。“嵌套”函数就是在另一个函数内部使用一个函数作为参数。在嵌套函数时，需要记住下列重要规则： 1. 必须提供各个函数需要的所有参数。
2. 对于每一个开括号都必须存在一个对应的闭括号。
3. 首先执行嵌套的函数或内部函数。内部函数的结果将被传递给外部函数，然后再执
行外部函数。
DUAL表
Dual是一个虚拟表。
1. SYSDATE：根据计算器的设置返回日期值
Select SYSDATE from dual
2. 任何单行函数都可以使用dual表。
Select Length('Hello') from dual
PL/SQL——第六章 组函数
10
第六章 组函数
组（多行）函数 函数（及语法） Sum（[Distinct | All] n） AVG（[Distinct | All] n） 说明 返回选定数字字段的总和。忽略Null值 例子 Select sum (retail-cost) form books Select AVG (retail-cost) form books Select count (*) from books Select count (shipdate) from books Select Max (customer) form customers Select Min (retail-cost) form books Select Stddev (retail) form books Select variance (retail) form books 返回选定数字字段的平均值。忽略Null值 Count（* | [|Distinct | All]） 返回指定字段中包含值的行数。不会在结果中包括在字段中包含Null值的行。要想计入所有行，包括那些包含Null值的行，应该使用*而不是字段名 Max（[Distinct | All] n） Min（[Distinct | All] n） Stddev [Distinct | All] n） 返回选定字段中的最大值。忽略Null值 返回选定字段中的最小值。忽略Null值 返回选定字段中的标准差。忽略Null值 variance（[Distinct | All] n） 返回选定字段中的方差。忽略Null值 Group By columnname[，columnname，„„] Having groupfunction comparisonoperator value
子句 根据指定列的内容将数组分组 Select AVG(cost) from books group by name 限制在查询结果中显示的组 Select AVG(cost) from books group by name having AVG(cost)>2 PL/SQL——第七章 子查询
11
第七章 子查询
子查询 单行子查询 多行子查询 多列子查询 相关子查询 不相关子查询 将多行结果返回到外部查询 将多列结果返回到外部查询 引用外部查询中的一列，对外部查询中的每一行执行一次子查询 首先执行子查询，然后将值传递给外部查询 说明 将包含一列的一行结果返回到外部查询
在使用任何类型的子查询时，要记住以下规则： 1. 子查询必须“自身就是一个完整的查询”——也就是说，它必须至少包括一个select
子句和一个from子句。
2. 子查询不能包括order by子句。如果显示的输出需要按照特定顺序显示，那么order
by子句应该作为外部查询的最后一个子句列出。 3. 子查询“必须包括在一组括号中”，以便将它们与外部查询分开。 4. 如果将子查询放在外部查询的where或having子句中，那么该子查询只能位于比较
运算符的“右边”
单行子查询
1. where子句中的单行子查询
select title , cost from books
where cost > (select cost from books where title =‟Database Implementation‟) and category = „computer‟
2. having子句中的单行子查询
select category , avg(retail-cost) “average profit” from books group by category having avg(retail-cost)>(select avg(retail-cost) from books
where category = „computer‟)
3. select子句中的单行子查询
select子句中列出子查询时，将对父查询生成的每一行输出都显示子查询返回的值。即同一个值。
select title , retail , (select avg(retail) from books) “overall average” from books
多行子查询
1. In运算符
select title , retail , category from books
PL/SQL——第七章 子查询
12
where retail in (select max(retail) from books group by category) order by category
2. All和Any运算符 运算符 说明 > ALL < ALL < ANY > ANY = ANY 大于子查询返回的最大值 小于子查询返回的最小值 小于子查询返回的最大值 大于子查询返回的最小值 等于子查询返回的任何值（与IN相同） select title , retail from books
where retail > ALL (select retail from books where category=‟Cooking‟)
3. Exists运算符
用来确定一个子查询中是否存在一个条件。这个运算符的结果是一个布尔值——如果条件存在，则为True，如果不存在，则为False。如果结果是True，则将显示满足条件的纪录。
Select title from books
Where not exists (select isbn from orderitems
where books.isbn =orderitems.isbn)
4. Having子句中的多行子查询
Select order , sum(retail*quantity) from orders natural join orderitems natural join books
Having sum(retail*quantity) > ANY (select avg(sum(retail*quantity))
From orders natural join orderitems Natural join books group by shipstate)
多列子查询
1. From子句中的多列子查询
在外部查询的from子句中使用多列子查询时，实际上是创建了一个外部查询的其它子句可以引用的临时表。如果子查询生成的临时表包含分组的数据，那么可以像对单独的数据值那样引用或使用这些数据。
Select b.title , b.retail , a.category , a.cataverage from books b , (select category , AVG(retail) cataverage from books group by category) a 作为一个临时表 Where b.category =a.category and b.retail > a.cataverage
2. Where子句中的多列子查询
语法：where（columnname，columnname，„„）in subquery
PL/SQL——第七章 子查询
13
① 因为where子句包含多个列名称，所以列列表必须包括在括号中。
② 在where子句中列出列名称的顺序必须与在子查询的select子句列出他们的顺
序相同
Select title , retail , category from books
Where (category , retail) in (select category , max(retail) from books
group by category)
Order by category
NULL值
1. 子查询中的NVL
如果子查询可以向外部查询返回一个NULL值进行比较，那么应该使用NVL函数来使用一个实际值来代替NULL。但必须记住以下两点：
① 必须对子查询和外部查询中的NULL值都替代NULL值。
② 替代NULL值的值必须是这个列中的任何位置都不可能存在的值。 Select customer from customers
Where NVL(referred , 0) =(select NVL(referred , 0) from customers where
customer = 1005)
2. 子查询中的IS NULL
虽然从一个子查询向另一个子查询传递NULL值时存在问题，但在子查询中搜索NULL值却是允许的。与常规的查询一样，仍然可以使用IS NULL比较运算符来搜索NULL值。
Select distinct title from books natural join orderitems
Where order in (select order from orders where shipdate IS NULL)
3. 关联的子查询
如果一个子查询引用外部查询中的一列，那么它就是一个关联子查询。关联子查询是对外部查询中的每一行处理或执行一次的子查询。
Select title from books
Where exists (select isbn from orderitems where books.isbn=orderitems.isbn)
嵌套的子查询
在嵌套子查询时，可以使用以下策略：
① 确切地决定自己想要查找什么。这是查询的目标。 ② 首先编写最内部的子查询。
③ 在编写了最内部的子查询之后，察看可以传递回外部查询的值，如果这不
是外部查询所需要的值（例如它引用了错误的列），则分析需要如何转换数据以获得正确的行，并且在外部查询与嵌套的查询之间使用另一个子查询。
PL/SQL——第七章 子查询
14
在某些情况下，你可能需要创建几层子查询，将最内部的子查询所返回的值链接到外部查询所需要的值。
Select customer# , lastname , firstname from customers natura join orders Where order in (select order from orderitems natural join orders
Group by order
Having count(*) in (select max(count(*)) from orderitems
Group by order))
PL/SQL——第八章 表的创建和维护
15
第八章 表的创建和维护
创建表 命令和子句 Create Table 说明 在数据库中创建一个新表。用户命令列并指定所存储的数据类型。要想查看一个表，可以使用SQL Plus命令Describe tablename 使用as子句和子查询根据现有的数据库创建一个表 修改表 向表添加一列 更改一个标的大小、数据类型或默认值 从一个表中删除一列 标记一个以后将要删除的列 结束删除之前使用Set Unused标记的列 更改一个表的名称 删除所有表行，但是保留表名称和列结构 删除表 从Oracle9i数据库中删除整个表 Create Table „„ as Alter Table „„ Add Alter Table „„ Modify Alter Table „„ Drop Column Alter Table „„ Set Unused或Set Unused Column Drop Unused Columns Rename „„ To Truncate Table Drop Table
表设计
命名表和列时，Oracle9i使用下列规则：
① 表和列的名称最多可以达到30个字符长，而且必须以一个字母开始。这个限制
指适用于表或列的名称，而不适用与一列中的数据量。 ② 表和列名称中允许数字、下划线（_）和数字符号（#）。但是不能在表和列名称
中包含任何空格。
③ 用户拥有的每一个表都应该具有唯一的表名称，而且每一个表中的列名称也应
该是唯一的。
④ 不能使用Oracle9i“保留字”，如select，distinct，char，number等。 Oracle9i数据类型 数据类型 Varchar2（n） 说明 长度可变的字符数据，其中n表示这个列的最大长度。最大大小是4000个字符。这种数据类型没有默认大小，必须指定一个最小值。例子：varchar2（9）最多可以包含9个字母、数字或符号 长度固定的字符列，其中n表示列的长度。默认大小是1。最大大小是2000个字符。例子：char（9）可以包含9个字母、数字或符Char（n） PL/SQL——第八章 表的创建和维护
16
号。但是，如果输入的字符少于9个，则会向右边添加空格，使数据达到9个字符的长度。 Number（p，s） 数字列，其中p表示精确度（或小数点右边和左边的总位数），最大值是38位。S（或scale，即小数字数）表示小数点右边的位数。例子：number（7，2）最多可以存储数值99999．99。如果没有指定精确度或小数字数，那么该列的精确度将默认为38位 存储公元前4712年1月1日与公元9999年12月31日之间的日期和时间。为这个列分配了7个字节来存储日期的世纪、念、月、日、小时、分、秒。Oracle9i以DD-Mon-YY格式显示日期。可以使用To_char格式显示日期的其它元素。Oracle9i将这个字段的宽度预定义为7个字节 存储最多2GB的长度可变的字符数据 用于最多4GB的单字节字符数据 最多存储2000字节的原始二进制数据 最多可以包含2GB的未结构化数据 最多可以存储4GB的未结构化数据 存储操作系统的一个二进制文件的文件定位器 Date数据类型的扩展，表示时间值（不需要TO_Char函数就可以引用的小时、分、秒） Date数据类型的扩展，用来确定特定的时间间隔或时间量 Date Long Clob Raw（n） Long Raw Blog BFile TIMESTAMP INTERVAL
创建表
1. 语法：Create Table [schema] tablename
（columnname datatype [default value] ，
[columnname datatype [default value]]„„）
Create Table acctmanager (amid varchar2(4) ,
Amname varchar2(20) ,
Amedate date default sysdate , Region char(2))
查看表结构：describe Describe tablename
2. 通过子查询创建表
语法：Create Table tablename [（columnname，„„）] As （subquery）
如果新表中的列命名与现有表中的列不同，那么必须在表名称后面的括号中列出新的列名称。但是，如果不想更改任何列名称，那么可以省略Create Table子句中的列列
PL/SQL——第八章 表的创建和维护
17
表。如果在Create Table子句中提供了一列列表，那么写出每一列的名称——包括那些保持不变的名称。换句话说，如果将从子查询返回5列，那么这5列都必须在Create Table子句中列出，否则Oracle9i将返回一个出错消息，语句将会失败。此外，列列表必须与子查询的select子句中列出列的顺序相同，这样Oracle就会知道子查询的哪一列将分配给新表中的哪一列。
Create Table secustomerorders As (select customer , state , ISBN , category , quantity , cost , retail
From customers natural join orders natural join orderitems natural join books where state in („FL‟,‟GL‟,‟AL‟))
在创建表时，这些记录将自动添加到新表中。
3. 带有组函数的子查询
如果要在创建新表时改变列名，可以在子查询中为每一个列指定一个列别名，或者为Create Table子句的列列表中的列提供一个新名称。
Create Table secustomersspent (name , spent)
As (select firstname || „‟ || lastname , sum(retail*quantity) from customers
natural join orders natural join orderitems natural join books Where state in („FL‟ , „GA‟ , „AL‟) group by firstname || „‟ || lastname)
修改现有的表
ALTER TABLE tablename
ADD | MODIFY | DROP COLUMN | columnname [definition]
1. ALTER TABLE „„ ADD命令：使用add子句允许用户向表添加一个新列。必
须使用一个列名称和数据类型（以及宽度，如果需要）定义一个新列。新列将添在表的最后一列
语法：ALTER TABLE tablename
ADD (columnname datatype , [default]„„) ALTER TABLE acctmanager ADD (ext number(4))
2. ALTER TABLE „„ MODIFY命令：
使用modify子句来更改现有列的定义，包括： ① 更改一列的大小（例如增加或减少）；
② 更改数据类型（例如将varchar2更改为char） ③ 更改或添加一列的默认值
语法：ALTER TABLE tablename
MODIFY(columnname datatype , [default]„„) 修改现有的表时，要注意：
PL/SQL——第八章 表的创建和维护
18
① 一列必须与它已经包含的数据字段一样宽；只能将一个列的大小减小到不
小于现有数据的最大宽度。
② 如果一个number列已经包含了数据，那么不能降低这一列的精确度或小树
位数；
③ 更改一列的默认值不会更改表中已经存在的数据值。只更改为“将来”插
入到表中的行分配的默认值。表中已经存在的行分配的默认值将不变，如果要对现有行中包含的默认值进行更改，那么必须手工执行这些更改。
ALTER TABLE acctmanager MODIFY(ext default 1200)
3. ALTER TABLE „„ DROP COLUMN命令：通过DROP COLUMN子句从一个
表中删除现有的列。包括其内容。 语法：ALTER TABLE tablename
DROP COLUMN columnname
在使用DROP COLUMN子句时，注意：
① 与使用带有ADD或MODIFY子句的ALTER TABLE命令不同，DROP
COLUMN子句只能引用“一个”列。
② 如果从表中删除一列，那么删除将是永久的。如果不小心从表中错误地删
除了列，那么你不能“取消”。唯一选择是将这一列重新添加到表中，然后手工重新输入以前包含的所有数据。
③ 不能删除表中剩余的最后一列。如果一个表只包含一列并且你尝试删除这
一列，那么这个命令将会失败，Oracle9i将返回一个错误消息。
ALTER TABLE acctmanager DROP COLUMN ext
4. ALTER TABLE „„ SET UNUSED / DROP UNUSED COLUMNS命令：如果
将该列标记为删除，则这一列就是不可用的，它不会显示在表结构中，不会出现在任何查询语句中，也不能在这一列上执行除了ALTER TABLE „„ DROP
UNUSED 命令之外的其它任何操作。换句话说，将这一列设置为“unused”之后，这一列及其所有内容都将无法使用，以后也不能恢复它。
语法：ALTER TABLE tablename SET UNUSED （columnname）
或者ALTER TABLE tablename SET UNUSED COLUMN columnname 删除以前标记为“不使用”的列的语法： ALTER TABLE tablename DROP UNUSED COLUMNS
ALTER TABLE secustomerorder SET UNUSED (cost)
ALTER TABLE secustomerorder DROP UNUSED COLUMNS
PL/SQL——第八章 表的创建和维护
19
5. 重命名表
语法：RENAME oldtablename TO newtablename Rename secustomersspent TO setotals
6. 截断表：截断一个表，表中包含的所有行都将被删除，但是表本身将会保留。换句
话说，列依然存在，只是其中没有存储值。实际上与删除一个表中的所有行是相同的，只不过用后者删除所有行后，这些行所占用的存储空间仍然是分配给这个表，而用Truncate Table命令，则删除存储在一个表中的行，并释放这些行所占用的存储空间。
语法：TRUNCATE TABLE tablename Truncate Table setotals
删除表
语法：DROP TABLE tablename Drop Table setotals
PL/SQL——第九章 约束
20
第九章 约束
约束 Primary Key Foreign Key 说明 缩写 确定哪些列唯一的标识了各个纪录。主键不能是NULL，_ pk 并且数据值必须是唯一的。 在一种一对多关系中，将约束添加到“多”表中。约束_ fk 确保了如果将一个值输入一个制定的列，那么它必须已经存在于“一”表中，或者还没有添加这个纪录 确保存储在一个指定列中的所有数据值都是唯一的。_ uk Unique约束与主键约束的不同之处在于它允许NULL值。 确保在向表添加数据值之前满足了一个指定的条件。例_ ck 如，一个订单的发货日期不能早于定购日期。 确保一个指定的列不能包含一个NULL值。“只能”使用创建表的列一级方法来创建Not NULL约束。 _ nn Unique Check Not Null
创建约束
1. 在列一级上创建约束
如果希望为约束提供一个特定的名称，则使用可选的关键词Constraint，只能在列一级上创建Not Null约束
语法：columnname [CONSTRAINT constraintname] constrainttype
2. 在表一级上创建约束
[CONSTRAINT constraintname] constrainttype （columnname ，„„），
在表一级上创建约束时，约束定义与列定义是分开的。如果在创建表的同时创建约束，那么将在定义了所有列“之后”列出它。表一级方法可以用来创建出了Not NULL约束之外的任何类型的约束。
使用Primary Key约束
语法：ALTER TABLE tablename ADD [CONSTRAINT constraintname] PRIMARY KEY( columnname ) ALTER TABLE promotion ADD CONSTRAINT promotion_gift_pk PRIMARY KEY( gift )
对于每一个表，只能创建“一个” PRIMARY KEY约束。如果这个主键包含多个列（一个复合主键），那么必须在表一级上创建它。
ALTER TABLE orderitems
PL/SQL——第九章 约束
21
ADD CONSTRAINT orderitems _pk PRIMARY KEY( order , item )
使用Foreign Key约束
语法：ALTER TABLE tablename
ADD [CONSTRAINT constraintname] FOREIGN KEY ( columnname ) REFERENCES referencedtablename (referencedcolumnname )
关键词FOREIGN KEY用来指定这样一列：如果它包含一个值，那么它必须与另外一个表中包含的数据匹配。指定为外键的列的名称包含在FOREIGN KEY关键词之后的一组圆括号中。
ALTER TABLE orders
ADD CONSTRAINT orders_customer_fk FOREIGN KEY
( customer ) REFERENCES customers ( customer )
当两个表之间存在FOREIGN KEY约束时，在默认情况下，如果在子表中存在匹配的项目，则不能从父表中删除一个记录，需先删除子表，再删除父表。如果在约束定义中包含了ON DELETE CASCADE关键词并从父表中删除一个记录，则将自动删除子表中任何相应纪录。
ALTER TABLE orders
ADD CONSTRAINT orders_customer_fk FOREIGN KEY
( customer )
REFERENCES customers ( customer ) ON DELETE CASCADE
FOREIGN KEY约束不能引用一个表中还没有指定为该被引用表的主键的列。
使用Unique约束
Unique约束的用途是确保在同一列中的两个记录没有存储相同的值。Unique约束允许NULL值。
语法：ALTER TABLE tablename ADD [CONSTRAINT constraintname] UNIQUE ( columnname ) ALTER TABLE books
ADD CONSTRAINT books_titles_uk UNIQUE (title)
使用Check约束
Check约束要求在向表添加纪录之前满足特定的条件。 语法：ALTER TABLE tablename ADD [CONSTRAINT constraintname] CHECK ( condition)
PL/SQL——第九章 约束
22
ALTER TABLE orders
ADD CONSTRAINT orders_shipdate_ck CHECK (orderdate<=shipdate)
使用Not NULL约束
禁止添加在指定的列中包含NULL值的行。 语法：ALTER TABLE tablename MODIFY ( columnname [CONSTRAINT constraintname] Not NULL) ALTER TABLE books
MODIFY ( pubid CONSTRAINT books_pubid_nn Not NULL)
在创建表的过程中包括约束
作为Create Table命令的一部分在列一级上创建一个约束，则在列的数据类型之后列出约束类型。
Create Table acctmanager
(amid VARCHAR2(4) PRIMARY KEY , amname VARCHAR2(20) Not NULL , amedate DATE DEFAULT SYSDATE , region CHAR(2) Not NULL)
除了Not NULL约束之外，还可以使用表一级方法在Create Table命令中包括约束：在定义了所有列之后，在这个命令的最后列出约束。
Create Table acctmanager (amid VARCHAR2(4) ,
amname VARCHAR2(20) CONSTRAINT acctmanager_amnae_nn Not NULL , amedate DATE DEFAULT SYSDATE , region CHAR(2) ,
CONSTRAINT acctmanager_amid_pk PRIMARY KEY (amid), CONSTRAINT acctmanager_ region_ck
CHECK (region IN(„N‟,‟NW‟,‟S‟,‟SE‟,‟SW‟,‟W‟,‟E‟)))
查看约束
语法：SELECT constraint_name , constraint_type , search_condition
FROM user_constraints WHERE table_name=‟acctmanager‟ 注意：
① 引用的第一列（constraint_name）列出了存在与acctmanager 表中的约束的
名称。
② 第二列（constraint_type）将根据具体情况列出一个字母；
如果约束是一个PRIMARY KEY约束，则列出P
PL/SQL——第九章 约束
23
如果约束是一个CHECK或Not NULL约束，则列出C 如果约束是一个UNIQUE约束，则列出U
如果约束是一个FOREIGN KEY约束，则列出R
③ 在SELECT子句中列出等第三列（search_condition）用来显示CHECK约
束使用的条件。对于不是CHECK约束的其它约束，这一列是空的。
禁用约束
使用DISABLE / ENABLE
语法：ALTER TABLE tablename DISABLE CONSTRAINT constraintname ALTER TABLE tablename ENABLE CONSTRAINT constraintname
删除约束
如果要修改约束，唯一的方法就是先删除，再重新添加。 语法：ALTER TABLE tablename DROP PRIMARY KEY | UNIQUE（columnname）|
CONSTRAINT constraintname
注意以下准则：
① DROP子句将根据所删除的约束类型而变化。如果DROP子句引用了这个
表的PRIMARY KEY约束，那么因为数据库中的每一个表只允许一个这样的子句，所以使用关键词PRIMARY KEY就足够了。
② 如果约束是一个UNIQUE约束，那么因为一个UNIQUE约束只引用一列，
所以只需要受约束影响的列名。
③ 无论约束名称是由用户指定还是由Oracle服务器指定的，都必须通过约束
的实际名称来引用其它任何类型的约束。
PL/SQL——第十章 数据操作
24
第十章 数据操作
命令 INSERT UPDATE COMMIT ROLLBACK DELETE LOCK TABLE SELECT„„FOR UPDATE 说明 向表添加新行，用户可以包括一个子查询来复制现有表中的行 向现有的行添加数据或者修改现有行中的数据 将更改后的数据永久地保存在表中 允许用户“撤销”对数据的未提交更改 删除表中的行 禁止其它用户更改表 在表上创建一个共享锁，以便禁止其它用户更改制定列中的数据 交互式运算符 &
指定一个替换变量，允许用户根据提示为替换变量输入一个特定的值 插入新行
1. INSERT命令
语法：INSERT INTO tablename [(columnname,„„)] VALUES (dateavalue,„„) 注意语法元素：
① 关键词INSERT INTO的后面是要输入这些行的名称，表明城后面是包含
数据的列的名称。
② VALUES子句指出将插入表中的数据值。在VALUES关键词之后的圆括号
中列出了实际的数据。
③ 如果在VALUES子句中输入的数据与表中的列的顺序相同，那么在INSERT
INTO子句中可以忽略列名称。不过，如果只输入了某些列的数据，或者如果列出的列的顺序与在表中列出它们的顺序不同，那么“必须”在INSERT子句中提供这些列的名称，并且使用在VALUES子句中列出他们的顺序。必须在INSERT INTO子句中的表名称后面的圆括号中列出列名称。 ④ 如果列出了多个列，那么必须使用逗号将列名称隔开。 ⑤ 如果输入了多个数据值，那么必须使用逗号将它们隔开。
⑥ 与在SELECT语句的WHERE子句中对搜索条件使用的数据值时一样，必
须在单引号中包括要插入到定义为非数字数据的列（也就是列的数据类型不是NUMBER）中的数据。
2. 插入现有表中的数据
语法：INSERT INTO tablename [(columnname,„„)]
Subquery
PL/SQL——第十章 数据操作
25
INSERT INTO命令不需要将子查询包括在一组圆括号中，不过包含圆括号也不会引发出错信息。 INSERT INTO acctmanager SELECT amid , amname , amedate , region FROM acctmanager WHERE amedate<=‟01-OCT-02‟
修改现有的行
UPDATE命令
语法：UPDATE tablename set columnname = new_datavalue [WHRE condition]
UPDATE acctmanager SET amname= UPPER (amname) WHRE amid=‟J500‟
替换变量
UPDATE customers SET region=‟& Region‟ WHERE state=‟& State‟
执行该SQL语句，将首先提示用户输入Region的替换变量的值，然后输入where子句的第二个替换变量值。
在执行UPDATE命令之后，用户可以通过在SQL>提示符上输入一个正斜线（/）来重新执行这个命令。
事务控制语句
COMMIT和ROLLBACK命令
在处理DML语句时，在隐含或明确执行一个COMMIT命令之前，对表进行的更改是不是永久性的，可以通过ROLLBACK命令撤销。DDL命令执行后自动发射
COMMIT，所以不能被撤销。如果系统崩溃了，那么将在Oracle9i重新启动之后自动发生ROLLBACK，并且撤销之前还没有提交的所有操作。 在SQL>提示符上输入COMMIT，将发生“明确”的COMMIT，当用户在SQL>提示符上执行EXIT命令以退出系统时，将“隐含”地发生COMMIT命令。隐含的COMMIT命令还会在执行一个DDL命令（如Create或Alter Table）时发生。
删除行
语法：DELETE FROM tablename [WHRE condition]
PL/SQL——第十章 数据操作
26
表锁
1. LOCK TABLE命令
共享锁：其它用户可以查看存储在表中的数据，但不能更改表的结构或执行其它类型的DDL操作。
语法：LOCK TABLE tablename IN SHARE MODE
排他锁：其它用户不能更改表，也不能添加或更新表的内容。 语法：LOCK TABLE tablename IN EXCLUSIVE MODE
如果在表中存在一个排他锁，那么其它用户不能在同一个表上获得一个排他锁或一个共享锁。如果用户在一个表上有一个共享锁，则其它用户不能在同一个表上放置排他锁。
如果用户执行一个事务控制语句（如ROLLBACK或者COMMIT），或者用户退出系统，那么将自动释放锁（包括排他锁）
2. SELECT „„ FOR UPDATE命令
防止在执行SELECT命令与执行UPDATE命令之间，记录的内容更改，可以在预计修改一个记录时，使用SEL SELECT „„ FOR UPDATE命令。SELECT „„ FOR UPDATE命令在将要更改的纪录上放置了一个共享锁，防止其它任何用户获得同样纪录上的锁。
语法：SELECT columnnames ,„„ FROM tablename , „„ [WHRE condition]
FOR UPDATE
如果用户决定更新一个记录，那么可以使用常规的UPDATE命令来执行更改。如果用户不更改SEL SELECT „„ FOR UPDATE命令选定的任何数据，那么仍然必须执行COMMIT或者ROLLBACK命令，否则选定的行将是保持是被锁定的，其它用户不能更改这些行。
PL/SQL——第十一章 视图
27
第十一章 视图
创建视图
语法：CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW viewname(columname , „„) AS subquery [WITH CHECK OPTION [CONSTRAINT constraintname]] [WITH READ ONLY] ; 语法元素：
① CREATE VIEW关键字用来创建视图。CREATE OR REPLACE VIEW
关键字用来更改一个视图。
② FORCE/NOFORCE：CREATE子句中包含FORCE关键字，那么Oracle9i
将在缺少任何引用表的情况下创建这个视图。NOFORCE是CREATE VIEW命令的默认模式，这意味着所有表和列都必须是有效的，否则将不会被创建。
③ WITH CHECK OPTION：该约束确保了在视图上执行的任何DML操作
（例如添加行、更改数据）不会因为不再符合WHERE子句的条件而禁止视图访问行。
1. 创建简单视图
连接一个表
CREATE VIEW inventory
AS SELECT isbn , title , retail price FROM books WITH READ ONLY ;
2. 简单视图上的DML操作
只要在创建视图时没有使用WITH READ ONLY选项，而且在DML操作没有违反底层的基本表的现有约束，则允许在简单视图上执行这个操作。PRIMARY KEY、NOT NULL、UNIQUE、FOREIGN KEY、WITH CHECK OPTION。
创建复杂视图
连接一个或多个表。
复杂视图上的DML操作准则：
① 不允许违反约束的DML操作。
② 不能将一个值添加到包含算术表达式的列中。 ③ 在非key-preserved表上不允许DML操作。
④ 在包含组函数、GROUP BY子句、ROWNUM伪列或DISTINCT关键字
的视图上不允许DML操作。
PL/SQL——第十一章 视图
28
删除视图
语法：DROP VIEW viewname ;
创建内联视图
内联视图不是一个持久的数据库对象。它实际上用来执行一个命令时提供临时的数据源。
TOP—N分析
语法：SELECT columnname , „„ , FROM (subquery) WHERE ROWNUM<=N ;
SELECT title , profit
FROM (SELECT title , retail-cost profit
FROM books
ORDER BY retail-cost DESC)
WHERE ROWNUM<=5 ;
PL/SQL——第十五章 PL/SQL简介
29
第十五章 PL/SQL简介
PL/SQL优点：
① 除了基本的SQL语句之外，PL/SQL还允许用户包含异常处理和控制结构，从
而允许更高的灵活性和效率。
② 对于经常执行的任务，可以存储PL/SQL块，并由各种应用程序（或用户）使
用。
③ 通过授予执行在PL/SQL中创建的存储过程的权限，而不是直接向用户授予对
表或其它数据库对象的权限，可以维护更高的安全性。
PL/SQL块可以使一个命名过程或者一个函数，用户或应用程序可以引用他们。“函数”是存储在Oracle9i数据库服务器上的一个命名PL/SQL块，它接受0个或更多输入参数并返回一个值。可以在SQL语句内部使用函数，用户通常会在需要计算一个值时使用它。“过程”也是一个命名的PL/SQL块，不过在处理几个变量时才会使用它。过程可以接受输入（IN）、输出（OUT）或者输入输出（INOUT）参数：
① IN参数表示在执行这个过程期间不能更改调用应用程序接受的值。 ② OUT参数表示在执行这个过程期间将会计算一个值。
③ INOUT参数表示这个过程将更改调用应用程序传递给过程的一个值。 与函数不同，“过程不返回一个值，必须使用OUT或INOUT参数才能向应用程序返回计算值。”此外，必须由PL/SQL块调用一个过程，或者使用EXECUTE命令运行它——不能在SQL语句中使用它。过程的默认参数是IN。
元素 说明 PL/SQL块结构 声明部分 可执行部分 异常处理部分 If语句 基本循环 For循环 While循环
包括三部分：声明、可执行和异常处理。只有可执行部分才是必需的。使用END（后面跟一个分号）来结束PL/SQL块 包含在PL/SQL块中使用的所有变量的定义和初始值。它是由关键词DECLARE标识的 包含在PL/SQL块中执行的所有SQL和非SQL语句 包含在PL/SQL块的过程中发生非语法错误时调用的异常处理程序 根据一个或一系列指定的条件，忽略PL/SQL块的可执行部分中的语句的执行顺序 在满足制定的条件之前重复执行一系列预计。在条件为True时退出循环。 使用隐含声明的计数器，使一系列语句重复执行指定的次数 在指定的条件为False之前重复执行一系列语句。因为条件是在循环最后评估的，所以总是至少执行一次 PL/SQL——第十五章 PL/SQL简介
30
基本结构
[Declare] Begin
[Exception]
声明部分：如果块中使用了任何变量或常量，必须先声明 异常处理部分：用来执行块的过程中出现了错误时闲时消息或者指出应该执行的其它操作。这个异常处理不是用来解决在编译块时发现的语法错误，而是处理在执行语句的过程中发生的错误。
End；
分号用来结束PL/SQL块中的各个语句。不过标识符Declare、Begin或者Exception后面没有包括分号。
声明部分
语法：variablename [CONSTANT] datatype [NOT NULL] [: = | DEFAULT value_or_expression] ; ① 指定一个名称
② 指出变量所能包含的数据类型
1. 变量名称
变量名称最多可以包括30个字符、数字或特殊符号。不过名称必须以一个字符开始。
2. 常量
通过CONSTANT关键词来指定一个常量。必须指定一个值。
3. PL/SQL数据类型
① 标量：用来存储一个单独的值，包括的数据类型于用于数据库列的数据类型相
同。此外还包括一种布尔数据类型（True，False，Null）以及用于整数的数据类型（如BINARY_INTEGER和PLS_INTEGER）。 ② 复合：组合在一起并被视为一个单元的数据集合。它可以用来确定表中一个整
行的结构，而不必定义各个单独的列。 ③ 引用：存储指向其它程序项目的指针。 ④ 大对象（Large Object，LOB）：存储指定大对象（例如映像）位置的定位器。
%TYPE属性可以用来指定与在同一个块中声明的另一个变量或数据库表中的一列相同的数据类型。
4. Not NULL约束
要想确保变量总是包括一个值，可以在声明它时为其指定一个NOT NULL约束。
PL/SQL——第十五章 PL/SQL简介
31
在数据类型之后包括关键词NOT NULL。如果将一个变量定义为NOT NULL，那么“必须”为其指定一个值或者初始化它。
5. 初始化变量
用default关键词或者PL/SQL“赋值运算符”（：=）来初始化变量。如果没有为一个变量指定初始值，那么Oracle9i将自动为其指定一个NULL值。
可执行部分
由Begin关键词标识。
1. Select语句语法：
SELECT columnname [ , columnname , „„]
INTO variablename [ , variablename , „„] 变量 FROM tablename WHERE condition
DECLARE v_a VARCHAR2(10) ; v_b NUMBER(5, 2) ; BEGIN SELECT a , b INTO v_a , v_b
FROM aaaa
WHERE c=‟ccc‟ ; END ;
2. PL/SQL中的DML语句
INSERT，UPDATE，DELETE命令语法与前面的相同。
在执行DML语句或者SELECT语句只检索表中的一行时，Oracle9i将创建一个隐式游标。 在发生一个隐含的或者明确的COMMIT操作之前，其他用户不能察看在PL/SQL块中包含的DML操作对数据进行的更改。
执行控制
IF语句：如果一个条件为True，那么IF语句将决定是否应该执行一个语句。 语法：IF condition THEN
statements ;
[ELSIF condition THEN
PL/SQL——第十五章 PL/SQL简介
32
statements ;]
[ELSE
statements ;]
END IF ;
DECLARE v_gift VARCHAR2(20) ; c_retailprice NUMBER (5, 2) : =29.95 BEGIN IF c_retailprice>56 THEN v_gift :=‟FREE SHIPPING‟ ; ELSIF c_retailprice>25 THEN v_gift :=‟BOOKCOVER‟ ; ELSE v_gift :=‟BOOKMARKER‟ ; END IF;
DBMS_OUTPUT.LINE(„The gift for a book costing‟ || c_retailprice || „is
a‟ || v_gift) ;
循环语句
1. 基本循环：执行语句，直到满足了EXIT子句中指定的条件为止
语法：LOOP statements; EXIT [WHEN condition] ; END LOOP ; DECLARE v_count NUMBER (1) : =0 ; BEGIN LOOP v_count : = v_count+1 ; DBMS_OUTPUT.LINE („The current value is ‟ || v_count) ; EXIT v_count : = 4 ; END LOOP ;
END ;
2. For循环
计数器不是一个必须在PL/SQL块声明部分声明的变量。在第一次执行LOOP时，将隐含声明计数器。
PL/SQL——第十五章 PL/SQL简介
33
语法：FOR counter IN [REVERSE] lower_limit .. upper_limit LOOP Statements ; END LOOP ;
FOR子句要求用户指出计数器的下限和上限。用两个句点隔开。如果在这个子句中包括了REVERSE关键字，那么计数器采取相反的方式（即计数器减少而不是增加）
BEGIN FOR i IN 1..10 LOOP DBMS_OUTPUT.LINE („The current value is ‟ || i) ; END LOOP ; END ;
3. While循环:WHILE循环执行一系列语句，直到条件变为Flase为止。如果条件最初
为Flase，那么永远不能进入这个循环 语法：WHILE condition LOOP statements ; END LOOP ;
DECLARE v_count NUMBER (1) : =0 ; BEGIN WHILE v_count<15 LOOP DBMS_OUTPUT.LINE („The current value is ‟ || v_count) ; v_count := v_count+1 ; END LOOP ; END ;
4. 嵌套的循环
任何类型的循环都可以嵌套在另一个循环中。在控制返回外部循环之前，必须完成内部循环的执行。
PL/SQL——第十六章 游标和异常
34
第十六章 游标和异常
元素 显式游标 说明 用户创建的游标。当SELECT语句检索了多个行时，就需要使用这种游标。在大多数情况下，用户必须声明、打开和关闭一个显式游标。可以从游标中提取数据以进行处理。 每一个游标都有4个属性：%ROWCOUNT、%FOUND、%NOTFOUND、%ISOPEN。这些属性可以用来控制循环 用来确定一个语句将评估为True、False还是NULL，该语句包含了通过逻辑运算符结合在一起的条件 异常指出在PL/SQL块的执行过程中发生了非语法错误。Oracle服务器返回一个错误来指出一个错误。如果这个块的异常处理部分没有捕获这个错误，那么这个错误将会传播（或返回）到调用环境 游标属性 逻辑表 异常处理
游标
1. 声明显式游标
显式游标是在PL/SQL块的声明部分声明的。 语法：CURSOR cursor_name IS selectquery ;
DECLARE CURSOR books_cursor IS SELECT title , retail FROM books NATURAL JOIN orderitems WHRE order =1012 ;
2. 打开显式游标
在打开游标时，将分配必要的内存，执行SELECT语句，然后将SELECT子句检索的数据加载到游标中。
语法：OPEN cursor_name ;
3. 关闭游标
从游标中检索了所有数据之后，必须明确关闭它。 语法：CLOSE cursor_name ;
4. 从游标中提取数据
Fetch命令检索游标中的行，然后将值赋给变量。必须首先在PL/SQL块的声明部分声明变量，然后才能在可执行部分引用他们。
PL/SQL——第十六章 游标和异常
35
语法：FETCH cursor_name INTO variablename [,„„variablename] ; 检索的第二行将替换之前赋给变量的第一行的值。 每一种游标（隐式游标和显式游标）都有4个可以作用PL/SQL块中的条件的属性。 游标属性 说明 %ROWCOUNT %FOUND %NOTFOUND %ISOPEN 指出处理的行数 如果处理了一行或多行，则包含值True——如果没有处理，则为False 如果没有处理行，则包含值True——如果处理了一行或多行，则为False 如果在处理之后不关闭游标，则包含值True——如果关闭游标，则为False。在发生隐式游标时，由于它是自动关闭的，因此这个值在处理之后总是False 5.
DECLARE v_title books.title%TYPE ; v_retail books. retail %TYPE ; CURSOR book_cursor IS 定义游标 SELECT title , retailV FROM books NATURAL JOIN orderitems WHERE order=1012 ; BEGIN OPEN book_cursor 打开游标 LOOP FETCH books_cursor INTO v_title , v_retail ; 通过循环提取数据 EXIT WHEN book_cursor%NOTFOUND 如果没数据，推出循环 DBMS_OUTPUT.LINE („Book title: ‟ || v_ title || „,price:‟ || v_ retail) ; END LOOP ; CLOSE book_cursor ; 关闭游标 END ;
游标For循环
游标For循环可以用来自动（或隐含）打开和关闭一个游标，也可以用来提取游标中的数据。
语法：FOR record_name IN crsor_name LOOP statement ; [statement ; „„] END LOOP ; 不是检索游标中的行并向变量赋值，而是将行的内容赋给一个记录。“记录”是一种复合数据类型，可以使其结构与检索的行的结构相同。要想指定记录的结构与检索的行的结构相同，可以在定义记录时使用%ROWTYPE属性。
PL/SQL——第十六章 游标和异常
36
DECLARE CURSOR books_cursor IS SELECT title , retail FROM books NATURAL JOIN orderitems WHERE order=1012 ; r_books books%ROWTYPE ; BEGIN FOR r_books IN books_cursor LOOP DBMS_OUTPUT.LINE („Book title: ‟ || r_books.title || „,price:‟ || r_books.retail) ; END LOOP ; END ;
在使用游标For循环时，你还可以使用一个子查询来声明游标。不是在PL/SQL块的声明部分声明游标，使用IN子句中的SELECT语句来代替游标名称。不过没有游标名称，所以不能引用游标的属性。
DECLARE r_books books%ROWTYPE ; BEGIN
FOR r_books IN (SELECT title , retail
FROM books NATURAL JOIN orderitems
WHERE order=1012) LOOP
DBMS_OUTPUT.LINE („Book title: ‟ || r_books.title || „,price:‟ || r_books.retail) ;
END LOOP ; END ;
6. 带有逻辑运算符的游标循环控制
如果没有提取任何行，%FOUND属性都将为NULL。当一个语句包含两个使用AND逻辑运算符结合在一起的条件，并且其中一个条件为NULL，而另一个条件为TRUE，整个语句都将评估为NULL。 AND True False Null OR True False Null True False Null
True False Null False False False Null False Null True False Null True True True True False Null True Null Null 异常处理
1. 常见的异常：NO_DATA_FOUND、TOO_MANY_ROWS、ZERO_DIVIDE
语法：EXCEPTION WHEN exception_type THEN
PL/SQL——第十六章 游标和异常
37
Statement ; [WHEN exception_type THEN Statement ;„„] [WHEN OTHERS THEN Statement ;„„]
如果发生了一个没有预料到的错误，那么可以在异常处理部分的最后一个语句中包括WHEN OTHERS子句，因为这是一个错误并且不符合在这一部分之前指定的类型，所以将捕获这个错误。
DECLARE v_title books.title%TYPE ; v_retail books. retail %TYPE ;
BEGIN SELECT title , retail INTO v_title , v_retail FROM books WHERE retail > 100 ; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.LINE („No rows were retrieved from the table‟) ; END ;
2. 用户定义的异常处理
步骤：
① 必须在PL/SQL块的声明部分声明异常的名称。为异常指定的数据类型是
EXCEPTION。
② 必须使用PRAGMA EXCEPTION_INIT语句将所声明的异常与Oracle服务器
错误号码相关联。
③ 必须在PL/SQL块的异常处理部分包含所声明的异常。 DECLARE id_already_in_use EXCEPTION ;
PRAGMA EXCEPTION_INIT (id_already_in_use ,-00001) ; BEGIN INSERT INTO publisher VALUES („1‟ , „A NEW PUBLISHER‟, ‟GUY SMART‟ , „000-555-2211‟) ; EXCEPTION WHEN id_already_in_use THEN DBMS_OUTPUT.LINE („Please choose another publisher ID‟) ; END ;
因篇幅问题不能全部显示，请点此查看更多更全内容