Mysql存储过程和函数

存储过程和函数

Mysql从5.0 版本开始支持存储过程

简单说，存储过程就是一条或多条的sql语句集合，我们可以简单看成是批文件处理

优点

• 封装我们sql语句，可以支持复杂的sql语句
• 提高我们sql表的安全性，比如一些敏感的字段，可以设置访问权限
• 可以减少磁盘访问次数，提高查询效率

存储过程和函数区别

• 本质上都是存储程序
• 函数只能通过return 返回单个值或者表对象
• 存储过程不允许return 只能通过out参数返回值
• 函数不能使用临时表，只能使用表变量，一些函数也不可以用，存储过程可以
• 函数可以嵌入sql语句使用，比如select hello();
• 存储过程通过call 调用

01创建存储过程和函数

1. 创建存储过程

基本语法

create procedure sp_name([proc_parameter]) [characteristics] routine_body

• sp_name 存储过程名字
• proc_parameter 存储过程的参数列表, 可选, 格式: [in | out |inout] param type
• characteristics 存储过程的特性, 可选, 比如权限，注释，初学者先知道有这个点即可
• routin_body SQL代码的内容, 可以用begin … end 表示开始和结束

实例

mysql> delimiter //
mysql> create procedure countproc(out param1 int)
    -> begin
    -> select count(*) into param1 from fruits;
    -> end //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call countproc(@value);
Query OK, 1 row affected (0.00 sec)

mysql> select @value;
+--------+
| @value |
+--------+
|     16 |
+--------+
1 row in set (0.00 sec)

2. 创建存储函数
   基本语法

create function func_name([func_parameter]) 
returns type
[characteristic] routine_body

• func_name 存储函数名字
• func_parameter 存储过程的参数列表 格式: [in| out| inout] param_name type
• returns type 语句表示函数返回数据的类型
• characteristic 同创建存储过程
• routine_body 同上, 另外对函数而言函数体必需包含一个return value 语句

实例

mysql> delimiter //
mysql> CREATE FUNCTION namebyzip()
    -> returns char(50)
    -> return (select s_name from suppliers where s_call='48075');
    -> //
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> delimiter ;
mysql> select namebyzip();
+----------------+
| namebyzip()    |
+----------------+
| FastFruit Inc. |
+----------------+
1 row in set (0.00 sec)

mysql>

02变量的使用

变量是在存储过程和存储函数中的routine_body声明和使用

基本语法

declare var_name data_type [default value];

• var_name 局部变量名字
• data_type Mysql数据类型
• default value 默认值

变量赋值

# 方法1
set var_name = expr;
# 方法2
select col_name into var_name from table;

03光标的使用

作用：查询语句返回多条记录，可以使用光标逐条读取查询结果集中的记录

1. 声明光标

declare cursor_name cursor for select_statement

• cursor_name 表示光标名字
• select_statement 表示select语句 返回一个创建光标的结果集

2. 打开光标

open cursor_name;

3. 使用光标

fetch cursor_name into var_name [, var_name]... {参数名字}

注意var_name参数必须在声明光标之前就定义好
4. 关闭光标

close cursor_name;

04流程控制的使用

1. if
2. case
3. loop
4. leave
5. iterate
6. repeat
7. while

05查看存储过程和函数

1. 使用show status查看存储过程和函数状态

mysql> show procedure status like 'c%' \G;
*************************** 1. row ***************************
                  Db: sys
                Name: create_synonym_db
                Type: PROCEDURE
             Definer: mysql.sys@localhost
            Modified: 2022-03-29 15:18:09
             Created: 2022-03-29 15:18:09
       Security_type: INVOKER
             Comment:

2. 使用show create 语句查看存储过程和函数的定义

基本语法

show create [procedure | function] sp_name

例子

mysql> delimiter //
mysql> create function hello()
    -> returns char(50)
    -> return "hello world !";
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> select hello() //
+---------------+
| hello()       |
+---------------+
| hello world ! |
+---------------+
1 row in set (0.00 sec)

mysql>
mysql> delimiter ;
mysql> show create function hello\G;
*************************** 1. row ***************************
            Function: hello
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
     Create Function: CREATE DEFINER=`root`@`localhost` FUNCTION `hello`() RETURNS char(50) CHARSET utf8mb4
return "hello world !"
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

3. 从information_schema.routines 表中查看存储过程和函数的信息

基本语法

select * from information_schema.routines where routine_name = ' sp_name ';

例子

mysql> select * from information_schema.routines where routine_name = 'countproc' and routine_type = 'FUNCTION' \G;
Empty set (0.00 sec)

06综合案例–创建存储过程和函数

mysql> #创建一个名称sch的数据表，插入数据
mysql> drop table if exists sch;
Query OK, 0 rows affected (0.00 sec)

mysql> create table sch(
    -> id int(10),
    -> name varchar(50),
    -> glass varchar(50)
    -> );

Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql>
mysql> insert into sch values
    -> (1,'xiaoming','glass 1'),
    -> (2,'xiaojun','glass 2');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql>
mysql> desc sch;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(50) | YES  |     | NULL    |       |
| glass | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql>
mysql> select * from sch;
+------+----------+---------+
| id   | name     | glass   |
+------+----------+---------+
|    1 | xiaoming | glass 1 |
|    2 | xiaojun  | glass 2 |
+------+----------+---------+
2 rows in set (0.00 sec)

mysql>
mysql> # 步骤2 创建一个存储函数用来统计表sch 的记录数
mysql> delimiter //
mysql> create function count_sch()
    -> returns int
    -> return (select count(*) from sch);
    -> //
ERROR 1304 (42000): FUNCTION count_sch already exists
mysql> delimiter ;
mysql> select count_sch();
+-------------+
| count_sch() |
+-------------+
|           2 |
+-------------+
1 row in set (0.00 sec)

mysql>
mysql> # 步骤3 创建一个存储过程，通过调用存储函数的方法来获取表sch中的记录数和sch表中id的和
mysql>
mysql> drop procedure add_id;
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter //
mysql> create procedure add_id(out count int)
    -> begin
    -> declare itmp int;
    -> declare cur_id cursor for select id from sch;
    -> declare exit handler for not found close cur_id;
    ->
    -> select count_sch() into count;
    -> set @sum=0;
    -> open cur_id;
    -> repeat
    -> fetch cur_id into itmp;
    -> if itmp<10
    -> then set @sum= @sum + itmp;
    -> end if;
    -> until 0 end repeat;
    -> close cur_id;
    -> end;
    ->
    -> //
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> call add_id(@a);
    -> select @a,@sum //
Query OK, 0 rows affected (0.00 sec)

+------+------+
| @a   | @sum |
+------+------+
|    2 |    3 |
+------+------+
1 row in set (0.00 sec)

mysql>
mysql> delimiter ;