how to count multiple rows and return the result in pl/sql oracle packages

Question

i have a table that has multiple records, however some records are similar from the following table

Table 1

Code         P1       P2         P3
 W           5        10         20
 S           5        10         20
 W           6        20         35

if i pass P1 , P2 , P3 as parameters following

select *
from table 1
where P1=5 and P2=10 and P3=20

it will return the result W and S,but only the W that matches to the paramteres. How do i go about creating a package that will count all the multiple records and return the codes that are similar? I am not familiar with oracle

it is not quite clear, what are you trying to achieve... why 'only W matches' ? clarify your question and give an example of desired output. — PKey
– PKey, Commented Aug 29, 2016 at 6:19
no S matches aswell, i mentioned that W and S should be returned because it has matching parameters, but if you notice theres 2 W's so only the corresponding W will get returned — doe
– doe, Commented Aug 29, 2016 at 6:23
so you want to return a similar result as the query you provided, say this one : select code from table1 where P1=5 and P2=10 and P3=20 - only a result should come from a function (inside a package) ? — PKey
– PKey, Commented Aug 29, 2016 at 6:29

PKey · Accepted Answer · 2016-08-29 08:59:51Z

This can be solved with pipelined functions.

declare package and types

CREATE OR REPLACE PACKAGE mytestpackage AS

    TYPE my_record is RECORD(
           code       varchar2
    );

    TYPE my_table IS TABLE OF my_record;

function  get_results(par1 number,par2 number,par3 number)  RETURN my_table PIPELINED;

end mytestpackage;

package body

CREATE OR REPLACE PACKAGE BODY as 

    function  get_results(par1 number,par2 number,par3 number)  RETURN my_table PIPELINED is 
        my_rec   my_record:=null;
        cursor myCursor(myp1 number,myp2 number,myp3 number) is
        select code from table1 where P1=myp1 and P2=myp2 and P3=myp3
        ;

        begin 

        --loop through outputs 

         FOR item IN myCursor(par1,par2,par3)  LOOP
          my_rec:=null; 
          select item.code into my_rec from dual;
          PIPE ROW(my_rec);
         end loop;
        return;
        end; 
end mytestpackage;

and finally use it

SELECT * FROM TABLE(mytestpackage.get_results(5,10,20));

Collectives™ on Stack Overflow

how to count multiple rows and return the result in pl/sql oracle packages

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related