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
how can i return all the books and not the last one (like += in c)
for the client name do I need to add another cursor, can I do it?
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);
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>