建议你看mysql manual,这是简单的例子: "create procedure db.CargoTransfer( in gid int, in sdid int, in ddid int, in amount int )\n begin\n declare a int; select quantity into a from db.cun where goodsid=gid and depotid=sdid; if a >= amount then update db.cun set quantity=quantity-amount where goodsid=gid and depotid=sdid; update db.cun set quantity=quantity+amount where goodsid=gid and depotid=ddid; end if; end;"调用方法: call db.CargoTransfer()
DELIMITER $$DROP PROCEDURE IF EXISTS `test`.`Uniq_rank`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `Uniq_rank`() BEGIN select id,name,max(datetime),rank from song group by rank order by rank,id; END$$DELIMITER ;============ 调用 call Uniq_rank();
CREATE PROCEDURE和CREATE FUNCTION CREATE PROCEDURE sp_name ([proc_parameter[,...]]) [characteristic ...] routine_body
param_name type
type: Any valid MySQL data type
characteristic: LANGUAGE SQL | [NOT] DETERMINISTIC | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER } | COMMENT 'string'
routine_body: Valid SQL procedure statement or statements 这些语句创建存储子程序。要在MySQL 5.1中创建子程序,必须具
drop procedure if exists Proc_TEST; DELIMITER $$ CREATE PROCEDURE Proc_TEST ( IN PARA1 int, IN PARA2 varchar(3000) ) BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK; START TRANSACTION;
建议你看mysql manual,这是简单的例子: "create procedure db.CargoTransfer( in gid int, in sdid int, in ddid int, in amount int )\n begin\n declare a int; select quantity into a from db.cun where goodsid=gid and depotid=sdid; if a > = amount then update db.cun set quantity=quantity-amount where goodsid=gid and depotid=sdid; update db.cun set quantity=quantity+amount where goodsid=gid and depotid=ddid; end if; end; " 调用方法: call db.CargoTransfer()
//创建存储过程simpleproce1 create procedure simpleproce1 (out par1 int) begin select count(*) into par1 from proce; end//调用存储过程 call simpleproce1(@a);//查询结果 select @a;
"create procedure db.CargoTransfer( in gid int, in sdid int, in ddid int, in amount int )\n
begin\n
declare a int;
select quantity into a from db.cun where goodsid=gid and depotid=sdid;
if a >= amount then
update db.cun set quantity=quantity-amount where goodsid=gid and depotid=sdid;
update db.cun set quantity=quantity+amount where goodsid=gid and depotid=ddid;
end if;
end;"调用方法:
call db.CargoTransfer()
BEGIN
select id,name,max(datetime),rank from song group by rank order by rank,id;
END$$DELIMITER ;============
调用
call Uniq_rank();
START TRANSACTION;
sql 语句... ...
commit / rollback
CREATE PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
param_name type
type:
Any valid MySQL data type
characteristic:
LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'
routine_body:
Valid SQL procedure statement or statements
这些语句创建存储子程序。要在MySQL 5.1中创建子程序,必须具
DELIMITER $$
CREATE PROCEDURE Proc_TEST (
IN PARA1 int,
IN PARA2 varchar(3000)
)
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
START TRANSACTION;
...................
COMMIT;END $$
DELIMITER ;
2、定义一个有事务的存储过程,在它的BODY里面调用前面N个存储过程。
"create procedure db.CargoTransfer( in gid int, in sdid int, in ddid int, in amount int )\n
begin\n
declare a int;
select quantity into a from db.cun where goodsid=gid and depotid=sdid;
if a > = amount then
update db.cun set quantity=quantity-amount where goodsid=gid and depotid=sdid;
update db.cun set quantity=quantity+amount where goodsid=gid and depotid=ddid;
end if;
end; " 调用方法:
call db.CargoTransfer()
create procedure simpleproce1 (out par1 int)
begin
select count(*) into par1 from proce;
end//调用存储过程
call simpleproce1(@a);//查询结果
select @a;