转载自：http://blog.chinaunix.net/uid-23302288-id-3785111.html
■存储过程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)