游标的灵活运用
来源：互联网 发布：mysql保存特殊字符 编辑：程序博客网 时间：2024/05/17 03:57
游标概念：游标是一个指向上下文的句柄或指针，可以用来控制上下文区和处理语句时上下文区发生的事情。
游标类型：游标的类型包括隐式游标、显式游标和REF游标。
隐式游标：在PL/SQL程序中执行DML SQL语句时自动创建隐式游标。
显示游标：用于处理返回多行的查询。
REF游标：用户处理运行是才能确定的动态SQL查询的结果。
一、隐式游标
在PL/SQL中使用DML语句时自动创建隐式游标。隐式游标自动声明、打开和关闭，其名为SQL。通过检查隐式游标的属性可以获得最近执行的DML语句的信息。
隐式游标的属性有：
%FOUND：SQL语句影响了一行或多行时为true
%NOTFOUND：SQL语句没有影响任何行时为true
%ROWCOUNT：SQL语句影响的行数
%ISOPEN：游标是否打开，始终未false
隐式游标实例1：
SQL> declare
v_RoomData rooms%ROWTYPE;
begin
select * into v_RoomData from rooms where room_id = -1;
if SQL%NOTFOUND THEN
insert into temp_table(char_col) values('NO FOUNT!');
end if;
exception
when NO_DATA_FOUND then
insert into temp_table(char_col) values('Not found,exception handler');
end;
/
说明：注意，这里如果rooms表中没有room_id等于-1的行记录，会引发预定义异常exception，
而不会去执行（if SQL%NOTFOUND THEN）
隐式游标实例2：
SQL> begin
update toys set toyprice=270 where toyid=123;
if SQL%FOUND then
dbms_output.put_line('表已更新');
end if;
end;
/
说明：对于修改语句，如果修改了记录，即影响了一行或者多行，这SQL%FOUND返回true
隐式游标实例3：
SQL> declare
v_toyid toys.toyid%type:='&toyid';
v_toyname toys.name%type:='&toyname';
begin
update toys set name = v_toyname where toyid=v_toyid;
if SQL%NOTFOUND then
dbms_output.put_line('编号未找到。');
else
dbms_output.put_line('表已经更新');
end if;
end;
/
隐式游标实例4：（NOTFOUND使用）
SQL> declare
2 flag char:= 'U';
3 begin
4 update dept set dname='yingfeng' where deptno=50;
5 if SQL%NOTFOUND then
6 flag:='Z';
7 insert into dept(deptno,dname) values(50,'yingfeng');
8 end if;
9 if flag='U' then
10 dbms_output.put_line('update');
11 else
12 dbms_output.put_line('insert');
13 end if;
14 end;
15 /
隐式游标实例5：（FOUND使用）
SQL> begin
2 update mark set marks=60 where cource='001' and marks<60;
3 if SQL%FOUND then
4 dbms_output.put_line('成绩已更新');
5 end if;
6 end;
7 /
隐式游标实例6：（rowcount使用）
SQL> begin update emp set sal = sal+200 where sal<2000;
2 Dbms_Output.put_line('update'||SQL%rowcount||'records');
3 end;
4 /
说明：返回受影响的行数
隐式游标实例6：（rowcount使用）
SQL> declare
2 v_rows number;
3 begin
4 update emp set comm = 1000 where deptno=30;
5 v_rows:=SQL%rowcount;
6 dbms_output.put_line('update '|| v_rows ||' rows');
7 end;
8 /
说明：返回受影响的行数
隐式游标实例8：（促发异常，隐式游标不起作用）
SQL> declare
2 empid varchar2(10);
3 desig varchar2(10);
4 begin
5 empid:='&Employeeid';
6 select designation into desig from employee where empno=empid;
7 exception
8 when NO_DATA_FOUND then
9 dbms_output.put_line('not found data');
10 end;
11 /
说明：如果select into语句没有返回值，将促发NO_DATA_FOUND异常
隐式游标实例8：（促发异常，隐式游标不起作用）
SQL> declare
2 empid varchar2(10);
3 begin
4 select empno into empid from empployee;
5 exception
6 when TOO_MANY_ROWS then
7 dbms_output.put_line('too many rows');
8 end;
9 /
说明：如果select into语句返回多个值，将促发TOO_MANY_ROWS异常
隐式游标小结：
隐式游标由PL/SQL自动定义、打开和关闭
隐式游标的应用
二、显示游标
显示游标在PL/SQL块的声明部分定义查询，该查询可以返回多行。
显示游标的操作过程：首先数据库打开游标；然后提取行，把提取的行的数据放到变量中去；使用完之后最后要关闭游标。如果有多行需要用循环来控制。如图所示：
声明游标：cursor is select
打开游标：open
结果集控制：fetch into
关闭游标：close
显示游标实例1：（打开显示游标）
SQL> declare
2 my_toy_price toys.toyprice%type;
3 cursor toy_cur is
4 select toyprice from toys where toyprice<250;
5 begin
6 open toy_cur;
7 loop
8 fetch toy_cur into my_toy_price;
9 exit when toy_cur%NOTFOUND;
10 dbms_output.put_line('toyprice='||my_toy_price);
11 end loop;
12 close toy_cur;
13 end;
14 /
显示游标实例2：（打开显示游标）
SQL> declare
2 cursor mycur is
3 select * from dept order by deptno;
4 myreco dept%rowtype;
5 begin
6 open mycur;
7 fetch mycur into myreco;
8 while mycur%found loop
9 dbms_output.put_line(myreco.deptno || ' ' ||myreco.log);
10 fetch mycur into myreco;
11 end loop;
12 close mycur;
13 end;
14 /
带参数的显示游标:
声明显示游标时可以带参数以提高灵活性
声明带参数的显示游标的语法如下：
Cursor<cursor_name>(<param_name><param_type>) is select_statement;
显示游标实例3：（带参数的显示游标）
SQL> declare
2 desig varchar2(20);
3 emp_code varchar2(5);
4 empnm varchar2(20);
5 cursor emp_cur(desigparam varchar2) is
6 select empno,ename from employee where designation = desig;
7 begin
8 desig:='&desig';
9 open emp_cur(desig);
10 loop
11 fetch emp_cur into emp_code,empnm;
12 exit when emp_cur%NOTFOUND;
13 dbms_output.put_line(emp_code||' '||empnm);
14 end loop;
15 close emp_cur;
16 end;
17 /
显示游标实例4：（带参数的显示游标）
SQL> declare
2 cursor mycur(my_job varchar2) is
3 select * from emp where job=my_job;
4 begin
5 for myreco in mycur('SALESMAN') loop
6 dbms_output.put_line(myreco.ename);
7 end loop;
8 end;
9 /
显示游标实例5：（带参数的显示游标）
SQL> declare
2 dept_no emp.deptno%type;
3 emp_no emp.empno%type;
4 emp_name emp.ename%type;
5 cursor emp_cur(deptparam number) is
6 select empno,ename from emp where deptno=deptparam;
7 begin
8 dept_no:=&deptno;
9 open emp_cur(dept_no);
10 loop
11 fetch emp_cur into emp_no,emp_name;
12 dbms_output.put_line(emp_no||' '||emp_name);
13 exit when emp_cur%NOTFOUND;
14 end loop;
15 close emp_cur;
16 end;
17 /
使用显示游标更新行
运行使用游标删除或更新活动集中的行
声明游标时必须使用select...for update 语句
语法如下：
Cursor<cursor_name> is select statement for update;
更新的语法：
Update<table_name> set<set_clause> where current of<cursor_name>
删除的语法：
Delete from<table_name> where current of<cursor_name>
显示游标实例6：（使用显示游标更新行）
SQL> declare
2 new_price number;
3 cursor cur_toy is
4 select toyprice from toys where toyprice<100
5 for update of toyprice;
6 begin
7 open cur_toy;
8 loop
9 fetch cur_toy into new_price;
10 exit when cur_toy%NOTFOUND;
11 update toys
12 set toyprice=10*new_price
13 where current of cur_toy;
14 end loop;
15 close cur_toy;
16 commit;
17 end;
18 /
显示游标实例7：（使用显示游标删除行）
SQL> declare
2 cursor mycur(dept_no integer) is
3 select * from dept where deptno>dept_no for update;
4 begin
5 for myreco in mycur(50) loop
6 delete from dept where current of mycur;
7 end loop;
8 end;
9 /
循环游标
循环游标用于简化游标处理代码
当用户需要从游标中提取所有记录时使用
循环游标的语法如下：
for<record_index> in <cursor_name>
Loop
<executable statements>
End loop;
显示游标实例8：（循环游标）
SQL> declare
2 cursor mytoy_cur is
3 select toyid,name,toyprice from toys;
4 begin
5 for toy_rec in mytoy_cur
6 loop
7 dbms_output.put_line(toy_rec.toyid||' '||toy_rec.name||' '||toy_rec.toyprice);
8 end loop;
9 end;
10 /
显示游标实例9：（循环游标）
SQL> declare
2 cursor c_dept is
3 select deptno,dname from dept order by deptno;
4 cursor c_emp(p_dept varchar2) is
5 select ename,sal from emp where deptno=p_dept order by ename;
6 v_salary emp.sal%type;
7 begin
8 for r_dept in c_dept loop
9 dbms_output.put_line('Department:'||r_dept.deptno||'-'||r_dept.dname);
10 v_salary:=0;
11 for r_emp in c_emp(r_dept.deptno)loop
12 dbms_output.put_line('Name:'||r_emp.ename||' salary='||r_emp.sal);
13 v_salary:=v_salary+r_emp.sal;
14 end loop;
15 dbms_output.put_line('Total salary for dept:'||v_salary);
16 end loop;
17 end;
18 /
三、REF游标和游标变量
REF游标和游标变量用于处理运行是动态执行的SQL查询
创建游标变量需要两个步骤：
1、声明REF游标类型
2、声明REF游标类型的变量
用于声明REF游标类型的语法为：
Type<ref_cursor_name> is ref cursor [return <return_type>];
打开游标变量的语法如下：
Open cursor_name for select_statement;
声明强类型的REF游标（指定返回值类型）
Type my_curtype is ref cursor
Return stud_det%ROWTYPE;
Order_cur my_curtype;
声明弱类型的REF游标（没有指定返回类型）
Type my_ctype is ref cursor;
Stud_cur my_ctype;
Ref游标实例1：（弱类型ref实现）
SQL> declare
2 type cursor_type is ref cursor;
3 stu_cursor cursor_type;
4 v_stu student%rowtype;
5 begin
6 open stu_cursor for
7 select * from student where sex = 'M';
8 loop
9 fetch stu_cursor into v_stu;
10
10 exit when stu_cursor%notfound;
11 dbms_output.put_line(v_stu.studentid||' '||v_stu.name||' '||v_stu.sex||' '||v_stu.age);
12 end loop;
13 close stu_cursor;
14 end;
15 /
游标变量的优点和限制
游标变量的功能强大，可以简化数据处理。
游标变量的优点有：
1、可以从不同的select语句中提取结果集
2、可以作为过程的参数进行传递
3、可以引用游标的所有属性
4、可以进行赋值运算
使用游标变量的限制：
1、不能在程序包中声明游标变量
2、For update自己不能与游标变量一起使用
3、不能使用比较运算符
使用游标变量执行动态SQL
可以使用游标变量执行动态构造的SQL语句
打开执行动态SQL的游标变量的语法如下：
Open cursor_name for dynamic_sqlstring [using bing_argument_list]
Ref游标实例2：（使用游标变量执行动态sql）
SQL> declare
2 r_emp emp%rowtype;
3 type c_type is ref cursor;
4 cur c_type;
5 p_salary number;
6 begin
7 p_salary:=2500;
8 open cur for 'select * from emp where sal>:1 order by sal desc' using p_salary;
9 dbms_output.put_line('薪水大于'||p_salary||'的员工有：');
10 loop
11 fetch cur into r_emp;
12 exit when cur%notfound;
13 dbms_output.put_line('编号：'||r_emp.empno||' 姓名：'||r_emp.ename||' 薪水：'||r_emp.sal);
14 end loop;
15 close cur;
16 end;
17 /
说明：>:1表示吧using后面的p_salary的值赋值进去sql语句中。如果有多个变量，可以用1、2、3、4分别代替，using后面变量之间用“，”隔开。
Ref游标实例3：（使用游标变量执行动态sql）
SQL> declare
2 type emp_type is ref cursor;
3 cur emp_type;
4 name varchar2(20);
5 salary number(7,2);
6 begin
7 open cur for 'select ename,sal from emp where job=:1'
8 using 'SALESMAN';
9 loop
10 fetch cur into name,salary;
11 exit when cur%notfound;
12 dbms_output.put_line(name||':'||salary);
13 end loop;
14 close cur;
15 end;
16 /
使用bulk
使用bulk批量提取游标，可以加快我们提取数据的速度，可以不需要用循环来一行行地提取。提高插入、更新和删除的效率。
Ref游标实例4：（bulk批量提取的使用）
SQL> declare
2 type empcurtyp is ref cursor;
3 type idlist is table of emp.empno%type;
4 type namelist is table of emp.ename%type;
5 type sallist is table of emp.sal%type;
6 emp_cv empcurtyp;
7 ids idlist;
8 names namelist;
9 sals sallist;
10 row_cn number;
11 begin
12 open emp_cv for select empno,ename,sal from emp;
13 fetch emp_cv bulk collect into ids,names,sals;
14 close emp_cv;
15 for i in ids.first..ids.last loop
16 dbms_output.put_line(ids(i)||' '||names(i)||' '||sals(i));
17 end loop;
18 end;
19 /
Cursor与Ref Cursor区别
从技术底层看，两者是相同的。普通plsql cursor在定义时是“静态”的。而Ref cursors可以动态打开。
1、Ref cursor根据逻辑动态打开；而游标cursor定义好了就无法修改了
2、Ref cursor可以返回给客户端，cursor则不行
3、Cursor可以是全局的global，ref cursor则必须定在过程或函数中
4、Ref cursor可以在子程序间传递，cursor则不行。
5、Cursor中定义的静态sql比ref cursor效率高，所以ref cursor通常用在：向客户端返回结果集。
游标总结：
1、游标用于处理查询结果集中的数据
2、游标类型有：隐式游标、显示游标和REF游标
3、隐式游标由PL/SQL自动定义、打开和关闭
4、显示游标用于处理返回多行的查询
5、显示游标可以删除和更新活动集中的行
6、要处理结果集中所有的记录时，可使用循环游标
7、在声明REF游标时，不需要将select语句与其他关联。
- 游标的灵活运用
- MathTypy的灵活运用
- onMouseover的灵活运用
- javascript “||”、“&&”的灵活运用
- MathType的灵活运用
- javascript “||”、“&&”的灵活运用
- javascript “||”、“&&”的灵活运用
- javascript “||”、“&&”的灵活运用
- javascript “||”、“&&”的灵活运用
- js “||”、“&&”的灵活运用
- javascript “||”、“&&”的灵活运用
- Activity的灵活运用
- tomcat的灵活运用
- for的灵活运用
- java的灵活运用
- js “||”、“&&”的灵活运用
- 指针的灵活运用
- 变长数组的灵活运用
- 关于博客中的126相册图片不显示的问题
- hdoj - 2196 - Computer
- Windows Azure微软免费云空间成功申请和使用教程:安装WP博客
- 自家电脑搭建服务器可能遇到的问题及解决方法
- Linux文件共享（四）——父进程与子进程之间的文件共享
- 游标的灵活运用
- Java 返回文件名字，不加后缀
- js 判断页面是第一次加载
- javaBean方式将不含时分秒的日期格式插入oracle数据库
- 《古剑奇谭》席卷暑期档 张檬挑战霸气御姐
- android apk安装
- 在工作使有效的沟通生产力推进人际沟通
- Cocos2d-x中Lua与C++通信
- Linux - vsFTPd 服务器的安装与配置
