目錄
一、存儲(chǔ)過(guò)程和函數(shù)概述
二、創(chuàng)建存儲(chǔ)過(guò)程和函數(shù)
1、創(chuàng)建存儲(chǔ)過(guò)程
2、創(chuàng)建存儲(chǔ)函數(shù)
三、查看/調(diào)用儲(chǔ)存過(guò)程和函數(shù)
1、查看儲(chǔ)存過(guò)程/函數(shù)
2、調(diào)用儲(chǔ)存過(guò)程/函數(shù)
四、修改/刪除存儲(chǔ)過(guò)程和函數(shù)
1、修改存儲(chǔ)過(guò)程和函數(shù)
2、刪除存儲(chǔ)過(guò)程和函數(shù)
五、練習(xí)?
一、存儲(chǔ)過(guò)程和函數(shù)概述
存儲(chǔ)過(guò)程就是一條或多條SQL語(yǔ)句的集合，可視為批文件，但是七作用不僅限于批處理。創(chuàng)建存儲(chǔ)過(guò)程和函數(shù)的語(yǔ)句分別是create proceddure 和 create function。使用CALL語(yǔ)句來(lái)調(diào)用存儲(chǔ)過(guò)程，只能輸出變量返回值。函數(shù)可以從語(yǔ)句外調(diào)用（即通過(guò)引用函數(shù)名），也能返回標(biāo)量值。存儲(chǔ)過(guò)程也可以調(diào)用其他存儲(chǔ)過(guò)程。
二、創(chuàng)建存儲(chǔ)過(guò)程和函數(shù)
1、創(chuàng)建存儲(chǔ)過(guò)程
語(yǔ)法
create procedure 存儲(chǔ)過(guò)程名稱(chēng) ([定義存儲(chǔ)過(guò)程參數(shù)列表])
begin
body
end 結(jié)束符儲(chǔ)存過(guò)程參數(shù)列表：
[in | out | inout]? 參數(shù)名 參數(shù)類(lèi)型
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 //語(yǔ)句的作用是將MySQL的結(jié)束符設(shè)置為//。MySQL默認(rèn)的結(jié)束符是“;”?。避免沖突。
2、創(chuàng)建存儲(chǔ)函數(shù)
語(yǔ)法
create function 存儲(chǔ)函數(shù)名 ([定義存儲(chǔ)過(guò)程參數(shù)列表])
returns 類(lèi)型
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)用儲(chǔ)存過(guò)程和函數(shù)
1、查看儲(chǔ)存過(guò)程/函數(shù)
基本語(yǔ)法1
show [procedure | function] status [like '存儲(chǔ)過(guò)程名|函數(shù)名' \G
基本語(yǔ)法2
show create [procedure | function] 存儲(chǔ)過(guò)程名|函數(shù)名 \G?基本語(yǔ)法3
select * from information_schema.Routines where ROUTINE_NAME='存儲(chǔ)過(guò)程名 | 函數(shù)名' \G例如
基本語(yǔ)法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)
基本語(yǔ)法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)
基本語(yǔ)法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、調(diào)用儲(chǔ)存過(guò)程/函數(shù)
基本語(yǔ)法
call 存儲(chǔ)過(guò)程名 (參數(shù));
select 函數(shù)名([參數(shù)]);例如
mysql> call avg_age;
+---------+
| ave_age |
+---------+
| 43.9000 |
+---------+
1 row in set (0.00 sec)
四、修改/刪除存儲(chǔ)過(guò)程和函數(shù)
1、修改存儲(chǔ)過(guò)程和函數(shù)
基本語(yǔ)法
alter procedure | function 存儲(chǔ)過(guò)程名 | 函數(shù)名 [參數(shù)]例如?
2、刪除存儲(chǔ)過(guò)程和函數(shù)
基本語(yǔ)法
drop [procedure | function] [if exists] 存儲(chǔ)過(guò)程名|函數(shù)名五、練習(xí)?
創(chuàng)建表sch并插入數(shù)據(jù)
| 字段名 |
數(shù)據(jù)類(lèi)型 |
主鍵 |
外鍵 |
非空 |
唯一 |
自增 |
| 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)建一個(gè)可以統(tǒng)計(jì)表格內(nèi)記錄條數(shù)的存儲(chǔ)函數(shù) ，函數(shù)名為count_sch();文章來(lái)源：http://www.zghlxwxcb.cn/news/detail-668324.html
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)建一個(gè)存儲(chǔ)過(guò)程record，有1個(gè)參數(shù)，輸入id，功能是輸入id輸出對(duì)應(yīng)完整信息。文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-668324.html
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)到了這里，關(guān)于MySQL 存儲(chǔ)過(guò)程和函數(shù)的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
