postgresql 游标的作用

1、CURSOR综述

2、申明游标

declare

      CURSER 游标名 IS 查询语句

注：如果在查询语句中使用了pl/sql变量，变量声明必须放在游标的前面

DECLARE       v_major studengs.major%type;        c_student              SELECT irst_name,last_name              FROM  students              WHERE  major = v_major

PS：当打开游标的时候，就代表所定义的游标后面的查询语句真正的被执行，在打开游标的的时候变量一定要去初始化值，否则这个值就是一个空值，导致查询后面的where条件就失去了意义。最后把执行的查询结果返回到游标所定义的空间当中，并且在游标当中会提供一些操作方法来操作这些语句

注：如果游标对应的select语句使用了“FOR UPDATE”（行级排他锁）选项，则此游标锁定

说明：

 FETCH  FROM 

 fetch  游标名 

 fetch  游标名  INTO 

 游标使用后应该关闭【务必谨记】

fetch 关闭后的游标 是非法的

关闭一个关闭了的游标也是非法的

游标所对应的内存工作区变为无效，释放与游标相关的系统资源

declare        v_id  number(4);        CURSOR  my_cur  IS --声明游标                select  first_name,salary,start_date  from  s_emp  where  id = v_id;        v_fn  s_emp.first_name%type;     --定义三个变量用以接收游标里面查询出来的参数        v_sal  s_emp.salary%type;        v_sd  s_emp.start_date%type; begin      v_id := &id; --根据输入的id号码来查询出所对应的结果值      open  my_cur;    --打开游标      fetch  my_cur  into  v_fn,v_sal,v_sd;   --检索游标      --打印输出结果      dbms_output.put_line('id:' || v_id || '  name:' || v_fn || ' salary:' || v_sal || ' start_date:' || v_sd );      close my_cur;       --关闭游标（必须的） end;

6、游标的属性

 %FOUND 如果fetch到数据。则返回true，否则返回false

 %NOT FOUND  如果fetch不到数据，则返回true，否则返回false

 %ISOPEN  如果当前游标已打开，则返回true

 %ROWCOUNT 返回当前游标的指针位移量，当然，游标一定要处于打开状态

7、游标的fetch循环

LOOP        游标名  INTO ……  --提取数据       EXIT  游标名%NOTFOUND；--退出条件 END  --结束循环 close 游标名 --关闭游标

while 游标名%FOUND  LOOP       fetch  游标名  END  close 游标名 –关闭游标 --注意：在使用while的时候，在while之前一定要先fetch一下先，否则进不了循环体，因为开始是空的

FOR  var  IN  cursor  LOOP …… END LOOP 注：在for循环中，会自动打开，自动fetch，和自动关闭游标，所以，使用for循环是最为简便的方法

8、一个游标的具体实例：

     

方法一：分别定义变量和游标

declare        v_fName varchar2(128);        --定义三个变量来存放数据        v_title s_emp.title%type;        v_startDate s_emp.start_date%type;        CURSOR  my_cur  IS        --声明一个游标        select  first_name|| ' '||last_name,title,start_date  from  s_emp  where  salary<1500; begin      open  my_cur;      loop        fetch  my_cur  into  v_fName,v_title,v_startDate;        dbms_output.put_line(v_fName|| ', ' || v_title ||', ' || v_startDate );  --输出结果        exit when my_cur%notfound;  --跳出循环的条件      end loop;      close my_cur;       --关闭 end;

方法二：改写方法一

下面是这个例子的第二中写法：不需要很麻烦的定义三个变量来存放数据，可以直接定义一个游标型的变量来存放游标里面存放的所有的数据

declare        --v_fName varchar2(128);        --v_title s_emp.title%type;        --v_startDate s_emp.start_date%type;       CURSOR  my_cur  IS        select  first_name|| ' '||last_name n,title,start_date sd  from  s_emp  where               --为了方便起见，我们给上面查询到的字段起了一个别名        emp  my_cur%rowtype;--直接定义一个游标类型一样的变量，用来存放所有的数据 begin      open  my_cur;      loop        fetch  my_cur  into  emp;--直接把数据给扔到上面定义的变量里面        dbms_output.put_line(emp.n|| ', ' || emp.title ||', ' || emp.sd );        exit when my_cur%notfound;      end loop;      close my_cur; end;

方法三：直接使用for循环

其实上面括号里面的红色部分就是一个游标，由此可以看出，当使用for循环的时候，是最简便的方法

在for循环中，变量因子无需声明，游标也无需显式的打开和关闭，并且也不需要显式的去fetch数据。

当然红色的部分也可以在DECLARE里面事先定义好

9、for循环检索游标的特性

10、带参数的游标

DECLARE       cursor c_student(p_major  students.major%type) IS       select * from student where najor = p_major; BEGIN       OPEN  c_student(101); --传入参数

注意：

 定义参数化游标时，只能指定参数的类型，而不能指定参数的长度、精度、刻度，比如VARCHAR2（20），只能是VARCHAR2，后面的不能再有括号里面的数据

11、利用游标更新或删除数据

cursor 游标名 IS

 select语句 

【OF 列名】【NOWAIT】

更新或是修改的语法为

     

where current of 游标名

注意：

            

12、案例

修改员工的工资，如果员工的部门号为10，则工资提高100；如果部门号为20，则工资提高150；如果部门号为30，则工资提高200；否则工资提高250.

declare        cursor c_emp is  select  *  from  emp  for update;        v_increment  number; begin      for  v_emp  in  c_emp  loop           case  v_emp.empno             when  10  then  v_increment := 100;             when  20  then  v_increment := 150;             when  30  then  v_increment := 200;             else  v_increment := 250;           end  case;           update  emp  set  sal=sal + v_increment  where  current  of  c_emp;      end loop;      commit;              --注意要提交事务 end;

13、隐式游标

修改员工号为1000的员工的工资，将他的工资增加100.如果该员工不存在，则向emp表中插入一个员工号为1000，工资为1600的员工

方法一：

begin     update emp set sal=sal+100  where  empno=1000;     if  sql%NOTFOUND  then        INSERT  INTO  emp(empno,sal) values(1000,1600);     end if;     commit; end;

            

begin     update emp set sal=sal+100  where  empno=1000;     if  sql%rowcount = 0  then        INSERT  INTO  emp(empno,sal) values(1000,1600);     end if;     commit; end;

14、游标变量

游标变量是一个指向多行查询结果集的指针，不与特定的查询绑定，因此具有非常大的灵活性，可以在打开游标变量时定义查询，可以返回不同结构的结果集

 REF 

 IS  REF  CURSOR  RETURN  type

type 可以是任务合法的Oracle数据类型

 IS  REF 

002、一个强类型的例子

declare           strong_cursor is  ref  cursor  return  s_emp%rowtype;--这是一个强类型          my_c1  strong_cursor;          emp  s_emp%rowtype;  --由于这是一个强类型，所以只能是跟游标类型相对应的变量          begin               open  my_c1 for  select * from  s_emp dept_id=41;               dbms_output.put_line('----------输出41部门的员工----------------');               loop                   fetch  my_c1  into  emp;                   exit  when  my_c1%notfound;                   dbms_output.put_line(emp.id||''||emp.salary);               end  loop;               close  my_c1;                             open  my_c1 for  select * from  s_emp where  salary>1000;               dbms_output.put_line('----------输出工资大于1000的员工 -------------');               loop                   fetch  my_c1 into  emp;                   exit   my_c1%notfound;                   dbms_output.put_line(emp.id||''||emp.salary);              end  loop;

由上面的例子可以看出，使用游标变量使得查询等变的很随意

003、一个若类型的例子

004、sys_refcursor类型

就像下面一个例子

declare      type id_list is  table  of  integer INDEX BY binary_integer;      type  name_list  is  table  of  varchar2(200) INDEX BY binary_integer;      ids  id_list;      names  name_list;      my_cur  sys_refcursor; begin      open my_cur  for  select  id, first_name from  s_emp;      fetch  my_cur  bulk  collect  INTO ids, names;      close my_cur;      for i in  ids.first..ids.last  loop          dbms_output.put_line('ID = '||ids(i)||'NAME = '||names(i));      end  loop; end;