一。游标

1.定义：

游标是Oracle服务器为SQL语句建立的一个私有的SQL工作区。

2.种类：

显示游标：由编程者明确指定。 隐式游标：在缺省情况下，Oracle服务器以隐式游标来编译和执行SQL。

3.SQL游标属性：

使用SQL游标属性，可以测试SQL语句的输出。

游标属性说明SQL%ROWCOUNT最后依次执行的SQL语句所影响到的数据行（一个整数值），几条数据受影响就返回几SQL%FOUND一个Boolean值，如果最后一次SQL语句影响到了一条或多条记录，则该值为true，否则为falseSQL%NOFOUND一个Boolean值，如果最后一次SQL语句影响到了一条或多条记录，则该值为false，否则为trueSQL%ISOPEN一个Boolean值，如果游标是打开的，则返回true，否则返回false；对于隐式游标永远为false，因为语句块执行完毕后，游标自动关闭

二。显示游标

1.控制显示游标

①declare：创建一个命名的SQL数据区 ②open：打开活动数据集 ③fetch：将当前行载入到变量中 ④判断：对当前行进行检验，如果有数据行，继续执行第三步；如果没有数据行，跳到下一步。 ⑤close：释放活动数据集

2.游标的声明

语法：

CURSOR cursor_name IS select statement;

注意： 游标中没有into 查询中可以使用order by

例子：

declare cursor emp_cursor is select empno, ename from emp; cursor dept_cursor is select * from dept where deptno=10; begin ....

3.显示游标的属性

属性类型描述%isopenBoolean如果游标是打开的，返回true%notfoundBoolean如果最近一次fetch没有找到数据，则返回true%foundBoolean如果最近一次fetch有一行数据，则返回true%rowcountnumber返回行数的总和

例子：

if not emp_cursor%isopen then open emp_cursor; end if; loop fetch emp_cursor...

4.操作游标步骤

①打开游标

open cursor_name;

打开游标的作用是执行查询，并获得活动数据集。 查询不返回数据集是正常的，这并不是一个异常。 进行一次fetch操作后，使用游标属性来检验输出。

②从游标中fetch数据

fetch cursor_name into v_empno, v_ename;

将游标的当前行取到变量中去。 变量应与游标的各个变量一一对应。 检验游标中是否存在数据行值。

③关闭游标

close cursor_name;

结束处理后，关闭游标。 如果需要，可以重新打开数据行 一旦一个游标被关闭，就不能从游标中获取数据

5.for循环游标

for循环游标是处理显示游标的捷径 open、fetch、close都暗含在语句里面 寻访数据行的记录是隐士声明的

语法：

FOR record_name IN cursor_name LOOP statement1; statement2; ... END LOOP;

6.隐式的声明一个游标

BEGIN FOR emp_record IN (select ename, deptno from emp ) LOOP IF emp_record.deptno=30 THEN ... END LOOP; END;

7.练习

员工表：

enamesal张三6000李四5000王五5000赵六4000刘七3000

①查出员工工资，降序排列，用户输入4，查出前四个人的工资，然后用游标把查出来的信息插入top_sales表中。

declare cursor emp_cursor is select * from (select ename, sal from emp order by sal desc) where rownum<=&rownum; top_emp emp_cursor %rowtype; begin open emp_cursor ; loop fetch emp_cursor into top_emp ; exit when emp_cursor%notfound; insert into top_sales values(top_emp.ename, top_emp.sal ); end loop; close emp_cursor ; end;

②查出员工工资，降序排列，用户输入4，查出工资数目前四的人，然后用游标把查出来的信息插入top_sales表中。

declare cursor emp_cursor is select * from (select ename, sal from emp order by sal desc) where rownum<=&rownum; top_emp emp_cursor %rowtype; same_sal emp.sal%type; same_ename emp.ename%type; cursor same_emp_cursor is select ename, sal from emp where sal=same_sal and ename<>same_ename ; same_emp same_emp_cursor %rowtype; begin open emp_cursor ; loop fetch emp_cursor into top_emp ; exit when emp_cursor %notfound; insert into top_sales values(top_emp.ename, top_emp.sal); same_sal :=top_emp.sal; same_ename :=top_emp.ename; open same_emp_cursor ; loop fetch same_emp_cursor into same_emp ; exit when same_emp_cursor %notfound; insert into top_sales values(same_emp.ename, same_emp.sal ); end loop; close same_emp_cursor ; end loop; close emp_cursor ; end;

三。高级游标

1.带参数的游标

declare cursor emp_cursor (p_deptno number, p_job varchar2(10) is select empno, ename from emp where deptno=p_deptno and job=p_job ; begin open emp_cursor(10,'CLERK'); ...........

2.for update子句

使用for update语句使得事务在运行期间，其他事务对同一数据的访问被拒绝。 通俗的讲就是锁表。 解释一下：在企业级开发中，会把oracle数据库装在公司服务器上，表也建在服务器上，所有的开发者通过链接服务器地址去操作数据库和数据库的表、视图等。如果一个开发者A对emp表进行for update操作，其他开发者如果想要对emp表进行update或者delete的时候，会陷入等待。直到开发者A对emp表进行commit操作之后，emp表的update个delete才能正常进行。

1.select * from Table1 for update 锁定表天、Table1的所有行，其他用户只能读不能写

2 select * from Table1 where id = 1 for update 只锁定表Table1的id=1的行

3 select * from Table1 a join Table2 b on a.id=b.id for update 锁定两个表的所有记录

4 select * from Table1 a join Table2 b on a.id=b.id where a.id = 10 for update 锁定两个表的中满足条件的行

5. select * from Table1 a join Table2 b on a.id=b.id where a.id = 10 for update of a.id 只锁定Table1中满足条件的行

划重点： for update 是把所有的表都锁点 for update of 根据of 后表的条件锁定相对应的表。

3.nowait与wait

讲的详细点： 开发者A：select * from emp where empno=6666 for update; ① 若开发者B：select * from emp where empno=1111 for update;此时不会阻塞，但是会显示no rows selected。事实上empno=1111的员工是存在的。 ②若开发者B：select * from emp where empno=6666 for update;此时会陷入阻塞状态，只有开发者A执行commit后，B才会从阻塞状态出来。 ③ 若开发者B：select * from emp for update;也会陷入阻塞，只有开发者A执行commit后，B才会从阻塞状态出来。 ④开发者B：select * from emp for update nowait;程序正常执行。 ⑤开发者B：select * from emp where empno=6666 for update nowait;程序正常执行。 ⑥开发者B：select * from emp where empno=6666 for update wait 3此时程序会在3秒后报错：ORA-30006: resource busy; acquire with WAIT timeout expired

4.where current of

当你要使用游标进行更新和删除操作时，则需要使用where current of 语句，这个是标示出当前游标的位置． 例子：

declare cursor sal_cursor is select sal from emp where deptno=30; for update of sal nowait; begin for emp_record in sal_cursor loop update emp set sal=emp_record.sal*1.1 where current of sal_cursor; end loop; commit; end;

5.带子查询的游标

例子：查询同一个部门员工大于5为的员工信息。

declare cursor emp_cursor is select t1.empno, t1.ename, t1.job, t1.sal, t2.deptno from emp t1,(select deptno,count(*) cols from emp group by deptno) t2 where t1.deptno=t2.deptno and t2.cols>5; emp_info emp_cursor%rowtype; begin open emp_cursor ; loop fetch emp_cursor into emp_info ; exit when emp_cursor%notfound; dbms_output.put_line(emp_info.empno || ', '|| emp_info.ename || ', '||emp_info.job || ', '||emp_info.sal ); end loop; close emp_cursor; end;

6.练习

①使用游标从dept表中提出部门编号。将这个参数传递到另一个游标中，取出emp表中工作在该部门的员工信息。

declare cursor dept_cursor is select deptno from dept; dept_rec number(3); cursor emp_cursor(v_deptno number) is select empno, ename, sal, job, hiredate from emp where deptno=v_deptno; emp_rec emp_cursor%rowtype; begin open dept_cursor ; loop fetch dept_cursor into dept_rec; exit when dept_cursor%notfound; for emp_rec in emp_cursor(dept_rec) loop dbms_output.put_line(emp_rec.empno || ', '|| emp_rec.ename || ', '||emp_rec.sal || ', '||emp_rec.job || ', '||emp_rec.hiredate ); end loop; end loop; end;

②使用游标来emp表中员工的工资，工资大于等于1500的增加20%，小于1500的增加30%。

declare cursor sal_cursor is select sal from emp for update of sal nowait; emp_record sal_cursor%rowtype; begin for emp_record in sal_cursor loop if emp_record.sal >=1500 then update emp set sal = 1.2*emp_record.sal where current of sal_cursor; elsif emp_record.sal<1500 then update emp set sal = 1.3*emp_record.sal where current of sal_cursor; end if; end loop; end;