Show Function in PL/SQL

Question

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;
/

What exactly is sal_afis?

Jacob
– Jacob

2020-05-12 10:25:02 +00:00
Commented May 12, 2020 at 10:25 — Jacob
– Jacob, Commented May 12, 2020 at 10:25
Shows the total salaries for Boston

Cristi
– Cristi

2020-05-12 10:37:00 +00:00
Commented May 12, 2020 at 10:37 — Cristi
– Cristi, Commented May 12, 2020 at 10:37

Littlefoot · Accepted Answer · 2020-05-12 10:31:27Z

2

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>

answered May 12, 2020 at 10:31

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Show Function in PL/SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related