變數（儲存過程和函式控制流程都要用到變數）

系統變數：是資料庫伺服器層面的，不是使用者定義的。
全域性變數：在資料庫系統，針對所有的資料庫的使用者、會話【一次連線】都有效
會話變數：在一次連線中有效，新的連線，這個變數就無效了
使用者自定義變數
使用者變數：在一次連線中有效，新的連線，這個變數就無效了
區域性變數：定義在程式塊中，其他的程式或者程式塊使用不了這個變數

一、系統變數

​ 說明：屬於系統定義的變數，不是使用者定義，所以屬於資料庫伺服器層面的
​ 在開發中，系統變數中分為全域性變數和會話變數的操作基本是一樣的，
​ 僅僅只有一個單詞的區別
​ 全域性：GLOBAL
​ 會話：SESSION

使用步驟：

1.檢視所有的系統變數,使用GLOBAL檢視全域性的，SESSION檢視會話的，預設則是會話變數
SHOW [GLOBAL|SESSION] VARIABLES;
SHOW GLOBAL VARIABLES;
2.檢視滿足條件的系統變數
SHOW [GLOBAL|SESSION] VARIABLES LIKE ‘%關鍵字%’;
SHOW GLOBAL VARIABLES LIKE ‘%auto%’;
3.檢視指定系統變數的值
SELECT @@系統變數名稱
SELECT @@autocommit;

4.設定系統變數的值
方式一：SET [GLOBAL|SESSION] 系統變數名稱=值;
方式二：SET [@@global|SESSION] 系統變數名=值;
SET SESSION autocommit = 0;
SET GLOBAL autocommit = 0;

二、使用者自定義變數

使用步驟：

宣告

賦值

使用

1 使用者變數：值針對當前的連線[會話]有效。

① 宣告使用者變數：宣告和賦值可以是一起操作，（mysql賦值符號有=或:=）
SET @變數名 = 值;
SET @變數名 := 值; //實際上在Oracle裡面也可以 使用 DEFAULT
SELECT @變數名 := 值;

SET @shcool = ‘南航科院’;
SET @shcool1 := ‘江西理工’;
SELECT @shcool2 := ‘萬邦易嵌’;

②給變數賦值
方式一：
SET @變數名 = 值;
SET @變數名 := 值; //用SET或SELECT給變數賦值
SELECT @變數名 := 值;
方式二：將查詢的結果賦值給變數
SELECT 資料列 INTO @變數名 FROM 表 ; //要求查詢的結果集是單行的，資料列數要和變數列表一致
SELECT stu_id,stu_name INTO @stuId,@stuName FROM student_info WHERE stu_id = 1;

SELECT ‘wanbangee’ INTO @shcool;

③ 使用變數
SELECT @變數名;
SELECT @shcool;

2.區域性變數

作用域：僅僅只能在定義這個變數的程式塊[BEGIN END]中使用
而且變數必須宣告在BEGIN END開頭

BEGIN 
	#宣告變數
	程式;
	程式;
	#宣告變數，這是錯誤的
END;

① 宣告區域性變數
declare 變數名 型別;
declare 變數名 型別 default 值; //oracle中 declare 變數名 型別 := 值

② 給區域性變數賦值
方式一：
set 變數名 = 值;
set 變數名 := 值;
select 變數名 := 值;
方式二：
select 資料列 into 變數名 from 表 ; //要求查詢的結果集是單行的，資料列數要和變數列表一致

③ 檢視區域性變數
select 區域性變數名稱;

	作用域	定義的位置	語法
使用者變數	在當前會話中有效	哪裡都可以	加 @符號 不指定型別
區域性變數	在當前的begin end 中有效	在begin end第一行位置	不用@,但是有型別

儲存過程

儲存過程和函式，在傳統J2EE企業級開發用的多，網際網路開發用的少。
儲存過程和函式：類似於Java中的方法，在資料庫、前端JS中叫函式。
好處：
1、提高程式碼的重用性
2、簡化程式碼編寫
儲存過程的含義：一組預先編譯好的SQL語句的集合，理解成批處理。
1、提高程式碼的重用性
2、簡化程式碼編寫
3、減少了編譯次數和資料庫伺服器的連線次數，提升了效率。
① 宣告區域性變數
DECLARE 變數名 型別;
DECLARE 變數名 型別 DEFAULT 值;
② 給區域性變數賦值
方式一：
SET 變數名 = 值;
SET 變數名 := 值;
SELECT 變數名 := 值;
方式二：
SELECT 資料列 INTO 變數名 FROM 表 ;
③ 檢視區域性變數
SELECT 區域性變數名稱;

	作用域	定義的位置	語法
使用者變數	在當前會話中有效	哪裡都可以	加 @符號 不指定型別
區域性變數	在當前的begin end 中有效	在begin end第一行位置	不用@,但是有型別

一、 建立儲存過程procedure

create PROCEDURE 過程名稱(引數列表)
begin
儲存過程體(一組SQL語句)
end

注意點：
1.引數列表包含三個部分
引數模式 引數名 引數型別
舉例:
create procedure myp1(in stu_name varchar(20))
begin

end
引數模式總共有三種：
in:表示這個引數是入參，在呼叫這個過程的時候必須傳入實際引數
out：表示這個引數是輸出引數，實際上這個引數就是過程的返回值
inout：即是傳入引數，又是輸出引數
2.儲存過程編寫規則

如果儲存過程體只有一句sql，那麼begin end 可以省略

儲存過程體中的每一條sql語句必須結尾，必須使用;結束

必須標記其他符號作為結束符號，使用delimiter 來標記

DELIMITER $;
SELECT * FROM employees $

二、 呼叫儲存過程

call 儲存過程名稱(引數列表)

案例1.沒有引數的儲存過程

1.建立儲存過程，往admin表中插入5筆資料。

DELIMITER $       #設定$為程式結束符
CREATE PROCEDURE myp1()
BEGIN
	INSERT INTO admin(username,password) VALUES 
	('jhon','123'),
	('lucy','123'),
	('lili','123'),
	('tom','123'),
	('jreey','123'),
	('rose','123');    #用;結束sql語句
END $    #用$來結束整個程式

呼叫：
CALL myp1()

案例2.帶in模式引數的儲存過程

2 傳入女神名，查詢對應的男神
DELIMITER $
CREATE PROCEDURE myp2(IN girlName VARCHAR(20))
BEGIN 
	SELECT b.* FROM beauty a ,boys b
	WHERE a.`boyfriend_id` = b.id 
	AND a.`name` = girlName;
END $

呼叫：
CALL myp2('Angelababy')

案例3.帶in模式 兩個引數的儲存過程

3 傳入使用者名稱和密碼，顯示使用者是否登入成功

CREATE PROCEDURE myp3(IN user_name VARCHAR(20),IN pass_word VARCHAR(20))
BEGIN
	DECLARE result  INT;
	SELECT COUNT(*) INTO result FROM admin WHERE username= user_name 
	AND password = pass_word;
	SELECT IF(result>0,'success','fail');
END $

呼叫：
CALL myp3('jjm','123') 

案例4.帶out模式引數 的儲存過程

4 傳入使用者名稱和密碼，返回使用者登入成功或失敗的訊息
CREATE PROCEDURE myp4(IN user_name VARCHAR(20),IN pass_word VARCHAR(20),OUT res VARCHAR(10))
BEGIN
	DECLARE result  INT;
	SELECT COUNT(*) INTO result FROM admin WHERE username= user_name 
	AND password = pass_word;
	SELECT IF(result>0,'success','fail') INTO res;
END $

呼叫：
CALL myp4('lucy','123',@result) $
SELECT  @result $

三 、刪除過程

DROP PROCEDURE myp3

四 、檢視儲存過程

show create PROCEDURE 儲存過程名

SHOW CREATE PROCEDURE myp3;

函式

函式類似儲存過程，儲存過程和函式：類似於Java中的方法

好處：
1、提高程式碼的重用性
2、簡化程式碼編寫

函式的含義：一組預先編譯好的sql語句的集合，理解成批處理
1、提高程式碼的重用性
2、簡化程式碼編寫
3、減少了編譯次數和資料庫伺服器的連線次數，提升了效率

函式與儲存過程的區別：

函式：有且僅有一個返回值，適合進行資料處理之後返回一個結果

過程：可以有0個返回值，可以定義多個返回值，適合做批量的插入、修改、刪除操作

一 、函式的建立

create function 函式名(引數列表) returns 返回型別
begin
函式體
end
注意點：
1.引數列表 包含兩個部分
引數名稱 引數型別
2.函式體必須存在return語句，沒有則會報錯 ，return放在方法體的最後，
3.如果函式體中僅僅只有一條sql，可以省略begin end
4.使用DELIMITER語句定義結束識別符號

二、 呼叫函式

select 函式名(引數列表)

三、 刪除函式

drop function 函式名1,函式名2…

案例1.沒有引數的函式

案例：返回員工個數

CREATE FUNCTION myf1() RETURNS INT
BEGIN	
	DECLARE emp_count INT;
	SELECT COUNT(*) INTO emp_count FROM employees;
	RETURN emp_count;
END $
呼叫函式：
SELECT myf1();

案例2.有引數有返回的函式

案例：根據出入的員工的last_name，返回工資

CREATE FUNCTION get_salary_by_last_name(last_name VARCHAR(20)) RETURNS DOUBLE
BEGIN 
	DECLARE emp_salary DOUBLE;
	SELECT salary INTO emp_salary FROM employees WHERE employees.last_name = last_name;
	RETURN emp_salary;
END $
呼叫函式：
SELECT get_salary_by_last_name('De Haan');

流程控制語句

程式執行流程有：順序，分支，迴圈

一、 分支結構

1.if函式
語法：if(條件，值1，值2) //三目運算子
可以使用在select字句中，也可以使用begin end 中

2.case結構
語法：類似於java的switch case
語法：
case 變數或表示式
when 值1/表示式 then 語句1；
when 值2/表示式 then 語句2；
…
else 語句n;
end case; //結束case分支

可以用在select字句中，也可以用在begin end 中
3.if結構
語法：
if 條件1 then 語句1；
elseif 條件2 then 語句2；
…
else 語句n;
end if; //結束if分支
只能用在begin end 中

案例1：建立函式，傳入Java的成績，如果成績>90 返回A，如果大於80 返回B…小於60 返回 E

使用if結構：

DELIMITER $
CREATE FUNCTION get_score_level(score FLOAT) RETURNS CHAR
BEGIN 
	DECLARE score_level CHAR ;
	IF score >= 90 THEN SET score_level='A';
	ELSEIF score >= 80 THEN SET score_level='B';
	ELSEIF score >= 70 THEN SET score_level='C';
	ELSEIF score >= 60 THEN SET score_level='D';
	ELSE SET score_level='E';
	END IF;
	RETURN score_level;
END $

SELECT get_score_level(59);

使用case結構：

DELIMITER $
CREATE FUNCTION get_score_level2(score FLOAT) RETURNS CHAR
BEGIN 
	DECLARE score_level CHAR ;
	CASE 
	WHEN score >= 90 THEN SET score_level='A';
	WHEN score >= 80 THEN SET score_level='B';
	WHEN score >= 70 THEN SET score_level='C';
	WHEN score >= 60 THEN SET score_level='D';
	ELSE SET score_level='E';
	END CASE;
	RETURN score_level;
END $

SELECT get_score_level2(80);

二 、迴圈結構

while loop repeat 三種迴圈語法
迴圈控制：

iterate 類似於java中continue，表示結束本次迴圈，繼續執行洗一次迴圈

leave 類似於java中break,退出迴圈，終止迴圈

1.while 迴圈

語法：while 迴圈條件 do
迴圈體;
end while;

2.loop 迴圈

語法：loop

迴圈體;
end loop;

如果不在迴圈體中編寫退出的話，則直接就是死迴圈

3.repeat 迴圈(用的少)

語法：
repeat
迴圈體;
until 結束迴圈的條件;
end repeat;

案例: 批量資料插入，根據傳入的次數插入資料到admin表中

DELIMITER $
CREATE PROCEDURE pro_insert_admin(IN insertc_count INT)
BEGIN 
	DECLARE i INT DEFAULT 1;
	WHILE i<=insertc_count DO
		INSERT INTO admin(username,PASSWORD) VALUES(CONCAT('rose',i),'666');
		SET i = i+1;
	END WHILE;
END $

CALL pro_insert_admin(20);

java中：
int i = 1;
	while(i<=insertc_count){
		//執行相應的操作
		i++;
	}

給迴圈一個標籤（用的少）

java中給迴圈加個標籤，用的少	
a:while(....){
	while(....){
		break a;//結束哪個迴圈
	}
}

DELIMITER $
CREATE PROCEDURE pro_insert_admin1(IN insert_count INT)
BEGIN 
	DECLARE i INT DEFAULT 1;
	a:WHILE i<=insert_count DO
		INSERT INTO admin(username,PASSWORD) VALUES(CONCAT('lucy',i),'666');
		SET i = i+1;
	END WHILE a;

END $

CALL pro_insert_admin1(10);

案例: 新增 leave 終止迴圈 ,增加到第 5 筆 就不增加了

DELIMITER $
CREATE PROCEDURE pro_insert_admin2(IN insert_count INT)
BEGIN 
	DECLARE i INT DEFAULT 1;
	a:WHILE i<=insert_count DO
		INSERT INTO admin(username,PASSWORD) VALUES(CONCAT('lucy',i),'666');
		IF i >= 5 THEN LEAVE a; -- 表示完成5行插入則退出程式
		END IF;
		SET i = i+1;
	END WHILE a;

END $

CALL pro_insert_admin2(20);

新增 iterate 終止本次迴圈 ,奇數新增資料，偶數不新增資料

DELIMITER $
CREATE PROCEDURE pro_insert_admin3(IN insert_count INT)
BEGIN 
	DECLARE i INT DEFAULT 0;
	a:WHILE i<=insert_count DO
		SET i = i+1;
		IF MOD(i,2) = 0 THEN ITERATE a;
		END IF;
		INSERT INTO admin(username,PASSWORD) VALUES(CONCAT('lucy',i),'666');
	END WHILE a;

END $

CALL pro_insert_admin3(20);

使用loop迴圈

DELIMITER $
CREATE PROCEDURE pro_insert_admin4(IN insert_count INT)
BEGIN 
	DECLARE i INT DEFAULT 0;
	a:LOOP
		IF i > insert_count THEN LEAVE a;
		END IF;
		SET i = i+1;
		IF MOD(i,2) = 0 THEN ITERATE a;
		END IF;
		INSERT INTO admin(username,PASSWORD) VALUES(CONCAT('lucy',i),'666');
	END LOOP a;

END $
CALL pro_insert_admin4(20);