Re: Proc or function to return a table

<devjnr_at_gmail.com> a écrit dans le message de news: 1157101017.069406.307050_at_e3g2000cwe.googlegroups.com...
|I would like to write a proc or a function (I think 2nd is better)
| that, accepting a table name as argument should return a table itself.
|
| Scenario:
|
| I have a matricial representation of a table in another table:
|
| tab_matr:
|
| i j value
|
| 1 1 200
| 1 2 400
| 2 1 150
| 2 2 500
| 3 1 750
| 3 2 600
| ...
| ...
|
| I wrote an anonymous block that transform these values into a table
| like this:
|
| 200 400
| 150 500
| 750 600
| ...
| ...
|
| I would like to write a proc to simply do something like this:
|
| begin
| some_proc('tab_matr');
| end;
|
| This is important because I'll have to join the resultset with other
| tables or other resultsets coming from same function with another table
| name as argument:
|
| begin
| select
| x.somcolumn,
| y.someothercolumn
| from
| some_proc('tab_matr') x
| join
| some_proc('other_tab') y on x.id = y.id;
| end;
|
|
| I'm quite a newbie in PL/SQL and I would get best way to start,
| possibly to avoid writing non efficient code and using all
| functionality and performance of PL/SQL.
|
| Best regards to all.
|

select max(decode(j,1,value)) c1, max(decode(j,2,value)) c2 from tab_matr
group by i
order by i
/

Regards
Michel Cadot Received on Fri Sep 01 2006 - 10:31:37 CDT