What does cursor mean?

What does cursor mean?

Cursor

 

I. classification:

Static cursor

Explicit cursor (need to be clearly defined)

Implicit cursor (all dml statements are implicit cursors, and for loop is also implicit cursors)

Ref cursor (try not to use ref cursor. ref cursor can pass datasets between programs)

Strong type ref cursor (returned value in definition)

Weak type ref cursor (no return value in definition)

 

Ii. Several Status values of the cursor: (% variables of the cursor type)

% Found

% Notfound

% Isopen

% Rowcount: the row value that the current cursor traverses

 

3. Rough definition: Used to traverse the query result set and perform relevant operations on the rows in the result set in sequence;

 

Iv. Simple examples of various cursors

 

-- Explicit cursor ------------------------ loop declare cursor test_cursor is select * from dept; test _ dept % rowtype; -- it can also be written as test _ test_cursor % rowtype; [In this case, test_cursor is treated as a result set?] Begin if test_cursor % isopen then close test_cursor; end if; open test_cursor; loop fetch test_cursor into test _; exit when test_cursor % notfound; dbms_output.put_line (test _. deptno); end loop; dbms_output.put_line (test_cursor % rowcount); close test_cursor; end; -- implicit cursor for loop declare cursor test_cursor is select * from dept; -- test _ test_cursor % rowtype; -- this section can be ignored. The cursor variable can be directly used in the for loop. In for test _ in test_cursor loop dbms_output.put_line (test _. loc); end loop; end;/* Note: for cursors do not define the variable of the cursor type. for is followed by the variable of the cursor type */-- DML (update, insert, delete) begin update test_trans01 set B = 111 where a = 'a'; if SQL % rowcount <> 0 then dbms_output.put_line (SQL % rowcount | 'the row is updated! '); End if; end; -- strong type ref cursor -------------------- declare type test_cursor is ref cursor return test_trans01 % rowtype; test _ test_cursor; test _ % rowtype; begin open test _ for select * from test_trans01; loop fetch test _ into test __; exit when test _ % notfound; dbms_output.put_line (test __. a); end loop; close test _; end; -- weak type ref cursor ---------------------- declare type test_cursor is ref cursor; test _ Cursor; -- test _ % rowtype; [This way, the application reports an error.] test _ dept % rowtype; begin open test _ for select * from dept; loop fetch test _ into test __; exit when test _ % notfound; dbms_output.put_line (test __. dname); end loop; close test _; end; -- application instance ------------------------ for update/delete [used to modify or delete table data] declare cursor test_cursor is select * from test_trans01 for update; -- begin for test _ in test_cursor loop dbms_output.pu T_line (test _. a); if test _. a = 'A' then update test_trans01 set B = 1111 where current of test_cursor; dbms_output.put_line (test _. a | 'updated! '); Else null; end if; end loop; end; -- display cursor with parameters -- no available items are found at the moment, and may be available in complicated statement blocks, the parameters generated by receiving condition determination are the same as those directly written after where ..? Declare cursor test_cursor (dd number: = 2) is select * from test_trans01 where B> dd; -- the parameter variable cannot be limited to test _ test_cursor % rowtype; begin open test_cursor; loop fetch test_cursor into test _; exit when test_cursor % notfound; dbms_output.put_line (test _. b); end loop; close test_cursor; end;