How do you add cursor into a procedure in PL/SQL block?

Question

I need to create a procedure that accepts first and last letter of a person's name and returns the totalcost and total items bought by him. I have no idea how to use the cursor with it also i'm thinking i have to use a for loop and exception handling since there can be many persons with the same starting and ending letter's.

So far I have come up with this:

    create or replace procedure total_spent(v_fname IN 
    saleinv.cname%TYPE,v_lname IN saleinv.cname%TYPE.v_netspend OUT 
    saleinv.net%TYPE,v_totalpurch OUT NUMBER) AS
    Begin
    select sum(net+tax),count(net) into v_netspend,v_totalpurch from saleinv 
    where cname LIKE '&v_fname%&v_lname';
    END;
    /

    ACCEPT p_fname PROMPT  'Enter The first letter of customer's name' 
    ACCEPT p_lname PROMPT 'Enter the last letter of customer's name'
    BEGIN
    totalspent('&p_fname',&'p_lname');

Sounds like a set based approach is totally fine and there's no need for a cursor. (But you maybe want to rewrite the like expression of the query to `LIKE v_fname || '%' || v_lname`.) — sticky bit, Aug 05 '18 at 03:52
it says i gotta use a cursor. other than the expression everything else is ok though? — Gurmohit Singh, Aug 05 '18 at 05:28
So, if someone's name is "SingH", you have to provide "S" and "H" as parameters and return some values related to "him" (as you put it). What if that table contains a person named "SetH", i.e. they both share the same first and last letter. Whose data will you return? — Littlefoot, Aug 05 '18 at 06:52
for that i have to use for loop to pull data for every person with similar letters i think — Gurmohit Singh, Aug 05 '18 at 17:41

score 0 · Answer 1 · answered Aug 06 '18 at 08:02

Came up with one solution, with small modifications (by the way, be more specific with parameters names, that there would be no interpretations):

procedure total_spent(
  p_fname_first_letter in varchar2,
  p_lname_last_letter in varchar2,
  p_netspend out number,
  p_totalpurch out number) as

  cursor c_net(
    p_fname_first_letter varchar2,
    p_lname_last_letter varchar2) is
  select
    sai.cname, 
    sum(sai.net + sai.tax) net_spend,
    count(sai.net) total_purch 
  from 
    saleinv sai
  where 
    upper(sai.cname) like upper(p_fname_first_letter) || '%' || upper(p_lname_last_letter)
  group by
    sai.cname;

  l_found_cust boolean := false;
  l_show_err boolean := false;
  l_cust_list varchar2(100);

Begin

  p_netspend := 0;
  p_totalpurch := 0;

  for l_sai_rec in c_net(
    p_fname_first_letter,
    p_lname_last_letter)
  loop

    if not l_found_cust then

      p_netspend := l_sai_rec.net_spend;
      p_totalpurch := l_sai_rec.total_purch;
      l_cust_list := l_sai_rec.cname;
      l_found_cust := true;

    else

      l_cust_list := l_cust_list || '; ' || l_sai_rec.cname;
      l_show_err := true;

    end if;

  end loop; 

  if l_show_err then
    raise_application_error(-20101, 'Found more customers with provided parameters. Customers: '||l_cust_list);
  end if;

end;

It uses cursor, as you mentioned. Of course, there is a better way to do this, without cursor, that also fulfills your logic requirements (less code). If you are willing to know, feel free to ask.

How do you add cursor into a procedure in PL/SQL block?

1 Answers1