1、PLSQL编程语言的使用与程序设计实验五 PL/SQL编程语言的使用与程序设计【开发语言及实现平台或实验环境】Oracle10g【实验目的】(1)了解PL/SQL在Oracle中的基本概念;(2)掌握PL/SQL的各组成部分;(3)PL/SQL的运用。【实验原理】1PL/SQL字符集和所有其他程序设计语言一样,PL/SQL也有一字符集。读者能从键盘上输入的字符集是PL/SQL的字符。此外,在某些场合,还有使用某些字符的规定。我们将要详细介绍: 用PL/SQL编程时可能使用的字符 算术运算符 关系运算符 杂符号1)合法字符用PL/SQL程序时,允许使用下列字符: 所有大、小写字母 数字0到9 符
2、号:()+-*/ =!;:. %,“# $ & _ | ? 2)算术运算符下面列出了PL/SQL中常用的算术运算符。如果读者使用过其他高级程序设计语言,想必不会陌生:表6 算术运算符运算符意义+加法-减法*乘法/除法*幂关系运算符下面列出了PL/SQL中常用的关系运算符。如果读者有使用其他程序设计语言的经验,一定见过这些符号:表7 关系运算符运算符意义不等于!=不等于=不等于大于=等于3)杂符号PL/SQL为支持编程,还使用下述符号。下面列出了部分符号,它们是最常用的,也是使用PL/SQL的所有读者都必须了解的。表8 杂符号符号意义样例( )列表分隔(Jones,Roy,Abramson);语
3、句结束Procedure_name(arg1,arg2).项分隔(在例子中,用分隔account与table_name)Select * from account.table_name;,字符串界定符if var1 = SANDRA:=赋值Rec_read := rec_read+1|并置Full_name:=Nahtan|Yebba-注释符-This is a comment/*与*/注释定界符/*This too is a comment */4)变量变量是PL/SQL中用来处理数据项所用的名字。读者根据下列规则选择变量名称: 变量必须以字母(AZ)开头。 其后跟可选的一个或多个字母,数字
4、(09)或特殊字符$、# 或_。 变量长度不超过30个字符。 变量名中不能有空格。5)保留字保留字可视为PL/SQL版权所有的字符串。在定义变量名时,读者不能使用这些保留字。例如,词“loop”在PL/SQL中有特殊含义,因此下列代码是非法的:declare employee varchar2(30); loop number;保留字不能用作变量名。尽管我们不推荐,但如果读者坚持,也可以连接两个保留字形成变量名(如loop_varchar2)。完整的PL/SQL保留字表可以在Oracle8文档中找到。2、常用数据类型到目前为止,我们讨论了在PL/SQL中编程时允许使用的字符,变量的命名和保留字
5、。下面着重讨论数据类型。PL/SQL程序用于处理和显示多种类型的数据。和所有计算机软件一样,Oracle也将数据类型分成大量的子类。例如:数可分为整型(不允许有小数)和小数(有一位小数或多位小数)类。PL/SQL支持多种数据类型,此处讨论代码中最常用并且最实用的数据类型。 Varchar2 Number Date Boolean3PL/SQL的组件1)块结构(Block structure)PL/SQL程序是由独立的变量声明、执行代码和异常处理等部分代码块写成的。PL/SQL可以作为一个命名的子程序存放在数据库中,或直接在SQL*Plus窗口中作为一个匿名的块编码。当在数据库中存储PL/SQL
6、时,子程序包括存储单元命名的头部分,程序类型的声明;以及可选的in, out 和in out参数的定义。只是可执行部分定义为begin 和end语句是固定的。Declare 和exception部分是可选的。下面在一个无名块上和一个存储过程的例子。-无名块declare begin end;-存储过程create or replace precodure_nameas -声明部分自动跟着语句而不需要编码。 begin exception end;2)声明部分(Declare section)此PL/SQL块用于定义变量。在declare段中,我们可找到前面讲过的常用数据类型,以及下一节要介绍的
7、cursor(光标)变量类型。下面的程序是一个过程的declare段例子。当存储对象(命名块)产生时,declare 段自动跟着as关键字。在SQL*Plus编写一个PL/SQL代码块(匿名块)时,用户必须指定DECLARE。Create or replace procedure samp(I_salary in number, I_city in number)as-这是DECLARE段;因为我们正在进行命名存储对象的编码,declare是隐含的,不需写-出。Accum1 number;Accum2 number;H_date date := sysdate; -变量能在此初始化Status
8、_flag varchar2(1);Mess_text varchar2(80);Temp_buffer varchar2(1);3)控制结构A)if逻辑结构在编写计算机程序时,有各种各样的情况需要处理。这时必须测试条件,如果测试值为TRUE,做某事,为FALSE,做另一件不同的事。PL/SQL提供三种if逻辑结构供用户测试TRUE/FALSE以完成相应的工作。b1)if-then这个结构用于测试一个简单条件。如果该条件为TRUE,则执行一行或多行代码;如果条件测试为FALSE,则程序控制转到后面的代码。在PL/SQL中实现if逻辑有两条规则:规则1每个if语句都有自己的then,以if开始的
9、语句行不跟语句结束符(;)。规则2每个if语句块以相应的end if结束。b2)if-then-else这种结构与if语句非常相似,唯一不同的是在条件为FALSE时,执行跟在else后的一条或多条语句。下面是PL/SQL中if逻辑的另外两条规则:规则3每个if语句有且只有一个else。规则4else语句行不跟语句结束符。if-then-elsif这种结构用于替代嵌套if-then-else结构。PL/SQL中有关if逻辑的最后一条规则:规则5elsif无匹配的end if。实际上,该end if术语本语句块开始的if,而不属于elsif关键字。注意上列各清单的代码缩进部分是如何表示它们属于那一
10、条件的。前些例子只说明了一个elsif的语句;但是,在任何if语句中可以有许多elsif语句。值得注意的是else语句是不需要的。关于前面举过的执照管理部门(DMV)的例子,用PL/SQL编程表示其逻辑如下。根据“the_act”的值,过程 12a,12b,12g将被调用:create or replace procedure license_transaction(the_act in varchar2 as beginif the_act = DCT then 12a;elseif the_act = DT then 12b;else 12g;end if;end;B)循环下面几节介绍PL
11、/SQL中使用的几种循环形式。LOOP-EXIT-END循环:此循环结构由三部分组成,其用法参见下面示例中的注释:cnt:=1; -在循环开始前,初始化循环计数器loop -第一部分:以循环关键字loop开始循环 cnt:=cnt+1; -第二部分:增加循环计数器的值if cnt=100 then - 测试cnt是否符合退出条件 exit; - 满足退出条件,退出循环end if; - End if与前面的if匹配End loop; - 第三部分:关键字End loop结束循环LOOP-EXIT WHEN-END循环:除退出条件检测有所区别外,此结构与前一个循环结构类似。cnt:=1; -在循
12、环开始前,初始化循环计数器loop -第一部分:以循环关键字loop开始循环 cnt:=cnt+1; -第二部分:增加循环计数器的值exit when cnt=100 - 测试cnt是否符合退出条件End loop; - 第三部分:关键字End loop结束循环WHILE-LOOP-END循环:此结构在循环的while部分测试退出条件。cnt:=1; -在循环开始前,初始化循环计数器while cnt DECLAREx NUMBER(7,2);BEGIN SELECT sal INTO x FROM emp WHERE empno = 7788;IF x create table log_ta
13、ble( 2 user_id varchar2(10), 3 log_date varchar2(12);CREATE OR REPLACE PROCEDURE log_executionISBEGININSERT INTO log_table (user_id,log_date) VALUES (user,sysdate);END;/存储过程的在SQL*Plus中运行SQLEXECUTE log_execution;3带输入参数的存储过程解雇给定职工号的职工,并调用log_execution:SQL CREATE OR REPLACE PROCEDURE fire_emp 2 (v_emp_
14、no IN emp.empno%type) 3 IS 4 BEGIN 5 Log_execution; 6 DELETE FROM EMP WHERE empno = v_emp_no; 7 END; 8 /SQLEXECUTE fire_emp(7654);存储过程删除了职工号7654的职工。4带输入输出的存储过程查询EMP中给定职工号的姓名、工资和佣金。SQL CREATE OR REPLACE PROCEDURE query_emp 2 (v_emp_no IN emp.empno%type, 3 v_emp_name OUT emp.ename%type, 4 v_emp_sal OU
15、T emp.sal%type, 5 v_emp_comm OUT m%type) 6 IS 7 BEGIN 8 SELECT ename,sal,comm 9 INTO v_emp_name,v_emp_sal,v_emp_comm 10 FROM EMP WHERE empno = v_emp_no;11 END;12 /调用:SQLVAR emp_name varchar2(15);SQLVAR emp_sal number;SQLVAR emp_comm number;SQLEXECUTE query_emp(7566,:emp_name, :emp_sal, :emp_comm);PL
16、/SQL procedure successfully completed.SQLPRINT emp_nameEMP_NAME-JONES或者用以下语句调用:DECLARE emp_name varchar2(15); emp_sal number; emp_comm number;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 CREATE OR REPLACE FUN
17、CTION get_sal 2 (v_emp_no IN emp.empno%type) 3 RETURN number 4 AS 5 V_emp_sal emp.sal%type:= 0; 6 BEGIN 7 SELECT sal INTO v_emp_sal 8 FROM EMP WHERE empno = v_emp_no; 9 RETURN (v_emp_sal);10 END;11 /SQLVARIABLE emp_sal number;SQLEXECUTE :emp_sal := get_sal(7566);PL/SQL procedure successfully complet
18、ed.SQLPRINT emp_sal; EMP_SAL- 2975【实验内容】1、 查询名为“SMITH”的员工信息,输出其员工号、工资、部门号。如果该员工不存在,则插入一条新记录,员工号为2007,员工名为“SMITH”,工资为1500,部门号为10.2、 创建一个存储过程,以员工号为参数,输出该员工的工资。3、 创建一个存储过程,以员工号为参数,修改该员工的工资。若该员工属于10号部门,则工资增加150;若属于20号部门,则工资增加200;若属于30号部门,则工资增加250;若属于其他部门,则工资增加300.4、创建一个存储过程,以员工号为参数,返回该员工的工作年限(以参数形式返回)。提
19、示:可以采用日期函数months_between求工作的月份5、创建一个函数,以员工号为参数,返回该员工所在部门的平均工资。 6、在emp表上创建一个触发器,当插入、删除或修改员工信息时,统计各个部门的人数及平均工资,并输出。7、在dept表上创建触发器,保证该表的记录的删除可以成功完成。 8、在dept表上创建触发器,使得当DEPT表的deptno(主键)发生变化时,EMP表的相关行也要跟着进行适当的修改。9、创建一个存储过程,以一个整数为参数,输出工资最高的前几个(参数值)员工的信息。10、创建一个存储过程,以两个整数为参数,输出工资排序在两个参数之间的员工信息。11、创建一个包,包中包含一个函数和一个过程。函数以部门号为参数,返回该部门员工的最高工资;过程以部门号为参数,输出该部门中工资最高的员工名、员工号。
copyright@ 2008-2023 冰点文库 网站版权所有
经营许可证编号:鄂ICP备19020893号-2