创建存储过程的要点

一、定义输出参数

1、定义输出参数

mysql> delimiter // <--delimiter与 //有空格 -->
mysql> create procedure sp_search_customer2
-> (in p_nam varchar(20),
-> out p_cnt int) <-- p_cnt 为输出参数 -->
-> begin
-> if p_nam is null or p_nam="" then
-> select * from customer;
-> else
-> select * from customer where nam like p_nam;
-> end if;
-> select found_rows() into p_cnt; <-- found_rows() 取得前一条select语句中检索出的记录件数，然后使用select·····into命令将记录件数设置到

变量p_cnt中 -->
-> end
-> //
Query OK, 0 rows affected (1.03 sec)

delimiter;

2、调用存储过程

mysql> call sp_search_customer2('s%',@num); <-- 指定out/outin类型的参数时，在参数头部加上@，处理结果将保存到out型变量num中-->

+-------+------+------------+------+
| mid | nam | birth | sex |
+-------+------+------------+------+
| N0008 | san | 1990-09-23 | 1 |
| N0009 | san | 1990-09-23 | 0 |
+-------+------+------------+------+
2 rows in set (3.76 sec)

Query OK, 1 row affected (3.77 sec)

3、显示处理结果

mysql> select @num; <--使用select @num显示变量-->
+------+
| @num |
+------+
| 2 |
+------+
1 row in set (0.00 sec)

二、使用if命令实现多重条件分支

1、建立存储过程

mysql> delimiter //
mysql> create procedure sp_search_employee(in p_depart int)
-> begin
-> if p_depart=1 then
-> select fname,lname,depart from employee where depart='研究部';
-> elseif p_depart=2 then
-> select fname,lname,depart from employee where depart='AC部';

-> elseif p_depart=3 then

-> select fname,lname,depart from employee where depart='IT部';

-> elseif p_depart=4 then

-> select fname,lname,depart from employee where depart='人事部';

-> elseif p_depart=5 then

-> select fname,lname,depart from employee where depart='经理部';

-> end if;

->end

-> //

Query OK, 0 row affected (3.77 sec)

2、调用存储过程

mysql> call sp_search_employee(3);

+-------+-------+--------+
| fname | lname | depart |
+-------+-------+--------+
| 度 | 王 | IT部 |
| 殊 | 方 | IT部 |
| 小 | 王 | IT部 |
| 操 | 曹 | IT部 |
+-------+-------+--------+
4 rows in set (0.00 sec)

Query OK, 0 rows affected (0.03 sec)

三、使用case命令使用多重条件分支

1、创建存储过程

mysql> delimiter //
mysql> create procedure sp_search_employee2(in p_depart int)
-> begin
-> case p_depart
-> when 1 then
-> select fname,lname,depart from employee where depart='研究部';
-> when 2 then
-> select fname,lname,depart from employee where depart='AC部';
-> when 3 then
-> select fname,lname,depart from employee where depart='IT部';
-> when 4 then
-> select fname,lname,depart from employee where depart='人事部';
-> else
-> select fname,lname,depart from employee where depart='经理部';
-> end case;
-> end
-> //
Query OK, 0 rows affected (0.00 sec)

2、调用存储过程

mysql> call sp_search_employee2(3);

+-------+-------+--------+
| fname | lname | depart |
+-------+-------+--------+
| 度 | 王 | IT部 |
| 殊 | 方 | IT部 |
| 小 | 王 | IT部 |
| 操 | 曹 | IT部 |
+-------+-------+--------+
4 rows in set (0.00 sec)

Query OK, 0 rows affected (0.03 sec)

四、使用循环语句