存儲過程（Stored Procedure）是在大型數(shù)據(jù)庫系統(tǒng)中略贮，一組為了完成特定功能的SQL 語句集兄淫，存儲在數(shù)據(jù)庫中绑莺，經(jīng)過第一次編譯后再次調(diào)用不需要再次編譯哈误，用戶通過指定存儲過程的名字并給出參數(shù)（如果該存儲過程帶有參數(shù)）來執(zhí)行它哩至。存儲過程是數(shù)據(jù)庫中的一個重要對象。
一蜜自、存儲過程語法
DELIMITER //
?CREATE?PROCEDURE? 方法名 (參數(shù)類型 參數(shù)名 數(shù)據(jù)類型菩貌，...);
? BEGIN
?????? 過程體（編寫方法需要執(zhí)行的操作）;
?? END;
//
DELIMITER ；
說明：
（1）分隔符->MySQL默認以";"為分隔符重荠，如果沒有聲明分割符箭阶，則編譯器會把存儲過程當(dāng)成SQL語句進行處理，因此編譯過程會報錯，所以要事先用“DELIMITER //”聲明當(dāng)前段分隔符仇参，讓編譯器把兩個"http://"之間的內(nèi)容當(dāng)做存儲過程的代碼嘹叫，不會執(zhí)行這些代碼；“DELIMITER ;”的意為把分隔符還原冈敛。
(2)參數(shù)->存儲過程根據(jù)需要待笑，可能會有輸入、輸出抓谴、輸入輸出參數(shù)暮蹂，如果有多個參數(shù)用","分割開。MySQL存儲過程的參數(shù)用在存儲過程的定義癌压，共有三種參數(shù)類型,IN,OUT,INOUT:
IN參數(shù)的值必須在調(diào)用存儲過程時指定仰泻，在存儲過程中修改該參數(shù)的值不能被返回，為默認值
OUT:該值可在存儲過程內(nèi)部被改變滩届，并可返回
INOUT:調(diào)用時指定集侯，并且可被改變和返回。
out:你往外輸出一個值，你輸出的那個值我就拿一個變量來接收你給我輸出的那個值
（3）過程體->過程體的開始與結(jié)束使用BEGIN與END進行標識泡挺。
二辈讶、存儲過程的意義
（1）增強SQL語言的功能和靈活性：
存儲過程可以用控制語句編寫，有很強的靈活性娄猫，可以完成復(fù)雜的判斷和較復(fù)雜的運算贱除。
（2）提升復(fù)用程度 ：
存儲過程被創(chuàng)建后，可以在程序中被多次調(diào)用媳溺，而不必重新編寫該存儲過程的SQL語句月幌。
（3）較快的執(zhí)行速度：
存儲過程是預(yù)編譯的。而批處理的SQL語句在每次運行時都要進行編譯和優(yōu)化悬蔽，速度相對要慢一些扯躺。
（4）減少網(wǎng)絡(luò)流量：
針對同一個數(shù)據(jù)庫對象的操作,如果操作的SQL語句寫在存儲過程，當(dāng)在客戶計算機上調(diào)用該存儲過程時蝎困，網(wǎng)絡(luò)中傳送的只是該調(diào)用語句缅帘，從而大大減少網(wǎng)絡(luò)流量并降低了網(wǎng)絡(luò)負載
三、調(diào)用存儲過程
CALL ?方法名?
四难衰、用戶定義變量
（1）用戶變量
使用SET或SELECT直接賦值钦无，變量名以 @ 開頭.
例如: SET @var=1;
???????? SELECT @var:=5;
???????? SELECT?? empno,ename INTO @var,@name FROM EMP WHERE empno =7499 ;
可以在一個會話的任何地方聲明，用戶變量可以作用于當(dāng)前整個連接盖袭，但是當(dāng)前連接斷開后失暂，其所定義的用戶變量都會消失稱為用戶變量彼宠。
（2）局部變量
DECLARE 關(guān)鍵字聲明的變量，只能在存儲過程中使用弟塞，稱為存儲過程變量凭峡，
格式：DECLARE? varname1[,varname2] ……? datatype??[DEFAULT VALUE]
例如：
DECLARE var1 INT DEFAULT 0;
DECLARE ?v1,v2?? VARCHAR(20);
SET C= 5; //賦值
SELECT?? empno,empname INTO var1,v1?FROMemp WHERE empno =7499;
局部變量一般用在sql語句塊中，比如存儲過程的begin/end决记。其作用域僅限于該語句塊摧冀，在該語句
塊執(zhí)行完畢后，局部變量就消失了系宫。
#練習(xí)1：編寫一個存儲過程索昂，要求能夠?qū)mp表中指定部門員工人數(shù)進行統(tǒng)計，并返回給調(diào)用者
SELECT * FROM emp
DELIMITER//
CREATE PROCEDURE statistical_two(IN department INT)
BEGIN
SELECT IFNULL(COUNT(empno),'無員工') FROM EMP? GROUP BY deptno
HAVING deptno = department;
END;
//
DELIMITER;
CALL statistical_two(40)
#練習(xí)2：編寫一個存儲過程扩借，要求能夠?qū)mp表中的所有員工平均工資進行統(tǒng)計椒惨，并返回調(diào)用者
DELIMITER//
CREATE PROCEDURE salarynum_one ()
BEGIN
SELECT AVG(sal) FROM emp;
END;
//
DELIMITER;
CALL salarynum_one
-----------------------------------
DELIMITER//
CREATE PROCEDURE salarynum (OUT salary FLOAT)
BEGIN
SELECT AVG(sal)INTO salary FROM emp;
END;
//
DELIMITER;
CALL salarynum(@1);
SELECT @1
/#練習(xí)3：編寫一個存儲過程，要求在執(zhí)行過程時潮罪，輸入一個員工的id號康谆，
/#過程能把該員工的所屬部門名稱和他的直接領(lǐng)導(dǎo)的姓名返回給調(diào)用者。#/
DELIMITER //
CREATE PROCEDURE belong_two (IN idnum INT)
BEGIN
SELECT ename,dname? FROM emp,dept
WHERE emp.deptno = dept.deptno
AND emp.empno = (SELECT mgr FROM emp WHERE empno = idnum);
END;
//
DELIMITER;
CALL belong_two(7369)
/#練習(xí)4：編寫一個存儲嫉到，要求在調(diào)用存儲時沃暗，輸入一個員工的id號，該函數(shù)能對該id號進行判斷何恶，判斷其是一個普通員工描睦，還是一個管理人員，如果是一個管理人員导而，就把他所管理的人數(shù)統(tǒng)計出來#/
DELIMITER?//
CREATE?PROCEDURE?Pmanager(IN?eid?INT)
BEGIN
DECLARE?n_work?VARCHAR(20);
DECLARE?num?INT;
SET?n_work=(SELECT?job?FROM?emp?WHERE?empno=eid);
IF?(n_work='MANAGER'OR?n_work='PRESIDENT')??THEN
SELECT?COUNT(*)?INTO?num?FROM?emp?WHERE?mgr=eid;
SELECT?'是管理人員',num;
ELSE
SELECT'是普通人員';
END?IF;
END;
#1. 查詢product表，顯示銷售價格低于4000的商品信息
SELECT * FROM product
WHERE pro_price < 4000
#2. 查詢product表隔崎，顯示銷售價格最高的商品的信息
SELECT * FROM product
WHERE pro_price IN (SELECT MAX(pro_price) FROM product)
#3. 查詢product表今艺，顯示10號供應(yīng)商所提供的商品信息
SELECT * FROM product
WHERE pro_sup_id = 10
#4. 查詢product表，顯示10號供應(yīng)商所提供的商品的平均銷售價格
SELECT AVG(pro_price) FROM product
WHERE pro_sup_id = 10
#5. 查詢product表爵卒，顯示每個供應(yīng)商所提供商品的平均價格虚缎，平均價格保留兩位小數(shù),按供應(yīng)商編號降序排列
SELECT CAST(AVG(pro_price) AS DECIMAL(18,2)) FROM product
GROUP BY pro_sup_id
ORDER BY pro_sup_id DESC
#6. 查詢supplier表，顯示在廣東的供應(yīng)商信息
SELECT * FROM supplier
WHERE sup_loc LIKE '廣東%'
#7. 查詢salesman表钓株，顯示領(lǐng)導(dǎo)編號為空的銷售人員信息
SELECT * FROM salesman
WHERE sm_mgr IS NULL
#8. 查詢product表和supplier表实牡，顯示‘mate7’的供應(yīng)商信息
SELECT sup_id,sup_name,sup_loc FROM supplier LEFT JOIN product
ON pro_sup_id = sup_id
WHERE pro_name = 'mate7'
#9. 查詢salesman表，顯示每個銷售人員及其領(lǐng)導(dǎo)的姓名? ? ? 虛表
SELECT a.sm_name salesman,b.sm_name mgr FROM salesman a,salesman b
WHERE a.sm_mgr = b.sm_id
#10. 查詢salegrade表轴合，顯示2015年1月16日的銷售總額
SELECT SUM(sg_grade) FROM salegrade
WHERE sg_date = '2015-01-16'
#11. 查詢salegrade表创坞，顯示2015年1月16日的銷售額最高的銷售人員信息（使用分頁子查詢）
SELECT m.sm_id,m.sm_job,m.sm_mjr,m.sm_name,m.sm_phno,m.sm_sex
FROM salesgrade s,salesman m
WHERE s.sm_id= m.sm_id
AND m.sm_id IN (
SELECT DISTINCT sm_id FROM salesgrade WHERE sg_grade =
(SELECT MAX(sg_grade) FROM salesgrade WHERE sg_date ='2015-1-06')
)
#12. 查詢salegrade表，顯示2015年1月16日‘iPhone6’的銷售數(shù)量
SELECT * FROM product
SELECT * FROM salegrade
SELECT * FROM salesman
SELECT * FROM supplier
SELECT? SUM(a.sg_grade)/b.pro_price num FROM salegrade a,product b
WHERE a.pro_id = b.pro_id
AND b.pro_name = 'iPhone6'
AND a.sg_date = '2015-01-16'
#13. 查詢salegrade表和salesman表受葛，顯示‘SMITH’在2015年1月的銷售總額
SELECT SUM(a.sg_grade) FROM salegrade a,salesman b
WHERE a.sm_id = b.sm_id
AND b.sm_name = 'SMITH'
AND a.sg_date LIKE '2015-01-%'
#14. 查詢product表和supplier表题涨，顯示每家供應(yīng)商的名稱偎谁，提供的商品數(shù)量，未提供商品的供應(yīng)商也需要顯示（數(shù)量顯示為0）
SELECT * FROM product
SELECT * FROM supplier
SELECT b.sup_name,IFNULL(SUM(a.pro_num),0) FROM product a RIGHT JOIN supplier b
ON a.pro_sup_id = b.sup_id
GROUP BY b.sup_name
#15. 查詢salegrade表纲堵，product表巡雨，顯示2015年1月16日各型號商品的銷售數(shù)量
SELECT * FROM product
SELECT * FROM salegrade
SELECT a.pro_name,SUM(b.sg_grade/a.pro_price) FROM product a,salegrade b
WHERE a.pro_id = b.pro_id
AND b.sg_date = '2015-01-16'
GROUP BY a.pro_name
------------------------------------------------------------------
#創(chuàng)建supplier表
CREATE TABLE supplier (
sup_id INT(2) PRIMARY KEY,
sup_name VARCHAR(20) NOT NULL,
sup_loc VARCHAR(40)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT INTO supplier(sup_id,sup_name,sup_loc)
VALUES (10,'蘋果','美國加利福尼亞'),
(20,'華為','廣東深圳'),
(30,'小米','北京'),
(40,'魅族','廣東珠海'),
(50,'vivo','四川成都'),
(60,'聯(lián)想','北京'),
(70,'HTC','臺灣桃源')
SELECT * FROM supplier
#創(chuàng)建product表
CREATE TABLE product (
PRO_ID INT(4) PRIMARY KEY,
PRO_NAME VARCHAR(14) NOT NULL,
PRO_SUP_ID INT(2),FOREIGN KEY(PRO_SUP_ID) REFERENCES supplier(sup_id),
PRO_NUM INT(4),
PRO_PRICE FLOAT(7,2)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
SELECT * FROM product
INSERT INTO product(PRO_ID,PRO_NAME,PRO_SUP_ID,PRO_NUM,PRO_PRICE)
VALUES (1001,'iPhone6 Plus',10,120,6000.00),
(1002,'iPhone6',10,120,5000.00),
(1003,'mate7',20,100,4500.00),
(1004,'榮耀6 Plus',20,200,1999.00),
(1005,'小米NOTE',30,50,1900.00),
(1006,'榮耀6',20,150,1499.00),
(1007,'iPhone5s',10,150,3500.00),
(1008,'小米4',30,100,1499.00),
(1009,'MX4',40,50,1699.00),
(1010,'MX4 PRO',40,120,2199.00)
#創(chuàng)建salesman表? ? ENUM('M','F')COLLATE utf8_estonian_ci DEFAULT NULL,? ? ? ? DROP TABLE salesman
CREATE TABLE salesman (
sm_id CHAR(8) PRIMARY KEY,
sm_name VARCHAR(20) NOT NULL,
sm_job VARCHAR(14),
sm_mgr CHAR(10),
sm_sex CHAR(1) DEFAULT 'M' CHECK (sm_sex IN ('M','F')),
sm_phno CHAR(11)UNIQUE KEY
)ENGINE=INNODB DEFAULT CHARSET=utf8;
SELECT * FROM salesman
DROP TABLE? salesman
INSERT INTO salesman(sm_id,sm_name,sm_job,sm_mgr)
VALUES ('20151051','SMITH','SALESMAN','20141030')
INSERT INTO salesman(sm_id,sm_name,sm_job,sm_mgr,sm_sex,sm_phno)
VALUES ('20151038','SCOTT','SALESMAN','20141030','F','13512345678'),
('20141030','JONES','MANAGER','20141001','M','13612345678'),
('20151041','VICKY','SALESMAN','20151031','F','15812345678'),
('20151031','SWIFT','MANAGER','20141001','M','13912345678'),
('20141001','MILLER','DIRECTOR',NULL,'M','17745678923')
#創(chuàng)建salesgrade 表?
CREATE TABLE salegrade? (
sm_id INT(4),
pro_id INT(4),
sg_date DATE,
sg_grade INT(7)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
SELECT * FROM salegrade
DELETE? FROM salegrade
DROP TABLE salegrade
INSERT INTO salegrade(sm_id,pro_id,sg_date,sg_grade)
VALUES (20151051,1001,'2015/1/16',60000),
(20151051,1002,'2015/1/16',30000),
(20151038,1003,'2015/1/16',13500),
(20151038,1004,'2015/1/16',19990),
(20151038,1006,'2015/1/16',2998),
(20151030,1005,'2015/1/16',1900),
(20151030,1008,'2015/1/16',8994),
(20151011,1001,'2015/1/16',72000),
(20151011,1007,'2015/1/16',70000),
(20151011,1002,'2015/1/16',10000),
(20151035,1009,'2015/1/16',11893),
(20151051,1001,'2015/1/20',60000),
(20151051,1001,'2015/1/20',60000),
(20151051,1002,'2015/1/20',30000),
(20151038,1003,'2015/1/20',13500),
(20151038,1004,'2015/1/20',19990),
(20151038,1006,'2015/1/20',2998),
(20151030,1005,'2015/1/20',1900),
(20151030,1008,'2015/1/20',8994),
(20151011,1001,'2015/1/20',72000),
(20151011,1007,'2015/1/20',70000),
(20151011,1002,'2015/1/20',10000),
(20151035,1009,'2015/1/20',11893)
----------------------------------------------------------------
