【Oracle】实验五 PL_SQL编程

【实验目的】

1. 熟悉PL/SQL的数据类型和书写规则
2. 熟悉控制结构和游标的使用
3. 编写和运行函数、过程和触发器

【实验内容】

编写脚本文件，调试运行脚本文件，并记录结果。

1. 本地子程序的编写及调试

1、编写一个PL/SQL块，功能用于打印学生信息。整个程序的具体要求如下：

• 在DECLARE部分完成：

1. 自定义学生信息记录类型stu_record_type，包括学号，姓名，性别，籍贯，学习成绩和活动成绩。每个元素均为可变长字符类型
2. 定义学生信息记录变量stu_record
3. 编写带一个形参的本地过程：学生信息打印过程PrintStuRecord（x stu_record_type），把形参x定义为(1)中记录类型。这个过程的代码用于实现将形参x的每个元素打印输出(提示：用dbms_output.put_line过程)。

• 在BEGIN…END部分完成：

4. 为stu_record变量的各个元素赋值如下：

学号：‘2001001’
姓名：’李新’
性别：‘m’
籍贯：‘黑龙江省哈尔滨市’
学习成绩：‘Excellent’
活动成绩：‘Good’

1. 调用本地过程，用stu_record变量作为实参

2、运行这个PL/SQL程序，查看并记录运行结果

1. 函数的编写及调试
2. 编写一个函数f_pjgz，有一个数值型参数v_deptno，根据函数调用时输入部门号，来查询scott用户下emp表中该部门所有员工的平均工资，这个平均工资作为函数的返回值。
3. 用select语句调用该函数，用来查询部门号为10的员工平均工资
4. 触发器程序的编写及调试
5. 建立对bookinfo表的DML触发器，一旦bookinfo表发生了任何变化，立即触发，对bookinfo表的数据进行统计，结果存储在数据统计表中
6. 如果没有则建立bookinfo表，选择建立在scott用户下，表结构为(bookno varchar2(36) Primary key,

bookname varchar2(40) not null,
authorname varchar2(10) not null,
publishtime date,
bookprice float)

1. 建立数据统计表major_stats，包含两个字段：书的总数和作者的总数
2. 创建触发器UpdateMajorStats，完成在bookinfo表中插入、删除和修改记录之后，对bookinfo表进行统计，结果存储在(2)建立的major_stats表中
3. 在bookinfo表中分别进行插入、删除和更新操作，每种操作执行后再查看bookinfo表和major_stats表中数据的变化

【实验记录】

编写脚本文件，调试运行脚本文件，并记录结果。

1. 本地子程序的编写及调试

1、编写一个PL/SQL块，功能用于打印学生信息。整个程序的具体要求如下：

• 在DECLARE部分完成：

1. 自定义学生信息记录类型stu_record_type，包括学号，姓名，性别，籍贯，学习成绩和活动成绩。每个元素均为可变长字符类型
2. 定义学生信息记录变量stu_record
3. 编写带一个形参的本地过程：学生信息打印过程PrintStuRecord（x stu_record_type），把形参x定义为(1)中记录类型。这个过程的代码用于实现将形参x的每个元素打印输出(提示：用dbms_output.put_line过程)。

• 在BEGIN…END部分完成：

4. 为stu_record变量的各个元素赋值如下：

学号：‘2001001’
姓名：’李新’
性别：‘m’
籍贯：‘黑龙江省哈尔滨市’
学习成绩：‘Excellent’
活动成绩：‘Good’

1. 调用本地过程，用stu_record变量作为实参

登录SCOTT用户

在桌面创建5_1.sql，用记事本打开，写入代码，完整代码及注释见下图

运行此程序

发现有乱码问题，排查后发现是字符编码错误地设置成了UTF-8，更改这个文件的字符编码为ANSI。同时错误信息“未知的SET选项‘；’”是因为在set serveroutput on后错误地添加了分号，将其删去。

2、运行这个PL/SQL程序，查看并记录运行结果

成功运行，结果如图所示

1. 函数的编写及调试
2. 编写一个函数f_pjgz，有一个数值型参数v_deptno，根据函数调用时输入部门号，来查询scott用户下emp表中该部门所有员工的平均工资，这个平均工资作为函数的返回值。

完整代码如下

1. 用select语句调用该函数，用来查询部门号为10的员工平均工资

创建函数

调用函数

1. 触发器程序的编写及调试
2. 建立对bookinfo表的DML触发器，一旦bookinfo表发生了任何变化，立即触发，对bookinfo表的数据进行统计，结果存储在数据统计表中
3. 如果没有则建立bookinfo表，选择建立在scott用户下，表结构为(bookno varchar2(36) Primary key,

bookname varchar2(40) not null,
authorname varchar2(10) not null,
publishtime date,
bookprice float)
通过查询bookinfo表来查看是否有bookinfo表

发现没有这个表，所以按题干表结构在scott下建立这个表

1. 建立数据统计表major_stats，包含两个字段：书的总数和作者的总数

1. 创建触发器UpdateMajorStats，完成在bookinfo表中插入、删除和修改记录之后，对bookinfo表进行统计，结果存储在(2)建立的major_stats表中

1. 在bookinfo表中分别进行插入、删除和更新操作，每种操作执行后再查看bookinfo表和major_stats表中数据的变化

插入操作：

删除操作：

更新操作：

【实验小结】

1.在本实验中，我们通过编写了一个用于打印学生信息的PL/SQL程序、编写并调试一个函数、编写并调试一个触发器，熟悉了PL/SQL编程过程和编程语句；
2.在本实验中遇到了两个问题：
①首先是编码方式问题，需要使用ANSI编码；
②其次是编写触发器时遇到了一个“ora-04901: student2,触发器/函数 不可读”问题，排查后发现是因为一开始写触发器用了一个“for each row”，但是我们触发器依附的表是一个动态表，不允许触发器主体对动态表进行查询修改，但这个限制只应用于行级触发器，去掉“for each row”即可完美解决；