mysql 存储过程和函数

  1 /*
  2 一、 函数和存储过程的相同点：函数和存储过程都是事先预编译并保存在数据库中的特殊的数据库对象，
　　　　　需要执行相应功能时就可以直接通过“函数名”、“存储过程”调用其中的代码，以提高执行效率和代码的复用性。
  3 二、 函数和存储过程的不同点：
  4 1、函数在定义部分有returns指定返回值类型，同时在函数体中必须使用return语句返回结果；
　　　　存储过程则在函数参数位置通过输入或输出参数向外输出结果而不能使用返回值return返回结果。
  5 2、存储过程的参数有IN、OUT、INOUT（传入传出）作为指定参数的传递方向；而函数的参数通常都是IN方向，通常可以省略不写。
  6 3、函数可以在任意的语句中调用，但存储过程需要通过call来调用。
  7 4、函数通常是一些固定功能的代码合集，具有一定的通用性，数据库开发者定义了许多函数可以使用，但存储过程通常需要我们自己根据项目定义。

  8 三、自定义函数的定义和使用
  9     所有的数据库管理系统都提供了许多系统函数，已经足够我们使用，但它也提供我们可以自定义函数的途径，以下是有关自定义函数的内容。*/
 10 
 11 /*
 12 自定义函数的语法
 13 DELIMITER $$  -- 定界符，将默认的语句结束符设置为$$
 14 CREATE FUNCATION <函数名> （参数名 参数数据类型（长度）)
 15 returns 返回值类型
 16 BEGIN
 17 #函数的实现部分（函数体）
 18 END $$
 19 DELIMITER ;  -- 将定界符改回来
 20 */
 21 
 22 -- 定义函数


 23 DELIMITER $$
 24 CREATE FUNCTION func_hello(name VARCHAR(20))
 25  RETURNS VARCHAR(20)
 26 BEGIN
 27 RETURN CONCAT("您好",name);
 28 END $$
 29 DELIMITER ;
 30 
 31 -- 调用函数,出入参数给name
 32 SELECT func_hello("张三");
 33 
 34 -- 删除函数
 35 drop FUNCTION func_hello;
 36 
 37 
 38 /*
 39 四、存储过程的定义和使用
 40 1、语法：
 41 DELIMITER $$  -- 定界符，将默认的语句结束符设置为$$
 42 CREATE PROCEDUCE<存储过程名> （传递方向 参数名 参数数据类型（长度）） returns 返回值类型
 43 BEGIN
 44 #存储过程的实现部分（存储过程体）
 45 END $$
 46 DELIMITER ;  -- 将定界符改回来
 47 2、    存储过程的案例1：入门
 48 */
 49 
 50 
 51 -- 查询成绩表中成绩合格的记录数，通过传出参数传递给调用者
 52 DROP PROCEDURE IF EXISTS proc_score_count;
 53 DELIMITER $$
 54 CREATE PROCEDURE proc_score_count(OUT result INT(3))
 55 BEGIN
 56     -- DECLARE num INT(10) DEFAULT 10;        -- 定义局部变量,后面可加默认值
 57     SELECT COUNT(*) INTO result 
 58     FROM tb_score WHERE student_score > ‘60‘;
 59 END $$
 60 DELIMITER ;
 61 
 62 -- 调用存储过程
 63 CALL proc_score_count(@及格人数);        -- @表示一个全局变量，用于接收存储过程输出的值
 64 
 65 -- 查看返回结果,通过select语句查看全局变量
 66 SELECT @及格人数;
 67 
 68 -- 删除存储过程
 69 DROP PROCEDURE proc_score_count;
 70 
 71 
 72 -- 存储过程的案例2：在存储过程中定义局部变量
 73 -- 向tb_grade表中插入一条记录，需要传入字段的值
 74 DROP PROCEDURE IF EXISTS proc_insertgrade;
 75 DELIMITER $$
 76 CREATE PROCEDURE proc_insertgrade(IN grade_name VARCHAR(10))
 77 BEGIN
 78     DECLARE name1 VARCHAR(10);        -- 定义局部变量,后面可加默认值
 79     SET name1 = grade_name;
 80     INSERT INTO tb_grade VALUES (null,name1);
 81 END $$
 82 DELIMITER ;
 83 
 84 -- 调用存储过程
 85 CALL proc_insertgrade(‘六年级‘);
 86 
 87 SELECT * FROM tb_grade ORDER BY grade_id;
 88 
 89 
 90 -- 存储过程的案例3：流程控制语句（IF）
 91 -- 案例描述：用户输入“课程编号”，查询对应编号的成绩
 92 DROP PROCEDURE IF EXISTS proc_selectscore_if;
 93 DELIMITER //
 94 CREATE PROCEDURE proc_selectscore_if(IN id INT(3))
 95 BEGIN
 96     IF id = 1 THEN
 97         SELECT * FROM tb_score WHERE subject_id = id;
 98     ELSEIF id = 2 THEN
 99         SELECT * FROM tb_score WHERE subject_id = 2;
100     ELSEIF id = 3 THEN
101         SELECT * FROM tb_score WHERE subject_id = 3;
102     ELSE
103         SELECT ‘您的输入有误！‘ FROM DUAL;        -- 从虚拟表4
104 END IF;
105 END //
106 DELIMITER ;
107 
108 CALL proc_selectscore_if(3);
109 
110 -- 存储过程的案例4：流程控制语句（case）
111 -- 案例描述：用户输入“课程编号”，查询对应编号的成绩
112 
113 DROP PROCEDURE IF EXISTS proc_selectscore_case;
114 DELIMITER //
115 CREATE PROCEDURE proc_selectscore_case(IN id INT(3))
116 BEGIN
117     CASE id
118         WHEN 1 THEN
119             SELECT * FROM tb_score WHERE subject_id = 1;
120         WHEN 2 THEN
121                 SELECT * FROM tb_score WHERE subject_id = 2;
122         WHEN 3 THEN
123                 SELECT * FROM tb_score WHERE subject_id = 3;
124         ELSE
125             SELECT ‘您的输入有误！‘ FROM DUAL;        -- 从虚拟表
126 END case;
127 END //
128 DELIMITER ;
129 
130 CALL proc_selectscore_case(3);
131 
132 
133 -- 存储过程的案例5：流程控制语句（loop循环）
134 -- 案例描述：输出1-最大值之间的奇数次
135 DROP PROCEDURE IF EXISTS proc_loop;
136 DELIMITER //
137 CREATE PROCEDURE proc_loop(IN MAX INT(3))
138 BEGIN
139     DECLARE num INT(3) DEFAULT 0;
140     xh:LOOP
141         SET num = num + 1;        /*更新变量*/
142         IF num>max THEN
143             LEAVE xh;
144         END IF;
145         IF MOD(num,2) = 0 THEN
146             ITERATE xh;
147         END IF;
148         SELECT CONCAT("第",num,"次输出");
149     END loop;
150 END //
151 DELIMITER ;
152 
153 CALL proc_loop(6);