Oracle cursor-for, loop, if combined application __oracle

I. Demand

when will an Oracle cursor be used, and the For, loop, if?

Let's look at one such requirement:

There is a Student class table t_teaching, each student has several courses:

Primary Key ID (self-increasing)	Course Number course_id	School Number user_id
1	01	201501
2	02	201501
3	03	201501
4	01	201502
5	01	201503
6	01	201504
7	02	201504
...	...	...

But for some reason, some students have incomplete courses (which should have 3 subjects each), how to retrieve the incomplete students, add the course to these students, and ask to solve the problem quickly.

Second, analysis

We have a step-by-step review of requirements:

1, we do not know which student curriculum is not full, so need to cycle the judgment of each student

Usually with a For loop: for user_id in User_ids

2, circular judge each student will get all the school number

Select:select user_id from T_teaching GROUP by user_id;

3, with the school number, to determine whether the students lack of courses, and then add this course

(1) Select:select count (*) from t_teaching where user_id= @USER_ID and course_id= @COURSE_ID;

(2) If Else judgment

(3) Insert:insert into t_teaching (course_id,user_id) VALUES (@COURSE_ID, @USER_ID)

Through the analysis of the general flow out, and then look at the details:

We passed the Select. GROUP by gets all the school numbers in the lecture table, and then iterates through the numbers, the key is select. GROUP by out how to loop the fetch.

third, the method

"Cursors" are provided in Oracle

　　What cursors are used to process multiple rows of records retrieved from the database (using the SELECT statement). With cursors, a program can process and traverse the entire set of records returned one at a time.

That's what we want.

With the For loop, we can use a for loop cursor with the following format:

--(1) Define cursor
    --keyword cursor
    cursor x is SELECT statement

---(2) define cursor variable
y x%rowtype

 -(3) use a For loop to use this cursor
    For y in X loop

      --Logical processing end

    loop;

The following is a complete implementation of the above requirements through SQL stored procedures

Declare
       --defines the type
      cursor T_tea is
       select user_id from t_teaching GROUP by user_id;
       --Define a cursor variable
      t_row t_tea%rowtype;
       --Defines a temporary variable v_count number of type number
;
 The begin for
      T_row in T_tea loop 
select COUNT (*) into the v_count from t_teaching where User_id=t_row.　　　 　USER_ID and course_id=02;
If V_count = 0 Then  INSERT INTO t_teaching (course_id,user_id) VALUES (02,t_row.　　　 　USER_ID);
End If;
End Loop;
End　　　 　　

Iv. Summary

　　

Cursor loops in Oracle are not only for loops, there are two types of cursors available in Oracle, cursors and implicit cursors, and the way other cursors can be used here. In-depth research: http://www.cnblogs.com/sc-xx/archive/ 2011/12/03/2275084.html