Stored Procedure 4-cursor: Stored Procedure cursor

Stored Procedure 4-cursor: Stored Procedure cursor

Cursors are used in stored procedures and functions. The syntax is like in an embedded SQL statement. The cursor is read-only and non-rolling. It can only be traversed in one direction. It cannot move between records or skip some records. Therefore, you should move to the next record after each reading. The cursor must be declared before the handler is declared, and variables and conditions must be declared before the cursor or handler is declared.

I. cursor

1. Definition

Declare cursor name cursor for query 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.

2. OPEN statements

OPEN cursor name

This statement opens the previously declared cursor.

3. FETCH statement

FETCH cursor name INTO variable [, variable 2]...

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

4. CLOSE statement

CLOSE cursor name

This statement closes the previously opened cursor. If it is not explicitly closed, the cursor is closed at the end of the declared compound statement.

Ii. Instances

The table structure is as follows:

-- Optimize Table structure for person -- -------------------------- drop table if exists 'person '; create table 'person' ('id' int (11) not null AUTO_INCREMENT, 'username' varchar (255) default null, 'age' int (11) default null, 'Password' varchar (255) default null, primary key ('id ')) ENGINE = InnoDB AUTO_INCREMENT = 11 default charset = utf8; -- returns Records of person -- -------------------------- insert into 'person 'values ('1', 'zhangsan', '21', null ); insert into 'person 'values ('2', 'Lee 4', '23', null); insert into 'person 'values ('3', 'wang 5 ', '22', null); insert into 'person 'values ('4', 'zhangsan', '22', 'fdsafds '); insert into 'person 'values ('8', 'hangsan ', '22', 'fdsafds'); insert into 'people' VALUES ('9', 'hangsan ', '22', 'fdsafds '); insert into 'others' VALUES ('10', 'wangw', '23', 'password123 ');

1. Use REPEAT for a cursor

Drop procedure if exists proc_test_cursor; -- age of all people and create procedure proc_test_cursor (OUT total INT (11) begin declare t int default 0; DECLARE done int default 0; DECLARE pcursor cursor for select age FROM person; declare continue handler for sqlstate '000000' SET done = 1; -- the CURSOR referenced in the FETCH statement is placed after the last row of the result table. SET total = 0; OPEN pcursor; repeat fetch pcursor INTO t; if not done THEN -- SET total = total + t; end if; UNTIL done end repeat; CLOSE pcursor; END; CALL proc_test_cursor (@ total); SELECT @ total; select sum (age) FROM person;

 

If the results of the two queries are the same, the cursor runs normally.

2. Use the while

Drop procedure if exists proc_test_cursor_while; -- age where id is less than a certain value and create procedure proc_test_cursor_while (IN uid INT (11), OUT total INT (11) begin declare t int default 0; DECLARE done int default 0; DECLARE pcursor cursor for select age FROM person WHERE id <uid; declare continue handler for sqlstate '000000' SET done = 1; -- The cursor referenced in the FETCH statement is placed after the last row of the result table. SET total = 0; OPEN pcursor; WHILE (NOT done) do fetch pcursor INTO t; IF (NOT done) then set total = total + t; end if; end WHILE; CLOSE pcursor; END; CALL proc_test_cursor_while (3, @ total); SELECT @ total; select sum (age) FROM person where id <3;

If the results of the two queries are the same, the cursor runs normally.

3. Use the update statement in the cursor

Drop procedure if exists proc_test_cursor_update; -- add a certain number of ages plus the create procedure proc_test_cursor_update (IN avgage INT (11) begin declare num int default 0; DECLARE t int default 0; DECLARE done int default 0; DECLARE pcursor cursor for select id, age FROM person; declare continue handler for sqlstate '000000' SET done = 1; -- The cursor referenced in the FETCH statement is placed after the last row of the result table. OPEN pcursor; repeat fetch pcursor INTO num, t; if not done THEN -- and record IF t> avgage THEN -- age greater than the input age value UPDATE person SET age = age + 5 where id = num; end if; UNTIL done end repeat; CLOSE pcursor; END; SET @ uage = 20; SELECT id, username, age FROM person where age> @ uage; CALL proc_test_cursor_update (@ uage); SELECT id, username, age FROM person where age> @ uage;

If the results of the two queries are the same, the cursor runs normally.

How to use a cursor in a stored procedure

ALTER proc [dbo]. [stored procedure name]
As
Begin
Declare cursor name cursor for select column name from table name where condition -- first declare that the cursor points to the query result, one column, or multiple columns can be customized
Declare variable name varchar (400) -- store the obtained value
Open cursor name -- enable cursor
While @ FETCH_STATUS = 0 -- Value
Begin
Fetch next FROM cursor name into variable name -- this will point the cursor to the next row, and the obtained first row value will be passed to the variable.
-------------------------------------------
-- Operations to be performed, such as modifying fields in a table
Update table name
Set column name = Value
Where (modifying columns in a table) = variable name
-------------------------------------------
End
Close cursor name -- close the cursor

Deallocate cursor name -- release cursor
End

Temporary table cursor in Stored Procedure

Prepare table t4 and table test_t4
T4 has the same test_t4 structure as t4 but has no data.

[TEST1 @ orcl #13-7 month-10] SQL> select * from t4;

ID SEX
------------------------------
1 male
2 female
3 eunuch

[TEST1 @ orcl #13-7 month-10] SQL> desc test_t4;
Is the name empty? Type
-----------------------------------------------------------------------------
ID NUMBER
SEX VARCHAR2 (20)

Tested:

Create or replace procedure p_test
Is
V_ SQL varchar2 (30000 );
V_tmptable varchar2 (30 );
V_row t4 % rowtype;
Cursor c is (select * from t4 );
Begin
V_tmptable: = 't3 _ tmp_t4 ';
V_ SQL: = 'Create global temporary table' | v_tmptable | '(id number (4), sex varchar2 (20 ))';
Execute immediate v_ SQL;

Open c;
Loop
Exit when c % notfound;
Fetch c into v_row;
V_ SQL: = 'insert' | v_tmptable | 'values ('| v_row.id |', ''' | v_row.sex | ''')';
Execute immediate v_ SQL;
End loop;
Close c;

-- Verify whether data exists in the temporary table (this temporary table is a transaction-level temporary table, which is cleared once transaction data is submitted, so the temporary table data is inserted into the real table)

Execute immediate 'insert into test_t4 select * from' | v_tmptable;
Commit;
End p_test;

View results:
[TEST1 @ orcl #13-7 month-10] SQL> select * from test_t4;

ID SEX
------------------------------
1 male
2 female
3 eunuch
3 eunuch

This indicates that the data in the temporary table has been inserted into the test_t4 table, and the loop has a small problem... the remaining full text>