----------弱类型动态游标:在声明变量时不使用return关键字定义游标的返回类型
【强类型动态游标】 --1.创建强类型游标：传入学生年龄，如果年龄小于0，则打印所有学生的信息。反之打印对应年龄的学生信息。 create or replace procedure printStudents(in_age in number) as begin declare --自定义类型 type student_type is record( id number, name varchar2(10), age number ); begin type students_type is ref cursor return student_type;--type student_type is ref cursor定义游标的类型 students students_type;--声明游标变量 --ref cursor表明是一个动态游标 student student_type; --声明变量 --return student_type表明是一个强类型游标 if in_age<=0 then open students for select * from students; else open students for select * from students where student_age=in_age; end if; fetch students into student;--获取游标中的结果集存储到student变量中 while students%found loop --循环游标记录 dbms_output.put_line(student.id||'--'||student.name||'--'||student.age);--遍历输出记录 fetch students into student;--游标指向下一个记录 end loop; close students; end; end printStudents; / --如果报错，点击存储过程选择edit即可看到错误提示进行修改 --对象 SYS.PRINTSTUDENTS 无效;查看对象中创建的存储过程信息，发现STATUS为INVALID，即存储过程没有启用 select * from user_objects where object_type='PROCEDURE' and object_name='PRINTSTUDENTS'; OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS TEMPORARY GENERATED SECONDARY NAMESPACE EDITION_NAME ---------------------- ------------------ ---------- -------------- ------------------- ----------- ------------- ------------------- ------- --------- --------- --------- ---------- ------------- PRINTSTUDENTS 71488 0 PROCEDURE 2017/8/30 2 2017/8/31 22: 2017-08-31:22:49:30 INVALID N N N 1 --修改成功后即可看到STATUS为VALID，代表可以调用 OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS TEMPORARY GENERATED SECONDARY NAMESPACE EDITION_NAME ---------------------- ------------------ ---------- -------------- ------------------- ----------- ------------- ------------------- ------- --------- --------- --------- ---------- ------------- PRINTSTUDENTS 71488 0 PROCEDURE 2017/8/30 2 2017/8/31 22: 2017-08-31:22:49:30 VALID N N N 1 --调用存储过程:年龄小于0 declare begin printStudents(-1); end; / --结果：打印出全部学生信息 1:金瑞:19 2:钟君:12 3:王山:29 4:刘迪:28 5:钟会:18 6:张玉:18 7:柳青:18 8:胡东:18 9:商乾:18 10:周明:18 PL/SQL procedure successfully completed --调用存储过程:年龄等于20，打印出对应学生信息 declare begin printStudents(18); end; / --结果 5:钟会:18 6:张玉:18 7:柳青:18 8:胡东:18 9:商乾:18 10:周明:18 PL/SQL procedure successfully completed select * from students; 【弱类型动态游标】 --2.创建弱类型游标：传入参数，根据传入的参数判断打印学生表的何种信息。 create or replace procedure printStudentsByFlag(in_flag in varchar2) as begin declare --自定义类型 type name_type is record( id number, name varchar2(10) ); type age_type is record( id number, age varchar2(10) ); --声明一个弱类型游标 type students_type is ref cursor; name name_type; age age_type; students students_type; --声明变量 begin if upper(in_flag)='NAME' then--打印姓名信息 open students for select student_id,student_name from students; fetch students into name; while students%found loop dbms_output.put_line(name.id||'--'||name.name); fetch students into name; end loop; elsif upper(in_flag)='AGE' then--打印年龄信息 open students for select student_id,student_age from students; fetch students into age; while students%found loop dbms_output.put_line(age.id||'--'||age.age); fetch students into age; end loop; end if; close students; end; end printStudentsByFlag; --结果：排错过程如1所述 Procedure created --调用存储过程--传入NAME declare begin printStudentsByFlag('NAME'); end; / --结果如下 1--金瑞 2--钟君 3--王山 4--刘迪 5--钟会 6--张玉 7--柳青 8--胡东 9--商乾 10--周明 PL/SQL procedure successfully completed --调用存储过程--传入AGE declare begin printStudentsByFlag('AGE'); end; / --结果 1--19 2--12 3--29 4--28 5--18 6--18 7--18 8--18 9--18 10--18 PL/SQL procedure successfully completed
