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


本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/fxloverssh/archive/2008/11/26/3376715.aspx