MySQL存储过程

1.存储过程

    •   是一组经过预先编译的SQL语句的封装

    •   存储过程预先存储在MySQL服务器上，执行时客户端只需要向服务器发出调用命令，服务器端就能够将预先存储的SQL语句执行

    •   存储过程的使用可以类比为使用函数，直接调用存储过程名即可，只是存储过程没有返回值而已

2.存储过程的参数类型

1.存储过程参数类型

        IN ：当前参数为输入参数，也就是表示入参；存储过程只是读取这个参数的值。如果没有定义参数种类， 默认就是 IN ，表示输入参数。
        OUT ：当前参数为输出参数，也就是表示出参；执行完成之后，调用这个存储过程的客户端或者应用程序就可以读取这个参数返回的值了。
        INOUT ：当前参数既可以为输入参数，也可以为输出参数。

2.存储过程的分类

1.注意

        存储过程的创建过程中需要设置结束标记；因为MySQL中以 “;”结束语句，而存储过程中需要存放的也是SQL语句的集合，因此需要规避这种情况。

一个例子：

DELIMITER $

        这行语句的意思是：MySQL的结束标记为$，以END $表示结束存储过程，最后再将";"设置回去即可

2.类型

   •   无参数

#创建存储过程
DELIMITER $

CREATE PROCEDURE select_all()
BEGIN
	SELECT * FROM employees
END $

DELIMITER ;

#调用存储过程
CALL select_all();

   •   带OUT

#创建存储过程：带out
DELIMITER $

CREATE PROCEDURE min_salary(OUT ms DOUBLE)
BEGIN
	SELECT MIN(salary) INTO sl
	FROM employees;
END $

DELIMITER ;

#调用存储过程
CALL min_salary(@ms);

#查看变量
SELECT @ms;

   •   带IN

#创建存储过程：带in
DELIMITER $

CREATE PROCEDURE show_name_salary(IN empname VARCHAR(20))
BEGIN
	SELECT salary
	FROM employees
	WHERE last_name = empname;
END $

DELIMITER ;

#调用存储过程1
CALL show_name_salary('haha');

#调用存储过程2
SET @empname := 'haha';
CALL show_name_salary(@empname);

   •   带IN和OUT

#创建存储过程：带in 和 out
DELIMITER $

CREATE PROCEDURE show_name_salary(IN empname VARCHAR(20),OUT empsalary DECIMAL(10,2))
BEGIN
	SELECT salary INTO empsalary
	FROM employees
	WHERE last_name = empname;
END $

DELIMITER ;

#调用存储过程
SET @empname := 'haha';
CALL show_name_salary(@empname,@empsalary);

   •   带INOUT

#创建存储过程：带INOUT
DELIMITER $

CREATE PROCEDURE show_emp_name(INOUT empname VARCHAR(20))
BEGIN
	SELECT last_name INTO empname
	FROM employees
	WHERE last_name = empname;
END $

DELIMITER ;

#调用存储过程
SET @empname := 'haha';
CALL show_name_salary(@empname);

3.为什么不用存储过程

        mysql中报错后只能逐步推进，没办法像idea中有断点等的调参方法，只能逐段调试。所以一旦报错就很难用。

3.存储函数的使用

    •   函数一定会有返回值

    •   参数列表默认为in参数

#创建存储函数

DELIMITER $

CREATE FUNCTION ename_salary(employee_name VARCHAR(25))
RETURNS DOUBLE

BEGIN
	RETURN (SELECT salary FROM employees WHERE last_name = employee_name);
END $

DELIMITER ;

#调用存储函数1
SELECT ename_salary('haha');
#调用存储函数2
SET @empname := 'haha';
SELECT ename_salary(@empname);

4.存储函数与存储过程的对比

1.存储函数能够放在查询语句中使用，存储过程不行；

2.存储过程能够执行表的操作，存储函数不行

5.存储过程，存储函数的查看、修改、删除

    •   存储过程/函数的查看 •   show create 过程/函数 表名

    •   show 过程/函数 status like ‘’

    •   select…from information_schema.Routines where…

    •   存储过程/函数的修改 •   此时的修改不影响存储过程或函数的功能，只修改相关特性

    •   存储过程/函数的删除 •   drop 过程/函数 表名

#查看
SELECT * 
FROM information_schema.Routines
WHERE ROUTINE_NAME='show_emp_name' AND ROUTINE_TYPE = 'PROCEDURE';

SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='ename_salary' [AND ROUTINE_TYPE = 'FUNCTION';

#修改
ALTER PROCEDURE show_emp_name
SQL SECURITY INVOKER;

#删除
DROP FUNCTION IF EXISTS ename_salary;

6.存储过程的优缺点

1.优点

        可以编译一次SQL语句，多次使用，提高效率，减少工作量；

        能够减少网络传输量，封装性好

2.缺点

        可移植性差，调试很困难；

        不适合高并发场景

​7."=" 和 ":=" 的区别

1."=" ：一般被当作比较操作

但在两种情况下："=" 和  ":=" 是等效，都会被当作赋值操作

        使用SET操作符赋值时：

SET @param = 1
SET @param := 1

        使用UPDATE语句的SET语句时：

UPDATE employees 
SET telephone = '11111111111'

2.":=" ：在任何情况下都会被当作是赋值操作