【www.bbyears.com--php安装】
技术要点
一个存储过程包括名字、参数列表，以及可以包括很多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 ;
下面是最终的结果：
