Common usage of cursor in oracle Database

1. What is a cursor is a PLSQL control structure that allows you to control the display of SQL statements to facilitate the processing of table data one by one. 2. Display cursor by cursor type: Declaredandnamedbytheprogrammer implicit cursor: declaredforallDMLandPLSQLSELECTstatements3: % FOUND: Ev

1. What is a cursor is a PL/SQL control structure that allows you to control the display of SQL statements to facilitate the processing of table data one by one. 2. cursor classification display cursor: declared and named by the programmer implicit cursor: Declared for all DML and PL/SQL SELECT statements 3. attributes of the cursor: % FOUND: Ev

1. What is a cursor?

A cursor is a PL/SQL control structure that allows you to control the display of SQL statements to facilitate the processing of table data one by one.

2. cursor Classification

Display cursor: Declared and named by the programmer

Implicit cursor: Declared for all DML and PL/SQL SELECT statements



3. cursor attributes:

% FOUND: Evaluates to TRUE if the most recent SQL statement affects one or more rows

% NOTFOUND: opposite to % FOUND

% ISOPEN: A boolean value. If the cursor is opened, the value is TRUE. If the cursor is closed, the value is FALSE. for implicit cursors, SQL % ISOPEN is always FALSE. This is because the implicit cursors are opened when DML statements are executed and are immediately closed at the end.

% ROWCOUNT: Number of records affected by the most recent SQL statement



Note: dbms_output.put_line (); The boolean type cannot be printed. solution:

If B then

Dbms_output.put_line ('B = true ');

End if;



Or:

Declare

B boolean;

Begin

B: = true;

Dbms_output.put_line (case when B then 'true' else 'false' end ));

End;



For null, NVL () or decode () is used for processing.



Display cursor: manual open and close are required

For example:

DECLARE

CURSOR mycursor IS

SELECT * FROM dept;

Myrecord dept % ROWTYPE;

BEGIN

OPEN mycursor;

FETCH mycursor INTO myrecord;

WHILE mycursor % FOUND LOOP

DBMS_OUTPUT.PUT_LINE (myrecord. deptno | ''| myrecord. dname |'' | myrecord. loc );

FETCH mycursor INTO myrecord;

End loop;

CLOSE mycursor;

END;

Note: before performing a while loop, you must first perform a FETCH .. INTO operation. Otherwise, % FOUND always returns false.



Parameter-based cursor:

DECLARE

CURSOR mycursor (num varchar2) IS

SELECT * from dept where deptno = num;

Myrecord dept % ROWTYPE;

BEGIN

OPEN mycursor (10 );

LOOP

FETCH mycursor INTO myrecord;

Exit when mycursor % NOTFOUND;

DBMS_OUTPUT.PUT_LINE ('deptnum = '| myrecord. deptno | 'deptname =' | myrecord. dname );

End loop;

CLOSE mycursor;

END;



FOR loop operation cursor

When you use a FOR loop to read a cursor, you do not need to display the declarative variable to receive the result, or manually open or close the cursor. FOR example:

DECLARE

CURSOR mycursor (num varchar2) IS

SELECT * from dept where deptno = num;

BEGIN

FOR cur IN mycursor (10) LOOP

DBMS_OUTPUT.PUT_LINE ('deptnnum = '| cur. deptno | 'deptname =' | cur. dname );

End loop;

END;



Note: parameters in PL/SQL only need to provide the type, and do not need to provide the length or accuracy.

When the cursor value is directly read to the variable, the number of variables should be the same as the number of columns in the result set pointed to by the cursor. For example, if there are two columns in the result set, the number of variables corresponding to FETCH... INTO should also be two.

DECLARE

D_no number;

D_name varchar2 (10 );

CURSOR mycursor (num varchar2) IS

SELECT deptno, dname from dept where deptno = num;

BEGIN

OPEN mycursor (10 );

FETCH mycursor INTO d_no, d_name;

LOOP

DBMS_OUTPUT.PUT_LINE (d_no | ''| d_name );

FETCH mycursor INTO d_no, d_name;

Exit when mycursor % NOTFOUND;

End loop;

CLOSE mycursor;

END;

/



The initial value of % ROWCOUNT is null. When FETCH... INTO is used to extract a piece of data from the cursor, the value of ROWCOUNT is incremented by 1, not the number of rows that identify the result set.

For example:

DECLARE

D_name varchar2 (10 );

CURSOR mycursor IS

SELECT dname from dept;

BEGIN

OPEN mycursor;

LOOP

FETCH mycursor INTO d_name;

Exit when mycursor % NOTFOUND;

DBMS_OUTPUT.PUT_LINE (mycursor % ROWCOUNT );

End loop;

CLOSE mycursor;

END;

There are four records in the result set. The output result is: 1 2 3 4.



Updatable data cursor

To modify data while using a cursor, you must add the for update keyword when declaring the cursor.

For example:

DECLARE

D_name VARCHAR2 (20 );

CURSOR mycursor IS

SELECT dname FROM dept for update;

BEGIN

OPEN mycursor;

LOOP

FETCH mycursor INTO d_name;

Exit when mycursor % NOTFOUND;

UPDATE dept SET dname = RTRIM (dname, '_ t') where current of mycursor;

End loop;

CLOSE mycursor;

END;

Current of + cursor name: Get the row to which the cursor is currently directed

RTRIM (dname, '_ t'): LTRIM and RTRIM implement string filtering (not only remove spaces)





Implicit cursor: Do not use DECLARE to display the declared cursor.

For example:

BEGIN

FOR cur IN (SELECT dname FROM dept) LOOP

DBMS_OUTPUT.PUT_LINE (cur. dname );

End loop;

END;



1. cursor with Parameters

Declare
Cursor cur_my (mv number) is select * from Person where no Begin
For tem in cur_my (4) loop
DBMS_OUTPUT.put_line ('name: '| tem. name );
End loop;
End;

2. Set the reference cursor
Declare
Temp_row Person % rowtype;
Type my_type is ref cursor;
Cur_my my_type;
Begin
Open cur_my for 'select * from Person ';
Loop
Fetch cur_my into temp_row;
Exit when cur_my % notfound;
DBMS_OUTPUT.put_line ('name: '| temp_row.name );
End loop;
Close cur_my;
End;

3. for loop cursor

DECLARE
V_id Integer;
V_name varchar2 (50 );
V_age Integer;
Cursor cur_mycursor is select id, name, age from Users;
BEGIN
For temp in cur_mycursor loop
V_id: = temp. id;
V_name: = temp. name;
V_age: = temp. age;
Dbms_output.put_line ('Id: '| v_id | 'name:' | v_name | 'Age: '| v_age );
End loop;
/** Dbms_output.put_line ('number of all records: '| cur_mycursor % rowcount |! ');*/
END;

4. Standardized loop cursor

DECLARE
V_id Integer;
V_name varchar2 (50 );
V_age Integer;
Cursor cur_mycursors is select id, name, age from Users;
BEGIN
OPEN cur_mycursors;
Dbms_output.put_line ('number of all records: '| cur_mycursors % rowcount |! ');
LOOP
FETCH cur_mycursors INTO v_id, v_name, v_age;
Dbms_output.put_line ('Id: '| v_id | 'name:' | v_name | 'Age: '| v_age );
IF cur_mycursors % NOTFOUND THEN
EXIT;
End if;
End loop;
Dbms_output.put_line ('number of all records: '| cur_mycursors % rowcount |! ');
CLOSE cur_mycursors;
END;