How to perform arithmetic calculation between rows in oracle Pl/Sql

Question

Table A

Subject	Marks
A	100
B	200
C	400
D	500

Table B

Subject	Formula
A	A/B
B	B/D
C	D/A-B
D	A+B/C-D

Output

Subject	Result
A	0.5
B	0.4
C	5
D	3

Answer 1

Some results you posted (C and D) are invalid; first you have to multiply/divide, then add/subtract. For your kind of results, you'll need to use brackets.

Anyway: dynamic SQL should be used. Here's one option:

• fetch all variables from table_a
• loop through all operations in table_b
• replace variables with their values (fetched in the 1st step)
• calculate
• display result

---

SQL> set serveroutput on;
SQL> declare
  2    l_val_a table_a.marks%type;
  3    l_val_b table_a.marks%type;
  4    l_val_c table_a.marks%type;
  5    l_val_d table_a.marks%type;
  6    --
  7    l_str varchar2(200);
  8    l_result number;
  9  begin
 10    select max(case when subject = 'A' then marks end),
 11           max(case when subject = 'B' then marks end),
 12           max(case when subject = 'C' then marks end),
 13           max(case when subject = 'D' then marks end)
 14      into l_val_a, l_val_b, l_val_c, l_val_d
 15      from table_a;
 16
 17    for cur_r in (select subject, formula
 18                  from table_b
 19                 )
 20    loop
 21      l_str := replace(replace(replace(replace(
 22                 cur_r.formula, 'A', l_val_a),
 23                                'B', l_val_b),
 24                                'C', l_val_c),
 25                                'D', l_val_d);
 26      l_str := 'select ' || l_str || ' from dual';
 27      execute immediate l_str into l_result;
 28      dbms_output.put_line(cur_r.subject ||': '|| l_str ||' = '|| l_result);
 29    end loop;
 30  end;
 31  /

Output:

A: select 100/200 from dual = .5
B: select 200/500 from dual = .4
C: select 500/100+200 from dual = 205
D: select 100+200/400-500 from dual = -399.5

PL/SQL procedure successfully completed.

SQL>

Answer 2

Let's build a parser in SQL....

1. Implement the shunting yard algorithm as a recursive sub-query; and then
2. Take the reverse polish output from the shunting yard and use it to calculate the value.

WITH shunting_yard (subject, formula, stack, output) AS (
  SELECT subject,
         formula,
         CAST(NULL AS VARCHAR2(4000)),
         CAST(NULL AS VARCHAR2(4000))
  FROM   B
UNION ALL
  SELECT subject,
         CASE
         WHEN SUBSTR(formula, 1, 1) NOT IN ('+', '-', '/', '*')
         THEN SUBSTR(formula, 2)
         WHEN stack IS NULL
         OR   DECODE(SUBSTR(stack, -1), '+', 1, '-', 1, '/', '2', '*', '2')
            < DECODE(SUBSTR(formula, 1, 1), '+', 1, '-', 1, '/', '2', '*', '2')
         THEN SUBSTR(formula, 2)
         ELSE formula
         END,
         CASE
         WHEN SUBSTR(formula, 1, 1) NOT IN ('+', '-', '/', '*')
         THEN stack
         WHEN stack IS NULL
         OR   DECODE(SUBSTR(stack, -1), '+', 1, '-', 1, '/', '2', '*', '2')
            < DECODE(SUBSTR(formula, 1, 1), '+', 1, '-', 1, '/', '2', '*', '2')
         THEN SUBSTR(formula, 1, 1) || stack
         ELSE SUBSTR(stack, 2)
         END,
         CASE
         WHEN SUBSTR(formula, 1, 1) NOT IN ('+', '-', '/', '*')
         THEN output || SUBSTR(formula, 1, 1)
         WHEN stack IS NULL
         OR   DECODE(SUBSTR(stack, -1), '+', 1, '-', 1, '/', '2', '*', '2')
            < DECODE(SUBSTR(formula, 1, 1), '+', 1, '-', 1, '/', '2', '*', '2')
         THEN output
         ELSE output || SUBSTR(stack, 1, 1)
         END
  FROM   shunting_yard
)
SEARCH DEPTH FIRST BY subject, formula SET order_id
CYCLE subject, formula, stack, output SET done TO 1 DEFAULT 0,
reverse_polish (subject, formula, stack, value) AS (
  SELECT subject,
         output,
         CAST(NULL AS VARCHAR2(4000)),
         CAST(NULL AS NUMBER)
  FROM   shunting_yard
  WHERE  done = 1
UNION ALL
  SELECT r.subject,
         SUBSTR(r.formula, 2),
         CASE
         WHEN SUBSTR(r.formula, 1, 1) IN ('+', '-', '*', '/')
         THEN '_' || SUBSTR(r.stack, 3)
         ELSE SUBSTR(r.formula, 1, 1) || r.stack
         END,
         CASE SUBSTR(r.formula, 1, 1)
         WHEN '+'
         THEN COALESCE(a2.marks, r.value) + COALESCE(a1.marks, r.value)
         WHEN '-'
         THEN COALESCE(a2.marks, r.value) - COALESCE(a1.marks, r.value)
         WHEN '/'
         THEN COALESCE(a2.marks, r.value) / COALESCE(a1.marks, r.value)
         WHEN '*'
         THEN COALESCE(a2.marks, r.value) * COALESCE(a1.marks, r.value)
         ELSE r.value
         END
  FROM   reverse_polish r
         LEFT OUTER JOIN a a1
         ON SUBSTR(r.stack, 1, 1) = a1.subject
         LEFT OUTER JOIN a a2
         ON SUBSTR(r.stack, 2, 1) = a2.subject
)
SEARCH DEPTH FIRST BY subject, formula SET order_id
CYCLE subject, formula, stack SET done TO 1 DEFAULT 0
SELECT subject, value
FROM   reverse_polish
WHERE  done = 1

Which, for the sample data:

CREATE TABLE A (Subject, Marks) AS
SELECT 'A', 100 FROM DUAL UNION ALL
SELECT 'B', 200 FROM DUAL UNION ALL
SELECT 'C', 400 FROM DUAL UNION ALL
SELECT 'D', 500 FROM DUAL;

CREATE TABLE B (Subject, Formula) AS
SELECT 'A', 'A/B' FROM DUAL UNION ALL
SELECT 'B', 'B/D' FROM DUAL UNION ALL
SELECT 'C', 'D/A-B' FROM DUAL UNION ALL
SELECT 'D', 'A+B/C-D' FROM DUAL;

Outputs:

SUBJECT	VALUE
A	.5
B	.4
C	-195
D	-399.5

fiddle