Mysql Stored Procedures

Beginning MySql Stored Procedure

A database stored program—sometimes called a stored module or a stored routine—is
a computer program (a series of instructions associated with a name) that is stored within, and executes within, the database server. The source code and (sometimes) any compiled version of the stored program are almost always held within the database server’s system tables as well. When the program is executed, it is executed within the memory address of a database server process or thread.

There are three major types of MySQL stored programs:
Stored procedures
Stored procedures are the most common type of stored program. A stored procedure is a generic program unit that is executed on request and that can accept multiple input and output parameters.
Stored functions
Stored functions are similar to stored procedures, but their execution results in
the return of a single value. Most importantly, a stored function can be used
within a standard SQL statement, allowing the programmer to effectively extend
the capabilities of the SQL language.
Triggers
Triggers are stored programs that are activated in response to, or are triggered
by, an activity within the database. Typically, a trigger will be invoked in
response to a DML operation (INSERT, UPDATE, DELETE) against a database table.
Triggers can be used for data validation or for the automation of denormalization.

Simple Example of Stored Procedure

1 2 3 4 5 6 7 8 9 10 11 12 13

BEGIN DECLARE l_book_count INTEGER; SELECT COUNT(*) INTO l_book_count FROM books WHERE author LIKE '%XYZ%'; SELECT CONCAT('XYZ has written (or co-written) ', l_book_count , ' books.'); UPDATE books SET author = REPLACE (author, 'XYZ', 'Zeenux') WHERE author LIKE '%XYZ%'; END