MySQL創(chuàng)建存儲過程
格式
MySQL存儲過程創(chuàng)建的格式：CREATE PROCEDURE 過程名 ([過程參數(shù)[,...]])
[特性 ...] 過程體
這里先舉個例子：
1.mysql>?DELIMITER?// ?
2.mysql>?CREATE?PROCEDURE?proc1(OUT?s?int) ?
3.????->?BEGIN?
4.????->?SELECT?COUNT(*)?INTO?s?FROM?user; ?
5.????->?END ;?
6.????->?// ?
7.Query OK, 0 rows affected (0.01 sec)
8.mysql>?DELIMITER?;?
注：
1、這里需要注意的是DELIMITER //和DELIMITER ;兩句，DELIMITER是分割符的意思，因為MySQL默認以";"為分隔符，如果我們沒有聲明分割符，那么編譯器會把存儲過程當成SQL語句進行處理，則存儲過程的編譯過程會報錯，所以要事先用DELIMITER關(guān)鍵字申明當前段分隔符，這樣MySQL才會將";"當做存儲過程中的代碼，不會執(zhí)行這些代碼，用完了之后要把分隔符還原。
例如:MySql默認的情況下以分號作為語句的結(jié)束,輸入SQL語句,在末尾加上分號,這樣MySql會執(zhí)行這行代碼。
我們可以改變MySql的分隔符,把分號改成 // ,輸入語句mysql> delimiter //。
這時候MySql的結(jié)束語句分隔符就改變成了// ,原來的分號就不起作用了，在結(jié)束語句的時候需要加上 //,這樣SQL語句才會執(zhí)行。
1.mysql> select * from emp//
2.+-------+--------+-----------+------+------------+---------+---------+--------+
3.| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
4.+-------+--------+-----------+------+------------+---------+---------+--------+
5.| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
6.| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
7.| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
8.| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
9.| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
10.| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
11.| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
12.| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
13.| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
14.| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
15.| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
16.| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
17.| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
18.| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
19.+-------+--------+-----------+------+------------+---------+---------+--------+
20.14 rows in set (0.00 sec)
最后,我們把MySql的結(jié)束分隔符恢復(fù)成 ; ,這樣的話,在結(jié)束語句的末尾還是用分號結(jié)束,SQL語句才會被執(zhí)行。
1.mysql> delimiter ;
2.mysql> select * from emp;
3.+-------+--------+-----------+------+------------+---------+---------+--------+
4.| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
5.+-------+--------+-----------+------+------------+---------+---------+--------+
6.| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
7.| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
8.| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
9.| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
10.| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
11.| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
12.| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
13.| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
14.| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
15.| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
16.| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
17.| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
18.| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
19.| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
20.+-------+--------+-----------+------+------------+---------+---------+--------+
21.14 rows in set (0.00 sec)
2、存儲過程根據(jù)需要可能會有輸入、輸出、輸入輸出參數(shù)，這里有一個輸出參數(shù)s，類型是int型，如果有多個參數(shù)用","分割開。
3、過程體的開始與結(jié)束使用BEGIN與END進行標識。
這樣，我們的一個MySQL存儲過程就完成了，是不是很容易呢?看不懂也沒關(guān)系，接下來，我們詳細的講解。
聲明分割符
其實，關(guān)于聲明分割符，上面的注解已經(jīng)寫得很清楚，不需要多說，只是稍微要注意一點的是：如果是用MySQL的Administrator管理工具時,例如NaviCat for MySql，可以直接創(chuàng)建，不再需要聲明。
NaviCat for MySql工具的使用:我們下載的是綠色版,解壓縮之后,輸入注冊碼就可直接使用,非常方便,使用界面如下:
我們需要建立和數(shù)據(jù)庫服務(wù)器的連接.點擊 文件->新建連接,填入數(shù)據(jù)庫的相關(guān)信息之后,點擊確定按鈕。
建立好的連接如下,雙擊本機,打開連接,我們可以直接看到MySql中的數(shù)據(jù)庫。
雙擊我們要使用的數(shù)據(jù)庫,然后點擊查詢->新建查詢,彈出窗口,我們可以在這個創(chuàng)建中編輯SQL語句,非常方便。
在編輯窗口中編輯SQL語句,然后選中該語句,點擊右鍵->運行已選擇的,即可執(zhí)行選中的SQL語句,并且顯示查詢結(jié)果。
在NaviCat中編輯存儲過程非常方便,不需要在聲明分隔符,直接編輯代碼,然后選中代碼 ,即可執(zhí)行,是不是被命令行客戶端爽多了。
參數(shù)
MySQL存儲過程的參數(shù)用在存儲過程的定義，共有三種參數(shù)類型,IN,OUT,INOUT,形式如：
CREATE PROCEDURE([[IN |OUT |INOUT ] 參數(shù)名 數(shù)據(jù)類形...])。
IN 輸入?yún)?shù):表示該參數(shù)的值必須在調(diào)用存儲過程時指定，在存儲過程中修改該參數(shù)的值不能被返回，為默認值。
OUT 輸出參數(shù):該值可在存儲過程內(nèi)部被改變，并可返回。
INOUT 輸入輸出參數(shù):調(diào)用時指定，并且可被改變和返回。
1、IN參數(shù)例子
在NaviCat中創(chuàng)建存儲過程:
CREATE PROCEDURE PROC2(P_IN INT)
BEGIN
SELECT P_IN; -- 查詢P_IN參數(shù)的值
SET P_IN = 3; -- 設(shè)置P_IN參數(shù)的值
SELECT P_IN ; -- 查詢P_IN參數(shù)的值
END;
語句執(zhí)行的過程如下圖:
調(diào)用存儲過程,查詢結(jié)果：
SET @P_IN = 10; -- 設(shè)置@P_IN =10
CALL PROC2(@P_IN); -- 調(diào)用存儲過程,并且傳遞@P_IN的參數(shù)
SELECT @P_IN; -- 查詢@P_IN的結(jié)果
語句的執(zhí)行過程如下圖所示：
以上可以看出，p_in雖然在存儲過程中被修改，但并不影響@p_id的值。
2、OUT參數(shù)例子
創(chuàng)建:
CREATE PROCEDURE PROC3(OUT P_OUT INT)
BEGIN
SELECT P_OUT;
SET P_OUT = 10;
SELECT P_OUT;
END;?
執(zhí)行過程如下:
調(diào)用存儲過程,查詢結(jié)果
SET @P_OUT = 1000; -- 設(shè)置@P_OUT變量為1000
CALL PROC3(@P_OUT); -- 調(diào)用存儲過程并且傳遞@P_OUT參數(shù)
SELECT @P_OUT; -- 查詢P_OUT的結(jié)構(gòu)
執(zhí)行結(jié)果如下:
3、 INOUT參數(shù)例子
創(chuàng)建:
-- 注意:INOUT之間沒有空格
CREATE PROCEDURE PROC4(INOUT P_IN_OUT INT) BEGIN
SELECT P_IN_OUT;
SET P_IN_OUT = 10;
SELECT P_IN_OUT;
END;
執(zhí)行結(jié)果如下:
調(diào)用存儲過程:
SET @P_IN_OUT = 50;
CALL PROC3(@P_IN_OUT);
SELECT @P_IN_OUT;
執(zhí)行結(jié)果如下:
變量
1、變量定義
DECLARE variable_name [,variable_name...] datatype [DEFAULT value]；其中，datatype為MySQL的數(shù)據(jù)類型，如:int, float, date, varchar(length)
例如:
CREATE PROCEDURE PROC4()
BEGIN
-- 聲明變量.注意:聲明變量的語句只能寫在BEGIN END之間
DECLARE SCHOOL VARCHAR(100) DEFAULT "動力節(jié)點";
DECLARE L_INT INT DEFAULT 40;
DECLARE L_NUMERIC DOUBLE(8,2) DEFAULT 9.95;
DECLARE L_DATE DATE DEFAULT '1999-12-31';
DECLARE L_DATETIME DATETIME DEFAULT '1999-12-31 23:59:59';
DECLARE L_VARCHAR VARCHAR(255) DEFAULT 'THIS WILL NOT BE PADDED';
SELECT SCHOOL ;-- 查詢變量的值
SET SCHOOL = "動力節(jié)點北京北部校區(qū)",L_INT =100;-- 給變量重新賦值
SELECT SCHOOL;-- 再次查詢變量
END;?
2、變量賦值
SET 變量名 = 表達式值 [,variable_name = expression ...]
3、 用戶變量
（1）在MySQL客戶端使用用戶變量
SELECT "Hello World" INTO @S;
SELECT @S;
SET @S = "Hello World";
SELECT @S;
SET @RES = 1+2+3;
SELECT @RES;
（2）在存儲過程中使用用戶變量
-- 聲明存儲過程
CREATE PROCEDURE PROC5()
BEGIN
SELECT CONCAT(@STR1,"World");
END;
-- 首先定義一個變量,然后調(diào)用存儲過程
SET @STR1 = "Hello";
CALL PROC5();
執(zhí)行效果如下
（3）在存儲過程間傳遞全局范圍的用戶變量
-- 聲明存儲過程P1,在P1中定義變量@LAST_PROC;
CREATE PROCEDURE P1()
BEGIN
SET @LAST_PROC = "P1";
END;
-- 聲明存儲過程P2,在P2中使用P1中定義的變量@LAST_PROC
CREATE PROCEDURE P2()
BEGIN
SELECT CONCAT("最后一個存儲過程是",@LAST_PROC);
END;
-- 調(diào)用存儲過程P1(),P2();
CALL P1();
CALL P2();?
執(zhí)行結(jié)果如下:
注意：用戶變量名一般以@開頭
注釋
MySQL存儲過程可使用兩種風(fēng)格的注釋
雙模杠：--
該風(fēng)格一般用于單行注釋
c風(fēng)格： /*一般用于多行注釋*/
