Application of Oracle cursor-for, loop, and if

Application of Oracle cursor-for, loop, and if

I. Requirements

When Will Oracle cursors and the for, loop, and if statements be used?

Let's look at this requirement first:

There is a student teaching table T_TEACHING. Each student has several courses:

Primary Key ID (auto-increment)	Course No. COURSE_ID	Student ID 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 (each student should have three courses). How can we retrieve incomplete students and add courses to these students, it is also required to be able to solve this problem quickly.

Ii. Analysis

We will sort out the requirements step by step:

1. We do not know which student courses are incomplete, so we need to judge each student cyclically.

Usually use for loop: for USER_ID in USER_IDS

2. Judge cyclically that each student will obtain all student IDs.

Select: select USER_ID from T_TEACHING group by USER_ID;

3. With the student ID, determine whether the student lacks a course and then add the 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)

After analyzing the general process, let's look at the details:

We use select... group by to obtain all student numbers in the teaching table, and then obtain these student numbers cyclically. The key is how to obtain them cyclically After select... group by is obtained.

Iii. Methods

Provides a "cursor" in oracle"

What is a cursor? A cursor is used to process multiple rows of records retrieved from the database (using the SELECT statement ). With the cursor, the program can process and traverse the entire record set returned by a retrieval one by one.

This is what we want !!

Combined with the for loop, we can use the for loop cursor in the following format:

-- (1) define a cursor
-- Keyword: cursor
Cursor x is select statement

& Nbsp; -- (2) define the cursor variable
Y x % rowtype

-- (3) use the cursor for a for Loop
For y in x loop

-- Logical Processing

End loop;

The following describes how to fully implement the above requirements through the SQL stored procedure.

Declare
-- Define 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 the number type
V_count number;
Begin
For t_row in t_tea loop
Select count (*) into 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

In Oracle, the cursor loop is not just a for loop. Oracle provides two types of cursors to display cursors and implicit cursors. Other cursors can be used here for further research: