鄢鸿实验五oracle综合性实验指导书.doc

湖南科技学院综合性实验指导书实验名称：PL/SQL编程实验项目性质：综合性所涉及课程：Oracle数据库计划学时：4一、 实验目的（1） 熟练掌握PL/SQL程序设计的基本知识。（2） 熟练掌握PL/SQL中控制结构的使用。具体包括选择结构语句（IF语句和CASE语句），循环结构（四种循环结构）。（3） 熟练使用PL/SQL中系统函数。（4） 掌握PL/SQL中异常处理语句的使用（5） 掌握PL/SQL中SELECT语句和DML语句的综合运用。二、 实验内容及步骤1. 创建用户，指定密码、表空间。用户名命名以本人姓名的缩写+_+学号最后两位。2. 为该用户授予sysdba、connect、resource权限。3. 以本人的用户身份登录。4. 练习选择结构语句的使用方法。1、条件语句IF 语法：IF THEN ELSIF THEN ELSE END IF;2、分支语句CASE 语法：CASE WHEN THEN 值1 WHEN THEN 值2 WHEN THEN 值n ELSE 值n + 1END;例1：声明一个整型变量Num，使用IF语句判断Num变量是正数、负数或0。SET ServerOutput ON;DECLARE Num INTEGER := -11; BEGIN IF Num 0 THEN dbms_output.put_line(正数); ELSE dbms_output.put_line(0); END IF;END;/例2、使用CASE语句根据给定的整数输出对应的星期值：SET ServerOutput ON;DECLARE varDAY INTEGER := 3; Result VARCHAR2(20);BEGIN Result := CASE varDAY WHEN 1 THEN 星期一 WHEN 2 THEN 星期二 WHEN 3 THEN 星期三 WHEN 4 THEN 星期四 WHEN 5 THEN 星期五 WHEN 6 THEN 星期六 WHEN 7 THEN 星期七 ELSE 数据越界 END; dbms_output.put_line(Result);END;5. 练习循环结构语句的使用方法。1、循环语句LOOPEXITEND LOOP IF THEN EXIT END IF END LOOP;2、循环语句LOOPEXIT WHENEND LOOP EXIT WHEN END LOOP;3、循环语句WHILELOOPEND LOOP WHILE LOOP END LOOP;4、循环语句FORINLOOPEND LOOP FOR IN .LOOP END LOOP;例1、计算1到3的累加和。SET ServerOutput ON;DECLARE v_Num INTEGER := 2; v_Sum INTEGER := 0;BEGIN LOOP v_Sum := v_Sum + v_Num; dbms_output.put_line(v_Num); IF v_Num = 100 THEN EXIT; END IF; dbms_output.put_line( + ); v_Num := v_Num + 2; END LOOP; dbms_output.put_line( = ); dbms_output.put_line(v_Sum);END;例2、SET ServerOutput ON;DECLARE v_Num INTEGER := 1; v_Sum INTEGER := 0;BEGIN LOOP v_Sum := v_Sum + v_Num; dbms_output.put_line(v_Num); EXIT WHEN v_Num = 3; dbms_output.put_line( + ); v_Num := v_Num + 1; END LOOP; dbms_output.put_line( = ); dbms_output.put_line(v_Sum);END;例3、SET ServerOutput ON;DECLARE v_Num INTEGER; v_Sum INTEGER := 0;BEGIN FOR v_Num IN 1.3 LOOP v_Sum := v_Sum + v_Num; dbms_output.put_line(v_Num); IF v_Num 3 THEN dbms_output.put_line( + ); END IF; END LOOP; dbms_output.put_line( = ); dbms_output.put_line(v_Sum);END;6. 练习异常处理语句的使用方法。WHEN语句来定义异常处理：EXCEPTION WHEN THEN WHEN THEN WHEN OTHERS THEN 例1、SET SERVEROUTPUT ON;DECLARE x NUMBER;BEGIN x:= a123;-向NUMBER类型的变量X中赋值字符串，导致异常EXCEPTION WHEN VALUE_ERROR THEN DBMS_OUTPUT.PUT_LINE(数据类型错误);END;例2、SET SERVEROUTPUT ON;DECLARE v_result NUMBER(10):=0;BEGIN v_result:= 100/0;DBMS_OUTPUT.PUT_LINE(结果是：|v_result);EXCEPTION WHEN ZERO_DIVIDE THEN DBMS_OUTPUT.PUT_LINE(除数是零！默认用1替代除数，结果是：|100/1);END;7. 练习PL/SQL中SELECT语句和DML语句的综合运用。创建表departments和表employees, 并为两张表输入若干数据。如下图所示。表departments结构：字段名称 字段类型 约束Dep_id number 主键Dep_name varchar2(20)表employees结构：字段名称 字段类型 约束emp_id number 主键emp_name varchar2(20)sex varchar2(20)title varchar2(20)wage numberidcard varchar2(12)dep_id number 外键完成思考题部分的PL/SQL程序编写。三、 实验（设计）仪器设备和材料清单（1） 硬件环境：PC机（2） 软件环境：Oracle 10g四、 实验要求1、 验证实验内容部分的程序代码。2、 综合运用各种程序结构完成思考题部分的PL/SQL程序编写。五、 考核形式以实验报告的程序代码结果作为成绩。六、 实验报告要求（1） 实验目的。（2） 思考题的源程序清单。（3） 写出在实验过程中遇到的问题及解决方法。（4） 要求字迹端正、条理清晰、概念正确。七、 思考题1、 编写PL/SQL程序，使用LOOPEXITEND语句计算1100之间所有偶数之和.2、 编写PL/SQL程序，查询5号员工工资，如果工资小于3000，则加200员工资，并提示信息“5号员工工资已更新。”，如果工资大于3000，则提示信息“5号员工工资为XXX，已达到规定标准。”3、 编写PL/SQL程序，查询1号员工的工资，使用CASE语句输出其工资等级。工资小于等于3000，等级为“低”；工资大于3000，小于5000，等级为”中”；工资大于等于5000，等级为高；4、 编写PL/SQL程序，查询的departments表中是否有部门号为“6”的记录，如果没有，插入新记录部门号为6，部门名称为“后勤部”。如果有，显示查询结果。湖南科技学院计算机与通信工程系实 验 报 告实验项目PL/SQL编程课程名称Oracle数据库成 绩时 间2012年12月 日 第15周 第 9-12节地 点E305姓 名学 号班 级一、实验要求在Oracle 10g中综合运用PL/SQL中各种程序控制结构、系统函数、异常处理语句以及DML语句完成思考题部分的PL/SQL程序编写。二、实验目的（1） 熟练掌握PL/SQL程序设计的基本知识。（2） 熟练掌握PL/SQL中控制结构的使用。具体包括选择结构语句（IF语句和CASE语句），循环结构（四种循环结构）。（3） 熟练使用PL/SQL中系统函数。（4） 掌握PL/SQL中异常处理语句的使用（5） 掌握PL/SQL中SELECT语句和DML语句的综合运用。三、实验环境 硬件设备：PC机一台 操作系统：Windows XP 应用工具：Oracle 10g四、实验内容及结果（1） 以SYS用户登录系统。代码如下：sqlplus sys/jsjorcl as sysdba;（2） 创建用户，指定密码、表空间。用户名命名以本人姓名的缩写+_+学号最后两位。代码如下：create user yh1_45identified by qq11111default tablespace systemtemporary tablespace temp;（3） 为该用户授予sysdba、connect、resource权限。代码如下：grant unlimited tablespace to yh1_45;（4） 以自己的用户身份登录。代码如下：quit;sqlplus yh1_45/qq11111 as sysdba;（5） 在本用户方案中创建表departments, 并为该表输入若干数据。程序代码如下：create table yh1_45.departments(Dep_id number primary key, Dep_name varchar2(20);Insert into yh1_45.departments(dep_id,dep_name)values (1,销售部);Insert into yh1_45.departments(dep_id,dep_name)values (2,行政部);Insert into yh1_45.departments(dep_id,dep_name)values (3,财务部);Insert into yh1_45.departments(dep_id,dep_name)values (4,人事部);Select * from yh1_45.departments;执行结果如下：（6） 在本用户方案中创建表employees, 并为该表输入若干数据。程序代码如下：create table yh1_45.employees(emp_id number primary key, emp_name varchar2(20),sex varchar2(20),title varchar2(20),wage number,idcard varchar2(12),dep_id number ,foreign key(dep_id) references yh1_45.Departments(Dep_id);insert into yh1_45.employees values(1,张三,女,销售部经理,10000,201201,1);insert into yh1_45.employees values(2,李四,男,销售部职员,2000,201202,1);insert into yh1_45.employees values(3,王麻子,男,行政部经理,12000,201203,2);insert into yh1_45.employees values(4,钱六,女,财务部经理,20000,201204,3);insert into yh1_45.employees values(5,刘七,男,人事部经理,13000,201205,4);select * from yh1_45.employees;执行结果如下：（7） 编写PL/SQL程序，使用LOOPEXITEND语句计算1100之间所有偶数之和。程序代码如下：SET ServerOutput ON;DECLARE v_Num INTEGER := 2; v_Sum INTEGER := 0;BEGIN LOOP v_Sum := v_Sum + v_Num; dbms_output.put_line(v_Num); IF v_Num = 100 THEN EXIT; END IF; dbms_output.put_line( + ); v_Num := v_Num + 2; END LOOP; dbms_output.put_line( = ); dbms_output.put_line(v_Sum);END;/执行结果如下：（8） 编写PL/SQL程序，查询5号员工工资，如果工资小于3000，则加200员工资，并提示信息“5号员工工资已更新。”，如果工资大于3000，则提示信息“5号员工工资为XXX，已达到规定标准。”程序代码如下：declare v_wage yh1_45.employees.wage%type;beginselect wage into v_wagefrom yh1_45.employeeswhere emp_id=5;if v_wage 3000 thenupdate yh1_45.employees set wage=wage+200 where v_wage3000;dbms_output.put_line(5号员工工资已更新。);elsedbms_output.put_line(5号员工工资为|v_wage|，已达到规定 标准。);end if;end;/执行结果如下：编写PL/SQL程序，查询1号员工的工资，使用CASE语句输出其工资等级。工资小于等于3000，等级为“低”；工资大于3000，小于5000，等级为”中”；工资大于等于5000，等级为高；程序代码如下：set serveroutput on;declarevarId number;varWage number;resulte varchar2(20);begin select emp_idinto varIdfrom yh1_45.employeeswher