存儲(chǔ)過程和存儲(chǔ)函數(shù)

• 前言
• 存儲(chǔ)過程的相關(guān)操作
• • 創(chuàng)建
  • 調(diào)用
  • 查看
  • 刪除
  • 語法
  • • 變量的定義與賦值
    • if條件判斷
    • 參數(shù)
    • case結(jié)構(gòu)
    • while循環(huán)
    • repeat循環(huán)結(jié)構(gòu)
    • loop語句
    • leave語句
    • 游標(biāo)/光標(biāo)
• 存儲(chǔ)函數(shù)

前言

存儲(chǔ)過程和函數(shù)是 事先經(jīng)過編譯并存儲(chǔ)在數(shù)據(jù)庫中的一段 SQL 語句的集合，調(diào)用存儲(chǔ)過程和函數(shù)可以簡化應(yīng)用開 發(fā)人員的很多工作，減少數(shù)據(jù)在數(shù)據(jù)庫和應(yīng)用服務(wù)器之間的傳輸，對(duì)于提高數(shù)據(jù)處理的效率是有好處的。
Mysql是從5.0版本開始支持存儲(chǔ)過程和函數(shù)的。

存儲(chǔ)過程和函數(shù)的區(qū)別在于函數(shù)必須有返回值，而存儲(chǔ)過程沒有。

函數(shù) ： 是一個(gè)有返回值的過程 ；
過程 ： 是一個(gè)沒有返回值的函數(shù) ；
對(duì)于存儲(chǔ)過程或函數(shù)在進(jìn)行操作的時(shí)候，需要首先確認(rèn)用戶是否具有相應(yīng)的權(quán)限。

查看當(dāng)前root用戶的權(quán)限:

查看普通用戶的權(quán)限：

SHOW GRANTS FOR 普通用戶名;

和存儲(chǔ)過程有關(guān)的權(quán)限有三種：
ALTER ROUTINE 編輯或刪除存儲(chǔ)過程
CREATE ROUTINE 建立存儲(chǔ)過程
EXECUTE 運(yùn)行存儲(chǔ)過程

DELIMITER 來聲明SQL語句的分隔符 , 告訴 MySQL 解釋器，該段命令是否已經(jīng)結(jié)束了，mysql是否可以執(zhí)行了。
默認(rèn)情況下，delimiter是分號(hào);。在命令行客戶端中，如果有一行命令以分號(hào)結(jié)束，那么回車后，mysql將會(huì)執(zhí)行該命令。

存儲(chǔ)過程的相關(guān)操作

創(chuàng)建

CREATE PROCEDURE procedure_name ([proc_parameter[,...]]) begin -- SQL語句 end ;

示例：

delimiter $create procedure pro_test1() begin select 'Hello Mysql' ; end$ delimiter ;

調(diào)用

call procedure_name() ;

查看

-- 查詢db_name數(shù)據(jù)庫中的所有的存儲(chǔ)過程 select name from mysql.proc where db='db_name'; -- 查詢存儲(chǔ)過程的狀態(tài)信息 show procedure status; -- 查詢某個(gè)存儲(chǔ)過程的定義 show create procedure test.pro_test1 \G;

如：

刪除

DROP PROCEDURE [IF EXISTS] sp_name ；

語法

存儲(chǔ)過程是可以編程的，意味著可以使用變量，表達(dá)式，控制結(jié)構(gòu)等來完成比較復(fù)雜的功能。

變量的定義與賦值

DECLARE
通過 DECLARE 可以定義一個(gè)局部變量，該變量的作用范圍只能在 BEGIN…END 塊中

語法:DECLARE var_name[,...] type [DEFAULT value];

示例：

delimiter $create procedure pro_test2() begin declare num int default 5; select num+ 10; end$delimiter ;

SET
直接賦值使用 SET，可以賦常量或者賦表達(dá)式，具體語法如下：

SET var_name = expr [, var_name = expr] ...

示例：

DELIMITER $ CREATE PROCEDURE pro_test3() BEGIN DECLARE NAME VARCHAR(20); SET NAME = 'MYSQL'; SELECT NAME ; END$ DELIMITER ;

select…into
也可以通過select … into 方式進(jìn)行賦值操作 :
示例：

DELIMITER $CREATE PROCEDURE pro_test5() BEGIN declare countnum int; --將count(*)賦值給countnumselect count(*) into countnum from city；select countnum; END$DELIMITER ;

if條件判斷

語法結(jié)構(gòu)：

if search_condition then statement_list[elseif search_condition then statement_list] ... [else statement_list] end if;

示例:

delimiter $create procedure pro_test6() begin declare height int default 175; declare description varchar(50); if height >= 180 then set description = '身材高挑'; elseif height >= 170 and height < 180 then set description = '標(biāo)準(zhǔn)身材'; else set description = '一般身材'; end if; select description ; end$delimiter ;

參數(shù)

create procedure procedure_name([in/out/inout] 參數(shù)名 參數(shù)類型) ...IN : 該參數(shù)可以作為輸入，也就是需要調(diào)用方傳入值 , 默認(rèn) OUT: 該參數(shù)作為輸出，也就是該參數(shù)可以作為返回值 INOUT: 既可以作為輸入?yún)?shù)，也可以作為輸出參數(shù)

示例：

create procedure pro_test5(in height int , out description varchar(100)) begin if height >= 180 then set description='身材高挑'; elseif height >= 170 and height < 180 then set description='標(biāo)準(zhǔn)身材'; else set description='一般身材'; end if; end$

調(diào)用：

call pro_test5(168, @description)$select @description$

@description : 這種變量要在變量名稱前面加上“@”符號(hào)，叫做用戶會(huì)話變量，代表整個(gè)會(huì)話過程他都是有作用 的，這個(gè)類似于全局變量一樣。

@@global.sort_bu?er_size : 這種在變量前加上 “@@” 符號(hào), 叫做 系統(tǒng)變量

case結(jié)構(gòu)

方式一 : CASE case_valueWHEN when_value THEN statement_list[WHEN when_value THEN statement_list] ...[ELSE statement_list]END CASE;方式二 : CASEWHEN search_condition THEN statement_list[WHEN search_condition THEN statement_list] ...[ELSE statement_list]END CASE;

示例：給定一個(gè)月份計(jì)算出所在季度

delimiter $--方式二 create procedure pro_test9(month int) begindeclare result varchar(20);case when month >= 1 and month <=3 then set result = '第一季度';when month >= 4 and month <=6 then set result = '第二季度';when month >= 7 and month <=9 then set result = '第三季度';when month >= 10 and month <=12 then set result = '第四季度';end case;select concat('您輸入的月份為 :', month , ' , 該月份為 : ' , result) as content ;end$delimiter ;

while循環(huán)

語法結(jié)構(gòu)：

while search_condition dostatement_listend while;

示例：求1到n的累加和

delimiter $create procedure pro_test8(n int) begindeclare total int default 0;declare num int default 1;while num<=n doset total = total + num;set num = num + 1;end while;select total; end$delimiter ;

repeat循環(huán)結(jié)構(gòu)

repeat是有條件的循環(huán)控制語句, 當(dāng)滿足條件的時(shí)候退出循環(huán)； while 是滿足條件才執(zhí)行，repeat 是滿足條件就退出循環(huán)。
語法結(jié)構(gòu)：

REPEATstatement_listUNTIL search_conditionEND REPEAT;

示例：計(jì)算從1到n的累加值

delimiter $create procedure pro_test10(n int) begindeclare total int default 0;repeat set total = total + n;set n = n - 1;until n=0 end repeat;select total ;end$delimiter ;

loop語句

LOOP 實(shí)現(xiàn)簡單的循環(huán)，退出循環(huán)的條件需要使用其他的語句定義，通?？梢允褂?LEAVE 語句實(shí)現(xiàn)，具體語法如下：

[begin_label:] LOOPstatement_listEND LOOP [end_label]

如果不在 statement_list 中增加退出循環(huán)的語句，那么 LOOP 語句可以用來實(shí)現(xiàn)簡單的死循環(huán)。
begin_label:是定義的loop循環(huán)的標(biāo)簽/名稱。

leave語句

用來從標(biāo)注的流程構(gòu)造中退出，通常和 BEGIN … END 或者循環(huán)一起使用。下面是一個(gè)使用 LOOP 和 LEAVE 的簡單例子 , 退出循環(huán)：

delimiter $CREATE PROCEDURE pro_test11(n int) BEGINdeclare total int default 0;ins: LOOPIF n <= 0 thenleave ins;END IF;set total = total + n;set n = n - 1;END LOOP ins;select total; END$delimiter ;

游標(biāo)/光標(biāo)

游標(biāo)是用來存儲(chǔ)查詢結(jié)果集的數(shù)據(jù)類型 , 在存儲(chǔ)過程和函數(shù)中可以使用光標(biāo)對(duì)結(jié)果集進(jìn)行循環(huán)的處理。光標(biāo)的使用包括光標(biāo)的聲明、OPEN、FETCH 和 CLOSE，其語法分別如下。

聲明光標(biāo)： DECLARE cursor_name CURSOR FOR select_statement ;OPEN 光標(biāo)： OPEN cursor_name ;FETCH 光標(biāo)：(抓取數(shù)據(jù)賦值給某變量) FETCH cursor_name INTO var_name [, var_name] ...CLOSE 光標(biāo)： CLOSE cursor_name ;

示例：
初始化腳本：

create table emp(id int(11) not null auto_increment ,name varchar(50) not null comment '姓名',age int(11) comment '年齡',salary int(11) comment '薪水',primary key(`id`) )engine=innodb default charset=utf8 ;insert into emp(id,name,age,salary) values(null,'金毛獅王',55,3800),(null,'白眉鷹王',60,4000),(null,'青翼蝠王',38,2800),(null,'紫衫龍王',42,1800);

創(chuàng)建存儲(chǔ)過程：（該存儲(chǔ)過程是通過游標(biāo)保存查詢的結(jié)果集）

-- 查詢emp表中數(shù)據(jù), 并逐行獲取進(jìn)行展示 create procedure pro_test11() begindeclare e_id int(11);declare e_name varchar(50);declare e_age int(11);declare e_salary int(11);declare emp_result cursor for select * from emp;open emp_result;fetch emp_result into e_id,e_name,e_age,e_salary;select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪資為: ',e_salary);fetch emp_result into e_id,e_name,e_age,e_salary;select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪資為: ',e_salary);fetch emp_result into e_id,e_name,e_age,e_salary;select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪資為: ',e_salary);fetch emp_result into e_id,e_name,e_age,e_salary;select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪資為: ',e_salary);fetch emp_result into e_id,e_name,e_age,e_salary;select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪資為: ',e_salary);close emp_result; end$

再創(chuàng)建一個(gè)存儲(chǔ)過程，該存儲(chǔ)過程是通過循環(huán)獲取游標(biāo)中的數(shù)據(jù)：

DELIMITER $create procedure pro_test12() beginDECLARE id int(11);DECLARE name varchar(50);DECLARE age int(11);DECLARE salary int(11);DECLARE has_data int default 1;DECLARE emp_result CURSOR FOR select * from emp;DECLARE EXIT HANDLER FOR NOT FOUND set has_data = 0;open emp_result;repeatfetch emp_result into id , name , age , salary;select concat('id為',id, ', name 為' ,name , ', age為 ' ,age , ', 薪水為: ', salary);until has_data = 0end repeat;close emp_result; end$DELIMITER ;

我們前面給數(shù)據(jù)庫中插入了4條數(shù)據(jù)，但是我們編寫的存儲(chǔ)過程中抓取了5個(gè)數(shù)據(jù)，所以在調(diào)用存儲(chǔ)過程的時(shí)候會(huì)將4條數(shù)據(jù)抓取完后報(bào)錯(cuò)。

調(diào)用:

我們一般在使用游標(biāo)抓取數(shù)據(jù)的時(shí)候可能不知道數(shù)據(jù)的多少或者數(shù)據(jù)量過多沒法一條一條進(jìn)行抓取，因此我們可以采用循環(huán)來實(shí)現(xiàn)抓取全部數(shù)據(jù)。

調(diào)用：

存儲(chǔ)函數(shù)

語法結(jié)構(gòu)：

CREATE FUNCTION function_name([param type ... ]) RETURNS type BEGIN... END;

示例：

--創(chuàng)建數(shù)據(jù)表 CREATE TABLE `city` (`city_id` int(11) NOT NULL AUTO_INCREMENT,`city_name` varchar(50) NOT NULL,`country_id` int(11) NOT NULL,PRIMARY KEY (`city_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;CREATE TABLE `country` (`country_id` int(11) NOT NULL AUTO_INCREMENT,`country_name` varchar(100) NOT NULL,PRIMARY KEY (`country_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;insert into `city` (`city_id`, `city_name`, `country_id`) values(1,'西安',1); insert into `city` (`city_id`, `city_name`, `country_id`) values(2,'NewYork',2); insert into `city` (`city_id`, `city_name`, `country_id`) values(3,'北京',1); insert into `city` (`city_id`, `city_name`, `country_id`) values(4,'上海',1);insert into `country` (`country_id`, `country_name`) values(1,'China'); insert into `country` (`country_id`, `country_name`) values(2,'America'); insert into `country` (`country_id`, `country_name`) values(3,'Japan'); insert into `country` (`country_id`, `country_name`) values(4,'UK');delimiter $create function count_city(countryId int) returns int begindeclare cnum int ;select count(*) into cnum from city where country_id = countryId;return cnum; end$delimiter ;

調(diào)用：

select count_city(1);

如果在創(chuàng)建存儲(chǔ)函數(shù)的時(shí)候遇到了報(bào)錯(cuò)請(qǐng)參考我的這篇文章
感謝🤭

總結(jié)

以上是生活随笔為你收集整理的Mysql存储过程和存储函数的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。