Oracle---an explicit cursor

Classification of a cursor

Two types of cursors are available in Oracle: Static cursors and dynamic cursors.

1. A static cursor is a cursor that knows its SELECT statement at compile time. Static cursors are also divided into two types, implicit and explicit cursors.

2. You can use a REF CURSOR (reference cursor) and a cursor variable when the user needs to be able to determine the query used for the cursor until run time. A cursor variable must be declared using a reference cursor . Reference cursors can also be categorized into two types: strongly-typed ref cursors and weakly-typed ref cursors.

Use of two cursors

1. Use of explicit cursors

4 Steps to use an explicit cursor:

1> declaring cursors

Syntax: CURSOR cursor_name [(Parameter[,parameter] ...)

[RETURN Return_type] is select_statement;

Cursor_name: The name of the cursor.

Parameter: Used to specify input parameters for the cursor. You cannot use a length constraint when specifying a data type .

Return_type: Defines the type of row that the cursor extracts.

Select_statement: The query statement defined by the cursor.

2> Opening Cursors

Syntax: OPEN cursor_name[(parameters)];

3> Extracting Cursors

Syntax: FETCH cursor_name into variables;

Variables: variable name.

4> closing Cursors

Syntax: CLOSE cursor_name;

An example of an explicit cursor use:

1 --an explicit cursor 12 DECLARE3V_name Tb_student.stu_name%TYPE;4V_sex Tb_student.stu_sex%TYPE;5V_email Tb_student.stu_email%TYPE;6   CURSORCur_stu is7   SELECTStu_name,stu_sex,stu_email fromtb_student;8 BEGIN9   --Open CursorTen   OPENCur_stu; One LOOP A     FETCHCur_stu intoV_name,v_sex,v_email; -     EXIT  whenCur_stu%NOTFOUND; -Dbms_output.put_line (v_name||'  '||V_sex||'  '||v_email); the   ENDLOOP; -   CLOSECur_stu; - END;

2. Explicit Cursor Properties

1>%found:%found Returns true only if the DML statement affects one or more rows.

2>%notfound: Contrary to the role of%found. Returns true if the DML statement does not affect any rows.

3>%rowcount: Returns the number of rows affected by the DML statement. Returns 0 if no rows are affected.

4>%isopen: Returns the value of whether the cursor is open. After the SQL statement is executed, Oracle automatically closes the SQL cursor, and the%isopen property of all implicit cursors is always false.

Oracle---an explicit cursor