create database proce;
use proce;
create table test(a int);
select * from test;
-- t1、创建查询test表的存储过程
delimiter //
create procedure t1()
begin
select * from test;
end //
delimiter ;
call t1();
drop procedure t1;
-- t2、使用局部变量
delimiter //
create procedure t2()
begin
declare a int default 0;
declare b int default 0;
set a = 1;
select 2 into b;
select a + b;
end //
delimiter ;
call t2();
-- t3、使用局部变量
delimiter //
create procedure t3()
begin
declare a int default 0;
declare b int default 0;
set a = 3;
select 4 into b;
select a + b;
end //
delimiter ;
call t3();
set @a = 1;
select 2 into @b;
select @a + @b;
-- t4、使用局部变量与用户变量
delimiter //
create procedure t4()
begin
declare a int default 0;
declare b int default 0;
set a = 3;
select 4 into b;
set @final = a + b;
end //
delimiter ;
call t4();
select @final;
-- t5、使用参数
delimiter //
create procedure t5(in a int, in b int)
begin
select a + b into @final;
end //
delimiter ;
call t5(4,5);
select @final;
-- t6、使用OUT类型参数
delimiter //
create procedure t6(in a int, in b int, out c int)
begin
select a + b into c;
end //
delimiter ;
call t6(3,4,@final);
select @final;
-- t7、根据指定值计算两个参数的四则运算中某种运算的结果
delimiter //
create procedure t7(in a int, in b int, in iCalSel int)
begin
if iCalSel = 1 then set @final = a + b;
elseif iCalSel = 2 then set @final = a - b;
elseif iCalSel = 3 then set @final = a * b;
elseif iCalSel = 4 then set @final = a / b;
else set @final = '参数错误';
end if;
end //
delimiter ;
call t7(4,2,6);
select @final;
-- t8、得到第十次循环的循环角标值
delimiter //
create procedure t8()
begin
declare i int default 1;
while i <= 10 do
set i = i + 1;
end while;
set @final = i;
end //
delimiter ;
call t8();
select @final;
-- t9、计算从1到5的累加和
delimiter //
create procedure t9()
begin
declare i int default 1;
set @iFinal = 0;
while i <= 5 do
set @iFinal = @iFinal + i;
set i = i + 1;
end while;
end //
delimiter ;
call t9();
select @iFinal;
-- t10、计算从1到5的累乘积
delimiter //
create procedure t10()
begin
declare i int default 1;
set @iFinal = 1;
while i < 5 do
set i = i + 1;
set @iFinal = @iFinal * i;
end while;
end //
delimiter ;
-- t11、根据指定值选择计算1到5的累加和或者累乘积
delimiter //
create procedure t11(in iCalSel int)
begin
declare i int default 1;
set @iFinal = 1;
if iCalSel = 1 then
-- 累加
while i < 5 do
set i = i + 1;
set @iFinal = @iFinal + i;
end while;
elseif iCalSel = 2 then
-- 累乘
while i < 5 do
set i = i + 1;
set @iFinal = @iFinal * i;
end while;
else set @iFinal = '参数错误';
end if;
end //
delimiter ;
call t11(2);
select @iFinal;
-- t12、根据指定值选择计算1到指定值的累加和或者累乘积
delimiter //
create procedure t12(in iCalSel int,in iLimit int)
begin
declare i int default 1;
set @iFinal = 1;
if iCalSel = 1 then
-- 累加
while i < iLimit do
set i = i + 1;
set @iFinal = @iFinal + i;
end while;
elseif iCalSel = 2 then
-- 累乘
while i < iLimit do
set i = i + 1;
set @iFinal = @iFinal * i;
end while;
else set @iFinal = '参数错误';
end if;
end //
delimiter ;
call t12(2,10);
select @iFinal;
prepare 预处理名称 from 'select * from test;';
execute 预处理名称;
set @vcode = 'select * from test;';
set @vcode = 'desc test';
prepare 预处理名称 from @vcode;
execute 预处理名称;
-- t13、为test表添加c1到c10十个整数型字段
-- alter table test add c1 int;
-- alter table test add c2 int;
delimiter //
create procedure t13()
begin
declare i int default 1;
set @final = '';
while i <= 10 do
set @final = concat('alter table test add c',i,' int');
prepare zx from @final;
execute zx;
set i = i + 1;
end while;
end //
delimiter ;
call t13();
desc test;
-- t14、用循环语句将c1到c10十个字段删除
-- alter table test drop c1;
delimiter //
create procedure t14()
begin
declare i int default 1;
set @final = '';
while i<=10 do
set @final = concat('alter table test drop c',i);
prepare zx from @final;
execute zx;
set i = i + 1;
end while;
end //
delimiter ;
call t14();
desc test;
-- t15、按照指定数值选择添加还是删除指定个数个字段
delimiter //
create procedure t15(in iSel int, in iLimit int)
begin
declare i int default 1;
set @final = '';
if iSel = 1 then
-- 增加字段
while i <= iLimit do
set @final = concat('alter table test add c',i,' int');
prepare zx from @final;
execute zx;
set i = i + 1;
end while;
elseif iSel = 2 then
-- 删除字段
while i<=iLimit do
set @final = concat('alter table test drop c',i);
prepare zx from @final;
execute zx;
set i = i + 1;
end while;
else set @final = '参数错误';
end if;
end //
delimiter ;
call t15(2,5);
desc test;
-- t16、按照指定数值选择添加还是删除指定个数个字段
-- 指定字段名以及数据类型
delimiter //
create procedure t16(in iSel int, in iLimit int, in vName varchar(100), in vType varchar(100))
begin
declare i int default 1;
set @final = '';
if iSel = 1 then
-- 增加字段
while i <= iLimit do
set @final = concat('alter table test add ',vName,i,' ',vType);
prepare zx from @final;
execute zx;
set i = i + 1;
end while;
elseif iSel = 2 then
-- 删除字段
while i<=iLimit do
set @final = concat('alter table test drop ',vName,i);
prepare zx from @final;
execute zx;
set i = i + 1;
end while;
else set @final = '参数错误';
end if;
end //
delimiter ;
call t16(2,10,'题号','float');
desc test;