Learning from MySql stored procedure _ MySQL

MySql learning experience-the stored procedure first look at the mysql Query article Reply statement:

# Query article replies
------------------------------
-- 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 = pagesize;
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.

# 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, the added foreign key field must have corresponding data in the table where the corresponding foreign key is located
SET @ u_id = u_id;
SET @ s_id = s_id;
SET @ j_id = j_id;
SET @ verifytime = DATE ('2017-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 number of affected rows is automatically returned in the query result.
END

DELIMITER;

# Deleting data by ID
------------------------------
-- 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 using an account
------------------------------
-- 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 administrators
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;
# Querying users
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;

# 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.