[by 王谭]oracle游标的使用教程

oracle游标的使用教程

---

游标的概念:
游标是SQL的一个内存工作区，由系统或用户以变量的形式定义。游标的作用就是用于临时存储从数据库中提取的数据块。在某些情况下，需要把数据从存放在磁盘的表中调到计算机内存中进行处理，最后将处理结果显示出来或最终写回数据库。这样数据处理的速度才会提高，否则频繁的磁盘数据交换会降低效率。
游标有两种类型：显式游标和隐式游标。在前述程序中用到的SELECT…INTO…查询语句，一次只能从数据库中提取一行数据，对于这种形式的查询和DML操作，系统都会使用一个隐式游标。但是如果要提取多行数据，就要由程序员定义一个显式游标，并通过与游标有关的语句进行处理。显式游标对应一个返回结果为多行多列的SELECT语句。
游标一旦打开，数据就从数据库中传送到游标变量中，然后应用程序再从游标变量中分解出需要的数据，并进行处理。
隐式游标
如前所述，DML操作和单行SELECT语句会使用隐式游标，它们是：
* 插入操作：INSERT。
* 更新操作：UPDATE。
* 删除操作：DELETE。
* 单行查询操作：SELECT … INTO …。
当系统使用一个隐式游标时，可以通过隐式游标的属性来了解操作的状态和结果，进而控制程序的流程。隐式游标可以使用名字SQL来访问，但要注意，通过SQL游标名总是只能访问前一个DML操作或单行SELECT操作的游标属性。所以通常在刚刚执行完操作之后，立即使用SQL游标名来访问属性。游标的属性有四种，如下所示。

隐式游标的属性 返回值类型 意 义
SQL%ROWCOUNT 整型 代表DML语句成功执行的数据行数
SQL%FOUND 布尔型 值为TRUE代表插入、删除、更新或单行查询操作成功
SQL%NOTFOUND 布尔型 与SQL%FOUND属性返回值相反
SQL%ISOPEN 布尔型 DML执行过程中为真，结束后为假

–以下建表、插入数据、游标测试代码在plsqldeveloper内创建sql窗口直接运行即可
先把以下demo内的代码运行环境配置下吧！
我都测试过了，嘎嘎

create table  emp(
name  varchar2(20),
sex varchar2(20),
dept varchar2(20)
);
insert into  emp values('tom','男','IT');
insert into  emp values('jarry','女','sell');
insert into  emp values('jason','男','manager');

举个栗子！使用隐式游标的属性，判断对员工性别描述的修改是否成功。
输入和运行以下程序：

     BEGIN  
        UPDATE emp SET sex=sex||'生' WHERE sex='女';   
         IF SQL%FOUND THEN    
        DBMS_OUTPUT.PUT_LINE('成功修改性别描述！');   
        COMMIT;    
        ELSE  
        DBMS_OUTPUT.PUT_LINE('修改性别描述失败！');   
         END IF;    
        END;  

显式游标
游标的定义和操作
游标的使用分成以下4个步骤。
1．声明游标
在DECLEAR部分按以下格式声明游标：
CURSOR 游标名[(参数1 数据类型[，参数2 数据类型…])]
IS SELECT语句;
参数是可选部分，所定义的参数可以出现在SELECT语句的WHERE子句中。如果定义了参数，则必须在打开游标时传递相应的实际参数。
SELECT语句是对表或视图的查询语句，甚至也可以是联合查询。可以带WHERE条件、ORDER BY或GROUP BY等子句，但不能使用INTO子句。在SELECT语句中可以使用在定义游标之前定义的变量。
2．打开游标
在可执行部分，按以下格式打开游标：
OPEN 游标名[(实际参数1[，实际参数2…])];
打开游标时，SELECT语句的查询结果就被传送到了游标工作区。
3．提取数据
在可执行部分，按以下格式将游标工作区中的数据取到变量中。提取操作必须在打开游标之后进行。
FETCH 游标名 INTO 变量名1[，变量名2…];
或
FETCH 游标名 INTO 记录变量;
游标打开后有一个指针指向数据区，FETCH语句一次返回指针所指的一行数据，要返回多行需重复执行，可以使用循环语句来实现。控制循环可以通过判断游标的属性来进行。
下面对这两种格式进行说明：
第一种格式中的变量名是用来从游标中接收数据的变量，需要事先定义。变量的个数和类型应与SELECT语句中的字段变量的个数和类型一致。
第二种格式一次将一行数据取到记录变量中，需要使用%ROWTYPE事先定义记录变量，这种形式使用起来比较方便，不必分别定义和使用多个变量。
定义记录变量的方法如下：
变量名 表名|游标名%ROWTYPE；
其中的表必须存在，游标名也必须先定义。
4．关闭游标
CLOSE 游标名;
显式游标打开后，必须显式地关闭。游标一旦关闭，游标占用的资源就被释放，游标变成无效，必须重新打开才能使用。
以下是使用显式游标的一个简单练习。

再举一堆栗子！使用显式游标：
for循环中游标的使用 与 其他不同，在for循环中使用游标的时候:不用特意打开游标 ，游标是自动打开的。

declare

       cursor c_job   --定义一个游标，并给它起个名字叫c_job
       is
       select name,sex,dept
       from emp;

       c_row c_job%rowtype;   --定义一个游标变量c_row，该类型为游标c_job中的一行数据类型
begin
       for c_row in c_job loop
         dbms_output.put_line(c_row.name||'-'||c_row.sex||'-'||c_row.dept);
       end loop;
end;

说明：可以看到该循环形式非常简单，隐含了记录变量的定义、游标的打开、提取和关闭过程。c_row为隐含定义的记录变量，循环的执行次数与游标取得的数据的行数相一致。

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

declare 
       cursor c_job --定义一个游标，并给它起个名字叫c_job
       is
       select name,sex,dept
       from emp;
       c_row c_job%rowtype;    --定义一个游标变量
begin
       open c_job;     --打开游标
         loop

           fetch c_job into c_row;   --提取一行数据到c_row
           --判读是否提取到值，没取到值就退出
           --取到值c_job%notfound 是false 
           --取不到值c_job%notfound 是true
           exit when c_job%notfound;
            dbms_output.put_line(c_row.name||'-'||c_row.sex||'-'||c_row.dept);
         end loop;
       --关闭游标
      close c_job;
end;

下面这个栗子是一个update操作，我们可以用隐式游标sql的属性%found,%notfound,%rowcount,%isopen观察update语句的执行情况。

      begin
         update emp set sex='女' WHERE name='jasom';
         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('没有找到符合条件的数据行');
              end if;
              if sql%notfound then
                dbms_output.put_line('执行完毕');
                else
                  dbms_output.put_line('正在执行');
                  end if;
                   dbms_output.put_line('本次操作共更改'||sql%rowcount||'行');
                   exception 
                     when no_data_found then
                       dbms_output.put_line('找不到可操作的数据');
                       when too_many_rows then
                         dbms_output.put_line('Too Many rows');
                         end;

declare
       empname emp.name%TYPE;
       empdept emp.dept%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(empname);
                 end if;
                 dbms_output.put_line('共检索到'||sql%rowcount||'行数据');
                 dbms_output.put_line('-------------');

                 select name,dept into  empname,empdept from emp where name='tom';
                 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('检索出的员工是：'||empname);
                 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;

–使用游标和loop循环来显示所有部门的名称
–游标声明
–遇到for循环不必亲自打开关闭游标哦！

declare 
       cursor csr_dept
       is
       --select语句
       select distinct dept
       from emp;
       --指定行指针,这句话应该是指定和csr_dept行类型相同的变量
       row_dept csr_dept%rowtype;
begin
       --for循环
       for row_dept in csr_dept loop
           dbms_output.put_line('部门名称:'||row_dept.dept);
       end loop;
end;

–使用游标和while循环来显示所有部门的职员（用%found属性）

declare
       --游标声明
       cursor csr_TestWhile
       is
       --select语句
       select  name,dept
       from emp;
       --指定行指针
       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.name||'在'||row_loc.dept||'部门');
           --给下一行喂数据
           fetch csr_TestWhile into row_loc;
         end loop;
       close csr_TestWhile;
end; 

–向游标传递一个部门名称，用for循环和游标，打印出此部门的所有雇员的所有信息(使用循环游标)
–CURSOR cursor_name[(parameter[,parameter],…)] IS select_statement;
–定义参数的语法如下:Parameter_name [IN] data_type[{:=|DEFAULT} value]

declare 
      CURSOR 
      c_dept(p_dept varchar2)
      is
      select * from emp where emp.dept=p_dept;
      r_emp emp%rowtype;
begin
        for r_emp in c_dept('IT') loop
            dbms_output.put_line('员工名：'||r_emp.name||'性别：'||r_emp.sex);
        end loop;
end;

–向游标传递一个姓名，显示此职员的所有信息(使用参数游标)

declare 
       cursor
       c_name(p_name nvarchar2)
       is 
       select * from emp where name=p_name;
       r_name emp%rowtype;
begin 
       for r_name in c_name('tom') loop
           dbms_output.put_line('员工名'||r_name.name||' '||'员工性别'||r_name.sex||'所属部门'||r_name.dept);
        end loop;
end;

–编写一个PL/SQL程序块，对所有的职员名字前增加前缀“superman-”

declare
      cursor
          csr_Addname
      is
          select * from emp  FOR UPDATE OF name;
     r_Addname  emp%rowtype;

begin
    for r_Addname in csr_Addname LOOP

         UPDATE EMP SET name= 'superman-'||r_Addname.name;
    END LOOP;
END;

升级一下我的emp表，以完成接下来的操作~

alter table  emp add(hiredate  date) --添加一个叫做hiredate的字段，数据自己update一下就可以

–编写一个PL/SQL程序块，以提升2个入职时间最久的职员为MANAGER
–（提示：可以定义一个变量作为计数器控制游标只提取两条数据；也可以在声明游标的时候把雇员中资格最老的两个人查出来放到游标中。）

declare
    cursor crs_testComput
    is
    select * from emp 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.NAME||' 入职时间：'||r_testComput.HIREDATE);
             --计数器减一
             top_two:=top_two-1;
             FETCH crs_testComput INTO r_testComput;
           end loop;
     close crs_testComput;
end;

–编写一个PL/SQL程序块，对所有雇员按他们的基本薪水(sal)的20%为他们加薪，
–如果增加的薪水大于300就取消加薪(对emp1表进行修改操作，并将更新前后的数据输出出来)

DECLARE 
  CURSOR  CUR_TEST
  is
  SELECT  * FROM   EMP  FOR UPDATE OF SAL;
  CUR CUR_TEST%ROWTYPE;
  sal_add  emp.sal%type;
  sal_info emp.sal%type;
  BEGIN


      FOR  CUR  IN  CUR_TEST  LOOP

      sal_add:=CUR.SAL*0.2;

      IF   sal_add<300  then
        sal_info:=cur.sal+sal_add;
         DBMS_OUTPUT.put_line('原工资为'||CUR.SAL||'现在涨薪到'||sal_info);

         else  
           sal_info:=CUR.SAL;
           dbms_output.put_line('员工资为'||SAL_INFO||'保持原有工资');
           end  if;
           update emp  set sal=sal_info where current of  cur_test;
           END  LOOP;
           end;

–将每位员工工作了多少年零多少月零多少天输出出来
–近似
–CEIL(n)函数：取大于等于数值n的最小整数
–FLOOR(n)函数：取小于等于数值n的最大整数
–truc的用法 http://jingyan.baidu.com/article/c275f6ba2d2347e33c756753.html

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;

以上便是关于游标的使用教程，大家如果有建议和分享可以在评论栏指出，很高兴可以分享交流相关知识！