Oracle专题12之游标
- 什么是游标?
我们提供的服务有:成都网站设计、成都做网站、外贸网站建设、微信公众号开发、网站优化、网站认证、达州ssl等。为上千企事业单位解决了网站和推广的问题。提供周到的售前咨询和贴心的售后服务,是有科学管理、有技术的达州网站制作公司
- 游标是SQL的一个内存工作区,由系统或者用户以变量的形式定义。
- 游标的作用就是用于临时存储从数据库中提取的数据块。
- 通俗的来讲,游标就是一个结果集。
- 游标的类型分为显式游标和隐式游标。
1、显式游标处理的四个步骤
-
显式游标处理的语法:
1、定义游标:CURSOR cursor_name[(parameter_name datatype)] IS select_statement;
2、打开游标:OPEN cursor_name
3、提取数据:FETCH cursor_name INTO variable1[, variable2, ...];
4、关闭游标:CLOSE cursor_name。 - 代码演示:查询所有员工的员工号、姓名和职位的信息。
DECLARE --定义游标 CURSOR emp_cursor IS SELECT empno, ename, job FROM emp; v_empno emp.empno%TYPE; v_ename emp.ename%TYPE; v_job emp.job%TYPE; BEGIN --打开游标,执行查询 OPEN emp_cursor; --提取数据 LOOP FETCH emp_cursor INTO v_empno, v_ename, v_job; DBMS_OUTPUT.PUT_LINE('员工号:' || v_empno || ',姓名' || v_ename || ',职位' || v_job); --什么时候退出循环?%FOUND,%NOTFOUND EXIT WHEN emp_cursor%NOTFOUND; --EXIT WHEN NOT emp_cursor%FOUND; END LOOP; --关闭游标 CLOSE emp_cursor; END;
- FETCH语句的说明:1、把当前指针指向的记录返回;2、将指针指向下一条记录。
2、显式游标的四个属性
1、%FOUND:该属性用于检测游标结果集是否存在数据,如果存在数据,返回TRUE。
2、%NOTFOUND:该属性用于检测结果集是否存在数据,如果不存在数据,返回TRUE。
3、%ISOPEN:该属性用于检测游标是否已经打开,如果已经打开返回TURE。
4、%ROWCOUNT:该属性用于返回已经提取的实际行数。(如EXIT WHEN emp_cursor%ROWCOUNT=5;)
示例:按职工的职称涨工资,总裁涨1000元,经理涨500元,其他员工涨300元。
DECLARE --定义游标 CURSOR emp01_cursor IS SELECT empno, job FROM emp01; v_empno emp01.empno%TYPE; v_job emp01.job%TYPE; BEGIN --打开游标,执行查询 OPEN emp01_cursor; --提取数据 LOOP FETCH emp01_cursor INTO v_empno, v_job; IF v_job ='PRESIDENT' THEN UPDATE emp01 SET sal = sal + 1000 WHERE empno = v_empno; ELSIF v_job = 'MANAGER' THEN UPDATE emp01 SET sal = sal + 500 WHERE empno = v_empno; ELSE UPDATE emp01 SET sal = sal + 300 WHERE empno = v_empno; END IF; --什么时候退出循环?%FOUND,%NOTFOUND EXIT WHEN NOT emp01_cursor%FOUND; END LOOP; COMMIT; CLOSE emp01_cursor; --关闭游标 END;
3、游标的FOR循环
a、游标FOR循环的语法
- 当使用游标FOR循环时,Oracle会隐含地打开游标,提取数据并关闭游标。
- 语法如下:
FOR record_name IN cursor_name(或者可以使用子查询) LOOP statement; END LOOP;
- 代码演示:查询员工的员工号、姓名和职位。
DECLARE CURSOR emp_cursor IS SELECT empno, ename, job FROM emp; BEGIN FOR emp_record IN emp_cursor LOOP DBMS_OUTPUT.put_line('员工号:' || emp_record.empno || ', 姓名' || ',职位' || emp_record.job); END LOOP; END;
BEGIN FOR emp_record IN (SELECT empno, ename, job FROM emp) LOOP DBMS_OUTPUT.put_line('员工号:' || emp_record.empno || ', 姓名' || ',职位' || emp_record.job); END LOOP; END;
- 示例:按职工的职称涨工资,总裁涨1000元,经理涨500元,其他员工涨300元。
DECLARE --定义游标 CURSOR emp01_cursor IS SELECT empno, job FROM emp01; BEGIN FOR emp01_record IN emp01_cursor LOOP DBMS_OUTPUT.put_line(emp01_record.empno || '----' || emp01_record.job); IF emp01_record.job = 'PRECIDENT' THEN UPDATE emp01 SET sal = sal + 1000 WHERE empno = emp01_record.empno; ELSIF emp01_record.job = 'MANAGER' THEN UPDATE emp01 SET sal = sal + 500 WHERE empno = emp01_record.empno; ELSE UPDATE emp01 SET sal = sal + 300 WHERE empno = emp01_record.empno; END IF; END LOOP; COMMIT; END;
b、参数游标
- 参数游标是指带有参数的游标。通过使用参数游标,使用不同参数值可以生成不同的游标结果集。
- 定义和打开参数游标的语法如下:
CURSOR cursor_name (parameter_name datatype) IS select_statement; OPEN cursor_name (parameter_value);
- 代码演示:显示10号部门的所有员工。
DECLARE CURSOR emp_cursor(dno NUMBER) IS SELECT empno, ename, job FROM emp WHERE deptno = dno; BEGIN FOR emp_record IN emp_cursor(10) LOOP DBMS_OUTPUT.put_line('员工号:' || emp_record.empno || ',姓名:' || emp_record.ename || ',职位:' || emp_record.job); END LOOP; END;
4、隐式游标的处理
a、显式游标和隐式游标
- 显式游标是用户自定义的显式创建的游标,主要是用于对查询语句的处理。
- 隐式游标是由系统隐含创建的游标。主要是用于对非查询语句,如修改,删除等操作,则有Oracle系统自动地为这些操作设置游标并创建其工作区,对于隐式游标的操作,如定义、打开、取值以及关闭操作,都有Oracle系统自动完成,无需用户进行操作。
- 隐式游标的名字为SQL,这是由Oracle系统定义的。
b、隐式游标的详细说明
- 如DML操作和单行SELECT语句会使用隐式游标,他们是:
- 插入操作:INSERT、更新操作:UPDATE、删除操作:DELECT、单行查询操作:SELECT ... INTO。
- 当系统使用一个隐式游标时,可以通过隐式游标的属性来了解操作的状态和结果,进而控制程序的流程。(需要注意:通过SQL游标名总是只能访问前一个DML操作或者单行SELECT操作的游标属性。)
- 隐式游标的属性有:
SQL%FOUND SQL%NOTFOUND SQL%ISOPEN SQL%ROWCOUNT
- 代码演示:根据用户输入的员工号,更新指定员工的工资。(比如说工资涨100)
BEGIN UPDATE emp01 SET sal = 100 + sal WHERE empno = &n1; IF SQL%FOUND THEN dbms_output.put_line('成功修改员工的工资'); ELSE dbms_output.put_line('修改员工工资失败'); ROLLBACK; END IF; END;
5、使用游标修改或者删除数据1:对提取出来的数据进行行级锁定
- 为了对正在处理(查询)的行不被另外的用户改动,oracle提供一个FOR UPDATE子句来对所选择的行进行锁住。
- 如果当前用户锁定所选择的行,则其他用户不能进行对所选行进行修改,直到当前用户数据提交。
- 因此如果创建的游标需要执行更新或者删除的操作的时候必须带有FOR UPDATE子句。
- FOR UPDATE子句会将游标提取出来的数据进行行级锁定,这样在本会话更新期间,其他用户的会话就不能对当前游标中的数据行进行更新操作。
- 语法格式:
CURSOR cursor_name IS select_statement FOR UPDATE [OF column_reference] [NOWAIT]; UPDATE table_name SET column = ... WHERE CURRENT OF cursor_name; DELETE FROM table_name WHERE CURRENT OF cursor_name;
- 示例:按职工的职称涨工资,总裁涨1000元,经理涨500元,其他员工涨300元。
DECLARE --定义游标 CURSOR emp01_cursor IS SELECT empno, job FROM emp01 FOR UPDATE; BEGIN FOR emp01_record IN emp01_cursor LOOP DBMS_OUTPUT.put_line(emp01_record.empno || '----' || emp01_record.job); IF emp01_record.job = 'PRECIDENT' THEN UPDATE emp01 SET sal = sal + 1000 WHERE CURRENT OF emp01_cursor; ELSIF emp01_record.job = 'MANAGER' THEN UPDATE emp01 SET sal = sal + 500 WHERE CURRENT OF emp01_cursor; ELSE UPDATE emp01 SET sal = sal + 300 WHERE CURRENT OF emp01_cursor; END IF; END LOOP; COMMIT; END;
- WHERE CURRENT OF cursor_name子句表示当前游标提取的结果集中对应条件的数据行。
6、使用游标修改或者删除数据2:死锁状态
- 当其他用户正在对所选行进行修改,而当前用户也对当前行进行锁定,则会出现死锁状态。(一直等待,无法锁定)
a、NOWAIT子句
- 用于指定不等待锁,如果发现所操作的数据行已经锁定,将不会等待,立即返回。(可以避免死锁的状态)
b、使用of子句在特定表上加行共享锁
- 当游标子查询涉及到多张表时,如果在特定表上加行共享锁,那么需要使用OF子句。
- 需求:输入部门号,显示该部门名称以及员工的姓名,并删除该部门下的这些员工。
DECLARE CURSOR emp01_cursor IS SELECT d.dname dname, e.ename ename FROM emp01 e join dept d on e.deptno = d.deptno WHERE e.deptno = &deptno FOR UPDATE OF e.deptno; BEGIN FOR emp01_record IN emp01_cursor LOOP dbms_output.put_line('部门名称:' || emp01_record.dname || ',员工名:' || emp01_record.ename); DELETE FROM emp01 WHERE CURRENT OF emp01_cursor; END LOOP; COMMIT; END;
分享名称:Oracle专题12之游标
链接地址:http://scgulin.cn/article/pojggg.html