MySql的存储过程学习小结 附pdf文档下载

CREATE PROCEDURE procedurel /*name 存储过程名*/
(IN parameter1 INTEGER) /*parameters 参数*/
BEGIN /*start of blokc 语句块头*/
DECLARE variable1 CHAR(10); /*variables变量声明*/
IF parameter1 = 17 THEN /*start of IF IF条件开始*/
SET variable1 = 'birds'; /*assignment赋值*/
END IF; /*end of IF IF结束*/
INSERT INTO table1 VALUES (variable1);/*statement SQL语句*/
END /*end of block 语句块结束*/

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

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

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

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

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