0

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

24sharon
  • 1,859
  • 7
  • 40
  • 65

2 Answers2

1

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>
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
0

You may use LISTAGG function to achieve this. This task can be solved in pure SQL via view, which will not produce child cursors compared to function invocation. You just need to filter it if you wish to select individual clients.

However you can also put the same text in a function if you wish, which also doesn't require any loops. Because from Oracle 12C and above you may extend varchar2 up to 32767 bytes with MAX_STRING_SIZE = EXTENDED parameter, which removes the boundary of varchar2 in SQL compared to PL/SQL.

Both the examples are below:

create table client
as
select
  level as id
  , 'Client ' || level as name
from dual
connect by level < 10

create table book
as
select
  level as id
  , 'Book ' || level as name
from dual
connect by level < 100

create table borrowed_book
as
select
  trunc(level / 4) + 1 as client_id
  , level as book_id
  , add_months(sysdate, -mod(level, 11)) as dt
  , case mod(level, 4) when 1 then 1 else 0 end as received
from dual
connect by level < 60

create view v_reminder
as
  select
    bb.client_id,
    c.name as client_name,
    'Hello, ' || max(c.name) || '!'
    || chr(10) || chr(10)
    || 'You should return this books:' || chr(10)
    || listagg(b.name || ' borrowed on ' || to_char(bb.dt, 'dd-Mon-yyyy'), chr(10))
         within group(order by bb.dt desc, bb.book_id) as rem

  from borrowed_book bb
    join client c
      on bb.client_id = c.id
    join book b
      on bb.book_id = b.id
  where bb.received = 0
    and bb.dt < add_months(sysdate, -3)
  group by bb.client_id, c.name

create or replace function f_get_reminder(p_client_id in int, p_dt_offset in int default 3)
return varchar2
as
  res varchar2(32000);
begin
  select
    'Hello, ' || max(c.name) || '!'
    || chr(10) || chr(10)
    || 'You should return this books:' || chr(10)
    || listagg(b.name || ' borrowed on ' || to_char(bb.dt, 'dd-Mon-yyyy'), chr(10))
         within group(order by bb.dt desc, bb.book_id)
    
    into res
  from borrowed_book bb
    join client c
      on bb.client_id = c.id
    join book b
      on bb.book_id = b.id
  where bb.received = 0
    and bb.dt < add_months(sysdate, -p_dt_offset)
    and bb.client_id = p_client_id
  /*Added this line to get NULL as output, because aggregate function
  without group by always returns a row even for empty input dataset*/
  group by bb.client_id
  ;
    
  return res;
end;
/

select
  c.id
  , c.name
  , f_get_reminder(c.id) as rem
from client c
ID NAME REM
1 Client 1 null
2 Client 2 Hello, Client 2!

You should return this books:
Book 4 borrowed on 27-Feb-2021
Book 6 borrowed on 27-Dec-2020
Book 7 borrowed on 27-Nov-2020
3 Client 3 Hello, Client 3!

You should return this books:
Book 8 borrowed on 27-Oct-2020
Book 10 borrowed on 27-Aug-2020
4 Client 4 Hello, Client 4!

You should return this books:
Book 15 borrowed on 27-Feb-2021
5 Client 5 Hello, Client 5!

You should return this books:
Book 16 borrowed on 27-Jan-2021
Book 18 borrowed on 27-Nov-2020
Book 19 borrowed on 27-Oct-2020
6 Client 6 Hello, Client 6!

You should return this books:
Book 20 borrowed on 27-Sep-2020
7 Client 7 Hello, Client 7!

You should return this books:
Book 26 borrowed on 27-Feb-2021
Book 27 borrowed on 27-Jan-2021
8 Client 8 Hello, Client 8!

You should return this books:
Book 28 borrowed on 27-Dec-2020
Book 30 borrowed on 27-Oct-2020
Book 31 borrowed on 27-Sep-2020
9 Client 9 Hello, Client 9!

You should return this books:
Book 32 borrowed on 27-Aug-2020

db<>fiddle here

astentx
  • 6,393
  • 2
  • 16
  • 25