Implementation of oracle function return table

Source: Internet
Author: User

How should we implement the return table through the oracle function? The following describes how to implement an oracle function return table for your reference.

Use a stored procedure in the package and return the cursor.

> Package Definition
1) Baotou

 
 
  1. create or replace package mypk  
  2. as  
  3. type t_cursor is ref cursor;  
  4. procedure proc(name varchar2,c out t_cursor,a number);  
  5. end; 

2) package body

 
 
  1. create or replace package body mypk  
  2. as  
  3. procedure proc(name varchar2,c out t_cursor,a number)  
  4. as   
  5. begin  
  6. open c for select * from test where id=a and namename=name;  
  7. end proc;  
  8. end;  

This solution has too many limitations to meet the requirements of select * from function ().

Since oracle 9i, a concept called "pipeline table function" is provided to solve this problem.
This type of function must return a set type and indicate pipelined
This oracle function cannot return specific variables and must return an empty return
In this oracle function, each row in the table to be returned is sent using the pipe row () statement.

When this oracle function is called, the pipeline stream is simulated as a dataset using the table () keyword.

The following is a simple example:

 
 
  1. create table tb1(k number, v varchar2(10));  
  2.  
  3. insert into tb1(k, v) values(100,'aaa');  
  4. insert into tb1(k, v) values(200,'bbb');  
  5. insert into tb1(k, v) values(200,'ccc');  
  6.  
  7. select * from tb1;  
  8.  
  9. create type row_type1 as object(k number, v varchar2(10));  
  10.  
  11. create type table_type1 as table of row_type1;  
  12.  
  13. create or replace function fun1 return table_type1 pipelined as  
  14. v row_type1;  
  15. begin  
  16. for myrow in (select k, v from tb1) loop  
  17. v := row_type1(myrow.k, myrow.v);  
  18. pipe row (v);  
  19. end loop;  
  20. return;  
  21. end;  
  22.  
  23. select * from table(fun1);  
  24.  

If an oracle function has parameters, you can write the following statement:

 
 
  1. create or replace function fun1(i_v Int) return table_type1 pipelined as  
  2. v1 row_type1;  
  3. begin  
  4. for myrow in (select k, v from tb1 Where k = i_v) loop  
  5. v1 := row_type1(myrow.k, myrow.v);  
  6. pipe row (v1);  
  7. end loop;  
  8. return;  
  9. end;  
  10.  
  11. select * from table(fun1(100));  

This scheme can basically meet the requirements of the returned table, but it should be noted that the application of too many set objects is not conducive to management.
 
 

Learn about the Oracle FBI Index

How to uninstall an Oracle database in Windows

How to install Oracle as a Linux Service

Multiple table Connection Methods in Oracle

Example of using SQL recursive statements in oracle

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.