PLSQL编程语言的使用与程序设计.docx
- 文档编号:11209671
- 上传时间:2023-02-25
- 格式:DOCX
- 页数:18
- 大小:135.45KB
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、创建一个包,包中包含一个函数和一个过程。
函数以部门号为参数,返回该部门员工的最高工资;过程以部门号为参数,输出该部门中工资最高的员工名、员工号。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- PLSQL 编程 语言 使用 程序设计