存储过程procedure语法如下
CREATE PROCEDURE <name>(
[[IN/OUT/INOUT] 参数名1 类型1]
[,[IN/OUT/INOUT] 参数名2 类型2])
[,...]
)
BEGIN
[DECLARE variable_name [,variable_name...] datatype [DEFAULT value];]
---------SQL语句块--------
END;
函数与存储过程区别:
- 函数用来做计算或查询指定列的数据,存储过程常用来进行数据更新。
- 函数可以出现在select或代码块中,存储过程只能通过CALL来调用,不能应用到SELECT语句中。
- 函数需要显示的使用RETURN关键来返回结果,而存储过程不需要。
- 函数的参数类型只能是IN(输入参数),存储过程的参数类型有IN(输入参数)、OUT(输出参数)、INOUT(输入输出参数)
- 函数只能有1个返回结果,而存储过程可以0到多个。
下面运行几个例子,提前建好表s_student,具体数据如下:
无参数存储过程
示例1 查询所有学生的信息
-- 无参存储过程
CREATE PROCEDURE proc_stu_all()
BEGIN
SELECT * FROM s_student;
END;
-- 调用存储过程
CALL proc_stu_all();
这里注意一点,存储过程的调用语句是CALL,和调用自定义内置函数不一样,结果如下(截图为部分结果):
有参数存储过程
存储过程有三种参数:IN(输入参数)、OUT(输出参数)、INOUT(输入输出参数);
而函数只有输入参数,返回结果用return,存储过程返回结果存储在输出参数中。
示例2 根据条件查询学生信息-根据学号查询
-- 有参存储过程
DROP PROCEDURE IF EXISTS proc_stu_one;
CREATE PROCEDURE proc_stu_one(v_sid int)
BEGIN
-- 数据有问题:如果传入参数的变量名与列名相同时,那么列名的优先级大于变量名
-- 同时这样的命名会导致代码不清晰。
-- SELECT * FROM s_student WHERE sid=sid; -- 1=1
-- 1)推荐:把变量名与列名设置为不相同
SELECT * FROM s_student WHERE sid=v_sid;
-- 2)不推荐:通过别名的方式来使用
-- SELECT * FROM s_student s1 WHERE s1.sid=sid;
END;
-- 调用存储过程
CALL proc_stu_one(19); -- 查询学号为19的学生信息
注意:输入参数变量名不能与列名相同,容易导致代码不清晰,这里使用v_sid为输入参数,查询结果为:
除了查询之外,存储过程还可以新增值
示例3 新增学生的1条记录,若插入成功返回保存成功
DROP PROCEDURE IF EXISTS proc_stu_save;
CREATE PROCEDURE proc_stu_save(
IN v_sid int,
v_sname VARCHAR(25),
v_birthday date,
v_sex char(1), # mysql不支持参数设置默认值
v_phone VARCHAR(20),
OUT v_result VARCHAR(50) # 作为返回结果,传递给外部调用,所以要加上OUT/INOUT
)
BEGIN
IF v_sid IS NOT NULL THEN
INSERT INTO s_student VALUES(v_sid,v_sname,v_birthday,v_sex,v_phone,null,null,null,null,null,null,null);
ELSE
INSERT INTO s_student(sname,birthday,sex,phone) VALUES(v_sname,v_birthday,v_sex,v_phone);
END IF;
SET v_result='保存成功';
END;
-- 调用存储过程
CALL proc_stu_save(null,'测试1',now(),'女','123',@result);
SELECT @result;
这里有两种参数类型,一种是IN,一种是OUT,调用过程中将输出参数保存在全局变量@result中
结果如下:
再举个栗子吧
示例4 保存学生的操作,要求,如果姓名相同时,插入失败,返回提示姓名已经存在,否则提示保存成功。
DROP PROCEDURE IF EXISTS proc_stu_save2;
CREATE PROCEDURE proc_stu_save(
IN v_sid int,
v_sname VARCHAR(25),
v_birthday date,
v_sex char(1), # mysql不支持参数设置默认值
v_phone VARCHAR(20),
OUT v_result VARCHAR(50),# 作为返回结果,传递给外部调用,所以要加上OUT/INOUT
)
BEGIN
-- 定义局部变量
DECLARE v_count int; -- 用于保存查询指定姓名的总记录数
-- 验证学生的姓名在数据表中是否存在
-- 1)根据指定的姓名查询学生的总记录数
SELECT count(*) INTO v_count FROM s_student WHERE sname=v_sname;
-- 2)判断总记录数
IF v_count>0 THEN
SET v_result='姓名已经存在';
ELSE
IF v_sid IS NOT NULL THEN
INSERT INTO s_student VALUES(v_sid,v_sname,v_birthday,v_sex,v_phone,null,null,null,null,null,null,null);
ELSE
INSERT INTO s_student(sname,birthday,sex,phone) VALUES(v_sname,v_birthday,v_sex,v_phone);
END IF;
SET v_result2='保存成功';
END if;
END;
-- 调用存储过程
CALL proc_stu_save(null,'测试1',now(),'女','123',@result);
SELECT @result;
注意:存储过程和内置函数一样申明局部变量时要用DECLARE
结果如下:
第一次写博客之前也没怎么接触过写的可能不好kkk本人是大学生之前没好好学编程,最近重新开始学,想着写博客记录一下自己的上课内容顺便也能理一下自己的思路,菜菜的我也在努力着。可能也没什么人看,不过没关系,截尾写一下自己的想法也很开心,话很多一个人。现在在听草东的歌,怎么听都听不腻。以后也会继续上传上课学的东西,一起加油吧。