游标不是oracle内部对象,要借助包体来实现
CREATE OR REPLACE PACKAGE pkg_test
AS
TYPE myrctype IS REF CURSOR; PROCEDURE get (p_id NUMBER, p_rc OUT myrctype);
END pkg_test;
/CREATE OR REPLACE PACKAGE BODY pkg_test
AS
PROCEDURE get (p_id NUMBER, p_rc OUT myrctype)
IS
sqlstr VARCHAR2 (500);
BEGIN
IF p_id = 0 THEN
OPEN p_rc FOR
SELECT ID, NAME, sex, address, postcode, birthday
FROM student;
ELSE
sqlstr :=
'select id,name,sex,address,postcode,birthday
from student where id=:w_id';
OPEN p_rc FOR sqlstr USING p_id;
END IF;
END get;
END pkg_test;
/
CREATE OR REPLACE PACKAGE pkg_test
AS
TYPE myrctype IS REF CURSOR; PROCEDURE get (p_id NUMBER, p_rc OUT myrctype);
END pkg_test;
/CREATE OR REPLACE PACKAGE BODY pkg_test
AS
PROCEDURE get (p_id NUMBER, p_rc OUT myrctype)
IS
sqlstr VARCHAR2 (500);
BEGIN
IF p_id = 0 THEN
OPEN p_rc FOR
SELECT ID, NAME, sex, address, postcode, birthday
FROM student;
ELSE
sqlstr :=
'select id,name,sex,address,postcode,birthday
from student where id=:w_id';
OPEN p_rc FOR sqlstr USING p_id;
END IF;
END get;
END pkg_test;
/
/* Demonstrates the use of a cursor variable on the server.
If p_Table is 'classes', then information from the classes
table is inserted into temp_table. If p_Table is 'rooms'
then information from rooms is inserted. */
(p_Table IN VARCHAR2) AS /* Define the cursor variable type */
TYPE t_ClassesRooms IS REF CURSOR; /* and the variable itself. */
v_CursorVar t_ClassesRooms; /* Variables to hold the output. */
v_Department classes.department%TYPE;
v_Course classes.course%TYPE;
v_RoomID rooms.room_id%TYPE;
v_Description rooms.description%TYPE;
BEGIN
-- Based on the input parameter, open the cursor variable.
IF p_Table = 'classes' THEN
OPEN v_CursorVar FOR
SELECT department, course
FROM classes;
ELSIF p_table = 'rooms' THEN
OPEN v_CursorVar FOR
SELECT room_id, description
FROM rooms;
ELSE
/* Wrong value passed as input - raise an error */
RAISE_APPLICATION_ERROR(-20000,
'Input must be ''classes'' or ''rooms''');
END IF; /* Fetch loop. Note the EXIT WHEN clause after the FETCH -
with PL/SQL 2.3 we can use cursor attributes with cursor
variables. */
LOOP
IF p_Table = 'classes' THEN
FETCH v_CursorVar INTO
v_Department, v_Course;
EXIT WHEN v_CursorVar%NOTFOUND; INSERT INTO temp_table (num_col, char_col)
VALUES (v_Course, v_Department);
ELSE
FETCH v_CursorVar INTO
v_RoomID, v_Description;
EXIT WHEN v_CursorVAR%NOTFOUND; INSERT INTO temp_table (num_col, char_col)
VALUES (v_RoomID, SUBSTR(v_Description, 1, 60));
END IF;
END LOOP; /* Close the cursor. */
CLOSE v_CursorVar; COMMIT;
END ShowCursorVariable;
/
create or replace PROCEDURE CursorSample(
v_StudentID students.id%TYPE
)as
p_StudentID students.id%TYPE;
/* 游标声明*/
CURSOR c_Students IS
SELECT id
FROM students
WHERE id = v_StudentID;
BEGIN
/* 打开并初始化游标*/
OPEN c_Students;
LOOP
/* 提取游标并赋给变量*/
FETCH c_Students INTO p_StudentID; /* 如果无记录可提取了退出循环*/
EXIT WHEN c_Students%NOTFOUND;
END LOOP; /* 释放游标*/
CLOSE c_Students;
END CursorSample;
cur1 is select * from table1;
begin
for rc in cur1 loop
update table2 set name=rc.name where id=rc.id;
end loop;
end;
as
(
l_saladate in varchar2,
l_errstr out varchar2)
is
l_empid varchar(20);
rowcount number;
//静态游标定义
cursor c1 is select distinct empid from salary ;
BEGIN
select count(*) into rowcount
from salary ;
if(rowcount)>0 then
l_errstr:='此月份的工资记录已经存在';
else
open c1;
loop fetch c1 into indexname,empname;
exit when %c1not found;
insert into ...
end loop;
close c1;
END;