每一条被oracle服务器执行的SQL语句都有一个独立的游标与之相关联
隐式游标 Implicit cursors: 用于所有的DML和PL/SQL的SELECT语句。
显示游标 Explicit cursors: 被程序显示声明和命名。
1.所定义的SQL语句必须只包含select语句,并且不能用insert、update或delete关键字。
2.当select语句可能返回零或多于一行时,必须用显式游标。
3.当Select语句预计只返回一行时,隐式游标将做得更好。
显示游标的功能:
能够一行一行的处理多行查询结果。
能够记录和跟踪当前正在处理的行。
在PL/SQL块中允许程序手工控制游标。
控制显示游标使用流程:
1.声明游标:cursor cur
is
select * from tablename
这里还可以带上查询所需要的参数!例如:
CURSOR CUR(ID NUMBER)
IS
SELECT *
FROM TABLENAME T
WHERE T.ID=ID
2.为查询打开游标(open cursor)例如:open cur或者open cur(50)
3.从结果集中循环提取数据:fetche into
例如:FETCHE CUR INTO VAR1,VAR2,VAR3...
4.关闭游标close
当所有的活动集都被检索以后,游标就应该关闭。
如果需要,可以在重新打开游标。
一旦关闭了游标,再从该游标提取数据就将是非法的。这样做会产生一个Oracle错误。
显示游标的属性:
%isopen,%notfound,%found,%rowcount
下面是游标数据循环提取简单举例:
1.loop ...end loop循环
DECLARE
v_StudentID students.id%TYPE;
v_FirstName students.first_name%TYPE;
v_LastName students.last_name%TYPE;
CURSOR c_HistoryStudents IS
SELECT id, first_name, last_name FROM students WHERE major = 'History';
BEGIN
OPEN c_HistoryStudents;
LOOP
FETCH c_HistoryStudents INTO v_StudentID, v_FirstName, v_LastName;
EXIT WHEN c_HistoryStudents%NOTFOUND;
INSERT INTO registered_students (student_id, department, course) VALUES (v_StudentID, 'HIS', 301);
INSERT INTO temp_table (num_col, char_col) VALUES (v_StudentID, v_FirstName || ' ' || v_LastName);
END LOOP;
CLOSE c_HistoryStudents;
COMMIT;
END;
2.while loop ....end loop;
DECLARE
CURSOR c_HistoryStudents IS
SELECT id, first_name, last_name FROM students WHERE major = 'History';
v_StudentData c_HistoryStudents%ROWTYPE;
BEGIN
OPEN c_HistoryStudents;
FETCH c_HistoryStudents INTO v_StudentData;
WHILE c_HistoryStudents%FOUND LOOP
INSERT INTO registered_students (student_id, department, course) VALUES (v_StudentData.ID, 'HIS', 301);
INSERT INTO temp_table (num_col, char_col) VALUES (v_StudentData.ID, v_StudentData.first_name || ' ' || v_StudentData.last_name);
FETCH c_HistoryStudents INTO v_StudentData;
END LOOP;
CLOSE c_HistoryStudents;
COMMIT;
END;
3.游标的for循环
DECLARE
CURSOR c1 IS
SELECT empno, ename
FROM emp;
BEGIN
FOR emp_record IN c1 LOOP
-- implicit open and implicit fetch occur
IF emp_record.empno = 7839 THEN
...
END LOOP; -- implicit close occurs
END;
隐式游标 Implicit cursors: 用于所有的DML和PL/SQL的SELECT语句。
显示游标 Explicit cursors: 被程序显示声明和命名。
1.所定义的SQL语句必须只包含select语句,并且不能用insert、update或delete关键字。
2.当select语句可能返回零或多于一行时,必须用显式游标。
3.当Select语句预计只返回一行时,隐式游标将做得更好。
显示游标的功能:
能够一行一行的处理多行查询结果。
能够记录和跟踪当前正在处理的行。
在PL/SQL块中允许程序手工控制游标。
控制显示游标使用流程:
1.声明游标:cursor cur
is
select * from tablename
这里还可以带上查询所需要的参数!例如:
CURSOR CUR(ID NUMBER)
IS
SELECT *
FROM TABLENAME T
WHERE T.ID=ID
2.为查询打开游标(open cursor)例如:open cur或者open cur(50)
3.从结果集中循环提取数据:fetche into
例如:FETCHE CUR INTO VAR1,VAR2,VAR3...
4.关闭游标close
当所有的活动集都被检索以后,游标就应该关闭。
如果需要,可以在重新打开游标。
一旦关闭了游标,再从该游标提取数据就将是非法的。这样做会产生一个Oracle错误。
显示游标的属性:
%isopen,%notfound,%found,%rowcount
下面是游标数据循环提取简单举例:
1.loop ...end loop循环
DECLARE
v_StudentID students.id%TYPE;
v_FirstName students.first_name%TYPE;
v_LastName students.last_name%TYPE;
CURSOR c_HistoryStudents IS
SELECT id, first_name, last_name FROM students WHERE major = 'History';
BEGIN
OPEN c_HistoryStudents;
LOOP
FETCH c_HistoryStudents INTO v_StudentID, v_FirstName, v_LastName;
EXIT WHEN c_HistoryStudents%NOTFOUND;
INSERT INTO registered_students (student_id, department, course) VALUES (v_StudentID, 'HIS', 301);
INSERT INTO temp_table (num_col, char_col) VALUES (v_StudentID, v_FirstName || ' ' || v_LastName);
END LOOP;
CLOSE c_HistoryStudents;
COMMIT;
END;
2.while loop ....end loop;
DECLARE
CURSOR c_HistoryStudents IS
SELECT id, first_name, last_name FROM students WHERE major = 'History';
v_StudentData c_HistoryStudents%ROWTYPE;
BEGIN
OPEN c_HistoryStudents;
FETCH c_HistoryStudents INTO v_StudentData;
WHILE c_HistoryStudents%FOUND LOOP
INSERT INTO registered_students (student_id, department, course) VALUES (v_StudentData.ID, 'HIS', 301);
INSERT INTO temp_table (num_col, char_col) VALUES (v_StudentData.ID, v_StudentData.first_name || ' ' || v_StudentData.last_name);
FETCH c_HistoryStudents INTO v_StudentData;
END LOOP;
CLOSE c_HistoryStudents;
COMMIT;
END;
3.游标的for循环
DECLARE
CURSOR c1 IS
SELECT empno, ename
FROM emp;
BEGIN
FOR emp_record IN c1 LOOP
-- implicit open and implicit fetch occur
IF emp_record.empno = 7839 THEN
...
END LOOP; -- implicit close occurs
END;
4.使用子查询的for循环游标 ,不需要声明游标
BEGIN
FOR emp_record IN ( SELECT empno, ename
FROM emp) LOOP
-- implicit open and implicit fetch occur
IF emp_record.empno = 7839 THEN
...
END LOOP; -- implicit close occurs
END;