mysql stored procedures, functions, triggers an example

2010-12-02  来源:本站原创  分类:Database  人气:88 

1, split a string with "|||" interval stored procedure.

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
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, online page example of a stored procedure. (From http://www.yiluo.net/?p=35), which is to prepare the string when the statement is executed

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, create a function must have a return type.

4, flip-flop.

Syntax:

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;

Uppercase for keywords
trigger_name: trigger name, I used the naming t_name_tableName_ (b | a) (i | u | d), t: trigger identification, name: English name, tableName: the table name, b (BEFORE): identifies the trigger Prior to that event, a (AFTER): identifies the trigger incident, i (insert): identify the insert event, u (update): identifies the update event, d (delete): identification delete events;
trigger_time: trigger time (BEFORE or AFTER)
trigger_event: event name (insert or update, or delete)
tbl_name: table name (must be a permanent table)
trigger_stmt: execute statement (which can be complex language name), use of aliases OLD and NEW, and triggers can reference columns in the table related.

Example:

create trigger tr_socre after insert on tal_name

for each row

begin

sql statement

end

This article comes from CSDN blog, reproduced, please indicate the source: http://blog.csdn.net/fxloverssh/archive/2008/11/26/3376715.aspx

相关文章