mysql存储过程示例

普通过程
DELIMITER //
CREATE DEFINER=root@% PROCEDURE proc_insert()
BEGIN

insert into logs(hyname,hynumber,ip,software) values('lf4′,’nn',’127.0.0.1′,’测试');
insert into logs(hyname,hynumber,ip,software) values('lf5lf5lf5lf5lf5lf5lf5lf5lf5lf5lf5lf5lf5lf5lf5lf5lf5lf5′,’nn2′,’127.0.0.1′,’测试');

END//
DELIMITER ;

带事务的过程
DELIMITER //
CREATE DEFINER=root@% PROCEDURE proc_insert_tran()
BEGIN
DECLARE t_error INTEGER DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1;

START TRANSACTION;
insert into logs(hyname,hynumber,ip,software) values('lf2′,’nn',’127.0.0.1′,’测试');
insert into logs(hyname,hynumber,ip,software) values('lf3lf2lf2lf2lf2lf2lf2lf2lf2lf2lf2lf2lf2lf2lf2lf2lf2lf2lf2lf2′,’nn2′,’127.0.0.1′,’测试');

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

END//
DELIMITER ;

分页存储过程
DELIMITER //
CREATE DEFINER=root@% PROCEDURE proc_paging(
in pi_fields varchar(2000),
in pi_tables text,
in pi_where varchar(2000),
in pi_orderby varchar(200),
in pi_pageindex int,
in pi_pagesize int,
out po_totalcount int ,
out po_pagecount int
)
begin
set @startRow = pi_pagesize * (pi_pageindex -1);
set @pageSize = pi_pagesize;
set @rowindex = 0;
set @strsql = CONCAT('select sql_calc_found_rows @rowindex:=@rowindex+1 as rownumber,',pi_fields,' from ',pi_tables,case ifnull(pi_where,") when " then " else concat(' where ',pi_where) end,' order by ',pi_orderby,' limit ',@startRow,',',@pageSize);

prepare strsql from @strsql;
execute strsql;
deallocate prepare strsql;

set po_totalcount = found_rows();
if (po_totalcount <= pi_pagesize) then
set po_pagecount = 1;
else if (po_totalcount % pi_pagesize > 0) then
set po_pagecount = po_totalcount / pi_pagesize + 1;
else
set po_pagecount = po_totalcount / pi_pagesize;
end if;
end if;
end//
DELIMITER ;

分页存储过程（支持视图，上面那个视图会出现莫名其妙的问题~）
DELIMITER //
CREATE DEFINER=root@% PROCEDURE proc_paging(IN pi_fields varchar(2000), IN pi_tables text, IN pi_where varchar(4000), IN pi_orderby varchar(200), IN pi_pageindex int, IN pi_pagesize int, OUT po_totalcount int , OUT po_pagecount int
)
begin
set @startRow = pi_pagesize * (pi_pageindex -1);
set @pageSize = pi_pagesize;
set @rowindex = 0;

set @strsql = CONCAT('select @rowindex:=@rowindex+1 as rownumber,',pi_fields,' from ',pi_tables,case ifnull(pi_where,") when " then " else concat(' where ',pi_where) end,' order by ',pi_orderby,' limit ',@startRow,',',@pageSize);
prepare strsql from @strsql;
execute strsql;
deallocate prepare strsql;

/*为视图单独优化记录总数*/
set @strsqlCount = CONCAT('select @po_totalcount:=count(*)',' from ',pi_tables,case ifnull(pi_where,") when " then " else concat(' where ',pi_where) end,' order by ',pi_orderby);
prepare strsqlCount from @strsqlCount;
execute strsqlCount;
deallocate prepare strsqlCount;
set po_totalcount = @po_totalcount;

if (po_totalcount <= pi_pagesize) then
set po_pagecount = 1;
else
if (po_totalcount % pi_pagesize > 0) then
set po_pagecount = po_totalcount / pi_pagesize + 1;
else
set po_pagecount = po_totalcount / pi_pagesize;
end if;
end if;
end//
DELIMITER ;