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 异常.
隐式游标属性
1: -- chap15_01.sql
2: create or replace procedure remove_from_circulation( isbn_in in books.isbn%type)
3: is
4: begin
5: delete from book where isbn = isbn_in;
6: end;
7: /
8: show errors;
9:
10: -- chap15_02.sql
11: create or replace procedure show_boo_count
12: is
13: l_count integer;
14: begin
15: select count(*)
16: into l_count
17: from books;
18:
19: remove_from_circulation('0-000-0000-0');
20: dbms_output.put_line(sql%rowcount);
21: end;
上例中, 无论book表中有多少条记录, 我从输出窗口看到的总是”0”, 这是因为我在SELECT INTO 语句之后又调用了 remove_from_circulation, 因此 SQL%ROWCOUNT 反映的是这个愚蠢的, 不可能有结果的 delete 语句的属性, 而不是查询语句的结果.
要想确保检查的确实是正确的SQL语句的属性值, 我们应该在该SQL语句执行之后立即把属性值保存到局部变量中, 否则SQL%ROWCOUNT等属性值, 永远保存的是最后执行的隐性游标的属性.
1: -- chap15_03.sql 正确方法
2: create or replace procedure show_boo_count
3: is
4: l_count integer;
5: l_numfound pls_integer;
6: begin
7: select count(*)
8: into l_count
9: from books;
10: l_num_found := SQL%ROWCOUNT; -- 将游标属性保存在局部变量
11: remove_from_circulation('0-000-0000-0');
12: dbms_output.put_line(sql%rowcount);
13: 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;
在包中声明游标
1: PACKAGE book_info
2: IS
3: CURSOR titles_cur
4: IS
5: select title
6: from books;
7:
8: cursor books_cur(title_filter_in IN books.title%type)
9: return books%rowtype
10: IS
11: select *
12: from books
13: where title like title_filter_in;
14: 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), 如果不起别名的话, 这列很难引用
1: declare
2: cursor comp_cur is
3: select c.name, sum(inv_amt) total_sales -- 列别名
4: from company C, invoice I
5: where C.company_id = I.company_id
6: and I.invoice_date BETWEEN '01-jan-2001' AND '31-dec-2001';
7: comp_rec comp_cur%rowtype; -- 这里定义的是结果集的rowtype类型
8: begin
9: open comp_cur;
10: fetch comp_cur into comp_rec;
11: ...
12: end;
13: -- 然后可以在结果集中使用如下:
14: IF comp_rec.total_sales > 5000 THEN
15: dbms_output.put_line('You have exceeded your credit limit of $5000 by ' ||
16: to_char(comp_rec.total_sales - 5000, '$9999'));
17: END IF;
关闭显示游标
CLOSE cursor_name;
如果我们在程序中声明并打开了一个游标, 就一定要确保在程序结束时关闭这个游标.
这里要特别注意在包中的游标, 包中的游标, 在异常处理单元都要有关闭游标的确认.
1: begin
2: open my_package.my_cursor;
3: ...
4: close my_package.my_cursor;
5: exception
6: when others then
7: IF mypackage.my_cursor%ISOPEN THEN
8: CLOSE my_package.my_cursor;
9: END IF;
10: END;
SELECT … FOR UPDATE
当使用 select 语句从数据库查询记录时, 数据库不会被选择的行添加任何锁. 通常来说, 这是一个非常不错的特性, 因为任何时候被锁定的记录数量都要尽可能的最小, 只有那些已经被修改了但是还没有提交的记录才需要被锁定. 即使这样, 其他人还是可以看到这些记录被修改之前的状态.
不过, 有时候我们希望在程序修改之前就锁住它们. 这时就可以使用 SELECT … FOR UPDATE
1: CURSOR toys_cur IS
2: SELECT name, manufacturer, preference_level
3: from my_sons_collection
4: where hours_used = 0
5: for update; -- 没有任何限制
6:
7: CURSOR fall_jobs_cur IS
8: SELECT task, expected_hours, tools_required
9: from winterize
10: where year_of_task = to_char(sysdate, 'yyyy')
11: for update of task; -- 只针对 task 这列限制
我们也可以夺标SELECT中使用 for update, 这种情况, 只有自己列出的在 for update of 子句中的记录才会被锁定, 换句话说, 必须要有限制.
WHERE CURRENT OF 语句
plsql 为游标的 update 和 delete 提供了 where current of 语句. 这个语句可以让我们很容易的修改最后取出来的数据行.
1: update table_name
2: set set_clause
3: where current of cursor_name;
4:
5: delete
6: from table_name
7: where current of cursor_name;
例如: 删除一个我刚刚取出的数据, 更新一个我刚刚取出的数据.
1: declare
2: cursor fall_jobs_cur IS select ... same as before ...;
3: job_rec fall_jobs_cur%rowtype;
4: begin
5: open fall_jobs_cur;
6: loop
7: fetch fall_jobs_cur into job_rec;
8:
9: exit when fall_jobs_cur%notfound;
10:
11: if job_rec.do_it_yourself_flag = 'youcandoit' then
12: update winterize
13: set responsible = 'steven'
14: where current of fall_jobs_cur;
15: commit;
16: exit;
17: end if;
18: end loop;
19: close fall_jobs_cur;
20: 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) 把游标变量从一个服务器传递给另一个服务器.( 游标变量保存的只是引用)
游标变量作为参数传递
1: declare
2: type curvar_type is ref cursor return company%rowtype;
3: procedure open_query(curvar_out OUT curvar_type)
4: is
5: local_cur curvar_type;
6: begin
7: open local_cur for select * from company;
8: curvar_out := local_cur;
9: end;
游标表达式(用于嵌套游标)
优质内容筛选与推荐>>
1、最新无镜头扑克感应
2、android中的margin和padding
3、波兰表示法和逆波兰表示法的转换
4、ligerui_ligerTree_005_动态增加“树”节点
5、pytorch中tensor的squeeze()和unsqueeze()