Getting started with MySQL's stored procedures and practices

The stored procedures are:
Through a series of SQL statements, according to the parameters passed in (also can not), through a simple call,
The ability to complete more complex functions than a single SQL statement, stored on the database server side, only need to compile once and then reuse no need to compile again. The main control of the stored process.

A transaction is a series of data change operations that comprise a whole. Once a transaction contains an operation that fails or the user aborts, the user can control all operations in the transaction body to be undone, returning the state before the transaction begins.
The operation in a transaction is a whole, either whole or not. Thus ensuring the integrity of the data.
In MySQL, the MyISAM storage engine does not support transactions, INNODB support.

Both are very important knowledge in the database.

One, stored procedures

Create a stored procedure for the Test1 table mysql> delimiter $--delimiter $ is set to the command termination symbol, in place of the default semicolon, because the semicolon has other uses .mysql> CREATE PROCEDURE Sp_test1 ()  -Begin-> CREATE TABLE test1 (ID int,name varchar);->insert into test1 values (1, ' Lilei ');-> select * FROM Test1;-> end-> $Query OK, 0 rows Affected (0.00 sec) mysql> delimiter; The recovery semicolon is called as a delimited terminating symbol for calling the stored procedure mysql> call Sp_test1 (); Creating a stored procedure with parameters mysql> delimiter $--delimiter $ is set to the command termination symbol, in place of the default semicolon, Because semicolons have other uses .mysql> CREATE PROCEDURE sp_test (in pi_id int, out po_name varchar ()), begin-> select * from Test.tb_ Test;-> Select Tb_test.name to Po_name from test.tb_test where tb_test.id = pi_id;-> end-> $Query OK, 0 rows AF Fected (0.00 sec) mysql> delimiter; --Recover semicolon as delimited terminating symbol 5. Call the stored procedure mysql> set @po_name = '; Query OK, 0 rows Affected (0.00 sec) Mysql> Call Sp_test (1, @po_name);

　　

Second, things

Getting started with MySQL stored procedures and practices