The MySQL stored procedure usage explanation detailed

Advantages of MySQL stored procedures

Precompilation, which is higher than direct SQL efficiency, can reduce the amount of traffic consumed during the transfer of SQL statements;

Simplifies business logic and translates requirements to professional DBAs (if any);

More convenient use of MySQL database things to deal with, especially shopping websites;

Security, user rights more easily managed;

Modifying stored procedures basically does not need to modify the program code, and directly write SQL modify SQL is generally to modify the relevant program

MySQL stored procedures for the creation of statements such as:

1. Create PROCEDURE (creating the storage process)

CREATE PROCEDURE Stored Procedure name (parameter list)

BEGIN

SQL Statement code block

End

Note: The parameter columns surrounded by parentheses must always exist. If there are no parameters, you should also use an empty parameter column (). Each parameter defaults to an in parameter. To specify other parameters, you can use the keyword out or inout before the parameter name to use the delimiter command when the MySQL client defines the stored procedure to change the statement delimiter from; When using the delimiter command, you should avoid using the backslash (') character, because that is the escape character for MySQL.

The code is as follows

Copy Code

CREATE PROCEDURE proentptypeinfo (iid int (one), lvl int) BEGIN --local variable definition declare tid int (one) default-1; DECLARE ttype_name varchar (255) Default "; DECLARE tptype_id int (one) default-1; --Cursor definition Declare cur1 CURSOR for select id,type_name,ptype_id from Entp_type_info where (ptype_id=iid or id=iid) and type = S_del = 0; --Cursor Introduction definition Declare CONTINUE HANDLER for SQLSTATE ' 02000 ' SET tid = null,ttype_name=null,tptype_id=null; SET @ @max_sp_recursion_depth = 13; --Open cursor OPEN Cur1; FETCH cur1 into tid,ttype_name,tptype_id; While (Tid are not null) Todo INSERT into Tmp_entp_type_info values (TID,TTYPE_NAME,TPTYPE_ID,LVL); --Tree-structured data recursive collection to the established temporary table Call Proentptypeinfo (tid,lvl+1); FETCH cur1 into tid,ttype_name,tptype_id; End while; End; drop procedure if exists proentptypeinfo; Drop temporary table if exists tmp_entp_type_info; Create temporary table if not exists tmp_entp_type_info (id int (), type_name varchar (255), FID Int (one), lvl int); Call Proentptypeinfo (7,0); SELECT * from Tmp_entp_type_info;

Below is a simple test, a dept table, 1-1000 departments, and departmental aliases; a users table, 200,000 users, one randomly belonging to 1000 departments; Suppose the users table has only department names, no department name aliases, add this field in the Users table ' Dept_alias ' The value of ' Dept_alias ' based on the Dept table:

The code is as follows

Copy Code

Department Information table CREATE TABLE ' Dept ' ( ' Name ' char (255) CHARACTER SET UTF8 NOT null DEFAULT null, ' Alias ' char (255) CHARACTER SET UTF8 DEFAULT NULL, PRIMARY KEY (' name ') ) Engine=myisam DEFAULT Charset=utf8; User Data table CREATE TABLE ' users ' ( ' ID ' int (a) unsigned not NULL auto_increment, ' username ' char (255) CHARACTER SET UTF8 DEFAULT NULL, ' Gender ' enum (' Male ', ' female ') CHARACTER SET UTF8 DEFAULT ' man ', ' Dept ' char (255) CHARACTER SET UTF8 DEFAULT NULL, ' Dept_alias ' char (255) DEFAULT NULL, PRIMARY KEY (' id '), KEY ' index_dept ' (' dept ') USING Btree ) Engine=myisam DEFAULT Charset=utf8; Testing stored Procedures DROP PROCEDURE IF EXISTS testprocedure; CREATE PROCEDURE TestProcedure () BEGIN DECLARE flag INT DEFAULT 0; DECLARE TID INT; DECLARE tdept CHAR (255); DECLARE Talias CHAR (20); DECLARE cur CURSOR for SELECT id,dept from users; DECLARE CONTINUE HANDLER for not FOUND SET flag = 1; OPEN cur; FETCH cur into tid,tdept; While flag<>1 do SELECT alias from dept WHERE name = tdept into Talias; UPDATE users SET Dept_alias=talias WHERE Id=tid; FETCH cur into tid,tdept; End while; Close cur; End

First of all, this needs to be implemented using one of the following SQL statements.

The code is as follows	Copy Code
--4.25 s UPDATE users as U SET u.dept_alias= (SELECT alias from dept WHERE Name=u.dept);

However, to test, read the data in the users individually and then query for updates, using stored procedures and using the usual query practices, respectively, as follows:

code is as follows

copy code

//time: 17.667736053467 s //memory:55128 bytes (does not contain MySQL memory for reference only) mysql_connect (' 127.0.0.1 ', ' root ', ' develop ') OR die (' Connect failure '); mysql_select_db (' test ') or Die (' Select db error! '); mysql_query (' SET NAMES utf8; '); $t 1 = getmicrotime (); mysql_query (' Call TestProcedure (); '); $t 2 = Getmicrotime (); Var_dump ($t 2-$t 1,memory_get_usage ()); Mysql_close ();     Function Getmicrotime () {     list ($usec, $sec) = Explode ("", Microtime ());     return ((float) $usec + (float) $sec); }