游标的主要作用是把select的数据,存到游标里,然后用的时候在读出来。
一般的步骤是:
1.定义游标:如 CURSOR c_student IS SELECT sname FROM student ORDER BY sname; 意思是建立了一个叫c_student的游标,它里面装的是SELECT sname FROM student的一条条记录,是根据ORDER BY sname来排列的
tips:cursor c_student is select sname || age from student 在fetch c_studnet into nameandage 这样读取的是sname和age合在一起的信息。当然这一步也完全可以在游标读取到内容再进行拼凑。只是说也它的可行性
2.打开游标:用open c_student来打开游标。%isopen c_student 来判断是否已经打开游标了
3.提取游标里的参数:用fetch来提取游标里值。先在declare里面写好一个参数,用来装cursor c_student.然后就用fetch c_student into rec_stu装进去了。接着你用输出函数DBMS_OUTPUT.PUT_LINE(REC_STU);就可以输出第一条信息了。当然你多加几句
fetch c_student into rec_stu;
DBMS_OUTPUT.PUT_LINE(REC_STU);这块代码,游标就一条一条帮你继续写出来。
4.然后用完了把游标 关掉吧 是个好习惯啊!
create or replace procedure c_su is
CURSOR c_student IS SELECT sname FROM student ORDER BY sname;
rec_stu student.sname%TYPE
begin
open c_student;
fetch c_student into rec_stu;
DBMS_OUTPUT.PUT_LINE(REC_STU);
close c_student;
end c_su;
二 使用循环输出游标。
意思差不多,就是在读游标的时候,使用了一个循环。
首先介绍一下循环:
这次不用fetch来装游标里的值了,咱用for来装:for s_name in c_student,装好之后就用loop循环吧.
for s_name in c_student
loop
DBMS_OUTPUT.put_line(s_name.sname);
end loop;
当然也可以这样来遍历:
loop
fetch c_studnet into rec_stu;
DBMS_OUTPUT.put_line(rec_stu);
end loop;
两者的区别就是:
假如你定义的游标是 cursor1 is select * from student。
for s_value in cursor1 那么可以用s_name.xxx的形式,取出任何一个列的值。
而如果用fetch 。。into。。读取出多列,那么只能是这样写fetch cursor1 into col1,col2,col3。。
tip:注意一下顺序for s_name in c_student <> fetch c_student into rec_stu;别下手太快搞错了。
三 游标属性:游标属性
%isopen 布尔型,如果游标打开,则为true
%notfound 布尔型,如果没有返回行,则为true
%found 布尔型,如果有返回行,则为true
%rowcount 数值型,当前为止从工作返回的总行数
根据这些属性我们可以给cursor再遍历的时候,加点判断,以求完美
loop
fetch c_studnet into rec_stu;
flag := c_student%isopen;
DBMS_OUTPUT.put_line(rec_stu);
countrow := c_studnet%rowcount;
DBMS_OUTPUT.put_line(countrow);--每次执行打印一次所执行的行数
exit when c_student%notfound;--如果没有返回行,退出
end loop;
DBMS_OUTPUT.put_line(flag);--判断游标打开否,true果,false未果
DBMS_OUTPUT.put_line(countrow);--执行打印所有执行的行数
四 隐式游标:
所有隐式的游标都叫SQL,是oracle自带的游标。简单的说你拿来用就是了。用的时候只要根据属性来判断,判断之后写你要操作的statement就可以了。写个例子:
create or replace procedure testsqlcursor(
v_id in testemp.empno%TYPE
) is
begin
delete from testemp where empno = v_id;
if sql%notfound then
delete from salary where ID = v_id;
END IF;
DBMS_OUTPUT.put_line(sql%rowcount);
end testsqlcursor;
五 游标变量。我觉得游标变量要比直接定义一个显示游标要灵活,
有些书上说是 动态游标,显示和隐式的叫静态游标。
1.游标声明:
声明成有返回值的叫强类型定义,没有返回值的叫弱类型定义(大概这意思)
游标变量的声明分成两步:
1.1 游标的声明 TYPE cursor1_type is ref cursor;
1.2 游标的定义 cur1 cursor1_type;
例子:声明一个强的,一个弱的
create or replace procedure TEST_REF_CURSOR
is
type return_type is record(
v_name testemp.empno%type,
v_job testemp.job%type
);
type cur1_type is ref cursor return return_type;
type cur3_type is ref cursor return testemp%rowtype;
type cur2_type is ref cursor;
cur1 cur1_type;--用cur1_type定义了一个cursor
cur2 cur2_type;--同上
cur3 cur3_type;--同上
begin
commit;
end TEST_REF_CURSOR;
2.打开游标OPEN emp_refcur FOR
SELECT employees.employee_id, employees.first_name||employees.last_name, employees.job_id
3.遍历游标
还是老方法 fetch cursor_name into variable_name
然后就loop吧loop完别忘了end loop就行,建议写完loop就直接写end loop。
4.关闭游标
养成物归原主的好习惯,用完了,你得关了它。
5.例子
还是写个例子吧
create or replace procedure TEST_REF_CURSOR is
type return_type is record(
v_name testemp.empno%type,
v_job testemp.job%type);
type cur3_type is ref cursor return testemp%rowtype;
cur3 cur3_type;
rec testemp%rowtype;
begin
open cur3 for
select * from testemp order by testemp.empno;
loop
fetch cur3 into rec;
exit when cur3%notfound;
dbms_output.put_line(rec.ename||rec.job);
end loop;
close cur3;--如果你要用游标对其他表进行处理的话,你只要再次打开游标就是了,当然你定义得是弱类型的游标,顺便把记录变量定义--一下就可以使用了。
end TEST_REF_CURSOR;
给个例子弱类型游标变量:
create or replace procedure TEST_REF_CURSOR is
type return_type is record(
v_name testemp.empno%type,
v_job testemp.job%type);
type cur3_type is ref cursor return testemp%rowtype;
type cur2_type is ref cursor;
cur2 cur2_type;
cur3 cur3_type;
rec testemp%rowtype;
rec2 salary%rowtype;
begin
open cur3 for
select * from testemp order by testemp.empno;
loop
fetch cur3
into rec;
exit when cur3%notfound;
-- dbms_output.put_line(rec.ename||rec.job);
end loop;
close cur3;
open cur2 for
select * from testemp order by testemp.empno;
loop
fetch cur2
into rec;
exit when cur2%notfound;
dbms_output.put_line(rec.deptno || rec.empno || rec.ename || rec.job);
end loop;
close cur2;
open cur2 for
select * from salary where salary.id = 3 order by salary.id;
loop
fetch cur2
into rec2;
exit when cur2%notfound;
dbms_output.put_line(rec2.sal);
end loop;
close cur2;
end TEST_REF_CURSOR;
一些补充:
TYPE MYTYPE (
MYTYPE1 NUMBER,
MYTYPE2 VARCHAR2,
MYTYPE3 VARCHAR
);
这样就算是定义了一个类型
然后声明一个为之对应的类型比如
mytype_1 MYTYPE;
那MYTYPE_1就可以用这个MYTYPE类型了
TABLENAME.COLNAME%TYPE
declare
myparameter test.name%type; --是说明你用test表里name列的类型
TABLENAME%ROWTYPE
DECLARE
REC TEST%ROWTYP;--说明rec的类型和test表里各个列的类型是一样的。