mysql_技能6_储存过程

delimiter $ 结束符号,navicat无作用

– 储存过程
CREATE PROCEDURE procedure_name ([proc_parameter[,…]])
BEGIN
– SQL语句
END ;

call sp_name ;

drop procedure [IF EXISTS] sp_name ;

– 示例
– 定义一个局部变量，该变量的作用范围只能在 BEGIN…END 块中
DECLARE variable_name datatype(size) DEFAULT default_value

create procedure pro_test2()
begin
declare num int default 5;
select num+ 10;
end;
– 对没有declare声明过的变量赋值，该变量必须以加上@号，否则会报错 select @variables 查询

– 赋值
SET var_name = expr
CREATE PROCEDURE pro_test3()
BEGIN
DECLARE NAME VARCHAR(20);
SET NAME = ‘MYSQL’;
SELECT NAME ;
END;

CREATE PROCEDURE pro_test4()
BEGIN declare countnum int;
select count(*) into countnum from city;
select countnum AS ‘行数’;
END;

CREATE PROCEDURE pro_test1()
BEGIN
declare city_num int;
set city_num = (select COUNT(1) from city);
select city_num as ‘城市数量’;
END;

call pro_test1()

– if判断
IF search_condition THEN
statement_list
ELSE
statement_list
ELSEIF
statement_list
END IF;

– 参数传递
create procedure procedure_name([in/out/inout] 参数名 参数类型)

IN : 该参数可以作为输入，也就是需要调用方传入值 , 默认
OUT: 该参数作为输出，也就是该参数可以作为返回值
INOUT: 既可以作为输入参数，也可以作为输出参数

@description : 这种变量要在变量名称前面加上“@”符号，叫做用户会话变量，代表整个会话过程他都是有作用的，这个类似于全局变量一样。
@@global.sort_buffer_size : 这种在变量前加上 “@@” 符号, 叫做 系统变量

– 当搜索函数 或者 if使用
CASE [case_value]
WHEN when_value THEN
statement_list
ELSE
statement_list
END CASE;

– 当…时候，一直做…
WHILE search_condition DO
statement_list
END WHILE;

– 重复…直到…
REPEAT
statement_list
UNTIL search_condition END REPEAT;

– 循环
label: LOOP
statement_list

IF exit_condition THEN
	LEAVE label; 
END IF; 

END LOOP label;

leave语句，用来从标注的流程构造中退出，通常和 BEGIN … END 或者循环一起使用。下面是一个使用 LOOP 和 LEAVE 的简单例子 , 退出循环：

create procedure pro_test01()
begin
create view city_country_view as
select a.*,b.country_name from city a ,country b where a.country_id = b.country_id;
select * from city_country_view;
end;

call pro_test01;

DROP PROCEDURE sp_name ;

create procedure pro_test2()
begin
declare num int default 5;
select num+ 10;
end;

CREATE PROCEDURE pro_test3()
BEGIN
DECLARE NAME VARCHAR(20);
SET NAME = ‘MYSQL’; #赋值
SELECT NAME ;
END;

CREATE PROCEDURE pro_test5()
BEGIN
declare countnum int;
select count(*) into countnum from city; #赋值
select countnum;
END;

create procedure pro_test6()
begin
declare height int default 175;
declare description varchar(50);

if height >= 180 then set description = ‘身材高挑’;
elseif height >= 170 and height < 180 then set description = ‘标准身材’;
else set description = ‘一般身材’;
end if;

select description AS ‘身材条件’;
end;

call PRO_test6();

INSERT INTO result_number(number) VALUES(66)

#水仙花数
CREATE TABLE result_number (
id int(11) NOT NULL AUTO_INCREMENT,
number int(20) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

create procedure pro_test7()
BEGIN
DECLARE i INT;
SET i=100;
WHILE i<1000 DO
IF POWER(floor(i/100),3)+POWER(floor(i/10)%10,3)+POWER(i%10,3)=i
THEN INSERT INTO result_number(number) VALUES(i);
END IF;
SET i = i+1;
END WHILE;
END;

create procedure pro_test5(in height int , out description varchar(100))
begin
if height >= 180 then
set description=‘身材高挑’;
elseif height >= 170 and height < 180 then
set description=‘标准身材’;
else
set description=‘一般身材’;
end if;
end;

call pro_test5(168, @description)
select @description

@description : 这种变量要在变量名称前面加上“@”符号，叫做用户会话变量，代表整个会话过程他都是有作用的，这个类似于全局变量一样。

@@global.sort_buffer_size : 这种在变量前加上 “@@” 符号, 叫做 系统变量

CASE case_value
WHEN when_value THEN
statement_list
ELSE
statement_list
END CASE;

create procedure pro_test9(month int)
begin
declare result varchar(20);
case when month >= 1 and month <=3 then set result = ‘第一季度’;
when month >= 4 and month <=6 then set result = ‘第二季度’;
when month >= 7 and month <=9 then set result = ‘第三季度’;
when month >= 10 and month <=12 then set result = ‘第四季度’;
end case;

select concat(‘您输入的月份为 :’, month , ’ , 该月份为 : ’ , result) as content ;

end;
call pro_test9(8)

create procedure pro_test8(n int)
begin
declare total int default 0;
declare num int default 1;
while num<=n do
set total = total + num;
set num = num + 1;
end while;
select total;
end;

create procedure pro_test10(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;

CREATE PROCEDURE pro_test11(n int)
BEGIN
declare total int default 0;
ins: LOOP
IF n <= 0 then
leave ins;
END IF;
set total = total + n;
set n = n - 1;
END LOOP ins;
select total;
END;

call pro_test11(10)

delimiter ;