Mysql Stored Programs

最新推荐文章于 2022-05-22 18:19:19 发布

原创最新推荐文章于 2022-05-22 18:19:19 发布 · 219 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#MySQL

数据库专栏收录该内容

14 篇文章

订阅专栏

本文介绍了MySQL中的存储程序概念，包括存储过程、触发器和事件等。详细解释了存储过程的创建、查看及删除方法，并提供了具体的示例代码。

[size=medium]
[b]1.Stored programs include these objects:[/b]
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.

[b]2.Defining Stored Programs[/b]
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.
[/size]


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

[size=medium]
Stored routines require the proc table in the mysql database.

[b]mysql stored procedure[/b]
[b]1.创建[/b]
delimiter //
create procedure calcal(actId int)
begin
#do something
end;
//
delimiter ;

[b]2.查看[/b]
方法一：
select `name` from mysql.proc where db = 'your_db_name' and `type` = 'PROCEDURE'

方法二：
show procedure status;

[b]3.删除[/b]
drop procedure db_name.sp_name;[/size]