Home » SQL & PL/SQL » SQL & PL/SQL » Out and conditional control
Out and conditional control [message #206853] Fri, 01 December 2006 14:45 Go to next message
lee337c33
Messages: 8
Registered: November 2006
Location: London
Junior Member
Hey,

I've created the following Function and procedure without any problems, they both compile and update my database successfully. But after thinking that I had finally got the hang of this PL/SQL lark I've come unstuck with the simply conditional control and output - I've fairly familiar with Java, VB etc so I left these out till last because I thought they'd be the easiest part. The aim is simply to display students average mark along with a grade:

FUNCTION

Create or replace function averagemark(
i_STUID lec_stu_mod.STUID%TYPE)
RETURN NUMBER
IS
v_LEC_STU_MOD_avemark lec_stu_mod.avemark%TYPE;
BEGIN
select SUM (MODULEGRADE/3)
into v_LEC_STU_MOD_avemark
from lec_stu_mod

WHERE STUID = i_STUID;

IF v_LEC_STU_MOD_avemark IS NULL THEN
v_LEC_STU_MOD_avemark :=0;
END IF;

RETURN v_LEC_STU_MOD_avemark;

END;
/

PROCEDURE

Create or Replace procedure totalmark
AS
CURSOR c_lec_stu_mod
IS
SELECT stuid
FROM lec_stu_mod;
v_stuid lec_stu_mod.stuid%TYPE;
v_modulegrade lec_stu_mod.modulegrade%TYPE;

BEGIN

OPEN c_lec_stu_mod;
LOOP
FETCH c_lec_stu_mod INTO v_stuid;

EXIT WHEN c_lec_stu_mod%NOTFOUND;

v_modulegrade := averagemark(v_stuid);

UPDATE lec_stu_mod
SET avemark = v_modulegrade
WHERE stuid = v_stuid;

END LOOP;
CLOSE c_lec_stu_mod;
END;
/

I've tried to alter the function as follows:

FUNCTION

Create or replace function averagemark(
i_STUID lec_stu_mod.STUID%TYPE)
RETURN NUMBER
IS
v_LEC_STU_MOD_avemark lec_stu_mod.avemark%TYPE;
BEGIN
select SUM (MODULEGRADE/3)
into v_LEC_STU_MOD_avemark

from lec_stu_mod
WHERE STUID = i_STUID;

IF v_LEC_STU_MOD_avemark >= 70
THEN DBMS_OUTPUT.PUT_LINE(' Distinction ')
ELSIF v_LEC_STU_MOD_avemark >= 50 AND <= 69
THEN DBMS_OUTPUT.PUT_LINE(' Pass ')
ELSIF v_Lec_STU_MOD_avemark <50
THEN DBMS_OUTPUT.PUT_LINE(' Fail: you need help.')
ELSE v_LEC_STU_MOD_avemark IS NULL --may not need to put IS NULL in there

v_LEC_STU_MOD_avemark :=0;
END IF;

RETURN v_LEC_STU_MOD_avemark;
END;
/

This compiles fine, but the above procedure then doesn't. I wondered if i missing anything obvious.

Any help would be greatly appriciated.
Re: Out and conditional control [message #206858 is a reply to message #206853] Fri, 01 December 2006 15:10 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I don't believe this function compiles successfully; ELSIF which contains (50 and 69) is wrong, the last ELSE should be ELSIF, none of DBMS_OUTPUT statements ends with a semi-colon. Correctly written function looks like this:
CREATE OR REPLACE FUNCTION averagemark (i_stuid lec_stu_mod.stuid%TYPE)
   RETURN NUMBER
IS
   v_lec_stu_mod_avemark   lec_stu_mod.avemark%TYPE;
BEGIN
   SELECT SUM (modulegrade / 3)
     INTO v_lec_stu_mod_avemark
     FROM lec_stu_mod
    WHERE stuid = i_stuid;

   IF v_lec_stu_mod_avemark >= 70
   THEN
      DBMS_OUTPUT.put_line (' Distinction ');
   ELSIF v_lec_stu_mod_avemark >= 50 AND v_lec_stu_mod_avemark <= 69
   THEN
      DBMS_OUTPUT.put_line (' Pass ');
   ELSIF v_lec_stu_mod_avemark < 50
   THEN
      DBMS_OUTPUT.put_line (' Fail: you need help.');
   ELSIF v_lec_stu_mod_avemark IS NULL
   THEN                                 --may not need to put IS NULL in there
      v_lec_stu_mod_avemark := 0;
   END IF;

   RETURN v_lec_stu_mod_avemark;
END;

However, use of DBMS_OUTPUT.PUT_LINE is questionable because this messages will be visible only if you run it through SQL*Plus, but not if this function is called from a procedure which is part of, for example, Form (written by Forms Builder).

That's why you should perhaps consider something else. For example, return a (negative?) numeric value which will be handled differently, possibly using a lookup table which will say
-1 -> Distinction
-2 -> Pass
-3 -> Fail

Also, you don't need to check it for NULL value using IF - use NVL function instead; your first function would look like this:
CREATE OR REPLACE FUNCTION averagemark (i_stuid lec_stu_mod.stuid%TYPE)
   RETURN NUMBER
IS
   v_lec_stu_mod_avemark   lec_stu_mod.avemark%TYPE;
BEGIN
   SELECT nvl(SUM (modulegrade / 3), 0)
     INTO v_lec_stu_mod_avemark
     FROM lec_stu_mod
    WHERE stuid = i_stuid;

   RETURN v_lec_stu_mod_avemark;
END;

Also, don't use cursors as you do - cursor FOR loops are much more elegant as you won't have to declare cursor variable, open cursor, fetch into it, check whether it is empty and close it. Procedure would then look like this:
CREATE OR REPLACE PROCEDURE totalmark
AS
BEGIN
   FOR cur_r IN (SELECT stuid, averagemark (stuid) aver
                   FROM lec_stu_mod)
   LOOP
      UPDATE lec_stu_mod
         SET avemark = cur_r.aver
       WHERE stuid = cur_r.stuid;
   END LOOP;
END;

I hope I didn't make too many syntax errors writing this code (couldn't check it as I don't have your data model here, and was too lazy to create one).
Re: Out and conditional control [message #206871 is a reply to message #206858] Fri, 01 December 2006 19:31 Go to previous messageGo to next message
lee337c33
Messages: 8
Registered: November 2006
Location: London
Junior Member
You're right that that function didn't compile, it was just how I wrote it in notepad before I debugged it.

Will the procedure you submitted work with my original (and working) function?

Thanks.
Re: Out and conditional control [message #206876 is a reply to message #206853] Fri, 01 December 2006 21:19 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>Will the procedure you submitted work with my original (and working) function?
Are you incapable or unwilling to test it for yourself?
With free advice you get what you paid for it.
Re: Out and conditional control [message #206946 is a reply to message #206876] Sat, 02 December 2006 11:19 Go to previous messageGo to next message
lee337c33
Messages: 8
Registered: November 2006
Location: London
Junior Member
Oh no anacedent, i'm not one of those! It's just that I'm not gona be able to test it until I get back to work on Monday.
Re: Out and conditional control [message #206952 is a reply to message #206946] Sat, 02 December 2006 11:34 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Oh well, it seems you'll have two sleepless nights ./fa/1774/0/
Re: Out and conditional control [message #207989 is a reply to message #206952] Thu, 07 December 2006 12:54 Go to previous messageGo to next message
lee337c33
Messages: 8
Registered: November 2006
Location: London
Junior Member
Hey guys,

Still stuck on the same problem. I need to calculate students average mark [AVEMARK - in the linker table ml_stu_mod] from their module grades [MODULE GRADE - also in linker table]

Below is the Function and Procedure I've created, they both compile fine, when I EXEC procedure [TOTALMARK] I get PL/SQL procedure successfully completed - and the database even updates!! But it wont print out on screen


Create or replace function averagemark(

i_STUID ml_stu_mod.STUID%TYPE)

RETURN NUMBER

IS

v_ml_STU_MOD_avemark ml_stu_mod.avemark%TYPE;

BEGIN

select SUM (MODULEGRADE/3)

into v_ML_STU_MOD_avemark



from ml_stu_mod

WHERE STUID = i_STUID;



IF v_ml_STU_MOD_avemark IS NULL THEN

v_ml_STU_MOD_avemark :=0;

END IF;



RETURN v_ml_STU_MOD_avemark;

END;

/





Create or Replace procedure totalmark

AS

CURSOR c_ml_stu_mod

IS
SELECT stuid, mlname, stuname, semester, yearofstudy, avemark


FROM ml_stu_mod;


v_stuid ML_stu_mod.stuid%TYPE;

v_modulegrade ML_stu_mod.modulegrade%TYPE;

v_stuname ml_stu_mod.stuname%TYPE;

v_mlname ml_stu_mod.mlname%TYPE;

v_yearofstudy ml_stu_mod.yearofstudy%TYPE;

v_semester ml_stu_mod.semester%TYPE;

V_avemark ML_stu_mod.avemark%TYPE;

v_ml_stu_mod_avemark number;

BEGIN

OPEN c_ml_stu_mod;

LOOP

FETCH
c_ml_stu_mod

INTO
v_stuid, v_mlname, v_stuname, v_semester, v_yearofstudy, v_avemark;


EXIT WHEN c_ml_stu_mod%NOTFOUND;


v_modulegrade := averagemark(v_stuid);


UPDATE ml_stu_mod

SET avemark = v_modulegrade

WHERE stuid = v_stuid;


END LOOP;

close c_ml_stu_mod;


IF

v_ml_stu_mod_avemark >= 70
THEN
DBMS_OUTPUT.put_line (' Distinction ');

ELSIF
v_ml_stu_mod_avemark >= 50 AND v_ml_stu_mod_avemark <= 69
THEN
DBMS_OUTPUT.put_line (' Pass ');

ELSIF
v_ml_stu_mod_avemark < 50
THEN
DBMS_OUTPUT.put_line (' Fail: you need help.');

END IF;


END;

/
Re: Out and conditional control [message #207998 is a reply to message #207989] Thu, 07 December 2006 14:17 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Your IF statment is checking the wrong variable.

The procedure contains the line:

v_modulegrade := averagemark(v_stuid);

So your IF statements need to check v_modulegrade.

Hope this helps
Re: Out and conditional control [message #208006 is a reply to message #207989] Thu, 07 December 2006 16:00 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
lee337c33 wrote on Thu, 07 December 2006 13:54

Below is the Function and Procedure I've created, they both compile fine, when I EXEC procedure [TOTALMARK] I get PL/SQL procedure successfully completed - and the database even updates!! But it wont print out on screen



And Littlefoot told you why DBMS_OUTPUT is a bad idea. Did you set serveroutput on?

[Updated on: Thu, 07 December 2006 16:04]

Report message to a moderator

Re: Out and conditional control [message #208352 is a reply to message #208006] Sat, 09 December 2006 12:46 Go to previous messageGo to next message
lee337c33
Messages: 8
Registered: November 2006
Location: London
Junior Member
Why the sarky answer joy_division?

Nevermind, all done Very Happy
Re: Out and conditional control [message #208593 is a reply to message #208352] Mon, 11 December 2006 08:24 Go to previous message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
It wasn't meant to be sarky (love that word). I just though maybe you forget to set serveroutput on. It's easy to overlook.

I'm trying to figure out the name. 337 upside-down and in reverse matches the first three letters of you name. I'm wondering if your name is some Escher jumble.
Previous Topic: Question related to Sql query
Next Topic: How to sum up values into a 419(!!) columns wide Table
Goto Forum:
  


Current Time: Thu Dec 08 04:32:55 CST 2016

Total time taken to generate the page: 0.13127 seconds