MYSQL存储过程

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;