【MySQL進階】存儲過程和函數

4. 存儲過程和函數

4.1 存儲過程和函數概述

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

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

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

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

4.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将會執行該指令。

4.3 調用存儲過程

call procedure_name() ;

4.4 檢視存儲過程

-- 查詢db_name資料庫中的所有的存儲過程
select name from mysql.proc where db='db_name';

-- 查詢存儲過程的狀态資訊
show procedure status;

-- 查詢某個存儲過程的定義
show create procedure test.pro_test1;

4.5 删除存儲過程

DROP PROCEDURE  [IF EXISTS] sp_name ；

4.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 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 ;

4.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 ;

調用結果為 :

4.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_test8(168, @description);

select @description

小知識

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

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

4.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 ;

4.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 ;

4.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 ;

4.6.7 loop語句

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

[begin_label:] LOOP

  statement_list

END LOOP [end_label]

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

4.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 ;

4.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 光标：

FETCH cursor_name INTO var_name [, var_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 ;

4.7 存儲函數

文法結構:

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

delimiter $

create function count_city(countryId int)
returns int
begin
  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);

【MySQL進階】存儲過程和函數

4. 存儲過程和函數

繼續閱讀

neo4j之cypher使用文檔

GitHub連夜封殺！這份阿裡 10W 字内部 Java 字面試手冊到底有多強？

spark/scala關于【資源檔案】加載方法概述外部檔案加載方案測試資源檔案打包入jar包中小結

詳解STM32單片機的堆棧

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

mysql使用source指令導入.sql檔案

AOP程式設計_Android優雅權限架構(1)概念基礎，2021金三銀四前言正文大綱正文

登入plsql 報錯 the account is locked --使用者被鎖

Effective Java 8:通用程式設計

SequoiaDB巨杉資料庫C++驅動概述

OOM三種類型

工廠模式-三種類型

【遞歸】高效率求2的n次幂

win10本地scala和spark安裝安裝scala安裝spark

scala (3) Function 和 Method