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

-- 創(chuàng)建存儲過程，實現加法運算
-- 存儲過程是有出入參數和輸出參數的
CREATE PROCEDURE proc_test1(IN a INT,IN b INT,OUT c INT)
BEGIN
	SET c = a+b;
END;

-- 刪除存儲過程
DROP PROCEDURE proc_test1;

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

-- 局部變量需要定義在存儲過程中，而且必須定義在存儲過程開始
declare <attr_name> <type> [default value];
-- 創(chuàng)建存儲過程:計算輸入參數的平方與輸入參數/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

-- 用戶變量會存儲在mysql數據庫的數據字典中（dual）
-- 用戶變量定義使用set關鍵字直接定義，變量名要以@開頭
SET @a = 0; -- 定義用戶變量
SELECT @a from DUAL;-- 查詢用戶變量

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

-- 創(chuàng)建存儲過程：添加學生信息
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;
-- 調用存儲過程
CALL proc_test4('8','張飛','男',88,'13667565656',2);

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

-- 存儲過程：根據學號，查詢學生姓名，使用INOUT
CREATE PROCEDURE proc_test6(INOUT str VARCHAR(20))
BEGIN
	SELECT stu_name INTO str from students where stu_num = str;
END;
-- 設置參數,默認賦值為查詢參數值
set @str = '8';
-- 調用存儲過程
CALL proc_test6(@str);
SELECT @str from dual;

-- 根據數據庫名，查詢當前數據庫中的存儲過程
show PROCEDURE STATUS where db = 'db_test';
-- 查詢存儲過程的創(chuàng)建細節(jié)
SHOW CREATE PROCEDURE db_test.proc_test1;

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

-- 刪除存儲過程 DROP
DROP PROCEDURE proc_test1;

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

-- 創(chuàng)建圖書信息表
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)
);
-- 添加圖書信息
INSERT INTO books(book_name,book_author,book_price,book_stock,book_desc) 
VALUES ('Java從入門到放棄','斯蒂芬',28.80,100,'一本帶你從入門到放棄的java頂級教材');
INSERT INTO books(book_name,book_author,book_price,book_stock,book_desc) 
VALUES ('MySQL從入門到放棄','庫里',68.20,20,'一本帶你從入門到放棄的mysql頂級教材');
-- 創(chuàng)建學生信息表
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
);
-- 添加學生信息
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');

-- 借書記錄表
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)
);

-- 實現借書業(yè)務
-- 參數1：	輸入參數	學號	a
-- 參數2：	輸入參數	圖書標號	b
-- 參數3：	輸入參數	借書數量	m
-- 參數4：	輸出參數	借書狀態(tài)（1-借書成功；2-學號不存在；3-圖書不存在；4-庫存不足）
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;
	-- 一、判斷學號是否存在	根據參數a去學生表查詢是否存在學生
	SELECT COUNT(stu_num) INTO stu_count from  students where stu_num = a;
	IF stu_count > 0 THEN
		-- 學號存在
		-- 二、查看圖書編號是否存在
		SELECT COUNT(book_id) INTO b_count from books where book_id = b;
		IF b_count > 0 THEN
			-- 圖書存在
			-- 三、查詢圖書庫存是否充足
			SELECT book_stock INTO b_stock from books where book_id = b;
			IF b_stock >= m THEN
				-- 庫存滿足
				-- 1、插入借書記錄表
				INSERT INTO records(snum,bid,borrow_num,is_return,borrow_date) VALUES(a,b,m,0,SYSDATE());
				-- 2、更新books表庫存數據book_stock
				UPDATE books SET book_stock = (b_stock - m) where book_id = b;
				-- 3、借書成功，返回成功狀態(tài) 0 
				SET state = 1;
			ELSE
				-- 庫存不足
			SET state = 4;
			END IF;
		ELSE
			-- 圖書不存在
		SET state = 3;
		END IF;
	ELSE
		-- 學號不存在
	SET state = 2;
	END IF;
END;

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