为什么要用到游标呢?因为我们都知道,在pl/sql 中,每次只能返回单行的数据,当返回多行时,数据库会报错,所以,游标就诞生了。那游标又是如何做得到呢?原来,游标类似于指针一样,他可以逐行的读取sql,然后输出。
我们可以通过以下步骤,定义和使用游标:
1 申明(declare)一个游标
2 打开(open)游标
3 读取(fetch)游标
4 关闭(close)游标
列
DECLARE
CURSOR C_STUDENT_NAME IS
SELECT first_name,last_name FROM student
where rownum<5;
vr_student_name c_student_name%ROWTYPE;
BEGIN
OPEN c_student_name;
LOOP
FETCH c_student_name INTO vr_student_name;
EXIT when c_student_name%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Student name:'||vr_student_name.first_name||''||vr_student_name.last_name);
END LOOP;
CLOSE c_student_name;
END;
那为什么要用到LOOP呢?因为游标包含多个变量,所以要通过循环来赋值。为了简化语法,处理游标的另一种方式被叫做FOR循环,使用时打开,读取和关闭被隐式处理,提高了效率。
DECLARE
CURSOR c_student IS
SELECT student_id,last_name,first_name from student
where student_id<100;
BEGIN
FOR r_student IN c_studnet
LOOP
insert into class
values( r_student.last_name);
END LOOP;
END;
以上为显示游标,其实还有一种叫做隐式游标的。
1 任何给定的块中,若无显示游标,就会发出一个隐式游标。
2 游标会自动的与每一个DML语言相关联。
3 INSERT语句需要有一个地方来接受插入的数据,隐式游标满足了这一要求。
游标还可以带参数,但是要注意以下几点:
1.游标参数可以被赋予默认值
2.参数只能是模式 IN
当游标已被申明为接受参数的游标后,调用他时,就必须包括此参数的值。
例:
DECLARE
CURSOR c_zip(p_state IN zipcode.stste%TYPE) IS
select zip,city,state from zipcode
where state= p_state
BEGIN
for r_zip IN c_zip(‘nanjing’)
LOOP
EXIT when r_zip%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(r_zip.city||’ ‘||r_zip.state);
END LOOP;
END;
还有一种游标叫做FOR UPDATE 游标
使用 FOR UPDATE 子句的目的是锁住你要更新的行,使用commit或rollback语句释放锁。在要锁定一个表中的既包含指定列之一,又是活动集的成员的行时,可以用for update of 项目名 来完成。
例
DECLARE
CURSOR c_student_zip is
select s.student_id,z.city from student s, zipcode z
where z.city=’nanjing’ and s.zip=z.zip
for update of phone;
BEGIN
for r_student_zip in c_student_zip
loop
exit when c_student_zip%NOTFOUND;
update student
set phone=’021’ where student_id=r_student_zip.student_id;
END LOOP;
END;
所以,由这几个块中,我们可以看到,凡是有游标的,必须有for 和LOOP,来进行读取。