Mysql Stored Procedure error debugging records
Mysql Stored Procedure error debugging records
The company's Platform runs on the mysql database. Some stored procedures report errors, the debugging process is painful, and errors and solutions are recorded for reference.
Debugging environment: dbForge Studio for MySQL 6.1, trial version;
1. BIZ_GET_ORGAN_BY_CONDITION
Problem 1: a recursive call error is prompted. There are too many recursion times. The maximum value is 255 based on the service settings;
SET @ max_sp_recursion_depth = 10;
If garbled characters are common, the condition becomes invalid and an endless loop occurs;
Question 2: The variable content is garbled, the database content is Chinese, urt-8
Declarevc_agency_type varchar (64) character set utf8;
When defining variables, you must set the character set;
Question 3: The stored procedure parameters are passed in Chinese and garbled; the defined parameters are the same as above;
2. BIZ_QUERY_USER_BY_CONDITION
Problem 1: row_count () returns-1
The Return Value of the select statement is FOUND_ROWS (); row_count () is generally the number of rows affected by insertion, modification, and deletion;
Question 2: mysql division of Integers
Declare page_count int;
Set page_count = 15/10; the result is automatically rounded to 2;
Use the following statement: set page_count = FLOOR (result_count/page_size );
3. C3_BIZ_DATA_LIMIT
Problem 1: The cursor has a record, but the value is null: The variable in the cursor statement cannot have the same name as the field and cannot be a keyword;
Question 2: The cursor cannot get the value;
The field name and variable name must not be the same; if the field name must be the same, add an alias to the table for the difference;
Problem 3: The out parameter cannot be set.
Changed to the inout type. There are three types of stored procedures: in out inout;
Question 4: Change the right function to the subStr function;
4. BIZ_CHANGER_ORG_PARENT_ID
Problem 1: mysql does not have the if not exists syntax, so you need to take the total number and then determine the number of rows;
Question 2: update does not support subqueries;
Update sys_org set leaftype = 1 where id = (
Selectorg_parent_id from sys_org where id = Variable
);
To:
Update sys_org set leaftype = 1 where id = (
Select org_parent_id from
(Select * from sys_org WHERE id = vc_source_id)
);