实验四、存储子程序设计(2 学时)
1、实验目的
(1)掌握存储存储过程/用户定义函数的概念,学会编写简单的存储过程/用户定义函 数及其使用。
(2)了解存储过程/用户定义函数的 JDBC 调用。
2、实验性质 验证性实验
3、实验导读
略
4、实验内容
(1)、简述 MySQL 数据库存储过程、函数的概念和区别。
答:MySQL 数据库存储过程: SQL 语句需要先编译然后执行,而存储过程(Stored Procedure)是一组为了完成特定 功能的 SQL 语句集,经编译后存储在数据库中,用户通过指定存储过程的名字并给定参数 (如果该存储过程带有参数)来调用执行它。 存储过程是可编程的函数,在数据库中创建并保存,可以由 SQL 语句和控制结构组成。 当想要在不同的应用程序或平台上执行相同的函数,或者封装特定功能时,存储过程是非常 有用的。数据库中的存储过程可以看作是对编程中面向对象方法的模拟,它允许控制数据的 访问方式。
存储过程和函数的区别:
1、一般来说,存储过程实现的功能要复杂一点,而函数的实现的功能针对性比较强。 存储过程,功能强大,可以执行包括修改表等一系列数据库操作;用户定义函数不能用于执 行一组修改全局数据库状态的操作。
2、对于存储过程来说可以返回参数,如记录集,而函数只能返回值或者表对象。函数 只能返回一个变量;而存储过程可以返回多个。存储过程的参数可以有 IN,OUT,INOUT 三 种类型,而函数只能有 IN 类~~存储过程声明时不需要返回类型,而函数声明时需要描述返 回类型,且函数体中必须包含一个有效的 RETURN 语句。
3、存储过程,可以使用非确定函数,不允许在用户定义函数主体中内置非确定函数。 4、存储过程一般是作为一个独立的部分来执行( CALL 语句执行),而函数可以作为 查询语句的一个部分来调用(SELECT 调用),由于函数可以返回一个表对象,因此它可以 在查询语句中位于 FROM 关键字的后面。 SQL 语句中不可用存储过程,而可以使用函数。
(2)、在学生-课程数据库中,编写存储过程,通过输入参数接收学生学号信息,通过 输出参数输出学生相应的平均成绩;
答:
-- 通过输入参数接收学生学号信息,通过输出参数输出学生相应的平均成绩
CREATE PROCEDURE STUDENT_AVERAGEGRADE_SEARCH(IN p_sno CHAR(7),OUT p_avggrade DECIMAL(5,2))
BEGIN
SELECT SAvgGrade INTO p_avggrade FROM student WHERE sno = p_sno;
END;
# 调用存储过程
CALL STUDENT_AVERAGEGRADE_SEARCH('9512101', @p_avggrade);
SELECT @p_avggrade;
(3)、编写 JDBC 应用程序,调用上题编写的存储过程,输出学生相应的平均成绩;
答:
public static void main(String[] args) {
Connection conn = null;
CallableStatement cs = null;
ResultSet rs = null;
String sno = "9512101";
try {
conn = DatabaseBean.getConnection();
cs = conn.prepareCall("{ call STUDENT_AVERAGEGRADE_SEARCH(?,?) }");
cs.setString(1, sno); //注册一个输出参数 cs.registerOutParameter(2, java.sql.Types.DECIMAL); cs.execute();
System.out.println("学号为 " + sno + " 的平均成绩为 " + cs.getString(2)); } catch (SQLException ex) {
ex.printStackTrace();
} finally {
DatabaseBean.close(rs, cs, conn); }
}
(4)、在学生-课程数据库中,编写函数,通过输入参数接收学生学号信息,通过返回 值输出学生相应的平均成绩;
-- 函数写法
CREATE FUNCTION fun_student_grade_search(p_sno CHAR(7)) RETURNS DECIMAL(5,2) reads sql data -- 从数据库中读取数据,但不修改数据
BEGIN
DECLARE p_avggrade DECIMAL(5,2);
SELECT SAvgGrade INTO p_avggrade FROM student WHERE sno = p_sno;
RETURN p_avggrade;
END;
SELECT fun_student_grade_search('9512102') as SAvgGrade;
(5)、编写 JDBC 应用程序,调用上题编写的函数,输出学生相应的平均成绩;
public static void main(String[] args) {
Connection conn = null;
CallableStatement cs = null;
ResultSet rs = null;
String sno = "9512105";
try {
conn = DatabaseBean.getConnection(); //返回值放到语句前面
cs = conn.prepareCall("{ ? = call fun_student_grade_search(?) }");
cs.registerOutParameter(1, java.sql.Types.DECIMAL);
cs.setString(2, sno); cs.execute(); System.out.println("学号为 " + sno + " 的平均成绩为 " + cs.getString(1));
} catch (SQLException ex) {
ex.printStackTrace();
} finally { DatabaseBean.close(rs, cs, conn); } }