Home » SQL & PL/SQL » SQL & PL/SQL » Cusor and if statement
Cusor and if statement [message #285179] Mon, 03 December 2007 18:00 Go to next message
vickie2405
Messages: 27
Registered: May 2007
Location: California, US
Junior Member

I want to update every student's GPA by using cusor. However, I encountered a difficulty in add the calculation GPA into the code.
Can anyone help me. Thank you so much.

Here is my code.

create or replace procedure UpdateGPA as
	vSnum Students.Snum%type;
	vGrade Enrollments.Grade%type;
	vCrhr Courses.Crhr%type;
	vGPA Students.GPA%type;
		
cursor CurStudents is
	select Snum from Students;
	
begin
	open CurStudents;
	Loop
		fetch CurStudents into vSnum;
		exit when CurStudents%Notfound;
		select Grade into vGrade
			from Enrollments, Schedules, Courses
			where Enrollments.Snum = vSnum
			and Enrollments.Callnum = Schedules.Callnum
			and Schedules.Cnum = Courses.Cnum;
		select Crhr into vCrhr
			from Enrollments, Schedules, Courses
			where Enrollments.Snum = vSnum
			and Enrollments.Callnum = Schedules.Callnum
			and Schedules.Cnum = Courses.Cnum;
			
		declare
		x1 number;
		x2 number;
		x3 number;
		x4 number;
		x5 number;
				
		If vGrade = 'A' then
			x1:= 4 * vCrhr;
		Elsif vGrade = 'B' then
			x2:= 3 * vCrhr;
			Elsif vGrade = 'C' then
				x3:= 2 * vCrhr;
			Else vGrade = 'D' then
			x3:= 1 * vCrhr;
			End if;
		Else
			x4:= 0 * vCrhr;
		End if;	
	
	vGPA = (x1+x2+x3+x4+x5)/(sum(vCrhr);
	
	update Students
		set GPA = vGPA
		where Snum = vSnum;
	End Loop;
	Close CurStudents;
End;
/

show err
spool off
Re: Cusor and if statement [message #285180 is a reply to message #285179] Mon, 03 December 2007 18:11 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Nicely formatted code but we need additional data
Quote:


Show us what you did (if you tried it yourself), including errors and/or why the result is not what you want.
Provide DDL (CREATE TABLE, etc.) as appropriate instead of listing the table structure. It'll help people setting up a test set (yes, some people really do that)
Provide INSERT statements for sample data instead of pasting in or mocking up the results of a SELECT statement.
Provide your expected result set and explain the rules/reasons that lead to it.


Above is stated in the posting guideline as found in URL below
http://www.orafaq.com/forum/t/88153/0/

See without the tables & data it is MUCH more difficult to debug.
You have both & can't find the error.

BTW - exactly what are the specifics of the 'difficulty'?
Re: Cusor and if statement [message #285181 is a reply to message #285179] Mon, 03 December 2007 18:21 Go to previous messageGo to next message
vickie2405
Messages: 27
Registered: May 2007
Location: California, US
Junior Member

sorry for the confusing.
I want to calculate the GPA then update into the table for every student.
Should I declare x1,x2,x3,x4 before the loop? Is it the logic problem or the code?

Here is the code
create or replace procedure UpdateGPA as
	vSnum Students.Snum%type;
	vGrade Enrollments.Grade%type;
	vCrhr Courses.Crhr%type;
	vGPA Students.GPA%type;
		
cursor CurStudents is
	select Snum from Students;
	
begin
	open CurStudents;
	Loop
		fetch CurStudents into vSnum;
		exit when CurStudents%Notfound;
		select Grade into vGrade
			from Enrollments, Schedules, Courses
			where Enrollments.Snum = vSnum
			and Enrollments.Callnum = Schedules.Callnum
			and Schedules.Cnum = Courses.Cnum;
		select Crhr into vCrhr
			from Enrollments, Schedules, Courses
			where Enrollments.Snum = vSnum
			and Enrollments.Callnum = Schedules.Callnum
			and Schedules.Cnum = Courses.Cnum;
			
		declare
		x1 number;
		x2 number;
		x3 number;
		x4 number;
		x5 number;
				
		If vGrade = 'A' then
			x1 = 4 * vCrhr;
		Elsif vGrade = 'B' then
			x2 = 3 * vCrhr;
			Elsif vGrade = 'C' then
				x3 = 2 * vCrhr;
			Else vGrade = 'D' then
			x3 = 1 * vCrhr;
			End if;
		Else
			x4 = 0 * vCrhr;
		End if;	
	
	vGPA = (x1+x2+x3+x4+x5)/(sum(vCrhr));
	
	update Students
		set GPA = vGPA
		where Snum = vSnum;
	End Loop;
	Close CurStudents;
End;
/

show err
spool off


and here is the error mesage:
LINE/COL ERROR
-------- -----------------------------------------------------------------
33/3     PLS-00103: Encountered the symbol "IF" when expecting one of the
         following:
         begin function package pragma procedure subtype type use
         <an identifier> <a double-quoted delimited-identifier> form
         current cursor
         The symbol "begin" was substituted for "IF" to continue.

34/7     PLS-00103: Encountered the symbol "=" when expecting one of the
         following:
         := . ( @ % ;


LINE/COL ERROR
-------- -----------------------------------------------------------------
35/3     PLS-00103: Encountered the symbol "ELSIF"
SQL> spool off
SQL> 


Here is the table:
SQL> select * from Students;

SNU SNAME        STANDING MAJ        GPA                                        
--- ---------- ---------- --- ----------                                        
101 Andy                4 IS                                                    
102 Betty               2                                                       
103 Cindy               3 IS                                                    
104 David               3 MKT                                                   
105 Ellen               3                                                       

SQL> select * from Enrollments;

 ENROLL_ID SNU    CALLNUM GR                                                    
---------- --- ---------- --                                                    
     58001 101      10110 F                                                     
     58002 102      10110 A                                                     
     58003 103      10120 D                                                     
     58004 101      10125 B                                                     
     58005 102      10130 C                                                     

SQL> select * from Schedules;

   CALLNUM       YEAR SEM DEP CNU    SECTION   CAPACITY                         
---------- ---------- --- --- --- ---------- ----------                         
     10110       2003 Sp  IS  300          1         45                         
     10115       2003 Sp  IS  300          2         35                         
     10120       2003 Sp  IS  380          1         35                         
     10125       2003 Fa  IS  300          1        118                         
     10130       2003 Fa  IS  301          1         33                         

SQL> select * from Courses;

DEP CNU CTITLE                               CRHR   STANDING                    
--- --- ------------------------------ ---------- ----------                    
IS  300 Intro to MIS                            3          2                    
IS  301 Business Communicatons                  3          2                    
IS  310 Statistics                              3          2                    
IS  355 Networks                                3          3                    
IS  380 Database                                3          3                    
IS  385 Systems                                 3          3                    
IS  480 Adv Database                            3          4                    

7 rows selected.
Re: Cusor and if statement [message #285182 is a reply to message #285179] Mon, 03 December 2007 18:37 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
I always declare all my variable at the top & before the 1st BEGIN.

The way the code is structured you need to add BEGIN after this line:
x5 number;

It appears you have a malformed IF statement starting with:
Else vGrade = 'D' then
Re: Cusor and if statement [message #285192 is a reply to message #285179] Mon, 03 December 2007 21:32 Go to previous messageGo to next message
cmerry
Messages: 109
Registered: November 2005
Location: Idaho
Senior Member
First of all, the PL/SQL block is not necessary. This could be done with a single statement.

Now to the point of your existing code. I don't think your GPA calculation is correct, and unless you have created your own SUM function, it cannot be used in an assignment like you have done. It looks like you should re-evaluate your logic.
Re: Cusor and if statement [message #285208 is a reply to message #285179] Mon, 03 December 2007 22:55 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


Plus , Beware of NULLS in..

(x1+x2+x3+x4+x5)/(sum(vCrhr);


SQL> select NULL+ 1+ 2+ 3 sum from  Dual;

       SUM
----------


SQL>


As suggested by cmerry , Try to do these with single SQL statement .

Thumbs Up
Rajuvan.
Re: Cusor and if statement [message #285209 is a reply to message #285179] Mon, 03 December 2007 23:02 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

And if you insist to use the current code , why cant you use only One variable say x instead of x1 ,x2,x3,x4,x5 ?

Hints for using single SQL statement instead Smile

Update
Decode
Sum analytic function
where exists


Thumbs Up
Rajuvan.



[Updated on: Mon, 03 December 2007 23:03]

Report message to a moderator

Re: Cusor and if statement [message #285211 is a reply to message #285179] Mon, 03 December 2007 23:09 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

Quote:

vGPA = (x1+x2+x3+x4+x5)/(sum(vCrhr));


Is the assign operator correct? It should be ( := ) right?

Regards,
Kiran.
Re: Cusor and if statement [message #285227 is a reply to message #285181] Tue, 04 December 2007 00:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The whole block can be replace by a single SQL statement.
What is your Oracle version?

Regards
Michel

[Updated on: Tue, 04 December 2007 00:52]

Report message to a moderator

Re: Cusor and if statement [message #285599 is a reply to message #285179] Wed, 05 December 2007 02:20 Go to previous messageGo to next message
vickie2405
Messages: 27
Registered: May 2007
Location: California, US
Junior Member

thank you so much for all of your kindly replies. I fixed my code as this and I got the error 00306. I checked all my data types, they are fine. May anyone take a look at it. Thanks
SQL> create or replace procedure UpdateGPA as
  2          vSnum Students.Snum%type;
  3          vGPA Students.GPA%type;
  4  
  5  cursor CurStudents is
  6          select Snum from Students;
  7  
  8  begin
  9          open CurStudents;
 10          Loop
 11                  fetch CurStudents into vSnum;
 12                  exit when CurStudents%Notfound;
 13                  select (sum(decode(Grade,'A',4,
 14                                                                  'B',3,
 15                                                                  'C',2,
 16                                                                  'D',1,
 17                                                                  'F',0))*crhr)/(sum(crhr)) into vGPA
 18                          from Enrollments, Schedules, Courses, Students
 19                          where Enrollments.Snum = Students.Snum
 20                          and Students.Snum = vSnum
 21                          and Enrollments.Callnum = Schedules.Callnum
 22                          and Schedules.Cnum = Courses.Cnum;
 23  
 24          update Students
 25                  set GPA = vGPA
 26                  where Snum = vSnum;
 27          End Loop;
 28          Close CurStudents;
 29  End;
 30  /

Procedure created.

SQL> 
SQL> show err
No errors.
SQL> spool off
SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> exec UpdateGPA('101');
BEGIN UpdateGPA('101'); END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'UPDATEGPA'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

[Updated on: Wed, 05 December 2007 02:22]

Report message to a moderator

Re: Cusor and if statement [message #285601 is a reply to message #285599] Wed, 05 December 2007 02:23 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
PLS-00306: wrong number or types of arguments in call to 'UPDATEGPA'

How many arguments does the procedure UPDATEGPA expect?
How many arguments do you pass to the procedure when calling it?
Re: Cusor and if statement [message #285603 is a reply to message #285179] Wed, 05 December 2007 02:29 Go to previous messageGo to next message
vickie2405
Messages: 27
Registered: May 2007
Location: California, US
Junior Member

Sad sorry, I dont get it. Sad
Re: Cusor and if statement [message #285604 is a reply to message #285603] Wed, 05 December 2007 02:33 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
SQL> create or replace procedure UpdateGPA as
  2          vSnum Students.Snum%type;
  3          vGPA Students.GPA%type;
  4


The procedure doesn't expect any arguments at all

SQL> exec UpdateGPA('101');
BEGIN UpdateGPA('101'); END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'UPDATEGPA'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


But you call it passing it one argument '101', hence the error
Re: Cusor and if statement [message #285605 is a reply to message #285179] Wed, 05 December 2007 02:33 Go to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Plus Even both Select and Update statement could be replaces with a single Update statement. Try in that way also once tyhe present issue is solved .

with the sql ..

Update Students
       Set GPA  = ( Select ...
                                   .......)
Where Snum = vSnum;


using ,

1/ Update
2/ Correlated subquery
3/ Decode
4/ Sum() Function

And not using Cursor , For Loop .. bla bla

Thumbs Up
Rajuvan.

[Updated on: Wed, 05 December 2007 02:38]

Report message to a moderator

Previous Topic: selection in columns
Next Topic: Triggers: how do I access values passed into a trigger?
Goto Forum:
  


Current Time: Sat Dec 10 05:00:12 CST 2016

Total time taken to generate the page: 0.12505 seconds