PLSQL 基本讲义.docx
《PLSQL 基本讲义.docx》由会员分享，可在线阅读，更多相关《PLSQL 基本讲义.docx（36页珍藏版）》请在冰豆网上搜索。
PLSQL基本讲义
第三章PL/SQL基本特性
在Oracle中有两种PL/SQL：
一种是数据库引擎（engine）的组成部分，另一种是嵌入到许多Oracle工具中得独立引擎。
将它们分别称为数据库PL/SQL和工具PL/SQL。
两者非常相似，都具有相同得编程结构、语法和逻辑机制，工具PL/SQL另外增加了用于支持工具需求得句法。
例如：
在窗体上设置下压式按扭，以定位到屏幕的底部，此动作可用OracleForms系统中的PL/SQL来编程实现。
1．PL/SQL字符集
和所有其他程序设计语言一样，PL/SQL也有一字符集。
读者能从键盘上输入的字符集是PL/SQL的字符。
此外，在某些场合，还有使用某些字符的规定。
我们将要详细介绍：
●用PL/SQL编程时可能使用的字符
●算术运算符
●关系运算符
●杂符号
1）、合法字符
用PL/SQL程序时，允许使用下列字符：
●所有大、小写字母
●数字0到9
●符号：
（）+-*/〈〉=！
~；：
.‘@%，“#$^&_|{}？
[]
其中一些字符用于编程，另一些用作算术运算操作符（除法、加法、幂等）及关系运算符（等于和不等于）。
例如：
在通信应用程序中，开发人员可能会使用变量名“area_code“存放客户的呼叫区号。
相应的变量名area_code要符合本章“变量”一节所给出的变量命名规则。
2）、算术运算符
下面列出了PL/SQL中常用的算术运算符。
如果读者使用过其他高级程序设计语言，想必不会陌生：
运算符
意义
+
加法
-
减法
*
乘法
/
除法
**
幂
关系运算符
下面列出了PL/SQL中常用的关系运算符。
如果读者有使用其他程序设计语言的经验，一定见过这些符号：
运算符
意义
<>
不等于
!
=
不等于
^=
不等于
<
小于
>
大于
=
等于
3）、杂符号
PL/SQL为支持编程，还使用下述符号。
下面列出了部分符号，它们是最常用的，也是使用PL/SQL的所有读者都必须了解的。
表6-1杂符号
符号
意义
样例
（）
列表分隔
（‘Jones’,’Roy’,’Abramson’）
;
语句结束
Procedure_name（arg1,arg2）
.
项分隔（在例子中，用分隔account与table_name）
Select*fromaccount.table_name;
字符串界定符
ifvar1=‘SANDRA’
:
=
赋值
Rec_read:
=rec_read+1
||
并置
Full_name:
=’Nahtan’||’’||’Yebba’
--
注释符
--Thisisacomment
/*与*/
注释定界符
/*Thistooisacomment*/
4）、变量
变量是PL/SQL中用来处理数据项所用的名字。
读者根据下列规则选择变量名称：
●变量必须以字母（A~Z）开头。
●其后跟可选的一个或多个字母，数字（0~9）或特殊字符$、#或_。
●变量长度不超过30个字符。
●变量名中不能有空格。
我们来看几个实例。
表6-2给出了样例变量名并评价了其合法性。
表6-2合法及不合法的变量名
变量名
合法否
理由
23_skidoo
不合法
必须以字母起头
Nature_trali
合法
Nature-trali
不合法
特殊字符只能是$#_
loveboat
不合法
不能含空格
a_very_insignificant_variable_name
不合法
长于30个字符
me_______and$$$$you
合法
lots_of_$$$$$$
合法
23
不合法
未用字母起头
5）、保留字
保留字可视为PL/SQL版权所有的字符串。
在定义变量名时，读者不能使用这些保留字。
例如，词“loop”在PL/SQL中有特殊含义，因此下列代码是非法的：
declare
employeevarchar2（30）;
loopnumber;
保留字不能用作变量名。
尽管我们不推荐，但如果读者坚持，也可以连接两个保留字形成变量名（如loop_varchar2）。
完整的PL/SQL保留字表可以在Oracle8文档中找到。
2、常用数据类型
到目前为止，我们讨论了在PL/SQL中编程时允许使用的字符，变量的命名和保留字。
下面着重讨论数据类型。
PL/SQL程序用于处理和显示多种类型的数据。
和所有计算机软件一样，Oracle也将数据类型分成大量的子类。
例如：
数可分为整型（不允许有小数）和小数（有一位小数或多位小数）类。
PL/SQL支持多种数据类型，本节讨论代码中最常用并且最实用的数据类型。
●Varchar2
●Number
●Date
●Boolean
1）、Varchar2
Varchar2为可变长的字母数字数据类型。
在PL/SQL中，varchar2的最大长度为32767字节。
在declare部分其定义以分号（；）结束，所有varchar2变量的定义类似于下定义
Variable_namevarchar2（max_length）;
括号中的长度值为本变量的最大长度且必须是正整数，如：
vc_fieldvarchar2（10）;
在定义变量时，可以同时对其进行初始化，格式为：
vc_fieldvarchar2（10）:
=’STARTVALUE’;
2）、number
number型数据可用来表示所有的数值数据，说明格式为：
num_fieldnumber（precision,scale）;
其中，precision可以有1到38个数字位，而scale表示在precision位数字中小数点后的数字位数，下面的定义：
num_fieldnumber（12,2）;
表示num_field是一个整数部分最多10位，小数部分最多2位的变量。
3）、date
此数据类型用于保存固定长度的日期值，date变量的说明为：
date_fielddate;
在缺省时，Oracle以DD-MON-YY格式显示日期。
4）、Boolean
这种数据类型只有两个值：
TRUE或FALSE。
在使用boolean变量时，如果测试结果为TRUE，则做某事，否则做另外的事。
例：
如果试图考察某公司是否分发了一个10KB的2000年年度预算表，可用boolean变量，若该公司分发了此表，则该变量值为TRUE。
3．PL/SQL的组件
下面讨论PL/SQL是如何构成的。
PL/SQL提供了一组标准的自计算机出现以来开发人员一直在使用的程序化了的技术：
逻辑、循环以及错误处理机制。
我们将详细讨论下列内容：
●块结构编码；
●变量声明；
●控制结构。
包括程序控制，if逻辑结构以及循环结构；
●异常处理（exception）；
1）、块结构（Blockstructure）
PL/SQL程序是由独立的变量声明、执行代码和异常处理等部分代码块写成的。
PL/SQL可以作为一个命名的子程序存放在数据库中，或直接在SQL*Plus窗口中作为一个匿名的块编码。
当在数据库中存储PL/SQL时，子程序包括存储单元命名的头部分，程序类型的声明；以及可选的in,out和inout参数的定义。
只是可执行部分定义为begin和end语句是固定的。
Declare和exception部分是可选的。
◆基本块结构：
所有的语句块都包含三个基本部分：
声明部分、执行部分、和异常处理。
只有执行部分是必需的，其他两个部分是可选的。
例如：
DECLARE
/*Startofdeclarativesection*/
v_StudentIDNUMBER（5）:
=10000;--Numericvariableinitialized
--to10,000
v_FirstNameVARCHAR2（20）;--Variablelengthcharacterstring
--withmaximumlengthof20
BEGIN
/*Startofexecutablesection*/
--RetrievefirstnameofstudentwithID10,000
SELECTfirst_name
INTOv_FirstName
FROMstudents
WHEREid=v_StudentID;
EXCEPTION
/*Startofexceptionsection*/
WHENNO_DATA_FOUNDTHEN
--Handletheerrorcondition
INSERTINTOlog_table（info）
VALUES（'Student10,000doesnotexist!
'）;
END;
下面在一个无名块上和一个存储过程的例子。
◆--无名块
declare
…
begin
…
end;
例子：
DECLARE
/*Declarevariablestobeusedinthisblock.*/
v_Num1NUMBER:
=1;
v_Num2NUMBER:
=2;
v_String1VARCHAR2（50）:
='HelloWorld!
';
v_String2VARCHAR2（50）:
=
'--ThismessagebroughttoyoubyPL/SQL!
';
v_OutputStrVARCHAR2（50）;
BEGIN
/*First,inserttworowsintotemp_table,usingthevalues
ofthevariables.*/
INSERTINTOtemp_table（num_col,char_col）
VALUES（v_Num1,v_String1）;
INSERTINTOtemp_table（num_col,char_col）
VALUES（v_Num2,v_String2）;
/*Nowquerytemp_tableforthetworowswejustinserted,and
outputthemtothescreenusingtheDBMS_OUTPUTpackage.*/
SELECTchar_col
INTOv_OutputStr
FROMtemp_table
WHEREnum_col=v_Num1;
DBMS_OUTPUT.PUT_LINE（v_OutputStr）;
SELECTchar_col
INTOv_OutputStr
FROMtemp_table
WHEREnum_col=v_Num2;
DBMS_OUTPUT.PUT_LINE（v_OutputStr）;
/*Rollbackourchanges*/
ROLLBACK;
END;
/
◆--存储过程
createorreplaceprecodure_name
as
--声明部分自动跟着语句而不需要编码。
…
…
begin
…
…
exception
…
…
end;
/
例子：
CREATEORREPLACEPROCEDUREInsertIntoTempIS
/*Declarevariablestobeusedinthisblock.*/
v_Num1NUMBER:
=1;
v_Num2NUMBER:
=2;
v_String1VARCHAR2（50）:
='HelloWorld!
';
v_String2VARCHAR2（50）:
=
'--ThismessagebroughttoyoubyPL/SQL!
';
v_OutputStrVARCHAR2（50）;
BEGIN
/*First,inserttworowsintotemp_table,usingthevalues
ofthevariables.*/
INSERTINTOtemp_table（num_col,char_col）
VALUES（v_Num1,v_String1）;
INSERTINTOtemp_table（num_col,char_col）
VALUES（v_Num2,v_String2）;
/*Nowquerytemp_tableforthetworowswejustinserted,and
outputthemtothescreenusingtheDBMS_OUTPUTpackage.*/
SELECTchar_col
INTOv_OutputStr
FROMtemp_table
WHEREnum_col=v_Num1;
DBMS_OUTPUT.PUT_LINE（v_OutputStr）;
SELECTchar_col
INTOv_OutputStr
FROMtemp_table
WHEREnum_col=v_Num2;
DBMS_OUTPUT.PUT_LINE（v_OutputStr）;
/*Rollbackourchanges*/
ROLLBACK;
ENDInsertIntoTemp;
/
BEGIN
InsertIntoTemp;
END;
/
◆触发器：
createorreplacetrigger_name
before/after
…
Ontable_name
…
begin
…
…
exception
…
…
Endtrigger_name;
/
例子：
REMtrigger.sql
REMChapter3,Oracle9iPL/SQLProgrammingbyScottUrman
REMThisisanexampleofadatabasetrigger.
CREATEORREPLACETRIGGEROnlyPositive
BEFOREINSERTORUPDATEOFnum_col
ONtemp_table
FOREACHROW
BEGIN
IF:
new.num_col<0THEN
RAISE_APPLICATION_ERROR（-20100,'Pleaseinsertapositivevalue'）;
ENDIF;
ENDOnlyPositive;
/
--ThisINSERTwillsucceed,sincethevalueispositive.
INSERTINTOtemp_table（num_col,char_col）
VALUES（1,'Thisisrow1'）;
--Thisonewillfail,however,becausethevalueisnegative.
INSERTINTOtemp_table（num_col,char_col）
VALUES（-1,'Thisisrow-1'）;
ROLLBACK;
◆功能函数:
CRRATEORREPLACEFUNCTIONget_sal
2（v_emp_noINemp.empno%type）
3RETURNnumber
4IS
5V_emp_salemp.sal%type:
=0;
6BEGIN
7SELECTsalINTOv_emp_sal
8FROMEMPWHEREempno=v_emp_no;
9RETURN（v_emp_sal）
10ENDget_sal;
◆光标：
…
…
declare
fnamevarchar2（10）;
lnamevarchar2（30）;
ssec_numvarchar2（8）;
cursorregion_curis
selectfirst_name,last_name,ssn
fromperson
whereregion_number=region_number_in;
begin
openregion_cur;
fetchregion_curintofname,lname,ssec_num;
whileregion_cur%found
loop
ifssec_numIsnullthen
insertintoe_msgvalues（pin_in,’Nossnum’）;
else
insertintoe_tabvalues（pin_in,sysdate）;
endif;
fetchregion_curintofname,lname,ssec_num;
endloop;
closeregion_cur;
end;
…
…
/
◆块嵌套
PL/SQL块可以嵌套，在主begin/end块中可以产生无数的BEGIN/END块。
我们演示怎样控制错误发生时的程序流程以后，PL/SQL好处将变得明显。
这将在本章的异常部分讨论。
在嵌套的块中嵌套块是可以接受的或者在外块中有许多begin/end块。
下面是一个嵌套块的例子：
Createorreplaceprocedurecalculate_redate
（pharmacy_idinnumber）
as
…
…
begin
…
…
begin
…
…
exception
…
…
end;
…
…
exception
…
…
end;
/
REMnested.sql
REMChapter3,Oracle9iPL/SQLProgrammingbyScottUrman
REMThisexampleillustrates2nestedblocks.
DECLARE
/*Startofdeclarativesection*/
v_StudentIDNUMBER（5）:
=10000;--Numericvariableinitialized
--to10,000
v_FirstNameVARCHAR2（20）;--Variablelengthcharacterstring
--withmaximumlengthof20
BEGIN
/*Startofexecutablesection*/
--RetrievefirstnameofstudentwithID10,000
SELECTfirst_name
INTOv_FirstName
FROMstudents
WHEREid=v_StudentID;
--Startofanestedblock,whichcontainsonlyanexecutable
--section
BEGIN
INSERTINTOlog_table（info）
VALUES（'Hellofromanestedblock!
'）;
END;
EXCEPTION
/*Startofexceptionsection*/
WHENNO_DATA_FOUNDTHEN
--Startofanestedblock,whichitselfcontainsanexecutable
--andexceptionsection
BEGIN
--Handletheerrorcondition
INSERTINTOlog_table（info）
VALUES（'Student10,000doesnotexist!
'）;
EXCEPTION
WHENOTHERSTHEN
--SomethingwentwrongwiththeINSERT
DBMS_OUTPUT.PUT_LINE（'Errorinsertingintolog_table!
'）;
END;
END;
/
2）、声明部分（Declaresection）
此PL/SQL块用于定义变量。
如果读者熟悉COBOL语言，declare段就相当于COBOL中的工作存储区。
在declare段中，我们可找到前面讲过的常用数据类型，以及下一节要介绍的cursor（光标）变量类型。
下面的程序是一个过程的declare段例子。
当存储对象（命名块）产生时，declare段自动跟着as关键字。
在SQL*Plus编写一个PL/SQL代码块（匿名块）时，用户必须指定DECLARE。
Createorreplaceproceduresamp（I_salaryinnumber,
I_cityinnumber）
as
--这时DECLARE段；因为我们正在进行命名存储对象的编码，declare是隐含的，不需写---出。
Accum1number;
Accum2number;
H_datedate:
=sysdate;-变量能在此初始化
Status_flagvarchar2
（1）;
Mess_textvarchar2（80）;
Temp_buffervarchar2
（1）;
--象下面的光标将在下面一节讨论
Cursormy_cursor
Is
Selectemployee_number,last_name,first_name
Fromemployeea,departmentb
Wherea.department_number=b.department_number
Anda.salary>I_salary
Andb.location=I_city;
Begin
…
…
end;
/
◆声明语法：
Variable_nametype[constraint][notnull][:
=value];
其中，Variable_name是该变量的名字，type是类型，而value是该变量的初始值。
例如:
DECLARE
V_DescriptionVARCHAR2（50）;
V_NumberSeatSnumber:
=45;
V_C
升级会员 