PLSQL编程语言的使用与程序设计.docx
《PLSQL编程语言的使用与程序设计.docx》由会员分享，可在线阅读，更多相关《PLSQL编程语言的使用与程序设计.docx（18页珍藏版）》请在冰豆网上搜索。
PLSQL编程语言的使用与程序设计
实验五PL/SQL编程语言的使用与程序设计
【开发语言及实现平台或实验环境】
Oracle10g
【实验目的】
（1）了解PL/SQL在Oracle中的基本概念；
（2）掌握PL/SQL的各组成部分；
（3）PL/SQL的运用。
【实验原理】
1．PL/SQL字符集
和所有其他程序设计语言一样，PL/SQL也有一字符集。
读者能从键盘上输入的字符集是PL/SQL的字符。
此外，在某些场合，还有使用某些字符的规定。
我们将要详细介绍：
●用PL/SQL编程时可能使用的字符
●算术运算符
●关系运算符
●杂符号
1）合法字符
用PL/SQL程序时，允许使用下列字符：
●所有大、小写字母
●数字0到9
●符号：
（）+-*/〈〉=！
~；：
.‘@%，“#$^&_|{}？
[]
2）算术运算符
下面列出了PL/SQL中常用的算术运算符。
如果读者使用过其他高级程序设计语言，想必不会陌生：
表6算术运算符
运算符
意义
+
加法
-
减法
*
乘法
/
除法
**
幂
关系运算符
下面列出了PL/SQL中常用的关系运算符。
如果读者有使用其他程序设计语言的经验，一定见过这些符号：
表7关系运算符
运算符
意义
<>
不等于
!
=
不等于
^=
不等于
<
小于
>
大于
=
等于
3）杂符号
PL/SQL为支持编程，还使用下述符号。
下面列出了部分符号，它们是最常用的，也是使用PL/SQL的所有读者都必须了解的。
表8杂符号
符号
意义
样例
（）
列表分隔
（‘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个字符。
●变量名中不能有空格。
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
3．PL/SQL的组件
1）块结构（Blockstructure）
PL/SQL程序是由独立的变量声明、执行代码和异常处理等部分代码块写成的。
PL/SQL可以作为一个命名的子程序存放在数据库中，或直接在SQL*Plus窗口中作为一个匿名的块编码。
当在数据库中存储PL/SQL时，子程序包括存储单元命名的头部分，程序类型的声明；以及可选的in,out和inout参数的定义。
只是可执行部分定义为begin和end语句是固定的。
Declare和exception部分是可选的。
下面在一个无名块上和一个存储过程的例子。
--无名块
declare
…
begin
…
end;
--存储过程
createorreplaceprecodure_name
as
--声明部分自动跟着语句而不需要编码。
…
begin
…
exception
…
end;
2）声明部分（Declaresection）
此PL/SQL块用于定义变量。
在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）;
3）控制结构
A）if逻辑结构
在编写计算机程序时，有各种各样的情况需要处理。
这时必须测试条件，如果测试值为TRUE，做某事，为FALSE，做另一件不同的事。
PL/SQL提供三种if逻辑结构供用户测试TRUE/FALSE以完成相应的工作。
b1）if-then
这个结构用于测试一个简单条件。
如果该条件为TRUE，则执行一行或多行代码；如果条件测试为FALSE，则程序控制转到后面的代码。
在PL/SQL中实现if逻辑有两条规则：
规则1
每个if语句都有自己的then，以if开始的语句行不跟语句结束符（；）。
规则2
每个if语句块以相应的endif结束。
b2）if-then-else
这种结构与if语句非常相似，唯一不同的是在条件为FALSE时，执行跟在else后的一条或多条语句。
下面是PL/SQL中if逻辑的另外两条规则：
规则3
每个if语句有且只有一个else。
规则4
else语句行不跟语句结束符。
if-then-elsif这种结构用于替代嵌套if-then-else结构。
PL/SQL中有关if逻辑的最后一条规则：
规则5
elsif无匹配的endif。
实际上，该endif术语本语句块开始的if，而不属于elsif关键字。
注意上列各清单的代码缩进部分是如何表示它们属于那一条件的。
前些例子只说明了一个elsif的语句；但是，在任何if语句中可以有许多elsif语句。
值得注意的是else语句是不需要的。
关于前面举过的执照管理部门（DMV）的例子，用PL/SQL编程表示其逻辑如下。
根据“the_act”的值，过程12a,12b,12g将被调用：
createorreplaceprocedurelicense_transaction（the_actinvarchar2asbegin
ifthe_act=‘DCT’then
12a;
elseifthe_act=‘DT’then
12b;
else
12g;
endif;
end;
B）循环
下面几节介绍PL/SQL中使用的几种循环形式。
LOOP-EXIT-END循环：
此循环结构由三部分组成，其用法参见下面示例中的注释：
cnt:
=1;--在循环开始前，初始化循环计数器
loop--第一部分：
以循环关键字loop开始循环
cnt:
=cnt+1;--第二部分：
增加循环计数器的值
ifcnt>=100then--测试cnt是否符合退出条件
exit;--满足退出条件，退出循环
endif;--Endif与前面的if匹配
…
Endloop;--第三部分：
关键字Endloop结束循环
…
LOOP-EXITWHEN-END循环：
除退出条件检测有所区别外，此结构与前一个循环结构类似。
cnt:
=1;--在循环开始前，初始化循环计数器
loop--第一部分：
以循环关键字loop开始循环
cnt:
=cnt+1;--第二部分：
增加循环计数器的值
exitwhencnt>=100--测试cnt是否符合退出条件
…
Endloop;--第三部分：
关键字Endloop结束循环
…
WHILE-LOOP-END循环：
此结构在循环的while部分测试退出条件。
cnt:
=1;--在循环开始前，初始化循环计数器
whilecnt<100loop--第一部分：
在每次执行循环前，while都要检查退出条件
…--第二部分：
循环体内部的可执行代码
cnt:
=cnt+1;--增加循环计数器的值以满足退出条件
…
Endloop;--第三部分：
关键字Endloop结束循环
…
FOR-IN-LOOP-END循环：
最后介绍的这种循环结构重复执行预定义次数的循环。
该循环结构也由三部分组成：
forin部分定义跟踪循环的变量；
执行循环体中的一条或多条语句，直至控制循环的变量满足退出条件为止；
endloop部分结束循环。
下面是一个说明如何使用这种循环机制的例子：
forcntin1..3loop
insertintotablvalues（‘Stillinloop’,cnt）;
endloop;
例：
1．在SQL*Plus中使用PL/SQL块处理
EMP表中职工号7788的职工，如果工资小于3000那么把工资更改为3000：
SQL>DECLARE
xNUMBER（7,2）;
BEGIN
SELECTsalINTOxFROMempWHEREempno=7788;
IFx<3000THEN
UPDATEempSETsal=3000WHEREempno=7788;
ENDIF;
END;
注：
PL/SQL块在SQL*Plus中以点号（.）结束。
如果想运行缓冲区的内容，那么可以用RUN命令或者/命令。
2．无参数的存储过程
首先创建表：
SQL>createtablelog_table（
2user_idvarchar2（10）,
3log_datevarchar2（12））;
CREATEORREPLACEPROCEDURElog_execution
IS
BEGIN
INSERTINTOlog_table（user_id,log_date）
VALUES（user,sysdate）;
END;
/
存储过程的在SQL*Plus中运行
SQL>EXECUTElog_execution;
3．带输入参数的存储过程
解雇给定职工号的职工，并调用log_execution：
SQL>CREATEORREPLACEPROCEDUREfire_emp
2（v_emp_noINemp.empno%type）
3IS
4BEGIN
5Log_execution;
6DELETEFROMEMPWHEREempno=v_emp_no;
7END;
8/
SQL>EXECUTEfire_emp（7654）;
存储过程删除了职工号7654的职工。
4．带输入输出的存储过程
查询EMP中给定职工号的姓名、工资和佣金。
SQL>CREATEORREPLACEPROCEDUREquery_emp
2（v_emp_noINemp.empno%type,
3v_emp_nameOUTemp.ename%type,
4v_emp_salOUTemp.sal%type,
5v_emp_commOUTm%type）
6IS
7BEGIN
8SELECTename,sal,comm
9INTOv_emp_name,v_emp_sal,v_emp_comm
10FROMEMPWHEREempno=v_emp_no;
11END;
12/
调用：
SQL>VARemp_namevarchar2（15）;
SQL>VARemp_salnumber;
SQL>VARemp_commnumber;
SQL>EXECUTEquery_emp（7566,:
emp_name,:
emp_sal,:
emp_comm）;
PL/SQLproceduresuccessfullycompleted.
SQL>PRINTemp_name
EMP_NAME
-------------------
JONES
或者用以下语句调用：
DECLARE
emp_namevarchar2（15）;
emp_salnumber;
emp_commnumber;
BEGIN
query_emp（7566,emp_name,emp_sal,emp_comm）;
DBMS_OUTPUT.PUT_LINE（emp_name||''||emp_sal||''||emp_comm）;
END;
5．用Function查询出EMP中给定职工号的工资
SQL>CREATEORREPLACEFUNCTIONget_sal
2（v_emp_noINemp.empno%type）
3RETURNnumber
4AS
5V_emp_salemp.sal%type:
=0;
6BEGIN
7SELECTsalINTOv_emp_sal
8FROMEMPWHEREempno=v_emp_no;
9RETURN（v_emp_sal）;
10END;
11/
SQL>VARIABLEemp_salnumber;
SQL>EXECUTE:
emp_sal:
=get_sal（7566）;
PL/SQLproceduresuccessfullycompleted.
SQL>PRINTemp_sal;
EMP_SAL
-------------------
2975
【实验内容】
1、查询名为“SMITH”的员工信息，输出其员工号、工资、部门号。
如果该员工不存在，则插入一条新记录，员工号为2007，员工名为“SMITH”，工资为1500，部门号为10.
2、创建一个存储过程，以员工号为参数，输出该员工的工资。
3、创建一个存储过程，以员工号为参数，修改该员工的工资。
若该员工属于10号部门，则工资增加150；若属于20号部门，则工资增加200；若属于30号部门，则工资增加250；若属于其他部门，则工资增加300.
4、创建一个存储过程，以员工号为参数，返回该员工的工作年限（以参数形式返回）。
提示：
可以采用日期函数months_between求工作的月份
5、创建一个函数，以员工号为参数，返回该员工所在部门的平均工资。
6、在emp表上创建一个触发器，当插入、删除或修改员工信息时，统计各个部门的人数及平均工资，并输出。
7、在dept表上创建触发器，保证该表的记录的删除可以成功完成。
8、在dept表上创建触发器，使得当DEPT表的deptno（主键）发生变化时，EMP表的相关行也要跟着进行适当的修改。
9、创建一个存储过程，以一个整数为参数，输出工资最高的前几个（参数值）员工的信息。
10、创建一个存储过程，以两个整数为参数，输出工资排序在两个参数之间的员工信息。
11、创建一个包，包中包含一个函数和一个过程。
函数以部门号为参数，返回该部门员工的最高工资；过程以部门号为参数，输出该部门中工资最高的员工名、员工号。
升级会员 