Creating PL/SQL function from query

Question

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.

There's a lot about your question that's unclear. What do you want your function to return? (string isn't a valid SQL type, in Oracle at least.) Why do you need to turn your query into a function? (If it's just to be able to use the same query in multiple places without repeating it, you might be better off with a view.) In SQL Server, stored functions can return the result of a SELECT query - are you looking for the Oracle equivalent of this? — Luke Woodward
– Luke Woodward, Commented Jul 17, 2013 at 20:02
Please see the edits I have made above. I initially put this up in a bit of a rush, hence the lack of information, but please let me know if anything else needs clarification. — Andy Kaufman
– Andy Kaufman, Commented Jul 18, 2013 at 9:05
@LukeWoodward - STRING is perfectly valid in Oracle. It's defined in SYS.STANDARD as subtype STRING is VARCHAR2;. Share and enjoy. — Bob Jarvis - Слава Україні
– Bob Jarvis - Слава Україні, Commented Jul 18, 2013 at 10:32
Further questions: when you say "doesn't run", what do you mean? Do you get an error, and if so, what? Why do you need SELECT DISTINCT in your query? What indexes are on the tables? Roughly how many rows are in the tables and roughly how many rows should the query return? How is this function being called, and from what language? Also, why do you need to return HTML from the database? — Luke Woodward
– Luke Woodward, Commented Jul 18, 2013 at 19:50

Jafar Kofahi · Accepted Answer · 2013-07-17 16:40:30Z

1

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;

edited Jul 17, 2013 at 16:40

Jafar Kofahi

7637 silver badges23 bronze badges

answered Jul 17, 2013 at 16:06

dseibert

1,3299 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Andy Kaufman Over a year ago

OK, I think I understand the concept of cursors now, but not really sure how to implement them and this statement still doesn't run. Any further suggestions?

sergdenisov · Accepted Answer · 2013-07-17 19:36:39Z

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'));

Collectives™ on Stack Overflow

Creating PL/SQL function from query

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related