CODEWITHSUNDEEP
htmlsqloracle-databasefunctionplsql
Andy Kaufman
Andy Kaufman

Reputation: 781

Creating PL/SQL function from query

Little knowledge of PL/SQL here, so need a bit of help.

I have the a query that I need to turn into a function (let's call it reject_list), but not sure how to do it. This is what I have so far:

create or replace function reject_list(ayrc in varchar2,mcrc in varchar2)
return string
begin
select distinct
'<tr><td>'||cap.cap_uci2||'</td>
<td>'||cap.cap_stuc||'</td>
<td>'||cap.cap_mcrc||'</td>
<td>'||cap.cap_ayrc||'</td>
<td>'||stu.stu_fnm1||'</td>
<td>'||stu.stu_surn||'</td>
<td>'||cap.cap_stac||'</td>
<td>'||cap.cap_crtd||'</td></tr>'
from
intuit.srs_cap cap
,intuit.ins_stu stu
,intuit.srs_apf apf
where
cap.cap_stuc = stu.stu_code
and cap.cap_apfs = apf.apf_seqn
and cap.cap_stuc = apf.apf_stuc
and cap.cap_mcrc = &mcrc
and cap.cap_ayrc = &ayrc
and cap.cap_idrc in ('R','CR','CFR')
and apf.apf_recd <= to_date('1501'||substr(&ayrc,1,4),'DDMMYYYY');
end;

This doesn't run - can anyone help?

Thanks :)

EDIT: This query is one that is being run in an application already but we are trying to optimize it for speed. I am not certain whether a function is the best option, but we have, in another part of the application created a function to return simple counts which improved the speed exponentially. I need guidance more than just straightforward instructions on how to turn this into a function. If a view is the best option, for example, please could someone offer some guidance on how would be the best way to do this?

The object, therefore, is to be able to have a query stored on the server which allows me to enter the parameters and return the fields listed. To make this more complicated, one thing I did not mention before is that this needs to be formatted as an HTML table. I have now added the markup that would do this into the query above, and all the fields need to be concatenated.

Any help on this is greatly appreciated.

Upvotes: 0

Views: 606

Answers (2)

sergdenisov
sergdenisov

Reputation: 8572

Try something like this (you must only change varchar(256) on your column's types):

create type t_row as object
(
cap_uci2 varchar(256)
, cap.cap_stuc varchar(256)
, cap.cap_mcrc varchar(256)
, cap.cap_ayrc varchar(256)
, stu.stu_fnm1 varchar(256)
, stu.stu_surn varchar(256)
, cap.cap_stac varchar(256)
, cap.cap_crtd varchar(256)
);
/

create type t_tab is table of t_row;
/

create or replace function reject_list(ayrc varchar2, mcrc varchar2)
  return t_tab pipelined
begin
  for cur in
  (
  select distinct 
         cap.cap_uci2
         , cap.cap_stuc
         , cap.cap_mcrc
         , cap.cap_ayrc
         , stu.stu_fnm1
         , stu.stu_surn
         , cap.cap_stac
         , cap.cap_crtd
  from intuit.srs_cap cap
       , intuit.ins_stu stu
       , intuit.srs_apf apf
  where cap.cap_stuc = stu.stu_code
        and cap.cap_apfs = apf.apf_seqn
        and cap.cap_stuc = apf.apf_stuc
        and cap.cap_mcrc = mcrc
        and cap.cap_ayrc = ayrc
        and cap.cap_idrc in ('R', 'CR', 'CFR')
        and apf.apf_recd <= to_date ('1501' || substr(ayrc, 1, 4), 'DDMMYYYY')
  )
    loop
      pipe row(cur);
    end loop;
end;
/

After that you can use the function this way (change 'xxx', 'yyy' on your param's values):

select *
from table(reject_list('xxx', 'yyy'));

Upvotes: 0

dseibert
dseibert

Reputation: 1329

You may have to loop through the results of the select statement using a cursor. Please consider the following code as a guide. http://www.plsql-tutorial.com/plsql-cursors.htm. Also please consider prefixing your function parameters with P_ or something like that. It will make them easier to spot in the code.

FUNCTION YOUR_FUNCTION(p_ayrc in varchar2,p_mcrc in varchar2) 
RETURN SYS_REFCURSOR
  IS
    THE_RESULT SYS_REFCURSOR;
    BEGIN
            OPEN THE_RESULT FOR
              select distinct
        cap.cap_uci2
        ,cap.cap_stuc
        ,cap.cap_mcrc
        ,cap.cap_ayrc
        ,stu.stu_fnm1
        ,stu.stu_surn
        ,cap.cap_stac
        ,cap.cap_crtd
        from
        intuit.srs_cap cap
        ,intuit.ins_stu stu
        ,intuit.srs_apf apf
        where
        cap.cap_stuc = stu.stu_code
        and cap.cap_apfs = apf.apf_seqn
        and cap.cap_stuc = apf.apf_stuc
        and cap.cap_mcrc = p_mcrc
        and cap.cap_ayrc = p_ayrc
        and cap.cap_idrc in ('R','CR','CFR')
        and apf.apf_recd <= to_date('1501'||substr(&ayrc,1,4),'DDMMYYYY');
            RETURN THE_RESULT;
END;

Upvotes: 1

Related Questions