当前位置：首页 » 软件开发

开发技术指南» 文章正文

    引言： Result Sets from Stored Proced

 

 

ResultSetsfromStoredProceduresInOracle

a frequently asked question is: 【程序编程相关:install mysql-standa】

result sets from stored procedures in oracle 【推荐阅读:如何取消jakarta-tomcat-5】

【扩展信息:转:动态网页的搜索引擎优化】

id like to know whether oracle supports procedures (functions) which

returns result sets.

the answer is most definitely yes.  in short, itll look like this:

 

create or replace function sp_listemp return types.cursortype

as

    l_cursor    types.cursortype;

begin

    open l_cursor for select ename, empno from emp order by ename;

    return l_cursor;

end;

/

with 7.2 on up of the database you have cursor variables.  cursor variables are cursors opened by a pl/sql routine and fetched from by another application or pl/sql routine (in 7.3 pl/sql routines can fetch from cursor variables as well as open them). the cursor variables are opened with the privelegs of the owner of the procedure and behave just like they were completely contained within the pl/sql routine. it uses the inputs to decide what database it will run a query on.

here is an example:

 

create or replace package types

as

    type cursortype is ref cursor;

end;

/ create or replace function sp_listemp return types.cursortype

as

    l_cursor    types.cursortype;

begin

    open l_cursor for select ename, empno from emp order by ename;     return l_cursor;

end;

/

examples for sqlplus, pro*c, java/jdbc, odbc, ado/asp, dbi perl and oci follow:

rem sql*plus commands to use a cursor variable variable c refcursor

exec :c := sp_listemp

print c

and the pro*c to use this would look like:

static void process()

{

exec sql begin declare section;

    sql_cursor  my_cursor;

    varchar     ename[40];

    int         empno;

exec sql end declare section;     exec sql whenever sqlerror do sqlerror_hard();     exec sql allocate :my_cursor;     exec sql execute begin

        :my_cursor := sp_listemp;

...   下一页

» 本期热门文章：

· 热门栏目：

» 相关精选文章

» 其它相关：