Thursday, December 21, 2017

Oracle  Database Function
( Create & Call )


In the name of Allah, Most Gracious, Most Merciful
Praise be to Allah, blessing and peace be upon our prophet Mohammed, his family and his companions. After that,


      This is just an example of  Oracle Database Function to show you how can we create and call it as an individual database function not related to any PL/SQL package. Later on, In shaa Allah, we will declare and implement it with it's related database functions in a new package called a trans_pkg. 
Now, let's start the current task definition.


Task Definition:   

1- Get the maximum quantity for each item stored in a specific  storage area.
2- Update its maximum quantity in  the transaction table. 


Task Solution:

     Create a database  function to returns  the maximum quantity  stored of  a  specific item. Please, follow the sample code and read the comments  for each line:


 
CREATE OR REPLACE FUNCTION  UDATE_QTY   
  ( p_store_id  NUMBER  ,   p_item_serial   NUMBER   ) 
    RETURN NUMBER IS
    v_max    NUMBER := 0;
  CURSOR CR_UPDATE  IS
           SELECT  NVL( MAX( balance_qty ) , 0  )
            FROM   wh_t_items
            WHERE  store_id    =   p_store_id  
            AND    item_serial = p_item_serial  ;       
BEGIN
    OPEN   CR_UPDATE  ;         -- Open the cursor
    FETCH   CR_UPDATE   INTO  v_max  ;         -- Fetch the cursor
    IF CR_UPDATE%NOTFOUND  THEN   NULL;
ELSE
   LOOP
        UPDATE  item_t_trans
        SET    balance_qty            =  v_max    
        WHERE  item_t_trans.store_id  =  p_store_id 
        AND    item_serial            =  p_item_serial;    
  END LOOP ;
-- Save the changes out of the loop but inside the cursor.
COMMIT; 
END IF;
   CLOSE CR_UPDATE  ; 
      RETURN v_max    ;
 EXCEPTION
WHEN OTHERS THEN
   RAISE_APPLICATION_ERROR(-20001,'An error was encountered - 
      '||SQLCODE||' -ERROR- '||SQLERRM);
 
END  UDATE_QTY   ; 
/


Note:

    RAISE_APPLICATION_ERROR  is actually a procedure that lets you define both error number from  -20,000  up to -20,999 and its customized message.


Oracle Database Function Call

Let's  call the stored database function from Oracle  form using 
e.g. WHEN-BUTTON-PRESSED Trigger or any appropriate trigger to execute the database function code.

 
DECLARE
  v_max  NUMBER := 0;
BEGIN
 -- Call  the database function to return the changes made.
 v_max := Update_Qty   (:block_name.store_id , :block_name.item_serial);
 -- Assign the returned function 's value to the form item.
:block_name.max_balance :=  v_max;
END;



Now, let's embed this function within the package.
Learn more about:



Hope this helps...

My success only comes from Allah, pls. note your comments and suggestions are great help for me in progress thanks in advance.