存储过程 函数 1、create procedure 创建存储过程 create function 创建函数 2、两者区别： （1）、函数调用有返回值 （2）、存储过程调用用call语句，调用函数直接引用函数名+参数即可。 3、in、out、inout三个参数前的关键词只适用于存储过程，对函数而言所有的参数默认都是输入参数。 in 用于把数值传入到存储过程 out输出参数把数值传递到调用者，初始值为null inout输入输出参数，把数据传入到存储过程，在存储过程中修改之后再传递到调用者。 Delimiter命令是改变语句的结束符，MySQL默认的结束符为;号，由于procedure和function中的;号并不代表创建的结束，所以要替换成另外的结束符以便表示创建的结束 4、创建存储过程示例 ~~~ mysql> delimiter // mysql> CREATE PROCEDURE simpleproc (IN param1 int,OUT param2 INT) -> BEGIN -> SELECT COUNT(*) INTO param2 FROM students where sid>param1; -> END// mysql> delimiter ; CALL simpleproc(1, @a); select @a; ~~~ 5、创建函数： ~~~ delimiter // create function hello(s char(20)) returns char(50) deterministic return concat('kkkk,',s,'!');// delimiter ; select hello('fedf'); select * from students where sname=hello(sname); ###字段被变量使用 ~~~ 6、通过 begin...end将多个sql语句包含在一起。 comment子句用来写入对存储过程和函数的注释。 language子句用来表示此存储过程和函数的创建语言。 存储过程和函数被标注为deterministic表明当输入相同的参数是会返回相同的结果，反之如果是not deterministic则表示相同参数不会是相同结果，默认是not deterministic ~~~ 示例： mysql> delimiter // mysql> create function simplefunc(param1 int) -> returns int -> begin -> update students set sex=1 where sid=param1; -> select count(*) into @a from students where sid>param1; -> return @a; -> end; -> // delimiter ; ~~~ #######returns 后跟返回值类型 7、Drop procedure/function语句用来删除指定名称的存储过程或函数 If exists关键词用来避免在删除一个本身不存在的存储过程或函数时，MySQL返回错误 示例： `drop procedure simpleproc;` 8、Begin…end语句通常出现在存储过程、函数和触发器中，其中可以包含一个或多个语句，每个语句用;号隔开 9、标签label可以加在begin…end语句以及loop, repeat和while语句 语句中通过iterate和leave来控制流程，iterate表示返回指定标签位置，leave表示跳出标签 ~~~ delimiter // CREATE PROCEDURE doiterate(IN p1 INT, OUT p2 int) BEGIN label1: LOOP SET p1 = p1 + 1; IF p1 < 10 THEN ITERATE label1; END IF; LEAVE label1; END LOOP label1; set p2=p1; END; // delimiter ; call doiterate (2,@x); ~~~ 10、Declare语句通常用来声明本地变量、游标、条件或者handler Declare语句只允许出现在begin … end语句中而且必须出现在第一行 Declare的顺序也有要求，通常是先声明本地变量，再是游标，然后是条件和handler 本地变量可以通过declare语句进行声明 声明后的变量可以通过select … into var_list进行赋值，或者通过set语句赋值，或者通过定义游标并使用fetch … into var_list赋值 示例： declare var_name [ , var_name] ... type [default value] 使用default指定变量的默认值，如果没有指定默认值则初始值为NULL。 Type指明该变量的数据类型。 声明的变量作用范围为被声明的begin … end语句块之间 声明的变量和被引用的数据表中的字段名要区分开来，否则会报错 ~~~ delimiter // CREATE PROCEDURE sp1 (v_sid int) BEGIN DECLARE xname VARCHAR(50) DEFAULT 'bob'; DECLARE xsex INT; SELECT sname, gender INTO xname, xsex FROM students WHERE sid=v_sid; SELECT xname,xsex; END; // delimiter ; ~~~ 课程练习： 1、创建一个过程，将男生和女生的学生数据分别存储到男生表和女生表中。 ~~~ create table students_man like students; create table students_woman like students; delimiter // create procedure sp2 () begin insert into students_man select * from students where gender=0; insert into students_woman select * from students where gender=1; end// delimiter ; ~~~ 2、创建一个过程，将每个学生的课程数，平均成绩，及格课程数，非及格课程数都存放在单独的表中 ~~~ create table temp1(course_id int,avg_score int,cou_score int); delimiter // Create procedure proc2() Begin Insert into temp1 select Sid,count(*),avg(score),sum(case when score>=60 then 1 else 0 end),sum(case when score<60 then 1 else 0 end)From score Group by Sid; End// delimiter ; ~~~ 3、创建一个过程，将学生sid作为输入参数，结果展示出该学生的课程数和平均成绩 delimiter // ~~~ create procedure sp3 (in a1 int) begin select count(course_id) ,avg(score) from score where sid=a1; end// delimiter ; ~~~ 4、创建一个函数，以学生sid作为输入参数，将该学生的课程数和平均成绩存放在单独的表中，并返回平均成绩 ~~~ create table temp2(course_nu int,avg_score int); delimiter // create function f1( a1 int) returns int begin Declare avg_score int； insert into temp2 select count(course_id) ,avg(score) from score where sid=a1 group by sid; select avg(score) into @x from score where sid=a1 group by sid; return @x; end// delimiter ; ~~~