MySQL 存储过程和函数

目录

一、存储过程和函数概述

二、创建存储过程和函数

1、创建存储过程

2、创建存储函数

三、查看/调用储存过程和函数

1、查看储存过程/函数

2、调用储存过程/函数

四、修改/删除存储过程和函数

1、修改存储过程和函数

2、删除存储过程和函数

五、练习 

---

一、存储过程和函数概述

存储过程就是一条或多条SQL语句的集合，可视为批文件，但是七作用不仅限于批处理。创建存储过程和函数的语句分别是create proceddure 和 create function。使用CALL语句来调用存储过程，只能输出变量返回值。函数可以从语句外调用（即通过引用函数名），也能返回标量值。存储过程也可以调用其他存储过程。

二、创建存储过程和函数

1、创建存储过程

语法

create procedure 存储过程名称 ([定义存储过程参数列表]) 
begin
body
end 结束符

储存过程参数列表：

[in | out | inout]  参数名 参数类型

in---输入参数

out---输出参数

inout---输入输出参数

例如

mysql> delimiter //
mysql> create procedure avg_age()
    -> begin
    -> select avg(age) as ave_age
    -> from emp3;
    -> end //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

delimiter //语句的作用是将MySQL的结束符设置为//。MySQL默认的结束符是“;” 。避免冲突。

2、创建存储函数

语法

create function 存储函数名 ([定义存储过程参数列表])
returns 类型
begin
body;
end 结束符

例如

mysql> delimiter //
mysql> create function count_num()
    -> returns int
    -> return (select count(*) from emp3);
    -> //
Query OK, 0 rows affected (0.03 sec)

mysql> delimiter ;

三、查看/调用储存过程和函数

1、查看储存过程/函数

基本语法1

 show [procedure | function] status [like '存储过程名|函数名' \G

基本语法2

 show create [procedure | function] 存储过程名|函数名 \G

 基本语法3

select * from information_schema.Routines where ROUTINE_NAME='存储过程名 | 函数名' \G

例如

基本语法1

mysql> show procedure status like 'avg_age' \G
*************************** 1. row ***************************
                  Db: mydb3
                Name: avg_age
                Type: PROCEDURE
             Definer: root@localhost
            Modified: 2023-08-22 16:22:41
             Created: 2023-08-22 16:22:41
       Security_type: DEFINER
             Comment: 
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)

基本语法2

mysql> show create procedure avg_age \G
*************************** 1. row ***************************
           Procedure: avg_age
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `avg_age`()
begin
select avg(age) as ave_age
from emp3;
end
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)

基本语法3

mysql> select * from information_schema.Routines where ROUTINE_NAME='avg_age' \G
*************************** 1. row ***************************
           SPECIFIC_NAME: avg_age
         ROUTINE_CATALOG: def
          ROUTINE_SCHEMA: mydb3
            ROUTINE_NAME: avg_age
            ROUTINE_TYPE: PROCEDURE
               DATA_TYPE: 
CHARACTER_MAXIMUM_LENGTH: NULL
  CHARACTER_OCTET_LENGTH: NULL
       NUMERIC_PRECISION: NULL
           NUMERIC_SCALE: NULL
      DATETIME_PRECISION: NULL
      CHARACTER_SET_NAME: NULL
          COLLATION_NAME: NULL
          DTD_IDENTIFIER: NULL
            ROUTINE_BODY: SQL
      ROUTINE_DEFINITION: begin
select avg(age) as ave_age
from emp3;
end
           EXTERNAL_NAME: NULL
       EXTERNAL_LANGUAGE: NULL
         PARAMETER_STYLE: SQL
        IS_DETERMINISTIC: NO
         SQL_DATA_ACCESS: CONTAINS SQL
                SQL_PATH: NULL
           SECURITY_TYPE: DEFINER
                 CREATED: 2023-08-22 16:22:41
            LAST_ALTERED: 2023-08-22 16:22:41
                SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
         ROUTINE_COMMENT: 
                 DEFINER: root@localhost
    CHARACTER_SET_CLIENT: utf8
    COLLATION_CONNECTION: utf8_general_ci
      DATABASE_COLLATION: latin1_swedish_ci
1 row in set (0.01 sec)

 2、调用储存过程/函数

基本语法

call 存储过程名 (参数);
select 函数名([参数]);

例如

mysql> call avg_age;
+---------+
| ave_age |
+---------+
| 43.9000 |
+---------+
1 row in set (0.00 sec)

四、修改/删除存储过程和函数

1、修改存储过程和函数

基本语法

alter procedure | function  存储过程名 | 函数名 [参数]

例如 

2、删除存储过程和函数

基本语法

drop [procedure | function] [if exists] 存储过程名|函数名

五、练习 

创建表sch并插入数据

字段名	数据类型	主键	外键	非空	唯一	自增
id	INT	是	否	是	是	否
name	VARCHAR(50)	否	否	是	否	否
glass	VARCHAR(50)	否	否	是	否	否

sch 表内容

id	name	glass
1	小明	Glass1
2	小军	Glass2

create table sch ( 
id int primary key, 
name varchar(50) not null, 
glass varchar(50) not null 
)charset=utf8;

insert into sch values
(1,'小明','Glass1'),
(2,'小军','Glass2')
;

mysql> select * from sch;
+----+--------+--------+
| id | name   | glass  |
+----+--------+--------+
|  1 | 小明   | Glass1 |
|  2 | 小军   | Glass2 |
+----+--------+--------+
2 rows in set (0.00 sec)

1、创建一个可以统计表格内记录条数的存储函数 ，函数名为count_sch();

mysql> delimiter $$
mysql> create function count_sch()
    -> returns int 
    -> begin
    -> return (select count(id) from sch)
    -> ;
    -> end$$
mysql> delimiter ;

mysql> select count_sch();
+-------------+
| count_sch() |
+-------------+
|           2 |
+-------------+
1 row in set (0.00 sec)

2、创建一个存储过程record，有1个参数，输入id，功能是输入id输出对应完整信息。

mysql> delimiter $$
mysql> create procedure record (in id_1 int) 
begin select * from sch where id=id_1; 
end$$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call record(1);
+----+---------+--------+
| id | name    | glass  |
+----+---------+--------+
|  1 | 小明    | glass1 |
+----+---------+--------+
1 row in set (0.00 sec)