Shulou(Shulou.com)06/02 Report--

This article mainly explains "how to debug stored procedures in Navicat". The content in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn how to debug stored procedures in Navicat.

Stored procedure debugging method reference

Https://www.cnblogs.com/firebata/p/4585978.html

Https://www.cnblogs.com/kungfupanda/p/5645553.html

Debugging is very troublesome. How to comment in a stored procedure?

You can't comment in the stored procedure editing, so you need to copy the code to the normal query page, comment it on the query page, and then copy the code back to the stored procedure and save it.

Variable definition definition varchar variable must specify the size

Such as varchar (10), otherwise an error will be reported.

Cursors need to be defined after all other variables, otherwise a 1337 error will be reported

ERROR 1337 (42000): Variable or condition declaration after cursor

The definition of a variable should not have the same name as the key of your select column! Otherwise, fetch into will fail!

I didn't try this, but it's better not to have the same name as the column name, so it's easier to read.

In syntax stored procedures, you must remember to put a semicolon after the statement, otherwise you will report a seemingly inexplicable error debugging technique: you can use SELECT @ a to view the printed variable, but note that @ a sees the final variable, and the intermediate variable is not visible.

The @ a value in the result seen with SELECT @ an is the final value, and the intermediate variable cannot be seen, so debugging is very troublesome and cannot print the value of the variable in the middle of the run.

Skills of ★ debugging

It is also a way to locate errors (because it is difficult to locate the wrong statement position according to the error prompted): delete extraneous statements and eliminate them.

Development while testing: (1) design the overall idea; (2) write out the framework of ideas, do not fill in the details, such as some complex implementations, you can first empty or simply print some variables, write while testing, find all kinds of problems in time; (3) after the idea framework is written, if there is no problem with the test, add the details of the code, it is best to write part of the test. It is safer to debug because it is too troublesome.

Use reference for cursors

Https://www.cnblogs.com/trying/p/3296793.html

Use

Cursors need to be defined after all other variables, otherwise a 1337 error will be reported

How to cycle?

How does it end?

How to debug? Debugging is very troublesome.

Background description of cursor using sample stored procedure 1

Copy the data from table t to table T1

Construction table sentence

-- Table structure for t

DROP TABLE IF EXISTS `t`

CREATE TABLE `t` (

`i` int (11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8

-- Records of t

INSERT INTO `t`VALUES ('1')

INSERT INTO `t`VALUES ('2')

-- Table structure for T1

DROP TABLE IF EXISTS `t1`

CREATE TABLE `t1` (

`i` int (11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8

Stored procedure testCopy

-- Procedure structure for testCopy

DROP PROCEDURE IF EXISTS `testCopy`

DELIMITER

CREATE DEFINER= `root` @ `localhost` PROCEDURE `testCopy` ()

BEGIN

-- you need to define variables that receive cursor data

DECLARE a CHAR (16)

-- traversal data end flag

DECLARE done INT DEFAULT FALSE

Cursor, which needs to be defined after all other variables, otherwise a 1337 error will be reported

DECLARE cur CURSOR FOR SELECT i FROM t

-bind the closing flag to the cursor

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE

-- Open the cursor

OPEN cur

-- start the cycle

Read_loop: LOOP

-- extract the data from the cursor. There is only one, and it is the same if there are more than one.

FETCH cur INTO a

At the end of the statement

IF done THEN

LEAVE read_loop

END IF

-- do the circular events you want to do here.

INSERT INTO T1 VALUES (a)

END LOOP

-- close the cursor

CLOSE cur

END

DELIMITER

Cursors use sample stored procedure 2 to build table statements

-- Table structure for current

DROP TABLE IF EXISTS `current`

CREATE TABLE `current` (

`player` varchar (11) DEFAULT NULL

`market` varchar (11) DEFAULT NULL

`Money` int (11) DEFAULT NULL

`dateno` int (11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8

-- Records of current

INSERT INTO `current`VALUES ('Xiaoming', 'Yonghui', '100mm,' 20190807')

INSERT INTO `current`VALUES ('Xiaoming', 'Wumei', '200cm,' 20190807')

INSERT INTO `current` VALUES ('Xiaogang', 'Carrefour', '230', '20190807')

-- Table structure for history

DROP TABLE IF EXISTS `roomy`

CREATE TABLE `roomy` (

`player` varchar (11) DEFAULT NULL

`market` varchar (11) DEFAULT NULL

`Money` int (11) DEFAULT NULL

`dateno` int (11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8

-- Records of history

Background description

Table current, table history

The structure is the same, all are

Player delivery man

Market supermarket

Money delivery money

Dateno date serial number

Imagine a delivery man can help several supermarkets (Yonghui, Wumart, Carrefour, etc.) deliver goods, each delivery will be a certain amount (from dozens of yuan to hundreds of thousands of yuan).

Current records the amount of goods delivered to home by a delivery man today.

History records the cumulative situation before today.

Then the stored procedure testMerge performs such an operation: accumulate the data from the current table into the history table, and then delete the accumulated data.

Idea: (1) select the qualified records in the current table to the cursor; (2) operate the cursor to see whether to insert the record into the history table or update the record in the history table; (3) close the cursor after the operation.

Insert a record into the history table or update the record in the history table: use the [delivery man, supermarket] joint key to determine that if there is a [delivery man, supermarket] joint key in history, the amount of delivery money in current will be added to the previous record; if there is no [delivery man, supermarket] joint key in history, a record will be added to history.

For the time being, it is just a test example, but it can continue to be improved.

Stored procedure testMerge

-- Procedure structure for testMerge

DROP PROCEDURE IF EXISTS `testMerge`

DELIMITER

CREATE DEFINER= `root` @ `localhost` PROCEDURE `testMerge` ()

BEGIN

-- you need to define variables that receive cursor data

DECLARE player_param VARCHAR (10)

DECLARE market_param VARCHAR (10)

DECLARE money_param INT

DECLARE exist_record INT

-- traversal data end flag

DECLARE done INT DEFAULT FALSE

Cursor, which needs to be defined after all other variables, otherwise a 1337 error will be reported

DECLARE record_list CURSOR FOR

SELECT

Player

Market

Money

FROM

Current

WHERE

Dateno

< 20190808; -- 将结束标志绑定到游标 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; -- 打开游标 OPEN record_list; -- 开始循环 read_loop: LOOP -- 提取游标里的数据，这里只有一个，多个的话也一样； FETCH record_list INTO player_param,market_param,money_param; -- 声明结束的时候 IF done THEN LEAVE read_loop; END IF; SELECT COUNT(*) INTO @exist_record FROM history WHERE player = player_param AND market = market_param; IF @exist_record = 0 THEN -- 这里做你想做的循环的事件 INSERT INTO history VALUES (player_param,market_param,money_param,0); END IF; IF @exist_record >

0 THEN

UPDATE history SET money = money + money_param WHERE player = player_param AND market = market_param

END IF

DELETE FROM current WHERE dateno = 20190807

END LOOP

-- close the cursor

CLOSE record_list

END

DELIMITER

Thank you for reading, the above is the content of "how to debug stored procedures in Navicat". After the study of this article, I believe you have a deeper understanding of how to debug stored procedures in Navicat, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.