MySQL 5.6存盤程序和函式語法的簡單介紹-有解無憂

MySQL支持存盤的例程（存盤程序和存盤函式）

存盤的例程是一組可以存盤在服務器中的SQL陳述句，完成此操作后，客戶無需繼續重新發出各個陳述句，而是可以參考存盤的例程，

首先先進行存盤的例程語法學習：

使用create procedure和create function陳述句創建存盤的例程，

使用call陳述句呼叫程序，并且只能使用輸出變數傳回值，

可以像其他任何函式一樣從陳述句內部呼叫一個函式（即，通過呼叫函式名稱），并且可以回傳標量值，

存盤例程的主體可以使用復合陳述句

可以使用drop procedure和drop function陳述句洗掉存盤的例程

可以使用alter procedure和alter function陳述句對其進行更改

簡單演示：

-- 宣告結束符，因為mysql默認使用“；”作為結束符，而在存盤程序中，會使用“；”作為一段陳述句的結束，導致“；”使用沖突
delimiter $$
CREATE PROCEDURE hello_procedure()
BEGIN 
  SELECT 'hello procedure';
END $$

CALL hello_procedure()

一、變數和賦值

對于存盤程序中的變數，可以類比java中的區域變數和成員變數的宣告和使用；

1、區域變數：

用戶自定義，在begin/end塊中有效

語法：

宣告變數： declare var_name type [default 'unkown'];

舉例：declare nickname varchar(32);

set 賦值

-- 宣告結束符，因為mysql默認使用“；”作為結束符，而在存盤程序中，會使用“；”作為一段陳述句的結束，導致“；”使用沖突
delimiter $$
create PROCEDURE sp_var01()
BEGIN
 -- 區域變數（需要宣告） 字符型別如果為char或者varchar，一定要表明單位長度，int可以不寫
 DECLARE nickname VARCHAR(32) DEFAULT 'SF';

 select nickname;

 -- set賦值
 set nickname := 'UG';

 select nickname;

END $$

-- 使用call呼叫該函式
call  sp_var01()$$

-- 使用drop洗掉該函式，若想要修改該存盤函式，mysql只能drop之后重新create
drop PROCEDURE sp_var01;

into 賦值

-- 首先創建一張表
DROP TABLE IF EXISTS `departments`;
CREATE TABLE `departments` (
  `id` int(11) DEFAULT NULL,
  `dep_name` varchar(255) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of departments
-- ----------------------------
INSERT INTO `departments` VALUES ('1', '開發');
INSERT INTO `departments` VALUES ('2', '測驗');
INSERT INTO `departments` VALUES ('3', '產品');
INSERT INTO `departments` VALUES ('4', '運維');


-- 創建存盤函式
delimiter $$
create PROCEDURE sp_var01_into()
BEGIN
 -- 宣告區域變數
 DECLARE dept_name VARCHAR(32) DEFAULT 'unkown';
 DECLARE dept_no int DEFAULT 0;

 -- select 查詢要給區域變數賦值的屬性 into 直接進行賦值
 SELECT d.id,d.dep_name into dept_no,dept_name from departments d where d.id = 1;

 select dept_no,dept_name;
END $$

call  sp_var01_into()$$

drop PROCEDURE sp_var01_into;

2、用戶變數

用戶自定義，當前會話（連接）有效

語法：

@var_name

不需要提前宣告，使用即宣告

set 賦值

delimiter $$
create PROCEDURE sp_var02()
BEGIN
	set @nick_name = 'WR';
END $$

CALL sp_var02 $$
select @nick_name $$

into 賦值

delimiter $$
create PROCEDURE sp_var_into()
BEGIN
	select d.dep_name into @deptname from departments d where d.id = 1;
END $$

CALL sp_var_into $$
select @deptname $$

3、會話變數

由系統提供，整個mysql服務器有效

語法：

@@global.var_name

4、全域變數

由系統提供，整個Mysql服務器有效

語法：

@@global.var_name

二、入參出參

-- 語法

in | out | inout param_name type

舉例

-- int 型別演示
delimiter $$
create PROCEDURE sp_param01(in age int)
BEGIN
	set @user_age = age;
end $$
call sp_param01(10) $$
SELECT @user_age $$

delimiter $$
create PROCEDURE sp_param03(in `name` VARCHAR(32))
BEGIN
	set @user_name = `name`;
end $$
call sp_param03('liman') $$
SELECT @user_name $$




-- out 型別，只負責輸出！
-- 需求：輸出傳入的地址字串對應的部門編號，
delimiter $$
create PROCEDURE sp_param02(in deptname VARCHAR(32),out dept_no int(11))
BEGIN
	SELECT d.id into dept_no from departments d where d.dep_name = deptname;
	-- 此處強調，要么表起別名，要么入參名不與欄位名一致
end $$

-- 測驗
delimiter ;
set @dept_no = 7;
call sp_param02("開發",@dept_no);
select @dept_no;


-- inout型別
delimiter $$
create PROCEDURE sp_param04(inout `name` VARCHAR(32))
	BEGIN
		set `name` = CONCAT('hello ',`name`);
end $$
delimiter ;
set @user_name = '小明';
call sp_param04(@user_name);
SELECT @user_name;

三、流程控制--判斷

if -- 語法
IF search_condition THEN statement_list
[ELSEIF search_condition THEN statement_list] ...
[ELSE statement_list]
END IF

舉例：

DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
  `empno` int(50) NOT NULL AUTO_INCREMENT,
  `ename` varchar(50) COLLATE utf8_general_mysql500_ci DEFAULT NULL,
  `job` varchar(50) COLLATE utf8_general_mysql500_ci DEFAULT NULL,
  `mgr` int(50) DEFAULT NULL,
  `hiredate` datetime DEFAULT NULL,
  `sal` decimal(10,2) DEFAULT NULL,
  `comm` varchar(25) COLLATE utf8_general_mysql500_ci DEFAULT NULL,
  `deptno` int(50) NOT NULL,
  PRIMARY KEY (`empno`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_general_mysql500_ci;

-- ----------------------------
-- Records of emp
-- ----------------------------
INSERT INTO `emp` VALUES ('1', 'smith', 'clerk', '7902', '1980-12-07 12:42:26', '8000.00', null, '1');
INSERT INTO `emp` VALUES ('2', 'martmin', 'saleman', '1630', '1981-10-14 12:43:24', '10000.00', null, '2');

-- 前置知識點：timestampdiff(unit,exp1,exp2)取差值，單位是unit
select TIMESTAMPDIFF(YEAR,e.hiredate,now()) from emp e where e.empno = '2'

delimiter //

CREATE PROCEDURE sp_hire ()
BEGIN
  DECLARE result VARCHAR (32) ;
  DECLARE years int;
  select TIMESTAMPDIFF(YEAR,e.hiredate,now()) into years from emp e where e.empno = '2';

	IF years > 40 THEN
		SET result = '元老' ;
	ELSEIF years > 38 THEN
		SET result = '老員工' ;
	ELSE
		SET result = '新手' ;
	END IF ; 
	SELECT result ;
END//

delimiter ;

CALL sp_hire();

case 語法：此語法是不僅可以用在存盤程序，查詢陳述句也可以用！

語法一（類比java的switch）
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]
END CASE

舉例：

-- 需求：入職年限年齡<=38 是新手  >38<=40老員工 >40元老
-- 語法一：
-- 1.創建函式
delimiter $$
create PROCEDURE sp_hire_case_in(in year INT)
BEGIN
	DECLARE result VARCHAR(32);
	DECLARE message VARCHAR(64);
	CASE year
		when 40
			then 
					set result = '元老';
					set message = '老爺爺';
		when 38
			then 
					set result = '老員工';
					set message = '油膩中年人';
		ELSE  set result = '新手';
					set message = '萌新';
  end CASE;
  SELECT result,message;
end $$
delimiter;

-- 2.呼叫函式
call sp_hire_case_in(40);

-- 3.洗掉函式
drop PROCEDURE sp_hire_case_in;

-- 語法二：
-- 1.創建函式
delimiter $$
create PROCEDURE sp_hire_case()
BEGIN
	DECLARE result VARCHAR(32);
	DECLARE message VARCHAR(64);
	CASE
		when TIMESTAMPDIFF(YEAR,'2001-01-01',now()) > 40
			then 
					set result = '元老';
					set message = '老爺爺';
		when TIMESTAMPDIFF(YEAR,'2001-01-01',now()) > 38
			then 
					set result = '老員工';
					set message = '油膩中年人';
		ELSE  set result = '新手';
			  set message = '萌新';
     end CASE;
     SELECT result,message;
end $$
delimiter;

-- 2.呼叫函式
call sp_hire_case();

-- 3.洗掉函式
drop PROCEDURE sp_hire_case;

四、流程控制-回圈

LOOP 語法：
[begin_label:]LOOP
statement_list
END LOOP [end_label]

舉例：
需要說明，loop是死回圈，需要手動退出回圈，我們可以使用leave來退出
可以把leave看成我們Java中的break;
與之對應的，就有iterate(繼續回圈),類比java中的continue

死回圈處理解決方法：

-- 如有死回圈處理，可以通過下面的命令查看并結束

show processlist;

kill  id;

實體：

-- 需求：回圈列印到1到10
-- leave控制回圈的退出
delimiter $$
CREATE PROCEDURE sp_flow_loop()
begin 
	DECLARE c_index int DEFAULT 1;
  DECLARE result_str VARCHAR(256) default '1';
  cnt:LOOP

		if c_index >= 10
			then leave cnt;
		end if;

		set c_index = c_index + 1;
		set result_str = CONCAT(result_str,',',c_index);

  end loop cnt;
  select result_str;
    
end $$

delimiter ;
call sp_flow_loop();


delimiter $$
create PROCEDURE sp_flow_loop02()
begin 
  DECLARE c_index int  DEFAULT 1;
  DECLARE result_str VARCHAR(256) default '1';
  cnt:LOOP

		set c_index = c_index + 1;
		set result_str = CONCAT(result_str,',',c_index);
		if c_index < 10 then 
			 ITERATE cnt;  -- 約等于java的continue
		end if;
		-- 	下面這句話能否執行到？什么時候執行到
    leave cnt;

  end loop cnt;
  select result_str;
END $$

delimiter;
call sp_flow_loop02();

REPEAT語法:相當于java中的 DO...WHILE...

[begin_label:]REPEAT
statement_list
until search_condition -- 直到... 為止才退出回圈
END REPEAT [end_label]

舉例：

-- 需求：回圈列印1到10
delimiter $$
create PROCEDURE sp_flow_repeat()
BEGIN
	DECLARE c_index int DEFAULT 1;
	-- 	收集結果字串
	declare result_str VARCHAR(256) DEFAULT '1';
	count_lab:REPEAT
		SET c_index = c_index + 1;
		set result_str = CONCAT(result_str,',',c_index);
		until c_index >= 10;
end REPEAT count_lab;
select result_str;
end $$
delimiter;
call sp_flow_repeat();

while:類比Java中的while（）{}

while語法：
[begin_label:] while search_condition DO
statement_list
END WHILE [end_label]

-- 需求：回圈列印1到10
delimiter $$
create PROCEDURE sp_flow_while()
begin 
	DECLARE c_index int DEFAULT 1;
	-- 收集結果字串
  DECLARE result_str VARCHAR(256) DEFAULT '1';

	while c_index < 10 DO
		set c_index = c_index + 1;
		set result_str = CONCAT(result_str,',',c_index);
	end while;
	SELECT result_str;
end $$

delimiter ;
call sp_flow_while();

五、流程控制 -- 退出、繼續回圈

leave:類比java的Break;

-- 退出leave can be used within begin ... end or loop constructs(LOOP,REPEAT,WHILE).
LEAVE label

ITERATE:類比java的continue;

-- 繼續回圈 ITERATE can appear only within LOOP,and while statements
ITERATE label

六、游標

用游標得到某一個結果集，逐行處理資料（不建議用，因為是一行一行進行掃描的，效率比較低）
類比 jdbc的resultSet

-- 宣告語法
DECLARE cursor_name CURSOR FOR select_statement

-- 打開語法
OPEN cursor_name

-- 取值語法(當fetch 到底 報錯 no datas ) 每次只能一個值
FETCH cursor_name into var_name [,var_name] ...

-- 關閉語法
CLOSE cursor_name

實體：

-- 需求：按照部門名稱查詢員工，通過select查看員工的編號、姓名、薪資，（注意，此處僅僅演示游標用法）
drop PROCEDURE if EXISTS sp_create_table;

delimiter $$
create PROCEDURE sp_create_table(in dept_name VARCHAR(32))
BEGIN 
  DECLARE emp_no int;
  DECLARE emp_name VARCHAR(32);
  DECLARE emp_sal DECIMAL(7,2); 

  DECLARE lp_flag boolean DEFAULT true;
 
  DECLARE emp_cursor cursor FOR  
		SELECT e.empno,e.ename,e.sal from emp e 
		inner join departments d 
		on e.deptno = d.id where d.dep_name = dept_name;

  -- handle 句柄
  DECLARE CONTINUE HANDLER for 1329 set lp_flag = false;

  OPEN emp_cursor;

  emp_loop:LOOP
		FETCH emp_cursor into emp_no,emp_name,emp_sal; -- 一個fetch每次只能取一個值
		if lp_flag then 
			SELECT emp_no,emp_name,emp_sal;
		ELSE
			LEAVE emp_loop;
		END IF;
	END LOOP emp_loop;
  set @end_flag = 'end';

  CLOSE emp_cursor;
END $$
delimiter ;
CALL sp_create_table('開發');

特別注意：

在語法中，變數宣告、游標宣告、handle宣告是必須按照先后順序書寫的，否則創建存盤程序出錯，

七、存盤程序中的handle

HANDLER 語法：
DECLARE handler_action HANDLER 
	for condition_value [,condition_value] ...
statement

handler_ation:{
	CONTINUE
|EXIT
|UNDO
}

conditon_value:{
	mysql_error_code
|SQLSTATE [VALUE] sqlstate_value
|conditon_name
|SQLWARNING
|NOT FOUND
|SQLEXCEPTION
}

轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/193776.html

標籤：其他

上一篇：mysql出現提示錯誤10061的解決方法

下一篇：Mysql性能調優(二)

MySQL 5.6存盤程序和函式語法的簡單介紹