oracle select from table(,请教Table()使用方法 select * from table(....)

找到了，可惜、居然 itpub没人能回答

You Asked (Jump to Tom's latest followup)

1. Does oracle support calling a procedure as a derived table,

Eg

select * from (call myprocedure) ....

2. Also, does it support creation of a view over a procedure ,

Eg,

Create view as (call myprocedure)

and we said...

You need to use Oracle9i or up to do that (but I do show a way in 8i):

ops$tkyte@ORA9I.WORLD> create or replace function virtual_table( p_num_rows in

number )

2  return virtual_table_type

3  PIPELINED -- NOTE the pipelined keyword

4  is

5  begin

6      for i in 1 .. p_num_rows

7      loop

8          pipe row( i );

9      end loop;

10

11      return; -- note return with no value!

12  end;

13  /

Function created.

ops$tkyte@ORA9I.WORLD> ops$tkyte@ORA9I.WORLD> ops$tkyte@ORA9I.WORLD> set echo

off

Enter to continue

=================================================================

now we'll just see this in action

ops$tkyte@ORA9I.WORLD> ops$tkyte@ORA9I.WORLD> select * from TABLE(

virtual_table(5) );

COLUMN_VALUE

------------

1

2

3

4

5

ops$tkyte@ORA9I.WORLD> select * from TABLE( virtual_table(10) );

COLUMN_VALUE

------------

1

2

3

4

5

6

7

8

9

10

10 rows selected.

We use "pipe" instead of suspend.

Prior to 9i, there is no pipe, it would look like this:

ops$tkyte@ORA9I.WORLD> create or replace type virtual_table_type as table of

number

2  /

Type created.

ops$tkyte@ORA9I.WORLD> ops$tkyte@ORA9I.WORLD> create or replace function

virtual_table( p_num_rows in

number )

2  return virtual_table_type

3  is

4          l_data virtual_table_type := virtual_table_type();

5  begin

6      for i in 1 .. p_num_rows

7      loop

8                  l_data.extend;

9                  l_data(l_data.count) := i;

10      end loop;

11

12      return l_data;

13  end;

14  /

Function created.

ops$tkyte@ORA9I.WORLD> ops$tkyte@ORA9I.WORLD> select * from TABLE(

virtual_table(5) );

COLUMN_VALUE

------------

1

2

3

4

5

ops$tkyte@ORA9I.WORLD> select * from TABLE( virtual_table(10) );

COLUMN_VALUE

------------

1

2

3

4

5

6

7

8

9

10

10 rows selected.

you would fill up the collect and then return it (no "pipe", no suspend

functionality)

Reviews

GOTO a page to Bookmark Review | Bottom | Top

Great answer!!  December 19, 2001

Reviewer:  Jimson K John  from Bangalore, India

This is what I was looking for! Thanks!

GOTO a page to Bookmark Review | Bottom | Top

Interesting  December 20, 2001

Reviewer:  Randy  from CHicago,IL

It sounds very useful. But unfortunately when I try

select * from TABLE(virtual_table(5) ); after finishing previous steps in my

8.1.7 server, I am get

select * from TABLE(virtual_table(5) )

*

ERROR at line 1:

ORA-22905: cannot access rows from a non-nested table item

The type and the function was creted successfully.

Followup:

try:

ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from TABLE( cast ( virtual_table(5)

as virtual_table_type ) );

COLUMN_VALUE

------------

1

2

3

4

5

sorry about that -- forgot the CAST which is needed in 8i, but not 9i