在过程PL/SQL内调用内部函数

Question

使用PL/SQL函数有点新手，但是尝试添加一个函数，如果当前用户没有通过应用程序获取相对alt_id的alt_id。在此之前，非常感谢您。

CREATE OR REPLACE PROCEDURE CLIENT.p_Verification(pin_Member_ID   IN dbo.member.member_id%Type,
                                                                 pin_Person_ID   IN dbo.person.person_id%type,
                                                                 pin_user_id     IN NUMBER default null,
                                                                 pioc_ref_cursor IN OUT dbo.pkg_benefit_q.ref_cursor) IS

lv_member_id         dbo.person.alt_identifier%Type;
-- other variables
...
Procedure p_get_other_deductions(...
Begin
...
end;

此处有新函数

    FUNCTION f_get_alt_id 
         RETURN Varchar2 
         IS-- dbo.person.alt_identifier%Type IS
        alt_id Varchar2(10); --dbo.person.alt_identifier%Type;
    BEGIN        

      SELECT p.ALT_IDENTIFIER
        into alt_id
        FROM DBO.PERSON p , DBO.MEMBER m, DBO.APPLICATION a
      where p.PERSON_ID = m.PERSON_ID
        AND m.MEMBER_ID = a.MEMBER_ID
        AND a.APPLICATION_ID= cn_EntAppId
        ;

      RETURN alt_id;

    END;

把它叫到这里。

  Select dbo.pkg_benefit_s.f_get_value_description('PREFIX',
                                                   p.prefix,
                                                   'Y',
                                                   'N') || case
           when p.prefix is not null then
            ' '
           else
            Null
         end || nvl2(p.first_name, initcap(p.first_name) || ' ', '') ||
         nvl2(p.middle_name, initcap(p.middle_name) || ' ', '') ||
         nvl2(p.last_name, initcap(p.last_name), '') || ' ' ||
         dbo.pkg_benefit_s.f_get_value_description('SUFFIX',
                                                   p.suffix,
                                                   'Y',
                                                   'N'),
         nvl2(p.alt_identifier, 
         f_get_alt_id, -- errors here
         ''), 
         nvl2(p.first_name, initcap(p.first_name), ''),
         nvl2(p.last_name, initcap(p.last_name), '')

    into lv_member_name,
         lv_member_id,
         lv_member_first_name,
         lv_member_last_name
    From dbo.person p
   where p.person_id = ln_person_id;
... more code 
END p_Verification;
/

Answer 1

更好的做法是以多种方式创建一个在标题和正文中列出过程和函数的包。

CREATE OR REPLACE PACKAGE CLIENT IS
PROCEDURE p_Verification(pin_Member_ID   IN dbo.member.member_id%Type,
                         pin_Person_ID   IN dbo.person.person_id%type,
                         pin_user_id     IN NUMBER default null,
                         pioc_ref_cursor IN OUT dbo.pkg_benefit_q.ref_cursor);
Procedure p_get_other_deductions(...
FUNCTION f_get_alt_id(alt_id_in IN NUMBER:= NULL)  RETURN Varchar2;
END CLIENT;
CREATE OR REPLACE PACKAGE BODY CLIENT IS

END CLIENT;

注意我是如何添加一个null参数的，所以如果您需要重新设计函数，您可以在不使任何依赖对象无效的情况下执行此操作。

Answer 2

我在声明部分添加了alt_id varchar(10)

将上面的select查询恢复为简单地返回

     dbo.pkg_benefit_s.f_get_value_description('SUFFIX',
                                               p.suffix,
                                               'Y',
                                               'N'),
     p.alt_identifier,         
     nvl2(p.first_name, initcap(p.first_name), ''),
     nvl2(p.last_name, initcap(p.last_name), '')

into lv_member_name,
     lv_member_id,
     lv_member_first_name,
     lv_member_last_name

..。其他一切都是same...until

我添加了另一个部分来设置lv_member_id

BEGIN

   Select p.alt_identifier
   into alt_id
   from dbo.person p
   where P.PERSON_ID = pin_Person_id;
   if alt_id is not null
   then
   lv_member_id := alt_id;
   else
   lv_member_id := f_get_alt_id;
   end if;   

   END;

..。过程的其余部分和结束；--它现在编译