复制代码注意 看看 有没有 漏掉 ;
存储过程和函数是在数据库中定义一些SQL语句的集合，然后直接调用这些存储过程和函数来执行已经定义好的SQL语句。存储过程和函数可以避免开发人员重复的编写相同的SQL语句。而且，存储过程和函数是在MySQL服务器中存储和执行的，可以减少客户端和服务器端的数据传输
存储函数 | 存储过程 |
不能拥有输出参数 | 可以拥有输出参数 |
可以直接调用存储函数，不需要call语句 | 需要call语句调用存储过程 |
必须包含一条return语句 | 不允许包含return语句 |
创建存储过程
一、创建存储过程
因为;分会冲突，所以要加delimiter //。将//设置为结束运行符号
delimiter //#声明存储过程的结束符号为//
create procedure sp_name()
begin
.........sql语句
end //
delimiter ;#重新声明分号为结束符号二、调用存储过程
call sp_name()注意：存储过程名称后面必须加括号，哪怕该存储过程没有参数传递
三、删除存储过程
drop procedure sp_name//注意：不能在一个存储过程中删除另一个存储过程，只能调用另一个存储过程
四、查询存储过程
四种方式
select `name` from mysql.proc where db = '数据库' and `type` = 'PROCEDURE' //存储过程
select * from mysql.proc where db = 'xx' and `type` = 'PROCEDURE' and name='xx'
select `name` from mysql.proc where db = 'xx' and `type` = 'FUNCTION' //函数
show procedure status; //存储过程
show function status; //函数4.1存储过程具体语句
show create procedure sp_name\G;五、存储过程语法结构
5.1、局部变量声明
在存储过程体中可以声明局部变量，用来存储存储过程体中临时结果。
DECLARE var_name[,…] type [DEFAULT value]
Var_name:指定局部变量的名称 ,多个变量名用逗号分割
Type:用于声明局部变量的数据类型
default子句:用于为局部变量指定一个默认值。若没有指定，默认为null.SET 变量赋值
declare age int default 1;
SET age = 10;delimiter //
create procedure sp_name()
begin
declare age int default 1;
declare num int default 0;
SET num = 10;
select concat('age的值:',age);
end //
delimiter ;调用
call sp_name();into赋值
DROP PROCEDURE IF EXISTS contStById;
DELIMITER // -- 定义存储过程结束符号为//
CREATE PROCEDURE contStById()
BEGIN
DECLARE sCount INT;
//把t_student 表的总数,查出来赋值给 sCount
SELECT COUNT(*) INTO sCount FROM t_student;
SELECT CONCAT('sCount 的值是:',sCount);
END // -- 结束符要加
DELIMITER ; -- 重新定义存储过程结束符为分号
CALL contStById();5.2、条件语句
if 条件 then
statement
else
statement
end if;DROP PROCEDURE IF EXISTS testIf;
DELIMITER //
CREATE PROCEDURE testIf()
BEGIN
DECLARE val VARCHAR(255);
DECLARE result VARCHAR(255);
SET val = 'a';
IF val IS NULL
//条件ture执行then
THEN SET result = 'IS NULL';
//如果var=b 执行
ELSEIF var=='b'
THEN SET result ='wo';
//都不满足执行else
ELSE SET result = 'IS NOT NULL';
END IF;
SELECT CONCAT('最终信息;',result );
END //
DELIMITER ;
CALL testIf();5.3、传递参数
语法： CREATE PROCEDURE sp_name(定义IN/OUT/INOUT) 参数名 参数类型)
BEGIN
SQL语句；
END
IN 表示输入参数，默认是IN
OUT表示输出参数，
INOUT表示既可以输入也可以输出的参数。
sp_name为存储过程的名字。
如果此存储过程没有任何输入输出，其实就没什么意义了，但是sp_name()的括号不能省略out
DROP PROCEDURE IF EXISTS testIf;
DELIMITER //
CREATE PROCEDURE testIf(OUT result VARCHAR(255))
BEGIN
DECLARE val VARCHAR(255);
SET val = 'a';
IF val IS NULL
THEN SET result = 'IS NULL';
ELSE SET result = 'IS NOT NULL';
END IF;
END //
DELIMITER ;
//@result用来接收输出的result,mysql中 @代表用户的会话变量
CALL testIf(@result);
SELECT @result;
综合
DROP PROCEDURE IF EXISTS getStuById;
DELIMITER // -- 定义存储过程结束符号为//
CREATE PROCEDURE getStuById(IN stuId INT(11),OUT stuName VARCHAR(255),OUT stuAge INT(11)) -- 定义输入与输出参数
COMMENT 'query students by their id' -- 提示信息
//SQL SECURITY DEFINER -- DEFINER指明只有定义此SQL的人才能执行，MySQL默认也是这个
BEGIN
//age INTO stuName 把表查出来的变量赋值给输出变量stuName
SELECT name ,age INTO stuName , stuAge FROM t_student WHERE id = stuId; -- 分号要加
END // -- 结束符要加
DELIMITER ; -- 重新定义存储过程结束符为分号#study 是当前数据库名称
CALL study.getStuById(1,@name,@age);
SELECT @name AS stuName,@age AS stuAge;
5.4 条件（2）CASE语句
DROP PROCEDURE IF EXISTS testCase;
DELIMITER //
CREATE PROCEDURE testCase(OUT result VARCHAR(255))
BEGIN
DECLARE val VARCHAR(255);
CASE
WHEN 表达式 THEN SET result = 'val is true';
WHEN 表达式 THEN SET result = 'val is false';
ELSE SELECT 'else';
END CASE;
// SELECT CONCAT('result ',result ) as result
END //
DELIMITER ;
CALL testCase(@result);
SELECT @result;5.5 条件循环语法 WHILE 条件 DO
DROP PROCEDURE IF EXISTS testWhile;
DELIMITER //
CREATE PROCEDURE testWhile(IN myCount INT(11),OUT result INT(11))
BEGIN
DECLARE i INT DEFAULT 0 ; -- 定义变量
WHILE i < myCount DO -- 符合条件就循环
-- 核心循环SQL;
SET i = i + 1 ; -- 计数器+1
END WHILE; -- 当不满足条件，结束循环 --分号一定要加！
SET result = i; -- 将变量赋值到输出
END //
DELIMITER ;CALL testWhile(10,@result);
SELECT @result AS 循环次数;
5.6 repeat 循环语法
/*repeat 循环语法
repeat
循环体
until 条件 end repeat;
*/
-- 实例；
create procedure sum3(a int)
begin
declare sum int default 0;
declare i int default 1;
repeat -- 循环开始
set sum=sum+i;
set i=i+1;
until i>a end repeat; -- 满足 i>a就 结束 循环
select sum; -- 输出结果
end5.6 LOOP 循环
LOOP用于重复执行SQL。LEAVE 用于退出循环。DROP PROCEDURE IF EXISTS testLoop;
DELIMITER //
CREATE PROCEDURE testLoop(OUT result VARCHAR(255))
BEGIN
DECLARE id INT DEFAULT 0;
add_loop:LOOP
SET id = id + 1;
IF id>10 THEN LEAVE add_loop; -- 满足条件结束循环,可在此处修改成批量插入
END IF;
SET result = id;
END LOOP add_loop;
END //
DELIMITER ;
CALL testLoop(@result);
SELECT @result;5.7 MySql存储过程—7、游标(Cursor)
1、游标的作用及属性
游标的作用就是用于对查询数据库所返回的结果集记录进行遍历，以便进行相应的操作；游标有下面这些属性：
a、游标是只读的，也就是不能更新它；
b、游标是不能滚动的，也就是只能在一个方向上进行遍历，不能在记录之间随意进退，不能跳过某些记录；
c、避免在已经打开游标的表上更新数据。2、如何使用游标
使用游标需要遵循下面步骤：
a、首先用DECLARE语句声明一个游标把查询的结果集封装到游标容器中
CURSOR FOR select 字段 from 表DECLARE cursor_name CURSOR FOR select ID from allintersection; -- 声明一个游标 ;上面这条语句就对，我们执行的select语句返回的记录指定了一个游标
b、其次需要使用OPEN语句来打开上面你定义的游标OPEN cursor_name;c、接下来你可以用FETCH语句来获得下一行数据，并且游标也将移动到对应的记录上(这个就类似java里面的那个iterator)
调用一次指正往下移动一次
FETCH cursor_name INTO variable list;d、然后最后当我们所需要进行的操作都结束后我们要把游标释放掉。
CLOSE cursor_name;案例一,只会获取第一行
DELIMITER //
create procedure test3()
begin
declare id int(11);
declare name varchar(22); -- 声明一个局部变量
declare cursorTest3 cursor for select ID,name from userTable; -- 声明一个游标
open cursorTest3; -- 打开游标
-- 取出来的是一行数据,进行赋值,类型,顺序和表保持一致
fetch cursorTest3 into id,name; -- 获取 id,name
close cursorTest3; -- 关闭游标
end//
DELIMITER ;案例二,会获取全部
DELIMITER //
create procedure test4()
begin
declare id int(11);
declare name varchar(22); -- 声明一个局部变量
#定义边界变量控制结束循环
declare done boolean default 1;
-- 声明一个游标
declare cursorTest3 cursor for select ID,name from userTable;
#当拿不到数据,就会触发该事件 把变量done设置为0,必须在游标之后
declare continue handler for sqlstate '02000' set done=0;
#也可以这么写
#DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=0;
open cursorTest3; -- 打开游标
repeat -- 遍历所有的行
fetch cursorTest3 into id,name; -- 获取IntersectionName
until done=0 end repeat; -- 当done=0 结束循环
close cursorTest3; -- 关闭游标
end//
DELIMITER ;创建存储函数
存储函数和存储过程一样，都是sql和语句组成的代码块。
存储函数不能有输出参数，并且可以直接调用，不需要call语句，且必须有一条包含RETURN语句。
一、 MySQL存储函数语法
CREATE FUNCTION fun_name (par_name type[,...])
RETURNS type
[characteristics]
fun_body- 其中，fun_name为函数名，并且名字唯一，不能与存储过程重名。par_name是指定的参数，type为参数类型；
- RETURNS字句用来声明返回值和返回值类型。
- fun_body是函数体，所有存储过程中的sql在存储函数中同样可以使用。
- 但是存储函数体中必须包含一个RETURN 语句。
characteristics指定存储过程的特性，有以下取值：
- LANGUAGE SQL:说明routine_body部分是由SQL语句组成的，当前系统支持的语言为SQL，SQL是LANGUAGE特性的唯一值。
- [NOT] DETERMINISTIC:指明存储过程执行的结果是否确定。DETERMINISTIC表示结果是确定的，每次执行存储过程时，相同的输入会得到相同的输出，NOT DETERMINISTIC表示结果是不确定的，相同的输入可能得到不同的输出，如果没有指定任意一个值，默认为NOT DETERMINISTIC。
- [CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA]：指明子程序使用SQL语句的限制。CONTAINS SQL表明子程序包含SQL语句，但不包含读写数据语句；NO SQL表明子程序不包含SQL语句；READS SQL DATA说明子程序包含读数据的语句；MODIFIES SQL DATA表名子程序包含写数据的语句。默认情况下，系统会指定为CONTAINS SQL。
- SQL SECURITY[DEFINER|INVOKER]：指明谁有权限来执行。DEFINER表示只有定义着才能执行。INVOKER表示用友权限的调用者可以执行。默认情况下，系统指定为DEFINER。
- COMMENT ‘string’：注释信息，用来描述存储过程或函数。
delimiter //
create function getName(Id int)
#声明返回值类型
RETURNS VARCHAR(50)
DETERMINISTIC
begin
#定义返回变量
declare name VARCHAR(50);
#变量赋值
set name=(select name from animal where id=Id);
#返回变量
return name;
end//
delimiter;
-- 调用
select getName(4)
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
