Simple example of Oracle cursors

1. The concept and role of cursors

The cursor can manipulate the query's result set as the user would like to manipulate the array, and it can be visualized as a cursor with a change, and its actual line is a pointer to the oracle that holds the data query result set or data

In memory of the action set, this pointer can point to any location in the result set.

Classification: Static Cursors and ref cursors (not currently introduced). Static cursors are also divided into display cursors and implicit cursors, which are automatically managed by Oracle and do not need to be understood in detail just to know how to use them.

Display cursors: We are generally used. is to have a clear definition before use, usually associated with data query statements, return one or more rows of records.

To use steps: 1. Declaring a cursor 2. Open cursor 3. Extract data 4. Close cursor

Syntax structure: Simple syntax structure The following code example

1 DECLARE CURSORcursor_name2      --1. Declaring a cursor named cursor_name3       is          4         --the SQL statement associated with it5        SELECTID, NAME, score from TABLE ;6          --declares a variable that is the same as the name type of the data table field MyName7MyName Tablename.name%TYPE; 8         --declares a variable that can go to a row of records in a table9Myrow TableName%ROWTYPE; Ten     BEGIN          One      --2. Open the cursor A         OPENcursor_name; -      --3. Extracting Data -         FETCH CURSOR  intoMyrow; theDbms_output.put_line (myrow.name||'---'||myrow.id); -          -       --4. Close the cursor -         CLOSEcursor_name; +     END;

It is common to show that the cursor extracts no more than one piece of data, all of which need to traverse their result set to use the loop

Create a simple data table below and manipulate the data using the loop cursor

1 CREATE TABLECur_test_student (2        --Create a simple test table ID number, name, score, account, age3Sid Number(8)PRIMARY KEY,   4SnameVARCHAR2(Ten) not NULL,   5Score Number(8) not NULL,6SubjectVarchar2( -),7Sage Number(8)8);

Examples of loop tour signs:

　　

1 DECLARE CURSORCur_score2 --declaring a cursor querying the name and score of all students in an account3  is4  SELECTSname,score fromCur_test_studentWHERESubject='language'; 5Namee Cur_test_student.sname%TYPE;6SCO Cur_test_student.score%TYPE;7     8  BEGIN9    OPENCur_score;Ten LOOP One      --extract data saved to two variables A        FETCHCur_score intoNamee,sco; -        --An implicit cursor determines when no data is exiting the loop -        EXIT  whenCur_score%NOTFOUND; theDbms_output.put_line (Namee||'----------'||SCO); -         -      ENDLOOP; -    CLOSECur_score; +  END;

For loop cursors:

The cursor for loop is the simplest way to use cursors in a PL/SQL block, simplifying the handling of cursors. When using a cursor for loop, Oracle implicitly opens the cursor, extracts the cursor data, and closes the cursor.

1 DECLARE CURSORCur_score2 --declaring a cursor querying the name and score of all students in an account3  is4  SELECTSname,score fromCur_test_studentWHERESubject='language'; 5    BEGIN6   forStudent_recodeinchCur_score LOOP7  8Dbms_output.put_line (student_recode.sname||'---'||student_recode.score);9  Ten   ENDLOOP; One  END;

Note the differences between the two types of writes: There are no four operations, such as cursor closing cursors, in the cursors for the For loop, and many of the work Oracle implicitly helps us perform and does not need to determine the appropriate exit.

This is more straightforward than simple.

Cursors with parameters

Cursors can be used to specify parameters that can be passed in multiple parameters for convenient multi-condition queries, and can be easily used in stored procedures.

　　

1 DECLARE CURSORCur_score (SubVARCHAR2) is2 3     SELECTSname, Score fromCur_test_studentWHERESubject=Sub;4     5     BEGIN6         forScore_recodeinchCur_score ('language') LOOP7           8Dbms_output.put_line (score_recode.sname||'---'||score_recode.score);9     Ten      ENDLOOP; One     END;

--------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- ---------------------------------------------------

　　

　　　　

　　　　

Simple example of Oracle cursors