MySQL-5.7 Cursors and declare

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.