oracle pl sql游标(cursor)使用小结

oracle中的游标(cursor)实际上是pl sql中的游标,pl sql的游标分为隐式游标和显式游标,oracle服务器对于提交的SQL语句都是以隐式游标去处理的,在做开发的时候大部分考虑的都是显式游标。游标主要是用于解决,当查询结果返回多条记录,这时候需要对记录一条条进行处理的时候,当然,如果只有一条数据,也是可以通过游标去操作。

下面这段代码是我从网上摘来的,几乎包括了游标的方方面面,非常具有参考意义:

-- 声明游标;CURSOR cursor_name IS select_statement

--For 循环游标
--(1)定义游标
--(2)定义游标变量
--(3)使用for循环来使用这个游标
DECLARE
  --类型定义
  CURSOR c_job IS
    SELECT empno, ename, job, sal FROM emp WHERE job = 'MANAGER';
  --定义一个游标变量v_cinfo c_emp%ROWTYPE ,该类型为游标c_emp中的一行数据类型
  c_row c_job%ROWTYPE;
BEGIN
  FOR c_row IN c_job LOOP
    dbms_output.put_line(c_row.empno || '-' || c_row.ename || '-' ||
                         c_row.job || '-' || c_row.sal);
  END LOOP;
END;

--Fetch游标
--使用的时候必须要明确的打开和关闭

DECLARE
  --类型定义
  CURSOR c_job IS
    SELECT empno, ename, job, sal FROM emp WHERE job = 'MANAGER';
  --定义一个游标变量
  c_row c_job%ROWTYPE;
BEGIN
  OPEN c_job;
  LOOP
    --提取一行数据到c_row
    FETCH c_job
      INTO c_row;
    --判读是否提取到值,没取到值就退出
    --取到值c_job%notfound 是false 
    --取不到值c_job%notfound 是true
    EXIT WHEN c_job%NOTFOUND;
    dbms_output.put_line(c_row.empno || '-' || c_row.ename || '-' ||
                         c_row.job || '-' || c_row.sal);
  END LOOP;
  --关闭游标
  CLOSE c_job;
END;

--1:任意执行一个update操作,用隐式游标sql的属性%found,%notfound,%rowcount,%isopen观察update语句的执行情况。
BEGIN
  UPDATE emp SET ENAME = 'ALEARK' WHERE EMPNO = 7469;
  IF SQL%ISOPEN THEN
    dbms_output.put_line('Openging');
  ELSE
    dbms_output.put_line('closing');
  END IF;
  IF SQL%FOUND THEN
    dbms_output.put_line('游标指向了有效行'); --判断游标是否指向有效行
  ELSE
    dbms_output.put_line('Sorry');
  END IF;
  IF SQL%NOTFOUND THEN
    dbms_output.put_line('Also Sorry');
  ELSE
    dbms_output.put_line('Haha');
  END IF;
  dbms_output.put_line(SQL%ROWCOUNT);
EXCEPTION
  WHEN no_data_found THEN
    dbms_output.put_line('Sorry No data');
  WHEN too_many_rows THEN
    dbms_output.put_line('Too Many rows');
END;
DECLARE
  empNumber emp.EMPNO%TYPE;
  empName   emp.ENAME%TYPE;
BEGIN
  IF SQL%ISOPEN THEN
    dbms_output.put_line('Cursor is opinging');
  ELSE
    dbms_output.put_line('Cursor is Close');
  END IF;
  IF SQL%NOTFOUND THEN
    dbms_output.put_line('No Value');
  ELSE
    dbms_output.put_line(empNumber);
  END IF;
  dbms_output.put_line(SQL%ROWCOUNT);
  dbms_output.put_line('-------------');

  SELECT EMPNO, ENAME INTO empNumber, empName FROM emp WHERE EMPNO = 7499;
  dbms_output.put_line(SQL%ROWCOUNT);

  IF SQL%ISOPEN THEN
    dbms_output.put_line('Cursor is opinging');
  ELSE
    dbms_output.put_line('Cursor is Closing');
  END IF;
  IF SQL%NOTFOUND THEN
    dbms_output.put_line('No Value');
  ELSE
    dbms_output.put_line(empNumber);
  END IF;
EXCEPTION
  WHEN no_data_found THEN
    dbms_output.put_line('No Value');
  WHEN too_many_rows THEN
    dbms_output.put_line('too many rows');
END;

--2,使用游标和loop循环来显示所有部门的名称
--游标声明
DECLARE
  CURSOR csr_dept IS
  --select语句
    SELECT DNAME FROM Depth;
  --指定行指针,这句话应该是指定和csr_dept行类型相同的变量
  row_dept csr_dept%ROWTYPE;
BEGIN
  --for循环
  FOR row_dept IN csr_dept LOOP
    dbms_output.put_line('部门名称:' || row_dept.DNAME);
  END LOOP;
END;

--3,使用游标和while循环来显示所有部门的的地理位置(用%found属性)
DECLARE
  --游标声明
  CURSOR csr_TestWhile IS
  --select语句
    SELECT LOC FROM Depth;
  --指定行指针
  row_loc csr_TestWhile%ROWTYPE;
BEGIN
  --打开游标
  OPEN csr_TestWhile;
  --给第一行喂数据
  FETCH csr_TestWhile
    INTO row_loc;
  --测试是否有数据,并执行循环
  WHILE csr_TestWhile%FOUND LOOP
    dbms_output.put_line('部门地点:' || row_loc.LOC);
    --给下一行喂数据
    FETCH csr_TestWhile
      INTO row_loc;
  END LOOP;
  CLOSE csr_TestWhile;
END;
SELECT *
  FROM emp
       
       --4,接收用户输入的部门编号,用for循环和游标,打印出此部门的所有雇员的所有信息(使用循环游标)
       --CURSOR cursor_name[(parameter[,parameter],...)] IS select_statement;
       --定义参数的语法如下:Parameter_name [IN] data_type[{:=|DEFAULT} value]  
       
        DECLARE CURSOR c_dept(p_deptNo NUMBER) IS
         SELECT * FROM emp WHERE emp.depno = p_deptNo;


r_emp emp%ROWTYPE;
BEGIN
  FOR r_emp IN c_dept(20) LOOP
    dbms_output.put_line('员工号:' || r_emp.EMPNO || '员工名:' || r_emp.ENAME ||
                         '工资:' || r_emp.SAL);
  END LOOP;
END;
SELECT *
  FROM emp
       --5:向游标传递一个工种,显示此工种的所有雇员的所有信息(使用参数游标)
        DECLARE CURSOR c_job(p_job NVARCHAR2) IS
         SELECT * FROM emp WHERE JOB = p_job;


r_job emp%ROWTYPE;
BEGIN
  FOR r_job IN c_job('CLERK') LOOP
    dbms_output.put_line('员工号' || r_job.EMPNO || ' ' || '员工姓名' ||
                         r_job.ENAME);
  END LOOP;
END;
SELECT *
  FROM EMP
       
       --6:用更新游标来为雇员加佣金:(用if实现,创建一个与emp表一摸一样的emp1表,对emp1表进行修改操作),并将更新前后的数据输出出来
        CREATE TABLE emp1 AS
         SELECT * FROM emp;


DECLARE
  CURSOR csr_Update IS
    SELECT * FROM emp1 FOR UPDATE OF SAL;
  empInfo  csr_Update%ROWTYPE;
  saleInfo emp1.SAL%TYPE;
BEGIN
  FOR empInfo IN csr_Update LOOP
    IF empInfo.SAL < 1500 THEN
      saleInfo := empInfo.SAL * 1.2;
    ELSIF empInfo.SAL < 2000 THEN
      saleInfo := empInfo.SAL * 1.5;
    ELSIF empInfo.SAL < 3000 THEN
      saleInfo := empInfo.SAL * 2;
    END IF;
    UPDATE emp1 SET SAL = saleInfo WHERE CURRENT OF csr_Update;
  END LOOP;
END;

--7:编写一个PL/SQL程序块,对名字以‘A’或‘S’开始的所有雇员按他们的基本薪水(sal)的10%给他们加薪(对emp1表进行修改操作)
DECLARE
  CURSOR csr_AddSal IS
    SELECT *
      FROM emp1
     WHERE ENAME LIKE 'A%'
        OR ENAME LIKE 'S%'
       FOR UPDATE OF SAL;
  r_AddSal csr_AddSal%ROWTYPE;
  saleInfo emp1.SAL%TYPE;
BEGIN
  FOR r_AddSal IN csr_AddSal LOOP
    dbms_output.put_line(r_AddSal.ENAME || '原来的工资:' || r_AddSal.SAL);
    saleInfo := r_AddSal.SAL * 1.1;
    UPDATE emp1 SET SAL = saleInfo WHERE CURRENT OF csr_AddSal;
  END LOOP;
END;
--8:编写一个PL/SQL程序块,对所有的salesman增加佣金(comm)500
DECLARE
  CURSOR csr_AddComm(p_job NVARCHAR2) IS
    SELECT * FROM emp1 WHERE JOB = p_job FOR UPDATE OF COMM;
  r_AddComm emp1%ROWTYPE;
  commInfo  emp1.comm%TYPE;
BEGIN
  FOR r_AddComm IN csr_AddComm('SALESMAN') LOOP
    commInfo := r_AddComm.COMM + 500;
    UPDATE EMP1 SET COMM = commInfo WHERE CURRENT OF csr_AddComm;
  END LOOP;
END;

--9:编写一个PL/SQL程序块,以提升2个资格最老的职员为MANAGER(工作时间越长,资格越老)
--(提示:可以定义一个变量作为计数器控制游标只提取两条数据;也可以在声明游标的时候把雇员中资格最老的两个人查出来放到游标中。)
DECLARE
  CURSOR crs_testComput IS
    SELECT * FROM emp1 ORDER BY HIREDATE ASC;
  --计数器
  top_two      NUMBER := 2;
  r_testComput crs_testComput%ROWTYPE;
BEGIN
  OPEN crs_testComput;
  FETCH crs_testComput
    INTO r_testComput;
  WHILE top_two > 0 LOOP
    dbms_output.put_line('员工姓名:' || r_testComput.ENAME || ' 工作时间:' ||
                         r_testComput.HIREDATE);
    --计速器减一
    top_two := top_two - 1;
    FETCH crs_testComput
      INTO r_testComput;
  END LOOP;
  CLOSE crs_testComput;
END;

--10:编写一个PL/SQL程序块,对所有雇员按他们的基本薪水(sal)的20%为他们加薪,
--如果增加的薪水大于300就取消加薪(对emp1表进行修改操作,并将更新前后的数据输出出来) 
DECLARE
  CURSOR crs_UpadateSal IS
    SELECT * FROM emp1 FOR UPDATE OF SAL;
  r_UpdateSal crs_UpadateSal%ROWTYPE;
  salAdd      emp1.sal%TYPE;
  salInfo     emp1.sal%TYPE;
BEGIN
  FOR r_UpdateSal IN crs_UpadateSal LOOP
    salAdd := r_UpdateSal.SAL * 0.2;
    IF salAdd > 300 THEN
      salInfo := r_UpdateSal.SAL;
      dbms_output.put_line(r_UpdateSal.ENAME || ':  加薪失败。' || '薪水维持在:' ||
                           r_UpdateSal.SAL);
    ELSE
      salInfo := r_UpdateSal.SAL + salAdd;
      dbms_output.put_line(r_UpdateSal.ENAME || ':  加薪成功.' || '薪水变为:' ||
                           salInfo);
    END IF;
    UPDATE emp1 SET SAL = salInfo WHERE CURRENT OF crs_UpadateSal;
  END LOOP;
END;

--11:将每位员工工作了多少年零多少月零多少天输出出来   
--近似
--CEIL(n)函数:取大于等于数值n的最小整数
--FLOOR(n)函数:取小于等于数值n的最大整数
--truc的用法 http://publish.it168.com/2005/1028/20051028034101.shtml
DECLARE
  CURSOR crs_WorkDay IS
    SELECT ENAME,
           HIREDATE,
           trunc(months_between(SYSDATE, hiredate) / 12) AS SPANDYEARS,
           trunc(MOD(months_between(SYSDATE, hiredate), 12)) AS months,
           trunc(MOD(MOD(SYSDATE - hiredate, 365), 12)) AS days
      FROM emp1;
  r_WorkDay crs_WorkDay%ROWTYPE;
BEGIN
  FOR r_WorkDay IN crs_WorkDay LOOP
    dbms_output.put_line(r_WorkDay.ENAME || '已经工作了' ||
                         r_WorkDay.SPANDYEARS || '年,零' || r_WorkDay.months ||
                         '月,零' || r_WorkDay.days || '天');
  END LOOP;
END;

--12:输入部门编号,按照下列加薪比例执行(用CASE实现,创建一个emp1表,修改emp1表的数据),并将更新前后的数据输出出来
--  deptno  raise(%)
--  10      5%
--  20      10%
--  30      15%
--  40      20%
--  加薪比例以现有的sal为标准
--CASE expr WHEN comparison_expr THEN return_expr
--[, WHEN comparison_expr THEN return_expr]... [ELSE else_expr] END
DECLARE
  CURSOR crs_caseTest IS
    SELECT * FROM emp1 FOR UPDATE OF SAL;
  r_caseTest crs_caseTest%ROWTYPE;
  salInfo    emp1.sal%TYPE;
BEGIN
  FOR r_caseTest IN crs_caseTest LOOP
    CASE
      WHEN r_caseTest.DEPNO = 10 THEN
        salInfo := r_caseTest.SAL * 1.05;
      WHEN r_caseTest.DEPNO = 20 THEN
        salInfo := r_caseTest.SAL * 1.1;
      WHEN r_caseTest.DEPNO = 30 THEN
        salInfo := r_caseTest.SAL * 1.15;
      WHEN r_caseTest.DEPNO = 40 THEN
        salInfo := r_caseTest.SAL * 1.2;
    END CASE;
    UPDATE emp1 SET SAL = salInfo WHERE CURRENT OF crs_caseTest;
  END LOOP;
END;

--13:对每位员工的薪水进行判断,如果该员工薪水高于其所在部门的平均薪水,则将其薪水减50元,输出更新前后的薪水,员工姓名,所在部门编号。
--AVG([distinct|all] expr) over (analytic_clause)
---作用:
--按照analytic_clause中的规则求分组平均值。
--分析函数语法:
--FUNCTION_NAME(<argument>,<argument>...)
--OVER
--(<Partition-Clause><Order-by-Clause><Windowing Clause>)
--PARTITION子句
--按照表达式分区(就是分组),如果省略了分区子句,则全部的结果集被看作是一个单一的组
SELECT *
  FROM emp1 DECLARE CURSOR crs_testAvg IS
        SELECT EMPNO,
               ENAME,
               JOB,
               SAL,
               DEPNO,
               AVG(SAL) OVER(PARTITION BY DEPNO) AS DEP_AVG
          FROM EMP1
           FOR UPDATE OF SAL;


r_testAvg crs_testAvg%ROWTYPE;
salInfo emp1.sal%TYPE;
BEGIN
  FOR r_testAvg IN crs_testAvg LOOP
    IF r_testAvg.SAL > r_testAvg.DEP_AVG THEN
      salInfo := r_testAvg.SAL - 50;
    END IF;
    UPDATE emp1 SET SAL = salInfo WHERE CURRENT OF crs_testAvg;
  END LOOP;
END;

有时候我们打开游标是为了更新或者删除一些记录,这种情况下就需要在打开游标的时候锁定相关记录,这时候可以使用for update nowait语句,倘若锁定失败我们就不再继续,以免出现长时间等待资源的死锁情况。同时,for update of column_name也是对表的锁定,但是它往往用在对多表的锁定,如果在多表锁定的时候用for update,那么涉及到的每张表都会被锁定,而这不是我们想看到的,这时候就用for update of column_name锁定需要锁定的表,其中column_name是要锁定的表的字段名。

当我们需要在游标的循环体内处理某条记录的时候,比如对于工资小于5000的记录,对工资提升10%,这个时候就要在循环体内做update操作,这时候在写where语句的时候可以用到where current of cursor_name,就显得非常简单。如下所示:

DECLARE
  CURSOR sal_cursor IS
    SELECT e.department_id, employee_id, last_name, salary
      FROM employees e, departments d
     WHERE d.department_id = e.department_id
       AND d.department_id = 60
       FOR UPDATE OF salary NOWAIT;
BEGIN
  FOR emp_record IN sal_cursor LOOP
    IF emp_record.salary < 5000 THEN
      ---------------------------------
      UPDATE employees
         SET salary = emp_record.salary * 1.10
       WHERE CURRENT OF sal_cursor; -- 这里直接使用where current of sal_cursor
    END IF;
  END LOOP;
END;

2014-11-07补充:

最基本的游标控制命令包括open、fetch和close。首先用open语句初始化一个游标,然后重复执行fetch语句取出已检索到的数据,或是使用bulk collect批量选取数据。当处理完结果集中最后一行数据时,就可以用close语句关闭游标。此外,还可以同时打开多个游标并发处理多个查询操作。Oracle会隐式的打开一个游标处理所有不与显示游标相关联的SQL语句,在引用这个隐式SQL游标的时,虽然不能使用open、fetch和close语句来控制sql游标,但可以利用它的属性来获取雨最近执行的sql语句的相关信息。close语句在关闭游标以后,如果操作了一个已经关闭的游标则会抛出预定义异常invalid_cursor异常。如果在没有打开游标或游标变量的情况下使用%FOUND、%NOTFOUND、%ROWCOUNT,将会引起预定义异常invalid_cursor。

Oracle游标有如下4个属性:

(1)%ISOPEN:判断游标是否被打开,如果打开,则%ISOPEN等于true,否则等于false。

(2)%FOUND和%NOTFOUND:判断游标所在的行是否有效,如果有效,则%FOUND等于true,否则等于false。

(3)%ROWCOUNT:返回当前位置为止游标读取的记录行数。

本文标题:oracle pl sql游标(cursor)使用小结

本文链接:http://yedward.net/?id=114

本文版权归作者所有,欢迎转载,转载请以文字链接的形式注明文章出处。

相关文章