MySQL-5.7 Cursors and declare

1.cursor Cursors

Used to declare a data set
The declaration of the cursor must be after the variable and the condition declaration, before the handler declaration

Cursor Properties:

• Insensitive: The server can or does not replicate its results
• Read only: Not updatable
• Non-scrollable: can only traverse in one direction, cannot skip rows

Instance:

CREATE PROCEDURE curdemo()BEGIN  DECLARE done INT DEFAULT FALSE;  DECLARE a CHAR(16);  DECLARE b,c INT;  DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;  DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done= TRUE;  OPEN cur1;  OPEN cur2;read_loop:LOOP    FETCH cur1 INTO a,b;    FETCH cur2 INTO c;    IF done THEN      LEAVE read_loop;    END IF;    IF b<c THEN      INSERT INTO test.t3 VALUES (a,b);    ELSE      INSERT INTO test.t3 VALUES (a,c);    END IF;  END LOOP;  CLOSE cur1;  CLOSE cur2;END;

Description

• Cursor Close is used to close the previously opened cursor;
• If you close an open cursor, MySQL will error;
• If you do not use this statement to close an open cursor in stored procedures and functions, the cursor will automatically close after the declared Begin...end statement block executes;

The cursor declare is used to declare a data collection that corresponds to a cursor and a specified cursor, typically a data collection that is a SELECT statement.

DECLARE cursor_name CURSOR FOR select_statement

Cursor fetch is used to get the next row of data for the specified data set of the cursor and assign each field value to the subsequent variable

FETCH [[NEXT] FROM] cursor_name INTO var_name [, var_name] ...

• The fields in the dataset need to correspond to the variable one by one defined in the INTO statement
• Returns not FOUND after the data in the data set has been fetch

2.Declare Condition Statement

Naming a specific error condition, which can be specified in the Declare...handler processing method
Grammar:

DECLARE condition_name CONDITION FOR condition_valuecondition_value:mysql_error_code  | SQLSTATE [VALUE] sqlstate_value

Description
CONDITION_VALUE specifies a specific error condition in the following two forms:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘v1 int default 5;

• Mysql_err_code represents the integer of MySQL error code
• Sqlstate_value represents the state of a statement expressed in MySQL with a 5-bit string

DECLARE CONTINUE HANDLER FOR 1051BEGIN-- body of handlerEND;declare no_such_table condition for 1051;declare continue handler for no_such_tablebegin--body of handlerend;declare no_such_table condition for sqlstate ‘42S02‘declare continue handler for no_such_tablebegin--body of handlerend;

3.Declare Handler Statement

Grammar:

DECLARE handler_action HANDLER    FOR condition_value [, condition_value] ...    statementhandler_action:CONTINUE  | EXIT  | UNDOcondition_value:mysql_error_code  | SQLSTATE [VALUE] sqlstate_value  | condition_name  | SQLWARNING  | NOT FOUND  | SQLEXCEPTION

• Used to declare a handler to handle one or more special conditions that trigger the execution of a SQL statement when one of the conditions is satisfied;
• SQL can make a simple statement, or it can be a begin...end composed of multiple statements;

Handler_action declares what should be done after the SQL statement is executed:

• Continue means that the stored procedure or function continues to execute
• Exit represents the Begin...end statement block that exits declaring this handler
• Undo parameter is not supported

The Condition_value values are as follows:

• Mysql_err_code represents the integer of MySQL error code;
• Sqlstate_value represents the state of a statement in MySQL with a 5-bit string;
• Condition_name represents the name previously declared in the Declare...condition statement;
• SQLWarning represents all warning messages, i.e. all errors in the beginning of the SQLState 01;
• Not found means that the data is not checked or not found, that is, all errors in the beginning of the SQLState 02;
• SqlException represents all error messages

Instance:

DECLARE CONTINUE HANDLER FOR 1051  BEGIN    -- body of handler  END;DECLARE CONTINUE HANDLER FOR SQLSTATE ‘42S02‘  BEGIN    -- body of handler  END;DECLARE CONTINUE HANDLER FOR SQLWARNING  BEGIN    -- body of handler  END;DECLARE CONTINUE HANDLER FOR NOT FOUND  BEGIN    -- body of handler  END;DECLARE CONTINUE HANDLER FOR SQLEXCEPTION  BEGIN    -- body of handler  END;

When conditon occurs but handler is not declared, stored procedures and functions are processed according to the following rules:

• If an sqlexception error occurs, execution exit exits;
• If a sqlwarning warning occurs, the execution continue continues execution;
• In the case of not found, the execution continue continues execution;

Instance:

mysql> CREATE TABLE test.t(s1 INT, PRIMARY KEY (s1));Query OK, 0 rows affected (0.35 sec)SQLSTATE ‘23000‘表示主键冲突mysql> delimiter //mysql> CREATE PROCEDURE handlerdemo()    -> BEGIN    -> DECLARE CONTINUE HANDLER FOR SQLSTATE ‘23000‘ SET @x2 = 1;    -> SET @x = 1;    -> INSERT INTO test.t VALUES (1);    -> SET @x = 2;    -> INSERT INTO test.t VALUES (1);    -> SET @x = 3;    -> END;    -> //Query OK, 0 rows affected (0.00 sec)mysql> call handlerdemo();    -> //Query OK, 0 rows affected (0.03 sec)mysql> delimiter ;mysql> select @x;+------+| @x   |+------+|    3 |+------+1 row in set (0.00 sec)

Instance:

mysql> delimiter //mysql> create procedure curdemo()    -> begin    -> declare done int default false;    -> declare a char(16);    -> declare b,c int;    -> declare cur1 cursor for select id,data from t1;    -> declare cur2 cursor for select i from test.t2;    -> declare continue handler for not found set done = true;    -> open cur1;    -> open cur2;    -> read_loop:loop    -> fetch cur1 into a,b;    -> fetch cur2 into c;    -> if done then    -> leave read_loop;    -> end if;    -> if b<c then    -> insert into test.t3 values(a,b);    -> else    -> insert into test.t3 values(a,c);    -> end if;    -> end loop;    -> close cur1;    -> close cur2;    -> end//Query OK, 0 rows affected (0.00 sec)mysql> delimiter ;

MySQL-5.7 Cursors and declare