Блог

Я совершенно новичок в хранимых процедурах. У меня есть запрос, и я хочу использовать его для веб-службы. Я собираюсь использовать Nhibernate. Я попробовал пару процедур и работал хорошо. Но это вызывает у меня проблемы. Я попытался скомпилировать хранимую процедуру, но выдал ошибки.

 create or replace
PROCEDURE GET_RSM_LIFE_AGENT_DUES (p_recordset OUT SYS_REFCURSOR, :Year IN number, :Month IN number, :Branch IN number, :Agency IN number) AS
BEGIN
OPEN p_recordset for

select     :Year  as required_year, :Month  as required_month, 
           case when pmagt = 0 then D.branch_code else E.branch_code end as branch_code,
           case when pmagt = 0 then D.branch_name else E.branch_name end as branch_name,
           case when pmagt = 0 then D.region else E.region end as region,
           pmagt as agency,
           status || ' ' || int || ' ' || name as agent_name,
           pmpol as policy_no,
           pmcom as commence_date,
           pmtrm as term,
           pmmod as policy_mode,

            case WHEN pmtbl not in (51,22,74,75,76) and ((:Year - SUBSTR (pmcom,1,4)) * 12   to_number(:Month) - SUBSTR (pmcom,-4,2)) < 12 then 'FYR'
            WHEN pmtbl not in (51,22,74,75,76) and ((:Year - SUBSTR (pmcom,1,4)) * 12   to_number(:Month) - SUBSTR (pmcom,-4,2)) >= 12  then 'Renewal' else '' end as premium_type,

           case when llprm is not null and pmprm < llprm then llprm else pmprm end as due_Premium,
           case when llprm is not null then llprm else 0 end as due_paid_premium            

    FROM   lphs.premast A left outer join lclm.ledger B on (A.pmpol = B.llpol) and (to_number(:Year || :Month) = lldue)
           left outer join agent.agent C on (A.pmagt = C.agency)
           left outer join BAU.SLIC_BRANCH_LIST D on (A.pmobr = D.csp_code)
           left outer join BAU.SLIC_BRANCH_LIST E on (C.branch = E.csp_code)

    WHERE   add_months(to_date(PMCOM,'YYYYMMDD'),PMTRM*12) >= add_months( to_date(:Year || :Month || 01 ,'YYYYMMDD'),1) 
            and to_date(pmcom,'yyyymmdd') < to_date(:Year || :Month || 01 ,'YYYYMMDD') 
            and pmmod <> 5
            and stid in ('Ag' , 'ME', 'Or')
            and case when to_date(pmcom,'yyyymmdd') >= to_date(:Year || :Month || 01 ,'YYYYMMDD') then 'N' 
                when pmmod = 4 then 'Y'
                when pmmod = 3 and remainder ( abs( to_number ( substr (pmcom, 5,2) )  - to_number ( :Month ) )   , 3 ) =0 then 'Y' 
                when pmmod = 2 and remainder ( abs( to_number ( substr (pmcom, 5,2) )  - to_number ( :Month ) )   , 6 ) =0 then 'Y' 
                when pmmod = 1 and remainder ( abs( to_number ( substr (pmcom, 5,2) )  - to_number ( :Month ) )   , 12 ) =0 then 'Y' else 'N' end = 'Y'

           and case when pmagt = 0 then D.branch_code else E.branch_code end = :Branch      
          and pmagt = :Agency    

END GET_RSM_LIFE_AGENT_DUES;
  

Ошибка…..

 Error(2,67): PLS-00049: bad bind variable 'YEAR'
Error(2,67): PLS-00103: Encountered the symbol "" when expecting one of the following:     <an identifier> <a double-quoted delimited-identifier>    current delete exists prior 
Error(2,84): PLS-00049: bad bind variable 'MONTH'
  

Удалите двоеточия из процедуры. Это параметры процедуры, поэтому используйте их как таковые. Передайте их значения как, например

 declare
  l_out sys_refcursor;
begin
  GET_RSM_LIFE_AGENT_DUES (l_out, :Year, :Month, :Branch, :Agency);
end;