Oracle Cursor Summary

1. Declaring cursors

Declareteacher_id Number(5); Teacher_namevarchar2(5); Teacher_titlevarchar2( -); Teacher_sexChar(1);cursorTeacher_cur is    SelectTid,tiname,title,sex fromTeacherswhereTid<117;

2. Open the cursor

Open cursor name;

Declareteacher_id Number(5); Teacher_namevarchar2(5); Teacher_titlevarchar2( -); Teacher_sexChar(1);cursorTeacher_cur is    SelectTid,tiname,title,sex fromTeacherswhereTid<117;OpenTeacher_cur;

3. Extracting cursors

Fetch cursor name into variable list

Declareteacher_id Number(5); Teacher_namevarchar2(5); Teacher_titlevarchar2( -); Teacher_sexChar(1);cursorTeacher_cur is    SelectTid,tiname,title,sex fromTeacherswhereTid<117;Openteacher_cur;FetchTeacher_cur intoTid,tinme,title,sex;

4. Close the cursor

Close cursor Name

Declareteacher_id Number(5); Teacher_namevarchar2(5); Teacher_titlevarchar2( -); Teacher_sexChar(1);cursorTeacher_cur is    SelectTid,tiname,title,sex fromTeacherswhereTid<117;Openteacher_cur; FetchTeacher_cur intoTid,tinme,title,sex; LoopEXIT  when  notTeacher_cur%FUND; IFTeaher_sex= 'M'  Then            INSERT  intoMale_teachers (Tid,tname,title)VALUES(); ELSE             INSERT  intoFemale_teachers (Tid,tname,title)VALUES(Teacher_id,teacher_name,teacher_title); END IF; FETCHTeacher_cur intoTeacher_id,teacher_name,teacher_title,teacher_sex; ENDLOOP;CLOSEteacher_cur;END;

To use a display cursor:

1): Check the open state with the cursor name%isopen before use, only the value is true to use

2): Use the cursor every time to confirm whether the return is successful with%notfund,%fund

3): The corresponding variable number is consistent when extracting the cursor

4): The cursor must be closed to release resources

1.%fund whether a valid row was found or true otherwise false

Open teacher_cur; Fetch  into  exit when not teacher_cur%found; End Loop

Sql%fund

Delete  from Teachers     where tid=teacher_id; if SQL%then    insertintovalues(TID); Else    Insert  into Values (TID); End if;

2.%notfound

OPEN teacher_cur; FETCH  into Teacher_id,teacher_name,teacher_title,teacher_sex; LOOP    EXIT when teacher_cur%NOTFOUND; END LOOP

Sql%notfound

DELETE  from TEACHERS     WHERE = teacher_id; IF SQL%then    INSERTintoVALUES(TID); ELSE    INSERT  into VALUES (TID); END IF;

3.%rowcount

This property records the number of record rows that the cursor has extracted, and it can be understood as the line number where the current cursor is located, which is valid in the loop judgment

LOOP     FETCH  into teacher_id,teacher_name,teacher_title,teacher_sex;     EXIT  when teacher_cur%ROWCOUNT=ten; -- extract only 10 records     ...     END LOOP;

Using a For statement to control the loop of a cursor, the system implicitly defines a record with a data type of%rowcount, as a loop counter, and the hermit's open and close cursors

 for inch -- Teacher_record as the record name, implicitly the surrogate cursor Teacher_cur    INSERT  into TEMP VALUES (teacher_record,tid,teacher_record,tname,teacher_record,title,teacher_record.sex); END LOOP

4.%isopen

... ...

5. Parameter session cursors:

DECLARE--defining a cursor is a parameter with the cursor_id    CURSORTeacher_cur (cursor_id Number) is        SELECTTname,title,sex fromTEACHERSWHERETID=cursor_id;--Using ParametersBEGIN    OPENTeacher_cur (My_tid);--with the parametersLOOPFETCHTeacher_cur intoTeacher_name,teacher_title,teacher_sex; EXIT  whenTeacher_cur%NOTFOUND; ...    ENDLOOP; CLOSEteacher_cur;END;

Oracle Cursor Summary