MYSQL tutorial 14: basic operations on cursors

MYSQL entry 14: basic operations of a cursor http://www.bkjia.com/database/201212/173868.htmlMYSQL Entry 2: Search Using Regular Expressions http://www.bkjia.com/database/201212/173869.htmlMYSQL Entry 3: Full Text Search http://www.bkjia.com/database/201212/173873.htmlMYSQL Entry 4: MYSQL Data Types http://www.bkjia.com/database/201212/175536.htmlMYSQL Entry 5: MYSQL Character Set http://www.bkjia.com/database/201212/175541.htmlMYSQL Getting started 6: MYSQL Operators http://www.bkjia.com/database/201212/175862.htmlMYSQL Entry 7: MYSQL common functions http://www.bkjia.com/database/201212/175864.htmlMYSQL Step 8: basic database and table operations http://www.bkjia.com/database/201212/175867.htmlMYSQL 9: simple indexing operations http://www.bkjia.com/database/201212/176772.htmlMYSQL 10: basic view operations http://www.bkjia.com/database/201212/176775.htmlMYSQL Quick Start 11: Basic trigger operations http://www.bkjia.com/database/201212/176781.htmlMYSQL 12: basic operations of Stored Procedures http://www.bkjia.com/database/201212/177380.htmlMYSQL Step 13: basic operations for custom functions http://www.bkjia.com/database/201212/177382.html CURSOR Is a database query stored on the MySQL server. It is not a SELECT statement but a result set retrieved by this statement. After the cursor is stored, the application can scroll or browse the data as needed. The www.2cto.com cursor is mainly used for interactive applications. You need to scroll the data on the screen and browse or modify the data. To use a cursor, MySQL5 or a later version is required. 1. Before using a cursor, you must declare (define) it. Once declared, the cursor must be opened for use. For a cursor with data filled in, retrieve (retrieve) each row as needed. You must close the cursor when using the end cursor. Ii. Basic operations on the CURSOR 1. create the cursor declare cur_name cursor for select *****. Example: [SQL] mysql> delimiter // mysql> create procedure test_cur () -> begin-> declare mycur cursor-> for-> select userid, username from newname;-> end;-> // mysql> delimiter; 2. OPEN the cursor OPEN cur_name; mySQL executes queries when processing OPEN statements to store the Retrieved Data for browsing and scrolling. 3. CLOSE the cursor CLOSE cur_name; the CLOSE statement releases all internal memory and resources used by the cursor. After a cursor is closed, it cannot be used if it is not re-opened. However, the declared cursor does not need to be declared again. If you do not explicitly close the cursor, MySQL will automatically close it when it reaches the END statement. Example: [SQL] mysql> delimiter // mysql> create procedure test_cur ()-> begin-> declare mycur cursor-> for-> select userid, username from newname; -> open mycur;-> close mycur;-> end;-> // mysql> delimiter; 4. After the cursor data cursor is opened, you can use the FETCH statement to access each row. Example: [SQL] mysql> delimiter // mysql> create procedure test_cur ()-> begin-> declare done boolean default 0;-> declare cur_userid int; -> declare cur_birthday varchar (50);-> declare mycur cursor-> for-> select userid, year (birthday) from newname where birthday is not null; -> declare continue handler for sqlstate '000000' set done = 1;-> open mycur;-> repeat-> fetch mycur into cur_userid, cur_birthday; -> select cur_userid, cur_birthday;-> until done end repeat;-> close mycur;-> end;-> // mysql> delimiter; mysql> call test_cur; + ------------ + -------------- + | cur_userid | cur_birthday | + ------------ + -------------- + | 19 | 1988 | + ------------ + 1 row in set (0.00 sec) + ------------ + -------------- + | cur_userid | cur_birthday | + ------------ + -------------- + | 42 | 2008 | + ------------ + 1 row in set (0.01 sec)