mysql存儲過程詳細講解及完整實例下載

知識 08-14

一、存儲過程概念

1.存儲過程（Stored Procedure）是一組為了完成特定功能的SQL語句集。經編譯後存儲在資料庫中。

2.存儲過程是資料庫中的一個重要對象，用戶通過指定存儲過程的名字並給出參數（如果該存儲過程帶有參數）來執行它。

3.存儲過程是由流控制和SQL語句書寫的過程，這個過程經編譯和優化後存儲在資料庫伺服器中。

4.存儲過程可由應用程序通過一個調用來執行，而且允許用戶聲明變數。

5.同時，存儲過程可以接收和輸出參數、返回執行存儲過程的狀態值，也可以嵌套調用。

二、存儲過程優點

1.增強了SQL語句的功能和靈活性

2不需要反覆建立一系列處理步驟，保證了數據的完整性

3.降低了網路的通信量，客戶端調用存儲過程只需要傳存儲過程名和相關參數即可，與傳輸SQL語句相比自然數據量少了很多

4.增強了使用的安全性，通過存儲過程可以使沒有許可權的用戶在控制之下間接地存取資料庫，從而保證數據的安全。

5.可以實現集中控制，當規則發生改變時，只需要修改存儲過程就可以。。、

三、存儲過程缺點

1.調試不是很方便。

2.可能沒有創建存儲過程的權利。

3.重新編譯問題。

4.移植性問題。

四、變數

1.用戶變數：以」@」開始，形式為」@變數名。」用戶變數跟MySQL客戶端是綁定的，設置的變數，只對當前用戶使用的客戶端生效.

2.全局變數：定義時，以如下兩種形式出現，set GLOBAL 變數名或者 set @@global.變數名。show global variables; 對所有客戶端生效。只有super許可權才可以設置全局變數。

3.會話變數：只對連接的客戶端有效。一旦客戶端失去連接，變數失效。show session variables;

4.局部變數：作用範圍在begin到end語句塊之間。

4.1在該語句塊里設置的變數declare語句專門用於定義局部變數。declare numeric number(8,2)【MySQL的數據類型，如:int,float, date, varchar(length)】 default 9.95;

4.2變數賦值:SET 變數名 = 表達式值 [,variable_name= expression ...]，set numeric=1.2或者SELECT 2.3 into @x；

五、mysql 存儲程序

1.基本語法：create procedure 過程名 ([過程參數[,...]])[特性 ...] 過程體;先看基本例子

第一種：

delimiter ;;
create procedure proc_on_insert
begin
end
;;
delimiter

第二種：

delimiter //
create procedure proc_on_insert
begin
end
//
delimiter ;;

注意：

1).這裡需要注意的是delimiter // 和delimiter ;;兩句，delimiter是分割符的意思，因為MySQL默認以";"為分隔符，如果我們沒有聲明分割符，那麼編譯器會把存儲過程當成SQL語句進行處理，則存儲過程的編譯過程會報錯，所以要事先用delimiter關鍵字申明當前段分隔符，這樣MySQL才會將";"當做存儲過程中的代碼。

2).存儲過程根據需要可能會有輸入、輸出、輸入輸出參數，這裡有一個輸出參數s，類型是int型，如果有多個參數用","分割開。

3).過程體的開始與結束使用begin與emd進行標識。

2..調用存儲過程基本語法：call sp_name

3.參數:MySQL存儲過程的參數用在存儲過程的定義，共有三種參數類型,IN,OUT,INOUT,形式如：

create procedure([[in |out |inout ] 參數名數據類形...])

in輸入參數:表示該參數的值必須在調用存儲過程時指定，在存儲過程中修改該參數的值不能被返回，為默認值

out 輸出參數:該值可在存儲過程內部被改變，並可返回

inout 輸入輸出參數:調用時指定，並且可被改變和返回

3.1in參數例子：

drop procedure if exists prc_on_in;
delimiter ;;
create procedure prc_on_in(in num int)
begin
declare number int ;
set number=num;
select number;
end
;;
delimiter ;;
set @num=1;
call prc_on_in(@num);

3.2out參數創建例子

drop procedure if exists prc_on_out;
delimiter ;;
create procedure prc_on_out(out out_num int)
begin
select out_num;
set out_num=78;
select out_num;
end
;;
delimiter ;;
set @number=6;
call prc_on_out(@number);

3.3inout參數創建例子

drop procedure if exists prc_on_inout;
delimiter ;;
create procedure prc_on_inout(inout p_inout int)
begin
select p_inout;
set p_inout=100;
select p_inout;
end
;;
delimiter ;;
set @p_out=90;
call prc_on_inout(@p_out);

3.4存儲過程中的IF語句（if then elseif then else end if）

drop procedure if exists p_else;
create procedure p_else(in id int)
begin
if (id > 0) then
select "> 0" as id;
elseif (id = 0) then
select "= 0" as id;
else
select "< 0" as id;
end if;
end;
set @p=-10;
call p_else(@p);

3.5存儲過程中的case when then

drop procedure if exists p_case;
delimiter ;;
create procedure p_case(
id int
)
begin
case id
when 1 then
select "one" as trans;
when 2 then
select "two" as trans;
when 3 then
select "three" as trans;
else
select "no trans" as trans;
end case;
end;
;;
delimiter ;;
set @id=1;
call p_case(@id);

3.6存儲過程中的while do … end while語句

drop procedure if exists p_while_do;
create procedure p_while_do
begin
declare i int;
set i = 1;
while i <= 10 do
select concat("index : ", i) ;
set i = i + 1;
end while;
end;
call p_while_do;

3.7存儲過程中的repeat … until end repeat語句

drop procedure if exists p_repeat;
delimiter ;;
create procedure p_repeat(in parameter int)
BEGIN
declare var int;
set var = parameter;
REPEAT
set var = var - 1;
set parameter = parameter -2;
UNTIL var<0
end REPEAT;
select parameter;
END
;;
delimiter ;;
set @parameter=1;
call p_repeat(@parameter);

這個REPEAT循環的功能和前面WHILE循環一樣，區別在於它的執行後檢查是否滿足循環條件（until i>=5），而WHILE則是執行前檢查（while i<5 do）。不過要注意until i>=5後面不要加分號，如果加分號，就是提示語法錯誤。

3.8存儲過程中的loop ··· end loop語句

drop procedure if exists p_loop;
delimiter;;
create procedure p_loop(in parameter int)
BEGIN
declare var int;
set var = parameter;
LOOP_LABLE:loop
set var = var - 1;
set parameter = parameter -2;
if var<0 THEN
LEAVE LOOP_LABLE;
END IF;
end LOOP;
select parameter;
END
;;
delimiter;;
set @parameter=4;
call p_loop(@parameter);

使用LOOP編寫同樣的循環控制語句要比使用while和repeat編寫的要複雜一些：在循環內部加入了IF……END IF語句，在IF語句中又加入了LEAVE語句，LEAVE語句的意思是離開循環，LEAVE的格式是：LEAVE 循環標號。

4.游標的使用 :定義游標，打開游標 ,使用游標 ,關閉游標例子

drop table if exists person;
CREATE TABLE `person` (
`id` int(11) NOT NULL DEFAULT "0",
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into person(age) value(1);
drop procedure if exists prc_test1;
delimiter ;;
create definer = root@localhost procedure prc_test1
BEGIN
declare var int;
/**跳出循環標識**/
declare done INT DEFAULT FALSE;
/**聲明游標**/
declare cur cursor for select age from person;
/**循環結束設置跳出標識**/
declare continue handler for not FOUND set done = true;
/**打開游標**/
open cur;
LOOP_LABLE:loop
FETCH cur INTO var;
select var;
if done THEN
LEAVE LOOP_LABLE;
END IF;
end LOOP;
/**關閉游標**/
CLOSE cur;
END;
;;
delimiter ;;
call prc_test1;