oracle函数中的多光标

24沙龙

我想创建一个 oracle 函数，该函数将 user_id 作为参数并返回 varchar2，其中包含用户从图书馆拿走但没有归还的书籍的电子邮件文本，我想要的电子邮件文本是：“你好“你应该归还这本书：1<BOOK_NAME>，在 2.....

这是我到目前为止所写的

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;

我有3个问题

我怎样才能归还所有的书而不是最后一本书（比如 c 中的 +=）
对于客户端名称，我是否需要添加另一个游标，可以吗？

如何从原始查询传递值

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

书，其中 recived =0 和 recived_date 为空

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

小脚怪

我没有你的表，所以我将使用 Scott 的示例模式来说明你可以如何做到这一点。阅读代码中的注释。

功能：

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.

测试：

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>

如果您 - 正如您所说 - 想要“动态”传递 ID，只需将其包含在函数中。像这样的东西（我正在检索部门 10 和 30 的数据，适用于所有担任文员的人）：

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>

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。