ITPub博客

首页 > Linux操作系统 > Linux操作系统 > [20130918]12c FETCH FIRST和绑定变量.txt

[20130918]12c FETCH FIRST和绑定变量.txt

原创 Linux操作系统 作者:lfree 时间:2013-09-22 10:06:00 0 删除 编辑
[20130918]12c FETCH FIRST和绑定变量.txt

http://connormcdonald.wordpress.com/2013/09/11/12c-fetch-first/

重复测试:

@ver
BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0

drop table T purge;
create table T as select * from all_objects;

select owner, object_name, object_id from t order by owner, object_name FETCH FIRST 5 ROWS ONLY;

OWNER                OBJECT_NAME                     OBJECT_ID
-------------------- ------------------------------ ----------
APEX_040200          APEX                                88917
APEX_040200          APEX$ARCHIVE_CONTENTS               89744
APEX_040200          APEX$ARCHIVE_CONTENTS_IDX1          89748
APEX_040200          APEX$ARCHIVE_HEADER                 89742
APEX_040200          APEX$ARCHIVE_HISTORY                89749

variable x number
exec :x  := 5

select owner, object_name, object_id from t order by owner, object_name FETCH FIRST :x  ROWS ONLY;

OWNER                OBJECT_NAME                     OBJECT_ID
-------------------- ------------------------------ ----------
APEX_040200          APEX                                88917
APEX_040200          APEX$ARCHIVE_CONTENTS               89744
APEX_040200          APEX$ARCHIVE_CONTENTS_IDX1          89748
APEX_040200          APEX$ARCHIVE_HEADER                 89742
APEX_040200          APEX$ARCHIVE_HISTORY                89749


declare
  x number := 5;
begin
 for i in (
select owner, object_name, object_id
from t
order by owner, object_name
FETCH FIRST x ROWS ONLY
)
loop
  null;
end loop;
end;
/

*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 2580
Session ID: 128 Serial number: 57

--出现错误!

declare
  x number := 5;
begin
 for i in (
select owner, object_name, object_id
from t
order by owner, object_name
FETCH FIRST cast(x as number) ROWS ONLY
)
loop
  null;
end loop;
end;
/

PL/SQL procedure successfully completed.
--OK 这样通过。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-772950/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2455
  • 访问量
    6257654