MYSQL 存储过程语法入门

迈不过友情╰ 2022-09-18 06:44 4阅读 0赞

首先看MySQL 5.0参考手册中关于创建存储过程的语法说明：

1. CREATE    
   2. \[DEFINER = \{ user | CURRENT\_USER \}\]    
   3. PROCEDURE sp\_name (\[proc\_parameter\[,...\]\])    
   4. \[characteristic ...\] routine\_body    
   5. proc\_parameter:    
   6. \[ IN | OUT | INOUT \] param\_name type    
   7. type:    
   8. Any valid MySQL data type    
   9. characteristic:    
  10. LANGUAGE SQL    
  11. | \[NOT\] DETERMINISTIC    
  12. | \{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA \}    
  13. | SQL SECURITY \{ DEFINER | INVOKER \}    
  14. | COMMENT 'string'    
  15. routine\_body:    
  16. Valid SQL procedure statement

如果你对MySQL还不太熟悉的话，单单看这个语法结构当然不足以进行MySQL存储过程编程。我之前基本都是使用MS SQL SERVER，所以以下记录我熟悉MySQL存储过程的过程，也是重点介绍MS SQL SERVER与MySQL区别较大的地方。

第一步，当然是写个Hello Word的存储过程，如下：

1. CREATE PROCEDURE phelloword()    
   2. BEGIN    
   3. SELECT 'Hello Word!' AS F;    
   4. 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;没有执行，这显然会导致错误。

这里MySQL 5.0需要选择以个分隔符，语法如下：DELIMITER //

分隔符是通知MySQL客户端已经输入完成的符号。一直都是用“;”，但是在存储过程中不行，因为存储过程中很多语句都需要用到分号。

因此上面的存储过程改为：

1. CREATE PROCEDURE ptest()    
   2. BEGIN    
   3. SELECT 'Hello Word!' AS F;    
   4. END //

另外在phpMyAdmin中执行时，在Delimiter文本框中填写 //，这次存储过程即可创建成功。

第二步，写一个包括参数，变量，变量赋值，条件判断，UPDATE语句，SELECT返回结果集的完整的一个存储过程，如下：

1. CREATE PROCEDURE plogin    
   2. (    
   3. p\_username char(15),    
   4. p\_password char(32),    
   5. p\_ip char(18),    
   6. p\_logintime datetime    
   7. )    
   8. LABEL\_PROC:    
   9. BEGIN     
  10. DECLARE v\_uid mediumint(8);     
  11. DECLARE v\_realpassword char(32);     
  12. DECLARE v\_nickname varchar(30);     
  13. DECLARE v\_oltime smallint(6);     
  14. SELECT u.uid, u.password, f.nickname, u.oltime INTO v\_uid, v\_realpassword, v\_nickname, v\_oltime    
  15. FROM cdb\_members u INNER JOIN cdb\_memberfields f ON f.uid = u.uid WHERE u.username = p\_username;    
  16. IF (v\_uid IS NULL) THEN    
  17. SELECT 2 AS ErrorCode;    
  18. LEAVE LABEL\_PROC;    
  19. END IF;    
  20. IF (p\_password <> v\_realpassword) THEN    
  21. SELECT 3 AS ErrorCode;    
  22. LEAVE LABEL\_PROC;    
  23. END IF;    
  24. UPDATE ipsp\_userexpands SET lastloginip = p\_ip, lastlogintime = p\_logintime WHERE uid = v\_uid;    
  25. SELECT 0 AS ErrorCode, v\_uid AS uid, v\_nickname AS nickname, v\_oltime AS oltime;    
  26. 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;这种方式给变量赋值。

其次是条件判断的语法结构，如下所示：

1. IF ... THEN    
   2. ...;    
   3. ELSE    
   4. IF ... THEN    
   5. ...;    
   6. ELSEIF    
   7. ...;    
   8. ELSE    
   9. ...;    
  10. END IF;    
  11. END IF;

最后说说LEAVE 语法的使用。当满足某种条件，不继续执行下面的SQL时，在MS SQL SERVER中使用RETURN语法，在MySQL中我没有找到对应的关键字，但是这里可以利用LEAVE语法来满足要求，在存储过程的BEGIN前定义一个标签，如：“LABEL\_PROC:” 然后再需要用到RETURN中断执行的地方执行“LEAVE LABEL\_PROC;”即可。

第三步，创建一个执行动态SQL的存储过程。

1. CREATE PROCEDURE ipsp\_getresourcedir    
   2. (    
   3. p\_hashcode char(40)    
   4. )    
   5. LABEL\_PROC:    
   6. BEGIN    
   7. DECLARE v\_sql varchar(200);    
   8. SET v\_sql = CONCAT('SELECT filedir FROM ipsp\_resources WHERE hashcode =\\'', p\_hashcode, '\\' LIMIT 0, 1');    
   9. SET @sql = v\_sql;    
  10. PREPARE sl FROM @sql;    
  11. EXECUTE sl;    
  12. DEALLOCATE PREPARE sl;    
  13. END LABEL\_PROC //

这里提一下 “\\”是转义字符，拼接成的SQL类似 SELECT filedir FROM ipsp\_resources WHERE hashcode ='xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' LIMIT 0, 1

另外@sql这个属于用户变量，具体用法请查询MySQL参考手册。

如果有在MS SQL SERVER上编写存储过程的经验的话，看完这些，我想基本的MySQL存储过程编程应该可以应付了吧！

想了解更多的内容可查询MySQL参考手册或者相关书籍！以上的相关内容就是对小题大做之MySQL 5.0存储过程编程入门的介绍，望你能有所收获。