Skip navigation links

MySQL Forums :: MySQL Workbench - Database Migration :: MySQL stored procedure


Advanced Search

MySQL stored procedure
Posted by: green jim ()
Date: March 15, 2010 08:19PM

MySQL stored procedure

MySQL 5.0 later began to support stored procedures, stored procedures, consistency, efficiency, security and architecture characteristics, this section will explain the adoption of specific examples of how to manipulate MySQL stored PHP process.

261 examples: the creation of stored procedure
This is a stored procedure to create an instance of
Video Location: CD \ mingrisoft \ 09 \ 261

Examples

In order to ensure data integrity, consistency, improve application performance, often using storage process technology. MySQL 5.0 does not support the previous version of the stored procedures, with the maturing technology, MySQL, stored procedures, the project would later be widely used. This example will introduce in later versions of MySQL 5.0 to create a stored procedure.

Techniques

Include the name of a stored procedure, parameter lists, and may include many SQL statements SQL statement set. The following definition for a stored procedure process: create procedure proc_name (in parameter integer) begindeclare variable varchar (20); if parameter = 1 thenset variable = 'MySQL'; elseset variable = 'PHP'; end if; insert into tb (name ) values (variable); end;
MySQL stored procedure create procedure to establish a starting keyword, followed by followed by the name of the stored procedure and parameters. MySQL stored procedure name is not case-sensitive, for example, PROCE1 () and proce1 () on behalf of the same name of a stored procedure. Stored procedure name can not be with the MySQL database built-in function Chong Ming.

Stored procedure parameters are generally three parts. The first part can be in, out or inout. in that the incoming parameter to the stored procedure; out that came out parameters; inout parameters can be passed that defined stored procedure, and can be modified stored procedure came a stored procedure, stored procedure default to pass parameters, so parameter in can be omitted. The second part parameter names. Part III is divided into the type of parameters, the type of MySQL database, all available field types, if there are multiple parameters, carried out between the parameters can be separated with a comma.

MySQL stored procedure in order to begin the beginning of the block in order to end an end. Statement body can contain variable declarations, control statements, SQL query and so on. As the stored procedure within the statement should end with a semicolon, so the definition of a stored procedure before the end of the statement should sign ";" changed to other characters, and the characters in a stored procedure should also be less likely to occur, you can use keyword delimiter更改. For example: mysql> delimiter / /
After the stored procedure created, the following statement can be used to delete the parameter proc_name that stored procedure name. drop procedure proc_name
Implementation process

(1) MySQL stored procedure in the "Command Prompt" created, therefore, should first open a "Command Prompt" window.
(2) to enter "Command Prompt" window, the first MySQL database server should log in the "Command Prompt", enter the following command:

mysql-u username-p Password
(3) to change the statement to the end of symbols, this instance will change the statement terminator "//"。 Code is as follows:

delimiter / /
(4) Create a stored procedure should first select a database before. Code is as follows:

use the database name
(5) to create a stored procedure.
(6) by calling a stored procedure call statements.tiffany & co

Options: ReplyQuote


Subject Written By Posted
MySQL stored procedure green jim 03/15/2010 08:19PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.