MySQL stored procedure stored procedure (3) Storage summary

Before always write stored procedures on the MSSQL, not written on the MySQL, but also basically no use, today need to use MySQL, research, the project needs to write a part of the stored procedures, write a summary of the work. This does not give the database structure, does not discuss the details of SQL statements, mainly discusses the stored procedure syntax, suitable for the basic people.

Posted Address: http://www.cnblogs.com/zxlovenet/p/3783136.html

#查询文章回复--------------------------------Procedure structure for ' sp_select_reply_article '------------------------- -----DROP PROCEDURE IF EXISTS ' sp_select_reply_article ';D elimiter;; 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;;D Elimiter;

#技术点1: MySql5.1 does not support the limit parameter (MySql5.5 is supported), if you are writing stored procedures using the limit to do variables, it is necessary to build with dynamic SQL, and this performance certainly does not have static SQL good. 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;

#技术点2: If you need to return the affected rows at the same time, you need to add a statement after the statement: the Row_count () function, which requires ";" between the two statements. Separated.

Posted Address: http://www.cnblogs.com/zxlovenet/p/3783136.html

#更新数据--------------------------------Procedure structure for ' sp_update_permission '------------------------------ DROP PROCEDURE IF EXISTS ' sp_update_permission ';D elimiter;; CREATE definer= ' root ' @ ' localhost ' PROCEDURE ' sp_update_permission ' (in ' puser_uid ' varchar "), 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_u Nicode_ci; END;;D Elimiter;

#技术点3: An error occurred while MySQL was comparing strings (illegal mix of collations (utf8_unicode_ci,implicit) and (utf8_general_ci,implicit) for Operation ' = '), Workaround: Convert the comparison equation on one side of the string, such as "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_u Nicode_ci;

#插入数据--------------------------------Procedure structure for ' sp_insert_user '------------------------------DROP PROCEDURE IF EXISTS ' sp_insert_user ';D elimiter;; CREATE definer= ' root ' @ ' localhost ' PROCEDURE ' sp_insert_user ' (in ' uid ' varchar "), in ' UPW ' varchar (+), in ' name ' varchar (a), in ' Sex ' int,in ' phone ' varchar (a), in ' u_id ' int,in ' s_id ' int,in ' j_id ' int) BEGIN         #Routine body Goes E ...         SET @uid = uid;         SET @upw = UPW;         SET @uname = uname;         SET @sex = sex;         SET @phone = phone;         #由于外键约束, the Foreign key field to be added needs to have the corresponding data SET in the table corresponding to the foreign key         @u_id = u_id;         SET @s_id = s_id;         SET @j_id = j_id;         SET @verifytime = DATE (' 0000-00-00 ');         
VALUES (@uid, @upw, @uname, @sex, @phone, @u_id, @s_id, @j_id, @verifytime);         #查询结果会自动返回受影响行数END;;D Elimiter;

Posted Address: http://www.cnblogs.com/zxlovenet/p/3783136.html

#根据ID删除数据--------------------------------Procedure structure for ' sp_delete_exchange_by_id '---------------------- --------DROP PROCEDURE IF EXISTS ' sp_delete_exchange_by_id ';D elimiter;; CREATE definer= ' root ' @ ' localhost ' PROCEDURE ' sp_delete_exchange_by_id ' (in ' Eid ' int. int) BEGIN         #Routine body goes here ...         SET @eid = Eid;         DELETE from gk_exchange WHERE id = @eid; END;;D Elimiter;

#通过账号查询用户或者管理员--------------------------------Procedure structure for ' sp_select_user_by_uid '-------------------- ----------DROP PROCEDURE IF EXISTS ' sp_select_user_by_uid ';D elimiter;; CREATE definer= ' root ' @ ' localhost ' PROCEDURE ' sp_select_user_by_uid ' (in ' uid ' varchar (), ' getadmin ' int) BEGIN #         Routine body goes here ...         SET @uid = uid;         #SET @getadmin = getadmin;  #查询管理员 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; #查询用户 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;;D Elimiter;

#技术点4: The save process requires a control statement (if Else elseif while loop repeat leave iterate).

IF (getadmin = 1) Then

#语句 ...

END IF;

Posted Address: http://www.cnblogs.com/zxlovenet/p/3783136.html

#技术点5: Error when incoming parameter mismatch (Column count doesn ' t match value count at row 1), this is the careful question, check the parameters in detail.

#技术点6: Gets the current time function: Now ()

#技术点7: "'" This symbol is inverted single quotation marks, two anti-single quotation marks will be used as a variable, usually in the definition of the field when the keyword conflict when encountered.

MySQL stored procedure stored procedure (3) Storage summary