html - Creating PL/SQL function from query -

little knowledge of pl/sql here, need bit of help.

i have query need turn function (let's call reject_list), not sure how it. have 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>' intuit.srs_cap cap ,intuit.ins_stu stu ,intuit.srs_apf apf cap.cap_stuc = stu.stu_code , cap.cap_apfs = apf.apf_seqn , cap.cap_stuc = apf.apf_stuc , cap.cap_mcrc = &mcrc , cap.cap_ayrc = &ayrc , cap.cap_idrc in ('r','cr','cfr') , apf.apf_recd <= to_date('1501'||substr(&ayrc,1,4),'ddmmyyyy'); end; 

this doesn't run - can help?

thanks :)

edit: query 1 being run in application trying optimize speed. not whether function best option, have, in part of application created function return simple counts improved speed exponentially. need guidance more straightforward instructions on how turn function. if view best option, example, please offer guidance on how best way this?

the object, therefore, able have query stored on server allows me enter parameters , return fields listed. make more complicated, 1 thing did not mention before needs formatted html table. have added markup query above, , fields need concatenated.

any on appreciated.

you may have loop through results of select statement using cursor. please consider following code guide. http://www.plsql-tutorial.com/plsql-cursors.htm. please consider prefixing function parameters p_ or that. make them easier spot in code.

function your_function(p_ayrc in varchar2,p_mcrc in varchar2)  return sys_refcursor       the_result sys_refcursor;     begin             open the_result               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                 intuit.srs_cap cap         ,intuit.ins_stu stu         ,intuit.srs_apf apf                 cap.cap_stuc = stu.stu_code         , cap.cap_apfs = apf.apf_seqn         , cap.cap_stuc = apf.apf_stuc         , cap.cap_mcrc = p_mcrc         , cap.cap_ayrc = p_ayrc         , cap.cap_idrc in ('r','cr','cfr')         , apf.apf_recd <= to_date('1501'||substr(&ayrc,1,4),'ddmmyyyy');             return the_result; end;