Oracle游标

一、静态游标

 (一)、隐式游标
  -- 在PL/SQL中使用DML语句时自动创建隐式游标
  ---隐式游标自动声明、打开和关闭，其名为 SQL
  
  --单行数据
  select job,sal from emp where ename='SCOTT';
  
  declare
      v_ename emp.ename%type:=&name;
      v_job emp.job%type;
      v_sal emp.sal%type;
  begin
     select job,sal into v_job,v_sal from emp where ename=v_ename;  --隐式游标
     dbms_output.put_line(v_job||'-->'||v_sal);
     --处理异常
      exception
        when no_data_found then
           dbms_output.put_line('没有找到数据！');
        when others then
           dbms_output.put_line('error:'||sqlcode||'-->'||sqlerrm);
  end;

（二）、显示游标

/*说明光标语法：
CURSOR 光标名 [ (参数名 数据类型[,参数名 数据类型]...)] IS SELECT 语句;
 用于存储一个查询返回的多行数据
例如： cursor c1 is select ename from emp;
 打开光标： open c1; (打开光标执行查询)
 取一行光标的值：fetch c1 into pjob; (取一行到变量中)
 关闭光标： close c1;(关闭游标释放资源)*/


/*%FOUND – SQL 语句影响了一行或多行时为 TRUE
%NOTFOUND – SQL 语句没有影响任何行时为TRUE
%ROWCOUNT – SQL 语句影响的行数
%ISOPEN  - 游标是否打开，始终为FALSE*/


--(1)简单显式游标
select * from emp;


declare
      v_emp emp%rowtype;  --一行记录类型变量
      
      --1)声明游标
      cursor c1 is select *  from emp;
  begin
      --2)打开游标
       open c1;
       
       --3)提取每一行的数据 (循环)
       loop
          fetch c1 into v_emp;   --select * into v_emp from emp;
          exit when  c1%notfound;  --退出循环
          dbms_output.put_line(c1%rowcount||': '||v_emp.empno||'-->'||v_emp.ename||'-->'||v_emp.job||'-->'||v_emp.sal);  ---输出结果         
       end loop;
           
      --4)关闭游标
      close c1;
  end;


select ename,sal from emp where deptno=10;



declare
      v_ename emp.ename%type;  --一行记录类型变量
      v_sal  emp.sal%type; 
      --1)声明游标
      cursor c1 is select ename,sal from emp where deptno=10;
  begin
      --2)打开游标
       open c1;
       
       --3)提取每一行的数据 (循环)
       loop
          fetch c1 into v_ename,v_sal;   --select * into v_emp from emp;
          exit when  c1%notfound;  --退出循环
          dbms_output.put_line(c1%rowcount||': '||v_ename||'-->'||v_sal);  ---输出结果         
       end loop;
           
      --4)关闭游标
      close c1;
  end;
  
  
  
--(2)基于游标定义记录变量
select *  from emp;


declare
     -- v_emp emp%rowtype;  --一行记录类型变量
      
      --1)声明游标
      cursor c1 is select *  from emp;
      
      --游标定义记录变量
       v_emp c1%rowtype; 
  begin
      --2)打开游标
       open c1;
       
       --3)提取每一行的数据 (循环)
       loop
          fetch c1 into v_emp;   --select * into v_emp from emp;
          exit when  c1%notfound;  --退出循环
          dbms_output.put_line(c1%rowcount||': '||v_emp.empno||'-->'||v_emp.ename||'-->'||v_emp.job||'-->'||v_emp.sal||'-->'||v_emp.deptno);  ---输出结果         
       end loop;
           
      --4)关闭游标
      close c1;
  end;
  
  
  --(3)带参数显式游标
  -- CURSOR 光标名 [ (参数名 数据类型[,参数名 数据类型]...)] IS SELECT 语句;
  select ename,sal from emp where deptno=20 and sal>2000


declare
      v_ename emp.ename%type;  --一行记录类型变量
      v_sal  emp.sal%type; 
      --1)声明游标 有参数时数据类型不能写大小number(5)
      cursor c1(v_deptno number,v_sal number) is select ename,sal from emp where deptno=v_deptno and sal>v_sal;
  begin
      --2)打开游标(传参数值)
       open c1(20,2000);
       
       --3)提取每一行的数据 (循环)
       loop
          fetch c1 into v_ename,v_sal;   --select * into v_emp from emp;
          exit when  c1%notfound;  --退出循环
          dbms_output.put_line(c1%rowcount||': '||v_ename||'-->'||v_sal);  ---输出结果         
       end loop;
           
      --4)关闭游标
      close c1;
  end;
  
  --(4)使用显式游标更新行
 -- 给所有的部门销售员加薪500
 select *from emp where job='SALESMAN'and sal<1800
 select * from emp2;
 --复制数据
 insert into emp2  select * from emp where job='SALESMAN'and sal<1800
 
 declare
   --1）声明游标
     cursor c1 is  select *from emp2 where job='SALESMAN' for update;   --for udpate
     
    --游标记录类型变量
    v_emp c1%rowtype; 
 begin
    --2)打开游标
    open c1;
    
     --3)更新数据
      loop
          fetch c1 into v_emp;
          exit when c1%notfound;
          --每一行
          update emp2 set sal = sal+500 where current of c1;  --where current of c1  在当前游标中
      end loop;
    
    --4）关闭游标
    close c1;
 end;
 
 select * from emp2;
 
 --将所有的部门销售员的工资小宇2200的全部删除?
 
 --(5)循环游标
 --PL/SQL语言提供了游标FOR循环语句，自动执行游标的OPEN、FETCH、CLOSE语句和循环语句的功能；

当进入循环时，游标FOR循环语句自动打开游标，并提取第一行游标数据；
当程序处理完当前所提取的数据而进入下一次循环时，游标FOR循环语句自动提取下一行数据供程序处理；
当提取完结果集合中的所有数据行后结束循环，并自动关闭游标。
格式： 


 /* FOR index_variable IN cursor_name[(value[, value]…)] LOOP
    -- 游标数据处理代码
  END LOOP;*/


 -- 给所有的部门销售员-500
 declare
   --1）声明游标
     cursor c1 is  select *from emp2 where job='SALESMAN' for update;   --for udpate 
 begin 
     --3)更新数据
      for v_emp in c1 loop
          --每一行
          update emp2 set sal = sal-500 where current of c1;  --where current of c1  在当前游标中
      end loop; 
 end;
 
 select * from emp2;
 --查询所有的数据
 declare
   --1）声明游标
     cursor c1 is  select *from emp2;
 begin 
      for v_emp in c1 loop
         dbms_output.put_line(c1%rowcount||v_emp.ename||'-->'||v_emp.job);
      end loop; 
 end;

 

二、动态游标

/* 
 什么是动态游标？
动态游标也称REF游标
经常用于处理运行时动态执行的 SQL 查询*/

/*声明 REF 游标类型   
          例：TYPE REF_CURSOR_TYPE IS REF CURSOR;
声明 REF 游标类型的变量   
          例：cv_ref REF_CURSOR_TYPE;*/
          
/* 打开游标变量时使用的是OPEN…FOR 语句
 例：OPEN cursor_name FOR select_statement;*/
 
 declare
    --1)定义一个游标数据类型 
    type emp_cursor_type is ref cursor;
     --游标变量
    c1 emp_cursor_type;
 
    v_emp emp%rowtype; --记录类型变量
    v_dept dept%rowtype; --记录类型变量
 begin
    --2)打开游标
    open c1 for select * from emp where deptno=20;
    
    --3)提取数据
    loop 
       fetch c1 into v_emp;
       exit when c1%notfound;
         dbms_output.put_line(c1%rowcount||': '||v_emp.empno||'-->'||v_emp.ename||'-->'||v_emp.job||'-->'||v_emp.sal||'-->'||v_emp.deptno);  ---输出结果         
    end loop;
    
    --用同一个游标指向另一个查询语句
     open c1 for select * from dept where deptno in (10,20);
    --3)提取数据
    loop 
       fetch c1 into v_dept;
       exit when c1%notfound;
         dbms_output.put_line(c1%rowcount||': '||v_dept.dname||'-->'||v_dept.loc);  ---输出结果         
    end loop;
    
    --4）关闭游标
    close c1;
 end;

三、静态SQL与动态SQL

什么是静态SQL？

需要在编写PL/SQL程序时就确定的SQL语句

什么是动态SQL？

动态 SQL 是指在PL/SQL程序执行时生成的 SQL 语句

DDL 语句命令和会话控制语句不能在 PL/SQL 中直接使用，但是可以通过动态 SQL 来执行

编译程序对动态 SQL 不做处理，而是在程序运行时动态构造语句、对语句进行语法分析并执行

使用EXECUTE IMMEDIATE 语句执行：

DDL 语句、DCL 语句、非查询的DML 语句、单行查询的SELECT 语句

　--(1)、EXECUTE IMMEDIATE 语句执行。

 本地动态SQL执行DML语句

select * from dept;

--添加部门信息
insert into dept values(?,?,?)

declare
    v_deptno dept.deptno%type:=&no;
    v_dname dept.dname%type:=&name;
    v_loc dept.loc%type:=&loc;
    
    --save sql
    v_sql varchar2(500);
begin
    v_sql:='insert into dept values(:no,:name,:loc)';
    --execute sql
     execute immediate v_sql using v_deptno,v_dname,v_loc;
     --exception
      exception
         when others then
           null;
end;


--查询部门的个数有具体返回的值
select count(1) from dept;

declare
    v_count number;
    --save sql
    v_sql varchar2(500);
begin
    v_sql:='select count(1) from dept';
    --execute sql
     execute immediate v_sql into v_count;
     dbms_output.put_line(v_count);
     --exception
      exception
         when others then
           null;
end;

--(2)通过游标实现

通过游标实现：

多行查询的SELECT语句

select * from emp where deptno=20;


declare
    type emp_cursor_type is ref cursor;
    c1 emp_cursor_type;
    v_emp emp%rowtype;
begin
    --OPEN cursor_name FOR dynamic_sql_string [USING bind_argument_list];
    --打开
    open c1 for 'select * from emp where deptno=:x'using 10;
    
    --提取数据
    loop
       fetch c1 into v_emp;
       exit when c1%notfound;
       dbms_output.put_line(c1%rowcount||': '||v_emp.empno||'-->'||v_emp.ename||'-->'||v_emp.sal||'-->'||v_emp.deptno);  ---输出结果         
    end loop;
    
    --关闭
    close c1;


end;