MYSQL--存储过程

什么是存储过程

存储过程是sql语句和控制语句的预编译集合，以一个名称存储并作为一个单元处理。

优缺点

优点:

• 增强SQL语句的功能和灵活性
• 实现较快的执行速度
• 减少网络流量

缺点:

• 存储过程，往往定制化于特定的数据库上，因为支持的编程语言不同。当切换到其他厂商的数据库系统时，需要重写原有的存储过程。
• 存储过程的性能调校与撰写，受限于各种数据库系统。

语法

摘自菜鸟教程:

DELIMITER //
CREATE
    [DEFINER = { user | CURRENT_USER }]   
　PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body
 //
 DELIMITER;
 
proc_parameter:
    [ IN | OUT | INOUT ] param_name type
 
characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
 
routine_body:
　　Valid SQL routine statement
 
[begin_label:] BEGIN
　　[statement_list]
　　　　……
END [end_label]

其中:

DELIMITER $$
或
DELIMITER //
DELIMITER是分割符的意思，因为MySQL默认以";"为分隔符，如果我们没有声明分割符，那么编译器会把存储过程当成SQL语句进行处理，则存储过程的编译过程会报错，所以要事先用DELIMITER关键字申明当前段分隔符，这样MySQL才会将";"当做存储过程中的代码，不会执行这些代码，用完了之后要把分隔符还原。

声明存储过程:
CREATE PROCEDURE demo_in_parameter(IN p_in int) 

过程体的开始与结束使用BEGIN与END进行标识。
[DEFINER = { user | CURRENT_USER }]:   
代表当前登陆的用户，一般不予设置

proc_parameter:
存储过程参数，取值范围，[[IN |OUT |INOUT ] 参数名 数据类形...]
1.IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）
2.OUT 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）
INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量

routine_body:
存储过程体

BEGIN .... END
是存储过程开始和结束符号

变量定义:
DECLARE l_int int unsigned default 4000000; 

变量赋值:
SET @p_in=1 

示例演示

创建表和添加数据

use test; --调用test数据库
create table employee(
id int(11) primary key auto_increment,
name varchar(11) not null,
age int(11) not null
);

insert into employee(id,name,age) values(1,'tom',11);
insert into employee(id,name,age) values(2,'jreey',14);

存储过程的创建

无参存储过程

--查询employee所有员工信息
delimiter $$
CREATE PROCEDURE queryemployee ( ) BEGIN
	SELECT
		* 
	FROM
		employee;
	
END $$delimiter;
--注意:这里的关键字是PROCEDURE 而不是 PROCEDUER 尴尬的一批，因为自己是在dos命令运行的，一直不成功，真是受尽折磨！！！

--调用
call queryemployee();
--结果
+----+-------+-----+
| id | name  | age |
+----+-------+-----+
|  1 | tom   |  11 |
|  2 | jreey |  14 |
+----+-------+-----+

带参存储过程(一进一出)

• 一个入参

-- 根据id查询员工信息

delimiter $$
CREATE PROCEDURE queryemployeebyid ( IN eid INT ( 11 ) ) BEGIN
	SELECT
		* 
	FROM
		employee 
	WHERE
		id = eid;
	
END $$delimiter;

-- 结果
call queryemployeebyid(1);
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | tom  |  11 |
+----+------+-----+

• 一个出参

-- 查询所有用户的姓名
delimiter $$
create procedure queryemployname(out ename varchar(11))
begin
select name from employee;
end
$$
delimiter;
-- 结果
call queryemployname(@ename);
+-------+
| name  |
+-------+
| tom   |
| jreey |
+-------+

注意:
这里的@name必须要声明，否则查不到数据，会报错，@变量名 其中变量名是可以任意取值的，就相当于java代码中的接收形参变量名

• 一进一出

-- 根据id查询员工信息
delimiter $$
create procedure queryemployeebyidorage(in eid int(11), out ename varchar(11))
begin 
select name from employee where id=eid;
end
$$
delimiter;
-- 结果
call queryemployeebyidorage(1,@abc);
+------+
| name |
+------+
| tom  |
+------+

带参数存储过程p(多进多出)

emmmmm…这个屡次尝试总是有报错，不知道是因为sql版本的问题还是什么，先放下，后续更新跟进，尬尬尬尬…

这里就先写一个一进多出的
mysql row_count()函数:
-- 受影响的行数，默认是-1
select row_count();

-- 根据id删除员工，返回移除员工(row_count())的个数deleteemploycounts 和剩余个数employeecounts
delimiter $$
CREATE PROCEDURE removeemployeebyidandreturninfos ( IN eid INT, OUT deleteemploycounts INT, OUT employeecounts INT ) BEGIN
	DELETE 
	FROM
		employee 
	WHERE
		id = eid;
	SELECT
		ROW_COUNT( ) 
	FROM
		employee INTO deleteemploycounts;
	SELECT
		count( * ) 
	FROM
		employee INTO employeecounts;
	
END $$delimiter;
-- 结果
-- 调用前:
mysql> select * from employee;
+----+-------+-----+
| id | name  | age |
+----+-------+-----+
|  1 | tom   |  11 |
|  2 | jreey |  13 |
+----+-------+-----+
2 rows in set (0.00 sec)
-- 调用后
mysql> call removeemployeebyidandreturninfos(2,@deleteemploycounts,@employeecounts);
Query OK, 1 row affected (0.00 sec)

mysql> select @deleteemploycounts,@employeecounts;
+---------------------+-----------------+
| @deleteemploycounts | @employeecounts |
+---------------------+-----------------+
|                   1 |               1 |
+---------------------+-----------------+
1 row in set (0.00 sec)

受影响的行数是jreey,剩下tom一条数据

存储过程调用

call 存储过程名称;

存储过程的查看

show procedure status where db='test';
或者
show create procedure test.queryemployee;

--菜鸟教程提供:
selectname from mysql.proc where db='数据库名';

或者

selectroutine_name from information_schema.routines where routine_schema='数据库名';

或者

showprocedure status where db='数据库名';

或者

SHOWCREATE PROCEDURE 数据库.存储过程名;

存储过程的删除

DROP PROCEDURE

drop procedure queryemployeebyidorage;

存储过程的修改

ALTER PROCEDURE
-- ！！！注意
-- 存储过程是不可以被修改的，只有先删除存储过程，再新的方式创建

存储过程和自定义函数区别

• 存储过程实现的功能要复杂一些；而函数的针对性越强
• 存储过程可以返回多个值；而函数只能返回一个值
• 存储过程一般独立的来执行；而函数可以作为其它SQL语句的组成部分来出现