本人是新手,内容属抄袭,请各位大神见谅!!!
16.游标
游标具有的四个属性:%isopen %found %notfound %rowcount
%isopen 是用来判断游标是否被打开的,如果被打开等于true,否则为false
%found 判断所在的行是否有效,如果有效则%found为true,否则为false
%notfound 则与%found相反
%rowcount 返回当前位置为止游标读取的记录行数
游标可以分为显示游标和隐式游标两种:
隐式游标: 由pl/sql维护,当执行查询时自动打开和关闭,属性%isopen总是为false,
显示游标:在程序之中显式的定义,打开,关闭,显式游标具有具体的名字
----普通游标的定义及使用
declare
cursor cursor_name isselect * from table_name;
table_record table_name%rowtype;
begin
open cursor_name;
loop
fetch cursor_name into table_record;
exit when cursor_name%notfound;
statements;
endloop;
close cursor_name;
end;
----带有参数的游标的定义与使用
declare
cursor cursor_name(idnumber) isselect * from table_name whereid=id;
table_record table_name%rowtype;
begin
open cursor_name(1);
loop
fetch cursor_name into table_record;
exit when cursor_name%notfound;
statements;
endloop;
close cursor_name;
end;
----简写的游标定义及使用
declare
cursor cursor_nameisselectnamefrom student;
begin
for sursor_name in cursor_name loop
statements;
endloop;
end;
----匿名游标的定义及使用
declare
begin
for sursor_name in (selectnamefrom student) loop
statements;
endloop;
end;
18.游标变量
游标变量分为指明返回类型的游标变量和不指明返回类型的游标变量。
----普通游标的定义及使用
declare
cursor cursor_name isselect * from table_name;
table_record table_name%rowtype;
begin
open cursor_name;
loop
fetch cursor_name into table_record;
exit when cursor_name%notfound;
statements;
endloop;
close cursor_name;
end;
----带有参数的游标的定义与使用
declare
cursor cursor_name(idnumber) isselect * from table_name whereid=id;
table_record table_name%rowtype;
begin
open cursor_name(1);
loop
fetch cursor_name into table_record;
exit when cursor_name%notfound;
statements;
endloop;
close cursor_name;
end;
----简写的游标定义及使用
declare
cursorcursor_name isselectnamefrom student;
begin
for sursor_name in cursor_name loop
statements;
endloop;
end;
----匿名游标的定义及使用
declare
begin
for sursor_name in (selectnamefrom student) loop
statements;
endloop;
end;
----不声明返回类型的游标变量
declare
type cursor_class isrefcursor;
cursor_name cursor_class;
data_record table_name%rowtype;
begin
open cursor_name forselect * from table_name;
loop
fetch cursor_name into data_record;
exitwhen cursor_name%notfound;
statements;
endloop;
close cursor_name;
end;
----声明返回类型的游标变量,用这种方式声明的游标不支持%rowtype类型的变量声明返回类型
declare
type class_record_type isrecord(classnameclass.classname%type);
type class_cursor_type isrefcursorreturn class_record_type;
class_record class_record_type;
class_cursor class_cursor_type;
begin
open class_cursor forselect classname fromclass;
loop
fetch class_cursor into class_record;
exitwhen class_cursor%notfound;
dbms_output.put_line(class_record.classname);
endloop;
close class_cursor;
end;
----嵌套游标:
declare
cursor info(n number) isselect c.classname,cursor(select s.name from student s where s.id=c.studentid )
fromclass c where c.studentid=&n;
type cursor_type isrefcursor;
class_cursor cursor_type;
classname class.classname%type;
tmp varchar2(100);
begin
open info(1);
loop
fetch info into classname,class_cursor;
exitwhen info%notfound;
dbms_output.put_line(classname||':');
loop
fetch class_cursor into tmp;
exitwhen class_cursor%notfound;
dbms_output.put_line(tmp);
endloop;
endloop;
close info;
end;
----批量返回数据的游标:
declare
cursor student_cursor isselectnamefrom student;
type name_table_type istableofvarchar(20);
name_table name_table_type;
begin
open student_cursor;
fetch student_cursorbulk collectinto name_table;
for i in1..name_table.count loop
dbms_output.put_line(name_table(i));
endloop;
close student_cursor;
end;
----批量返回数据的游标但可以限制每次返回数据的行数的游标
declare
cursor student_cursor isselectnamefrom student;
type name_table_type istableofvarchar(20);
name_table name_table_type;
rlimit number(2):=10;
vcount number(2):=0;
begin
open student_cursor;
loop
fetch student_cursorbulk collectinto name_tablelimitrlimit;
exitwhen student_cursor%notfound;
dbms_output.put_line('rowcount:'||student_cursor%rowcount||'vcount:'||vcount);
for i in1..(student_cursor%rowcount-vcount) loop
dbms_output.put_line(name_table(i));
endloop;
dbms_output.new_line();
vcount:=student_cursor%rowcount;
endloop;
close student_cursor;
end;
17.包的理解与使用
包在一个大型的项目之中有着重要的作用,oracle中的包类似与一般编程语言的包或者命名空间的含义,是一个模块化的含义。在大型的项目之中,每一个模块都有自己的过程,函数等,通过包可以来管理项目之中的同名过程与函数等一些现象。在包之中可以自定义类型,在函数和过程之中可以直接使用自定义变量,包分为包规范(package)与包体(package body)。
createorreplacepackage temppackage ----包可以把一些操作以包的形式包含,向类一样,又如同命名空间
is
procedure studenttotemp; ----通过一个存储过程来使数据导入到事物表之中
----包中也可以声明结构体,函数,游标变量等
end;
create orreplacepackagebody temppackage ----包体实现存储过程
is
procedure studenttotemp
is
begin
insert into studentresult select * from studenttem;
end;
end;