Home » SQL & PL/SQL » SQL & PL/SQL » how to stop total execution in procedure via condition
how to stop total execution in procedure via condition [message #436137] Mon, 21 December 2009 15:33 Go to next message
pyscho
Messages: 134
Registered: December 2009
Senior Member
I've got a following procedure below that does either an insert, update or delete after some variable processing. However, I need to add the following logic in somewhere like;

if (select cad_yn from pf_clients != 'Y') then don't do anything

how can i stop the execution, i dont want it to execute anything insdide before or after the begin block if the cad_yn != 'Y'

    PROCEDURE PROCESS_ROW( P_Event                    IN VARCHAR2,
                           P_Old_Record_Key           IN VARCHAR2, 
                           P_Bargain_Id               IN VARCHAR2,
                           P_Account_Letter           IN VARCHAR2,
                           P_Fund                     IN VARCHAR2,
                           P_Client_Rec_No            IN VARCHAR2,
                           P_Payment_Currency         IN VARCHAR2,
                           P_Contract_Ref             IN VARCHAR2) IS
    
    -- PF_CASH_BAL_BARGAINS field variables
    l_cash_bal_bgn_id      PF_CASH_BAL_BARGAINS.CASH_BAL_BGN_ID%TYPE;
    l_lty_id               PF_CASH_BAL_BARGAINS.LTY_ID%TYPE; 
    l_client_id            PF_CASH_BAL_BARGAINS.CLIENT_ID%TYPE;
    l_ledger_ccy           PF_CASH_BAL_BARGAINS.LEDGER_CCY%TYPE;
    l_bargain_id           PF_CASH_BAL_BARGAINS.BARGAIN_ID%TYPE;
    l_plan_id              PF_CASH_BAL_BARGAINS.PLAN_ID%TYPE;
    
    -- Variables used in building the core PF_CASH_TXNS fields above
    l_temp_account      VARCHAR2(1);
    
    BEGIN
    
    -- CASH_BAL_BGN_ID
    l_cash_bal_bgn_id := P_Bargain_Id;
    
    -- LTY_ID
    l_temp_account := P_Account_Letter;
    
    SELECT  LTY_ID 
    INTO    l_lty_id 
    FROM    INTEGRATION.MAPPING 
    WHERE   ACCOUNT = l_temp_account AND (fund = P_Fund OR fund IS NULL OR fund LIKE ' %');
    
    l_lty_id := RTRIM(l_lty_id);
    
    -- CLIENT_ID
    l_client_id := P_Client_Rec_No;
    
    -- LEDGER_CCY
    l_ledger_ccy := RTRIM(P_Payment_Currency); -- check with chris if can query pf_clients for val ccy if null ****
    
    -- BARGAIN_ID
    l_bargain_id := P_Contract_Ref;
    
    -- PLAN_ID
    SELECT  PLAN_ID 
    INTO    l_plan_id 
    FROM    INTEGRATION.MAPPING 
    WHERE   ACCOUNT = l_temp_account AND (fund = P_Fund OR fund IS NULL OR fund LIKE ' %');
        
    l_plan_id := LTRIM(RTRIM(l_plan_id));
    
    IF (l_plan_id = '') THEN l_plan_id := NULL;
    END IF;
    
    
    IF (P_Event = 'insert') THEN INSERT_ROW( l_cash_bal_bgn_id,
                                             l_lty_id,
                                             l_client_id,
                                             l_ledger_ccy,
                                             l_bargain_id,
                                             l_plan_id);
    
    ELSIF (P_Event = 'update') THEN UPDATE_ROW( P_Old_Record_Key,
                                                l_cash_bal_bgn_id,
                                                l_lty_id,
                                                l_client_id,
                                                l_ledger_ccy,
                                                l_bargain_id,
                                                l_plan_id);
                                                
    ELSIF (P_Event = 'delete') THEN DELETE_ROW( P_Old_Record_Key);
    END IF;
    
    END PROCESS_ROW;
Re: how to stop total execution in procedure via condition [message #436138 is a reply to message #436137] Mon, 21 December 2009 16:11 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Does pf_client have always single value?


You can store the value into a variable

select nvl(flag,'N') into v_flag from table1;


Begin
if v_flag ='Y' then
 <statement>

else 
null;
end;




Re: how to stop total execution in procedure via condition [message #436140 is a reply to message #436138] Mon, 21 December 2009 16:49 Go to previous messageGo to next message
pyscho
Messages: 134
Registered: December 2009
Senior Member
Thanks but what about the stuff before the begin block?

[Updated on: Mon, 21 December 2009 16:49]

Report message to a moderator

Re: how to stop total execution in procedure via condition [message #436142 is a reply to message #436140] Mon, 21 December 2009 16:57 Go to previous messageGo to next message
pablolee
Messages: 2813
Registered: May 2007
Location: Scotland
Senior Member
inconsequential
Re: how to stop total execution in procedure via condition [message #436164 is a reply to message #436137] Mon, 21 December 2009 23:12 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
First of all, have you tried anything before posting your question here?
Second, scenario is not clearly described.
Third, I can't understand what do you want to achieve.
Fourth, I didn't found anything like "cad_yn" or "pf_clients", using which we can get some reference.

Whatever I have understood, I am giving the following solution.
select cad_yn into l_variable from pl_clients where...

if (l_variable != 'Y') then
   return;
elsif (...)
   insert...;
elsif (...)
   update...;
else
   delete...;
end if;


One more thing,
./fa/1707/0/ Just check your spellings before clicking on submit.

regards,
Delna
Re: how to stop total execution in procedure via condition [message #436182 is a reply to message #436164] Tue, 22 December 2009 01:02 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
delna.sexy wrote on Tue, 22 December 2009 06:12
Fourth, I didn't found anything like "cad_yn" or "pf_clients", using which we can get some reference.
[...]
Just check your spellings before clicking on submit.

Pot & kettle?

[Updated on: Tue, 22 December 2009 01:03]

Report message to a moderator

Re: how to stop total execution in procedure via condition [message #436195 is a reply to message #436137] Tue, 22 December 2009 01:30 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
IF (l_plan_id = '') THEN l_plan_id := NULL;

This
1/ Don't work as for Oracle an empty string IS null, so "X = ''" always returns UNKNOWN and not TRUE
2/ Is useless for the same reason
Conclusion: you are lucky.

Regards
Michel
Re: how to stop total execution in procedure via condition [message #436214 is a reply to message #436140] Tue, 22 December 2009 03:03 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
Thanks but what about the stuff before the begin block?


Surely the place to put this check is at the start of the whole operation, before you've done any work.

Assuming that you've written your code correctly, you should be able to just perform a Rollback , or a rollback to savepoint to undo any work done so far, and then raise an exception to avoid doing any further work.
You'll need to catch and handle the exception too.
Previous Topic: Difference between explicit cursors and ref cursor
Next Topic: Multiple Partition in a Single Query
Goto Forum:
  


Current Time: Sun Sep 25 23:12:46 CDT 2016

Total time taken to generate the page: 0.07276 seconds