USE test;
1、要使用临时变量(会话变量),用@修饰
2、要使用局部变量,用declare 修饰
SET @ret = 24/12*2;
SELECT @ret;
#这是对的
DECLARE ret INT;
SET ret=24/12;
==========================
存储过程
DELIMITER $$
CREATE PROCEDURE up1()
BEGIN
SELECT 'hello world!' AS 问候;
END$$
DELIMITER ;
CALL up1(); #调用存储过程
DROP PROCEDURE up1; #删除一个存储过程
==========================
DELIMITER $$
CREATE PROCEDURE up2()
BEGIN
DECLARE age INT DEFAULT 23;
SET age=24;
SELECT CONCAT('你已经',age,'了') AS 结果;
END$$
DELIMITER ;
CALL up2();
==========================
DELIMITER $$
CREATE PROCEDURE up3()
BEGIN
DECLARE age INT ;
DECLARE cxm VARCHAR(20);
SELECT xm,DATEDIFF(NOW(),sr)/365 INTO cxm,age
FROM xuesheng
WHERE xm='周地';
SELECT CONCAT(cxm,'的年龄是:',age);
END$$
DELIMITER ;
CALL up3();
DROP PROCEDURE up3;
==========================
存储过程中,可以传递参数进去控制流程
MySQL 的参数有三种:
1、输入参数,用 IN 修饰
2、输出参数,用 OUT 修饰
3、输入输出参数,用 INOUT 修饰
MySQL 的流程控制语句有2种:
1、判断语句
(1)
IF 条件 THEN
<语句体>
END IF;
IF 条件 THEN
<语句体1>
ELSE
<语句体2>
END IF;
(2)
CASE < 表达式>
WHEN <值1> THEN <语句体1>
WHEN <值2> THEN <语句体2>
WHEN <值3> THEN <语句体3>
ELSE <语句体4>
END CASE;
CASE
WHEN <条件1> THEN <语句体1>
WHEN <条件2> THEN <语句体2>
WHEN <条件3> THEN <语句体3>
ELSE <语句体4>
END CASE;
2、循环语句
(1)
LOOP
<语句体>
END LOOP;
(2)
WHILE <条件> DO
<语句体>
END WHILE;
(3)
REPEAT
<语句体>
UNTIL <条件>
END REPEAT;
DELIMITER $$
CREATE PROCEDURE up4(IN cname VARCHAR(20))
BEGIN
DECLARE age INT ;
DECLARE czy VARCHAR(60);
SELECT zy,DATEDIFF(NOW(),sr)/365 INTO czy,age
FROM xuesheng
WHERE xm=cname;
IF age>19 THEN
SELECT CONCAT(czy,'的',cname,',你太老了!');
ELSE
SELECT CONCAT(czy,'的',cname,',你要多吃点,长高点!');
END IF;
END$$
DELIMITER ;
SHOW WARNINGS;
DROP PROCEDURE up4;
SET @arg='周地';
CALL up4(@arg);
CALL up4('赵志群');
==========================
DELIMITER $$
CREATE PROCEDURE up5(IN arga INT,IN argb INT,IN argsign VARCHAR(1))
BEGIN
DECLARE result INT;
SET result=0;
CASE argsign
WHEN '+' THEN SET result=arga+argb;
WHEN '-' THEN SET result=arga-argb;
WHEN '*' THEN SET result=arga*argb;
WHEN '/' THEN SET result=arga/argb;
END CASE;
SELECT result;
END$$
DELIMITER ;
CALL up5(2,3,'*');
CALL up5(2,3,'-');
CALL up5(2,3,'+');
========================================
DELIMITER $$
CREATE PROCEDURE up6(IN cname VARCHAR(20),OUT ret VARCHAR(320))
BEGIN
DECLARE age INT ;
DECLARE czy VARCHAR(60);
SELECT zy,DATEDIFF(NOW(),sr)/365 INTO czy,age
FROM xuesheng
WHERE xm=cname;
IF age>19 THEN
SELECT CONCAT(czy,'的',cname,',你太老了!') INTO ret;
ELSE
SELECT CONCAT(czy,'的',cname,',你要多吃点,长高点!') INTO ret;
END IF;
END$$
DELIMITER ;
DROP PROCEDURE up6;
SET @arg='周地';
CALL up6(@arg,@retten);
SELECT @retten;
CALL up6('赵志群',@retten);
SELECT @retten;