Mysql (12) stored procedure _ MySQL

Mysql (12) stored procedure bitsCN.com

Mysql stored procedures

Related links:

Mysql (1) installation of mysql

Http: // database/201210/162314 .html;

Mysql-related operations (2)

Http: // database/201210/162315 .html;

Mysql operations on data tables (3)

Http: // database/201210/162316 .html;

Mysql (4) Data Table query operations

Http: // database/201210/162317 .html;

Mysql operations (5)

Http: // database/201210/162318 .html;

Mysql (6) string pattern matching

Http: // database/201210/163969 .html;

Mysql (7) in-depth select query

Http: // database/201210/163970 .html;

Mysql Index (8)

Http: // database/201210/163971 .html;

Mysql common functions

Http: // database/201210/164229 .html;

Mysql (10) trigger 1

Http: // database/201210/164516 .html

Mysql things (11) trigger 2

Http: // database/201210/164766 .html

A stored procedure is a collection of SQL statements stored in the database after compilation.

How to write stored procedures

Example:

Stored procedure syntax

SQL code

Create procedure proc_name (proc_peremeter1,...) -- Stored procedure name and parameters

[Characteristic...]

Routine_body -- Stored Procedure

Call the stored procedure syntax

SQL code

Call proc_name -- call a stored procedure

Example: create a stored procedure

Of course, before creating a stored procedure, you should first create the relevant table. in order to learn, you only need to create a simple table structure;

SQL code

Create table filmall (

Id smallint (5) not null,

Film_id smallint (6) not null,

Name varchar (40) default null,

Store_id smallint (6) not null,

Txt text,

Primary key ('id ')

);

Insert data into the data table.

Write a simple stored procedure:

SQL code

-- The name of the stored procedure is proc_film_store, with three parameters: input and output

SQL code

Delimiter $

SQL code

Create procedure proc_film_store (IN p_film_id INT, IN p_store_id INT, OUT p_film_count INT)

BEGIN

SELECT txt FROM filmall WHERE film_id = p_film_id AND store_id = p_store_id;

SELECT FOUND_ROWS () INTO p_film_count; -- put the number of entries in the variable

END $

SQL code

Delimiter;

Now you can call the stored procedure.

SQL code

Call proc_film_store (, @ a); --- enter the parameter

-- The queried data is output after execution.

-- Output the number of queried items

Select @;

The stored procedure is OK.

SQL code

-- The following command is usually used to write a stored procedure:

-- Change the Terminator before writing the stored procedure

Delimiter $ -- this statement means to replace the Terminator with $

-- After writing the stored procedure, change the Terminator back.

Delimiter;

BitsCN.com