1.1 存儲過程和函數概述

存儲過程和函數是 事先經過編譯并存儲在數據庫中的一段 SQL 語句的集合，調用存儲過程和函數可以簡化應用開發人員的很多工作，減少數據在數據庫和應用服務器之間的傳輸，對于提高數據處理的效率是有好處的。

存儲過程和函數的區別在于函數必須有返回值，而存儲過程沒有。

函數 ： 是一個有返回值的過程 ；

過程 ： 是一個沒有返回值的函數 ；

1.2 創建存儲過程

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

示例 ：

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

知識小貼士

DELIMITER

該關鍵字用來聲明SQL語句的分隔符 , 告訴 MySQL 解釋器，該段命令是否已經結束了，mysql是否可以執行了。默認情況下，delimiter是分號;。在命令行客戶端中，如果有一行命令以分號結束，那么回車后，mysql將會執行該命令。

1.3 調用存儲過程

call procedure_name() ;

1.4 查看存儲過程

-- 查詢db_name數據庫中的所有的存儲過程select name from mysql.proc where db='db_name';??-- 查詢存儲過程的狀態信息show procedure status;??-- 查詢某個存儲過程的定義show create procedure test.pro_test1 G;

1.5 刪除存儲過程

DROP PROCEDURE [IF EXISTS] sp_name ；

1.6 語法

存儲過程是可以編程的，意味著可以使用變量，表達式，控制結構 ， 來完成比較復雜的功能。

4.6.1 變量

• DECLARE通過 DECLARE 可以定義一個局部變量，該變量的作用范圍只能在 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，可以賦常量或者賦表達式，具體語法如下：

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 方式進行賦值操作 :

DELIMITER $?CREATE PROCEDURE pro_test5()BEGIN declare countnum int; select count(*) into countnum from city; select countnum;END$?DELIMITER ;

1.6.2 if條件判斷

語法結構 :

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

需求：

根據定義的身高變量，判定當前身高的所屬的身材類型 ? 180 及以上 ----------> 身材高挑? 170 - 180 ---------> 標準身材? 170 以下 ----------> 一般身材

示例 :

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 = '標準身材'; else ? ?set description = '一般身材'; end if; ? ?select description ;end$?delimiter ;

調用結果為 :

1.6.3 傳遞參數

語法格式 :

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

IN - 輸入

需求 :

根據定義的身高變量，判定當前身高的所屬的身材類型

示例 :

delimiter $?create procedure pro_test5(in height int)begin ? declare description varchar(50) default ''; if height >= 180 then ? ?set description='身材高挑'; elseif height >= 170 and height < 180 then ? ?set description='標準身材'; else ? ?set description='一般身材'; end if; ?select concat('身高 ', height , '對應的身材類型為:',description);end$?delimiter ;

OUT-輸出

需求 :

根據傳入的身高變量，獲取當前身高的所屬的身材類型 ?

示例:

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='標準身材'; else ? ?set description='一般身材'; end if;end$

調用:

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

小知識

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

@@global.sort_buffer_size : 這種在變量前加上 "@@" 符號, 叫做 系統變量

1.6.4 case結構

語法結構 :

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

需求:

給定一個月份, 然后計算出所在的季度

示例 :

delimiter $??create procedure pro_test9(month int)begin declare 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 ;

1.6.5 while循環

語法結構:

while search_condition do? statement_list end while;

需求:

計算從1加到n的值

示例 :

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

1.6.6 repeat結構

有條件的循環控制語句, 當滿足條件的時候退出循環 。while 是滿足條件才執行，repeat 是滿足條件就退出循環。

語法結構 :

REPEAT? statement_list? UNTIL search_condition?END REPEAT;

需求:

計算從1加到n的值

示例 :

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

1.6.7 loop語句

LOOP 實現簡單的循環，退出循環的條件需要使用其他的語句定義，通常可以使用 LEAVE 語句實現，具體語法如下：

[begin_label:] LOOP? statement_list?END LOOP [end_label]

如果不在 statement_list 中增加退出循環的語句，那么 LOOP 語句可以用來實現簡單的死循環。

1.6.8 leave語句

用來從標注的流程構造中退出，通常和 BEGIN ... END 或者循環一起使用。下面是一個使用 LOOP 和 LEAVE 的簡單例子 , 退出循環：

delimiter $?CREATE PROCEDURE pro_test11(n int)BEGIN declare total int default 0; ? ins: LOOP ? ? ? IF n <= 0 then ? ? leave ins; ? END IF; ? ? ? ?set total = total + n; ? ?set n = n - 1; END LOOP ins; ? ?select total;END$?delimiter ;

1.6.9 游標/光標

游標是用來存儲查詢結果集的數據類型 , 在存儲過程和函數中可以使用光標對結果集進行循環的處理。光標的使用包括光標的聲明、OPEN、FETCH 和 CLOSE，其語法分別如下。

聲明光標：

DECLARE cursor_name CURSOR FOR select_statement ;

OPEN 光標：

OPEN cursor_name ;

FETCH 光標：

FETCH cursor_name INTO var_name [, var_name] ...

CLOSE 光標：

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);?-- 查詢emp表中數據, 并逐行獲取進行展示create procedure pro_test11()begin declare 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$?

通過循環結構 , 獲取游標中的數據 :

DELIMITER $?create procedure pro_test12()begin DECLARE 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; ? repeat ? fetch emp_result into id , name , age , salary; ? ?select concat('id為',id, ', name 為' ,name , ', age為 ' ,age , ', 薪水為: ', salary); ? until has_data = 0 end repeat; ? close emp_result;end$?DELIMITER ;

1.7 存儲函數

語法結構:

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

案例 :

定義一個存儲過程, 請求滿足條件的總記錄數 ;

?delimiter $?create function count_city(countryId int)returns intbegin declare cnum int ; ? ?select count(*) into cnum from city where country_id = countryId; ? return cnum;end$?delimiter ;

調用:

select count_city(1);?select count_city(2);

總結

以上是生活随笔為你收集整理的bat 存储过程返回值_MySQL-存储过程和函数详述的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。