mysql存储过程

■存储过程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: 2011-08-13 05:40:12
      Created: 2011-08-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 | 2011-08-13 05:40:12 | 2011-08-13 05:40:12 |
| test | search_nam2 | PROCEDURE | root@localhost | 2011-08-13 05:56:37 | 2011-08-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)