DECLARE
-- Declare variables to hold information about the students
-- majoring in History.
v_StudentID students.id%TYPE;
v_FirstName students.first_name%TYPE;
v_LastName students.last_name%TYPE;
v_rn integer; -- Cursor to retrieve the information about History students
CURSOR c_HistoryStudents IS
SELECT rownum rn,id, first_name, last_name
FROM students
WHERE major = 'History';
BEGIN
-- Open the cursor and initialize the active set
OPEN c_HistoryStudents;
LOOP
-- Retrieve information for the next student
FETCH c_HistoryStudents INTO v_rn,v_StudentID, v_FirstName, v_LastName; -- Exit loop when there are no more rows to fetch
EXIT WHEN c_HistoryStudents%NOTFOUND;
if v_rn=100 then
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 if;
END LOOP; -- Free resources used by the cursor
CLOSE c_HistoryStudents; -- Commit our work
COMMIT;
END;
/
-- Declare variables to hold information about the students
-- majoring in History.
v_StudentID students.id%TYPE;
v_FirstName students.first_name%TYPE;
v_LastName students.last_name%TYPE;
v_rn integer; -- Cursor to retrieve the information about History students
CURSOR c_HistoryStudents IS
SELECT rownum rn,id, first_name, last_name
FROM students
WHERE major = 'History';
BEGIN
-- Open the cursor and initialize the active set
OPEN c_HistoryStudents;
LOOP
-- Retrieve information for the next student
FETCH c_HistoryStudents INTO v_rn,v_StudentID, v_FirstName, v_LastName; -- Exit loop when there are no more rows to fetch
EXIT WHEN c_HistoryStudents%NOTFOUND;
if v_rn=100 then
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 if;
END LOOP; -- Free resources used by the cursor
CLOSE c_HistoryStudents; -- Commit our work
COMMIT;
END;
/
解决方案 »
- 求教pl/sql 循环中出现异常捕获异常并继续执行循环的方法
- PL/sql 中的查询问题
- 为什么我在 win2008 for 64 的机器上装了 oracle 11g 占用16G内存
- 没分了,江湖救急,求一条高效的sql语句
- Oracle 出了这样的问题,高手们,乍办?
- 还原了一个WE8ISO8859P1字符集 数据库 表里汉字都是乱码显示 如何才能看到汉字?
- 优化大师来看看我SQL
- 表结构调整,相关的视图与存储过程失效如何处理?
- 用pro c写的一个集成访问数据库的动态库(属于16位版本)。访问Oracle8i以下的版本没问题,访问Oracle8i以上版本出错,请教是什么原因?
- 请教oracle删除数据?
- 需要用程序读取oracle中存储过程的参数个数和类型以及是输入还是输出,如何?
- 请问怎样写这样一个触发器?(在线等待)
loop
fetch …………
exit 游标%notfound;
end loop
close 游标for
end loop;
不必使用游标。
select * from tab where rownum<=100
minus
select * from tab where rownum<100