I used to write a stored procedure on MSSQL. I have never written or used it on MYSQL. 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.