作为mysql的初学者,自己看着教程视频,做的笔记,以便日后回顾复习,纯手打,可能有些误差,可指出更正。
CREATE PROCEDURE procedure_name([proc_parameter[....]])
begin
-- sql语句
end;
示例:
-- delimiter来指定哪个符号作为分隔符(也就是原来';'的作用)
delimiter $
create procedure pro_test1()
begin
select 'hello mysql';
end$
delimiter ;
call 存储过程名称;
示例: call procedure_test1();
select name from mysql.proc where db='db_name';
show procedure status;
show create procedure test.pro_test1 \G;
DROP PROCEDURE [IF EXISTS] sp_name;
declare num int default 10;
set var_name = expr[,var_name = expr]...
示例:
delimiter $
create procedure procedure_test01()
begin
declare name varchar(20);
set name = 'mysql';
select name;
end$
delimiter ;
也可以通过select…into方式进行赋值操作;
delimiter $
create procedure procedure_test02()
begin
declare num int default 0;
select count(*) into num from stu;
select num;
end$
delimiter ;
if search_condition then statement_list
[elseif search_condition then statement_list]...
[else statement_list]
end if;
示例:
根据定义的身高变量,来判断所属身材类型.
180及以上:身材高挑,
170-180:标准身材,
170及以下,一般身材
delimiter $
create procedure procedure_test03()
begin
declare hight int default 182;
declare description varchar(10) default '';
if hight>=180 then
set description = '身材高挑';
elseif hight>=170 and hight<180 then
set description = '标准身材';
else
set description = '一般身材';
end if;
select concat('身高',hight,'对应的身材类型为',description);
end$
delimiter ;
create procedure procedrue_test04([in/out/inout])
输入参数:in(默认的,可不加修饰),输出参数:out
输入输出参数:inout
需求:
根据传入的身高参数,来显示对应的身材.
-- 有输入,没输出
delimiter $
create procedure procedure_test04(in hight int)
begin
declare description varchar(10) default '';
if hight>=180 then
set description = '身材高挑';
elseif hight>=170 and hight<180 then
set description = '标准身材';
else
set description = '一般身材';
end if;
select concat('身高',hight,'对应的身材类型为',description);
end$
delimiter ;
-- 有输入,有输出
delimiter $
create procedure procedure_test05(in hight int,out description varchar(10))
begin
if hight>=180 then
set description = '身材高挑';
elseif hight>=170 and hight<180 then
set description = '标准身材';
else
set description = '一般身材';
end if;
end$
delimiter ;
-- 调用该存储过程并获取返回值
call procedure_test05(178,@desc);
select @desc;
小知识 @description:这种变量要在变量名称前加‘@’符号,叫做用户会话变量,代表整个会话都是有效的,只要不关闭连接,类似于全局变量. @@global.sort_buffer_size:这种变量加上‘@@’符号,叫做系统变量.
方式一:
CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list]...
[ELSE statement_list]
END CASE;
方式二:
CASE
WHEN search_condition THEN statement_list
[WHEN search_condition THEN statement_list]...
[ELSE statement_list]
END CASE;
/*
需求:
给定一个月份,然后计算是哪一季度
*/
delimiter $
create procedure procedure_test06(mon int,out jidu varchar(10))
begin
case mon
when mon>=1 and mon<=3 then
set jidu='第一季度';
when mon>=4 and mon<=6 then
set jidu='第二季度';
when mon>=7 and mon<=9 then
set jidu='第三季度';
else
set jidu='第四季度';
end case;
select concat(mon,'是',jidu);
end$
delimiter ;
while search_condition do
statement_list
end while;
需求:
计算从1到n的累加值
delimiter $
create procedure procedure_test07(n int)
begin
declare total int default 0;
declare start int default 1;
while start<=n do
set total=total+start;
set start=start+1;
end while;
select total;
end$
delimiter ;
有条件的循环控制语句,当满足条件时退出循环. while循环是满足条件才执行,repeat是满足条件则退出.
repeat
statement_list
until search_condition(注意这里没有';')
end repeat;
需求:
计算从1到n的累加值
delimiter $
create procedure procedure_test08(n int)
begin
declare total int default 0;
repeat
set total=total+n;
set n=n-1;
until n<=0
end repeat;
select total;
end$
delimiter ;