mysql存儲進程詳解。本站提示廣大學習愛好者：（mysql存儲進程詳解）文章只能為提供參考，不一定能成為您想要的結果。以下是mysql存儲進程詳解正文
1.存儲進程簡介
我們經常使用的操作數據庫說話SQL語句在履行的時刻須要要先編譯，然後履行，而存儲進程（Stored Procedure）是一組為了完成特定功效的SQL語句集，經編譯後存儲在數據庫中，用戶經由過程指定存儲進程的名字並給定參數（假如該存儲進程帶有參數）來挪用履行它。
一個存儲進程是一個可編程的函數，它在數據庫中創立並保留。它可以有SQL語句和一些特別的掌握構造構成。當願望在分歧的運用法式或平台上履行雷同的函數，或許封裝特定功效時，存儲進程長短常有效的。數據庫中的存儲進程可以看作是對編程中面向對象辦法的模仿。它許可掌握數據的拜訪方法。
存儲進程平日有以下長處：
(1).存儲進程加強了SQL說話的功效和靈巧性。存儲進程可以用流掌握語句編寫，有很強的靈巧性，可以完成龐雜的斷定和較龐雜的運算。
(2).存儲進程許可尺度組件是編程。存儲進程被創立後，可以在法式中被屢次挪用，而不用從新編寫該存儲進程的SQL語句。並且數據庫專業人員可以隨時對存儲進程停止修正，對運用法式源代碼毫無影響。
(3).存儲進程能完成較快的履行速度。假如某一操作包括年夜量的Transaction-SQL代碼或分離被屢次履行，那末存儲進程要比批處置的履行速度快許多。由於存儲進程是預編譯的。在初次運轉一個存儲進程時查詢，優化器對其停止剖析優化，而且給出終究被存儲在體系表中的履行籌劃。而批處置的Transaction-SQL語句在每次運轉時都要停止編譯和優化，速度絕對要慢一些。
(4).存儲進程能過削減收集流量。針對統一個數據庫對象的操作（如查詢、修正），假如這一操作所觸及的Transaction-SQL語句被組織程存儲進程，那末當在客戶盤算機上挪用該存儲進程時，收集中傳送的只是該挪用語句，從而年夜年夜增長了收集流量並下降了收集負載。
(5).存儲進程可被作為一種平安機制來充足應用。體系治理員經由過程履行某一存儲進程的權限停止限制，可以或許完成對響應的數據的拜訪權限的限制，防止了非受權用戶對數據的拜訪，包管了數據的平安。
2. 關於MySQL的存儲進程
存儲進程是數據庫存儲的一個主要的功效，然則MySQL在5.0之前其實不支撐存儲進程，這使得MySQL在運用上年夜打扣頭。好在MySQL 5.0終究開端曾經支撐存儲進程，如許便可以年夜年夜進步數據庫的處置速度，同時也能夠進步數據庫編程的靈巧性。
3. MySQL存儲進程的創立
(1). 格局
MySQL存儲進程創立的格局：CREATE PROCEDURE 進程名 ([進程參數[,...]])
[特征 ...] 進程體
這裡先舉個例子：
mysql> DELIMITER //
mysql> CREATE PROCEDURE proc1(OUT s int)
-> BEGIN
-> SELECT COUNT(*) INTO s FROM user;
-> END
-> //
mysql> DELIMITER ;
注：
（1）這裡須要留意的是DELIMITER //和DELIMITER ;兩句，DELIMITER是朋分符的意思，由於MySQL默許以";"為分隔符，假如我們沒有聲明朋分符，那末編譯器會把存儲進程當做SQL語句停止處置，則存儲進程的編譯進程會報錯，所以要事前用DELIMITER症結字聲名以後段分隔符，如許MySQL才會將";"當作存儲進程中的代碼，不會履行這些代碼，用完了以後要把分隔符復原。
（2）存儲進程依據須要能夠會有輸出、輸入、輸出輸入參數，這裡有一個輸入參數s，類型是int型，假如有多個參數用","朋分開。
（3）進程體的開端與停止應用BEGIN與END停止標識。
如許，我們的一個MySQL存儲進程就完成了，是否是很輕易呢?看不懂也沒緊要，接上去，我們具體的講授。
(2). 聲明朋分符
其實，關於聲明朋分符，下面的注解曾經寫得很清晰，不須要多說，只是略微要留意一點的是：假如是用MySQL的Administrator治理對象時，可以直接創立，不再須要聲明。
(3). 參數
MySQL存儲進程的參數用在存儲進程的界說，共有三種參數類型,IN,OUT,INOUT,情勢如：
CREATE PROCEDURE([[IN |OUT |INOUT ] 參數名 數據類形...])
IN 輸出參數:表現該參數的值必需在挪用存儲進程時指定，在存儲進程中修正該參數的值不克不及被前往，為默許值
OUT 輸入參數:該值可在存儲進程外部被轉變，並可前往
INOUT 輸出輸入參數:挪用時指定，而且可被轉變和前往
Ⅰ. IN參數例子
創立:
mysql > DELIMITER //
mysql > CREATE PROCEDURE demo_in_parameter(IN p_in int)
-> BEGIN
-> SELECT p_in;
-> SET p_in=2;
-> SELECT p_in;
-> END;
-> //
mysql > DELIMITER ;
履行成果:
mysql > SET @p_in=1;
mysql > CALL demo_in_parameter(@p_in);
+------+
| p_in |
+------+
| 1 |
+------+
+------+
| p_in |
+------+
| 2 |
+------+
mysql> SELECT @p_in;
+-------+
| @p_in |
+-------+
| 1 |
+-------+
以上可以看出，p_in固然在存儲進程中被修正，但其實不影響@p_id的值
Ⅱ.OUT參數例子
創立:
mysql > DELIMITER //
mysql > CREATE PROCEDURE demo_out_parameter(OUT p_out int)
-> BEGIN
-> SELECT p_out;
-> SET p_out=2;
-> SELECT p_out;
-> END;
-> //
mysql > DELIMITER ;
履行成果:
mysql > SET @p_out=1;
mysql > CALL sp_demo_out_parameter(@p_out);
+-------+
| p_out |
+-------+
| NULL |
+-------+
+-------+
| p_out |
+-------+
| 2 |
+-------+
mysql> SELECT @p_out;
+-------+
| p_out |
+-------+
| 2 |
+-------+
Ⅲ. INOUT參數例子
創立:
mysql > DELIMITER //
mysql > CREATE PROCEDURE demo_inout_parameter(INOUT p_inout int)
-> BEGIN
-> SELECT p_inout;
-> SET p_inout=2;
-> SELECT p_inout;
-> END;
-> //
mysql > DELIMITER ;
履行成果:
mysql > SET @p_inout=1;
mysql > CALL demo_inout_parameter(@p_inout) ;
+---------+
| p_inout |
+---------+
| 1 |
+---------+
+---------+
| p_inout |
+---------+
| 2 |
+---------+
mysql > SELECT @p_inout;
+----------+
| @p_inout |
+----------+
| 2 |
+----------+
(4). 變量
Ⅰ. 變量界說
DECLARE variable_name [,variable_name...] datatype [DEFAULT value];
個中，datatype為MySQL的數據類型，如:int, float, date, varchar(length)
例如:
DECLARE l_int int unsigned default 4000000;
DECLARE l_numeric number(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';
Ⅱ. 變量賦值
SET 變量名 = 表達式值 [,variable_name = expression ...]
Ⅲ. 用戶變量
ⅰ. 在MySQL客戶端應用用戶變量
mysql > SELECT 'Hello World' into @x;
mysql > SELECT @x;
+-------------+
| @x |
+-------------+
| Hello World |
+-------------+
mysql > SET @y='Goodbye Cruel World';
mysql > SELECT @y;
+---------------------+
| @y |
+---------------------+
| Goodbye Cruel World |
+---------------------+
mysql > SET @z=1+2+3;
mysql > SELECT @z;
+------+
| @z |
+------+
| 6 |
+------+
ⅱ. 在存儲進程中應用用戶變量
mysql > CREATE PROCEDURE GreetWorld( ) SELECT CONCAT(@greeting,' World');
mysql > SET @greeting='Hello';
mysql > CALL GreetWorld( );
+----------------------------+
| CONCAT(@greeting,' World') |
+----------------------------+
| Hello World |
+----------------------------+
ⅲ. 在存儲進程間傳遞全局規模的用戶變量
mysql> CREATE PROCEDURE p1() SET @last_procedure='p1';
mysql> CREATE PROCEDURE p2() SELECT CONCAT('Last procedure was ',@last_proc);
mysql> CALL p1( );
mysql> CALL p2( );
+-----------------------------------------------+
| CONCAT('Last procedure was ',@last_proc |
+-----------------------------------------------+
| Last procedure was p1 |
+-----------------------------------------------+
留意:
①用戶變量名普通以@開首
②濫用用戶變量會招致法式難以懂得及治理
(5). 正文
MySQL存儲進程可以使用兩種作風的正文
雙模槓：--
該作風普通用於單行正文
c作風： 普通用於多行正文
例如：
mysql > DELIMITER //
mysql > CREATE PROCEDURE proc1 --name存儲進程名
-> (IN parameter1 INTEGER)
-> BEGIN
-> DECLARE variable1 CHAR(10);
-> IF parameter1 = 17 THEN
-> SET variable1 = 'birds';
-> ELSE
-> SET variable1 = 'beasts';
-> END IF;
-> INSERT INTO table1 VALUES (variable1);
-> END
-> //
mysql > DELIMITER ;
4. MySQL存儲進程的挪用
用call和你進程名和一個括號，括號外面依據須要，參加參數，參數包含輸出參數、輸入參數、輸出輸入參數。詳細的挪用辦法可以參看下面的例子。
5. MySQL存儲進程的查詢
我們像曉得一個數據庫上面有那些表，我們普通采取show tables;停止檢查。那末我們要檢查某個數據庫上面的存儲進程，能否也能夠采取呢？謎底是，我們可以檢查某個數據庫上面的存儲進程，然則是令一鐘方法。
我們可以用
select name from mysql.proc where db='數據庫名';
或許
select routine_name from information_schema.routines where routine_schema='數據庫名';
或許
show procedure status where db='數據庫名';
停止查詢。
假如我們想曉得，某個存儲進程的具體，那我們又該怎樣做呢？是否是也能夠像操作表一樣用describe 表名停止檢查呢？
謎底是：我們可以檢查存儲進程的具體，然則須要用另外一種辦法：
SHOW CREATE PROCEDURE 數據庫.存儲進程名;
便可以檢查以後存儲進程的具體。
6.MySQL存儲進程的修正
ALTER PROCEDURE
更改用CREATE PROCEDURE 樹立的事後指定的存儲進程，其不會影響相干存儲進程或存儲功效。
7. MySQL存儲進程的刪除
刪除一個存儲進程比擬簡略，和刪除表一樣：
DROP PROCEDURE
從MySQL的表格中刪除一個或多個存儲進程。
8. MySQL存儲進程的掌握語句
(1). 變量感化域
外部的變量在其感化域規模內享有更高的優先權，當履行到end。變量時，外部變量消逝，此時曾經在其感化域外，變量不再可見了，應為在存儲
進程外不再能找到這個聲名的變量，然則你可以經由過程out參數或許將其值指派
給會話變量來保留其值。
mysql > DELIMITER //
mysql > CREATE PROCEDURE proc3()
-> begin
-> declare x1 varchar(5) default 'outer';
-> begin
-> declare x1 varchar(5) default 'inner';
-> select x1;
-> end;
-> select x1;
-> end;
-> //
mysql > DELIMITER ;
(2). 前提語句
Ⅰ. if-then -else語句
mysql > DELIMITER //
mysql > CREATE PROCEDURE proc2(IN parameter int)
-> begin
-> declare var int;
-> set var=parameter+1;
-> if var=0 then
-> insert into t values(17);
-> end if;
-> if parameter=0 then
-> update t set s1=s1+1;
-> else
-> update t set s1=s1+2;
-> end if;
-> end;
-> //
mysql > DELIMITER ;
Ⅱ. case語句：
mysql > DELIMITER //
mysql > CREATE PROCEDURE proc3 (in parameter int)
-> begin
-> declare var int;
-> set var=parameter+1;
-> case var
-> when 0 then
-> insert into t values(17);
-> when 1 then
-> insert into t values(18);
-> else
-> insert into t values(19);
-> end case;
-> end;
-> //
mysql > DELIMITER ;
(3). 輪回語句
Ⅰ. while ···· end while：
mysql > DELIMITER //
mysql > CREATE PROCEDURE proc4()
-> begin
-> declare var int;
-> set var=0;
-> while var<6 do
-> insert into t values(var);
-> set var=var+1;
-> end while;
-> end;
-> //
mysql > DELIMITER ;
Ⅱ. repeat···· end repeat：
它在履行操作後檢討成果，而while則是履行進步行檢討。
mysql > DELIMITER //
mysql > CREATE PROCEDURE proc5 ()
-> begin
-> declare v int;
-> set v=0;
-> repeat
-> insert into t values(v);
-> set v=v+1;
-> until v>=5
-> end repeat;
-> end;
-> //
mysql > DELIMITER ;
Ⅲ. loop ·····end loop:
loop輪回不須要初始前提，這點和while 輪回類似，同時和repeat輪回一樣不須要停止前提, leave語句的意義是分開輪回。
mysql > DELIMITER //
mysql > CREATE PROCEDURE proc6 ()
-> begin
-> declare v int;
-> set v=0;
-> LOOP_LABLE:loop
-> insert into t values(v);
-> set v=v+1;
-> if v >=5 then
-> leave LOOP_LABLE;
-> end if;
-> end loop;
-> end;
-> //
mysql > DELIMITER ;
Ⅳ. LABLES 標號：
標號可以用在begin repeat while 或許loop 語句前，語句標號只能在正當的語句後面應用。可以跳出輪回，使運轉指令到達復合語句的最初一步。
(4). ITERATE迭代
Ⅰ. ITERATE:
經由過程援用復合語句的標號,來重新開端復合語句
mysql > DELIMITER //
mysql > CREATE PROCEDURE proc10 ()
-> begin
-> declare v int;
-> set v=0;
-> LOOP_LABLE:loop
-> if v=3 then
-> set v=v+1;
-> ITERATE LOOP_LABLE;
-> end if;
-> insert into t values(v);
-> set v=v+1;
-> if v>=5 then
-> leave LOOP_LABLE;
-> end if;
-> end loop;
-> end;
-> //
mysql > DELIMITER ;
9.MySQL存儲進程的根本函數
(1).字符串類
CHARSET(str) //前往字串字符集
CONCAT (string2 [,... ]) //銜接字串
INSTR (string ,substring ) //前往substring初次在string中湧現的地位,不存在前往0
LCASE (string2 ) //轉換成小寫
LEFT (string2 ,length ) //從string2中的右邊起取length個字符
LENGTH (string ) //string長度
LOAD_FILE (file_name ) //從文件讀取內容
LOCATE (substring , string [,start_position ] ) 同INSTR,但可指定開端地位
LPAD (string2 ,length ,pad ) //反復用pad加在string開首,直到字串長度為length
LTRIM (string2 ) //去除前端空格
REPEAT (string2 ,count ) //反復count次
REPLACE (str ,search_str ,replace_str ) //在str頂用replace_str調換search_str
RPAD (string2 ,length ,pad) //在str後用pad彌補,直到長度為length
RTRIM (string2 ) //去除後端空格
STRCMP (string1 ,string2 ) //逐字符比擬兩字串年夜小,
SUBSTRING (str , position [,length ]) //從str的position開端,取length個字符,
注：mysql中處置字符串時，默許第一個字符下標為1，即參數position必需年夜於等於1
mysql> select substring('abcd',0,2);
+-----------------------+
| substring('abcd',0,2) |
+-----------------------+
+-----------------------+
1 row in set (0.00 sec)
mysql> select substring('abcd',1,2);
+-----------------------+
| substring('abcd',1,2) |
+-----------------------+
| ab |
+-----------------------+
1 row in set (0.02 sec)
TRIM([[BOTH|LEADING|TRAILING] [padding] FROM]string2) //去除指定地位的指定字符
UCASE (string2 ) //轉換成年夜寫
RIGHT(string2,length) //取string2最初length個字符
SPACE(count) //生成count個空格
(2).數學類
ABS (number2 ) //相對值
BIN (decimal_number ) //十進制轉二進制
CEILING (number2 ) //向上取整
CONV(number2,from_base,to_base) //進制轉換
FLOOR (number2 ) //向下取整
FORMAT (number,decimal_places ) //保存小數位數
HEX (DecimalNumber ) //轉十六進制
注：HEX()中可傳入字符串，則前往其ASC-11碼，如HEX('DEF')前往4142143
也能夠傳入十進制整數，前往其十六進制編碼，如HEX(25)前往19
LEAST (number , number2 [,..]) //求最小值
MOD (numerator ,denominator ) //求余
POWER (number ,power ) //求指數
RAND([seed]) //隨機數
ROUND (number [,decimals ]) //四捨五入,decimals為小數位數]
注：前往類型並不是均為整數，如：
(1)默許變成整形值
mysql> select round(1.23);
+-------------+
| round(1.23) |
+-------------+
| 1 |
+-------------+
1 row in set (0.00 sec)
mysql> select round(1.56);
+-------------+
| round(1.56) |
+-------------+
| 2 |
+-------------+
1 row in set (0.00 sec)
(2)可以設定小數位數，前往浮點型數據
mysql> select round(1.567,2);
+----------------+
| round(1.567,2) |
+----------------+
| 1.57 |
+----------------+
1 row in set (0.00 sec)
SIGN (number2 ) //
(3).日期時光類
ADDTIME (date2 ,time_interval ) //將time_interval加到date2
CONVERT_TZ (datetime2 ,fromTZ ,toTZ ) //轉換時區
CURRENT_DATE ( ) //以後日期
CURRENT_TIME ( ) //以後時光
CURRENT_TIMESTAMP ( ) //以後時光戳
DATE (datetime ) //前往datetime的日期部門
DATE_ADD (date2 , INTERVAL d_value d_type ) //在date2中加上日期或時光
DATE_FORMAT (datetime ,FormatCodes ) //應用formatcodes格局顯示datetime
DATE_SUB (date2 , INTERVAL d_value d_type ) //在date2上減去一個時光
DATEDIFF (date1 ,date2 ) //兩個日期差
DAY (date ) //前往日期的天
DAYNAME (date ) //英文禮拜
DAYOFWEEK (date ) //禮拜(1-7) ,1為禮拜天
DAYOFYEAR (date ) //一年中的第幾天
EXTRACT (interval_name FROM date ) //從date中提取日期的指定部門
MAKEDATE (year ,day ) //給出年及年中的第幾天,生成日期串
MAKETIME (hour ,minute ,second ) //生成時光串
MONTHNAME (date ) //英文月份名
NOW ( ) //以後時光
SEC_TO_TIME (seconds ) //秒數轉成時光
STR_TO_DATE (string ,format ) //字串轉成時光,以format格局顯示
TIMEDIFF (datetime1 ,datetime2 ) //兩個時光差
TIME_TO_SEC (time ) //時光轉秒數]
WEEK (date_time [,start_of_week ]) //第幾周
YEAR (datetime ) //年份
DAYOFMONTH(datetime) //月的第幾天
HOUR(datetime) //小時
