■存储过程Stored Procedure 存储过程就是保存一系列SQL命令的集合，将这些sql命令有组织的形成一个小程序，这样会实现很复杂的处理 SQL基本是一个命令一个命令执行，虽然可以通过连接、子查询等实现些高级的处理，但局限性是显而易见的 ■存储过程的优势 1.提高执行性能(存储过程事先完成了解析、编译的处理，执行时能减轻数据库负担) 2.可减轻网络负担(比起多次传递SQL命令本身，这大大减轻了网络负担) 3.可防止对表的直接访问(可只赋予用户对相关存储过程的访问权限) 4.存储过程会保存在数据库中，应用程序只需要知道调用哪个存储过程就可以完成相应处理 ■使用存储过程 参数种类分为: IN(输入型)，OUT(输出型)， INOUT(输入输出型) SELECT column1.. INTO 变量1... FROM table1 WHERE xxx; //这个变量1对应OUT，INOUT create procedure 存储过程名( 参数种类1 参数1 参数类型1 参数种类2 参数2 参数类型2...） begin 处理内容 end DELIMITER // CREATE PROCEDURE search_customer( IN p_nam VARCHAR(20)) BEGIN IF p_nam IS NULL OR p_nam = '' THEN SELECT * FROM customer; ELSE SELECT * FROM customer WHERE nam LIKE p_nam; END IF; END // DELIMITER ； ■注意事项 1.DELIMITER命令改变分隔符 默认分隔符是';' 存储过程中肯定会有';' ,所以使用其将分隔符改为'//' , 创建好后，在将分隔符改回';' 2.可使用的控制语句 IF语句 IF situation=1 THEN command1; ELSEIF situation=2 THEN command2; ELSE command3; END IF ; CASE语句 CASE situation WHEN 1 THEN command1; WHEN 2 THEN command2; WHEN 3 THEN command3; ELSE command4; END CASE; WHILE (前置判断) 根据条件，循环有可能一次不执行 WHILE situation >1 DO command1; END WHILE; REPEAT (后置判断) 不论条件如何，循环至少会执行一次 command1 REPEAT command1; UNTIL situation<=1 END REPEAT; 3.查看创建的存储过程状态 SHOW PROCEDURE STATUS \G; SHOW CREATE PROCEDURE search_nam \G; 4.删除存储过程 DROP PROCEDURE search_nam; 5.执行存储过程 CALL search_nam('li%'); CALL search_nam(''); 创建存储函数 mysql> DELIMITER // mysql> CREATE PROCEDURE search_nam( -> IN p_nam VARCHAR(20)) -> BEGIN -> IF p_nam IS NULL OR p_nam='' THEN -> SELECT * FROM USER3; -> ELSE -> SELECT * FROM USER3 WHERE name LIKE p_nam; -> END IF; -> END -> // Query OK, 0 rows affected (0.05 sec) mysql> DELIMITER ; 查看创建的存储函数语句 mysql> SHOW CREATE PROCEDURE search_nam \G; *************************** 1. row *************************** Procedure: search_nam sql_mode: Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `search_nam`( IN p_nam VARCHAR(20)) BEGIN IF p_nam IS NULL OR p_nam='' THEN SELECT * FROM USER3; ELSE SELECT * FROM USER3 WHERE name LIKE p_nam; END IF; END 1 row in set (0.00 sec) 查看创建的存储函数状态 mysql> SHOW PROCEDURE STATUS \G; *************************** 1. row *************************** Db: test Name: search_nam Type: PROCEDURE Definer: root@localhost Modified: 2017-12-13 05:40:12 Created: 2017-12-13 05:40:12 Security_type: DEFINER Comment: 1 row in set (0.00 sec) 调用存储过程 成功 mysql> CALL search_nam('aa%'); +------+------+ | id | name | +------+------+ | 1 | aaa | +------+------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> CALL search_nam(''); +------+------+ | id | name | +------+------+ | 1 | aaa | | 2 | bbb | | 3 | ccc | +------+------+ 3 rows in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) 演示OUT类型参数 mysql> DELIMITER // mysql> CREATE PROCEDURE search_nam2( -> IN p_nam VARCHAR(20), -> OUT p_num INT) -> BEGIN -> IF p_nam IS NULL OR p_nam='' THEN -> SELECT * FROM user3; -> ELSE -> SELECT * FROM USER3 WHERE name LIKE p_nam; -> END IF; -> SELECT FOUND_ROWS() INTO p_num; -> END -> // mysql> DELIMITER ; mysql> SHOW PROCEDURE STATUS ; +------+-------------+-----------+----------------+---------------------+---------------------+ | Db | Name | Type | Definer | Modified | Created | +------+-------------+-----------+----------------+---------------------+---------------------+ | test | search_nam | PROCEDURE | root@localhost | 2017-12-13 05:40:12 | 2017-12-13 05:40:12 | | test | search_nam2 | PROCEDURE | root@localhost | 2017-12-13 05:56:37 | 2017-12-13 05:56:37 | +------+-------------+-----------+----------------+---------------------+---------------------+ 2 rows in set (0.00 sec) 调用成功 mysql> CALL search_nam3('bb%',@num); +------+------+ | id | name | +------+------+ | 2 | bbb | +------+------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> SELECT @num; +------+ | @num | +------+ | 1 | +------+ 1 row in set (0.00 sec) IF多分枝演示 mysql> DELIMITER // mysql> CREATE PROCEDURE depart( -> IN de_nam VARCHAR(10)) -> BEGIN -> IF de_nam=1 THEN -> SELECT * FROM USER3 WHERE depart='IT'; -> ELSEIF de_nam=2 THEN -> SELECT * FROM USER3 WHERE depart='HR'; -> ELSE -> SELECT * FROM USER3 WHERE depart='BOSS'; -> END IF; -> END -> // Query OK, 0 rows affected (0.00 sec) mysql> DELIMITER ; mysql> CALL depart(2); //演示成功 +------+------+--------+ | id | name | depart | +------+------+--------+ | 2 | bbb | HR | +------+------+--------+ 1 row in set (0.00 sec) CASE演示 mysql> DELIMITER // mysql> CREATE PROCEDURE depart2( -> IN de_num INT) -> BEGIN -> CASE de_num -> WHEN 1 THEN -> SELECT * FROM USER3 WHERE depart='IT'; -> WHEN 2 THEN -> SELECT * FROM USER3 WHERE depart='HR'; -> ELSE -> SELECT * FROM USER3 WHERE depart='BOSS'; -> END CASE; -> END -> // Query OK, 0 rows affected (0.00 sec) mysql> DELIMITER ; mysql> CALL depart2(1); +------+------+--------+ | id | name | depart | +------+------+--------+ | 1 | aaa | IT | +------+------+--------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) //演示成功 mysql> CALL depart2(2); +------+------+--------+ | id | name | depart | +------+------+--------+ | 2 | bbb | HR | +------+------+--------+ 1 row in set (0.00 sec) 声明局部变量： DECLARE tmp CHAR(10) [值]； 给变量赋值： SET tmp='值' ; mysql> DELIMITER // mysql> CREATE PROCEDURE depart3( -> IN p_num INT) -> BEGIN -> DECLARE tmp CHAR(5); -> CASE p_num -> WHEN 1 THEN -> SET tmp='IT'; -> WHEN 2 THEN -> SET tmp='HR'; -> ELSE -> SET tmp='BOSS'; -> END CASE; -> SELECT * FROM USER3 WHERE depart=tmp; -> END -> // mysql> DELIMITER ; mysql> call depart3(1); +------+------+--------+ | id | name | depart | +------+------+--------+ | 1 | aaa | IT | +------+------+--------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> call depart3(2); //调用成功 +------+------+--------+ | id | name | depart | +------+------+--------+ | 2 | bbb | HR | +------+------+--------+ 1 row in set (0.00 sec) 演示while mysql> DELIMITER // mysql> CREATE PROCEDURE sp_sum( -> IN p_num INT, -> OUT res INT) -> BEGIN -> SET res=1; -> WHILE p_num > 1 DO -> SET res=res * p_num; -> SET p_num=p_num - 1; -> END WHILE; -> END -> // Query OK, 0 rows affected (0.00 sec) mysql> DELIMITER ; mysql> CALL sp_sum(5,@res); Query OK, 0 rows affected (0.00 sec) mysql> SELECT @res; //演示成功 +------+ | @res | +------+ | 120 | +------+ 1 row in set (0.00 sec) 演示repeat mysql> DELIMITER // mysql> CREATE PROCEDURE sp_sum2( -> IN p_num INT, -> OUT res INT) -> BEGIN -> SET res = 1; -> REPEAT -> SET res=res * p_num; -> SET p_num=p_sum - 1; -> UNTIL p_num < 2 END REPEAT; -> END -> // mysql> DELIMITER ; mysql> CALL sp_sum2(5,@res); Query OK, 0 rows affected (0.00 sec) //调用成功 mysql> SELECT @res; +------+ | @res | +------+ | 120 | +------+ 1 row in set (0.00 sec)