#1
  1. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Dec 2004
    Posts
    2,985
    Rep Power
    375

    Need OPINIONS - using functions inside of a procedure - necessary or not?


    ok This time i just want an opinion on whether i should have functions that do one thing and then call them inside a procedure or just do whatever that func is doing inside a procedure so something like:

    sql Code:
     
    CREATE OR REPLACE PROCEDURE changeQty(ID IN NUMBER)
    AS
    	BEGIN
    	SELECT checkOrderId(ID) INTO checkId FROM dual;
    	IF(checkId IS NULL) THEN
    		errorStatus := 'orderId is not valid';
    		raise no_data_found;
    	END IF;


    and then have a function called checkOrderId
    sql Code:
     
    CREATE OR REPLACE FUNCTION checkOrderId(Id IN NUMBER) RETURN NUMBER
     
    AS
    	oID NUMBER;
    BEGIN
    	SELECT orderId INTO oID FROM orders WHERE orderId = Id;
    	RETURN oID; 
     
    EXCEPTION
    	WHEN no_data_found THEN
    		oID := NULL;
    		RETURN oID;		
    END;
    /


    the problem is that although i could do it like this, the procedure will still look messy and i could just easily replace the function call select statement with the ACTUAL checking statement.
    Last edited by paulh1983; August 27th, 2007 at 01:23 PM.
  2. #2
  3. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    848
    Rep Power
    387

    Cool



    You could allways move the function inside the procedure like this:
    Code:
    CREATE OR REPLACE PROCEDURE changeQty (ID IN number)
    AS
    FUNCTION checkOrderId(Id IN number) 
      RETURN Number 
    IS
    DECLARE oID number;
    BEGIN
      SELECT orderId INTO oID
        FROM orders WHERE orderId = Id;
      RETURN oID;
    EXCEPTION
      WHEN no_data_found THEN
        oID := NULL;
        RETURN oID;
    END;
    
    BEGIN
      IF(checkOrderId(ID) IS NULL) THEN
        RAISE_APPLICATION_ERROR(-20001,'orderId is not valid');
      END IF;
    END;  
    /
    Or defining the function just call it like this:

    Code:
    CREATE OR REPLACE PROCEDURE changeQty (ID IN number)
    AS
    BEGIN
      IF (checkOrderId(ID) IS NULL) THEN
        RAISE_APPLICATION_ERROR(-20001,'orderId is not valid');
      END IF;
      -- Process order Here
    END;  
    /


  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Dec 2004
    Posts
    2,985
    Rep Power
    375
    i'll go with the second one since i have other procedures which will also use this function.


    CHEERS THANK YOU

IMN logo majestic logo threadwatch logo seochat tools logo