MYSQL的存储过程

 

实验目的：

1. 掌握创建存储过程的方法；
2. 熟练掌握存储过程体参数传递
3. 熟练掌握存储过程语句的三大结构
4. 掌握存储过程的调用、删除和修改。 

 实验内容：

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

 创建数据库“xscj”，从“sy121.sql”中导入数据到此数据库

create database xscj;

use xscj;

source d:/sy121.sql;        从D盘中引用sy121.sql;

delimiter $$
drop procedure if exists get_a_character;
create procedure get_a_character(in s char(100),out w char(1))
begin
set @len1=char_length(s);
set @a=floor(rand()*@len1+1);
set @xing=substring(s,@a,1);
set w=@xing;
end $$
delimiter ;
call get_a_character('刘王张赵李钱朱何孙曾陈吴黄郑邓周毛江胡',@k);
select @k;
 

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

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

3.定义一个过程main

定义一个main过程，编写一个循环，循环20次，结果放在xs1的表中（xs1复制xs表的结构），需要调用get_a_cgaracter得到一个姓氏；再调用get_name得到名字和性别。最后用concat在连接起来，得到姓名。 再插入到xs1中。

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);
set xh='19000';
drop table if exists xs1;
create table xs1 as select 学号,姓名,性别 from xs;
delete from xs1;
set x='刘王张赵李钱朱何孙曾陈吴黄郑邓周毛江胡';
set n=1;
while n<21 do
call get_a_character(x,xing);
call get_name(ming,sex);
set xm=concat((select xing),(select ming)); 
select xm;
insert into xs1 values ((select xh),(select xm),sex);
set xh=xh+1;
set n=n+1;
end while;
end $$
delimiter ;
call main();
select * from xs1;

 

完整代码 

delimiter $$
drop procedure if exists get_a_character;
create procedure get_a_character(in s char(100),out w char(1))
begin
set @len1=char_length(s);
set @a=floor(rand()*@len1+1);
set @xing=substring(s,@a,1);
set w=@xing;
end $$
delimiter ;
call get_a_character('刘王张赵李钱朱何孙曾陈吴黄郑邓周毛江胡',@k);
select @k;
delimiter $$
drop procedure if exists get_name;
create procedure  get_name(out m varchar(2),out xb varchar(45))
begin
declare cd int;
declare ms1,ms2 varchar(50);
declare m1,m2,m3 varchar(2);
set ms1='依秋香巧紫萱莉玉碧丽念雅红燕艳莲荷蕾紫莹颖琪';
set ms2='澄德海超阳昌瀚亮锋涵煦杰俊诚毅峰衍浩广邈言博畅';
set cd=floor(rand()*2+1);
select cd;
set xb=floor(rand()*2);
select xb;
if xb=1 then 
call get_a_character(ms1,m1);
call get_a_character(ms1,m2);
set m3=concat((select m1),(select m2));
elseif xb=0 then 
call get_a_character(ms2,m1);
call get_a_character(ms2,m2);
set m3=concat((select m1),(select 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,@xb);
select @name,@xb;
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);
set xh='19000';
drop table if exists xs1;
create table xs1 as select 学号,姓名,性别 from xs;
delete from xs1;
set x='刘王张赵李钱朱何孙曾陈吴黄郑邓周毛江胡';
set n=1;
while n<21 do
call get_a_character(x,xing);
call get_name(ming,sex);
set xm=concat((select xing),(select ming)); 
select xm;
insert into xs1 values ((select xh),(select xm),sex);
set xh=xh+1;
set n=n+1;
end while;
end $$
delimiter ;
call main();
select * from xs1;