ORACLE游标的应用

在oracle数据库中，可以使用游标浏览数据、更新数据和删除数据，接下来列举以几个简单的例子

通过使用游标既可以逐行检索结果集中的记录，又可以更新或删除当前游标行的数据如果要通过游标更新或删除数据，在定义游标时必须要带有FOR UPDATE子句其语句格式如下：

 

CURSOR CURSOR_NAME IS SELECT_STATEMENT
	FOR UPDATE[ OF COLUMN_REFERENCE]  [NOWAIT];

 

1.浏览数据

 

SET SERVEROUTPUT ON 
<span style="white-space:pre">	</span>DECLARE 
<span style="white-space:pre">		</span>--声明变量和游标
<span style="white-space:pre">		</span>V_SPECIALTY STUDENTS.SPECIALTY%TYPE;
<span style="white-space:pre">		</span>V_SNAME STUDENTS.NAME%TYPE;
<span style="white-space:pre">		</span>V_DOB STUDENTS.DOB%TYPE;
<span style="white-space:pre">	</span>
<span style="white-space:pre">		</span>CURSOR STUDENTS_CUR
<span style="white-space:pre">		</span>IS 
<span style="white-space:pre">			</span>SELECT NAME,SPECIALTY 
<span style="white-space:pre">				</span>FROM STUDENTS
<span style="white-space:pre">				</span>WHERE SPECIALTY=V_SPECIALTY;
<span style="white-space:pre">				</span>
<span style="white-space:pre">	</span>BEGIN
<span style="white-space:pre">		</span>V_SPECIALTY:='&SPECIALTY';
<span style="white-space:pre">		</span>OPEN STUDENTS_CUR;
<span style="white-space:pre">		</span>DBMS_OUTPUT.PUT_LINE('学生姓名<span style="white-space:pre">	</span>出生日期');
<span style="white-space:pre">		</span>LOOP
<span style="white-space:pre">			</span>FETCH STUDENTS_CUR INTO V_SNAME,V_DOB;
<span style="white-space:pre">			</span>EXIT WHEN STUDENTS_CUR%NOTFOUND;
<span style="white-space:pre">			</span>DBMS_OUTPUT.PUT_LINE(V_SNAME||'<span style="white-space:pre">	</span>'||V_DOB);
<span style="white-space:pre">		</span>END LOOP;
<span style="white-space:pre">		</span>CLOSE STUDENTS_CUR;
<span style="white-space:pre">		</span>
<span style="white-space:pre">	</span>END;
/

 

 

</pre><p></p>2.更新数据<p>update语句的格式</p><p></p><pre name="code" class="sql">UPDATE TABLE_NAME SET ... WHERE CURRENT OF CURSOR_NAME;

DECLARE 
	--声明变量和游标
	V_TITLE TEACHERS.TITLE%TYPE;
	CURSOR TEACHERS_CUR
	IS 
		SELECT TITLE 
			FROM TEACHERS
			FOR UPDATE;
BEGIN
	OPEN TEACHERS_CUR;
	LOOP
		FETCH TEACHERS_CUR INTO V_TITLE;
		EXIT WHEN TEACHERS_CUR%NOTFOUND;
		CASE
			WHEN V_TITLE='教授' THEN
				UPDATE TEACHERS
					SET WAGE = 1.1 * WAGE WHERE CURRENT OF TEACHERS_CUR;
			WHEN V_TITLE='高工' THEN
				UPDATE TEACHERS
					SET WAGE = 1.05 * WAGE WHERE CURRENT OF TEACHERS_CUR;			
		ELSE 
			UPDATE TEACHERS
				SET WAGE = WAGE+100 WHERE CURRENT OF TEACHERS_CUR;
		END CASE;
	END LOOP;
	CLOSE TEACHERS_CUR;

END;
/

3.删除数据

 

delete语句的格式

DELETE FROM TABLE_NAME WHERE CURRENT OF CURSOR_NAME;

 

DECLARE 
	--声明变量和游标
	V_SPECIALTY STUDENTS.SPECIALTY%TYPE;
	V_SNAME STUDENTS.NAME%TYPE;
	
	CURSOR STUDENTS_CUR
	IS 
		SELECT NAME,SPECIALTY 
			FROM STUDENTS
			FOR UPDATE;
BEGIN
	OPEN STDUENTS_CUR;
	FETCH STUDENTS_CUR INTO V_SNAME,V_SPECIALTY;
	WHILE STUDENTS_CUR%FOUND LOOP
		IF V_SPECIALTY = 'CS' THEN
			DELETE FROM STUDENTS WHERE CURRENT OF STUDENTS_CUR;
		END IF;
		FETCH STDUENTS_CUR INTO V_SNAME,V_SPECIALTY;
	END LOOP;
	CLOSE STUDENTS_CUR;

END;
/

以上内容参考其他书籍的例子所写