Simple Functions

CREATE OR REPLACE FUNCTION get_result(a IN NUMBER,

                                      b IN varchar2,

                                      c IN NUMBER) RETURN NUMBER IS

  reslt NUMBER;

BEGIN

  if b = '+' then

    reslt := a + c;

 

  elsif b = '-' then

    reslt := a - c;

 

  elsif b = '*' then

    reslt := a * c;

 

  elsif b = '/' then

    reslt := a / c;

  end if;

  RETURN(reslt);

END;

--********************

--   To get output  --

--********************

set serveroutput on

declare

v_result number;

begin

v_result := get_result1(1,'+',2);

 DBMS_OUTPUT.PUT_LINE(v_result);

end;

select get_result1(1,'*',2) from dual;

CREATE OR REPLACE FUNCTION dept_salary(dnum NUMBER) RETURN NUMBER IS

  CURSOR emp_cursor IS

    SELECT salary, commission_pct

      FROM employees

     WHERE department_id = dnum;

  total_wages NUMBER(11, 2) := 0;

  counter     NUMBER(10) := 1;

BEGIN

  FOR emp_record IN emp_cursor LOOP

    emp_record.commission_pct := NVL(emp_record.commission_pct, 0);

    total_wages               := total_wages + emp_record.salary +

                                 emp_record.commission_pct;

    DBMS_OUTPUT.PUT_LINE('Loop number = ' || counter || '; Wages = ' ||

                         TO_CHAR(total_wages)); /* Debug line */

    counter := counter + 1; /* Increment debug counter */

  END LOOP;

  /* Debug line */

  DBMS_OUTPUT.PUT_LINE('Total wages = ' || TO_CHAR(total_wages));

  RETURN total_wages;

END dept_salary;