oracle-databasecursor

Multi cursor in oracle function

I want to create an oracle function that get the user_id as param and return varchar2 that contain the e-mail text for the books that the user take from the library and didn't return them back, my email text that I want is: "hello " You should return this books: 1 <BOOK_NAME> that taken at 2. ....

This is what I wrote so far

create or replace function get_un_recived_books(param_client_id in number) return varchar2 is
  Result varchar2(2000);
 

      
   cursor cur_book is 
      select * from hashala natural join client natural join all_books natural join book
           where 
                    recived =0
                    and recived_date is null
                    and clientid= param_client_id          
                    and taken_date <   add_months(trunc(sysdate, 'month'), -3);
                    
        begin
               FOR b IN cur_book LOOP

        Result:= 'book  ' || b.book_name;
  END LOOP;
 
                  
  return(Result);
end;

I have 3 problems

  1. how can i return all the books and not the last one (like += in c)

  2. for the client name do I need to add another cursor, can I do it?

  3. how to pass the value from the original query

      select get_un_recived_books(!!!ADD HERE THE CLIENID!!!), clientid from hashala natural join client natural join all_books natural join

    book where recived =0 and recived_date is null

                     and taken_date <   add_months(trunc(sysdate, 'month'), -3);

enter image description here

Solution

  • I don't have your tables so I'll use Scott's sample schema to illustrate how you might do that. Read comments within code.

    Function:

    SQL> create or replace function f_test (par_deptno in number)
  2    return varchar2
  3  is
  4    l_dname  dept.dname%type;
  5    retval   varchar2(4000);
  6  begin
  7    -- department name (in your case, client name)
  8    select dname
  9      into l_dname
 10      from dept
 11      where deptno = par_deptno;
 12
 13    -- loop through employees in PAR_DEPTNO (in your case,
 14    -- books client borrowed)
 15    for cur_r in (select ename, hiredate
 16                  from emp
 17                  where deptno = par_deptno
 18                 )
 19    loop
 20      -- this is what you're missing: "retval := retval || ..."
 21      retval := retval || 'Name: ' || cur_r.ename ||
 22               ', borrowed on ' || to_char(cur_r.hiredate, 'dd.mm.yyyy') || chr(10);
 23    end loop;
 24
 25    retval := 'Hello, ' || l_dname ||chr(10) ||
 26              'you borrowed the following books and didn''t return them yet.' || chr(10) ||
 27              retval;
 28    return retval;
 29  end;
 30  /

Function created.

    Testing:

    SQL> select f_test(10) from dual;

F_TEST(10)
--------------------------------------------------------------------------------
Hello, ACCOUNTING
you borrowed the following books and didn't return them yet.
Name: CLARK, borrowed on 09.06.1981
Name: KING, borrowed on 17.11.1981
Name: MILLER, borrowed on 23.01.1982


SQL>

    If you - as you said - want to pass the ID "dynamically", just include it into the function. Something like this (I'm retrieving data for departments 10 and 30, for everyone who works as a clerk):

    SQL> select f_test(d.deptno)
  2  from dept d join emp e on e.deptno = d.deptno
  3  where d.deptno in (10, 30)
  4    and e.job = 'CLERK';

F_TEST(D.DEPTNO)
------------------------------------------------------------------------
Hello, SALES
you borrowed the following books and didn't return them yet.
Name: ALLEN, borrowed on 20.02.1981
Name: WARD, borrowed on 22.02.1981
Name: MARTIN, borrowed on 28.09.1981
Name: BLAKE, borrowed on 01.05.1981
Name: TURNER, borrowed on 08.09.1981
Name: JAMES, borrowed on 03.12.1981

Hello, ACCOUNTING
you borrowed the following books and didn't return them yet.
Name: CLARK, borrowed on 09.06.1981
Name: KING, borrowed on 17.11.1981
Name: MILLER, borrowed on 23.01.1982


SQL>