PLSQL 编程详解 系列PLSQL1.docx
《PLSQL 编程详解 系列PLSQL1.docx》由会员分享，可在线阅读，更多相关《PLSQL 编程详解 系列PLSQL1.docx（134页珍藏版）》请在冰豆网上搜索。
PLSQL编程详解系列PLSQL1
PL/SQL程序设计
目录
第一章PL/SQL程序设计简介4
§1.2SQL与PL/SQL4
§1.2.1什么是PL/SQL?
4
§1.2.1PL/SQL的好处4
§1.2.2PL/SQL可用的SQL语句5
§1.3运行PL/SQL程序5
第二章PL/SQL块结构和组成元素6
§2.1PL/SQL块6
§2.2PL/SQL结构6
§2.3标识符6
§2.4PL/SQL变量类型7
§2.4.1变量类型7
§2.4.2复合类型9
§2.4.3使用%ROWTYPE11
§2.4.4LOB类型*11
§2.4.5BIND变量11
§2.4.6INDEXBYTABLES12
§2.4.7数据类型的转换*13
§2.5运算符和表达式（数据定义）13
§2.5.1关系运算符13
§2.5.2一般运算符13
§2.5.3逻辑运算符14
§2.6变量赋值14
§2.6.1字符及数字运算特点14
§2.6.2BOOLEAN赋值14
§2.6.3数据库赋值14
§2.6.4可转换的类型赋值15
§2.7变量作用范围及可见性15
§2.8注释16
§2.9简单例子17
§2.9.1简单数据插入例子17
§2.9.2简单数据删除例子17
第三章PL/SQL流程控制语句18
§3.1条件语句18
§3.2CASE表达式19
§3.3循环19
§3.3标号和GOTO21
§3.4NULL语句21
第四章游标的使用23
§4.1游标概念23
§4.1.1处理显式游标23
§4.1.2处理隐式游标27
§4.1.3关于NO_DATA_FOUND和%NOTFOUND的区别27
§4.1.4游标修改和删除操作27
§4.2游标变量28
§4.2.1声明游标变量28
§4.2.2游标变量操作29
§4.2.3游标变量应用30
第五章异常错误处理31
§5.1异常处理概念31
§5.1.1预定义的异常处理31
§5.1.2非预定义的异常处理32
§5.1.3用户自定义的异常处理33
§5.1.4用户定义的异常处理34
§5.2异常错误传播35
§5.2.1在执行部分引发异常错误36
§5.2.2在声明部分引发异常错误36
§5.3异常错误处理编程37
§5.4在PL/SQL中使用SQLCODE,SQLERRM37
第六章存储函数和过程39
§6.1引言39
§6.2创建函数39
§6.3存储过程42
§6.3.1创建过程42
§6.3.2调用存储过程44
§6.3.3开发存储过程步骤46
§6.3.4与过程相关数据字典47
第七章包的创建和应用48
§7.1引言48
§7.2包的定义48
§7.3包的开发步骤49
§7.4包定义的说明49
§7.5子程序重载57
§7.6删除过程、函数和包59
§7.7包的管理59
第八章触发器60
§8.1触发器类型60
§8.1.1DML触发器60
§8.1.2替代触发器60
§8.1.3系统触发器60
§8.2创建触发器61
§8.2.1触发器触发次序62
§8.2.2创建DML触发器62
§8.2.3创建替代（INSTEADOF）触发器63
§8.2.3创建系统事件触发器64
§8.2.4系统触发器事件属性65
§8.2.5使用触发器谓词66
§8.2.6重新编译触发器66
§8.3删除和使能触发器66
§8.4触发器和数据字典67
§8.5数据库触发器的应用举例67
§8.6数据库触发器的应用实例77
第九章ORACLE提供的常用包81
§9.1使用DBMS_SQL软件包81
§9.2使用EXECUTEIMMEDIATE语句82
§9.3使用DBMS_DDL软件包83
§9.4使用DBMS_JOB软件包编制工作调度84
§9.5使用DBMS_OUTPUT软件包86
§9.6使用UTL_FILE软件包87
第一章
PL/SQL程序设计简介
PL/SQL是一种高级数据库程序设计语言，该语言专门用于在各种环境下对ORACLE数据库进行访问。
由于该语言集成于数据库服务器中，所以PL/SQL代码可以对数据进行快速高效的处理。
除此之外，可以在ORACLE数据库的某些客户端工具中，使用PL/SQL语言也是该语言的一个特点。
本章的主要内容是讨论引入PL/SQL语言的必要性和该语言的主要特点，以及了解PL/SQL语言的重要性和数据库版本问题。
还要介绍一些贯穿全书的更详细的高级概念，并在本章的最后就我们在本书案例中使用的数据库表的若干约定做一说明。
本章主要重点：
●PL/SQL概述
●PL/SQL块结构
●PL/SQL流程
●运算符和表达式
●游标
●异常处理
●数据库存储过程和函数
●包
●触发器
§1.2SQL与PL/SQL
§1.2.1什么是PL/SQL?
PL/SQL是ProcedureLanguage&StructuredQueryLanguage的缩写。
ORACLE的SQL是支持ANSI（AmericannationalStandardsInstitute）和ISO92（InternationalStandardsOrganization）标准的产品。
PL/SQL是对SQL语言存储过程语言的扩展。
从ORACLE6以后，ORACLE的RDBMS附带了PL/SQL。
它现在已经成为一种过程处理语言，简称PL/SQL。
目前的PL/SQL包括两部分，一部分是数据库引擎部分；另一部分是可嵌入到许多产品（如C语言，JAVA语言等）工具中的独立引擎。
可以将这两部分称为：
数据库PL/SQL和工具PL/SQL。
两者的编程非常相似。
都具有编程结构、语法和逻辑机制。
工具PL/SQL另外还增加了用于支持工具（如ORACLEForms）的句法，如：
在窗体上设置按钮等。
本章主要介绍数据库PL/SQL内容。
§1.2.1PL/SQL的好处
§1.2.1.1有利于客户/服务器环境应用的运行
对于客户/服务器环境来说，真正的瓶颈是网络上。
无论网络多快，只要客户端与服务器进行大量的数据交换。
应用运行的效率自然就回受到影响。
如果使用PL/SQL进行编程，将这种具有大量数据处理的应用放在服务器端来执行。
自然就省去了数据在网上的传输时间。
§1.2.1.2适合于客户环境
PL/SQL由于分为数据库PL/SQL部分和工具PL/SQL。
对于客户端来说，PL/SQL可以嵌套到相应的工具中，客户端程序可以执行本地包含PL/SQL部分，也可以向服务发SQL命令或激活服务器端的PL/SQL程序运行。
§1.2.2PL/SQL可用的SQL语句
PL/SQL是ORACLE系统的核心语言，现在ORACLE的许多部件都是由PL/SQL写成。
在PL/SQL中可以使用的SQL语句有：
INSERT，UPDATE，DELETE，SELECTINTO，COMMIT，ROLLBACK，SAVEPOINT。
提示：
在PL/SQL中只能用SQL语句中的DML部分，不能用DDL部分，如果要在PL/SQL中使用DDL（如CREATEtable等）的话，只能以动态的方式来使用。
●ORACLE的PL/SQL组件在对PL/SQL程序进行解释时，同时对在其所使用的表名、列名及数据类型进行检查。
●PL/SQL可以在SQL*PLUS中使用。
●PL/SQL可以在高级语言中使用。
●PL/SQL可以在ORACLE的开发工具中使用。
●其它开发工具也可以调用PL/SQL编写的过程和函数，如PowerBuilder等都可以调用服务器端的PL/SQL过程。
§1.3运行PL/SQL程序
PL/SQL程序的运行是通过ORACLE中的一个引擎来进行的。
这个引擎可能在ORACLE的服务器端，也可能在ORACLE应用开发的客户端。
引擎执行PL/SQL中的过程性语句，然后将SQL语句发送给数据库服务器来执行。
再将结果返回给执行端。
第二章PL/SQL块结构和组成元素
§2.1PL/SQL块
PL/SQL程序由三个块组成，即声明部分、执行部分、异常处理部分。
PL/SQL块的结构如下：
DECLARE
/*声明部分:
在此声明PL/SQL用到的变量,类型及游标，以及局部的存储过程和函数*/
BEGIN
/*执行部分:
过程及SQL语句,即程序的主要部分*/
EXCEPTION
/*执行异常部分:
错误处理*/
END;
其中执行部分是必须的。
PL/SQL块可以分为三类：
1.无名块：
动态构造，只能执行一次。
2.子程序：
存储在数据库中的存储过程、函数及包等。
当在数据库上建立好后可以在其它程序中调用它们。
3.触发器：
当数据库发生操作时，会触发一些事件，从而自动执行相应的程序。
§2.2PL/SQL结构
●PL/SQL块中可以包含子块；
●子块可以位于PL/SQL中的任何部分；
●子块也即PL/SQL中的一条命令；
§2.3标识符
PL/SQL程序设计中的标识符定义与SQL的标识符定义的要求相同。
要求和限制有：
●标识符名不能超过30字符；
●第一个字符必须为字母；
●不分大小写；
●不能用’-‘（减号）;
●不能是SQL保留字。
提示:
一般不要把变量名声明与表中字段名完全一样,如果这样可能得到不正确的结果.
例如：
下面的例子将会删除所有的纪录，而不是KING的记录；
DECLARE
Enamevarchar2（20）:
=’KING’;
BEGIN
DELETEFROMempWHEREename=ename;
END;
变量命名在PL/SQL中有特别的讲究，建议在系统的设计阶段就要求所有编程人员共同遵守一定的要求，使得整个系统的文档在规范上达到要求。
下面是建议的命名方法：
标识符
命名规则
例子
程序变量
V_name
V_name
程序常量
C_Name
C_company_name
游标变量
Name_cursor
Emp_cursor
异常标识
E_name
E_too_many
表类型
Name_table_type
Emp_record_type
表
Name_table
Emp
记录类型
Name_record
Emp_record
SQL*Plus替代变量
P_name
P_sal
绑定变量
G_name
G_year_sal
§2.4PL/SQL变量类型
在前面的介绍中，有系统的数据类型，也可以自定义数据类型。
下表是ORACLE类型和PL/SQL中的变量类型的合法使用列表：
§2.4.1变量类型
在ORACLE8i中可以使用的变量类型有：
类型
子类
说明
范围
ORACLE限制
CHAR
Character
String
Rowid
Nchar
定长字符串
民族语言字符集
032767
可选,确省=1
2000
VARCHAR2
Varchar,String
NVARCHAR2
可变字符串
民族语言字符集
032767
4000
4000
BINARY_INTEGER
带符号整数,为整数计算优化性能
NUMBER（p,s）
Dec
Doubleprecision
Integer
Int
Numeric
Real
Smallint
小数,NUMBER的子类型
高精度实数
整数,NUMBER的子类型
整数,NUMBER的子类型
与NUMBER等价
与NUMBER等价
整数,比integer小
LONG
变长字符串
0->2147483647
32,767字节
DATE
日期型
公元前4712年1月1日至公元后4712年12月31日
BOOLEAN
布尔型
TRUE,FALSE,NULL
不使用
ROWID
存放数据库行号
UROWID
通用行标识符，字符类型
例1.插入一条记录并显示；
DECLARE
Row_idROWID;
infoVARCHAR2（40）;
BEGIN
INSERTINTOdeptVALUES（90,‘SERVICE’,‘BEIJING’）
RETURNINGrowid,dname||’:
’||to_char（deptno）||’:
’||loc
INTOrow_id,info;
DBMS_OUTPUT.PUT_LINE（‘ROWID:
’||row_id）;
DBMS_OUTPUT.PUT_LINE（info）;
END;
其中：
RETURNING子句用于检索INSERT语句中所影响的数据行数，当INSERT语句使用VALUES子句插入数据时，RETURNING字句还可将列表达式、ROWID和REF值返回到输出变量中。
在使用RETURNING子句是应注意以下几点限制：
1．不能并行DML语句和远程对象一起使用；
2．不能检索LONG类型信息；
3．当通过视图向基表中插入数据时，只能与单基表视图一起使用。
例2.修改一条记录并显示
DECLARE
Row_idROWID;
infoVARCHAR2（40）;
BEGIN
UPDATEdeptSETdeptno=80WHEREDNAME=‘SERVICE’
RETURNINGrowid,dname||’:
’||to_char（deptno）||’:
’||loc
INTOrow_id,info;
DBMS_OUTPUT.PUT_LINE（‘ROWID:
’||row_id）;
DBMS_OUTPUT.PUT_LINE（info）;
END;
其中：
RETURNING子句用于检索被修改行信息：
当UPDATE语句修改单行数据时，RETURNING子句可以检索被修改行的ROWID和REF值，以及行中被修改列的列表达式，并可将他们存储到PL/SQL变量或复合变量中；当UPDATE语句修改多行数据时，RETURNING子句可以将被修改行的ROWID和REF值，以及列表达式值返回到复合变量数组中。
在UPDATE中使用RETURNING子句的限制与INSERT语句中对RETURNING子句的限制相同。
例3.删除一条记录并显示
DECLARE
Row_idROWID;
infoVARCHAR2（40）;
BEGIN
DELETEdeptWHEREDNAME=‘SERVICE’
RETURNINGrowid,dname||’:
’||to_char（deptno）||’:
’||loc
INTOrow_id,info;
DBMS_OUTPUT.PUT_LINE（‘ROWID:
’||row_id）;
DBMS_OUTPUT.PUT_LINE（info）;
END;
其中：
RETURNING子句用于检索被修改行信息：
当UPDATE语句修改单行数据时，RETURNING子句可以检索被修改行的ROWID和REF值，以及行中被修改列的列表达式，并可将他们存储到PL/SQL变量或复合变量中；当UPDATE语句修改多行数据时，RETURNING子句可以将被修改行的ROWID和REF值，以及列表达式值返回到复合变量数组中。
在UPDATE中使用RETURNING子句的限制与INSERT语句中对RETURNING子句的限制相同。
§2.4.2复合类型
ORACLE在PL/SQL中除了提供象前面介绍的各种类型外,还提供一种称为复合类型的类型---记录和表.
§2.4.2.1记录类型
记录类型是把逻辑相关的数据作为一个单元存储起来，它必须包括至少一个标量型或RECORD数据类型的成员，称作PL/SQLRECORD的域（FIELD），其作用是存放互不相同但逻辑相关的信息。
定义记录类型语法如下:
TYPErecord_typeISRECORD（
Field1type1[NOTNULL][:
=exp1],
Field2type2[NOTNULL][:
=exp2],
......
Fieldntypen[NOTNULL][:
=expn]）;
例4：
DECLARE
TYPEtest_recISRECORD（
CodeVARCHAR2（10）,
NameVARCHAR2（30）NOTNULL:
=’abook’）;
V_booktest_rec;
BEGIN
V_book.code:
=’123’;
V_book.name:
=’C++Programming’;
DBMS_OUTPUT.PUT_LINE（v_book.code||v_book.name）;
END;
可以用SELECT语句对记录变量进行赋值,只要保证记录字段与查询结果列表中的字段相配即可。
§2.4.2.2使用%TYPE
定义一个变量，其数据类型与已经定义的某个数据变量的类型相同，或者与数据库表的某个列的数据类型相同，这时可以使用%TYPE。
使用%TYPE特性的优点在于：
●所引用的数据库列的数据类型可以不必知道；
●所引用的数据库列的数据类型可以实时改变。
例5：
DECLARE
--用%TYPE类型定义与表相配的字段
TYPEt_RecordISRECORD（
T_noemp.empno%TYPE,
T_nameemp.ename%TYPE,
T_salemp.sal%TYPE）;
--声明接收数据的变量
v_empt_Record;
BEGIN
SELECTempno,ename,salINTOv_empFROMempWHEREempno=7788;
DBMS_OUTPUT.PUT_LINE
（TO_CHAR（v_emp.t_no）||v_emp.t_name||TO_CHAR（v_emp.t_sal））;
END;
例6：
DECLARE
v_empnoemp.empno%TYPE:
=&no;
Typer_recordisrecord（
v_nameemp.ename%TYPE,
v_salemp.sal%TYPE,
v_dateemp.hiredate%TYPE）;
Recr_record;
BEGIN
SELECTename,sal,hiredateINTORecFROMempWHEREempno=v_empno;
DBMS_OUTPUT.PUT_LINE（Rec.v_name||'---'||Rec.v_sal||'--'||Rec.v_date）;
END;
§2.4.3使用%ROWTYPE
PL/SQL提供%ROWTYPE操作符,返回一个记录类型,其数据类型和数据库表的数据结构相一致。
使用%ROWTYPE特性的优点在于：
●所引用的数据库中列的个数和数据类型可以不必知道；
●所引用的数据库中列的个数和数据类型可以实时改变。
例7：
DECLARE
v_empnoemp.empno%TYPE:
=&no;
recemp%ROWTYPE;
BEGIN
SELECT*INTOrecFROMempWHEREempno=v_empno;
DBMS_OUTPUT.PUT_LINE（'姓名:
'||rec.ename||'工资:
'||rec.sal||'工作时间:
'||rec.hiredate）;
END;
§2.4.4LOB类型*
ORACLE提供了LOB（LargeOBject）类型，用于存储大的数据对象的类型。
ORACLE目前主要支持BFILE,BLOB,CLOB及NCLOB类型。
BFILE（Movie）
存放大的二进制数据对象，这些数据文件不放在数据库里，而是放在操作系统的某个目录里，数据库的表里只存放文件的目录。
BLOB（Photo）
存储大的二进制数据类型。
变量存储大的二进制对象的位置。
大二进制对象的大小<=4GB。
CLOB（Book）
存储大的字符数据类型。
每个变量存储大字符对象的位置，该位置指到大字符数据块。
大字符对象的大小<=4GB。
NCLOB
存储大的NCHAR字符数据类型。
每个变量存储大字符对象的位置，该位置指到大字符数据块。
大字符对象的大小<=4GB。
§2.4.5BIND变量
绑定变量是在主机环境中定义的变量。
在PL/SQL程序中可以使用绑定变量作为他们将要使用的其它变量。
为了在PL/SQL环境中声明绑定变量，使用命令VARIABLE。
例如：
VARIABLEreturn_codeNUMBER
VARIABLEreturn_msgVARCHAR2（20）
可以通过SQL*Plus命令中的PRINT显示绑定变量的值。
例如：
PRINTreturn_code
PRINTreturn_msg
例7：
VARIABLEresultNUMBER
BEGIN
SELECT（sal*12）+nvl（comm,0）INTO:
resultFROMempWHEREempno=7788;
END;
PRINTresult
§2.4.6PL/SQL表
PL/SQL表,或者称为索引表（index-table），是可以在PL/SQL程序中引用、能够模仿数组的非永久表。
用户可以定义一个表类型，然后声明这种类型的变量。
接下来，用户就可以将记录添加到用户的PL/SQL表中，并且采用与引用数组元素大体相同的方法引用他们．
包括两个基本成分:
．数据处理类型为BINARY_INTEGER主键;
．标量或记录数据类型的列．
TYPEtype_nameISTABLEOF
{column_type|variable%TYPE|table.column%TYPE}[NOTNULL]|table%ROWTYPE
[INDEXBYBINARY_INTEGER];
方法
描述
EXISTS（n）
ReturnTRUEifthenthelementinaPL/SQLtableexists;
COUNT
ReturnsthenumberofelementsthataPL/SQLtablecurrentlyco
升级会员 