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