Oracle Learning (quad) cursors

• An explicit cursor
• Implicit cursors: If you operate with a SELECT statement in a PL/SQL program segment, PL/SQL implicitly handles the cursor definition, which is an implicit cursor. This type of cursor does not need to be declared as explicitly or turned off.

• 1 CREATE OR REPLACE PROCEDURECX_XM2(In_xhinch Char, OUT_XM outvarchar2)3  as4 BEGIN5      SELECTName intoOUT_XM6               fromXSB7              WHERESchool Number=In_xh andROWNUM=1;8 Dbms_output. Put_Line (OUT_XM);9 ENDCX_XM;

  View Code
• Attention
1. Each implicit cursor must have an into
2. As with an explicit cursor, the number and type of target variables for the data to be received is consistent with the select list.
3. An implicit cursor can only return one row of data at a time, and must be checked when used, and the most common exceptions are "No_data_found" & "Too_many_rows".
4. To ensure that an implicit cursor returns only one row of data, it can be qualified with rowcount=1, which means that the first row of data is returned.
• Cursor FOR loop

  1  for < variable name >IN< cursor name >[(< parameter 1>[,< parameter 2> .... ])]loop2             statement segment 3END LOOP;

• Cursor variable REF CURSOR
  • Defining the cursor Type

    Type<cursor types name >iscursor[return< return type >]; eg:DECLAREtype xs_cur  is    CURSORRETURN XSB%ROWTYPE;

    View Code

    The REF cursor type can be either a strongly or weakly type. The strongly REF CURSOR type has a return type, while the weak type has no

    1 DECLARE 2     is CURSOR RETURN XSB%ROWTYPE;  /* strongly typed */ 3     is CURSOR;                                      /* Weak type */

    View Code

Oracle Learning (quad) cursors