Mysql Stored Programs

刘伟

1.Stored programs include these objects:
   a).Stored routines, that is, stored procedures and functions. A stored procedure is invoked using the CALL statement. A procedure does not have a return value but can modify its parameters for later inspection by the caller. It can also generate result sets to be returned to the client program. A stored function is used much like a built-in function. you invoke it in an expression and it returns a value during expression evaluation.
   b).Triggers. A trigger is a named database object that is associated with a table and that is activated when a particular event occurs for the table, such as an insert or update.
   c).Events. An event is a task that the server runs according to schedule.
2.Defining Stored Programs
Each stored program contains a body that consists of an SQL statement. This statement may be a compound statement made up of several statements separated by semicolon (;) characters.

CREATE PROCEDURE dorepeat(p1 INT)
BEGIN
SET @x = 0;
REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
END;



Stored routines require the proc table in the mysql database.

mysql stored procedure
1.创建
delimiter //
create procedure calcal(actId int)
begin
   #do something
end;
//
delimiter ;
2.查看
方法一：
       select `name` from mysql.proc where db = 'your_db_name' and `type` = 'PROCEDURE'
方法二：
         show procedure status;
3.删除
drop procedure db_name.sp_name;