[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 这样通过。