【MySQL】《数据库系统》-实验项目9-存储过程与函数

实验项目
实验1  实验环境与操作流程
实验2  数据定义、查询与更新
实验3  基本表的定义、删除与修改
实验4  数据的插入、修改和删除
实验5  数据的查询
实验6  数据的视图
实验7  完整性语言
实验8  触发器
实验9  存储过程与函数
实验10  SQL语句1（创建、插入、查询）
实验11  SQL语句2（查询）
实验12  SQL语句3（视图、完整性、关系代数查询）

实验9  存储过程与函数

一、实验目的

1. 掌握数据库SQL语言；

2. 掌握数据库存储过程的设计和使用方法。

3. 掌握自定义函数的定义和运行。

二、实验要求

1. 熟练掌握存储过程的定义与运行；

2. 规范设计存储过程；

3. 熟练掌握自定义函数的定义和运行。

三、实验内容

1．存储过程：

利用存储过程实现下面的应用：从账户1转指定数额的款项到账户2中。假设账户关系表为Account(Accountnum, Total)。参考例8.9。

1. 创建基本表Account，并插入数据（1,10000），（2,20000），（3,30000），（4,40000）。
2. 创建存储过程transfer(inAccount,outAccount,amount)，并查看当前数据库的全部存储过程。
3. 执行存储过程transfer（1,2,10000）（表示从账户2往账户1转账10000元），查看基本表Account中的数据。

2．函数：

通过函数来实现查询账户的余额。

1. 创建函数get_total(AccountID)，并查看当前数据库的全部函数。
2. 执行函数语句get_total(1)、get_total(2)、get_total(accountnum)（表示分别得到账号1、账号2、所有账号的余额）。

实验指导：

• 1．存储过程：

利用存储过程实现下面的应用：从账户1转指定数额的款项到账户2中。假设账户关系表为Account(Accountnum, Total)。参考例8.9。

• 创建基本表Account，并插入数据（1,10000），（2,20000），（3,30000），（4,40000）。

• 创建存储过程transfer(inAccount,outAccount,amount)，并查看当前数据库的全部存储过程。

• 执行存储过程transfer（1,2,10000）（表示从账户2往账户1转账10000元），查看基本表Account中的数据。

• 2．函数：

通过函数来实现查询账户的余额。

• 创建函数get_total(AccountID)，并查看当前数据库的全部函数。

• 执行函数语句get_total(1)、get_total(2)、get_total(accountnum)（表示分别得到账号1、账号2、所有账号的余额）。

实验9代码：

create table Account(
Accountnum int primary key,
Total double);

insert into Account values(1,10000),(2,20000),(3,30000),(4,40000);

show procedure status where db='acc';

create procedure transfer(inAccount int,outAccount int,amount double)
       begin
         declare totalDepositOut double;
         declare totalDepositIn double;
        declare inAccountnum int;
         select Total into totalDepositOut from Account where accountnum=outAccount;
         if totalDepositOut is null then
           rollback;
         end if;
         if totalDepositOut<amount then
           rollback;
        end if;
         select Accountnum into inAccountnum from Account where accountnum=inAccount;
         if inAccountnum is null then
           rollback;
         end if;
         update Account set total=total-amount where accountnum=outAccount;
        update Account set total=total+amount where accountnum=inAccount;
        commit;
      end //


show function status where db='acc';

create function get_total(AccountID int) returns double
	begin
		declare total_amount double default null;
		select total into total_amount from Account where Accountnum=AccountID;
		return total_amount;
	end //



错误：
执行创建函数的sql语句时，提示:This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled

原因：
我们创建函数时必须指定我们的函数是否是
DETERMINISTIC 不确定的
NO SQL 没有SQl语句，当然也不会修改数据
READS SQL DATA 只是读取数据，当然也不会修改数据
MODIFIES SQL DATA 要修改数据
CONTAINS SQL 包含了SQL语句

解决方法：
1、在mysql数据库中执行以下语句 （临时生效，重启后失效）
set global log_bin_trust_function_creators=TRUE;
2、在配置文件/etc/my.cnf的[mysqld]配置log_bin_trust_function_creators=1

create procedure transfer1(inAccount int,outAccount int,amount double)
       begin
         declare totalDepositOut double;
         declare totalDepositIn double;
         declare inAccountnum int;        
         loop_label: loop
         start transaction;
         select Total into totalDepositOut from Account where accountnum=outAccount;
         if totalDepositOut is null then
           rollback;
           leave loop_label;
         end if;
         if totalDepositOut<amount then
           rollback;
           leave loop_label;
         end if;
         select Accountnum into inAccountnum from Account where accountnum=inAccount;
         if inAccountnum is null then
           rollback;
           leave loop_label;
         end if;
         update Account set total=total-amount where accountnum=outAccount;
         update Account set total=total+amount where accountnum=inAccount;
         commit;
         leave loop_label;
         end loop;
      end //