目錄

一、存儲過程和函數(shù)概述

二、創(chuàng)建存儲過程和函數(shù)

1、創(chuàng)建存儲過程

2、創(chuàng)建存儲函數(shù)

三、查看/調(diào)用儲存過程和函數(shù)

1、查看儲存過程/函數(shù)

2、調(diào)用儲存過程/函數(shù)

四、修改/刪除存儲過程和函數(shù)

1、修改存儲過程和函數(shù)

2、刪除存儲過程和函數(shù)

五、練習?

---

一、存儲過程和函數(shù)概述

存儲過程就是一條或多條SQL語句的集合，可視為批文件，但是七作用不僅限于批處理。創(chuàng)建存儲過程和函數(shù)的語句分別是create proceddure 和 create function。使用CALL語句來調(diào)用存儲過程，只能輸出變量返回值。函數(shù)可以從語句外調(diào)用（即通過引用函數(shù)名），也能返回標量值。存儲過程也可以調(diào)用其他存儲過程。

二、創(chuàng)建存儲過程和函數(shù)

1、創(chuàng)建存儲過程

語法

create procedure 存儲過程名稱 ([定義存儲過程參數(shù)列表]) 
begin
body
end 結(jié)束符

儲存過程參數(shù)列表：

[in | out | inout]? 參數(shù)名 參數(shù)類型

in---輸入?yún)?shù)

out---輸出參數(shù)

inout---輸入輸出參數(shù)

例如

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的結(jié)束符設置為//。MySQL默認的結(jié)束符是“;”?。避免沖突。

2、創(chuàng)建存儲函數(shù)

語法

create function 存儲函數(shù)名 ([定義存儲過程參數(shù)列表])
returns 類型
begin
body;
end 結(jié)束符

例如

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

三、查看/調(diào)用儲存過程和函數(shù)

1、查看儲存過程/函數(shù)

基本語法1

 show [procedure | function] status [like '存儲過程名|函數(shù)名' \G

基本語法2

 show create [procedure | function] 存儲過程名|函數(shù)名 \G

?基本語法3

select * from information_schema.Routines where ROUTINE_NAME='存儲過程名 | 函數(shù)名' \G

例如

基本語法1

mysql> show procedure status like 'avg_age' \G
*************************** 1. row ***************************Db: mydb3Name: avg_ageType: PROCEDUREDefiner: root@localhostModified: 2023-08-22 16:22:41Created: 2023-08-22 16:22:41Security_type: DEFINERComment: 
character_set_client: utf8
collation_connection: utf8_general_ciDatabase Collation: latin1_swedish_ci
1 row in set (0.00 sec)

基本語法2

mysql> show create procedure avg_age \G
*************************** 1. row ***************************Procedure: avg_agesql_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_SUBSTITUTIONCreate 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_ciDatabase 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_ageROUTINE_CATALOG: defROUTINE_SCHEMA: mydb3ROUTINE_NAME: avg_ageROUTINE_TYPE: PROCEDUREDATA_TYPE: 
CHARACTER_MAXIMUM_LENGTH: NULLCHARACTER_OCTET_LENGTH: NULLNUMERIC_PRECISION: NULLNUMERIC_SCALE: NULLDATETIME_PRECISION: NULLCHARACTER_SET_NAME: NULLCOLLATION_NAME: NULLDTD_IDENTIFIER: NULLROUTINE_BODY: SQLROUTINE_DEFINITION: begin
select avg(age) as ave_age
from emp3;
endEXTERNAL_NAME: NULLEXTERNAL_LANGUAGE: NULLPARAMETER_STYLE: SQLIS_DETERMINISTIC: NOSQL_DATA_ACCESS: CONTAINS SQLSQL_PATH: NULLSECURITY_TYPE: DEFINERCREATED: 2023-08-22 16:22:41LAST_ALTERED: 2023-08-22 16:22:41SQL_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_SUBSTITUTIONROUTINE_COMMENT: DEFINER: root@localhostCHARACTER_SET_CLIENT: utf8COLLATION_CONNECTION: utf8_general_ciDATABASE_COLLATION: latin1_swedish_ci
1 row in set (0.01 sec)

?2、調(diào)用儲存過程/函數(shù)

基本語法

call 存儲過程名 (參數(shù));
select 函數(shù)名([參數(shù)]);

例如

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

四、修改/刪除存儲過程和函數(shù)

1、修改存儲過程和函數(shù)

基本語法

alter procedure | function  存儲過程名 | 函數(shù)名 [參數(shù)]

例如?

2、刪除存儲過程和函數(shù)

基本語法

drop [procedure | function] [if exists] 存儲過程名|函數(shù)名

五、練習?

創(chuàng)建表sch并插入數(shù)據(jù)

字段名	數(shù)據(jù)類型	主鍵	外鍵	非空	唯一	自增
id	INT	是	否	是	是	否
name	VARCHAR(50)	否	否	是	否	否
glass	VARCHAR(50)	否	否	是	否	否

sch 表內(nèi)容

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、創(chuàng)建一個可以統(tǒng)計表格內(nèi)記錄條數(shù)的存儲函數(shù) ，函數(shù)名為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、創(chuàng)建一個存儲過程record，有1個參數(shù)，輸入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)