[mysql] STORE PROCEDURE

MYSQL5.0 的 儲存過程(STORE PROCEDURE).
重點: 傳入的變數 名稱 千萬不要和 欄位名稱重複.
create procedure add_catagory (
IN param1 int,
IN param2 char(50),
IN param3 text,
OUT cid int,
OUT error_msg char(80))
begin
declare master_id, master_exist, name_exist int;
set cid = -1;
set name_exist = 0, master_exist = 0;

# Insert a subcatagory #
if param1 > 0 then

# Check if the master catagory ID is valid #
select count(catagory_id), catagory_id into master_exist, master_id
from catagory where catagory_id=param1 group by catagory_id;

# Check if the same catagory name exist and the master catagory #
select count(catagory_id) into name_exist from catagory, catagory_set
where catagory.name=param2 and catagory.catagory_id=catagory_set.slave_id
and catagory_set.master_id=master_id;

if master_exist > 0 and name_exist = 0 then

lock tables catagory write, catagory_set write;
flush table catagory, catagory_set;
insert into catagory values (null, param2, param3);
select last_insert_id() into cid;
insert into catagory_set values (param1, cid);
unlock tables;

elseif master_exist = 0 then
set error_msg = 'The master catagory ID provided does not exist';

elseif name_exist > 0 then
set error_msg = 'The catagory name already exist, please choose another name';

end if;
# Insert a primary catagory #
else
# Search and compare the name of all primary catagory #
select count(catagory_id) into name_exist from catagory
where name = param2 and not exists(
select * from catagory_set
where catagory_set.slave_id = catagory.catagory_id
);
if name_exist > 0 then
set error_msg = 'The catagory name already exist, please choose another name';
else
insert into catagory values (null, param2, param3);
select last_insert_id() into cid;
end if;

end if;
end ?
delimiter ;

call add_catagory(1,'Planet','Earth',@cid,@error);
select @cid, @error;

Stored Procedure 的寫法可參考：http://dev.mysql.com/doc/refman/5.0/en/stored-procedures.html

Trigger 的寫法可參考：http://dev.mysql.com/doc/refman/5.0/en/triggers.html

View 的寫法可參考：http://dev.mysql.com/doc/refman/5.0/en/views.html