游标和动态SQL - 数据库编程

游标类别：静态游标（指在编译的时候，游标就与一个select语句进行了静态绑定的游标，这种游标只能作用于一个查询语句）和动态游标（就是希望我们的查询语句在运行的时候才跟游标绑定，为了使用动态游标，必须声明游标变量）。

动态游标分两种，分别是强类型和弱类型。强类型的动态游标只能支持查询结果与他类型匹配的这种查询语句，弱类型的动态游标可以支持任何的查询语句。

静态游标分为两种，隐式游标和显示游标。显示游标是有用户声明和操作的一种游标。隐式游标是Oracle为所有的数据操作语句自动声明的一种游标。

在每个用户的会话中，我们可以同时打开多个游标，这个数量有数据库初始化参数文件中的OPEN CURSORS这个参数来定义。

显示游标的用法步骤：

1、声明显式游标，语法：CURSOR<游标名>ISSELECT<语句>;

在声明游标的时候通常还要声明一些变量用来存放查询语句产生的查询结果。声明游标和变量都在declare中的。通常先声明变量，在声明游标。

2、打开游标，从打开游标开始，后面的步骤都是在begin和end中执行的。语法：open<游标名>;当打开游标后查询语句就开始执行了，查询结果放到Oracle的缓冲区中，然后游标指向了这个缓冲区中查询结果的第一行记录之前。

3、提取游标，通过提取游标，游标依次指向查询结果的每一行。语法：FETCH<游标名>INTO<变量列表>;

4、关闭游标，语法：CLOSE<游标名>;

示例：

declare
 name varchar2(50);                               --定义变量存储employees表中的内容。
 department_name varchar2(20);               --定义变量存储departments表中的内容；
 cursor emp_cur IS                               --定义游标emp_cur
select name,department_name               --选出所有员工的姓名和所做部门。
from employees e,departments d
  where e.department_id=d.department_id;
begin
 open emp_cur;                                      --打开游标
 LOOP
 FETCH emp_cur INTO name,depart_name;      --将第一行数据放入变量中，游标后移。
      EXIT WHEN emp_cur%NOTFOUND;
      dbms_output.put_line(name||’在’||department_name);
 END LOOP;
 CLOSE emp_cur;
END;

游标的属性：%ISOPEN，游标是否打开；%FOUND,游标是否指向有效行；%NOTFOUND，游标是否没有指向有效行；%ROWCOUNT，游标抽取过的行数。

语法：游标名%属性名。

例如：公司上市，决定给员工提高薪资，入职时间超过1年涨100，1000元封顶。

declare
 hire_date date;              --存放员工入职日期
 e_id number;                   --存放员工id
 cursor emp_cur is            --定义游标
      select id,hire_date from employees;
begin
 open emp_cur;                 --打开游标
 loop
      fetch emp_cur into e_id,hire_date       --将数据逐条存入变量
      exit when emp_cur%NOTFOUND;
      if 100*(2014-to_char(hire_date,’yyyy’))<1000 then
             update salary setsalaryvalue=salaryvalue+100*(2010-to_char(hire_date,’yyyy’)) where employee.id=e_id;
      else
             update salary setsalaryvalue=salaryvalue+1000 where employee.id=e_id;
      end if;
 end loop;
      close emp_cur;
end

使用循环游标游标的读取，语法：FOR <类型> IN <游标名>LOOP –操作各行数据 END LOOP;

DECLARE
      CURSOR emp_cur IS
      SELECT name,department_name
      FROM employees e,departments d;
      WHERE e.department_id=d.department_id;
BEGIN
      FOR employ_record IN emp_cur LOOP
             dnms_output.put_line(employ_record.name||’在’||employee_record.department_name);
      END LOOP;
END;

隐式游标

隐式游标与显示游标的区别：1、不用声明游标。2、不用打开和关闭游标。3、必须使用INTO子句，结果只能是一条。

隐式游标与显示游标的相同的：有相同的属性，隐式游标使用属性的方法是在属性名前面加上SQL%，即SQL%FOUND，SQL%ISOPEN等。

DECLARE
      name VARCHAR2(50);
      department_name varchar(20);
BEGIN
      SELECT name,department_name
      INTO name,deprtment_name
      FROM employees e,departments d;
      WHERE e.department_id=d.department_id and e.id=1;
      dbms_output.put_line(name||’在’||department_name);
END;

因为隐式游标查询结果只有一行，所以如果用来计数没有多大的意义，所以%ROECOUNT这个属性经常用来判断插入、删除、更新是否成功，但是要在COMMIT语句之前。如果在COMMIT之后，%ROECOUNT只能是0；

begin
      update employees set name=name||’A’
      where id=7;
      if sql%rowcount=1 then
             dbms_output.put_line(‘表已更新！’);
      else
             dbms_output.put_line(‘编号未找到’);
      end if;
end;

REF动态游标

ref动态游标可以在运行的时候与不同的语句进行关联，他是动态的。ref动态游标被用于处理多行的查询结果集，ref动态游标是ref类型的变量，类似于指针。

定义ref动态游标类型：type<类型名> is ref cursor return <返回类型>;

声明ref动态游标：<游标名> <类型名>;

打开ref动态游标：OPEN<游标名> FOR <查询语句>;

示例：

DECLARE
      TYPE refcur_t IS REF CURSOR
      RETURN employess%ROWTYPE;
      refcur refcur_t;
      v_emp employees%ROWTYPE;
BEGIN
      OPEN refcur FOR
      SELECT * FROM employees;
      LOOP
             FETCH refcur INTO v_emp;
             EXIT WHEN refcur%NOTFOUND;
             dbms_output.put_line(refcur%ROWCOUNT||’‘||v_emp.name);
      END LOOP;
      CLOSE refcur;
END;

强类型ref动态游标：带有RETURN语句的REF动态游标。

弱类型ref动态游标：不带有RETURN语句的REF动态游标。

例如：

DECLARE
      TYPE refcur_t IS REF CURSOR 
       refcur refcur_t;
      e_id number;
      e_name varchar2(50);
BEGIN
      OPEN refcur FOR
      S

游标和动态SQL(一)