【网学网提醒】：网学会员为您提供天PLSQL简介参考，解决您在天PLSQL简介学习中工作中的难题，参考学习。
目标
PL/SQL是一项ORACLE的技术它可以让SQL像过程型语言一样工作到今天的结
束你将
??对PL/SQL有一个基本的了解
??明白PL/SQL的特点以及它与标准的SQL的区别
??知道PL/SQL编写程序的基本原理
??可以写简单的PL/SQL程序
??知道如何处理在PL/SQL编程中的错误
??知道如何在真实的世界中使用PL/SQL
入门
从对标准的SQL进行介绍是一种入门PL/SQL或SQL的方法SQL是一种可以让
关系数据库用户以一种直接的方式来访问数据库的方法你可以使用SQL来对数据库进行
查询和修改其中的表在你写SQL语句的时候你可以告诉数据库你要做什么而不是如
何去做优化器会自己决定采用一种最有效的方法来执行你的语句如果你向数据库中发
送了一系列标准的SQL语句服务将会按语句的先后次序来执行它们
PL/SQL是ORACLE的过程型语言它由标准的SQL语句和一系列可以让你在不同的
情况下对SQL语句的执行进行控制的命令组成PL/SQL也可以在运行时捕获错误储如
LOOP和IFTHENELSE语句让PL/SQL具有了第三代编程语言的能力PL/SQL
也可以以交互方式写出用户友好型界面会把数值赋给它的变量你可以使用许多种预定
义的包它们可以将信息显示给用户
今天的内容主要包括PL/SQL的以下特性
??程序员可以定义在语句中使用的变量
??程序员可以使用错误控制例程来访止程序的意外中断和退出
??程序员可以写交互式程序来接受来自用户的输入
SQL21日自学通(V1.0)翻译人笨猪
379
??程序员可以将功能划分为不同的逻辑代码块编程的模块化为应用程序开发环境
提供了更大的灵活性
??SQL语句可以并行执行以获得最优的性能
在PL/SQL中的数据类型
大多数数据的类型是相似的但是每一种解释器都有自己独特的存储方式和内部过程
需要在写PL/SQL的语句块时你将会定义变量它们必须是真实的数据类型下边的
小标题对PL/SQL中可用的变量进行了详细的介绍
在PL/SQL中也提供了数据类型的子类型例如数据类型NUMBER的子类型叫
INTEGER你可以在PL/SQL程序中使用子类型来保证它与其它程序如COBOL中数据类
型的一致尤其是在其它的程序中内嵌的PL/SQL代码时更要如此子类型只是ORACLE
中数据类型的别名所以它必须遵循与之相关联的数据类型的规则
注在大多数的SQL解释器中语法的大小写是不敏感的PL/SQL同样允许在它的语句中
使用大小写它也是大小写不敏感的
字符串类型
在PL/SQL中的字符串类型所你所想的一样是一种常见的数据类型定义它允许在
其中有字母和数字像名字代码描述序列号等都可以包括在字符串中
CHAR中存储着固定长度的字符串CHAR在最大长度为32767个字节尽管很难想
象在哪一个表中的字符串会有这么长
语法
CHAR(max_length)
子类型CHARACTER
VARCHAR2则存储着长度可变的字符串你会经常用VARCHAR2来存储长度可变的
字符串比如某人的名字VARCHAR2所允许的最大长度为32767个字节
语法
VARCHAR2(max_length)
子类型VARCHAR,STRING
SQL21日自学通(V1.0)翻译人笨猪
380
LONG也可以存储变长的字符串它的最大长度为32760个字节LONG是典型的用
以存储长文本如备注尽管VARCHAR2也能做同样的工作
数值数据类型
NUMBER用以存储在ORACLE数据库中的任何类型的数值
语法
NUMBER(max_length)
你也可以使用下边的语法来指定数据类型的精度
NUMBER(precision,scale)
子类型DECDECIMALDOUBLEPRECISIONINTEGERINTNUMERICREAL,
SMALLINTFLOAT
PLS_INTEGER定义的整数是可以带有符号的例如负数
二进制数据类型
二进制数据类型可以以二进制形式来存储数据如图形或图像这种数据类型包括RAW
和LONGRAW
日期数据类型
DATE是在ORACLE中可以有效存储的数据类型如果你将某一列定义为DATE类型
你就不能指定它的长度DATE数据类型的长度是默认的ORACLE数据类型是像01-
OCT-97这样的
逻辑数据类型
逻辑数据类型可以存储下列数值TRUEFALSE和NULL与DATE相类似BOOLEAN
在作为列或变量的类型在定义时也不需要参数
SQL21日自学通(V1.0)翻译人笨猪
381
ROWID
ROWID是存在于ORACLE数据库的每一个表中的预定义列ROWID以二进制格式
存储和确定表中的每一列索引就是使用ROWID指向数据的
PL/SQL块的结构
PL/SQL是一种块结构语言也就是说PL/SQL的程序可以分成逻辑块来写在一个块
的内部可以有像数据操作或查询之类的过程下边将会对PL/SQL的块进行详细的讨论
??在DECLARE部分包括了定义的变量和其它的对象如常量和指针这一部分在
PL/SQL块中是可以选择的
??PROCDURE部分包括条件语句和SQL语句块可以对它进行控制它是PL/SQL
的必须部分
??EXCEPTION告诉了PL/SQL如何处理指定的错误并按用户的定义进行处理它
也是PL/SQL的可选择部分
注块是PL/SQL代码的逻辑单元包括至少一个PROCDURE部分和可以选择的
DECLARE以及EXCEPTION部分
这里是PL/SQL块的基本结构
SYNTAX:
BEGIN--optional,denotesbeginningofblock
DECLARE--optional,variabledefinitions
BEGIN--mandatory,denotesbeginningofproceduresection
EXCEPTION--optional,denotesbeginningofexceptionsection
END--mandatory,denotesendingofproceduresection
END--optional,denotesendingofblock
请注意必要部分在第二个BEGIN——END对中的它构成了一个过程部分当然
在它们中间是有语句的如果你使用了第一个BEGIN那么你必须同时使用第二个BEGIN
反之亦然
SQL21日自学通(V1.0)翻译人笨猪
38
2
注释
如果一个程序没有注释会怎样编程语言提供了命令使你可以在你的代码中放放注
释PL/SQL当然不例外你可以在需要注释的行前加一短线公认的PL/SQL注释形式如
下多行注释则与C语言的注释方法相同
语法
--Thisisaone-linecomment.
/*Thisisa
multiple-linecomment.*/
注PL/SQL可以直接支持数据操作言语DML命令和数据查询然而它不支持数据
字典语言DDL命令你通常会使用PL/SQL维护在数据库结构中的数据但是并
不能维护这些结构
DECLARE部分
在PL/SQL的DECLARE部分包括了变量常量指针和特殊数据类型的定义作为
一个PL/SQL程序员你可以在你的代码块中定义所有类型的变量但是你必须要指定数
据类型而且每一个变量都必须与ORACLE中所定义的数据类型一致变量也要符合
ORACLE的对象命名标准
变量声明
在PL/SQL语句块中变量的数据是可变以在变量声明时必须对它加以定义如有必
须还要进行初始化工作下例中定义了一组在PL/SQL的DECLARE部分的变量
DECLARE
ownerchar(10);
tablenamechar(30);
bytesnumber(10);
todaydate
分析
DECLARE部分不能自动运行DECLARE部分以DECLARE语句开头每一个变量
SQL21日自学通(V1.0)翻译人笨猪
383
占一行注意在每一个定义的变量后边都有一个分号
在DECLARE部分也可以对变量进行初始化工作例如
DECLARE
customerchar(30);
fiscal_yearnumber(2):='97';
你可以使用=符号来进行初始化工作或者说是赋给它一个初值对于被定义为NOT
NULL的变量你必须要赋初值
DECLARE
customerchar(30);
fiscal_yearnumber(2)NOTNULL:='97';
分析
NOTNULL子句在这里的作用与它在CREATETABLE中所起的作用类似
常量定义
定义常量的方法与定义变量相同但是常量的数值是静态的他们不能改变在上个
例子中fiscal_year可能是常量
注在每一个变量的定义都必须以分号结束
指针定义
指针是PL/SQL中的另一种类型的变量在你通常所认为的变量中保存的是数值而
指针型变量则指向了查询结果中的某一行数据在查询的结果有多个时为了分析数据你
需要在每个记录之间进行翻阅在查看PL/SQL块中的查询结果时它使用指针来指向返
回的每一行下边是一个在PL/SQL语句块中定义指针的例子
INPUT
DECLARE
cursoremployee_cursoris
select*fromemployees;
指针与视图类似通过在PROCDURE部分使用LOOP循环你可以翻阅指针这
SQL21日自学通(V1.0)翻译人笨猪
384
项技术曾简要地提到过
%TYPE属性
%TYPE可以返回表中给定列的变量属性除了查看在PL/SQL中的数据类型定义代码
你可以使用%TYPE来保持在你的块中的代码的一致性
INPUT
DECLARE
cursoremployee_cursoris
selectemp_id,emp_namefromemployees;
id_numemployees.emp_id%TYPE;
nam
eemployees.emp_name%TYPE;
分析
在这个例子中表中ID_NUM变量与EMP_ID变量具有相同的数据类型%TYPE所定
义的变量NAME具有与EMPLOYEES中的emp_name具有相同的数据类型
%ROWTYPE属性
变量不仅限于单一的数值如果你所定义的变量与一个指针相关联的话你可以使用
%ROWTYPE属性来声明变量与保证它与游标所在行的类型相同在ORACLE的词典中
%ROWTYPE
INPUT
DECLARE
cursoremployee_cursoris
selectemp_id,emp_namefromemployees;
employee_recordemployee_cursor%ROWTYPE
分析
在上例中定义了一个叫employee_record的变量%ROWTYPE定义了这个变量的使它
与employee_cursor所在行的数据类型相同这个%ROWTYPE属性定义的变量也称为集合
变量
SQL21日自学通(V1.0)翻译人笨猪
385
%ROWCOUNT属性
在PL/SQL中%ROWCOUNT属性可以保证在特定的SQL语句块中的游标行数
INPUT
DECLARE
cursoremployee_cursoris
selectemp_id,emp_namefromemployees;
records_processed:=employee_cursor%ROWCOUNT;
分析
在上例中变量records_processed将会返回PL/SQL语句所访问的employee_cursor的行
数
警告在定义变量的时候要小心以访止它和表的名字相冲突例如如果你定义的变量与
你的PL/SQL语句块中所访问的表的名字是相同的那么变量的名字会优先于表的名字
Procdure部分
PROCDURE部分是PL/SQL语句块中的必须部分在这一部分将会使用变量和用户指
针来操作数据库中的数据PROCDURE部分是一个块的主要部分它包括条件语句和SQL
语句
BEGINEND
在一个语句块中BEGIN标明了一个过程的开始与此类似END则标明了一个语
句块的结束下边的例子显示了一个PROCDURE部分的基本结构
语法
BEGIN
openacursor;
condition1
statement1
condition2
statement2
SQL21日自学通(V1.0)翻译人笨猪
386
condition3
statement3
closethecursor
END
指针控制命令
现在你将学习如何在PL/SQL的语句块中定义一个指针你需要知道如何来访问一个
定义过的指针这一部分诠释了基本的指针控制命令DECLAREOPENFETCH和
CLOSE
DECLARE
在今天的早些时候你已经学习了如何在DELCARE部分定义一个指针DECLARE语
句包括下列指针控制命令
OPEN
现在你已经定义了一个指针但是你应该如何去使用它呢如果你不使用打开这本书
你无法看到它的内容的同样如果你不使用OPEN命令来打开一个指针你也将无法使用
它例如
语法
BEGIN
openemployee_cursor;
statement1;
statement2;
.
END
SQL21日自学通(V1.0)翻译人笨猪
387
FETCH
FETCH可以从一个指针中取得变量的值这里有两个使用FETCH的例子一个是取
得一个集合变量另外了个则取得特定的变量
INPUT
DECLARE
cursoremployee_cursoris
selectemp_id,emp_namefromemployees;
employee_recordemployee_cursor%ROWTYPE;
BEGIN
openemp
loyee_cursor;
loop
fetchemployee_cursorintoemployee_record;
endloop;
closeemployee_cursor;
END
分析
上一个例子中将一个指针所指的行的数据赋给了一个名字叫employee_record的变量
它使用LOOP循环来移动指针当然这个块事实上没有做任何工作
DECLARE
cursoremployee_cursoris
selectemp_id,emp_namefromemployees;
id_numemployees.emp_id%TYPE;
nameemployees.emp_name%TYPE;
BEGIN
openemployee_cursor;
loop
fetchemployee_cursorintoid_num,name;
endloop;
closeemployee_cursor;
SQL21日自学通(V1.0)翻译人笨猪
388
END
分析
在这个例子中是把当前的指针所指的行的对应数据填入变量id_num和name中这两
个变量是在DECLARE部分中定义的
CLOSE
如果你已经在一个块中结束了对指针的使用时你应该将这个指针关闭与你通常在
读完一本书以后要将书合上一样你应该使用CLOSE命令来关闭指针
语法
BEGIN
openemployee_cursor;
statement1;
statement2;
.
.closeemployee_cursor;
END
分析
当关闭一个指针以后查询的结果集就不复存在了如果你想访问结果集中的数据你
必须重新打开指针才行
条件语句
现在我们得到了非常有用的东西来控制我们的SQL语句的运行在PL/SQL中的条件
语句与大多数第三代编程语言是类似的
IFTHEN
在大多数编程中IFTHEN语句可能是得常用的语句了它决定了对于特定的条件
应当执行哪一部分的操作其结构如下
语法
SQL21日自学通(V1.0)翻译人笨猪
389
IFcondition1THEN
statement1;
ENDIF
如果你需要对这两种情况进行分别处理那么你可以将语句形式写成下边的样子
语法
IFcondition1THEN
statement1
ELSE
statement2
ENDIF
如果你需要进行判断的条件多于两个那么语句可以写成下边样子
语法
IFcondition1THEN
statement1
ELSIFcondition2THEN
statement2
ELSE
statement3
ENDIF
分析
在最后一个例子中如果满足条件1那么就执行语句1如果满足条件2那么就会
执行语句2否则的话就会执行语句3条件语句也可以嵌于其他语句和LOOP循环中
LOOPS循环
LOOPS在PL/SQL的语句块中将不断地执行过程直到指定的条件满足为止一共有三
种循环
LOOP本身是一个无限的循环它经常在指针中使用如果你想终止这种循环你必
须指定在什么时候退出例如在循环中翻阅指针的时候你可以指定当指针处于最后一行
的时候退出循环见下例
SQL21日自学通(V1.0)翻译人笨猪
390
输入
BEGIN
openemployee_cursor
LOOP
FETCHemployee_cursorintoemployee_record
EXITWHENemployee_cursor%NOTFOUND
statement1
ENDLOOP
closeemployee_cursor
END
%NOTFOUND是指针的一种属性它表明在当前指针中没有任何数据在上一个例
子中如果指针没有发现数据就会退出循环假如你在循环中忽略了这条语句循环将会
一
直进行下去
WHILE-LOOP则是在当条定条件满足时执行特定的语句而当条件不在满足时就会从
循环中退出转而执行下一条语句
输入
DECLARE
cursorpayment_cursoris
selectcust_id,payment,total_duefrompayment_table
cust_idpayment_table.cust_id%TYPE
paymentpayment_table.payment%TYPE
total_duepayment_table.total_due%TYPE
BEGIN
openpayment_cursor
WHILEpayment
FETCHpayment_cursorintocust_id,payment,total_due
EXITWHENpayment_cursor%NOTFOUND
insertintounderpay_table
values(cust_id,'STILLOWES')
SQL21日自学通(V1.0)翻译人笨猪
391
ENDLOOP
closepayment_cursor
分析
在上一个例子中使用了WHILE-LOOP来对指针进行翻阅并且在当条件
payment 在上一个例子中你也可以使用FOR-LOOP来限定当前的指针所指定的行的数值处于已
给定的数值内
输入
DECLARE
cursorpayment_cursoris
selectcust_id,payment,total_duefrompayment_table
cust_idpayment_table.cust_id%TYPE
paymentpayment_table.payment%TYPE
total_duepayment_table.total_due%TYPE
BEGIN
openpayment_cursor
FORpay_recINpayment_cursorLOOP
IFpay_rec.payment insertintounderpay_table
values(pay_rec.cust_id,'STILLOWES')
ENDIF
ENDLOOP
closepayment_cursor
END
分析
在这个例子中使用了FOR-LOOP翻阅指针它是默认的FETCH下执行该语句被省
略了而且在这里%NOTFOUND属性也被省略了该属性在FOR-LOOP循环中也是默认
的所以这个例子与上一个例子的结果将是相同的
SQL21日自学通(V1.0)翻译人笨猪
392
EXCEPTION部分
在PL/SQL语句块中这一部分是可以选择的如果在这一部分被省略而遇到异常的时
候该语句块就会终止了由于一些异常在出现时也许不需要将语句块终止所以
EXCEPTION可以用一定的方式来捕获指定的异常或用户定义的异常异常可以由用户来
定义尽管许多的异常已经在ORACLE中进行了预定义
激活EXCEPTION异常
在语句块中的异常可以由RAISE语句来激活异常可以由程序员进行准确地激活然
而当数据库产生内部错误时它会被自动激活或由默认的数据库服务来调用
语法
BEGIN
DECLARE
exception_nameEXCEPTION
BEGIN
IFconditionTHEN
RAISEexception_name
ENDIF
EXCEPTION
WHENexception_nameTHEN
Statement
END
END
分析
这个例子给出了对异常进行准确激活的基本方法首先exception_name要在
EXCEPTION语句中进行了定义在PROCEDURE部分当给定的条件满足时这个异常就
由RAISE语句激活了然后RAISE语句将会引用EXCEPTION语句中的对应异常部分以
进行适当的工作
SQL21日自学通(V1.0)翻译人笨猪
393
异常的处理
在上例中捕获了在EXCEPTION部分中的一个异常在PL/SQL中错误是非常容易捕
获的在进行了异常处理以后PL/SQL可以在
错误状态下继续运行或是以下种合理的方
式来终止
语法
EXCEPTION
WHENexception1THEN
statement1
WHENexception2THEN
statement2
WHENOTHERSTHEN
statement3
分析
这个例子告诉了你当你有多于一个的异常时应该如何在EXCEPTION部分中进行安
排在这个例子中的块在运行时有两个预料中的异常exception1和exception2如果在
该语句中产生了其他的异常就会调用WHENOTHERS它对你的语句块中的其它的错误进
行了处理
运行一个PL/SQL语句块
PL/SQL通常在一个主机的编辑器上创建而它的运行则与常规的SQL脚本文件一样
PL/SQL在语句块中的每一条语句——从变量的赋值到数据操作命令——使用分号结束
在SQL的脚本中正斜线/但是在PL/SQL也使用正斜线来表明脚本的结束最为
容易的运行PL/SQL语句块的方法是使用START命令或简写为STA或@
你的PL/SQL脚本可能会像下边这样
语法
/*Thisfileiscalledproc1.sql*/
BEGIN
DECLARE
...
BEGIN
SQL21日自学通(V1.0)翻译人笨猪
394
...
statements;
...
EXCEPTION
...
END;
END;
/
你可以像下边这样执行PL/SQL脚本
SQL>startproc1or
SQL>staproc1or
SQL>@proc1
注PL/SQL脚本语言可以使用START命令或@字符来运行它也可以被其它的PL/SQL
脚本SHELL脚本或其它的程序调用
将输入返回给用户
尤其是在捕获了错误的时候你可以会希望输出信息给用户告诉他出现了什么错误
你可以转送已有的错误信息你也可以显示你所定制的错误信息对于用户来说这会与错
误代码更容易理解也许你想的是当在错误产生时让他们与数据库管理员联系而不是给他
们尽可以准确的信息
PL/SQL在它的语法部分中并没有提供直接的方式来显示输出但是它可以让你来调
用一个对该语句块服务的包这个包是由DBMS_OUTPUT来调用的
EXCEPTION
WHENzero_divideTHEN
DBMS_OUTPUT.put_line('ERROR:DIVISORISZERO.SEEYOURDBA.')
分析
ZERO_DIVIDE是ORACLE的一个预定义的异常有许多在程序运行中产生的常见的
错误都被预定义为异常并且可以被默认地激活也就是说你不必在编程的过程中手动将其
激活
SQL21日自学通(V1.0)翻译人笨猪
395
如果在这个语句块运行的过程中产生的异常用户将会看到
INPUT
SQL>@block1
ERROR:DIVISORISZERO.SEEYOURDBA.
PL/SQLproceduresuccessfullycompleted.
是不是这样的错误信息比下边的错误信息更友好
输入/输出
SQL>@block1
begin
*
ERRORatline1:
ORA-01476:divisorisequaltozero
ORA-06512:atline20
在PL/SQL中的事务控制
在第11天中的事务控制中我们已经讨论了事务控制命令COMMITROLLBACK
SAVEPOINT这些命令可以让程序员在在事务向数据库中进行写操作时加以控制在多数
时候所进行的操作是需要撤消
语法
BEGIN
DECL
ARE
...
BEGIN
statements...
IFconditionTHEN
COMMIT;
ELSE
ROLLBACK;
ENDIF;
SQL21日自学通(V1.0)翻译人笨猪
396
...
EXCEPTION
...
END;
END;
PL/SQL的一个好处就是你可以用自动地执行事务控制命令来代替对大型事务的不断
监控——这是非常单调和乏味的
让所有的事在一起工作
到目前为止我们已经介绍了PL/SQL你已经熟悉了它所支持的数据类型以及PL/SQL
语句块的主要特性你已经知道了如何定义一个局部变量常量和指针你也已经知道了
如何在一个PROCDURE部分指针的操作部分和异常部分嵌入SQL语句当指针在使用
时你应该清楚地知道如何在异常部分捕获它现在你已经可以使用BEGINEND语
句块来进行了实际工作在今天的结束部分你将会彻底明白PL/SQL语句块之间的相同
关系
示例表及数据
在我们创建的PL/SQL语句块中使用两个表PAYMENT_TABLE确定了一个客户她
/他的付款是多少应得的总数是多少PAY_STATUS_TABLE最初实际上没有任何数据
数据将会依据PAYMENT_TABLE中的特定条件插入到PAY_STATUS_TABLE表中
输入
SQL>select*frompayment_table;
输出
CUSTOMERPAYMENTTOTAL_DUE
ABC90.50150.99
AAA79.0079.00
BBB950.001000.00
CCC27.5027.50
DDD350.00500.95
EEE67.8967.89
SQL21日自学通(V1.0)翻译人笨猪
397
FFF555.55455.55
GGG122.36122.36
HHH26.750.00
输入
SQL>describepay_status_table
输出
NameNull?Type
CUST_IDNOTNULLCHAR(3)
STATUSNOTNULLVARCHAR2(15)
AMT_OWEDNUMBER(8,2)
AMT_CREDITNUMBER(8,2)
分析
DESCRIBE是一个ORACLESQL它可以不通过查询数据字典就可以显示一个表的结
构它与其它的ORACLESQL*PLUS命令将会在第20天SQL*PLUS中提到
一个简单的PL/SQL语句块
这个PL/SQL的脚本内容如下所示
输入
setserveroutputon
BEGIN
DECLARE
AmtZeroEXCEPTION;
cCustIdpayment_table.cust_id%TYPE;
fPaymentpayment_table.payment%TYPE;
fTotalDuepayment_table.total_due%TYPE;
cursorpayment_cursoris
selectcust_id,payment,total_due
frompayment_table;
fOverPaidnumber(8,2);
fUnderPaidnumber(8,2);
BEGIN
SQL21日自学通(V1.0)翻译人笨猪
398
openpayment_cursor;
loop
fetchpayment_cursorinto
cCustId,fPayment,fTotalDue;
exitwhenpayment_cursor%NOTFOUND;
if(fTotalDue=0)then
raiseAmtZero;
endif;
if(fPayment>fTotalDue)then
fOverPaid:=fPayment-fTotalDue;
insertintopay_status_table(cust_id,status,amt_credit)
values(cCustId,'OverPaid',fOverPaid);
elsif(fPayment fUnderPaid:=fTotalDue-fPayment;
insertintopay_status_table(cust_id,status,amt_owed)
values(cCustId,'StillOwes',fUnderPaid);
else
insertintopay_status_table
values(cCustId,'PaidinFull',null,null);
endif;
endloop;
closepayment_cursor;
EXCEPTION
whenAmtZerothen
DBMS_OUTPUT.put_line('ERROR:amountisZero.Seeyoursupervis
or.');
whenOTHERSthen
DBMS_OUTPUT.put_line('ERROR:unknownerror.SeetheDBA');
END;
END;
/
SQL21日自学通(V1.0)翻译人笨猪
399
分析
DECLARE部分定义了六个局部变量与被称为payment_cursor指针一样PROCEDURE
从第二个BEGIN语句开始并先打开了这个游标并开始循环而FETCH命令则将当前指针
所指向的记录的内容存入在DECLARE部分定义的变量中当在指针中发现记录以后语
句会将客户的支付与他应付的总数进行比较根据支付的的数量来计算已付款的人数和未
付款的人数然后将计算过的数据插入到PAY_STATUS_TABLE表中当循环终止以后
关闭指针异常用于处理在这一部分可以发生的错误
现在我们来运行一个这个脚本看一个他的结果
INPUT:
SQL>@block1
OUTPUT:
Inputtruncatedto1characters
ERROR:amountisZero.Seeyoursupervisor.
PL/SQLproceduresuccessfullycompleted.
现在看来你在应得总数上有一个数值是不正确的你应该修正这个数量然后再运行脚
本
输入/输出
SQL>updatepayment_tablesettotal_due=26.75wherecust_id='HHH';
1rowupdated.
SQL>commit;
Commitcomplete.
SQL>truncatetablepay_status_table;
Tabletruncated.
注在上例中我们清除了pay_status_table中的内容在下一次运行这个语句块时表将会被
重新写入你也许会想把清除语句也加入到语句块中吧
输入/输出
SQL>@block1
Inputtruncatedto1characters
PL/SQLproceduresuccessfullycompleted.
SQL21日自学通(V1.0)翻译人笨猪
400
现在你可以对PAY_STATUS_TABLE表执行SELECT语句看一下每一个客户的支付
情况
输入/输出
SQL>select*frompay_status_tableorderbystatus;
CUSTOMERSTATUSAMT_OWEDAMT_CREDIT
FFFOverPaid100.00
AAAPaidinFull
CCCPaidinFull
EEEPaidinFull
GGGPaidinFull
HHHPaidinFull
ABCStillOwes60.49
DDDStillOwes150.95
BBBStillOwes50.00
分析
从PAYMENT_TABLE获得的数据中有一行被插入到了PAY_STATUS_TABLE表中
如果用户的付款金额比应付金额更多那么差额将会被输入到AMT_CREDIT列中如果
用户的支付金额少于应付金额那么将会在AMT_OWED中输入一个信息如果客户已经
全部支付完毕那么这两列中就不会有任何数据
又一个程序
在这个例子中使用的表叫PAY_TABLE
输入
SQL>descpay_table
输出
NameNull?Type
NAMENOTNULLVARCHAR2(20)
PAY_TYPENOTNULLVARCHAR2(8)
PAY_RATENOTNULLNUMBER(8,2)
EFF_DATENOTNULLDATE
PREV_PAYNUMBER(8,2)
先来看一下数据
SQL21日自学通(V1.0)翻译人笨猪
401
输入
SQL>select*frompay_tableorderbypay_type,pay_ratedesc;
输出
NAMEPAY_TYPEPAY_RATEEFF_DATEPREV_PAY
SANDRASAMUELSHOURLY12.5001-JAN-97
ROBERTBOBAYHOURLY11.5015-MAY-96
KEITHJONESHOURLY10.0031-OCT-96
SUSANWILLIAMSHOURLY9.7501-MAY-97
CHRISSYZOESSALARY50000.0001-JAN-97
CLODEEVANSSALARY42150.0001-MAR-97
JO
HNSMITHSALARY35000.0015-JUN-96
KEVINTROLLBERGSALARY27500.0015-JUN-96
现实情况由于销售情况很好你需要给为你工作的时间超过了六个月的个人增加薪
金符合条件的钟点工的薪金增加4%而符全条件的雇员的薪金需要增加5%
今天的日期是
输入/输出
SQL>selectsysdatefromdual
SYSDATE
20-MAY-97
在对下边的PL/SQL语句块进行检查之前我们要对PAY_TABLE表进行手工的选择
以找出都有哪些人需要增加薪金
输入
SQL>selectname,pay_type,pay_rate,eff_date,
2'YES'due
3frompay_table
4whereeff_date 5UNIONALL
6selectname,pay_type,pay_rate,eff_date,
7'No'due
8frompay_table
9whereeff_date>=sysdate-180
SQL21日自学通(V1.0)翻译人笨猪
402
10orderby2,3desc;
输出
NAMEPAY_TYPEPAY_RATEEFF_DATEDUE
SANDRASAMUELSHOURLY12.5001-JAN-97No
ROBERTBOBAYHOURLY11.5015-MAY-96YES
KEITHJONESHOURLY10.0031-OCT-96YES
SUSANWILLIAMSHOURLY9.7501-MAY-97No
CHRISSYZOESSALARY50000.0001-JAN-97No
CLODEEVANSSALARY42150.0001-MAR-97No
JOHNSMITHSALARY35000.0015-JUN-96YES
KEVINTROLLBERGSALARY27500.0015-JUN-96YES
DUE列的内容是确定每一个人是否有增加工资的资格下边是PL/SQL的脚本
输入
setserveroutputon
BEGIN
DECLARE
UnknownPayTypeexception;
cursorpay_cursoris
selectname,pay_type,pay_rate,eff_date,
sysdate,rowid
frompay_table;
IndRecpay_cursor%ROWTYPE;
cOldDatedate;
fNewPaynumber(8,2);
BEGIN
openpay_cursor;
loop
fetchpay_cursorintoIndRec;
exitwhenpay_cursor%NOTFOUND;
cOldDate:=sysdate-180;
if(IndRec.pay_type='SALARY')then
SQL21日自学通(V1.0)翻译人笨猪
403
fNewPay:=IndRec.pay_rate*1.05;
elsif(IndRec.pay_type='HOURLY')then
fNewPay:=IndRec.pay_rate*1.04;
else
raiseUnknownPayType;
endif;
if(IndRec.eff_date updatepay_table
setpay_rate=fNewPay,
prev_pay=IndRec.pay_rate,
eff_date=IndRec.sysdate
whererowid=IndRec.rowid;
commit;
endif;
endloop;
closepay_cursor;
EXCEPTION
whenUnknownPayTypethen
dbms_output.put_line('=======================');
dbms_output.put_line('ERROR:Abortingprogram.');
dbms_output.put_line('UnknownPayTypeforName');
whenothersthen
dbms_output.put_line('ERRORDuringProcessing.SeetheDBA.');
END;
END;
/
你是否已经决定了要给这四个雇员增加工资在上边的SELECT语句中有四个人有
YES标记为什么不呢让我们给所有的这四个人加薪吧你可以通过运行名字叫
block2.sql的脚本来自动为这四个人进行合理的加薪
输入/输出
SQL21日自学通(V1.0)翻译人笨猪
404
SQL>@block2
Inputtruncatedto1characters
PL/SQLproceduresuccessfullycompleted.
你可以作一个快速的检查也确定对于每个人的薪金的增加比率是多少
输入
SQL>select*frompay_tableorderbypay_type,pay_ratedesc
输出
NAMEPAY_T
YPEPAY_RATEEFF_DATEPREV_PAY
SANDRASAMUELSHOURLY12.5001-JAN-97
ROBERTBOBAYHOURLY11.9620-MAY-9711.5
KEITHJONESHOURLY10.4020-MAY-9710
SUSANWILLIAMSHOURLY9.7501-MAY-97
CHRISSYZOESSALARY50000.0001-JAN-97
CLODEEVANSSALARY42150.0001-MAR-97
JOHNSMITHSALARY36750.0020-MAY-9735000
KEVINTROLLBERGSALARY28875.0020-MAY-9727500
分析
四个雇员的薪金已经增加了如果将现在的输出和原来的SELECT的输出做比较的话
你会发现相应的改变当前的薪金率的变化反映和薪金的增加原有的薪金率被插入到了
PREV_PAY列中而有效日期则被更新为当前的日期没有符合资格的人的情况则没有任
何变化
请等一下我们没有看到定义的异常工作的机会你可以向PAY_TABLE表中插入一
个不合法的记录来对异常部分进行检测
输入
SQL>insertintopay_tablevalues
2('JEFFJENNINGS','WEEKLY',71.50,'01-JAN-97',NULL);
输出
1rowcreated.
输入/输出
SQL>@block2
SQL21日自学通(V1.0)翻译人笨猪
405
Inputtruncatedto1characters
=======================
ERROR:Abortingprogram.
UnknownPayTypefor:JEFFJENNINGS
PL/SQLproceduresuccessfullycompleted.
分析
错误信息表明JEFFJENNINGS的薪金支付方式不是HOURLY和SALARY这就是异
常所捕获到的错误信息
存储过程包和触发机制
使用PL/SQL你可以创建存储对象来代替日复一日的输入单调和枯燥的代码过程
是一些可以执行一些特定类型的存储工作的代码块相关的过程可以组合和存储在一起
这称为包触发机制是一种在其它的事务中使用的数据库对象你也许对一个叫ORDERS
的表建立了一个触发机制以使得每次当ORDERS表接受到数据时都向HISTORY表中插入
数据这些对象的基本语法如下
过程示例
语法
PROCEDUREprocedure_nameIS
variable1datatype;
...
BEGIN
statement1;
...
EXCEPTION
when...
ENDprocedure_name;
SQL21日自学通(V1.0)翻译人笨猪
406
示例包
语法
CREATEPACKAGEpackage_nameAS
PROCEDUREprocedure1(global_variable1datatype,...);
PROCEDUREprocedure2(global_variable1datatype,...);
ENDpackage_name;
CREATEPACKAGEBODYpackage_nameAS
PROCEDUREprocedure1(global_variable1datatype,...)IS
BEGIN
statement1;
...
ENDprocedure1;
PROCEDUREprocedure2(global_variable1datatype,...)IS
BEGIN
statement1;
...
ENDprocedure2;
ENDpackage_name;
示例触发机制
SYNTAX:
CREATETRIGGERtrigger_name
AFTERUPDATEOFcolumnONtable_name
FOREACHROW
BEGIN
statement1;
...
END;
SQL21日自学通(V1.0)翻译人笨猪
407
下边的例子在当对PAY_TABLE表的数据进行更新时使用触发机制向一个事务表中插
入数据事务表如下所示
INPUT:
SQL>describetrans_table
OUTPUT:
NameNull?Type
ACTION(10)VARCHAR2
NAMEVARCHAR2(20)
PREV_PAYNUMBER(8,2)
CURR_PAYNUMBER(8,2)
EFF_DATEDATE
示例行的数据如下
输入/
输出
SQL>select*frompay_tablewherename='JEFFJENNINGS';
NAMEPAY_TYPEPAY_RATEEFF_DATEPREV_PAY
JEFFJENNINGSWEEKLY71.5001-JAN-97
现在创建一个触发机制
SQL>CREATETRIGGERpay_trigger
2AFTERupdateonPAY_TABLE
3FOREACHROW
4BEGIN
5insertintotrans_tablevalues
6('PAYCHANGE',:new.name,:old.pay_rate,
7:new.pay_rate,:new.eff_date);
8END;
9/
然后对PAY_TABLE进行更新操作这会导致触发机制的运行
输入/输出
SQL>updatepay_table
2setpay_rate=15.50,
3eff_date=sysdate
SQL21日自学通(V1.0)翻译人笨猪
408
4wherename='JEFFJENNINGS';
SQL>select*frompay_tablewherename='JEFFJENNINGS';
NAMEPAY_TYPEPAY_RATEEFF_DATEPREV_PAY
JEFFJENNINGSWEEKLY15.5020-MAY-97
SQL>select*fromtrans_table
ACTIONNAMEPREV_PAYCURR_PAYEFF_DATE
PAYCHANGEJEFFJENNINGS71.515.520-MAY-97
分析
在PAY_TABE表中的PREV_PAY中是空的但是在TRANS_TABLE中则存在数值
你是不是糊涂了PAY_TABLE是不需要PREV_PAY的因为每小时的薪金为71.5在这里
很明显是一个错误的数值由于更新操作是一个事务所以我们把PREV_PAY的数值插入
到了TRANS_TABLE表中它的目的是为所以的用PAY_TABLE工作的表保存记录
注如果你工作在类似的网络环境中你也许会注意到PL/SQL与JAVA的存储过程有一
些类似但是你要注意到他们的不同之处PL/SQL是对标准的SQL的增强它是
一种过程型语言JAVA比它有更多的先进的特性它允许程序写出比PL/SQL更为复
杂的程序PL/SQL是基于指定的数据库的增强型SQL而JAVA则在CPU级上工作
的程序大多数的过程型语言如PL/SQL是针对特定的平台开发的而JAVA则比过
程型语言更高级它可以在交叉的平台上工作并可以实现标准化
总结
PL/SQL对标准的SQL进行了扩展PL/SQL所执行的基本功能与第三代语言相同
它可以使用局部变量来支持动态代码也就是说块内的数值可以根据用户的输入指定的
条件和指针的内容的变化而变化PL/SQL使用标准的过程语言来对语句进行控制IF
THEN和LOOP可以让你按指定的条件搜索你也可以使用LOOP来对指定的指针的内容
进行翻阅
在任何程序中都会有各种错误产生PL/SQL通过异常可以让你对产生错误后的行为
进行控制许多异常是预定义过的如被零除错误异常可以在程序运行时根据指定的条
件激活并按程序员所定义的方式进行处理
在今天也介绍一些对PL/SQL的实际应用数据库对象如触发机制存储过程包可
以自动完成许多功能在今天的例子中我们也应用了一些在前一天中所提到的概念
SQL21日自学通(V1.0)翻译人笨猪
409
问与答
问在第18天中我是否已经学习了我需要对PL/SQL所掌握的所有内容
答当然不是像今天的介绍只是提及到了一些表层的一些与SQL相关的东
西我们
只是提及了一些SQL的非常明显的特性使你对PL/SQL有一个基本的了解
问我不用PL/SQL行不行
答当然你不使用它也是可以的但是如果你不使用它你会为达到相同的目的而不
得不在第三代编程语言中使用更多的时间和代码如果你没有使用ORACLE那
么请检查你的解释器以找到与PL/SQL类似的过程方法
校练场
1如何在数据库中使用触发机制
2是否可以将相关的过程存储在一起
3可以在PL/SQL中使用数据操作语言对不对
4可以在PL/SQL中使用数据定义语言对不对
5在PL/SQL的语法中是否支持直接的文本输出
6给出PL/SQL语句块的三个主要部分
7请给出与指针控制相关的命令
练习
1请定义一个变量使它可以接受的最大数值为99.99
2请定义一个指针它的内容包括CUSTOMER_TABLE表中的所有CITY为
INDIANAPOLIS的客户
3定义一个名字为UnknownCode的异常
4请写一个语句使得在AMOUNT_TABLE中的AMT当CODE为A时其值为10
当CODE为B时其值为20当CODE既不是A也不是B时激活一个名字叫
UnknownCode的异常表中的内容只有一行
SQL21日自学通(V1.0)翻译人笨猪
