Oracle -- plsql cursor creation and use

Cursor Is a workspace opened up by the Oracle System in the memory. It stores the query results returned by the SELECT statement ., PLSQL implicitly creates and automatically manages this game

Cursor Is a workspace opened up by the Oracle System in the memory. It stores the query results returned by the SELECT statement ., PL/SQL implicitly creates and automatically manages this game

Why Oracle cursor:

First look at this:

DECLARE

V_empno emp. empno % type;

V_ename emp. ename % type;

BEGIN

SELECT empno, ename

Into v_ename, v_empno

FROM emp;

Dbms_output.put_line (v_empno | ''| v_ename );

END;

This SELECT statement may return multiple records, so this assignment statement may be incorrect. Therefore, the cursor can be used.

What is a cursor?

Cursor Is a workspace opened up by the Oracle System in the memory. It stores the query results returned by the SELECT statement.

Cursor category:

Implicit cursor: PL/SQL implicitly creates and automatically manages this cursor. Implicit cursors are also called SQL cursors. You cannot explicitly execute OPEN, CLOSE, and FETCH statements for SQL cursors. However, you can use the cursor attribute to obtain information from the recently executed SQL statements.

Explicit cursor: it is explicitly explained and controlled by the programmer. It is used to extract multiple rows of data from the table and process multiple rows of data with one row.

Implicit cursor:

Internal declaration by Oracle

Auto-manage cursor by Oracle

You can use the cursor attribute to obtain information from the recently executed SQL statement.

Used to process DML statements and query that returns a single row

Implicit cursor attributes:

Implicit cursor example:

VARIABLE rows_deleted VARCHAR2 (30)

DECLARE

V_deptno NUMBER: = 20;

BEGIN

Delete from emp

WHERE deptno = v_deptno;

: Rows_deleted: = (SQL % ROWCOUNT | 'rows deleted .');

END;

/

Returns the number of deleted rows.

Explicit cursor: it is explicitly declared by the user, and multiple rows of records are returned by the query.

When a cursor is used, the query result of a select statement can be a single record, multiple records, or Zero record.

A pointer exists in the cursor workspace. In the initial state, it points to the first record of the query result.

To access all records of the query results, you can use the FETCH statement to move the pointer.

Use a cursor to define a cursor, open a cursor, extract data, and close the cursor.

For more information about Oracle, see the Oracle topic page? Tid = 12