条件判断

if 条件 then...

elseif .... then

else....

end if;

---

循环

while  do

---

label : loop 

 end loop

---

repeat ...

 until  条件 end repeat

---

函数存储        procedure 过程

DELIMITER $$   //定义结束符

create PROCEDURE p1()

BEGIN

select * from student;

end;

$$

DELIMITER ;

call p1();

delimiter $$

CREATE PROCEDURE  p2(in stusno VARCHAR(10), in stucno VARCHAR(10),out lev VARCHAR(10))

BEGIN

declare tempscore DECIMAL(4,1);

SELECT  degree  into tempscore from scorce where sno=stusno and cno=stucno;

IF(tempscore>=90) THEN

SET lev='优秀';

elseIF(tempscore>=75) THEN

set lev='良好';

elseif (tempscore>=60) THEN

set lev='及格';

ELSE

set lev='不及格';

end if;

END;

$$

delimiter;

set @lev='';

call p2('1001','02',@lev);

SELECT @lev;

---

游标

//$$结束

delimiter $$

//创建存储过程

CREATE PROCEDURE p3()

BEGIN

//声明变量

declare sno VARCHAR(10);

DECLARE sname VARCHAR(10);

DECLARE cname VARCHAR(10);

DECLARE degree  DECIMAL(4,1);

DECLARE lev VARCHAR(10);

// 结束标志

DECLARE done int default 0;

//声明游标

DECLARE cur CURSOR FOR

select s.sno,s.sname,c.cname,g.degree

FROM student s, course c, scorce g

where s.sno= g.sno and c.cno=g.cno;

//将结束标志绑定到游标

DECLARE  CONTINUE HANDLER for not found  set done=1;

CREATE TEMPORARY TABLE temp_table(

sno VARCHAR(10),

sname VARCHAR(10),

cname VARCHAR(10),

degree  DECIMAL(4,1),

lev VARCHAR(10)

);

open cur;

REPEAT

//游标向下走一步

FETCH cur INTO sno,sname,cname,degree;

IF(degree>=90) THEN

SET lev='优秀';

ELSEIF(degree>=75) THEN

SET lev='良好';

ELSEIF(degree>=60) THEN

SET lev='及格';

ELSE

SET lev='不及格';

end if;

//输出格式

select CONCAT_WS(',',sno,sname,cname,degree,lev);

INSERT INTO temp_table

VALUES(sno,sname,cname,degree,lev);

UNTIL done

END repeat;

CLOSE cur;

SELECT * from temp_table;

end

$$

delimiter