PL/SQL Oracle 基础教程

justthere

贡献于2013-02-19

字数:0 关键词: Oracle 数据库服务器 SQL

PL/SQL——目录 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 * from books ; 查看表的一列的命令 SELECT columnname FROM tablename ; select title from books ; 查看表的多个列的命令 SELECT columnname, columnname,„„ FROM tablename ; select title , pubdate from books ; 在显示时向一列指定别名 的命令 SELECT columnname [AS] alias FROM tablename ; select title AS titles from books ; 或 select title titles from books ; 在检索时执行数学运算的 命令 SELECT arithmetic expression FROM tablename ; select retail-cost from books ; 消除输出中的重复记录的 命令 SELECT DISTINCT columnname FROM tablename ; 或 SELECT UNIQUE columnname FROM tablename ; select distinct title from books ; 或 select unique title from books ; 在显示时对列内容执行串 联命令 SELECT columnname | | columnname FROM tablename ; select firstname | | lastname from customers ; 察看表的结构的命令 DESCRIBE tablename DESCRIBE books PL/SQL——第三章 限制行和对数据排序 2 第三章 限制行和对数据排序 语法 元素 说明 WHERE 子句 指定一个搜索条件 ORDER BY 子句 指定结果的显示顺序 数学比较运算符 = 相等运算符—需要记录数据与搜索值完全相等 > “大于”运算符—需要记录大于搜索值 < “小于”运算符—需要记录小于搜索值 <>、!=、^= “不等于”运算符—需要记录与搜索值不相等 <= “小于或等于”运算符—需要记录小于或等于搜索值 >= “大于或等于”运算符—需要记录大于或等于搜索值 其他比较运算符 [NOT] BETWEEN x AND y 搜索指定值范围内的记录 [NOT] IN (x,y,„„) 搜索与列表中的某个项目相等的记录 [NOT] LIKE 搜索符合一种搜索模式的记录—与统配符一起使用 IS [NOT] NULL 搜索指定列包含 NULL 值的记录 统配符 % 百分号表示任意数量的字符 _ 下划线表示指定位置刚好有一个字符 逻辑运算符 AND 将两个条件结合在一起—记录必须满足这两个条件 OR 只需要记录满足其中一个搜索条件 PL/SQL——第四章 联接多个表 3 第四章 联接多个表 A 表 A_ID(或 ID) A_name A_Price B 表 B_ID(或 ID) B_name C_ID P 表 P_Name Min Max  迪卡尔连接(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 月份名称的前三个字母简写 APR MM 月份的两位数字值 04 RM 罗马数字的月份 IV D 一周中某一天的数值 星期四就是 4 DD 一个月某一天的数值 28 DDD 一年中某一天的数值 12 月 31 就是 365 DAY 一周中某一天的名称--添加空格, 达到 9 个字符的总长度 Wednesday DY 一周中某一天的三个字母间歇 WED YYYY 显示 4 位的年份 2004 YYY、YY、Y 现实年份的最后三位、两位、一位 2004 年为 004、04、4 YEAR 全部写出年份 TWO THOUSAND FOUR B.C.或 A.D. 表示公元前或公元后 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 全部写出的月份名称--添加空格, 达到 9 个自负的总宽度 APRIL MON 月份名称的前三个字母简写 APR MM 月份的两位数字值 04 RM 罗马数字的月份 IV PL/SQL——第五章 单行函数 8 D 一周中某一天的数值 星期四就是 4 DD 一个月某一天的数值 28 DDD 一年中某一天的数值 12 月 31 就是 365 DAY 一周中某一天的名称--添加空格, 达到 9 个字符的总长度 Wednesday DY 一周中某一天的三个字母间歇 WED YYYY 显示 4 位的年份 2004 YYY、YY、Y 现实年份的最后三位、两位、一位 2004 年为 004、04、4 YEAR 全部写出年份 TWO THOUSAND FOUR B.C.或 A.D. 表示公元前或公元后 2004 A.D. 时间元素 SS 秒 0 到 59 之间的值 SSS 午夜过后的秒数 0 到 86399 之间的值 MI 分钟 0 到 59 之间的值 HH 或 HH12 小时 1 到 12 之间的值 HH24 小时 1 到 23 之间的值 A.M.或 P.M. 表示上午或下午的时间 A.M.(中午之前)或 P.M.(中午之后) 数字元素 9 一系列 9 表示显示的宽度(不显示 没有意义的前导 0) 99999 0 显示没有意义的前导 0 0009999 $ 显示一个浮动美元符号 $99999 . 表示显示的小数字数 999.99 , 在指定的位置显示一个逗号 9,999 其它元素 ,.(标点符号) 显示指定的标点符号 DD,YYYY=24,2001 “string” 现实双引号内的字符串 “of the year”YYYY=of the year 2001 TH 序号 DDTH=8th SP 完整写出数字 DDSP=EIGHT SPTH 完整写出序号 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) 返回选定数字字段的总和。忽 略 Null 值 Select sum (retail-cost) form books AVG([Distinct | All] n) 返回选定数字字段的平均值。 忽略 Null 值 Select AVG (retail-cost) form books Count(* | [|Distinct | All]) 返回指定字段中包含值的行 数。不会在结果中包括在字段 中包含 Null 值的行。要想计入 所有行,包括那些包含 Null 值 的行,应该使用*而不是字段名 Select count (*) from books Select count (shipdate) from books Max([Distinct | All] n) 返回选定字段中的最大值。忽 略 Null 值 Select Max (customer) form customers Min([Distinct | All] n) 返回选定字段中的最小值。忽 略 Null 值 Select Min (retail-cost) form books Stddev [Distinct | All] n) 返回选定字段中的标准差。忽 略 Null 值 Select Stddev (retail) form books variance([Distinct | All] n) 返回选定字段中的方差。忽略 Null 值 Select variance (retail) form books 子句 Group By columnname[, columnname,„„ ] 根据指定列的内容将数组分组 Select AVG(cost) from books group by name Having groupfunction comparisonoperator value 限制在查询结果中显示的组 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 Create Table „„ as 使用 as 子句和子查询根据现有的数据库创建一 个表 修改表 Alter Table „„ Add 向表添加一列 Alter Table „„ Modify 更改一个标的大小、数据类型或默认值 Alter Table „„ Drop Column 从一个表中删除一列 Alter Table „„ Set Unused 或 Set Unused Column 标记一个以后将要删除的列 Drop Unused Columns 结束删除之前使用 Set Unused 标记的列 Rename „„ To 更改一个表的名称 Truncate Table 删除所有表行,但是保留表名称和列结构 删除表 Drop Table 从 Oracle9i 数据库中删除整个表  表设计 命名表和列时,Oracle9i 使用下列规则: ① 表和列的名称最多可以达到 30 个字符长,而且必须以一个字母开始。这个限制 指适用于表或列的名称,而不适用与一列中的数据量。 ② 表和列名称中允许数字、下划线(_)和数字符号(#)。但是不能在表和列名称 中包含任何空格。 ③ 用户拥有的每一个表都应该具有唯一的表名称,而且每一个表中的列名称也应 该是唯一的。 ④ 不能使用 Oracle9i“保留字”,如 select,distinct,char,number 等。 Oracle9i 数据类型 数据类型 说明 Varchar2(n) 长度可变的字符数据,其中 n 表示这个列的最大长度。最大大小 是 4000 个字符。这种数据类型没有默认大小,必须指定一个最小 值。例子:varchar2(9)最多可以包含 9 个字母、数字或符号 Char(n) 长度固定的字符列,其中 n 表示列的长度。默认大小是 1。最大大 小是 2000 个字符。例子:char(9)可以包含 9 个字母、数字或符 PL/SQL——第八章 表的创建和维护 16 号。但是,如果输入的字符少于 9 个,则会向右边添加空格,使 数据达到 9 个字符的长度。 Number(p,s) 数字列,其中 p 表示精确度(或小数点右边和左边的总位数),最 大值是 38 位。S(或 scale,即小数字数)表示小数点右边的位数。 例子:number(7,2)最多可以存储数值 99999.99。如果没有指 定精确度或小数字数,那么该列的精确度将默认为 38 位 Date 存储公元前 4712 年 1 月 1 日与公元 9999 年 12 月 31 日之间的日 期和时间。为这个列分配了 7 个字节来存储日期的世纪、念、月、 日、小时、分、秒。Oracle9i 以 DD-Mon-YY 格式显示日期。可以 使用 To_char 格式显示日期的其它元素。Oracle9i 将这个字段的宽 度预定义为 7 个字节 Long 存储最多 2GB 的长度可变的字符数据 Clob 用于最多 4GB 的单字节字符数据 Raw(n) 最多存储 2000 字节的原始二进制数据 Long Raw 最多可以包含 2GB 的未结构化数据 Blog 最多可以存储 4GB 的未结构化数据 BFile 存储操作系统的一个二进制文件的文件定位器 TIMESTAMP Date 数据类型的扩展,表示时间值(不需要 TO_Char 函数就可以 引用的小时、分、秒) INTERVAL Date 数据类型的扩展,用来确定特定的时间间隔或时间量  创建表 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 确定哪些列唯一的标识了各个纪录。主键不能是 NULL, 并且数据值必须是唯一的。 _ pk Foreign Key 在一种一对多关系中,将约束添加到“多”表中。约束 确保了如果将一个值输入一个制定的列,那么它必须已 经存在于“一”表中,或者还没有添加这个纪录 _ fk Unique 确保存储在一个指定列中的所有数据值都是唯一的。 Unique 约束与主键约束的不同之处在于它允许 NULL 值。 _ uk Check 确保在向表添加数据值之前满足了一个指定的条件。例 如,一个订单的发货日期不能早于定购日期。 _ ck Not Null 确保一个指定的列不能包含一个 NULL 值。“只能”使 用创建表的列一级方法来创建 Not NULL 约束。 _ nn  创建约束 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 块结构 包括三部分:声明、可执行和异常处理。只有可执行部分才是必 需的。使用 END(后面跟一个分号)来结束 PL/SQL 块 声明部分 包含在 PL/SQL 块中使用的所有变量的定义和初始值。它是由关 键词 DECLARE 标识的 可执行部分 包含在 PL/SQL 块中执行的所有 SQL 和非 SQL 语句 异常处理部分 包含在PL/SQL 块的过程中发生非语法错误时调用的异常处理程 序 If 语句 根据一个或一系列指定的条件,忽略 PL/SQL 块的可执行部分中 的语句的执行顺序 基本循环 在满足制定的条件之前重复执行一系列预计。在条件为 True 时 退出循环。 For 循环 使用隐含声明的计数器,使一系列语句重复执行指定的次数 While 循环 在指定的条件为 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 如果处理了一行或多行,则包含值 True——如果没有处理,则为 False %NOTFOUND 如果没有处理行,则包含值 True——如果处理了一行或多行,则为 False %ISOPEN 如果在处理之后不关闭游标,则包含值 True——如果关闭游标,则 为 False。在发生隐式游标时,由于它是自动关闭的,因此这个值在 处理之后总是 False 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 ; 5. 游标 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 True False Null True True True True False False False False False True False Null Null Null False Null 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 ;

下载文档,方便阅读与编辑

文档的实际排版效果,会与网站的显示效果略有不同!!

需要 10 金币 [ 分享文档获得金币 ] 3 人已下载

下载文档

相关文档