Home » SQL & PL/SQL » SQL & PL/SQL » Cusor and if statement
Cusor and if statement Mon, 03 December 2007 18:00
 vickie2405 Messages: 27Registered: 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;
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;
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
 BlackSwan Messages: 25431Registered: 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
 vickie2405 Messages: 27Registered: 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;
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;
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
 BlackSwan Messages: 25431Registered: 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
 cmerry Messages: 109Registered: 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
 rajavu1 Messages: 1574Registered: 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 .

Rajuvan.
Re: Cusor and if statement [message #285209 is a reply to message #285179] Mon, 03 December 2007 23:02
 rajavu1 Messages: 1574Registered: 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

Update
Decode
Sum analytic function
where exists

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
 kir_ait Messages: 198Registered: 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
 Michel Cadot Messages: 64850Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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
 vickie2405 Messages: 27Registered: 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;
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
 MarcS Messages: 312Registered: 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
 vickie2405 Messages: 27Registered: May 2007 Location: California, US Junior Member
sorry, I dont get it.
Re: Cusor and if statement [message #285604 is a reply to message #285603] Wed, 05 December 2007 02:33
 MarcS Messages: 312Registered: 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
 rajavu1 Messages: 1574Registered: 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

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: Mon May 29 15:50:22 CDT 2017

Total time taken to generate the page: 0.09006 seconds