四、区块，条件，循环
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;
