CODEWITHSUNDEEP
oracle-databaseplsql
user2919688
user2919688

Reputation: 5

PL/SQL command keeps running

I have the following PL/SQL function:

CREATE OR REPLACE FUNCTION GETOVERUREN(v_user_id IN NUMBER, v_jaar IN NUMBER)
RETURN NUMBER
AS
    v_resultaat number := 0;
    v_min_uren_id number := 0;
    v_max_uren_id number := 0;
BEGIN
    SELECT MIN(UREN_ID) INTO v_min_uren_id FROM UREN WHERE JAAR_NR = v_jaar AND UREN_ID IN (SELECT UREN_ID FROM GEBRUIKER_UREN g WHERE g.USER_ID = v_user_id);
    SELECT MAX(UREN_ID) INTO v_max_uren_id FROM UREN WHERE JAAR_NR = v_jaar AND UREN_ID IN (SELECT UREN_ID FROM GEBRUIKER_UREN g WHERE g.USER_ID = v_user_id);

    DECLARE
        v_subtotaal number := 0;
    BEGIN
    v_max_uren_id := v_max_uren_id +1;
    WHILE v_min_uren_id < v_max_uren_id LOOP
        SELECT SUM(OMAANDAG+ODINSDAG +OWOENSDAG +ODONDERDAG +OVRIJDAG +OZATERDAG +OZONDAG) INTO v_subtotaal FROM UREN WHERE UREN_ID = v_min_uren_id;

                    ----------------------------------------------
                    FIXED: v_min_uren_id := v_min_uren_id +1;(FORGOT TO + THE LOOP ITSELF)
                    ----------------------------------------------

                    v_resultaat := v_resultaat + v_subtotaal;
    END LOOP;
RETURN v_resultaat;
END;
END;

The following sql command should give the following outcome:
SELECT GETOVERUREN(1,2013) FROM UREN; WHERE 1 is the userid and 2013 is the year

GETOVERUREN(1,2013)
-------------------
          10.25

But instead it gives:

GETOVERUREN(1,2013)
-------------------
          10.25 
          10.25 
          10.25 
          10.25 
          10.25 
          10.25 
          10.25 
          10.25 
          10.25 
          10.25
          10.25 
          10.25 
          10.25 
          10.25 
          10.25 
          10.25 
          10.25 
          10.25 
          10.25 
          10.25 
          10.25 
          10.25 
          10.25
          10.25 
          10.25 
          10.25 
          10.25 
          10.25 
          10.25 
          10.25 
          10.25 
          10.25 
          10.25 
          10.25 
          10.25 
          10.25 
          10.25 
          10.25 
          10.25 
          10.25 
          10.25 
          10.25 
          10.25 
          10.25 
          10.25 
          10.25 
          10.25 
          10.25 
          10.25 
          10.25 
          10.25
          10.25 
          10.25 
          10.25 
          10.25 
          10.25 
          10.25 
          10.25 
          10.25 
          10.25 
          10.25 
 Up to 157 times (wich is the total of rows i have in my table.)

/* When I use the function SQLDeveloper stays running and wont stop running the function. I have waited for half an hour stil no results. What did i do wrong? */

The problem of running is fixed, now i get to much results back.

I've made myself an example function looking like this:

create or replace FUNCTION TESTING(v_user_id IN NUMBER)
RETURN NUMBER
AS
    v_resultaat number := 0;
BEGIN
    SELECT IS_ADMIN INTO v_resultaat FROM GEBRUIKER WHERE USER_ID = v_user_id;
  return v_resultaat;
END TESTING;

This function does return a value, but its returning the value X the number of rows that are in GEBRUIKER so again, something is wrong but i cant seem to figure out what it is.

Upvotes: 0

Views: 210

Answers (1)

Przemyslaw Kruglej
Przemyslaw Kruglej

Reputation: 8123

You have to move the incrementation of the v_min_uren_id variable inside the loop (and change it from max to min) - otherwise, you get an inifite loop. Try this:

CREATE OR REPLACE FUNCTION GETOVERUREN(v_user_id IN NUMBER, v_jaar IN NUMBER)
RETURN NUMBER
AS
    v_resultaat number := 0;
    v_min_uren_id number := 0;
    v_max_uren_id number := 0;
BEGIN
    SELECT MIN(UREN_ID) INTO v_min_uren_id FROM UREN WHERE JAAR_NR = v_jaar AND UREN_ID IN (SELECT UREN_ID FROM GEBRUIKER_UREN g WHERE g.USER_ID = v_user_id);
    SELECT MAX(UREN_ID) INTO v_max_uren_id FROM UREN WHERE JAAR_NR = v_jaar AND UREN_ID IN (SELECT UREN_ID FROM GEBRUIKER_UREN g WHERE g.USER_ID = v_user_id);

    DECLARE
        v_subtotaal number := 0;
    BEGIN
    WHILE v_min_uren_id < v_max_uren_id LOOP
        v_min_uren_id := v_min_uren_id +1;
        SELECT SUM(OMAANDAG+ODINSDAG +OWOENSDAG +ODONDERDAG +OVRIJDAG +OZATERDAG +OZONDAG) INTO v_subtotaal FROM UREN WHERE UREN_ID = v_min_uren_id;

        v_resultaat := v_resultaat + v_subtotaal;
    END LOOP;
RETURN v_resultaat;
END;
END;

Upvotes: 3

Related Questions