儲存過程
●介紹: 儲存過程是事先經過編譯並儲存在資料庫中的一段SQL語句的集合,呼叫儲存過程可以簡化應用開發人員的很多工作,減少資料在資料庫和應用伺服器之間的傳輸,對於提高資料處理的效率是有好處的。
儲存過程思想上很簡單,就是資料庫SQL語言層面的程式碼封裝與複用。
●特點 :封裝,複用 可以接收引數,也可以返回資料,減少網路互動,效率提升
●建立:
create procedure 儲存過程名稱 ([引數列表])
begin
---SQL語句
end;
●呼叫
call 名稱([引數])
●檢視
select * from information_schema.routines(檔案位置) where routines_schema='XXX';--查詢指定資料庫的儲存過程及狀態資訊
show create procedure 儲存過程名稱; --查詢某個儲存過程的定義
●刪除
drop procedure [if exists] 儲存過程名稱;
注意:在命令列中,執行建立儲存過程的SQL時,需要透過關鍵字 delimiter 指定SQL 語句的結束語
●變數
系統變數是MySQL伺服器提供,不是使用者定義的,屬於伺服器層面。分為全域性變數(global)、會話變數(session)。
◇檢視系統變數
show [session|global] variables; ----檢視所有系統變數
show [session|global] variables like '';-----可以透過like ,模糊匹配方式查詢變數
select @@[session|global] 系統變數名; ----檢視指定變數的值
◇設定系統變數
set [session|global] 系統變數名=值;
set @@[session|global] 系統變數名=值;
注意:
如果沒有指定session/global,預設是session,會話變數。
mysql服務重新啟動之後,所設定的全域性變數會失效,要想不失效,可以在/etc/my.cnf中配置
使用者定義變數是使用者根據需要自己定義的變數,使用者變數不用提前宣告,在用的時候直接用“變數名”使用就可以。其作用域為當前連線。
◇賦值
set @var_name = expr[,@var_name = expr...];
set @var_name := expr[,@var_name := expr...];
select @var_name := expr[,@var_name := expr...];
select 欄位名 into @var_name from 表名;
◇使用
select @var_name;
注意:
使用者定義的變數無需對其進行宣告或初始化,只不過獲取到的值為null
區域性變數
是根據需要定義在區域性生效的變數,訪問之前,需要declare宣告。可用作儲存過程內的區域性變數和輸入引數,區域性變數的範圍是在其內宣告的begin.....end塊
宣告:declare 變數名 變數型別[default...]; 變數型別可以是:int、bigint(整數資料型別)、char、varchar、date、time等
賦值:
set 變數名=值;
set 變數名:=值;
select 欄位名 into 變數名 from 表名...;
例1:
drop procedure if exists p1;
create procedure p1()
BEGIN
declare stu_Id int default 0;
select count(*) into stu_Id from student;
select stu_Id;
end;
call p1();
●if
語法:
if 條件1 then
....
else if 條件2 then
....
else
....
end if;
●引數
型別:in、out 、inout
in:該類引數作為輸入,也就是需要呼叫時傳入值
out:該類引數作為輸出,也就是該引數可以作為返回值
inout:既可以作為輸入引數,也可以作為輸出引數
用法:
create procedure 儲存過程名稱([in/out/inout 引數名 引數型別])
begin
--SQL語句
end;
●case
語法一:case 表示式
when 表示式值為1 then 執行SQL語句
[when when_value2 then statement_list2]...
[else statement_list]
end case;
語法二:case
when 表示式成立 then 執行SQL語句
[when search_condition1 then statement_list1]
[else statement_list]
end case;
●while
while迴圈是有條件的迴圈控制語句。滿足條件後,再執行迴圈體中的SQL語句。具體語法為:
#先判定條件,如果條件為true,則執行邏輯,否則,不執行邏輯
while 條件 do
---SQL邏輯
end while;
●repeat
repeat是有條件的迴圈控制語句,當滿足條件的時候退出迴圈,具體語法為:
#先執行一次邏輯,然後判斷邏輯是否滿足,如果滿足,則退出,如果不滿足,則繼續下一次迴圈
repeat
SQL邏輯
until 條件
end repeat
●loop
loop實現簡單的迴圈,如果不在SQL邏輯中增加退出迴圈的條件,可以用其來實現簡單的死迴圈。loop可以配合一下兩個語句使用:
·leave:配合迴圈使用,退出迴圈
·iterate:必須用在迴圈中,作用是跳過當前迴圈剩下的語句,直接進入下一次迴圈
[(標記)begin_label:]loop
SQL邏輯...
end loop [end_label];
leave label;---退出指定標記的迴圈體
iterate label;直接進入下一次迴圈
●遊標
是用來儲存查詢結果集的資料型別,在儲存過程和函式中可以使用遊標對結果集進行迴圈的處理。遊標的使用包括遊標的宣告、open、fetch和close 其語法如下:
宣告遊標:declare 遊標名稱 cursor for 查詢語句;
開啟遊標:open 遊標名稱;
獲取遊標記錄:fetch 遊標名稱 into 變數[,變數];
關閉遊標:close 遊標名稱;