mysql存储过程，函数，触发器例子

1，分割一个用"|||"间隔的字符串的存储过程．

create PROCEDURE sp_InsertJoKe(lastindex int)
begin
declare i int;
set i=1;
while (i<lastindex) do
begin
declare a varchar(4000);
declare p int;
declare part1 varchar(4000);
set a='';
select a=TitleContent from content where ID=i ;
set p=position('|||' in a);
while (p>0) do
begin
set part1=mid(a,1,p-1);
insert MyJoke(Content) values(part1);
set a=SUBSTRING(a,p+3);
set p=position('|||' in a);
end;
end while;
insert MyJoke(Content) values(a);
set i=i+1;
end;
end while;
end;


2，网上一个存储过程分页的例子．（转自http://www.yiluo.net/?p=35），其中把字符串当语句执行是prepare

CREATE PROCEDURE ClassList(
IN ID int,
fldName varchar(100),
pageSize int,
pageIndex int,
orderType int,
strWhere varchar(2000),
OUT cou int
)
begin
declare beginRow int;
declare sqlStr varchar(1000);
declare limitTemp varchar(1000);
declare orderTemp varchar(1000);
declare v_classp int;
declare oo int;
declare done int default 0;
declare sql1 varchar(500) default ” “;
declare sql2 varchar(200);
declare cur cursor for select shop_classid from shop_class where shop_parentclassid=id;
DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000′ SET done = 1;

set sql2=’select*from shop_class left join shop_main on shop_classid=shop_type_id where’;
select shop_parentclassid into v_classp from shop_class where shop_classid=id;

if(v_classp=0) then
open cur;
repeat
fetch cur into oo;
IF NOT done THEN
set sql1=concat(’ or shop_classid=’,oo,sql1);
END IF;
UNTIL done END REPEAT;
close cur;
set @sql=concat(sql2,substring(sql1,4,length(sql1)-3));
else
set @sql=concat(’select * from shop_main where shop_type_id=’,id);
end if;

set beginRow = (pageIndex-1)*pageSize;
set limitTemp = CONCAT(’ limit ‘,beginRow,’,',pageSize);
set orderTemp = CONCAT(’ order by ‘,fldName);
if orderType = 0 then
set orderTemp = CONCAT(orderTemp,’ ASC ‘);
else
set orderTemp = CONCAT(orderTemp,’ DESC ‘);
end if;

set @sqlString = CONCAT(@sql,’ ‘,strWhere,orderTemp,limitTemp);

prepare sqlstmt from @sqlString;
execute sqlstmt;

deallocate prepare sqlstmt;
end


3，创建函数时必须有返回类型．

4，触发器．

语法：

CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name
FOR EACH ROW
BEGIN
trigger_stmt
END；

CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name
FOR EACH ROW
BEGIN
trigger_stmt
END；

大写的为关键字
trigger_name:触发器的名字，我常用的命名规则t_name_tableName_(b|a)(i|u|d),t:触发器标识，name:英文名，tableName:表名，b(BEFORE):标识是触发事件之前，a(AFTER):标识触发事件之后,i(insert):标识insert事件，u(update):标识update事件，d(delete):标识delete事件;
trigger_time:触发时间(BEFORE或AFTER)
trigger_event:事件名(insert或update或delete)
tbl_name:表名(必须是永久性表)
trigger_stmt：执行语句(可以是复合语名)，使用别名OLD和NEW，能够引用与触发程序相关的表中的列。

例：

create trigger tr_socre after insert on tal_name

for each row

begin

sql语句

end


本文来自优快云博客，转载请标明出处：http://blog.youkuaiyun.com/fxloverssh/archive/2008/11/26/3376715.aspx