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)

本文版权归属：康宝莱减肥转载请注明，肆意删除链接，我们将保留追责权利