MySQL cursor usage, instance

After MySQL was acquired by Oracle, from the mysql-5.5, InnoDB as the default storage engine, is a major breakthrough. As a storage engine supporting transactions, InnoDB has the relevant RDBMS features, including ACID transaction support, data integrity (external), and disaster recovery capabilities.

If MySQL is used as a database, programmers will certainly write a lot in the future, such as stored procedures and functions. When writing something, the cursor must be indispensable. The following is a brief introduction.
1. What is cursor)

I personally think it is a cursor, which is an identifier used to identify where the data is obtained. You can also understand it as a subscript in an array.

Ii. cursor features

1. Read-only, cannot be updated.
2. Do not scroll
3. No sensitivity. No Sensitivity means the server can survive and cannot copy its result table.

Cursor must be declared before the handler is declared, and variables and conditions must be declared before the cursor or handler is declared.

3. Use cursor)

1. Declare a cursor

DeclareCursor_nameCursor for select_statement
This statement declares a cursor. You can also define multiple cursors in a subroutine, but each cursor in a block must have a unique name. A single operation is performed after the cursor is declared, but the SELECT statement cannot have an into clause.

2. cursor open statement

OpenCursor_name
This statement opens the previously declared cursor.

3. cursor fetch statement

FetchCursor_nameInto var_name [, var_name]...
This statement reads the next row with the specified open cursor (if there is a next row) and advances the cursor pointer.

4. cursor close statement

Close cursor_name
This statement closes the previously opened cursor.

4. Application Example

1. test tables and data

View copy print?

1. Mysql> show create table Users \ G; // create a test table
2. * *************************** 1. row ***************************
3. Table: Users
4. Create Table: Create Table 'users '(
5. 'Id' bigint (20) unsigned not null auto_increment,
6. 'User _ name' varchar (60) not null default '',
7. 'User _ pass' varchar (64) not null default '',
8. Primary Key ('id ')
9. ) Engine = InnoDB auto_increment = 1 default charset = utf8
10. 1 row in SET (0.00 Sec)
12. Mysql> select * from users; // Test Data
13. + ---- + ----------- +
14. | ID | user_name | user_pass |
15. + ---- + ----------- +
16. | 1 | tank |
17. | 2 | Zhang |
18. | 3 | ying |
19. | 4 | tank | Zhang |
20. + ---- + ----------- +
21. 4 rows in SET (0.00 Sec)

2. Test the Stored Procedure

View copy print?

1. Mysql> delimiter |
2. Mysql> Create procedure test_cursor (In Param int (10), out result varchar (90 ))
3. -> Begin
4. -> Declare name varchar (20 );
5. -> Declare pass varchar (20 );
6. -> Declare done int;
7. -> Declare cur_test cursor for select user_name, user_pass from test. users;
8. -> Declare continue handler for sqlstate '000000' set done = 1;
9. -> If Param then
10. -> Select concat_ws (',', user_name, user_pass) into result from test. Users where id = Param;
11. -> Else
12. -> Open cur_test;
13. -> Repeat
14. -> Fetch cur_test into name, pass;
15. -> Select concat_ws (',', result, name, pass) into result;
16. -> Until done end repeat;
17. -> Close cur_test;
18. -> End if;
19. -> End; |
20. Query OK, 0 rows affected (0.00 Sec)

Note,Do not use tab when indent the command line. Otherwise, a prompt is displayed,

Display all 749 possibilities? (Y or N)
? Mbrintersects
ABS mbroverlaps
... .......

Line 1: Create a stored procedure,NOTE: If I change out result varchar (90) to out result varchar, only one character is returned.
2 rows, start
Define a variable name.
Four rows define the variable pass
Five rows. Define the end ID.
Six rows define a cursorNote: Do not place the declare content in the IF statement. Otherwise, an error will be reported.
Line 7: If sqlstate is equal to 02000, set done to 1, that is, when data cannot be found
Rows 8, 10, 17, if
9 rows. extract the data according to the parameter and put it in the result.
Line 11: Open the cursor
12, 15 rows, repeat loop, the root PHP do while principle is the same
13 rows to retrieve data from the cursor.
14 rows to merge the data
16 rows, close the cursor
Rows 17, 18, and tags closed.

3. Test Results

View copy print?

1. Mysql> call test_cursor (3, @ test );
2. Query OK, 0 rows affected (0.00 Sec)
4. Mysql> select @ test; // It is similar to the one in PHP and is referenced
5. + ----------- +
6. | @ Test |
7. + ----------- +
8. | Ying, Ying |
9. + ----------- +
10. 1 row in SET (0.00 Sec)
12. Mysql> call test_cursor ('', @ test );
13. Query OK, 0 rows affected, 1 warning (0.00 Sec)
15. Mysql> select @ test;
16. + --------------------------------------------------------- +
17. | @ Test |
18. + --------------------------------------------------------- +
19. | Tank, Tank, Zhang, Zhang, Ying, Ying, Tank, Zhang, tank, and Zhang |
20. + --------------------------------------------------------- +
21. 1 row in SET (0.00 Sec)