语句语法忒复杂，眼花缭乱的记不住，放几个例子。

游标属性：

 Cursor_name%FOUND     布尔型属性，当最近一次提取游标操作FETCH成功则为 TRUE,否则为FALSE；

 Cursor_name%NOTFOUND   布尔型属性，与%FOUND相反；

 Cursor_name%ISOPEN     布尔型属性，当游标已打开时返回 TRUE；

 Cursor_name%ROWCOUNT   数字型属性，返回已从游标中读取的记录数。

显示游标：

无参游标：

DECLARE CURSOR c_cursor IS SELECT sname, sage FROM l_student_info_tbl WHERE rownum < 5; v_sname l_student_info_tbl.sname%TYPE; v_sage l_student_info_tbl.sage%TYPE;BEGIN OPEN c_cursor; FETCH c_cursor INTO v_sname, v_sage; WHILE c_cursor%FOUND LOOP DBMS_OUTPUT.PUT_LINE(v_sname || ‘---‘ || to_char(v_sage)); FETCH c_cursor INTO v_sname, v_sage; END LOOP; CLOSE c_cursor;END;

遍历也可以这样（个人比较喜欢下面fetch）：

DECLARE CURSOR c_cursor IS SELECT sname, sage FROM l_student_info_tbl WHERE rownum < 5; v_sname l_student_info_tbl.sname%TYPE; v_sage l_student_info_tbl.sage%TYPE;BEGIN OPEN c_cursor; LOOP FETCH c_cursor INTO v_sname, v_sage; EXIT WHEN c_cursor%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_sname || ‘---‘ || v_sage); END LOOP; CLOSE c_cursor;END;

for 遍历更简洁（推荐）：

DECLARE CURSOR c_cursor IS SELECT sname, sage FROM l_student_info_tbl WHERE rownum < 5; --v_stu c_cursor%rowtype;BEGIN for v_stu in c_cursor loop DBMS_OUTPUT.PUT_LINE(v_stu.sname || ‘---‘ || v_stu.sage); end loop;END;

还可以如下遍历：

DECLARE CURSOR c_cursor IS SELECT sname, sage FROM l_student_info_tbl WHERE rownum < 5; --定义数组 type t_snage is table of l_student_info_tbl.sname%type; type t_sage is table of l_student_info_tbl.sage%type; v_arr_sname t_snage; v_arr_sage t_snage;BEGIN open c_cursor; fetch c_cursor bulk collect into v_arr_sname, v_arr_sage; close c_cursor; for i in v_arr_sname.first .. v_arr_sname.last loop dbms_output.put_line(v_arr_sname(i) || ‘---‘ || v_arr_sage(i)); end loop;END;

return子句：

DECLARE type stu_record_type is record( v_sname l_student_info_tbl.sname%type, v_sage l_student_info_tbl.sage%type, v_sgender l_student_info_tbl.sgender%type, v_sclassno l_student_info_tbl.sclassno%type); v_stu_record stu_record_type; TYPE stu_cursor_type IS REF CURSOR RETURN stu_record_type; c1 stu_cursor_type;BEGIN OPEN c1 FOR SELECT sname, sage, sgender, sclassno FROM l_student_info_tbl WHERE sage = 30; LOOP FETCH c1 INTO v_stu_record; EXIT WHEN c1%NOTFOUND; DBMS_OUTPUT.PUT_LINE(‘名称：‘ || v_stu_record.v_sname || ‘ 年龄：‘ || v_stu_record.v_sage || ‘ 性别：‘ || v_stu_record.v_sgender); END LOOP; CLOSE c1;END;

有参游标：

DECLARE CURSOR c_cursor(v_limit_num number default 5) IS SELECT sname, sage FROM l_student_info_tbl WHERE rownum < v_limit_num;BEGIN for v_stu in c_cursor(8) loop DBMS_OUTPUT.PUT_LINE(v_stu.sname || ‘---‘ || v_stu.sage); end loop;END;

隐式游标

对于非查询语句，如修改、删除操作，则由ORACLE 系统自动地为这些操作设置游标并创建其工作区，这些由系统隐含创建的游标称为隐式游标，隐式游标的名字为SQL。

格式：SQL%

注：INSERT, UPDATE, DELETE, SELECT 语句中不必明确定义游标。

隐式游标属性

eg1：删除EMPLOYEES表中某部门的所有员工，如果该部门中已没有员工，则在DEPARTMENT表中删除该部门

DECLARE V_deptno department_id%TYPE := &p_deptno;BEGIN DELETE FROM employees WHERE department_id = v_deptno; IF SQL%NOTFOUND THEN DELETE FROM departments WHERE department_id = v_deptno; END IF;END;

eg2:通过隐式游标SQL的%ROWCOUNT属性来了解修改了多少行

DECLARE v_rows NUMBER;BEGIN --更新数据 UPDATE employees SET salary = 30000 WHERE department_id = 90 AND job_id = ‘AD_VP‘; --获取默认游标的属性值 v_rows := SQL%ROWCOUNT; DBMS_OUTPUT.PUT_LINE(‘更新了‘ || v_rows || ‘个雇员的工资‘); --回退更新，以便使数据库的数据保持原样 ROLLBACK;END;