第9章PL SQL语言基础 孟德欣 主编 许勇 谢二莲 贺师君 副主编.docx
- 文档编号:25588229
- 上传时间:2023-06-10
- 格式:DOCX
- 页数:46
- 大小:28.97KB
第9章PL SQL语言基础 孟德欣 主编 许勇 谢二莲 贺师君 副主编.docx
《第9章PL SQL语言基础 孟德欣 主编 许勇 谢二莲 贺师君 副主编.docx》由会员分享,可在线阅读,更多相关《第9章PL SQL语言基础 孟德欣 主编 许勇 谢二莲 贺师君 副主编.docx(46页珍藏版)》请在冰豆网上搜索。
第9章PLSQL语言基础孟德欣主编许勇谢二莲贺师君副主编
第9章PL/SQL语言基础
9.1.4PL/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绑定变量
下面的语句演示了绑定变量的使用:
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过程已成功完成。
注意:
如果在SQL*Plus中执行上面的代码时,没有输出结果。
可先执行下面的语句允许服务器输出:
SQL>SETSERVEROUTPUTON
9.2PL/SQL控制结构
9.2.1选择结构
下面的语句是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循环结构
下面是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结构:
DECLARE
NINTEGER;
BEGIN
IFN>1THEN
GOTOBIG_LABLE;
ENDIF;
……/*其他执行语句*/
<
DBMS_OUTPUT.PUT_LINE('N小于1');
END;
/
N小于1
PL/SQL过程已成功完成。
由于GOTO结构破坏了执行流程,使得理解和维护代码变得非常困难,应该尽量避免使用GOTO结构。
9.2.4NULL结构
如果两个变量的值都为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记录和表类型
9.3.1使用%TYPE
下面的代码块声明了一个%TYPE类型的变量。
DECLARE
my_namestudent.name%TYPE;
BEGIN
SELECTnameINTOmy_nameFROMstudentWHEREno=’01203001’;
DBMS_OUTPUT.PUT_LINE(my_name);
END;
/
9.3.2记录类型
下面的代码块声明一个记录类型为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
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表类型
下面的代码块声明表类型。
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).CLASS:
=’计算机2030’;
s1
(2).NO:
=’01203102’;
s1
(2).NAME:
=’李小龙’;
s1
(2).SEX:
=’男’;
s1
(2).BIRTHDAY:
=TO_DATE(’1982-6-8’,’YYYY-MM-DD’);
s1
(2).CLASS:
=’电子2030’;
DBMS_OUTPUT.PUT_LINE(s1
(1).NAME||’,’||s1
(1).CLASS);
DBMS_OUTPUT.PUT_LINE(s1
(2).NAME||’,’||s1
(2).CLASS);
END;
/
张大成,计算机2030
李小龙,电子2030
PL/SQL过程已成功完成
9.4游标
9.4.1游标的基本操作
1.声明游标
DECLARE
CURSORc_stuISSELECT*FROMSTUDENT;/*声明游标*/
BEGIN
……/*执行语句部分*/
END;
/
2.打开游标
DECLARE
CURSORc_stuISSELECT*FROMSTUDENT;
BEGIN
OPENc_stu;/*打开游标*/
……/*其他执行语句部分*/
END;
/
3.提取数据
DECLARE
CURSORc_stuISSELECT*FROMSTUDENT;
Student_recordstudent%ROWTYPE;
BEGIN
OPENc_stu;/*打开游标*/
LOOP
FETCHc_stuINTOstudent_record;
……/*其他执行语句部分*/
EXITWHENc_stu%NOTFOUND/*是否发现一条记录*/
ENDLOOP;
END;
/
4.关闭游标
DECLARE
CURSORc_stuISSELECT*FROMSTUDENT;/*声明游标*/
stu1student%ROWTYPE;
BEGIN
OPENc_stu;/*打开游标*/
DBMS_OUTPUT.PUT_LINE('学号姓名性别生日班级');
LOOP
FETCHc_stuINTOstu1;/*提取游标*/
DBMS_OUTPUT.PUT_LINE(stu1.no||stu1.name||
stu1.sex||stu1.birthday
||stu1.class);
EXITWHENc_stu%NOTFOUND;
ENDLOOP;
CLOSEc_stu;/*关闭游标*/
END;
/
在SQL*Plus中执行上面的代码,执行结果如下。
学号姓名性别生日班级
01203001王晓英女01-8月-80计算机2030
01203002周成男12-5月-81计算机2030
…………………………………………………………………………………………………………………………
PL/SQL过程已成功完成。
9.4.2游标的属性操作
DECLARE
CURSORc_stuISSELECT*FROMSTUDENT;
BEGIN
……/*对游标c_stu的操作*/
IFc_stu%ISOPENTHEN/*如果游标已经打开,即关闭游标*/
CLOSEc_stu;
ENDIF;
END;
/
下面的代码块是使用%FOUND属性的示例。
DECLARE
CURSORc_stuISSELECT*FROMSTUDENT;
stu1student%ROWTYPE;
BEGIN
OPENc_stu;/*打开游标*/
WHILEc_stu%FOUNDLOOP/*如果找到记录,开始循环提取数据*/
FETCHc_stuINTOstu1;
……/*对游标c_stu的操作*/
ENDLOOP;
CLOSEc_stu;
END;
/
下面的代码块是使用%NOTFOUND属性的示例。
DECLARE
CURSORc_stuISSELECT*FROMSTUDENT;
stu1student%ROWTYPE;
BEGIN
OPENc_stu;/*打开游标*/
LOOP
FETCHc_stuINTOstu1;
……/*对游标c_stu的操作*/
/*如果没有找到下一条记录,退出LOOP*/
EXITWHENc_stu%NOTFOUND;
ENDLOOP;
CLOSEc_stu;
END;
/
下面的代码块是使用%ROWCOUNT属性的示例。
DECLARE
CURSORc_stuISSELECT*FROMSTUDENT;
stu1student%ROWTYPE;
BEGIN
OPENc_stu;/*打开游标*/
LOOP
FETCHc_stuINTOstu1;
EXITWHENc_stu%NOTFOUND;
ENDLOOP;
DBMS_OUTPUT.PUT_LINE('Rowcount:
'||c_stu%ROWCOUNT);
CLOSEc_stu;
END;
/
9.4.3参数化游标和隐式游标
DECLARE
CURSORc_stu(v_classstudent.class%TYPE)IS
SELECT*FROMSTUDENT
WHERECLASS=v_class;
stu1student%ROWTYPE;
BEGIN
OPENc_stu('计算机2030');/*打开参数化游标*/
LOOP
FETCHc_stuINTOstu1;
EXITWHENc_stu%NOTFOUND;
ENDLOOP;
DBMS_OUTPUT.PUT_LINE('Rowcount:
'||c_stu%ROWCOUNT);
CLOSEc_stu;
END;
/
下面的代码块使用了SQL隐式游标的%ROWCOUNT属性。
DECLARE
V_COUNTNUMBER;
BEGIN
UPDATEHR.JOBSSETMAX_SALARY=60000
WHEREMIN_SALARY=20000ANDJOB_ID=’AD_PRES’;
V_COUNT:
=SQL%ROWCOUNT;
DBMS_OUTPUT.PUT_LINE('更新数据行数:
'||v_COUNT);
END;
9.4.4游标变量
DECLARE
TYPEstu_TypISREFCURSORRETURNstudent%ROWTYPE;
c_stustu_Typ;--声明游标变量
BEGIN
NULL;
END;
2.操作游标变量
下面的代码块是一个提取游标变量的示例。
DECLARE
TYPEstu_TypISREFCURSORRETURNstudent%ROWTYPE;
c_stustu_Typ;
rec_stustudent%ROWTYPE;
BEGIN
IFNOTc_stu%ISOPENTHEN
/*打开游标变量*/
OPENc_stuFORSELECT*FROMstudentWHEREname='马力';
ENDIF;
LOOP
FETCHc_stuINTOrec_stu;/*提取游标变量*/
EXITWHENc_stu%NOTFOUND;
……/*处理数据*/
DBMS_OUTPUT.PUT_LINE('姓名:
'||rec_stu.name||
'班级:
'||rec_stu.class);
ENDLOOP;
CLOSEc_stu;
END;
/
在上面的代码块中,每次从游标变量中提取一行记录并把该记录插入到一个记录变量中。
9.5过程和函数
9.5.1过程的基本操作
1.创建过程
创建过程的语法如下:
CREATE[ORREPLACE]PROCEDURE过程名
[参数1{IN|OUT|INOUT}类型,
参数2{IN|OUT|INOUT}类型,
……
参数N{IN|OUT|INOUT}类型]
{IS|AS}
过程体
下面的代码块创建了一个过程。
CREATEORREPLACEPROCEDUREview_stuAS
CURSORc_stuISSELECT*FROMSTUDENT;
stu1student%ROWTYPE;
BEGIN
OPENc_stu;/*打开游标*/
DBMS_OUTPUT.PUT_LINE('学号姓名性别生日班级');
LOOP
FETCHc_stuINTOstu1;
DBMS_OUTPUT.PUT_LINE(stu1.no||stu1.name||stu1.sex||
stu1.birthday||stu1.class);
EXITWHENc_stu%NOTFOUND;
ENDLOOP;
CLOSEc_stu;
END;
/
过程已创建。
2.查看过程
在USER_SOURCE视图中查看过程信息如下:
SQL>SELECTTEXTFROMUSER_SOURCEWHERENAME='VIEW_STU';
TEXT
---------------------------------------------------------
PROCEDUREview_stuAS
CURSORc_stuISSELECT*FROMSTUDENT;
stu1student%ROWTYPE;
BEGIN
OPENc_stu;/*打开游标*/
DBMS_OUTPUT.PUT_LINE('学号姓名性别生日班级');
LOOP
FETCHc_stuINTOstu1;
DBMS_OUTPUT.PUT_LINE(stu1.no||stu1.name||stu1.sex||
stu1.birthday||stu1.class);
EXITWHENc_stu%NOTFOUND;
ENDLOOP;
CLOSEc_stu;
END;
已选择13行。
过程作为一个数据库对象,也可以用DESC命令列出关于过程结构的详细信息,如下面的语句:
SQL>CREATEORREPLACEPROCEDUREpp1(
P1INNUMBER,
P2OUTNUMBER,
P3OUTVARCHAR2,
P4OUTDATE)
AS
BEGIN
NULL;
END;
/
过程已创建。
SQL>DESCpp1
PROCEDUREpp1
参数名称类型输入/输出默认值?
-----------------------------------------------------
P1NUMBERIN
P2NUMBEROUT
P3VARCHAR2OUT
P4DATEOUT
3.修改过程
修改过程使用带有ORREPLACE选项的重建命令进行修改。
下面的代码块表示修改VIEW_STU过程。
CREATEORREPLACEPROCEDUREview_stuAS
CURSORc_stuISSELECT*FROMSTUDENTWHEREsex='男';
stu1student%ROWTYPE;
BEGIN
OPENc_stu;/*打开游标*/
DBMS_OUTPUT.PUT_LINE('学号姓名性别生日班级');
LOOP
FETCHc_stuIN
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 第9章 PL SQL语言基础 孟德欣 主编 许勇 谢二莲 贺师君 副主编 SQL 语言 基础