mysql 存储过程 总结_mysql存储过程总结

N年没写过存储过程了，突然要写存储一下都忘记完了，现在回顾总结。

先废话的说下mysql中的变量有1.系统变量 和 2.自定义变量

系统变量：变量由系统提供，不是用户定义，属于服务器层面

a.查看所有的系统变量: show global | session variables;

b.查看满足条件的部分系统变量: show global | 【session】 variables like '%char%';

c.查看指定的某哥系统变量的值:

select @@global | 【session】.系统变量名;

d.为某个系统变量赋值

方法一:

set global | 【session】 系统变量名 = 值；

方法二:

set @@global | 【session】.系统变量名 = 值；

e.全局变量

1.查看所有的全局变量:

SHOW GLOBAL VARIABLES;

查看部分的全局变量:

SHOW GLOBAL VARIABLES LIKE '%char%';

3.查看指定的全局变量的值:

SELECT @@global.autocommit;

SELECT @@tx_isolation;

4.为某个指定的全局变量赋值:

SET @@global.autocommit=0;

f.会话变量

1.查看所有的会话变量

SHOW VARIABLES;

SHOW SESSION VARIABLES;

2.查看部分的会话变量

SHOW VARIABLES LIKE '%char%';

SHOW SESSION VARIABLES LIKE '%char%'；

3.查看指定的某个会话变量

SELECT @@tx_isolation;

SELECT @@session.tx_isolation;

4.为某个会话变量赋值

方式一:

SET @@tx_isolation='read-uncommitted';

方式二:

SET SESSION tx_isolation='read-committed';

自定义变量: 变量是用户自定义的，不是系统的

作用域: 针对于当前会话(连接)有效，同于会话变量的作用域。应用在任何 地方，也就是begin end里面或begin end外面

2.1用户变量

赋值的操作符: = 或 :=

a.声明并初始化

SET @用户变量名=值；

或 SET @用户变量名:=值；

或 SELECT @用户变量名:=值

b.赋值(更新用户变量的值)

方式一: 通过SET 或 SELECT

SET @用户变量名=值；

或 SET @用户变量名:=值；

或 SELECT @用户变量名:=值；

方式二: 通过SELECT INTO

SELECT 字段 INTO @变量名 FROM 表；

c.使用(查看用户变量的值)

SELECT @用户变量名;

例子:

#声明并初始化

SET @name='john';

SET @name=100;

SET @count=1;

#赋值

SELECT COUNT( * ) INTO @count FROM 表名

#查看

SELECT @count;

2.2 局部变量

作用域: 仅仅在定义它的begin end 中有效,应用在begin end中的第一句 话

a.声明

DECLARE 变量名 类型；

DECLARE 变量名 类型 DEFAULT 值；

b.赋值

方式一: 通过SET 或 SELECT

SET 局部变量名=值；

或 SET 局部变量名:=值；

或 SELECT @局部变量名:=值；

方式二: 通过SELECT INTO

SELECT 字段 INTO 局部变量名 FROM 表

c.使用

SELECT 局部变量名；

对比用户变量和局部变量

用户变量：

作用域: 当前会话

定义和使用的位置: 会话中的任何地方

语法: 必须加@符合，不用限定类型

局部变量：

作用域: BEGIN END中

定义和使用的位置: 只能在BEGIN END中，且为第一句话

语法: 一般不用加@符合，需要限定类型

例如: 声明两个变量并赋初始值，求和，并打印

SET @m=1;

SET @n=2;

SET @sum = @m + @n;

SELECT @sum;

存储过程:

使用存储过程的好处: 1.提高代码的重用性； 2.简化操作； 3.减少了编译次数并减少了和数据库服务器的连接次数，提高了效率

含义: 一组预先编译好的SQL语句的集合，理解成批处理语句

一. 创建语法:

CREATE PROCEDURE 存储过程名(参数列表)

BEGIN

存储过程体(一组合法的SQL语句)

END

注意: 1.参数列表包含三部分：参数模式(IN、OUT、INOUT) , 参数名，参数类型

例如: IN stuname VARCHAR(20)

参数模式:

IN:该参数可以作为输入，也就是该参数需要调用方法入值

OUT:该参数可以作为输出，也就是该参数可以作为返回值

INOUT:该参数即可以作为输入又可以作为输出，也就是该参数即需要传入 值，又可以返回值

2.如果存储过程体仅仅只有一句话，BEGIN END可以省略

存储过程体中的每条SQL语句的结尾要求必须加分号。

存储过程的结尾可以使用DELMITER重新设置，语法:

DELIMITER 结束标记

例如: DELIMITER $

二.调用语句

CALL 存储过程名(实参列表)；

例如:

1.空参列表

#例:插入到admin表中五个记录

DELIMITER $

CREATE PROCEDURE myp1()

BEGIN

INSERT INTO admin(username, password)

VALUES('name1','1111'),('name2','1111'),('name3','1111'),('name4','1111'),('name5','1111');

END $

调用: CALL myp1() $

2.创建带IN模式参数的存储过程

例如: 创建存储过程实现 根据名字查询对应的信息

CREATE PROCEDURE myp2(IN beautyName VARCHAR(20))

BEGIN

SELECT tb1.*

FROM table1 tb1

RIGHT JOIN table2 tb2 on tb1.id = tb2.nameId

WHERE tb1.name = tb2.name

END $

调用: CALL myp2('姓名') $

例如:创建存储过程实现,用户是否登陆成功

CREATE PROCEDURE myp3(IN username VARCHAR(20), IN password VARCHAR(20))

BEGIN

DECLARE result INT DEFAULT 0; #声明并初始化

SELECT COUNT(*) INTO result #赋值

FROM admin

WHERE admin.username = username

AND admin.password = password;

SELECT IF(result>0, '成功', '失败'); #使用

END $

调用: CALL myp3('name','password') $

3.带返回值的

例如:根据gril姓名,返回对应的boy姓名

CREATE PROCEDURE myp5(IN grilName VARCHAR(20), OUT boyName VARCHAR(20))

BEGIN

SELECT b.boyName INTO boyName

FROM boys b

INNER JOIN gril g ON b.id = g.boy_id

WHERE g.name = grilName;

END $

调用: CALL myp5('name', @bName) $

输出: SELECT @bName$

例如: 根据gril姓名,返回对应的boy的姓名和魅力值

CREATE PROCEDURE myp6(IN gName VARCHAR(20), OUT bName VARCHAR(20), OUT bCp INT)

BEGIN

SELECT b.boyname, b.userCp INTO bName, bCp

FROM boys b

INNER JOIN gril g on b.id = g.boy_id

WHERE g.name = gName

END $

调用: CALL myp6(‘name’, @bName, @usercp)$

输出: SELECT @bName, @usercp

4.创建带INOUT模式参数的存储过程

例如: 传入a和b两个值，最终a和b都翻倍并返回

CREATE PROCEDURE myp8(INOUT a INT, INOUT b INT )

BEGIN

SET a=a*2;

SET b=b*2;

END $

调用:

SET @m=10$

SET @n=20$

CALL myp8(@m, @n)$

SELECT @m,@n

#一、创建存储过程实现传入用户名和密码,插入到admin表中

CREATE PROCEDURE test_pro1(IN username VARCHAR(20), IN loginPwd VARCHAR(20))

BEGIN

INSERT INTO admin(admin.username, admin.password)

VALUES(username, loginPwd);

END $

#二、创建存储过程实现传入女神编号,返回女神名称和女神电话

CREATE PROCEDURE test_pro2(IN id INT, OUT name VARCHAR(20), OUT phone VARCHAR(20))

BEGIN

SELECT b.name, b.phone INTO name,phone

FROM gril b

WHERE b.id = id

END $

#三、创建存储过程实现传入两个生日，返回大小

CREATE PROCEDURE test_pro3(IN birth1 DATETIME, IN birth2 DATETIME, OUT result INT)

BEGIN

SELECT DATEDIFF(birth1, birth2) INTO result;

END $

#四、创建存储过程实现传入一个日期，格式化成为YYYY年MM月DD日并返回

CREATE PROCEDURE test_pro4(IN mydate DATETIME, OUT strDate VARCHAR(50))

BEGIN

SELECT DATE_FORMAT(mydate, '%y年%m月%d日') INTO strDate;

END $

CALL test_pro4(NOW(), @str)$

SELECT @str $

#五、创建存储过程实现分页的效果

CREATE PROCEDURE test_pro6(IN startIndex INT, IN size INT)

BEGIN

SELECT * FROM admin LIMIT startIndex, size;

END $

3.删除存储过程

语法: DROP PROCEDURE 存储过程名


4.查看存储过程

SHOW CREATE PROCEDURE 存储名称;

函数:

函数和存储过程的区别:

存储过程:可以有0个返回，也可以有多个返回，适合做批量插入、批量更新

函数:有且仅有1个返回，适合做处理数据后返回一个结果

一、创建语法

CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型

BEGIN

函数体

END

注意:

1.参数列表 包含两个部分:参数名 参数类型

2.函数体:肯定会有return语句，如果没有会报错

如果return语句没有放在函数体的最后也不报错，不建议

return 值；

3.函数体中仅有一句话，则可以省略BEGIN END

4.使用DELIMITER语句设置结束标记

二、调用语法

SELECT 函数名(参数列表)

例如:

#1.无参有返回

CREATE FUNCTION my1() RETURNS INT

BEGIN

DECLARE c INT DEFAULT 0; #定义变量

SELECT COUNT(*) INTO c； #赋值

FROM employees;

RETURN c;

END $

调用: SELECT myf1()$

#2.有参有返回

CREATE FUNCTION myf2(empName VARCHAR(20)) RETURNS DOUBLE

BEGIN

SET @sal=0; #定义用户变量

SELECT salary INTO @sal #赋值

FROM employees

WHERE last_name = empName;

RETURN @sal;

END $

SELECT myf2('k-ing') $

CREATE FUNCTION myf3(deptName VARCHAR(20)) RETURNS DOUBLE

BEGIN

DECLARE sal DOUBLE;

SELECT AVG(salary) INTO sal

FROM employees e

JOIN departments d ON e.department_id = d.department_id

WHERE d.department_name = deptName;

RETURN sal;

END $

SELECT myf3('IT')$

二、查看函数

SHOW CREATE FUNCTION 函数名；

三、删除函数

DROP FUNCTION 函数名；