Oracle cursor-for, loop, if combined application

First, the demand

When will Oracle cursors be used, and for, loops, if?

First look at such a requirement:

There is a student lecture form t_teaching, each student has several courses:

Primary Key ID (self-increment)	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, there are students who have incomplete courses (which should have 3 courses for each student), how to retrieve the incomplete students, add courses to the students, and ask to solve the problem quickly.

Second, analysis

We make a step-by-step review of our needs:

1, we do not know which student curriculum is not complete, so need to cycle to judge each student

Typically used for loops: for user_id in User_ids

2, cycle judgment each student will get all the school number

Select:select user_id from T_teaching GROUP by user_id;

3, have a school number, judge whether the student lacks a course, and then add this course

(1) Select:select count (*) from t_teaching where [email protected]_id and [email protected]_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 process came out, and then look at the details:

We pass SELECT. GROUP by gets all the numbers in the lecture list and then loops through the numbers, the key is select. The GROUP by comes out how to loop fetch.

Third, the method

"Cursors" are available in Oracle

cursor is used to process multiple rows of records retrieved from the database (using the　　 SELECT statement). Cursors allow the program to process and iterate through the entire set of records returned one at a time.

That's what we want!!

In conjunction with a For loop, we can use a for loop cursor in the following format:

--(1) Define cursor-    -keyword cursor    cursor x is SELECT statement--(2) define cursor variable y x%rowtype--(3) Use the For loop for this cursor for    y in x loop
   --logic processing    end loop;

Below we are using SQL stored procedures to fully implement the above requirements

Declare       --definition type      cursor t_tea       is       select user_id from t_teaching GROUP by user_id;       --Define a cursor variable      t_row t_tea%rowtype;       --Define a temporary variable of type number V_count number; Begin for      t_row in T_tea Loop select COUNT (*) to 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, which show cursors and implicit cursors, and how other cursors can be used here, using in-depth research: http://www.cnblogs.com/sc-xx/archive/ 2011/12/03/2275084.html

Oracle cursor-for, loop, if combined application