oracle游标使用

最新推荐文章于 2021-12-08 16:29:29 发布

ceclar123

最新推荐文章于 2021-12-08 16:29:29 发布

阅读量813

点赞数

分类专栏： PL/SQL学习文章标签： oracle sql c integer insert delete

本文链接：https://blog.csdn.net/ceclar123/article/details/7973720

版权

PL/SQL学习专栏收录该内容

11 篇文章 0 订阅

订阅专栏

在Oracle数据库中，游标分为显式游标与隐式游标，其中隐式游标又称为SQL游标，专门用于处理select into、update、insert、delete语句，而显式游标常用处理多行数据的select语句。

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

一、SQL游标

1、SQL%ISOPEN

当在pl/sql块中使用select into、update、insert、delete语句的时候，Oracle会自动打开游标，并且在语句执行完了之后自动关闭，对于开发人员来说，不用关心该属性。

2、SQL%FOUND

该属性用于确定SQL语句是否执行成功（根据是否作用于行来判断，一个update语句，更新了0行，返回false…………）

begin
  declare 
    v_deptno dept.deptno%type;
  begin
    v_deptno:=0;
    update dept set loc='Hello' where deptno=0;
    if SQL%FOUND then
      dbms_output.put_line('true查询到了数据');
    else
      dbms_output.put_line('false没有查询到数据');  
    end if;
  end;
end;

3、SQL%NOTFOUND

该属性用于确定SQL语句是否执行失败（根据是否作用于行来判断，一个update语句，更新了0行，返回true…………）

begin
  declare 
    v_deptno dept.deptno%type;
  begin
    v_deptno:=0;
    update dept set loc='Hello' where deptno=0;
    if SQL%NOTFOUND then
      dbms_output.put_line('true没有查询到数据');
    else
      dbms_output.put_line('false查询到数据');  
    end if;
  end;
end;

4、SQL%ROWCOUNT

该属性返回SQL语句作用于行的行数

begin
  declare 
    v_deptno dept.deptno%type;
  begin
    v_deptno:=0;
    update dept set loc='Hello' where deptno=10;   
    dbms_output.put_line(SQL%ROWCOUNT||'行数据更新');    
  end;
end;

二、显示游标

1、%ISOPEN

判断游标是否已经打开，如果打开，返回true，否则返回false，例如：

if not cursor_name%ISOPEN then
  open cursor_name;
end if;

2、%FOUND

用于检测游标结果集是否有数据，如果存在数据，则返回true，否则返回false

begin
  declare 
    v_dname dept.dname%type;
    v_loc dept.loc%type;
    cursor c_dept is select dname,loc from dept where deptno=10;
  begin
    if not c_dept%ISOPEN then
      open c_dept;
    end if;       
    loop
      fetch c_dept into v_dname,v_loc;       
        exit when not c_dept%FOUND;
        dbms_output.put_line('部门名称：'||v_dname||'   部门地址：'||v_loc);  
    end loop;    
    close c_dept;
  end;
end;

3、%NOTFOUND

用于检测游标结果集是否不存在数据，如果不存在数据，则返回true，否则返回false

begin
  declare 
    v_dname dept.dname%type;
    v_loc dept.loc%type;
    cursor c_dept is select dname,loc from dept where deptno=10;
  begin
    if not c_dept%ISOPEN then
      open c_dept;
    end if;       
    loop
      fetch c_dept into v_dname,v_loc;       
        exit when c_dept%NOTFOUND;
        dbms_output.put_line('部门名称：'||v_dname||'   部门地址：'||v_loc);  
    end loop;    
    close c_dept;
  end;
end;

4、%ROWCOUNT

返回从游标结果集中提取到的实际行数

begin
  declare 
    v_dname dept.dname%type;
    v_loc dept.loc%type;
    cursor c_dept is select dname,loc from dept;
  begin
    if not c_dept%ISOPEN then
      open c_dept;
    end if;       
    loop
      fetch c_dept into v_dname,v_loc;       
        exit when c_dept%NOTFOUND;
        dbms_output.put_line('当前取得实际行数'||c_dept%ROWCOUNT||'   部门名称：'||v_dname||'   部门地址：'||v_loc);  
    end loop;    
    close c_dept;
  end;
end;

当前取得实际行数1   部门名称：ACCOUNTING   部门地址：Hello
当前取得实际行数2   部门名称：RESEARCH   部门地址：DALLAS
当前取得实际行数3   部门名称：SALES   部门地址：CHICAGO
当前取得实际行数4   部门名称：OPERATIONS   部门地址：BOSTON

5、用pl/sql记录变量接受游标数据

begin
  declare    
    cursor c_dept is select dname,loc from dept;
    v_record c_dept%rowtype;
  begin
    if not c_dept%ISOPEN then
      open c_dept;
    end if;       
    loop
      fetch c_dept into v_record;       
        exit when c_dept%NOTFOUND;
        dbms_output.put_line('当前取得实际行数'||c_dept%ROWCOUNT||'   部门名称：'||v_record.dname||'   部门地址：'||v_record.loc);  
    end loop;    
    close c_dept;
  end;
end;

%rowtype不仅可以基于表、视图定义记录变量，还可以基于游标进行定义，这样可以减少定义变量的个数

6、用pl/sql集合变量接受游标数据

begin
  declare    
    cursor c_dept is select dname,loc from dept;
    type tab_type is table of c_dept%rowtype index by binary_integer;
    dept_tab tab_type;
    i integer;
  begin
    if not c_dept%ISOPEN then
      open c_dept;
    end if;       
    loop
      i:=c_dept%rowcount+1;
      fetch c_dept into dept_tab(i);       
      exit when c_dept%NOTFOUND;        
      dbms_output.put_line('索引值'||i||'  当前取得实际行数'||c_dept%ROWCOUNT||'   部门名称：'||dept_tab(i).dname||'   部门地址：'||dept_tab(i).loc);  
    end loop;    
    close c_dept;
  end;
end;

7、游标for循环的使用

（1）定义游标，使用游标属性

begin
  declare   
    cursor c_dept is select dname,loc from dept;
  begin
    for item in c_dept loop
        dbms_output.put_line('当前取得实际行数'||c_dept%ROWCOUNT||'   部门名称：'||item.dname||'   部门地址：'||item.loc);     
    end loop;
  end;
end;

采用游标for循环，会自动打开关闭游标，循环完了，自动退出游标

（2）、不定义游标，采用子查询

begin 
  for item in (select dname,loc from dept) loop
      dbms_output.put_line('部门名称：'||item.dname||'   部门地址：'||item.loc);     
  end loop;  
end;

同样会自动打开、关闭游标、并且循环完了自动退出游标

8、参数游标

顾名思义，就是游标加上参数过滤数据，需要注意的地方就是定义参数的时候，只需指定数据类型，不用定义长度。

定义的参数需要用到后面的查询语句中，否则就没有意义了，当然不用也不会报错

多个参数当然也是支持的

begin
  declare   
    cursor c_dept(dno number) is select dname,loc from dept where deptno=dno;
  begin
    for item in c_dept(10) loop
        dbms_output.put_line('当前取得实际行数'||c_dept%ROWCOUNT||'   部门名称：'||item.dname||'   部门地址：'||item.loc);     
    end loop;
  end;
end;

9、更新、删除游标行

语法：cursor cursor_name is select_statement

for update [of 表名.列名] [nowait]

update tab_name set colname=value where current of cursor_name;

delete from tab_name where current of cursor_name;

for update：加行级共享锁，防止其他用户在该行上执行DML操作；

nowait：用于指定不等待锁；

当查询语句涉及到多张表的时候，需指定OF子句，如果不指定会在多张表上面同时加锁，指定了，只在指定的表上面加锁；

（1）、更新游标行

begin
  declare   
    cursor c_dept is select deptno,dname,loc from dept for update;
    v_deptno dept.deptno%type;
  begin
    for item in c_dept loop
      if item.deptno=10 then
        dbms_output.put_line('部门名称：'||item.dname||'   部门地址：'||item.loc);     
        update dept set loc='测试地址' where current of c_dept;
      end if;
    end loop;
  end;
end;

（2）、删除游标行

begin
  declare   
    cursor c_dept is select deptno,dname,loc from dept for update;
    v_deptno dept.deptno%type;
  begin
    for item in c_dept loop
      if item.deptno=1 then
        dbms_output.put_line('删除部门名称：'||item.dname||'   部门地址：'||item.loc);     
        delete from dept where current of c_dept;
      end if;
    end loop;
  end;
end;

（3）使用OF子句在特定表上面加行共享锁

begin
  declare   
    cursor c_emp is 
    select emp.empno,emp.ename from emp
    inner join dept on dept.deptno=emp.deptno 
    where dept.dname='ACCOUNTING' 
    for update OF emp.empno;
    v_deptno emp.empno%type;
  begin
    for item in c_emp loop
      if item.empno=7782 then
        dbms_output.put_line('更新人员编码：'||item.empno||'   人员名称：'||item.ename);     
        update emp set sal=1200 where current of c_emp;
      end if;
    end loop;
  end;
end;

10、游标变量

语法：type ref_c_name is ref cursor [return return_type];

cur_name ref_c_name;

open cur_name for select_statement;

fetch cur_name into var1,var2…………;

colse cur_name;

（1）、无返回类型的游标变量

begin
  declare   
    type ref_dept is ref cursor;
    c_dept ref_dept;
    v_dname dept.dname%type;
    v_loc dept.loc%type;
  begin
    open c_dept for select dname,loc from dept; 
    loop
      fetch c_dept into v_dname,v_loc;
      exit when c_dept%NOTFOUND;
       dbms_output.put_line('当前取得实际行数'||c_dept%ROWCOUNT||'   部门名称：'||v_dname||'   部门地址：'||v_loc);  
    end loop;
    close c_dept;
  end;
end;

（2）、有返回类型的游标变量

如果有返回类型，那么再打开游标时返回的结果数据集类型必须与指定的类型匹配

begin
  declare   
    type ref_dept is ref cursor return dept%rowtype;
    c_dept ref_dept;
    v_dept dept%rowtype;
  begin
    open c_dept for select * from dept; 
    loop
      fetch c_dept into v_dept;
      exit when c_dept%NOTFOUND;
       dbms_output.put_line('当前取得实际行数'||c_dept%ROWCOUNT||'   部门名称：'||v_dept.dname||'   部门地址：'||v_dept.loc);  
    end loop;
    close c_dept;
  end;
end;

ceclar123

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
oracle游标使用

在Oracle数据库中，游标分为显式游标与隐式游标，其中隐式游标又称为SQL游标，专门用于处理select into、update、insert、delete语句，而显式游标常用处理多行数据的select语句。---------------------------------------------------------------------------------------------
复制链接

扫一扫