MySQL stored procedures Getting started and improved (first article)

Previously on the internet to see crooked people specifically wrote this mysql stored procedures, feel very powerful ah. Just recently the optimization of the new project to use the stored procedure, I took the previous study notes to tidy up a bit. Recently too busy Ah!!!

MySQL stored programs includes stored procedures, functions, triggers.

Let's take a look at the official definition.

Stored Procedures Stored Procedures
Stored procedures is the most common type of Stored program. A stored proce-
Dure is a generic program unit this is executed on request and that can accept
Multiple input and output parameters.
Stored functions function
Stored functions is 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 trigger.
Triggers is stored programs that is activated in response to, or is triggered
By, an activity within the database. Typically, a trigger'll be invoked in
Response to a DML operation (INSERT, UPDATE, DELETE) against a database table.
Triggers can is used for data validation or for the automation of denormalization.

Probably say a bit.

Stored Procedures : Stored procedures can make it much easier to manage the database and to display information about the database and its users. A stored procedure is a precompiled collection of SQL statements and optional control-flow statements, stored as a single name and processed as a unit. Stored procedures are stored in the database and can be executed by the application through a call, and allow the user to declare variables, conditional execution, and other powerful programming features. Stored procedures can contain program flow, logic, and queries against the database. They can accept parameters, output parameters, return single or multiple result sets, and return values. stored procedures are stored in the database and can be executed by the application through a call, and allow the user to declare variables, conditional execution, and other powerful programming features. Stored procedures can contain program flow, logic, and queries against the database. They can accept parameters, output parameters, return single or multiple result sets, and return values. You can use stored procedures for any purpose that uses SQL statements, and it has the following advantages: it can be in a single stored procedure .

custom Functions : Stored procedures can make it much easier to manage the database and to display information about the database and its users. A stored procedure is a precompiled collection of SQL statements and optional control-flow statements, stored as a single name and processed as a unit. The difference between stored procedures and functions:

1. In general, the function of the stored procedure implementation is a bit more complex, and the function implementation of the function is relatively strong.
2. Parameters can be returned for stored procedures, and functions can only return values or table objects.
3. The stored procedure is typically performed as a separate part, and the function can be called as part of a query statement, since the function can return a Table object.

A stored procedure is a program, which is a syntax-checked and compiled SQL statement, so it runs particularly fast. (This is why the stored procedure is used)

triggers : Triggers are special stored procedures that require program calls, and triggers are executed automatically; for performance and data security reasons, the triggers are largely unused in the project.

MySQL view all stored procedures, functions, triggers in the library:

Select ' Name ' from Mysql.proc where db = ' your_db_name ' and ' type ' = ' PROCEDURE '

can also "show procedure status; Stored Procedure "

Select ' Name ' from Mysql.proc where db = ' your_db_name ' and ' type ' = ' function '//functions

can also show function status; Function

Stored procedures for triggers table queries in the INFORMATION_SCHEMA database

Mysql>select * FROM triggers T WHERE trigger_name= "Mytrigger" \g

SHOW TRIGGERS [from db_name] [like expr]

SHOW triggers\g//Trigger

MySQL stored procedures Getting started and improved (first article)