一:定义和区别
存储过程和函数是 事先经过编译并存储在数据库中的一段 SQL 语句的集合。存储过程是可以编程的。
存储过程和函数的区别在于函数必须有返回值,而存储过程没有。
函数 : 是一个有返回值的过程 ;
过程 : 是一个没有返回值的函数 ;
二:基本语法:
1)创建存储过程
-- 语法
CREATE PROCEDURE procedure_name ([proc_parameter[,...]])
begin
-- SQL语句
end
-- 语法说明:
-- procedure_name:存储过程名称
-- [proc_parameter[,...]]:存储过程所需要的参数
-- 举例
delimiter $
create procedure pro_test1()
begin
select 'Hello Mysql' ;
end$
delimiter ;
-- 注意:DELIMITER
-- 该关键字用来声明SQL语句的分隔符 , 告诉 MySQL 解释器,该段命令是否已经结束了,mysql是否可以执行了。
-- 默认情况下,delimiter是分号(;)。在命令行客户端中,如果有一行命令以分号结束,那么回车后,mysql将会执行该命令。
2)调用存储过程
-- 语法:
call procedure_name() ;
3)查看存储过程(除了以上语句,一般用工具查看)
-- 方法一:查询db_name数据库中的所有的存储过程
-- db_name:数据库名称
select name from mysql.proc where db='db_name';
-- 方式二:查询存储过程的状态信息
show procedure status;
-- 方式三:查询名为pro_test1的存储过程的的创建语句
show create procedure pro_test1
4)删除存储过程
-- 删除名为sp_name 的存储过程
DROP PROCEDURE [IF EXISTS] sp_name
三:编程语法
一:变量
1)变量声明(DECLARE)
-- 语法
DECLARE var_name[,...] type [DEFAULT value]
-- 通过 DECLARE 可以定义一个局部变量,该变量的作用范围只能在 BEGIN…END 块中
-- var_name[,...]:变量名,可以是多个变量,逗号分隔
-- [DEFAULT value]:变量类型
-- 举例
create procedure pro_test1()
begin
declare num int default 5;
select num + 10;
end
2)赋值变量(set 或者 select… into…)
-- 方式一:语法
-- 直接赋值使用 SET,可以赋常量或者赋表达式,具体语法如下:
SET var_name = expr [, var_name = expr] ...
--举例
create procedure pro_test2()
begin
declare myname VARCHAR(20) ;
set myname = '张三';
select myname;
end
-- 方式二:语法select... into...
create procedure pro_test3()
begin
declare num int;
select count(*) into num from city;
select num;
end
-- 说明
-- 将city表的条数赋值给num(主要是into给变量)
二:if条件判断
-- 语法结构
if search_condition then statement_list
[elseif search_condition then statement_list] ...
[else statement_list]
end if;
-- 语法说明
-- search_condition :条件
-- statement_list:条件成立,执行的sql语句
-- 举例
create procedure pro2()
begin
declare height int default 175;
declare descrition VARCHAR(20) default '';
if height >180 then set descrition = '身材高挑';
elseif height between 170 and 180 then set descrition = '标准身材';
else set descrition = '一般身材';
end if;
select concat('身高:',height,'对应的身材类型:',descrition);
end
三:传递参数
注意变量:
@description : 这种变量要在变量名称前面加上“@”符号,叫做用户会话变量,代表整个会话过程他都是有作用的,这个类似于全局变量一样。
@@global.sort_buffer_size : 这种在变量前加上 “@@” 符号, 叫做 系统变量
-- 语法
create procedure procedure_name([in/out/inout] 参数名 参数类型)
-- 语法说明
-- procedure_name:参数名称
-- IN : 该参数可以作为输入,也就是需要调用方传入值 , 默认
-- OUT: 该参数作为输出,也就是该参数可以作为返回值
-- INOUT: 既可以作为输入参数,也可以作为输出参数
-- 举例(输入参数)
create procedure pro3(in height int)
begin
declare height int default 175;
declare descrition VARCHAR(20) default '';
if height >180 then set descrition = '身材高挑';
elseif height between 170 and 180 then set descrition = '标准身材';
else set descrition = '一般身材';
end if;
select concat('身高:',height,'对应的身材类型:',descrition);
end
call pro3(198)
-- 举例(输出参数)
create procedure pro4(in height int,out descrition VARCHAR(20))
begin
if height >180 then set descrition = '身材高挑';
elseif height between 170 and 180 then set descrition = '标准身材';
else set descrition = '一般身材';
end if;
end
-- 调用之后返回值赋值给了变量 @descrition
call pro4(168,@descrition)
-- 查询变量
select @descrition
四:case结构
-- 方式一 :
CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list] ...
[ELSE statement_list]
END CASE;
-- 方式一说明
-- case值 = when值 执行 sql语句
-- statement_list:执行的sql语句
-- 方式二 :
CASE
WHEN search_condition THEN statement_list
[WHEN search_condition THEN statement_list] ...
[ELSE statement_list]
END CASE;
-- 方式二说明
-- search_condition :条件表达式
-- statement_list:执行的sql语句
-- 举例
create procedure pro6(in m int,out q varchar(10))
begin
case when m between 1 and 3 then set q = '第一季度';
when m between 4 and 6 then set q = '第二季度';
when m between 7 and 9 then set q = '第三季度';
else set q = '第四季度';
end case;
end
call pro6(12,@q);
select @q;
五:循环
1)while 循环
-- 语法
while search_condition do
statement_list
end while;
-- 语法说明
-- search_condition :判断条件
-- statement_list:sql语句
-- 举例:从1到n累加
create procedure pro2(in n int,out sum int)
begin
declare total int default 0;
declare i int default 1;
while i<=n do
set total = total + i;
set i =i+1;
end while;
set sum = total;
end
call pro2(5,@sum);
select @sum;
2)repeat 循环( 满足条件退出循环 )
-- 语法
REPEAT
statement_list
UNTIL search_condition
END REPEAT;
-- 语法说明
-- statement_list:满足条件执行的sql
-- search_condition:满足这个条件时,退出
-- 举例:从1到n累加
create procedure pro2(in n int,out sum int)
begin
declare total int default 0;
repeat
set total = total + n;
set n = n - 1;
-- 注意until 后不要加分号
until n = 0
end repeat;
set sum = total;
end
call pro2(100,@sum);
select @sum;
3)loop 语句,leave语句
LOOP 实现简单的循环,退出循环的条件需要使用其他的语句定义,通常可以使用 LEAVE 语句实现。
leave语句:用来从标注的流程构造中退出,通常和 BEGIN … END 或者循环一起使用
-- 语法
[begin_label:] LOOP
statement_list
END LOOP [end_label]
-- 注意:如果不在 statement_list 中增加退出循环的语句,那么 LOOP 语句可以用来实现简单的死循环。
-- [begin_label:] 循环别名
-- statement_list:sql语句
-- 举例 :从1到n累加
CREATE PROCEDURE pro_test11(n int)
BEGIN
declare total int default 0;
-- ins 别名
ins: LOOP
-- 当n <= 0 退出
IF n <= 0 then
leave ins;
END IF;
set total = total + n;
set n = n - 1;
END LOOP ins;
select total;
END;
六:游标/光标
游标是用来存储查询结果集的,包括 :声明、OPEN、FETCH 和 CLOSE
1)声明光标:
DECLARE cursor_name CURSOR FOR select_statement ;
-- cursor_name:游标名称
-- select_statement :select 语句
2)OPEN 光标
OPEN cursor_name ;
3)FETCH(抓取) 光标:
FETCH cursor_name INTO var_name [, var_name] ...
4)CLOSE 光标:
CLOSE cursor_name ;
游标举例:
create procedure pro_test12()
begin
-- 声明变量
DECLARE id int(11);
DECLARE name varchar(50);
DECLARE age int(11);
DECLARE salary int(11);
DECLARE has_data int default 1;
-- 声明游标
DECLARE emp_result CURSOR FOR select * from emp;
-- 拿不到数据的时候,将has_data设置为0
DECLARE EXIT HANDLER FOR NOT FOUND set has_data = 0;
-- open游标
open emp_result;
-- 循环抓取
repeat
fetch emp_result into id , name , age , salary;
select concat('id为',id, ', name 为' ,name , ', age为 ' ,age , ', 薪水为: ', salary);
until has_data = 0
end repeat;
-- close游标
close emp_result;
end;
-- 调取的行数和count(*) 相同
CALL pro_test12()
七: 存储函数
-- 创建存储函数
create function count_city(countryId int)
-- 返回值类型
returns int
begin
declare cnum int ;
select count(*) into cnum from city where country_id = countryId;
return cnum;
end;
-- count_city:函数名
-- countryId:入参
-- 调用:
select count_city(1);