Autonomous Transactions, Tune SQL Developer, sql default value if no row is returned

SQL Formula (Math Functions)

Rating: 4.74/5. From 19 votes.
Please wait...
Werbung / Advertisements

If you want to calculate a value with a function which is stored in the table (row or column) you can use the follwing code to execute the math formula. Replace placeholders with the values you need.

DECLARE
    v_return NUMBER;
    pi_val VARCHAR2(50) := '2*6*10';
BEGIN
    EXECUTE IMMEDIATE 'select '||pi_val||' from dual' INTO v_return;
    dbms_output.put_line(v_return);
END;

Example Code for a function:

Werbung / Advertisements
Werbung / Advertisements
set serveroutput on
DECLARE
    v_return NUMBER;
    pi_val VARCHAR2(50) := '2*6*10';
    pi_val2 VARCHAR2(50) := 'ROUND(x/12,2)';
    pi_x NUMBER := 12.558;
BEGIN
    -- EXECUTE IMMEDIATE 'select '||pi_val||' from dual' INTO v_return;
    -- dbms_output.put_line(v_return);
    pi_val2 := REPLACE(pi_val2, 'x', TO_CHAR(pi_x, '9G990D00', 'NLS_NUMERIC_CHARACTERS = ''.,'''));
    dbms_output.put_line(pi_val2);
    EXECUTE IMMEDIATE 'select '||pi_val2||' from dual' INTO v_return;
    dbms_output.put_line(v_return);
END;
CREATE OR REPLACE FUNCTION Calc(pi_x NUMBER, pi_formula VARCHAR2) 
RETURN NUMBER IS
    v_return NUMBER;
    BEGIN
        pi_formula := REPLACE(pi_formula, 'x', TO_CHAR(pi_x, '9G990D00', 'NLS_NUMERIC_CHARACTERS = ''.,'''));
        EXECUTE IMMEDIATE 'select '||pi_formula||' from dual' INTO v_return;
        RETURN v_return;
    END;
    
SELECT Calc(12.558, 'ROUND(x/12,2)')
FROM DUAL;

Check also the full SQL Guide with lots of useful examples.

Rating: 4.74/5. From 19 votes.
Please wait...
Werbung / Advertisements