Oracle游标学习笔记的知识点介绍
游标按以下操作进行
parse 解析 bind 绑定 open 打开 execute 执行 fetch 回取 close 关闭1.写自己第一个游标PL/SQL
declare cursor c_s is select * from user_tables; begin open c_s; --打开游标 close c_s;--关闭游标 end;游标的4个属性 %found,%notfound,%rowcount,%isopen
1.%found游标有记录则返回true否则false declare cursor c_s is select * from user_tables; cc c_s%rowtype; begin open c_s; --打开游标 fetch c_s into cc; while c_s%found loop fetch c_s into cc; end loop; close c_s;--关闭游标 end; 2.%notfound游标没记录则返回true否则false(个人感觉有点多余) declare cursor c_s is select * from user_tables; cc c_s%rowtype;//游标变量 begin open c_s; --打开游标 fetch c_s into cc; while c_s%found loop exit when c_s%notfound; end loop; close c_s;--关闭游标 end;3.%rowcount返回游标取回的记录数目
declare cursor c_s is select * from user_tables; cc c_s%rowtype; begin open c_s; --打开游标 fetch c_s into cc; while c_s%found loop dbms_output.put_line(c_s%rowcount); end loop; close c_s;--关闭游标 end;4.%isopen如果游标打开就返回true 否则false
declare cursor c_s is select * from user_tables; begin if c_s%isopen then dbms_output.put_line('cursor is open'); else open c_s; --打开游标 end if; close c_s;--关闭游标 end;游标参数
declare cursor c_s(cs_id number) is select * from admin id=cs_id; begin open c_s(10); --打开带参数的游标 close c_s;--关闭游标 end;游标中的for update
declare cursor c_s is select id from admin for update of id //查询时锁定 id列 begin open c_s; commit;//提交释放锁 或者可以用 rollback close c_s; end;游标中的where cursor of
UPDATE table_name SET set_clause WHERE CURSOR OF cursor_name; //更新游标所指向的那条记录 DELETE FROM table_name WHERE CURSOR OF cursor_name; //删除游标所指向的那条记录游标中的ref cursor类型
TYPE c_s IS REF CURSOR RETRUN table%rowtype; //这种形式为强类型 在声明的时候就定了为行类型 TYPE c_s IS REF CURSOR;//弱类型 不与记录的数据类型关联 例子: declare TYPE c_s IS REF CURSOR RETRUN table%rowtype; TYPE c_s2 IS REF CURSOR; var_cs c_s;//声明为游标变量的类型 begin OPEN c_s FOR select * from admin; CLOSE c_s; end;