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>