CREATE PROCEDURE <proc_name>([IN/OUT args])
BEGIN
	-- SQL
END;

-- 創(chuàng)建存儲(chǔ)過(guò)程，實(shí)現(xiàn)加法運(yùn)算
-- 存儲(chǔ)過(guò)程是有出入?yún)?shù)和輸出參數(shù)的
CREATE PROCEDURE proc_test1(IN a INT,IN b INT,OUT c INT)
BEGIN
	SET c = a+b;
END;

-- 刪除存儲(chǔ)過(guò)程
DROP PROCEDURE proc_test1;

-- 定義變量
SET @m = 0;
-- 調(diào)用存儲(chǔ)過(guò)程
CALL proc_test1(3,2,@m);
-- 顯示變量值
SELECT @m from DUAL;

-- 局部變量需要定義在存儲(chǔ)過(guò)程中，而且必須定義在存儲(chǔ)過(guò)程開(kāi)始
declare <attr_name> <type> [default value];
-- 創(chuàng)建存儲(chǔ)過(guò)程:計(jì)算輸入?yún)?shù)的平方與輸入?yún)?shù)/2 之和
CREATE PROCEDURE proc_test2(IN a INT,OUT r INT)
BEGIN
	DECLARE x INT DEFAULT 0; -- 局部變量定義
	DECLARE y INT DEFAULT 0; -- 局部變量定義
	set x = a*a;
	set y = a/2;
	SET r = x + y ;
END

-- 用戶變量會(huì)存儲(chǔ)在mysql數(shù)據(jù)庫(kù)的數(shù)據(jù)字典中（dual）
-- 用戶變量定義使用set關(guān)鍵字直接定義，變量名要以@開(kāi)頭
SET @a = 0; -- 定義用戶變量
SELECT @a from DUAL;-- 查詢用戶變量

-- 創(chuàng)建存儲(chǔ)過(guò)程，查詢學(xué)生表中的學(xué)生數(shù)量,賦值給參數(shù)s
CREATE PROCEDURE proc_test3(OUT s INT)
BEGIN
	SELECT COUNT(stu_num) INTO s from students;-- 將查詢到的學(xué)生數(shù)量，賦值給參數(shù)s
END;
-- 調(diào)用存儲(chǔ)過(guò)程test3
SET @s=0;
CALL proc_test3(@s);
select @s from DUAL;

-- 創(chuàng)建存儲(chǔ)過(guò)程：添加學(xué)生信息
CREATE PROCEDURE proc_test4 (IN snum CHAR(8),IN sname VARCHAR(20),IN denger CHAR(2),IN age INT,IN tel VARCHAR(20),IN cid INT)
BEGIN
	INSERT INTO students(stu_num,stu_name,stu_denger,stu_age,stu_tel,cid) 	         		VALUES(snum,sname,denger,age,tel,cid);
END;
-- 調(diào)用存儲(chǔ)過(guò)程
CALL proc_test4('8','張飛','男',88,'13667565656',2);

-- 創(chuàng)建存儲(chǔ)過(guò)程：根據(jù)學(xué)號(hào)，查詢學(xué)生姓名
CREATE PROCEDURE proc_test5(IN snum INT,OUT sname VARCHAR(20))
BEGIN
	SELECT stu_name INTO sname from students where stu_num = snum;
END;
-- 設(shè)置用戶參數(shù)
SET @name = '';
-- 調(diào)用存儲(chǔ)過(guò)程
CALL proc_test5(8,@name);
select @name from DUAL;

-- 存儲(chǔ)過(guò)程：根據(jù)學(xué)號(hào)，查詢學(xué)生姓名，使用INOUT
CREATE PROCEDURE proc_test6(INOUT str VARCHAR(20))
BEGIN
	SELECT stu_name INTO str from students where stu_num = str;
END;
-- 設(shè)置參數(shù),默認(rèn)賦值為查詢參數(shù)值
set @str = '8';
-- 調(diào)用存儲(chǔ)過(guò)程
CALL proc_test6(@str);
SELECT @str from dual;

-- 根據(jù)數(shù)據(jù)庫(kù)名，查詢當(dāng)前數(shù)據(jù)庫(kù)中的存儲(chǔ)過(guò)程
show PROCEDURE STATUS where db = 'db_test';
-- 查詢存儲(chǔ)過(guò)程的創(chuàng)建細(xì)節(jié)
SHOW CREATE PROCEDURE db_test.proc_test1;

alter procedure <proc_name> 特征1 {特征2...}

-- 刪除存儲(chǔ)過(guò)程 DROP
DROP PROCEDURE proc_test1;

## 創(chuàng)建數(shù)據(jù)庫(kù)
create database da_test3;
## 使用數(shù)據(jù)庫(kù)
use db_test3;

-- 創(chuàng)建圖書(shū)信息表
CREATE TABLE books(
	book_id INT PRIMARY KEY auto_increment,
	book_name VARCHAR(50) NOT NULL,
	book_author VARCHAR(20) NOT NULL,
	book_price DECIMAL(10,2) NOT NULL,
	book_stock INT NOT NULL,
	book_desc VARCHAR(200)
);
-- 添加圖書(shū)信息
INSERT INTO books(book_name,book_author,book_price,book_stock,book_desc) 
VALUES ('Java從入門到放棄','斯蒂芬',28.80,100,'一本帶你從入門到放棄的java頂級(jí)教材');
INSERT INTO books(book_name,book_author,book_price,book_stock,book_desc) 
VALUES ('MySQL從入門到放棄','庫(kù)里',68.20,20,'一本帶你從入門到放棄的mysql頂級(jí)教材');
-- 創(chuàng)建學(xué)生信息表
CREATE TABLE students(
	stu_num CHAR(8) PRIMARY KEY,
	stu_name VARCHAR(20) NOT NULL,
	stu_denger CHAR(2) NOT NULL,
	stu_age INT NOT NULL
);
-- 添加學(xué)生信息
INSERT INTO students (stu_num,stu_name,stu_denger,stu_age) VALUES('1001','不知火舞','女','20');
INSERT INTO students (stu_num,stu_name,stu_denger,stu_age) VALUES('1002','安其拉','女','25');
INSERT INTO students (stu_num,stu_name,stu_denger,stu_age) VALUES('1003','奕星','男','30');

-- 借書(shū)記錄表
CREATE TABLE records(
	rid INT PRIMARY KEY auto_increment,
	snum CHAR(4) NOT NULL,
	bid INT NOT NULL,
	borrow_num INT NOT NULL,
	is_return INT NOT NULL,	-- 0-未歸還；1-已歸還
	borrow_date date NOT NULL,
	CONSTRAINT FK_RECORDS_STUDENTS FOREIGN KEY(snum) REFERENCES students(stu_num),
	CONSTRAINT FK_RECORDS_BOOKS FOREIGN KEY(bid) REFERENCES books(book_id)
);

-- 實(shí)現(xiàn)借書(shū)業(yè)務(wù)
-- 參數(shù)1：	輸入?yún)?shù)	學(xué)號(hào)	a
-- 參數(shù)2：	輸入?yún)?shù)	圖書(shū)標(biāo)號(hào)	b
-- 參數(shù)3：	輸入?yún)?shù)	借書(shū)數(shù)量	m
-- 參數(shù)4：	輸出參數(shù)	借書(shū)狀態(tài)（1-借書(shū)成功；2-學(xué)號(hào)不存在；3-圖書(shū)不存在；4-庫(kù)存不足）
CREATE PROCEDURE proc_borrow_book(IN a CHAR(4),IN b INT,IN m INT,OUT state INT)
BEGIN
	DECLARE stu_count INT DEFAULT 0;	
	DECLARE b_count INT DEFAULT 0;
	DECLARE b_stock INT DEFAULT 0;
	-- 一、判斷學(xué)號(hào)是否存在	根據(jù)參數(shù)a去學(xué)生表查詢是否存在學(xué)生
	SELECT COUNT(stu_num) INTO stu_count from  students where stu_num = a;
	IF stu_count > 0 THEN
		-- 學(xué)號(hào)存在
		-- 二、查看圖書(shū)編號(hào)是否存在
		SELECT COUNT(book_id) INTO b_count from books where book_id = b;
		IF b_count > 0 THEN
			-- 圖書(shū)存在
			-- 三、查詢圖書(shū)庫(kù)存是否充足
			SELECT book_stock INTO b_stock from books where book_id = b;
			IF b_stock >= m THEN
				-- 庫(kù)存滿足
				-- 1、插入借書(shū)記錄表
				INSERT INTO records(snum,bid,borrow_num,is_return,borrow_date) VALUES(a,b,m,0,SYSDATE());
				-- 2、更新books表庫(kù)存數(shù)據(jù)book_stock
				UPDATE books SET book_stock = (b_stock - m) where book_id = b;
				-- 3、借書(shū)成功，返回成功狀態(tài) 0 
				SET state = 1;
			ELSE
				-- 庫(kù)存不足
			SET state = 4;
			END IF;
		ELSE
			-- 圖書(shū)不存在
		SET state = 3;
		END IF;
	ELSE
		-- 學(xué)號(hào)不存在
	SET state = 2;
	END IF;
END;

SELECT * FROM students;-- 學(xué)生表
select * from books;-- 圖書(shū)表
select * from records;-- 借書(shū)記錄表
-- 測(cè)試借書(shū)存儲(chǔ)過(guò)程
-- 1、正常借書(shū)成功業(yè)務(wù)測(cè)試：學(xué)生學(xué)號(hào)a = 1001;借書(shū)編號(hào)b = 1;借書(shū)數(shù)量m = 10;
SET @state = 0;
CALL proc_borrow_book('1001',1,10,@state);
SELECT @state from DUAL;
-- 2、測(cè)試學(xué)號(hào)不存在：學(xué)生學(xué)號(hào)a = 1008;借書(shū)編號(hào)b = 1;借書(shū)數(shù)量m = 10;
SET @state = 0;
CALL proc_borrow_book('1008',1,10,@state);
SELECT @state from DUAL;
-- 3、測(cè)試圖書(shū)編號(hào)不存在：學(xué)生學(xué)號(hào)a = 1002;借書(shū)編號(hào)b = 8;借書(shū)數(shù)量m = 10;
SET @state = 0;
CALL proc_borrow_book('1002',8,10,@state);
SELECT @state from DUAL;
-- 4、測(cè)試圖書(shū)庫(kù)存不足：學(xué)生學(xué)號(hào)a = 1002;借書(shū)編號(hào)b = 2;借書(shū)數(shù)量m = 100;
SET @state = 0;
CALL proc_borrow_book('1002',2,100,@state);
SELECT @state from DUAL;