如我们需要查询课程号为1的学生的人数和平均成绩,则存储过程定义如下:CREATE PROCEDURE Proc_AVG(IN classID INT,OUT total INT,OUT a_s FLOAT) BEGIN SELECT COUNT(*),AVG(grade) INTO total,a_s FROM tb_score WHERE cID=classID; END;
创建存储函数
创建存储函数需要使用CREATE FUNCTION语句,基本语法格式为:CREATE FUNCTION func_name([func_parameter]) returns type [characteristic] routine_body CREATE FUNCTION为用来创建存储函数的关键字,func_name表示存储函数的名称,func_parameter为存储过程的参数列表如下:
[IN|OUT|INOUT] param_name type 其参数含义同存储过程(PROCEDURE)相同,其默认为IN参数。
RETURNS type语句表示函数返回数据的类型,characteristic指定存储函数的特性,取值与创建存储过程时相同。
查询某个学生某门课程的成绩函数代码为:CREATE FUNCTION Query_score(classID INT,studentID INT) RETURNS INT RETURN (SELECT grade FROM tb_score WHERE cID=classID AND sID=studentID); 通过SELECT Query_score(1,1)查询1号学生1号课程的成绩。
读者可能会发现存储过程的查询结果可能为多个值,而存储函数的查询结果是某一类型的单值。而且存储过程在调用时用CALL而存储函数是SELECT。那么存储过程和函数具体的区别又是什么呢?
存储过程的功能更加复杂,而函数的功能针对性更强;
存储过程可以返回参数(通过OUT|INOUT),而函数只能返回单一值或者表对象;
存储过程作为一个独立的部分来执行,而函数可以作为查询语句的一部分来调用,由于函数可以返回一个表对象,因此它可以在查询语句中位于FROM关键字之后;
存储过程是通过关键字CALL来调用,作为一个独立的执行部分。而存储函数则可作为SELECT语句的一部分调用,嵌入到SQL语句中;
当存储过程和函数被执行的时候,SQLManager会到procedure cache中去取相应的查询语句,如果在procedure cache里没有相应的查询语句,SQLManager就会对存储过程和函数进行编译。
变量的使用
变量可以在子程序中声明并使用,作用范围是在BEGIN...END程序中,如下将主要介绍如何定义变量和为变量赋值。
定义变量。语法格式为:DECLARE var_name[,var_name]...data_type[DEFAULT value]; var_name为局部变量名称,DEFAULT value给变量提供一个默认值。值除了可以被声明为一个常数之外,还可以被指定为一个表达式。如果缺少DEFAULT子句,初始值为NULL。
为变量赋值。MySQL中使用SET语句为变量赋值,语法格式为:SET var_name=expr[,var_name=expr]...
流程控制的使用
流程控制与用来根据条件控制语句的执行。MySQL中的用来构造控制流程的语句有:IF语句、CASE语句、LOOP语句、LEAVE语句、ITERATE语句、REPEAT语句和WHILE语句。各语句介绍如下:
IF语句。包含多个条件判断,根据判断的结果为TRUE或FALSE执行相应的语句,语法格式为 IF expr_condition THEN statement_list ESLEIF expr_condition THEN statement_list ESLE statement_list END IF 如下我们定义一个学生成绩等级评定函数,将学生成绩以参数的形式传输函数,输出学生成绩等级A(90~100)、B(75~90)、C(60~75)、D(60以下)。代码编写中需要注意,IF中如果有多个限制条件,限制条件间用AND连接,DECLARE的变量声明必须在BEGIN内,以及字符串之间拼接用CONCAT。该功能代码如下图所示:
CASE语句。另外一个进行条件判断的语句,该语句有2种语句格式。
第一种格式为:CASE case_expr WHEN value THEN statement_list [WHEN value THEN statement_list] [ELSE statement_list] END CASE 其中case_expr参数表示条件判断的表达式,决定哪个WHEN子句会被执行,value表示表达式可能的值,如果case_expr等于某个value,则执行相应value后的statement_list。