Mysql 存儲過程的學習筆記

存儲過程比我們普通使用的要高級一些並且mysql存儲過程性能與安全性也高一些，下面一起來看一篇Mysql 存儲過程的學習筆記吧．

技術要點

一個存儲過程包括名字、參數列表，以及可以包括很多SQL語句的SQL語句集。下面為一個存儲過程的定義過程：
create procedure proc_name (in parameter integer)begindeclare variable varchar(20);if parameter=1 thenset variable='MySQL';elseset variable='PHP';end if;insert into tb (name) values (variable);end;

MySQL中存儲過程的建立以關鍵字create procedure開始，後面緊跟存儲過程的名稱和參數。MySQL的存儲過程名稱不區分大小寫，例如PROCE1()和proce1()代表同一個存儲過程名。存儲過程名不能與MySQL數據庫中的內建函數重名。
存儲過程的參數一般由3部分組成。第一部分可以是in、out或inout。in表示向存儲過程中傳入參數；out表示向外傳出參數；inout表示定義的參數可傳入存儲過程，並可以被存儲過程修改後傳出存儲過程，存儲過程默認為傳入參數，所以參數in可以省略。第二部分為參數名。第三部分為參數的類型，該類型為MySQL數據庫中所有可用的字段類型，如果有多個參數，參數之間可以用逗號進行分割。
MySQL存儲過程的語句塊以begin開始，以end結束。語句體中可以包含變量的聲明、控制語句、SQL查詢語句等。由於存儲過程內部語句要以分號結束，所以在定義存儲過程前應將語句結束標志“;”更改為其他字符，並且該字符在存儲過程中出現的幾率也應該較低，可以用關鍵字delimiter更改。例如：
mysql>delimiter //

存儲過程創建之後，可用如下語句進行刪除，參數proc_name指存儲過程名。
drop procedure proc_name

實現過程

（1）MySQL存儲過程是在“命令提示符”下創建的，所以首先應該打開“命令提示符”窗口。
（2）進入“命令提示符”窗口後，首先應該登錄MySQL數據庫服務器，在“命令提示符”下輸入如下命令：
mysql –u用戶名 –p用戶密碼

（3）更改語句結束符號，本實例將語句結束符更改為“//”。代碼如下：
delimiter //

（4）創建存儲過程前應首先選擇某個數據庫。代碼如下：
use 數據庫名

（5）創建存儲過程。
（6）通過call語句調用存儲過程。

舉一反三

-- 建表

use test;
create table user(
id mediumint(8) unsigned not null auto_increment,
name char(15) not null default '',
pass char(32) not null default '',
note text not null,
primary key (id)
)engine=Innodb charset=utf8;

-- 示例一

delimiter //
create procedure proc_name (in parameter integer)
begin
if parameter=0 then
select * from user order by id asc;
else
select * from user order by id desc;
end if;
end;
//
delimiter ;
show warnings;
call proc_name(1);
call proc_name(0);

-- 示例二

drop procedure proc_name;
delimiter //
create procedure proc_name (in parameter integer)
begin
declare variable varchar(20);
if parameter=1 then
set variable='Windows';
else
set variable='Linux';
end if;
select parameter;
end;
//
delimiter ;
show warnings;
call proc_name(1);
call proc_name(0);

存儲過程中使用事務transaction

一.在處理事務時，使用SQLException捕獲SQL錯誤，然後處理； 按照這個推論，我們必須在MySQL存儲過程中捕獲SQL錯誤，最後判斷是回滾(ROLLBACK)還是提交(COMMIT)。 所以存儲過程為：

DELIMITER $$ 
DROP PROCEDURE IF EXISTS  test_sp1 $$ 
CREATE PROCEDURE test_sp1( ) 
    BEGIN 
    DECLARE t_error INTEGER DEFAULT 0; 
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1; 
 
        START TRANSACTION; 
            INSERT INTO test VALUES(NULL, 'test sql 001');    
            INSERT INTO test VALUES('1', 'test sql 002');    
 
        IF t_error = 1 THEN 
            ROLLBACK; 
        ELSE 
            COMMIT; 
        END IF; 
 
    END$$ 
DELIMITER ; 

在這個例子中，我們為test_sp1() 定義了一個 SQLEXCEPTION 參數 t_error， 在遇到SQL錯誤時，繼續執行(CONTINUE); 如果執行狀態沒有錯誤，則提交，反之回滾！

二.在調用事務時，將事務的執行狀態（即：事務是提交了還是回滾了），返回給被調者。

下面給出另一個例子：

CREATE DEFINER=`3dmodelbaseadmin`@`%` PROCEDURE `p_userConfirmPay`(
in p_lID int,
in p_endTime DATETIME,
in p_moneyAfterTax decimal(10,2),
in p_integralAfterTax decimal(10,0),
in p_sellerID int unsigned,
in p_cashOrPoints int,
in p_loginName_site varchar(50),
in p_transactionID_site char(100),
in p_orderID char(100),
in p_remarks_site char(100),
in p_transactionID char(100),
in p_cMEMID INT UNSIGNED,
in p_curTotal DECIMAL(10,2),
in p_curTotalcIntegral decimal(10,0),
in p_remarks char(100))
BEGIN

DECLARE p_cMEMID_site INT;
DECLARE p_balance_site DECIMAL(10,2);
DECLARE p_balance DECIMAL(10,2);
DECLARE p_intBalance_site DECIMAL(10,0);
DECLARE p_intBalance DECIMAL(10,0);

DECLARE t_error INTEGER DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1;

START TRANSACTION;

set p_cMEMID_site=(select cMEMID from m_member where cLoginName=p_loginName_site);

update l_memberdownlog set lState=1,endTime=p_endTime where lID=p_lID;

update m_memberMoney set cMoney=cMoney-p_moneyAfterTax,
cIntegral = cIntegral-p_integralAfterTax where cMEMID=p_cMEMID_site;

update m_memberMoney set cMoney=cMoney+p_moneyAfterTax,
cIntegral = cIntegral+p_integralAfterTax where cMEMID=p_sellerID;

if p_cashOrPoints=0 then
  set p_balance_site = (select cMoney from m_memberMoney where cMEMID=p_cMEMID_site);
  INSERT INTO cashDetail ......(此處省略);
else
  set p_intBalance_site = (select cIntegral from m_memberMoney where cMEMID=p_cMEMID_site);
  INSERT INTO integralDetail ......（此處省略）;
end if;

update m_memberMoney set totalConsMoney=totalConsMoney+p_curTotal,
totalConsIntegral=totalConsIntegral+p_curTotalcIntegral where cMEMID=p_cMEMID;

IF t_error = 1 THEN
    ROLLBACK;
ELSE
    COMMIT;
END IF;

select t_error;  將事務的執行狀態返回給被調者

MySql存儲過程游標(Cursor)

1、游標的作用及屬性

游標的作用就是用於對查詢數據庫所返回的記錄進行遍歷，以便進行相應的操作；游標有下面這些屬性：

    a、游標是只讀的，也就是不能更新它；

    b、游標是不能滾動的，也就是只能在一個方向上進行遍歷，不能在記錄之間隨意進退，不能跳過某些記錄；

    c、避免在已經打開游標的表上更新數據。

2、如何使用游標

使用游標需要遵循下面步驟：

a、首先用DECLARE語句聲明一個游標             

DECLARE cursor_name CURSOR FOR SELECT_statement; 
上面這條語句就對，我們執行的select語句返回的記錄指定了一個游標  
     b、其次需要使用OPEN語句來打開上面你定義的游標

OPEN cursor_name; 
     c、接下來你可以用FETCH語句來獲得下一行數據，並且游標也將移動到對應的記錄上(這個就類似java裡面的那個iterator)。

FETCH cursor_name INTO variable list; 
     d、然後最後當我們所需要進行的操作都結束後我們要把游標釋放掉。

CLOSE cursor_name; 
在使用游標時需要注意的是，使用定義一個針對NOT FOUND的條件處理函數(condition handler)來避免出現“no data to fetch”這樣的錯誤，條件處理函數就是當某種條件產生時所執行的代碼，這裡但我們游標指到記錄的末尾時，便達到NOT FOUND這樣條件，這個時候我們希望繼續進行後面的操作，所以我們會在下面的代碼中看到一個CONTINUE。先看看我們的表格內容：

下面的游標使用演示獲取庫存量小於100的產品的代碼code，這個代碼純粹演示如何使用，在這裡沒有其他任何意義:)

DELIMITER $$ 
 
DROP PROCEDURE IF EXISTS `test`.`CursorProc` $$ 
CREATE PROCEDURE `test`.`CursorProc` () 
BEGIN 
 DECLARE  no_more_products, quantity_in_stock INT DEFAULT 0; 
 DECLARE  prd_code VARCHAR(255); 
 DECLARE  cur_product CURSOR FOR   SELECT code FROM products;  /*First: Delcare a cursor,首先這裡對游標進行定義*/ 
 DECLARE  CONTINUE HANDLER FOR NOT FOUND  SET  no_more_products = 1; /*when "not found" occur,just continue,這個是個條件處理,針對NOT FOUND的條件*/ 
 
 /* for  loggging information 創建個臨時表格來保持*/ 
 CREATE TEMPORARY TABLE infologs ( 
 Id int(11) NOT NULL AUTO_INCREMENT, 
 Msg varchar(255) NOT NULL, 
 PRIMARY KEY (Id) 
 ); 
 
 OPEN  cur_product; /*Second: Open the cursor 接著使用OPEN打開游標*/ 
 FETCH  cur_product INTO prd_code; /*Third: now you can Fetch the row 把第一行數據寫入變量中,游標也隨之指向了記錄的第一行*/ 
 
 REPEAT 
 
 SELECT  quantity INTO quantity_in_stock 
 FROM  products 
 WHERE  code = prd_code; 
  
 IF  quantity_in_stock < 100 THEN 
 INSERT  INTO infologs(msg) 
 VALUES  (prd_code); 
 END  IF; 
 FETCH  cur_product INTO prd_code; 
 
 UNTIL  no_more_products = 1 
 END REPEAT; 
 CLOSE  cur_product;  /*Finally: cursor need be closed 用完後記得用CLOSE把資源釋放掉*/ 
 SELECT *  FROM infologs; 
 DROP TABLE  infologs; 
END $$ 
 
DELIMITER ; 

下面是最終的結果：

<img src="http://www.111cn.net /uploads/201208/18/1345302174_3501.png" alt="">