mysql--存储过程实战
mysql--存储过程实战
一、存储过程格式
-
desc student
-
-
delimiter //
-
CREATE PROCEDURE 名称（参数列表）
-
-
BEGIN
-
SQL语句块
-
END
-
//
-
delimiter ;
注意：
- 存储名称的参数列必须总是存在。如果没有参数，也该使用一个空参数列()。每个参数默认都是一个IN参数。
- 参数类型要改变，可在参数名之前使用关键词 OUT或INOUT
二、创建存储过程
例子：
-
delimiter //
-
CREATE PROCEDURE pro1()
-
BEGIN
-
select * from student;
-
END
-
//
-
delimiter ;
-
call pro1();
解析：
（1）这里需要注意的是DELIMITER//和DELIMITER;两句，DELIMITER是分割符的意思，因为MySQL默认以”;”为分隔 符，如果我们没有声明分割符，那么编译器会把存储过程当成SQL语句进行处理，则存储过程的编译过程会报错，所以要事先用DELIMITER关键字申明当 前段分隔符，这样MySQL才会将”;”当做存储过程中的代码，不会执行这些代码，用完了之后要把分隔符还原。
（2）存储过程根据需要可能会有输入、输出、输入输出参数，这里有一个输出参数s，类型是int型，如果有多个参数用”,”分割开。
（3）过程体的开始与结束使用BEGIN与END进行标识。
三、存储过程定义
- 声明变量： declare 变量名 数据类型
- 参数类型：
- in参数：输入参数:表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值
- OUT 输出参数:该值可在存储过程内部被改变，并可返回
- INOUT 输入输出参数:调用时指定，并且可被改变和返回
四、无参数存储实例
- 注意存储过程写好后，需要先运行一下。保存到数据库后，才可以调用这个存储过程，否则在调用的时候会报错，提示存储过程不存在。
-
delimiter //
-
CREATE PROCEDURE pro1()
-
BEGIN
-
select * from student;
-
END
-
//
-
delimiter ;
-
call pro1(); //调用无参数的存储过程
五、有参数存储过程实例
- 参数类型为 in
-
delimiter //
-
CREATE PROCEDURE pro2(s_sex VARCHAR(10))
-
BEGIN
-
select * from student where sex=s_sex;
-
END
-
//
-
delimiter ;
-
-
set @s_sex='男';
-
CALL pro2(@s_sex);
pro2存储过程使用了in类型的参数。在调用pro2存储过程时候，需要先给s_sex参数赋值（set @s_sex='男';），然后再调用pro2存储过程时将s_sex传入进去（CALL pro2(@s_sex);）。
- 参数类型为 out
-
delimiter //
-
CREATE PROCEDURE pro3(out s_name varchar(20))
-
BEGIN
-
DECLARE s_name varchar(12);
-
DECLARE department varchar(20);
-
set s_name='测试1';
-
set department='测试地址1';
-
INSERT INTO student (s_name,department) values(s_name,department);
-
END
-
//
-
delimiter ;
-
set @s_name='张三强';
-
CALL pro3(@s_name);
-
select * from student;
测试结果：
调用pro3存储过程时给s_name参数赋值，但是在运行存储过程时，s_name参数值被改变了，说明了out类型的该值可在存储过程内部改变，并可以返回.往往是用于获取存储过程里的参数值
- 参数类型为inout
-
delimiter //
-
CREATE PROCEDURE pro4(out s_name varchar(12),INOUT sex varchar(6),IN department varchar(13))
-
BEGIN
-
DECLARE sex VARCHAR(6)DEFAULT('男');
-
set s_name='王明';
-
set department='雄安新区';
-
INSERT INTO student(s_name,sex,department)VALUES (s_name,sex,department);
-
END
-
//
-
delimiter ;
-
set @s_name='张三';
-
set @department='北京';
-
set @sex='女';
-
CALL pro4(@s_name,@department,@sex);
-
select * from student;
运行结果：
调用pro4存储过程，sex参数为inout类型在存储过程运行过程中改变了值的结果。
六、存储过程if判断语句
- if条件使用格式
-
if 条件 then
-
执行语句
-
elseif 条件 then
-
执行语句
-
else
-
执行语句
-
end if;
存储过程if判断实例
-
delimiter //
-
CREATE PROCEDURE pro5(sex varchar(12))
-
BEGIN
-
DECLARE s_name varchar(12);
-
DECLARE department varchar(21)DEFAULT '北京';
-
IF sex='男' THEN
-
set s_name='张三';
-
INSERT INTO student (s_name,department) VALUES (s_name,department);
-
ELSE
-
set s_name='张丽丽';
-
INSERT INTO student (s_name,sex,department) VALUES (s_name,sex,department);
-
END IF;
-
END
-
//
-
delimiter ;
pro5存储过程判断sex参数男，女。来改变插入数据的s_name名称。如果sex为男，则插入数据的s_name=张三。如果sex为女，则插入数据的s_name=张丽丽。
①、 sex=男，执行结果
-
set @sex='男';
-
CALL pro5(@sex);
-
select * FROM student;
②、sex=女，执行结果
-
set @sex='女';
-
CALL pro5(@sex);
-
select * FROM student;
七、存储过程while循环
-
while 条件 do
-
sql语句
-
end while;
- while循环实例
-
delimiter //
-
CREATE PROCEDURE pro6(count int)
-
BEGIN
-
DECLARE s_name varchar(12);
-
DECLARE department varchar(20);
-
-
DECLARE i int; -- 设置一个自增长变量i
-
set i=1; -- 自增长i赋值
-
WHILE i<=count DO
-
SELECT CONCAT('变量i的值=',i); -- 查看每次循环i的值
-
set s_name=CONCAT('测试',i); -- CONCAT()是个函数
-
set department='测试地址1';
-
-
INSERT INTO student (s_name,department) values(s_name,department);
-
set i=i+1; -- 自增长i加1
-
END WHILE;
-
END
-
//
-
delimiter ;
CALL pro6(3);
pro6存储过程使用了while循环，在调用的时候需要传入循环的次数，结果会创建相应次数的数据。
八、查询存储过程
- 查询有多少个存储过程：
SHOW PROCEDURE STATUS;
- 查询某个存储过程详细信息
-
SHOW CREATE PROCEDURE 存储过程名称;
-
select * FROM information_schema.ROUTINES WHERE ROUTINE_NAME='存储过程名称';
文章来源: brucelong.blog.csdn.net，作者：Bruce小鬼，版权归原作者所有，如需转载，请联系作者。
原文链接：brucelong.blog.csdn.net/article/details/79786539
- 点赞
- 收藏
- 关注作者
评论（0）