首先看MySQL 5.0参考手册中关于创建存储过程的语法说明： CREATE [DEFINER = { user | CURRENT_USER }] PROCEDURE sp_name ([proc_parameter[,...]]) [characteristic ...] routine_body proc_parameter: [ IN | OUT | INOUT ] param_name type type: Any va
首先看mysql 5.0参考手册中关于创建存储过程的语法说明：
create
[definer = { user | current_user }]
procedure sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
proc_parameter:
[ in | out | inout ] param_name type
type:
any valid mysql data type
characteristic:
language sql
| [not] deterministic
| { contains sql | no sql | reads sql data | modifies sql data }
| sql security { definer | invoker }
| comment 'string'
routine_body:
valid sql procedure statement
如果你对mysql还不太熟悉的话，单单看这个语法结构当然不足以进行mysql存储过程编程。我之前基本都是使用ms sql server，所以以下记录我熟悉mysql存储过程的过程，也是重点介绍ms sql server与mysql区别较大的地方。
第一步，当然是写个hello word的存储过程，如下：
Java代码 
- CREATE PROCEDURE phelloword()
- BEGIN
- SELECT 'Hello Word!' AS F;
- END;
<span>CREATE</span> <span>PROCEDURE</span> phelloword() BEGIN SELECT 'Hello Word!' AS F; END;
将上面创建phelloword存储过程的语句拷到phpMyAdmin中执行，报如下错误：
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3
在这个问题上我纠缠了很久，在MySQL的命令行工具中执行同样不成功，但是根据提示信息可以知道执行在 SELECT 'Hello Word!' AS F;处结束，后面的END;没有执行，这显然会导致错误。
这里需要选择以个分隔符，语法如下：DELIMITER //
分隔符是通知MySQL客户端已经输入完成的符号。一直都是用“;”，但是在存储过程中不行，因为存储过程中很多语句都需要用到分号。
因此上面的存储过程改为：
Java代码
- CREATE PROCEDURE ptest()
- BEGIN
- SELECT 'Hello Word!' AS F;
- END //
<span>CREATE</span> <span>PROCEDURE</span> ptest() BEGIN SELECT 'Hello Word!' AS F; END //
另外在phpMyAdmin中执行时，在Delimiter文本框中填写 //，这次存储过程即可创建成功。
第二步，写一个包括参数，变量，变量赋值，条件判断，UPDATE语句，SELECT返回结果集的完整的一个存储过程，如下：
Java代码
- CREATE PROCEDURE plogin
- (
- p_username char(15),
- p_password char(32),
- p_ip char(18),
- p_logintime datetime
- )
- LABEL_PROC:
- BEGIN
- DECLARE v_uid mediumint(8);
- DECLARE v_realpassword char(32);
- DECLARE v_nickname varchar(30);
- DECLARE v_oltime smallint(6);
- SELECT u.uid, u.password, f.nickname, u.oltime INTO v_uid,v_realpassword, v_nickname, v_oltime
- FROM cdb_members u INNER JOIN cdb_memberfields f ON f.uid = u.uid WHERE u.username = p_username;
- IF (v_uid IS NULL) THEN
- SELECT 2 AS ErrorCode;
- LEAVE LABEL_PROC;
- END IF;
- IF (p_password v_realpassword) THEN
- SELECT 3 AS ErrorCode;
- LEAVE LABEL_PROC;
- END IF;
- UPDATE ipsp_userexpands SET lastloginip = p_ip, lastlogintime = p_logintime WHERE uid = v_uid;
- SELECT 0 AS ErrorCode, v_uid AS uid, v_nickname AS nickname, v_oltime AS oltime;
- END LABEL_PROC //
<span>CREATE</span> <span>PROCEDURE</span> plogin
(
p_username char(15),
p_password char(32),
p_ip char(18),
p_logintime datetime
)
LABEL_PROC:
BEGIN
DECLARE v_uid mediumint(8);
DECLARE v_realpassword char(32);
DECLARE v_nickname varchar(30);
DECLARE v_oltime smallint(6);
SELECT u.uid, u.password, f.nickname, u.oltime INTO v_uid,v_realpassword, v_nickname, v_oltime
FROM cdb_members u INNER JOIN cdb_memberfields f ON f.uid = u.uid WHERE u.username = p_username;
IF (v_uid IS NULL) THEN
SELECT 2 AS ErrorCode;
LEAVE LABEL_PROC;
END IF;
IF (p_password <> v_realpassword) THEN
SELECT 3 AS ErrorCode;
LEAVE LABEL_PROC;
END IF;
UPDATE ipsp_userexpands SET lastloginip = p_ip, lastlogintime = p_logintime WHERE uid = v_uid;
SELECT 0 AS ErrorCode, v_uid AS uid, v_nickname AS nickname, v_oltime AS oltime;
END LABEL_PROC //
首先要说的是给变量赋值的语法，MySQL中使用SELECT u.uid, u.password, f.nickname, u.oltime INTO v_uid, v_realpassword, v_nickname, v_oltime FROM cdb_members u INNER JOIN cdb_memberfields f ON f.uid = u.uid WHERE u.username = p_username;这种方式给变量赋值。
其次是条件判断的语法结构，如下所示：
Java代码
- IF ... THEN
- ...;
- ELSE
- IF ... THEN
- ...;
- ELSEIF
- ...;
- ELSE
- ...;
- END IF;
- END IF;
IF ... THEN
...;
ELSE
IF ... THEN
...;
ELSEIF
...;
ELSE
...;
END IF;
END IF;
最后说说LEAVE 语法的使用。当满足某种条件，不继续执行下面的SQL时，在MS SQL
第三步，创建一个执行动态SQL的存储过程。
Java代码
- CREATE PROCEDURE ipsp_getresourcedir
- (
- p_hashcode char(40)
- )
- LABEL_PROC:
- BEGIN
- DECLARE v_sql varchar(200);
- SET v_sql = CONCAT('SELECT filedir FROM ipsp_resources WHERE hashcode =/'', p_hashcode, '/' LIMIT 0, 1');
- SET @sql = v_sql;
- PREPARE sl FROM @sql;
- EXECUTE sl;
- DEALLOCATE PREPARE sl;
- END LABEL_PROC //
<span>CREATE</span> <span>PROCEDURE</span> ipsp_getresourcedir
(
p_hashcode char(40)
)
LABEL_PROC:
BEGIN
DECLARE v_sql varchar(200);
SET v_sql = CONCAT('SELECT filedir FROM ipsp_resources WHERE hashcode =/'', p_hashcode, '/' LIMIT 0, 1');
SET @<span>sql</span> = v_sql;
PREPARE sl FROM @<span>sql</span>;
EXECUTE sl;
DEALLOCATE PREPARE sl;
END LABEL_PROC //
这里提一下 “/”是转义字符，拼接成的SQL类似 SELECT filedir FROM ipsp_resources WHERE hashcode ='xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' LIMIT 0, 1
另外@sql这个属于用户变量，具体用法请查询MySQL参考手册。
如果有在MS SQL SERVER上编写存储过程的经验的话，看完这些，我想基本的MySQL存储过程编程应该可以应付了吧！
想了解更多的内容可查询MySQL参考手册或者相关书籍！
