CODEWITHSUNDEEP
oracle-databasefunctionplsql
Cristi
Cristi

Reputation: 5

Show Function in PL/SQL

Based on EMP and DEPT tables:https://livesql.oracle.com/apex/livesql/file/content_O5AEB2HE08PYEPTGCFLZU9YCV.html

I need to create a function that shows total salaries from a city(My choice is BOSTON).Problem is I get an error.The function must be called from an anonimous block.

SET SERVEROUTPUT ON;

CREATE OR REPLACE FUNCTION show_sal (local dept.loc%TYPE)RETURN NUMBER AS
 vval NUMBER;
 BEGIN
  SELECT SUM(SAL)INTO vval FROM emp INNER JOIN dept ON dept.deptno=emp.deptno
  HAVING UPPER(dept.loc)=UPPER(local);
  RETURN vval;
  END;
/


BEGIN
DBMS_OUTPUT.PUT_LINE('Total sum of salaries ='||sal_afis('BOSTON'));
END;
/

Upvotes: 0

Views: 76

Answers (1)

Littlefoot
Littlefoot

Reputation: 143103

You're calling function that doesn't exist (or, if it exists, that's not the one whose code you posted).

If there's at least one employee in BOSTON's department, you'd still get an error because you misused HAVING - should be WHERE.

So:

SQL> set serveroutput on;
SQL> create or replace function show_sal (local dept.loc%type)
  2    return number
  3  as
  4    vval number;
  5  begin
  6    select sum(emp.sal)
  7      into vval
  8      from emp inner join dept on dept.deptno = emp.deptno
  9      where upper(dept.loc) = upper(local);
 10    return vval;
 11  end;
 12  /

Function created.

SQL> begin
  2    dbms_output.put_line('Total sum of salaries = ' || show_sal('DALLAS'));
  3  end;
  4  /
Total sum of salaries = 11075

PL/SQL procedure successfully completed.

SQL>

Upvotes: 2

Related Questions