文章詳情頁

SQL中的游標、異常處理、存儲函數及總結(最新推薦)

瀏覽：128日期：2023-03-06 14:25:45

一.游標

游標(cursor)是用來存儲查詢結果集的數據類型,在存儲過程和函數中可以使用游標對結果集進行循環的處理。游標的使用包括游標的聲明、OPEN、FETCH和 CLOSE.

格式

操作

演示

use test_procedure ;-- 需求:輸入一個部門名，查詢該部門員工的編號、名字、薪資，將查詢的結果集添加游標delimiter $$create procedure proc21(in in_name varchar(50))begin	-- 定義局部變量	declare var_empno int;	declare var_ename varchar(50);	declare var_sal decimal(7,2);	-- 聲明游標	declare my_cursor cursor for	select empno ,ename ,sal  from emp e,dept d where d.dname =in_name ;	-- 打開游標	open my_cursor;	-- 通過游標獲得值	fetch my_cursor into var_empno,var_ename,var_sal;	select var_empno,var_ename,var_sal;	-- 關閉游標	close my_cursor;end $$delimiter ; call proc21("銷售部");

運行輸出的結果是

但是

我們發現符合要求的有多個值，這是因為游標是一條條往下執行的所以我們可以用一個循環

use test_procedure ;-- 需求:輸入一個部門名，查詢該部門員工的編號、名字、薪資，將查詢的結果集添加游標delimiter $$create procedure proc21(in in_name varchar(50))begin	-- 定義局部變量	declare var_empno int;	declare var_ename varchar(50);	declare var_sal decimal(7,2);	-- 聲明游標	declare my_cursor cursor for	select empno ,ename ,sal  from emp e,dept d where d.dname =in_name ;	-- 打開游標	open my_cursor;	-- 通過游標獲得值	label:loop	fetch my_cursor into var_empno,var_ename,var_sal;	select var_empno,var_ename,var_sal;	end loop label;	-- 關閉游標	close my_cursor;end $$delimiter ; call proc21("銷售部");

結果會報錯

No data - zero rows fetched, selected, or processed 這句話意思是沒有數據零行讀取選擇或處理，就是說循環到最后沒值了，所以我們需要找到一個條件并及時退出循環，這就需要異常處理—handler句柄。

二.異常處理—handler句柄

格式

注意

在語法中，變量聲明、游標聲明、handler聲明是必須按照先后順序書寫的，否則創建存儲過程出錯。

--定義句柄:定義異常的處理方式

1:異常處理完之后程序該怎么執行
continue :繼續執行剩余代碼

exit :直接終止程序
undo:不支持
2:觸發條件

條件碼:1329

條件名:
SQLWARNING

NOT FOUND

SQLEXCEPTION

演示

-- 用條件名use test_procedure ;-- 需求:輸入一個部門名，查詢該部門員工的編號、名字、薪資，將查詢的結果集添加游標delimiter $$create procedure proc22(in in_name varchar(50))begin	-- 定義局部變量	declare var_empno int;	declare var_ename varchar(50);	declare var_sal decimal(7,2); 	-- 定義標記值	declare flag int default 1;	-- 聲明游標	declare my_cursor cursor for	select empno ,ename ,sal  from emp e,dept d where d.dname =in_name ;	-- 定義句柄，當數據未發現時將標記位設置為0	declare continue handler for  not found set flag=0; 	-- 打開游標	open my_cursor;	-- 通過游標獲得值	label:loop	fetch my_cursor into var_empno,var_ename,var_sal;	if flag=1 then		select var_empno,var_ename,var_sal;	else		leave label;	end if;	end loop label;	-- 關閉游標	close my_cursor;end $$delimiter ; call proc22("銷售部");   -- 用條件碼use test_procedure ;-- 需求:輸入一個部門名，查詢該部門員工的編號、名字、薪資，將查詢的結果集添加游標delimiter $$create procedure proc23(in in_name varchar(50))begin	-- 定義局部變量	declare var_empno int;	declare var_ename varchar(50);	declare var_sal decimal(7,2); 	-- 定義標記值	declare flag int default 1;	-- 聲明游標	declare my_cursor cursor for	select empno ,ename ,sal  from emp e,dept d where d.dname =in_name ;	-- 定義句柄，當數據未發現時將標記位設置為0	declare continue handler for  1329 set flag=0; 	-- 打開游標	open my_cursor;	-- 通過游標獲得值	label:loop	fetch my_cursor into var_empno,var_ename,var_sal;	if flag=1 then		select var_empno,var_ename,var_sal;	else		leave label;	end if;	end loop label;	-- 關閉游標	close my_cursor;end $$delimiter ; call proc23("銷售部");

兩個的運行結果是一樣的

三.存儲函數

格式

在MySQL中，創建存儲函數使用create function關鍵字，其基本形式如下:

參數說明

(1) func_name :存儲函數的名稱。
(2) param_name type:可選項，指定存儲函數的參數。type參數用于指定存儲函數的參數類型，該類型可以是MySQL數據庫中所有支持的類型。
(3)returns type:指定返回值的類型。
(4)characteristic:可選項，指定存儲函數的特性。
(5)routine_body: SQL代碼內容。

演示

drop function if exists myfunc1_emp;delimiter $$create function myfunc1_emp() returns intbegin	declare cnt int default 0;	select count(*) into cnt from emp;	return cnt;end $$delimiter ;

運行結果可以會報錯，像這樣

意思是說“此函數在其聲明中沒有確定性、無 SQL 或讀取 SQL 數據，并且啟用了二進制日志記錄（您*可能*希望使用不太安全的 log_bin_trust_function_creators 變量）”

所以我們設置允許創建函數權限信任即可

-- 允許創建函數權限信任
set global log_bin_trust_function_creators=true ;

-- 允許創建函數權限信任set global log_bin_trust_function_creators=true ; drop function if exists myfunc1_emp;delimiter $$create function myfunc1_emp() returns intbegin	declare cnt int default 0;	select count(*) into cnt from emp;	return cnt;end $$delimiter ; -- 調用存儲函數select myfunc1_emp();