MySQL存储过程与函数(下）

四、区块，条件，循环

1、区块定义，常用

begin
......
end;      

也可以给区块起别名，如：

lable:begin
...........
end lable;      

可以用leave lable;跳出区块，执行区块以后的代码

2、条件语句

if 条件 then
statement
else
statement
end if;      

3、循环语句

(1)while循环

[label:] WHILE expression DO

statements

END WHILE [label] ;      

(2)、loop循环

[label:] LOOP

statements

END LOOP [label];      

(3)、repeat until循环

[label:] REPEAT

statements

UNTIL expression

END REPEAT [label] ;      

五、其他常用命令

1.show procedure status

显示数据库中所有存储的存储过程基本信息，包括所属数据库，存储过程名称，创建时间等

2.show create procedure sp_name

显示某一个存储过程的详细信息

函数function示例

CREATE DEFINER=`root`@`%` FUNCTION `spr_checkadmin`(acckey varchar(32), accpwd varchar(64)) RETURNS int(11)
BEGIN
DECLARE x INT;
SELECT COUNT(*) INTO x FROM admins WHERE account=acckey AND passwd=accpwd;
RETURN(x);
END;      

单个返回值的存储过程

CREATE DEFINER=`root`@`%` PROCEDURE `spr_getuserstorage`(tok varchar(128))
BEGIN
DECLARE acc VARCHAR(32);
DECLARE pkgid VARCHAR(32);
DECLARE regdate DATETIME;
DECLARE logindate DATETIME;
DECLARE sumsize BIGINT;
SELECT account INTO acc FROM userinfo WHERE token=tok;
IF (acc != NULL) THEN
SELECT SUM(filesize) INTO sumsize FROM userfiles WHERE account=acc;
SELECT packageid, registerdate, lastlogindate INTO pkgid, regdate, logindate FROM userinfo WHERE account=acc;
SELECT 0,pkgid,regdate,logindate;
ELSE
SELECT(-1);
END IF;      

多个返回值存储过程

CREATE DEFINER=`root`@`%` PROCEDURE `spr_queryfolderallfile`(sToken varchar(32), OUT sfid varchar(32), OUT sfext varchar(32))
BEGIN
DECLARE acc CHAR(32);
SELECT account INTO acc FROM userinfo WHERE token=sToken;
IF (acc != NULL) THEN
SELECT fileid, fileext INTO sfid, sfext FROM userfiles WHERE account=acc AND filetype=1;
END IF;
END;