导航

小题大做之MySQL 5.0存储过程编程入门(收藏)

Posted on 2011-07-15 15:51  kingwangzhen  阅读(229)  评论(0编辑  收藏  举报

MySQL 版本:5.0.45    phpMyAdmin版本:2.11.3

 

首先看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 SERVERMySQL区别较大的地方。

 

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

CREATE PROCEDURE 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客户端已经输入完成的符号。一直都是用“;”,但是在存储过程中不行,因为存储过程中很多语句都需要用到分号。

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

CREATE PROCEDURE ptest()

BEGIN

 SELECT 'Hello Word!' AS F;

END //

 

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

 

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

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 //

 

首先要说的是给变量赋值的语法,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;这种方式给变量赋值。

 

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

IF ... THEN

    ...;

ELSE

    IF ... THEN

     ...;

    ELSEIF

     ...;

    ELSE

     ...;

    END IF;

END IF;

 

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

 

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

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 //

 

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

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

 

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

 

想了解更多的内容可查询MySQL参考手册或者相关书籍!