MySql stored procedure summary _ MySQL

MySql stored procedure summary I used to write the stored procedure on MSSQL. I have not written it on MYSQL, and I have never used it. I need to use MYSQL today, write a part of the stored procedure required by the project and summarize the work. The database structure is not provided here. I will not discuss the details of SQL statements. I will mainly discuss the stored procedure syntax, which is suitable for people with basic knowledge.

Posting address: http://www.cnblogs.com/zxlovenet/p/3783136.html

# Query the article reply -- Reply Procedure structure for 'sp _ select_reply_article '-- -------------------------- drop procedure if exists 'sp _ select_reply_article'; DELIMITER ;; create definer = 'root' @ 'localhost' PROCEDURE 'sp _ select_reply_article '(IN 'Ra _ id' int, IN 'pagefrom 'Int, IN 'pagesize' int) BEGIN # Routine body goes here... SET @ ra_id = ra_id; SET @ pagefrom = pagefrom; SET @ pagesize = p Agesize; SET @ ssra = CONCAT ('select * FROM gk_article WHERE id =? LIMIT ?,? '); PREPARE sqlquery FROM @ ssra; EXECUTE sqlquery USING @ ra_id, @ pagefrom, @ pagesize; END; DELIMITER;

# Technical point 1: MySql5.1 does not support the LIMIT parameter (MySql5.5 is supported). if LIMIT is used as a variable when writing a stored procedure, it needs to be built using dynamic SQL, in this way, the performance is certainly not as good as static SQL. The main code is as follows:

SET @ ssra = CONCAT ('select * FROM gk_article WHERE id =? LIMIT ?,? ');

PREPARE sqlquery FROM @ ssra;

EXECUTE sqlquery USING @ ra_id, @ pagefrom, @ pagesize;

# Technical point 2: If you want to return the affected rows at the same time, you must add the statement ROW_COUNT () after the statement. The two statements must be separated.

Posting address: http://www.cnblogs.com/zxlovenet/p/3783136.html

# Update data -- ------------------------------ Procedure structure for 'sp _ update_permission '-- ---------------------------- drop procedure if exists 'sp _ update_permission'; DELIMITER ;; create definer = 'root' @ 'localhost' PROCEDURE 'sp _ update_permission '(IN 'puser _ uid' varchar (20), IN 'plevel' int, IN 'ppower' int) BEGIN # Routine body goes here... SET @ puser_uid = puser_uid; SET @ plevel = plevel; SET @ ppower = ppower; UPDATE gk_permission SET 'level' = @ plevel, power = @ ppower WHERE user_uid = CONVERT (@ puser_uid USING utf8) COLLATE utf8_unicode_ci; END; DELIMITER;

# Technical point 3: An error occurred when MySQL compared strings (Illegal mix of collations (utf8_unicode_ci, IMPLICIT) and (utf8_general_ci, IMPLICIT) for operation '='). solution: CONVERT the comparison equation to a string, for example, "CONVERT (B. fullCode USING utf8) COLLATE utf8_unicode_ci ", the main code is as follows:

UPDATE gk_permission SET 'level' = @ plevel, power = @ ppower WHERE user_uid = CONVERT (@ puser_uid USING utf8) COLLATE utf8_unicode_ci;

# Insert data -- ------------------------------ Procedure structure for 'sp _ insert_user '-- ---------------------------- drop procedure if exists 'sp _ insert_user'; DELIMITER ;; create definer = 'root' @ 'localhost' PROCEDURE 'sp _ insert_user '(IN 'uid' varchar (20), IN 'upw' varchar (32 ), IN 'name' varchar (20), IN 'sex' int, IN 'phone' varchar (20), IN 'U _ id' int, IN's _ id' int, IN 'J _ id' int) BEGIN # Routine body goes here... SET @ uid = uid; SET @ upw = upw; SET @ uname = uname; SET @ sex = sex; SET @ phone = phone; # due to foreign key constraints, therefore, the added foreign key field must have the corresponding data SET @ u_id = u_id; SET @ s_id = s_id; SET @ j_id = j_id; SET @ verifytime = DATE ('1970-00-00 '); insert into gk_user (uid, upw, uname, sex, phone, u_id, s_id, j_id, verifytime)
VALUES (@ uid, @ upw, @ uname, @ sex, @ phone, @ u_id, @ s_id, @ j_id, @ verifytime ); # The query results will automatically return the number of affected rows END; DELIMITER;

Posting address: http://www.cnblogs.com/zxlovenet/p/3783136.html

# Delete data by ID -- revoke Procedure structure for 'sp _ delete_exchange_by_id '-- -------------------------- drop procedure if exists 'sp _ delete_exchange_by_id'; DELIMITER ;; create definer = 'root' @ 'localhost' PROCEDURE 'sp _ delete_exchange_by_id '(IN 'eid' int) BEGIN # Routine body goes here... SET @ eid = eid; delete from gk_exchange WHERE id = @ eid; END; DELIMITER;

# Query a user or administrator through an account -- revoke Procedure structure for 'sp _ select_user_by_uid '-- -------------------------- drop procedure if exists 'sp _ select_user_by_uid'; DELIMITER ;; create definer = 'root' @ 'localhost' PROCEDURE 'sp _ select_user_by_uid '(IN 'uid' varchar (20), IN 'getadmin' int) BEGIN # Routine body goes here... SET @ uid = uid; # SET @ getadmin = getAdmin; # query the administrator IF (getAdmin = 1) then select us. *, un. 'name', se. 'name', jo. 'name', pe. 'level', pe. power FROM gk_user AS us, gk_unit AS un, gk_section AS se, gk_jobtitle AS jo, gk_permission AS pe WHERE us. u_id = un. id AND us. s_id = se. id AND us. j_id = jo. id AND us. uid = pe. user_uid AND us. uid = CONVERT (@ uid USING utf8) COLLATE utf8_unicode_ci; end if; # Query user IF (getAdmin = 0) then select us. *, un. 'name', se. 'name', jo. 'name' FROM gk_user AS us, gk_unit AS un, gk_section AS se, gk_jobtitle AS jo WHERE us. u_id = un. id AND us. s_id = se. id AND us. j_id = jo. id AND us. uid = CONVERT (@ uid USING utf8) COLLATE utf8_unicode_ci; end if; END; DELIMITER;

# Technical point 4: The control statement (if else elseif while loop repeat leave iterate) is used in this memory process ).

IF (getAdmin = 1) THEN

# Statement...

End if;

Posting address: http://www.cnblogs.com/zxlovenet/p/3783136.html

# Technical Point 5: If the input parameters do not match, an error is returned (Column count doesn't match value count at row 1). This is a careful question. check the parameters in detail.

# Technical point 6: function for obtaining the current time: NOW ()

# Technical Point 7: the symbol "'" is a single quotation mark. the Two quotation marks are enclosed as variables, which are generally used when a keyword conflict occurs during field definition.