第9章PL SQL语言基础.docx
- 文档编号:10169056
- 上传时间:2023-02-09
- 格式:DOCX
- 页数:61
- 大小:49.25KB
第9章PL SQL语言基础.docx
《第9章PL SQL语言基础.docx》由会员分享,可在线阅读,更多相关《第9章PL SQL语言基础.docx(61页珍藏版)》请在冰豆网上搜索。
第9章PLSQL语言基础
第9章PL/SQL语言基础
本章主要介绍PL/SQL语言基础、PL/SQL控制结构。
PL/SQL记录和表类型、游标、过程和函数、触发器、程序包和异常处理等知识。
9.1PL/SQL基础
PL/SQL是ProcedureLanguage/StructuerQueryLanguage的英文缩写,是Oracle对标准SQL规范的扩展,全面支持SQL的数据操作、事务控制等。
PL/SQL完全支持SQL数据类型,减少了在应用程序和数据库之间转换数据的操作。
PL/SQL是一种块结构语言,即构成一个PL/SQL程序的基本单位是程序块。
程序块由过程、函数和无名块三种形式组成,它们之间可以相互嵌套。
PL/SQL程序块在执行时不必逐条在网络上传送SQL语句去执行,作为一组SQL语句的整体发送到Oracle。
PL/SQL引擎还可以嵌入到Oracle开发工具中,这样在客户机上就可以处理PL/SQL代码,减少了网络数据流量。
PL/SQL能够在运行Oracle的任何平台上运行,但不能像其他高级语言一样编译成可执行文件去执行。
SQL*Plus是PL/SQL语言运行的基本工具,当程序第一句以DECLARE或BEGIN开头时,系统会自动识别出PL/SQL语句,而不是直接的SQL命令。
PL/SQL在SQL*Plus中运行时,当遇到斜杠(/)时才提交数据库执行,而不像SQL命令,遇到分号(;)就执行。
PL/SQL程序单元可以提高数据库的安全性,程序单元允许用户访问数据库对象,而不需向用户赋予特定对象的权限。
DBA也可以利用PL/SQL的功能自动执行和处理一些日常管理任务。
下面先介绍PL/SQL语言的基础知识。
9.1.1变量及声明
变量是存储值的内存区域,在PL/SQL中用来处理程序中的值。
像其他高级程序语言一样,PL/SQL中的变量也要遵循一定的命名规则,约定如下:
①变量名以字母开头,不区分大小写;
②变量名由字母、数字以及$、#或_和特殊字符组成;
③变量长度最多包含30个字符;
④变量名中不能有空格;
⑤尽可能避免缩写,用一些具有意义的单词命名;
⑥不能用保留字命名。
正确的变量命名,如Mynumber1,My_number1,My$money,v_temp,TEMP,money$$$tree,SN##,try_again等。
非法的变量命名,如1Mynumber,mynumber1,my&money,vtemp123456-7891011121314151617181920等。
其中,1Mynumber以数字开头非法,mynumber1中间有空格非法,my&money使用了非法字符&,vtemp1234567891011121314151617181920长度过长,超过了30个字符长度。
在PL/SQL中,使用变量前一定要声明,声明变量的语句格式如下:
变量名[CONSTANT]数据类型NOTNULL[DEFAULT|:
=默认值]
其中,CONSTANT选项表示申明的是固定不变的值,即常量。
数据类型指变量的数据类型,常用的数据类型如CHAR,NUMBER,DATE等。
NOTNULL表示该变量非空,必须指定默认值。
DEFAULT和“:
=”作用相同可互换。
下面的一组语句声明了几个变量。
IDCONSTANTInteger:
=100;
cChar(4);
nNumber(5)DEFAULT100.00;
last_nameVarchar2(10):
='WANG';
dDate;
isfinishedBooleanDEFAULTTRUE;
9.1.2数据类型
PL/SQL除了可以使用基本数据类型以外,还允许用户自定义数据类型,如记录类型、TABLE类型、OBJECT类型以及XML类型等。
PL/SQL的数据类型之间可以相互转换,PL/SQL提供了一些类型转换函数,但并不是所有的数据类型之间都可以相互转换。
常用的类型转换函数见表9-1。
表9-1常用的类型转换函数
名称
说明
TO_CHAR
转换为CHAR类型
TO_DATE
转换为DATE类型
TO_TIMESTAMP
转换为TIMESTAMP类型
TO_NUMBER
转为为NUMBER类型
HEX_TO_RAW
将十六进制转化为二进制
RAW_TO_HEX
将二进制转化为十六进制
TO_BINARY_FLOAT
转换为BINARY_FLOAT类型
TO_BINARY_DOUBLE
转换为BINARY_DOUBLE类型
TO_LOB
转换为大对象类型
TO_BLOB
转换为BLOB类型
TO_CLOB
转换为CLOD类型
TO_NCLOB
转换为NCLOB类型
9.1.3表达式
PL/SQL中的表达式由操作对象和操作符组成。
操作对象可以是变量、常量、数字或者函数。
一个简单算术表达式如下:
-X/3+2
操作符可以是一元操作符如负号(--)等,或二元操作符如加号(+)等。
在PL/SQL中没有三元操作符。
最简单的表达式可以由一个变量组成,该变量直接产生计算结果。
PL/SQL按照不同运算符来计算表达式的值,每个表达式只能返回唯一的一个值。
PL/SQL通过对表达式的数据类型来决定计算结果的数据类型。
表9-2列出了PL/SQL表达式中常用的操作符。
表9-2Pl/SQL表达式中的操作符
操作符
说明
+,-
正号、负号
*,/
乘号、除号
+,-,||
加号、减号、字符串连接号
=,<,>,<=,>=,<>(!
=,~=,^=),ISNULL,LIKE,BETWEEN,IN
相等、大于、小于、小于等于、大于等于、不等于、
为空、模式比较、在……之间、在……内
NOT
逻辑非
AND
逻辑与
OR
逻辑或
9.1.4PL/SQL程序块结构
PL/SQL程序的程序块分为无名块、命名块两种。
无名块指未命名的程序块。
命名块指过程、函数、触发器和包等。
本小节先介绍无名块,其结构如下:
[DECLARE
--声明部分]
BEGIN
--语句部分
[EXCEPTION
--异常处理部分]
END;
其中,DECLARE声明部分可选,用来定义PL/SQL中使用到的变量、常量、游标等,BEGIN部分是是PL/SQL块体的核心执行部分,这里包含PL/SQL实际执行的各种对数据库操作的语句,以及可选的以EXCEPTION开头的异常处理部分。
在PL/SQL程序块中,“--”表示单行注释,如果需要多行注释,可以使用符号“/*”开始,以符号“*/”结束。
下面是一个简单的PL/SQL程序块。
DECLARE
first_nameVarchar2(10):
='WANG';--字符变量
dDate;--日期变量
isFinishedBooleanDEFAULTTRUE;--布尔变量
BEGIN
D:
=TO_DATE('2004-2-25',’yyyy-mm-dd’);
IFisFinished=TRUETHEN
isFinished:
=FALSE;
ENDIF;
/*
DBMS_OUTPUT是用来输出结果的ORACLE系统包
PUB_LINE是包括在DBMS_OUTPUT包中的过程
*/
DBMS_OUTPUT.PUT_LINE(first_name);
END;
/
9.1.5绑定变量
绑定变量又称主机变量或全局变量,用于将应用程序环境中的值传递给PL/SQL程序块中进行处理。
在SQL*Plus中创建绑定变量后,可以在整个会话期间的多个程序块中使用,因此,绑定变量有时又称会话变量。
在SQL*Plus中可以创建的绑定变量类型主要有CHAR、NUMBER和VARCHAR2,不存在DATE和BOOLEAN数据类型的SQL*Plus变量。
在SQL*Plus中声明绑定变量使用VAR关键字,在PL/SQL块内部使用绑定变量要在变量名前添加冒号(:
)来标记。
下面的语句演示了绑定变量的使用:
SQL>VARg_countNUMBER;
SQL>BEGIN
SELECTCOUNT(*)INTO:
g_countFROMHR.JOBS;
END;
/
PL/SQL过程已成功完成。
SQL>PRINTg_count
G_COUNT
------------
19
SQL>BEGIN
DBMS_OUTPUT.PUT_LINE(:
g_count);
END;
/
6
PL/SQL过程已成功完成。
上面的代码首先声明了一个NUMBER类型的绑定变量g_count,然后执行一个PL/SQL块,接着使用PRINT命令输出g_count的变量值,最后在PL/SQL块中使用DBMS_OUTPUT包中的PUT_LINE过程也输出g_count的变量值,注意在PL/SQL块内部绑定变量的用法。
注意:
如果在SQL*Plus中执行上面的代码时,没有输出结果。
可先执行下面的语句允许服务器输出:
SQL>SETSERVEROUTPUTON
9.2PL/SQL控制结构
PL/SQL具有过程化语言的特征,可以使用顺序结构、选择结构、循环结构及GOTO结构等控制结构。
下面介绍这几种控制结构。
9.2.1选择结构
选择结构就是根据条件表达式的值来决定执行不同的语句。
PL/SQL可用的选择结构有IF语句和CASE语句。
1.IF语句
IF条件表达式1THEN
语句组1
[ELSIF条件表达式2THEN
语句组2]
……
[ELSE
语句组N]
ENDIF;
IF语句是基本的选择结构语句。
每一个IF语句都有THEN,以IF开头的语句行不能跟语句结束符(分号),每一个IF语句以ENDIF结束,每一个IF语句有且只能有一个ELSE语句相对应。
2.CASE语句
CASE
WHEN条件表达式1THEN语句组1
WHEN条件表达式2THEN语句组2
……
WHEN条件表达式N-1THEN语句组N-1
[ELSE语句组N]
ENDCASE;
CASE语句可以实现多分支选择结构,其中,ELSE子句可选。
下面的语句是IF…THEN…ELSE…ENDIF结构的示例。
DECLARE
var1number:
=100;
var2number:
=200;
BEGIN
IFvar1>var2THEN
DBMS_OUTPUT.PUT_LINE('var1islargerthanvar2');
ELSE
DBMS_OUTPUT.PUT_LINE('var1issmallerthanvar2');
ENDIF;
END;
/
下面的语句是IF…THEN…ELSEIF…ELSE…ENDIF结构的示例。
DECLARE
var1number:
=100;
var2number:
=200;
BEGIN
IFvar1>var2THEN
DBMS_OUTPUT.PUT_LINE('var1islargerthanvar2');
ELSIFvar1=var2THEN
DBMS_OUTPUT.PUT_LINE('var1isequaltovar2');
ELSE
DBMS_OUTPUT.PUT_LINE('var1issmallerthanvar2');
ENDIF;
END;
/
下面的语句是CASE结构的示例。
DECLARE
djCHAR
(1):
='B';
pjVARCHAR2(20);
BEGIN
pj:
=
CASEdj
WHEN'A'THEN'优'
WHEN'B'THEN'良'
WHEN'C'THEN'中'
WHEN'D'THEN'及格'
WHEN'F'THEN'不及格'
ELSE'没有这样的等级。
'
END;
DBMS_OUTPUT.PUT_LINE('等级:
'||dj||',对应评价:
'||pj);
END;
/
其中,符号“||”用于字符串连接。
9.2.2循环结构
循环结构指重复执行一组语句,直至达到指定循环条件的结束要求。
PL/SQL可用的循环结构有以下几种。
1.LOOP语句
LOOP
语句组
ENDLOOP;
退出循环则可以通过在上面的语句组中使用EXIT或EXITWHEN子句来实现。
下面是LOOP语句的示例。
DECLARE
totalnumber:
=0;
salarynumber:
=1800;
BEGIN
LOOP
total:
=total+salary;
EXITWHENtotal>25000;--结束循环
ENDLOOP;
DBMS_OUTPUT.PUT_LINE('工资总额是:
'||total);
END;
/
工资总额是:
25200
PL/SQL过程已成功完成。
2.FOR语句
FOR循环变量IN[REVERSE]起始值..终止值LOOP
语句组
ENDLOOP;
其中,REVERSE选项强制循环变量从终止值开始,每次循环减1,直到起始值。
下面是FOR语句的示例。
DECLARE
totalnumber:
=0;
salarynumber:
=1800;
Iinteger:
=0;
BEGIN
FORIin1..10LOOP
total:
=total+salary;
ENDLOOP;
DBMS_OUTPUT.PUT_LINE('工资总额是:
'||total);
END;
/
工资总额是:
18000
PL/SQL过程已成功完成。
下面的FOR语句使用了REVERSE关键字。
DECLARE
IINTEGER;
BEGIN
FORIINREVERSE1..3LOOP
DBMS_OUTPUT.PUT_LINE(I);
ENDLOOP;
END;
/
3
2
1
PL/SQL过程已成功完成。
3.WHILE语句
WHILE条件表达式LOOP
语句组
ENDLOOP;
下面是WHILE语句的示例。
DECLARE
totalnumber:
=0;
salarynumber:
=1800;
BEGIN
WHILEtotal<25000LOOP
total:
=total+salary;
ENDLOOP;
DBMS_OUTPUT.PUT_LINE('工资总额是:
'||total);
END;
/
工资总额是:
25200
PL/SQL过程已成功完成。
9.2.3GOTO结构
GOTO结构又称为跳转结构。
使用GOTO结构可以使程序转到设定的标签,执行某个代码区域,实现逻辑分支结构。
在PL/SQL中使用符号“<<>>”来创建标签。
下面的代码块使用了GOTO结构:
DECLARE
NINTEGER;
BEGIN
IFN>1THEN
GOTOBIG_LABLE;
ENDIF;
……/*其他执行语句*/
<
DBMS_OUTPUT.PUT_LINE('N小于1');
END;
/
N小于1
PL/SQL过程已成功完成。
由于GOTO结构破坏了执行流程,使得理解和维护代码变得非常困难,应该尽量避免使用GOTO结构。
9.2.4NULL结构
PL/SQL中还有一类特殊的结构,即NULL结构,又称空操作或空值结构。
NULL结构表示什么操作也不做,仅起到占位符的作用,例如:
IFN>1THEN
NULL;/*空语句,什么操作也不做*/
ELSE
DBMS_OUTPUT.PUT_LINE('Nislargerthen1');
ENDIF;
另外,在PL/SQL中含有NULL的条件表达式其运算结果总是NULL。
对NULL施加逻辑运算符NOT,其结果也总是NULL。
如果在选择结构中的条件表达式的值是NULL,则相应的THEN语句不会被执行。
在CASE结构中,不能出现WHENNULL这样的结构,而应该使用ISNULL子句。
如果两个变量的值都为NULL,它们也是不相等的,如下例所示。
DECLARE
aNUMBER:
=NULL;
bNUMBER:
=NULL;
BEGIN
IFa=bTHEN--结果为NULL,不是TRUE
DBMS_OUTPUT.PUT_LINE('a=b');--不会被执行
ELSIFa!
=bTHEN--结果为NULL,不是TRUE
DBMS_OUTPUT.PUT_LINE('a!
=b');--不会被执行
ELSE
DBMS_OUTPUT.PUT_LINE('变量的值都为NULL它们也是不相等的。
');
ENDIF;
END;
/
变量的值都为NULL它们也是不相等的。
PL/SQL过程已成功完成。
9.3PL/SQL记录和表类型
声明变量时所用的数据类型在PL/SQL和SQL都适用,如NUMBER或VARCHAR2等。
PL/SQL还可以声明%TYPE和%ROWTYPE类型的变量。
其中,用%TYPE声明的数据类型与数据表中的字段类型将保持一致,用%ROWTYPE声明的数据类型与数据表中的记录相同,使用这两种数据类型可以更加方便地对操作数据库。
9.3.1使用%TYPE
在PL/SQL中,使用%TYPE声明的变量类型与数据表中的字段的数据类型相同,当数据表中的字段类型修改后,PL/SQL程序中相应变量的类型也随之改变。
如Books表中有一个Title字段,其数据类型为VARCHAR2(30),声明变量my_title用来Title字段存放Title字段的数据,声明如下:
my_titlebooks.title%TYPE;
则my_title变量的数据类型始终与Title字段的类型保持一致,即VARCHAR2(30),当Title字段的数据类型改为VARCHAR2(50)时,my_title变量的数据类型也自动修改为VARCHAR2(50)。
由此看出,使用%TYPE不必确切知道Title字段的数据类型;当Title字段改变时,my_title变量的类型也随之改变。
下面的代码块声明了一个%TYPE类型的变量。
DECLARE
my_namestudent.name%TYPE;
BEGIN
SELECTnameINTOmy_nameFROMstudentWHEREno=’01203001’;
DBMS_OUTPUT.PUT_LINE(my_name);
END;
/
9.3.2记录类型
记录类型是用户自定义的数据类型,记录类型包含一个或多个相关字段,每个字段都有自己的名称和数据类型,即字段包含的类型可以是不相同的。
通常,PL/SQL使用记录类型保存表中记录结构的变量内容。
在PL/SQL中,声明记录类型的语法如下:
TYPE记录类型名ISRECORE(
字段1类型[NOTNULL][DEFAULT|:
=]表达式
……
);
下面的代码块声明一个记录类型为studentRecord,这个数据类型包含no(学号)、name(姓名)、sex(性别)、birthday(生日)和class(班级)字段。
DECLARE
TYPEstudentRecordISRECORD(
nostudent.no%TYPE,
namestudent.name%TYPE,
sexstudent.sex%TYPE,
birthdaystudent.birthday%TYPE,
classstudent.class%TYPE);
stu1studentRecord;
BEGIN
SELECT*INTOstu1FROMstudentWHEREno=’01203001’;
DBMS_OUTPUT.PUT_LINE(stu1.no);
DBMS_OUTPUT.PUT_LINE(stu1.name);
DBMS_OUTPUT.PUT_LINE(stu1.sex);
DBMS_OUTPUT.PUT_LINE(stu1.birthday);
DBMS_OUTPUT.PUT_LINE(stu1.class);
END;
/
9.3.3使用%ROWTYPE
使用%TYPE可以使一个变量与字段的数据类型保持一致,PL/SQL还提供了%ROWTYPE来得到数据表中的记录的数据类型。
声明了%ROWTYPE类型的记录可以完整的存放数据表中的整行记录,并可以使用游标或者游标变量来获取记录中的数据。
关于游标的介绍详见9.4节。
下面的代码块使用%ROWTYPE定义记录类型,实现了与使用记录类型一样的效果。
DECLARE
stu1student%ROWTYPE;--声明%ROWTYPE记录类型变量
BEGIN
SELECT*INTOstu1FROMstudentWHEREno=’01203001’;
DBMS_OUTPUT.PUT_LINE(stu1.no);
DBMS_OUTPUT.PUT_LINE(stu1.name);
DBMS_OUTPUT.PUT_LINE(stu1.sex);
DBMS_OUTPUT.PUT_LINE(stu1.birthday);
DBMS_OUTPUT.PUT_LINE(stu1.class);
END;
/
9.3.4表类型
表类型也是一种由用户定义的数据类型,PL/SQL允许用户使用表类型存放查询出来的表中的数据。
表变量可以用来存储多行及多个字段的值,在将数据存储在表变量中时,必须指出将值插入哪个字段和哪一行。
表类型可以存储多行、多列数据,就好像在PL/SQL中创建了一个表,所以又被称为PL/SQL表。
但这里说讲的表不等同于数据库中的表。
声明表类型的语法如下:
TYPE表类型名ISTABLEOF类型INDEXBYBINARY_INTEGER;
其中,类型为所声明的表类型名的类型,INDEXBYBINARY_INTEGER子句将创建一个主键索引,用于引用表变量的特定行。
下面的代码块声明表类型。
DECLARE
TYPEstu_TypISTABLEOFstudent%ROWTYPEINDEXBYBINARY_INTEGER;
s1stu_Typ;--声明表类型变量
BEGIN
s1
(1).NO:
=’01203101’;
s1
(1).NAME:
=’张大成’;
s1
(1).SEX:
=’男’;
s1
(1).BIRTHDAY:
=TO_DATE(’1982-2-2’,’YYYY-MM-DD’);
s1(1
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 第9章 PL SQL语言基础 SQL 语言 基础