pl/sql programming 15 数据提取

数据提取 -- 游标

游标只是一个指向某个结果集的指针.

声明游标: cursor employee_cur IS select * from employees;

打开游标: open employee_cur;

提取数据: fetch employee_cur into employee_rec;  -- employee_rec 是employees%rowtype类型

关闭游标: close employee_cur;

也可以多表 join 作为结果集.

declare

  cursor joke_feedback_cur

is

  select j.name, r.laugh_volume, c.name

    from joke J, response R, comedian C

   where j.joke_id = r.joke.id

      and r.joker_id = c.joker_id;

begin

  ...

end;

不管是哪一种游标类型, pl/sql 执行一个 sql 语句使用的都是相同的操作方式. 有时, plsql 会替我们执行这些步骤

执行步骤

游标的属性

-- 隐式游标举例 --
Function book_title (isbn_in IN books.isbn%TYPE)
return books.title%type
IS
    return_value    book.title%type;
BEGIN
    SELECT     title      -- 隐式游标
    into    return_value
    from    books
   where    isbn = isbn_in;
  
    return    return_value;
   
    exception    -- 隐式游标的异常处理
        when no_date_found
        then
            return null;
        when too_many_rows  -- 隐式游标异常
        then
            errpkg.record_and_stop ('Data integrity error for:' || isbn_in);
            raise;       
END;

隐式游标有可能自动抛出 2 种异常

• 查询没有找到能够匹配我们条件的任何一行. 这种情况下, 数据库会抛出异常 NO_DATA_FOUND
• select 语句返回的结果已经超过了一行. 这种情况下, 数据库会抛出 TOO_MANY_ROWS 异常.

隐式游标属性

-- chap15_01.sql
create or replace procedure remove_from_circulation( isbn_in in books.isbn%type)
is
begin
    delete from book where isbn = isbn_in;
end;
/
show errors;

-- chap15_02.sql
create or replace procedure show_boo_count
is
    l_count    integer;
begin
    select count(*)
      into l_count
      from books;

      remove_from_circulation('0-000-0000-0');
      dbms_output.put_line(sql%rowcount);
end;

上例中, 无论book表中有多少条记录, 我从输出窗口看到的总是”0”, 这是因为我在SELECT INTO 语句之后又调用了 remove_from_circulation, 因此 SQL%ROWCOUNT 反映的是这个愚蠢的, 不可能有结果的 delete 语句的属性, 而不是查询语句的结果.

要想确保检查的确实是正确的SQL语句的属性值, 我们应该在该SQL语句执行之后立即把属性值保存到局部变量中, 否则SQL%ROWCOUNT等属性值, 永远保存的是最后执行的隐性游标的属性.

-- chap15_03.sql 正确方法
create or replace procedure show_boo_count
is
    l_count    integer;
    l_numfound pls_integer;
begin
    select count(*)
      into l_count
      from books;
      l_num_found := SQL%ROWCOUNT;  -- 将游标属性保存在局部变量
      remove_from_circulation('0-000-0000-0');
      dbms_output.put_line(sql%rowcount);
end;

-- 显示游标举例 --
Function jealousy_level (
    NAME_IN    IN    friends.NAME%TYPE)    RETURN    NUMBER
AS
    cursor jealousy_cur
    IS
        select location from friends
        where name = UPPER(name_in);
       
    jealousy_rec    jealousy_cur%ROWTYPE;    
    retval    NUMBER;
BEGIN
    OPEN jealousy_cur;
   
    fetch jealousy_cur into    jealousy_rec;
   
    IF jealousy_cur%found then
        if jealousy_rec.location = 'PUERTO RICO' THEN
            retval := 10;
        elsif jealousy_rec.location = 'CHICAGO' THEN
            retval := 1;
        end if;
    end if;
   
    close jealousy_cur;
   
    return retval;
EXCEPTION
    when others then
        if jealousy_cur%isopen then
            close jealousy_cur;
        end if;           
END;

带参数化的游标, 游标的参数只能是 IN 类型的.

使用游标变量的好处是它提供了一种可以在不同的PL/SQL程序间传递查询结构集(从游标获取记录行)的机制.

声明显示游标

1. 不带参数的游标

  CURSOR company_cur IS

     SELECT company_id FROM company;

2. 带参数的游标

  CURSOR name_cur ( company_id_in IN number)

  IS

    SELECT  name FROM company

     WHERE company_id = company_id_in;

3. 带 return 语句的游标

  CURSOR emp_cur RETURN employee%ROWTYPE

  IS

    SELECT * FROM employees

     WHERE department_id = 10;

在包中声明游标

PACKAGE book_info
IS
  CURSOR titles_cur
  IS
    select title
      from books;

  cursor books_cur(title_filter_in IN books.title%type)
    return books%rowtype
  IS
    select *
      from books
     where title like title_filter_in;
end;

为什么要把游标定义在包中呢?

答案很简单, 通过在包中定义游标, 我们重用这些查询就更加容易, 也避免了在应用程序中一遍遍的编写相同的数据提取语句.

为什么要使用 return 语句 ?

这个包有点类似 object-c, 实际内容在包体中, 包头只是一个声明, 我们应该尽可能的隐藏包体中的内容, 只给用户看到包头的内容, 那么 return 语句的用处是.

1. 隐藏信息, 隐藏包体信息, 别人只要读包头, 就知道游标的一切, 包括这个游标返回的结果集的类型.

2. 最小化编译, 我们可以随意的修改包体中的 select 语句而不会影响到包头的游标, 这样就可以改进, 重编译代码, 而不用重新编译包头部分, 这也就意味着所有依赖于这个包的程序都不会被置成无效状态, 自然也就不必重新编译.

打开显示游标

OPEN cursor_name[argument, argument …];

也就是说, 当你游标打开时, 如果没有指定 for update 语句, 那么不会为游标的记录集加锁, 这时候, 会保存打开游标时刻的SCN, 如果游标操作过程中, 修改了记录集(游标虚拟表中的记录), 当提交时, 如果SCN 比实际数据块的SCN小, 那么这个游标的修改操作将是不成功的.

从显示游标中提取数据

select 语句构建了一个虚拟数据表, 它的返回集是由 where 语句(或者没有)确定的一系列行, 因此 plsql程序中, 游标就代表着这个虚拟的数据表.

FETCH cursor_name INTO record_or variable_list;

显示游标中的列别名

别名的作用只是为了在结果集中使用方便, 比如你有个计算函数 sum(a, b), 如果不起别名的话, 这列很难引用

declare
    cursor comp_cur is
        select c.name, sum(inv_amt) total_sales  -- 列别名
          from company C, invoice I
         where C.company_id = I.company_id
           and I.invoice_date BETWEEN '01-jan-2001' AND '31-dec-2001';
    comp_rec comp_cur%rowtype;  -- 这里定义的是结果集的rowtype类型
begin
    open comp_cur;
    fetch comp_cur into comp_rec;
    ...
end;
-- 然后可以在结果集中使用如下:
IF comp_rec.total_sales > 5000 THEN
    dbms_output.put_line('You have exceeded your credit limit of $5000 by ' ||
                        to_char(comp_rec.total_sales - 5000, '$9999'));
END IF;

 

关闭显示游标

CLOSE cursor_name;

如果我们在程序中声明并打开了一个游标, 就一定要确保在程序结束时关闭这个游标.

这里要特别注意在包中的游标, 包中的游标, 在异常处理单元都要有关闭游标的确认.

begin
    open my_package.my_cursor;
    ...
    close my_package.my_cursor;
exception
    when others then
        IF mypackage.my_cursor%ISOPEN THEN
            CLOSE my_package.my_cursor;
        END IF;
END;

SELECT … FOR UPDATE

当使用 select 语句从数据库查询记录时, 数据库不会被选择的行添加任何锁. 通常来说, 这是一个非常不错的特性, 因为任何时候被锁定的记录数量都要尽可能的最小, 只有那些已经被修改了但是还没有提交的记录才需要被锁定. 即使这样, 其他人还是可以看到这些记录被修改之前的状态.

不过, 有时候我们希望在程序修改之前就锁住它们. 这时就可以使用 SELECT … FOR UPDATE

CURSOR toys_cur IS
    SELECT name, manufacturer, preference_level
      from my_sons_collection
     where hours_used = 0
       for update;  -- 没有任何限制

CURSOR fall_jobs_cur IS
    SELECT task, expected_hours, tools_required
      from winterize
     where year_of_task = to_char(sysdate, 'yyyy')
       for update of task;  -- 只针对 task 这列限制

我们也可以夺标SELECT中使用 for update, 这种情况, 只有自己列出的在 for update of 子句中的记录才会被锁定, 换句话说, 必须要有限制.

WHERE CURRENT OF 语句

plsql 为游标的 update 和 delete 提供了 where current of 语句. 这个语句可以让我们很容易的修改最后取出来的数据行.

update table_name
   set set_clause
 where current of cursor_name;

 delete
   from table_name
  where current of cursor_name;

例如: 删除一个我刚刚取出的数据, 更新一个我刚刚取出的数据.

declare
    cursor fall_jobs_cur IS select ... same as before ...;
    job_rec fall_jobs_cur%rowtype;
begin
    open fall_jobs_cur;
    loop
        fetch fall_jobs_cur into job_rec;

        exit when fall_jobs_cur%notfound;

        if job_rec.do_it_yourself_flag = 'youcandoit' then
            update winterize
               set responsible = 'steven'
             where current of fall_jobs_cur;
            commit;
            exit;
        end if;
    end loop;
    close fall_jobs_cur;
end;

声明 REF CURSOR 类型 游标变量

游标变量是一个指向或者引用底层游标的变量, 是一个指针, 和显示游标不一样, 显示游标已经为结果集的 pl/sql 工作区指定了名字, 而游标变量只是指向这个工作区的引用. 显示游标和隐式游标都绑定到一个专门的查询语句, 从这一点来说, 这两种游标都是静态的, 而游标变量可以用于打开任何一个查询, 甚至在一个程序中执行多个不同的查询.

使用游标变量一个最重要的好处就在于它提供了一种可以在不同的plsql 程序间传递查询的结果集

比如一个session 打开了一个游标, 可以使用游标变量将这个传递给另一个session, 而另一个session 可以关闭这个游标.

创建一个游标变量

TYPE cursor_type_name IS REF CURSOR [RETURN return type];  -- 创建引用游标类型

TYPE company_curtype IS REF CURSOR RETURN company%ROWTYPE;   -- 强类型

TYPE generic_curtype IS REF CURSOR;                          -- 弱类型

从 oracle 9i 开始, 数据库已经替我们定义好了一个弱类型, 直接用就可以了

declare my_cursor SYS_RECURSOR;  -- 定义了真正的游标变量

声明游标变量的方法
cursor_name cursor_type_name;

声明一个游标变量 和 创建一个真正的游标对象, 二者之前的区别很重要, 后者通过游标的sql语句查询出的结果集, 而游标变量指向游标对象. 如图:

打开游标变量

open cursor_name FOR select_statement;

如果是强类型的, 那么类型一定要匹配

从游标变量获取数据, 同静态游标一样
FETCH cursor_variable_name INTO record_name;
FETCH cursor_variable_name INTO varibale_name, variable_name...;

如果是强类型转换, 有可能类型不匹配, 这时候会抛出 rowtype_mistach 异常, 要捕获这个异常

EXCEPTION

  WHEN ROWTYPE_MISMATCH THEN

     --do something

游标变量的使用规则

记住 游标变量时对一个游标对象或者一个数据库中查询的引用, 而不是游标对象本身.

游标对象的作用范围类似 java 中对象的概念, 堆中的对象, 只要有一个引用(指针) 还在引用这个对象, 这个对象就要保持可以被访问状态.

游标变量不能在包中声明, 因为它没有持久状态.

我们不能通过远程过程调用(RPC) 把游标变量从一个服务器传递给另一个服务器.( 游标变量保存的只是引用)

游标变量作为参数传递

declare
    type curvar_type is ref cursor return company%rowtype;
    procedure open_query(curvar_out OUT curvar_type)
    is
        local_cur curvar_type;
    begin
        open local_cur for select * from company;
        curvar_out := local_cur;
    end;

游标表达式(用于嵌套游标)