[Solved] PLSQL procedure to add numbers by fetching records from table [closed]

by


Something like this?

set serveroutput on

declare
  l_what varchar2(10);         --> fail or pass
begin
  for cur_r in (select col1, 
                       col2, 
                       col3,   --> to be compared with
                       col1 + col2 result 
               from your_table)
  loop
    l_what := case when cur_r.col1 + cur_r.col2 = cur_r.col3 then 'pass'
                   else 'fail'
              end;

    dbms_output.put_line(cur_r.col1 ||' + '|| cur_r.col2 ||' = ' || cur_r.result ||' -> '|| l_what);
  end loop;
end;
/

[EDIT: a function that sums values in a table]

Based on Scott’s schema, this function will sum SAL and COMM values:

SQL> select empno, ename, sal, comm from emp where comm is not null;

     EMPNO ENAME             SAL       COMM
---------- ---------- ---------- ----------
      7499 ALLEN            1600        300
      7521 WARD             1250        500
      7654 MARTIN           1250       1400
      7844 TURNER           1500          0

SQL> create or replace function f_sum (par_empno in number)
  2    return number
  3  is
  4    retval number;
  5  begin
  6    select sal + comm
  7      into retval
  8      from emp
  9      where empno = par_empno;
 10    return retval;
 11  end;
 12  /

Function created.

Testing:

SQL> select empno, ename, sal, comm, f_sum(empno) result
  2  from emp
  3  where empno = 7499;

     EMPNO ENAME             SAL       COMM     RESULT
---------- ---------- ---------- ---------- ----------
      7499 ALLEN            1600        300       1900

SQL>

10

solved PLSQL procedure to add numbers by fetching records from table [closed]