pl_sql functiion questions

I have a function to process numbers in Table X and return a decimal between 0 and 1.

FUNCTION CALC_FACTOR
  ( p_company    IN    VARCHAR2,
    p_media      IN    VARCHAR2,
    p_order_Date    IN    DATE )
 
RETURN  NUMBER
IS

FR  NUMBER(5,3);

BEGIN

FR = (A-B-C) / (E-F-G)

END;

1.  I want FR to return the result if it falls between 0 and 1.

If FR is negative I want to set FR = 1.
If (E-F-G) is 0 (divisor is 0) I want to set FR =1.

For negative number i do this
IF (FR < 0)  THEN
  FR = 0;
END IF:

For divisor of 0 i raise an exception
EXCEPTION
WHEN ZERO DIVIDE
  FR := 1;
 
is this correct and efficient?

2. How can i get the function to return 0.123 instead of .123.

3. Should "p_order_date" parameter be same as table column type or you assign it to VARCHAR2 and convert it to DATE.


4.
procedure P1
begin
 For x in (select * from orders)
LOOP
UPDATE  orders
  SET order_qty = MAX(FLOOR(order_qty*FR(x.company,x.media,v_order_Date),1)
END LOOP;
end;

I am getting an error on the MAX

                                *
ERROR at line 1:
ORA-00934: group function is not allowed here

Is there a way around this?


5. The FR function does a select on same table  "orders" to determine some factor. I get this error:

when i have

Procedure P1
begin
update orders
   set order_qty=order_Qty*FR(x.company,x.media,v_order_date)
end
   
Error -4091: ORA-04091: table ORDERS is mutating, trigger/function
may not see it

If i do this it works.


procedure P1
begin
SELECT FR(x.comany,x.media,v_order_Date) into V_FR from dual;
Upate orders
   set order_Qty = order_qty*v_fr;
end;  
   
any idea why.


My procedure P1 which calls the function does select records from same table.
what it does is that it select a group of records by company,media and then call the function which also does compute the factor and then P1 updates the QTY of the records by the factor.

Start Free Trial