CODEWITHSUNDEEP
oracle-databasefunctionplsql
Benat Garrido de Ordorika
Benat Garrido de Ordorika

Reputation: 5

Why does this function bring null values?

I have made a function in a view to calculate a league. The problem is that the function gives me the next error when I try to see the view. ORA-06503: PL/SQL: Function returned without value ORA-06512: at "project.league", line 24 06503. 00000 - "PL/SQL: Function returned without value"

This is my function code.

create or replace FUNCTION leaguepts(Team preleague.Team1%TYPE)
RETURN NUMBER
AS
cumulpts NUMBER(3):=1;
cumulpts2 NUMBER(3):=2;
cumulpts3 NUMBER(3):=0;
VT matches.Team1%TYPE;
texist EXCEPTION;
BEGIN
SELECT COUNT(Team1) INTO VT FROM matches WHERE Team1=Team; 
IF (VT>0) THEN
    SELECT SUM(PTS) INTO cumulpts FROM preleague WHERE Team2=Team;
    SELECT SUM(PTS2) INTO cumulpts2 FROM preleague WHERE Team2=Team;
    cumulpts3:=(cumulpts+cumulpts2);
ELSE
    RAISE texist;
END IF;
RETURN cumulpts3;
EXCEPTION
    WHEN texist THEN
        DBMS_OUTPUT.PUT_LINE ('The team does not exist');

END;

And this is my view code

CREATE OR REPLACE FORCE EDITIONABLE VIEW "project"."league" ("Team", "PTS")   AS 
  SELECT Name, Leaguepts(team_cod) FROM Teams;

I wanted to do a function to know how many point each team has to see the league view like a clasification. I´ll be grateful with everyone who tries to help me.

Upvotes: 0

Views: 89

Answers (2)

Ikacho
Ikacho

Reputation: 75

@Boneist - Good point.
It’s not only removing unnecessary variable but (more important) unnecessary query. Context Switch or switching between SQL and PL/SQL engines impacts on performance.
I’d go even further by removing two more variables and one more SQL query:

    CREATE OR REPLACE FUNCTION leaguepts (team preleague.team1%TYPE) RETURN NUMBER AS
      cumulpts  NUMBER(3) := 0;
      texist EXCEPTION;
    BEGIN

    SELECT SUM(NVL(pts, 0) + NVL(pts2, 0))
    INTO cumulpts
    FROM preleague
    WHERE  team2 = team;

    IF cumulpts > 0 THEN
      RETURN cumulpts;
    ELSE
      RAISE texist;
    END IF;

    EXCEPTION
  WHEN texist THEN
    dbms_output.put_line ('The team "'||team||'" does not exist');
    return null;

END leaguepts;
/

Upvotes: 1

Boneist
Boneist

Reputation: 23578

I would rewrite your function as:

CREATE OR REPLACE FUNCTION leaguepts (team preleague.team1%TYPE) RETURN NUMBER AS
  cumulpts  NUMBER(3) := 0;
  cumulpts2 NUMBER(3) := 0;
  vt        matches.team1%TYPE;
  texist EXCEPTION;
BEGIN
  SELECT COUNT(team1)
  INTO   vt
  FROM   matches
  WHERE  team1 = team;

  IF (vt > 0)
  THEN
    SELECT SUM(pts), SUM(pts2)
    INTO   cumulpts, cumulpts2
    FROM   preleague
    WHERE  team2 = team;

  ELSE
    RAISE texist;
  END IF;

  RETURN nvl(cumulpts, 0) + nvl(cumulpts2, 0);

EXCEPTION
  WHEN texist THEN
    dbms_output.put_line ('The team "'||team||'" does not exist');
    return null;

END leaguepts;
/

And here's a demo of it working

I have modified your function to remove the unnecessary cumulpts3 variable, and I've also updated cumulpts + cumulpts2 to use NVL to return a 0 if any of the values are 0 (e.g. in my example, team D exists, but has no rows in preleague, so 0 is returned, vs team B which doesn't have a team1 value in the matches table, so null is returned.

You may wish to change these values accordingly (maybe you want both to be null? In which case, remove the NVLs, or maybe you want to return null for the team present and -1 for a missing team, etc)

Upvotes: 0

Related Questions