PLSQL编程语言的使用与程序设计.docx

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);

16、PL/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 F

17、UNCTION 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 com

18、pleted.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、创建一个包，包中包含一个函数和一个过程。函数以部门号为参数，返回该部门员工的最高工资；过程以部门号为参数，输出该部门中工资最高的员工名、员工号。