这里写自定义目录标题

  • 一 实验目的
  • 二 实验平台
  • 三 实验内容和要求
  • 3.1 创建存储过程
  • 3.2 查看存储过程
  • 3.3 修改存储过程
  • 3.4 删除存储过程
  • 3.5 创建函数
  • 3.6 查看函数
  • 3.7 删除函数


一 实验目的

  1. 掌握 MySQL 块结构、MySQL 的基本语法、MySQL 的流程控制语句；
  2. 掌握 MySQL 块中使用游标的方法；
  3. 掌握 MySQL 异常处理技术。
  4. 掌握存储过程、函数数据库对象的基本作用；
  5. 掌握存储过程、函数的建立、修改、查看、删除等操作

二 实验平台

  1. 操作系统： Windows 10；
  2. 数据库MySQL Server 8.0，MySQL Workbench

三 实验内容和要求

任务中涉及的数据表是在**员工医疗保险系统（实验 03）**中给出的表。

3.1 创建存储过程

  1. 使用 MySQL 命令行或者 workbench(或者 Navicat for MySQL)为员工医疗
    保险系统（实验 03 创建的 7 个表）创建存储过程“sex_pro”，通过传入参数传入性
    别(男或女)，显示员工表“staff”中不同性别的员工人数。并调用该存储过程，测试
    运行结果。
-- 3.1.1
delimiter //
create procedure sex_pro(in sex char,out num int)
begin
	set num = (select count(*) from staff where ssex=(sex));
end;
call sex_pro('男',@num);
select @num;
  1. 创建一个存储过程“cno_pro”,通过输入医保卡号，输出该医保卡的全部
    消费信息。并调用该存储过程，测试运行结果。
-- 3.1.2
delimiter //
create procedure cno_pro(in cno char)
begin
	select * from consume where cno = (cno);
end;
call cno_pro();
-- 测试2题
delimiter //
create procedure aaa(in cno char)
begin
	select * from staff where sno = (cno);
end;
call aaa(1);
  1. 创建一个存储过程“insert_pro”，该存储过程可以向员工表“staff”中插入
    员工信息；如果向员工表“staff”插入已经存在的员工编号，自定义异常处理，则显示自定义错误消息“MySQL 1062：该员工编号已存在”，否则显示“员工信息插入成功！”（注：Error: 1062 SQLSTATE: 23000 (ER_DUP_ENTRY)）。该题选做！

3.2 查看存储过程

  1. 使用 MySQL 命令行或者 workbench(或者 Navicat for MySQL)查看员工医
    疗保险数据库中的所有存储过程的状态、以及创建存储过程的语句；
-- 3.2.1
 show procedure status where db='db_test_学号';
  1. 使用 MySQL 命令行或者 workbench(或者 Navicat for MySQL)从数据字典
    中查看员工医疗保险数据库中的所有存储过程
-- 3.2.2 不确定
SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='db_test_学号' AND ROUTINE_TYPE='PROCEDURE';

3.3 修改存储过程

  1. 使用MySQL命令行或者workbench(或者Navicat for MySQL)修改存储过程
    “sex_pro”，通过输入参数传入性别(男或女)，通过输出参数得到员工表“staff”中不
    同性别的员工人数，并执行该存储过程，显示员工表“staff”中不同性别的员工人数。
-- 3.3.1
-- ALTER {PROCEDURE | FUNCTION}……语句只能改变存储过程的特征，
-- 不能修改过程的参数以及过程体。如果想做这样的修改，
-- 必须先使用DROP PROCEDURE 删除过程，然后使用and CREATE PROCEDURE重建过程。
drop procedure sex_pro;
delimiter //
create procedure sex_pro(in sex char,out num int)
begin
	if sex='男' then
    set num = (select count(*) from staff where ssex='女');
	end if;
	if sex='女' then 
    set num = (select count(*) from staff where ssex='男');
    end if;
end;
call sex_pro('女',@num);
select @num;

3.4 删除存储过程

  1. 使用 MySQL 命令行或者 workbench(或者 Navicat for MySQL)删除存储过
    程“sex_pro”。
drop procedure sex_pro
  1. 使用 MySQL 命令行或者 workbench(或者 Navicat for MySQL)删除存储过
    程“cno_pro”。
drop procedure cno_pro;

3.5 创建函数

  1. 使用 MySQL 命令行或者 workbench(或者 Navicat for MySQL)创建函数
    “num_fun”，该函数可以计算 1+3+5+7+9+……+N,其中 N 位任意的正整数；并调
    用该函数，测试运行结果。
-- 3.5.1
delimiter //
create function num_fun(last_num int) returns int
begin   
	declare js int;
    declare i int;
    set i = 1;
	set js = 0;
    while i<=last_num do
		set js=js+i;
		set i=i+2;
    end while;
    return js;
end
//
select num_fun(9);
drop function if exists num_fun
-- This function has none of DETERMINISTIC, NO SQL解决办法
show variables  like  'log_bin_trust_function_creators';
set  global log_bin_trust_function_creators = 1 ;
show variables  like  ' log_bin_trust_function_creators ';
  1. 使用 MySQL 命令行或者 workbench(或者 Navicat for MySQL)在员工医疗
    保险系统（实验 03 创建的 7 个表）数据库中创建函数“card_fun”，通过传入参数
    传入医保类型(企业、事业或是灵活就业)，返回医保卡表“card”中该类型的医保卡
    数量，并调用该函数，测试运行结果。
-- 3.5.2
delimiter //
create function card_fun(YBtype char(10)) returns int
begin  
	return (select count(*) from card where ctype=(YBtype));
end
//
select card_fun('企业');
drop function if exists card_fun;
  1. 使用 MySQL 命令行或者 workbench(或者 Navicat for MySQL)在员工医疗
    保险系统（实验 03 创建的 7 个表）数据库中创建函数“staff_fun”，通过传入参数
    传入员工的编号，根据传入的员工编号，检查该员工是否存在。如果存在，则返
    回 TRUE，否则返回 FALSE，并调用该函数，测试运行结果。
-- 3.5.3 不完善
delimiter //
create function staff_fun(insno int) returns boolean
begin  
    if (select count(sno) from staff where sno=(insno))=0 then
    return false;
    else return true;
    end if;
end
//
select staff_fun(22);
drop function if exists staff_fun;
  1. 使用 MySQL 命令行或者 workbench(或者 Navicat for MySQL)在员工医疗
    保险系统（实验 03 创建的 7 个表）数据库中创建函数“sex_fun”，利用传入参数传
    入性别(男或女)，返回员工表“staff”中不同性别的员工人数，并调用该函数，注意
    比较与存储过程“sex_pro”的差别。
-- 3.5.4
delimiter //
create function sex_fun(insex char)returns int
begin
	declare num int;
	if insex='男' then
    set num = (select count(*) from staff where ssex='男');
	end if;
	if insex='女' then 
    set num = (select count(*) from staff where ssex='女');
    end if;
    return num;
end;
select sex_fun('男');
drop function if exists sex_fun;

3.6 查看函数

  1. 使用 MySQL 命令行或者 workbench(或者 Navicat for MySQL)查看员工医
    疗保险数据库中的上面创建的所有函数的状态、以及创建函数的语句；
-- 3.6.1
show function status where db='db_test_学号';
  1. 使用 MySQL 命令行或者 workbench(或者 Navicat for MySQL)从数据字典
    中查看员工医疗保险数据库中的上面所创建的所有函数。
-- 3.6.2 不确定
SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='db_test_学号' AND ROUTINE_TYPE='function';

3.7 删除函数

  1. 使用 MySQL 命令行或者 workbench(或者 Navicat for MySQL)删除函数
    “staff_fun”。
drop function if exists staff_fun;
  1. 使用 MySQL 命令行或者 workbench(或者 Navicat for MySQL)删除函数
    “card_fun”。
drop function if exists card_fun