HNU数据库技术实验四存储过程

Morr0w

已于 2024-05-15 02:22:36 修改

阅读量381

点赞数 3

分类专栏： HNU 文章标签：数据库 mysql

于 2024-04-08 01:35:29 首次发布

本文链接：https://blog.csdn.net/Morr0w/article/details/137484951

版权

HNU 专栏收录该内容

3 篇文章 1 订阅

订阅专栏

实验四存储过程与事务处理

1 存储过程

1.实验目的

掌握数据库PL/SQL编程语言，以及数据库存储过程的设计和使用方法。

2.实验内容和要求

存储过程定义，存储过程运行，存储过程更名，存储过程删除，存储过程的参数传递。掌握PL/SQL编程语言和编程规范，规范设计存储过程。

3.实验重点和难点

实验重点：存储过程定义和运行。

实验难点：存储过程的参数传递方法。

4.实验过程

(1)无参数的存储过程

定义一个存储过程，更新支出表中的教材总费用。

DELIMITER $
CREATE PROCEDURE proc_calTextBookExpense()
BEGIN 
	UPDATE expenditure
	SET amount = (
    	SELECT SUM(total_price)
        FROM textbook_order)
    WHERE expenditure_type = "textbook";
END$
DELIMITER ;

执行存储过程。

CALL  proc_calTextBookExpense();

(2)有参数的存储过程

定义一个存储过程，更新特定课程的学分。

DELIMITER $
CREATE PROCEDURE proc_updateCourseCredit(cno CHAR(9), newCredit SMALLINT)
BEGIN 
    UPDATE sc
    SET Credit = newCredit
    WHERE cno = sc.cno;
END$
DELIMITER ;

执行存储过程。

call proc_updateCourseCredit('21', 100);

(3)有局部变量的存储过程

定义一个存储过程，更新某个学生的成绩信息。

/*成绩表，记录成绩信息*/
create table grade(
	sno char(9) PRIMARY KEY,
    avg_grade FLOAT,
    avg_weight_grade FLOAT
)
/*存储过程*/
DELIMITER $
CREATE PROCEDURE proc_updateGrade(sname CHAR(20)) 
BEGIN 
	/*局部变量声明*/
	DECLARE sno CHAR(9);
    DECLARE avg_grade SMALLINT;
    DECLARE avg_weight_grade FLOAT;
	/*查询sno存储到局部变量*/
	SELECT student.sno INTO sno
	FROM student 
	WHERE student.sname = sname;
	/*计算成绩表-算数平均成绩*/
	SELECT SUM(grade) / COUNT(*) INTO avg_grade
	FROM SC
	WHERE SC.sno = sno;
    /*计算加权平均成绩*/
	SELECT SUM(grade*Ccredit) / SUM(Ccredit) INTO avg_weight_grade
	FROM SC,course
	WHERE SC.sno = sno AND SC.cno = course.cno;
    /*更新成绩表*/
    INSERT INTO grade VALUES (sno, avg_grade, avg_weight_grade) 
    ON DUPLICATE KEY UPDATE grade.avg_grade = avg_grade, grade.avg_weight_grade = avg_weight_grade; 
END$
DELIMITER ;

执行该存储过程。

call proc_updateGrade('李勇');

查看执行结果。

SELECT * FROM grade;

(4)有输出参数的存储过程

定义一个存储过程，更新某个学生的加权平均成绩信息并返回该成绩。

/*存储过程*/
DELIMITER $
CREATE PROCEDURE proc_updateAvgWGrade(sname CHAR(20), out result REAL) 
BEGIN 
	/*局部变量声明*/
	DECLARE sno CHAR(9);
    DECLARE avg_weight_grade FLOAT;
	/*查询sno存储到局部变量*/
	SELECT student.sno INTO sno
	FROM student 
	WHERE student.sname = sname;
    /*计算加权平均成绩*/
	SELECT SUM(grade*Ccredit) / SUM(Ccredit) INTO avg_weight_grade
	FROM SC,course
	WHERE SC.sno = sno AND SC.cno = course.cno;
	SELECT avg_weight_grade INTO result;
    /*更新成绩表*/
    INSERT INTO grade VALUES (sno, avg_grade, avg_weight_grade) 
    ON DUPLICATE KEY UPDATE grade.avg_grade = avg_grade, grade.avg_weight_grade = avg_weight_grade; 
END$
DELIMITER ;

执行该存储过程，并得到返回结果。

set @result = 0;
call proc_updateAvgWGrade('李勇', @result);
select @result;

(5)修改存储过程

在MYSQL中，ALTER PROCEDURE 语句用于修改存储过程的某些特征。如果要修改存储过程的内容，可以先删除原存储过程，再以相同的命名创建新的存储过程；如果要修改存储过程的名称，可以先删除原存储过程，再以不同的命名创建新的存储过程。

MySQL 中修改存储过程的语法格式如下：

ALTER PROCEDURE 存储过程名 [ 特征 … ]

特征指定了存储过程的特性，可能的取值有：

CONTAINS SQL 表示子程序包含 SQL 语句，但不包含读或写数据的语句。
NO SQL 表示子程序中不包含 SQL 语句。
READS SQL DATA 表示子程序中包含读数据的语句。
MODIFIES SQL DATA 表示子程序中包含写数据的语句。
SQL SECURITY { DEFINER |INVOKER } 指明谁有权限来执行。
DEFINER 表示定义者有对存储过程中对象的访问权限，调用者就能够执行。
INVOKER 表示调用者必须拥有存储过程中的对象的访问权限，才能执行。
COMMENT ‘string’ 表示注释信息。
为存储过程添加注释信息：

ALTER PROCEDURE proc_updateAvgWGrade
COMMENT "计算指定学生的平均加权成绩";

指定只有定义者权限。

ALTER PROCEDURE proc_updateAvgWGrade MODIFIES SQL DATA SQL SECURITY  DEFINER;
SHOW CREATE PROCEDURE proc_updateAvgWGrade;

(6)删除存储过程

删除存储过程proc_updateAvgWGrade。

DROP PROCEDURE proc_updateAvgWGrade;

5.实验总结

用户自定义函数和存储过程可以可以带有IN, OUT, INOUT参数。在MYSQL中，获取OUT值需要先声明一个变量，然后执行函数或存储过程，获取结果，不能传入NULL获取结果集。存储过程不可以修改名称或内容，只可以修改其特征。用户自定义函数只能通过SELECT调用，而存储过程只能通过call调用。

2 自定义函数

1.实验目的

掌握数据库PL/SQL编程语言以及数据库自定义函数的设计和使用方法。

2.实验内容和要求

自定义函数定义，自定义函数运行，自定义函数更名，自定义函数删除，自定义函数的参数传递。掌握PL/SQL和编程规范，规范设计自定义函数。

3.实验重点和难点

实验重点：自定义函数的定义和运行。

实验难点：自定义函数的参数传递方法。

4.实验过程

(1)无参数的自定义函数

定义一个自定义函数，更新支出表的教材总费用，并返回该费用。由于MYSQL启用了二进制日志，需要修改log_bin_trust_function_creators为TRUE，表示信任存储函数创建者，不会创建写入二进制日志引起不安全事件的存储函数。

/*与1中的存储过程类似，将结果返回*/
set global log_bin_trust_function_creators=TRUE;	
DELIMITER $
CREATE FUNCTION func_calTextBookExpense() 
RETURNS REAL
BEGIN
	DECLARE	res REAL;
	UPDATE expenditure
	SET amount = (
    	SELECT SUM(total_price)
        FROM textbook_order)
    WHERE expenditure_type = "textbook";
    SELECT SUM(total_price) INTO res
    FROM textbook_order;
    RETURN res;
END$
DELIMITER ;

调用该函数，返回值以结果集的方式返回并显示。

SELECT func_calTextBookExpense();

(2)有参数的自定义函数

定义一个自定义函数，更新特定课程的容量，返回剩余容量。

DELIMITER $
CREATE FUNCTION func_updateCourseCapacity(cno CHAR(9), newCapacity SMALLINT)
RETURNS REAL
BEGIN 
	DECLARE res REAL;
    UPDATE sc_summary
    SET capacity = newCapacity
    WHERE cno = sc_summary.cno;
    SELECT capacity - snum INTO res
    FROM sc_summary
    WHERE cno = sc_summary.cno;
    RETURN res;
END$
DELIMITER ;

调用该函数。

SELECT proc_updateCourseCapacity('21', 100);

(3)有局部变量的自定义函数

定义一个函数，更新某个学生的加权平均成绩信息并返回该成绩。

DELIMITER $
CREATE FUNCTION func_updateAvgWGrade(sname CHAR(20))
RETURNS FLOAT
BEGIN 
	/*局部变量声明*/
	DECLARE sno CHAR(9);
    DECLARE avg_weight_grade FLOAT;
	/*查询sno存储到局部变量*/
	SELECT student.sno INTO sno
	FROM student 
	WHERE student.sname = sname;
    /*计算加权平均成绩*/
	SELECT SUM(grade*Ccredit) / SUM(Ccredit) INTO avg_weight_grade
	FROM SC,course
	WHERE SC.sno = sno AND SC.cno = course.cno;
    /*更新成绩表*/
    INSERT INTO grade VALUES (sno, avg_grade, avg_weight_grade) 
    ON DUPLICATE KEY UPDATE grade.avg_grade = avg_grade, grade.avg_weight_grade = avg_weight_grade; 
    /*返回结果*/
    RETURN avg_weight_grade;
END$
DELIMITER ;

调用该函数。

SELECT func_updateAvgWGrade('李勇');

(4)修改自定义函数

类似于存储过程，修改自定义函数只能修改其特性，不能修改名称或函数体。

/*添加注释*/
ALTER FUNCTION func_updateAvgWGrade COMMENT "使用函数计算指定学生的平均加权成绩";
/*修改函数为定义者权限*/
ALTER FUNCTION func_updateAvgWGrade MODIFIES SQL DATA SQL SECURITY  DEFINER;

(5)删除自定义函数

删除func_updateAvgWGrade函数。

DROP FUNCTION func_updateAvgWGrade;

5.实验总结

MYSQL中，自定义函数需要在定义时声明返回值的类型，并在函数结束后显示使用RETURN语句返回值。MYSQL的FUNCTION语句只能通过RETURN返回值，不能通过OUT参数返回多个值，且只能通过SELECT语句调用，不能使用call语句调用。

3 游标

1.实验目的

掌握PL/SQL游标的设计、定义和使用方法，理解PL/SQL游标按行操作和SQL按结果集操作的区别和联系。

2.实验内容和要求

游标定义、游标使用。掌握各种类型游标的特点、区别与联系。

3.实验重点和难点

实验重点：游标定义和使用。

实验难点：游标类型。

4.实验过程

(1)普通游标

定义一个存储过程，更新sc_summary表中每门课程的选课人数。

DELIMITER $
CREATE PROCEDURE procCursor_calSCnum()
BEGIN
	DECLARE sc_cno	CHAR(9);
	DECLARE sc_num	SMALLINT;
	DECLARE done BOOL;
	DECLARE mycursor CURSOR FOR
				SELECT Cno
				FROM sc_summary;
     DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
	OPEN mycursor;
    SELECT FALSE INTO done;
	WHILE NOT done DO/*处理每一条选课统计记录*/
		FETCH mycursor INTO sc_cno;
		/*计算选课人数*/
		SELECT COUNT(*) INTO sc_num
		FROM SC
		WHERE sc.Cno = sc_cno;
		/*更新选课人数*/
		UPDATE sc_summary
		SET sc_summary.snum = sc_num
		WHERE sc_summary.Cno = sc_cno;
    END WHILE;
	/*关闭游标*/
	CLOSE mycursor;
END$
DELIMITER ;

执行存储过程，并查询选课统计表，选课人数被更新。

CALL procCursor_calSCnum();
select * from sc_summary;

(2)REFCURSOR类型游标

REF CURSOR是动态游标，一般的CURSOR是静态游标。一般的CURSOR在定义时结果集的类型就是确定的，不可以再更改，而动态游标可以打开任何形式的结果集，取出任何类型的记录数据。在MYSQL中只能使用静态游标，定义时的SELECT语句必须确定，不可以再更改。

(3)带参数的游标

MYSQL中游标不可以携带参数。

5.实验总结

游标对数据库记录逐条处理，而不是对结果集一起处理。声明游标和游标结束处理的异常需要按顺序声明。REF CURSOR是动态游标，可以在打开该游标时指定SELECT语句产生游标的结果集，游标的结果可以使用RECORD类型保存。在MYSQL中不支持动态游标，也没有RECORD类型，游标也不可以使用参数，只能声明变量完成处理。

4. 事务处理

1. 实验目的

掌握利用存储过程进行事务处理相关操作的方法。

2. 实验内容和要求

掌握利用存储过程进行事务处理相关操作的方法。

3. 实验重点与难点

实验重点：创建存储过程，设置任务管理。

实验难点：利用存储过程进行事务处理的相关操作。

4. 实验过程

(1)创建一个存储表

CREATE TABLE account(
	account_id CHAR(6) PRIMARY KEY,
	account_name VARCHAR(20),
	balance DECIMAL(10,2) CHECK(balance>=0)
	);

(2)设计一个存储过程并应用事务控制

设计一个存储过程p_transfer用于转账，在其中进行事务控制，出现异常时能撤销事务，保证数据的一致性。

DELIMITER $$
USE bank$$
DROP PROCEDURE IF EXISTS p_transfer$$
CREATE PROCEDURE p_transfer(IN from_account CHAR(6),IN to_account CHAR(6),IN amount DECIMAL(10,2))
BEGIN
	DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
	START TRANSACTION;
	UPDATE account SET balance=balance+amount WHERE account_id=to_account;
	UPDATE account SET balance=balance-amount WHERE account_id=from_account;
	COMMIT;
	END$$
DELIMITER;

(3)验证相关操作

若有以下两个记录(‘202201’,‘张一’,1000)和(‘202202’,‘王二’,1000)，调用两次p_transfer分别转账600，查看余额。

CALL p_transfer('202201','202202',600);
CALL p_transfer('202201','202202',600);

在这里插入图片描述

5. 实验总结

TART TRANSACTION;
UPDATE account SET balance=balance+amount WHERE account_id=to_account;
UPDATE account SET balance=balance-amount WHERE account_id=from_account;
COMMIT;
END$$
DELIMITER;


#### (3)验证相关操作

若有以下两个记录('202201','张一',1000)和('202202','王二',1000)，调用两次p_transfer分别转账600，查看余额。

```mysql
CALL p_transfer('202201','202202',600);
CALL p_transfer('202201','202202',600);

[外链图片转存中…(img-MqZStBC4-1712511268144)]

5. 实验总结

了解了mysql中的如何编写和使用事务控制，进一步理解了数据库运行的原理。

Morr0w

关注

3
点赞
踩
11

收藏

觉得还不错? 一键收藏
0
评论
HNU数据库技术实验四存储过程

用户自定义函数和存储过程可以可以带有IN, OUT, INOUT参数。在MYSQL中，获取OUT值需要先声明一个变量，然后执行函数或存储过程，获取结果，不能传入NULL获取结果集。存储过程不可以修改名称或内容，只可以修改其特征。用户自定义函数只能通过SELECT调用，而存储过程只能通过call调用。掌握数据库PL/SQL编程语言以及数据库自定义函数的设计和使用方法。定义一个自定义函数，更新支出表的教材总费用，并返回该费用。
复制链接

扫一扫