MySQL数据库 *实验17存储过程

青春之码 用智慧去破解更多可能

<<<<<<【注】本实验有导入编写好的数据库xscj，xs表在本次有用到，下面展示它的表结构

1、以下是一个过程get_a_character的部分代码，需要大家完善代码。该过程实现输入任意一个字符串(s)，该过程执行后从s字符串中任意抽取一个字符，并将此字符返回给变量w

可以通过以下语句测试get_word过程是否编写成功

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< get_word过程

delimiter $$
drop procedure if exists get_word;
create procedure get_word(in s char(100),out w char(1))
begin
declare len tinyint;
set len = char_length(s);
set @a = floor(rand()*len+1);
set w = substring(s,@a,1);
end $$
delimiter ;

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< get_a_character过程

delimiter $$
drop procedure if exists get_a_character;
create procedure  get_a_character(in s char(100),out w char(1))
begin
call get_word(s,w);
end  $$
delimiter ;
set @x = '刘王张赵李钱朱何孙曾陈吴黄刘邓周毛江胡';

call get_a_character(@x,@k);
select @k;

2、编写存储过程get_name,该过程根据cd变量的值判断名字是1字还是2字，再根据xb的值判断m是取m2（男）的值还是取m1（女）的值

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< get_name过程

delimiter $$
drop procedure if exists get_name;
create procedure  get_name(out m varchar(2),out xb int)
begin
declare cd int;
declare ms1,ms2 varchar(50);
declare m1,m2,m3 varchar(2);
set ms1='依秋香巧紫萱莉玉碧丽念雅红燕艳莲荷蕾紫莹颖琪';
set ms2='澄德海超阳昌瀚亮锋涵煦杰俊诚毅峰衍浩广邈言博畅';
set xb = floor(rand()*2);
set cd = floor(rand()*2+1);
    if  xb = 0 then
          call get_word(ms1,m1); 
          call get_word(ms1,m2); 
          set m3 = concat(m1,m2);
    elseif xb = 1 then
          call get_word(ms2,m1); 
          call get_word(ms2,m2); 
          set m3 = concat(m1,m2);
    end if;
    if cd = 1 then set m = m1;
    elseif cd = 2 then set m = m3;
    end if;
end $$
delimiter ;
call get_name(@name,@sex);
select @name,@sex;

3、定义一个过程main

可以通过以下语句测试main过程是否编写成功

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< main过程

delimiter $$
drop procedure if exists main;
create procedure main()
begin
declare x varchar(100);
declare xing,ming,xm varchar(3);
declare sex,n int;
declare xh varchar(8);
create  table xs2 as select 学号,姓名,性别 from xs where 1<>1 ;
set xh = '191100';
set n = 1;
while n<=20 do
 call get_a_character(@x,@k);
 set xing = @k;
 call get_name(@name,@sex);
 set ming = @name;
 set sex = @sex;
 set xm = concat(xing,ming);
 insert into xs2 values(xh,xm,sex);
 set n = n+1;
set xh = xh +1;
end while;
end $$
delimiter ;
call main();
select * from xs2;
+--------+--------+------+
| 学号   | 姓名   | 性别 |
+--------+--------+------+
| 191100 | 陈颖莲 |    0 |
| 191101 | 刘香秋 |    0 |
| 191102 | 胡香   |    0 |
| 191103 | 李博   |    1 |
| 191104 | 邓玉莹 |    0 |
| 191105 | 钱毅德 |    1 |
| 191106 | 陈蕾   |    0 |
| 191107 | 刘巧燕 |    0 |
| 191108 | 孙依   |    0 |
| 191109 | 吴香   |    0 |
| 191110 | 曾玉红 |    0 |
| 191111 | 毛萱碧 |    0 |
| 191112 | 王琪   |    0 |
| 191113 | 刘邈   |    1 |
| 191114 | 张锋   |    1 |
| 191115 | 江燕   |    0 |
| 191116 | 孙莲蕾 |    0 |
| 191117 | 刘莹   |    0 |
| 191118 | 何德峰 |    1 |
| 191119 | 张德畅 |    1 |
+--------+--------+------+
20 rows in set (0.00 sec)

存储过程附加作业

delimiter $$
drop procedure if exists get_date;
create procedure get_date(out t date)
begin
declare s1 int;
set s1 = round(rand()*66+10);
set @year = year(now()) - s1;
set @month = round(rand()*11+1);
if @month = 1 or @month =3 or @month = 5 or @month = 7
   or @month = 8 or @month = 10 or @month =12
then set @day = round(rand()*31+1);
elseif @month = 2 then
set @day = round(rand()*29+1);
else set @day = round(rand()*30+1);
end if;
set t = concat(@year,'-',@month,'-',@day);
end $$
delimiter ;
call get_date(@date);
select @date;


delimiter $$
drop procedure if exists main1;
create procedure main1()
begin
declare x varchar(100);
declare xing,ming,xm varchar(3);
declare sex,n int;
declare xh varchar(8);
drop table if exists xs1;
create  table xs1 as select 学号,姓名,性别,出生日期 from xs limit 0 ;
set xh = '191100';
set n = 1;
while n<=20 do
 call get_a_character(@x,@k);
 set xing = @k;
 call get_name(@name,@sex);
 set ming = @name;
 set sex = @sex;
 set xm = concat(xing,ming);
 call get_date(@date);
 insert into xs1 values(xh,xm,sex,@date);
 set n = n+1;
set xh = xh +1;
end while;
end $$
delimiter ;
call main1();
select * from xs1;

每一步都走得踏踏实实，未来才更加有底气！