当前位置:Gxlcms > mysql > 小题大做之MySQL5.0存储过程编程入门

小题大做之MySQL5.0存储过程编程入门

时间:2021-07-01 10:21:17 帮助过:35人阅读

首先看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代码

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

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

Java代码

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

CREATE PROCEDURE ptest()
BEGIN
  SELECT 'Hello Word!' AS F;
END //


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


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

Java代码

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

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;这种方式给变量赋值。

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

Java代码

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

IF ... THEN
    ...;
ELSE
    IF ... THEN
      ...;
    ELSEIF
      ...;
    ELSE
      ...;
    END IF;
END IF;


最后说说LEAVE 语法的使用。当满足某种条件,不继续执行下面的SQL时,在MS SQL

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

Java代码

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

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参考手册或者相关书籍!

人气教程排行