CODEWITHSUNDEEP
sqloracle-databaseplsqloracle10g
Lizzie
Lizzie

Reputation: 179

PL/SQL Write a procedure to update a column?

I am supposed to update a students grade to 'A' based on procedure 

ChangeGrade(p_sID, p_classID)

If the student is not enrolled in the class (p_classID) then an error message is printed.

Here is the table:

Enrolling

sID    classID   Grade
***    *******   *****
104      10440     B
102      10220     C
...      .....     .

Am I supposed to do an inner join? Here's what I have:

Create or Replace ChangeGrade (
  p_sID enrolling.sID%type
  p_classID enrolling.classID%type )
AS
  p_id_enrolled NUMBER;

BEGIN
  SELECT sID into p_id_enrolled 
  FROM Enrolling
  WHERE sID = p_sID
  AND classID = p_classID

  IF p_sID = p_id_enrolled THEN
    update Enrolling
    set GRADE = 'A'
    dbms_output.put_line('Student grade has been changed.')
  ELSE
    dbms_output.put_line('Student record does not exist.')
  END IF;
END;
/

Upvotes: 3

Views: 26039

Answers (6)

Tenzin
Tenzin

Reputation: 2505

You could also use a cursor, and then see if the student is enrolled in that course. If he/she is, then update the grade. I think a cursor is more safe then a SELECT X INTO n, becasue that does give an ugly error when you try to insert a NULL, SELECT NULL INTO n, and those errors are generaly hard to find.

For example: 

CREATE OR REPLACE ChangeGrade (p_sID enrolling.sID%TYPE,
                               p_classID enrolling.classID%TYPE)
AS
              CURSOR cEnrolling IS
                     SELECT     *
                     FROM       Enrolling
                     WHERE      SID = p_sID
                     AND        classID = p_classID;

              rEnrolling cEnrolling%ROWTYPE;
BEGIN
        OPEN cEnrolling;
        FETCH cEnrolling INTO rEnrolling;
              IF cEnrolling%FOUND THEN
                      -- Student record found.
                      UPDATE Enrolling
                      SET Grade = 'A'
                      WHERE SID = rEnrolling.sId;

                      DBMS_OUTPUT.PUT_LINE('Student grade has been changed.');
              ELSE
                      -- Student record not found. 
                      DBMS_OUTPUT.PUT_LINE('Student record does not exist.');
              END IF;
        CLOSE cEnrolling;
END;
/

Upvotes: 0

XING
XING

Reputation: 9886

As mentioned in my comment, there were several places where semicolon(line termiator) was missing in your procedure. Try this:

CREATE OR REPLACE Procedure ChangeGrade (
  p_sID enrolling.sID%type
  p_classID enrolling.classID%type )
AS
  p_id_enrolled NUMBER;

BEGIN
   SELECT sID
     INTO p_id_enrolled
     FROM Enrolling
    WHERE sID = p_sID AND classID = p_classID;

   IF p_sID = p_id_enrolled
   THEN
      UPDATE Enrolling
         SET GRADE = 'A'
       WHERE sID = p_sID;

      DBMS_OUTPUT.put_line ('Student grade has been changed.');
   ELSE
      DBMS_OUTPUT.put_line ('Student record does not exist.');
   END IF;

 EXCEPTION 
 WHEN NO_DATA_FOUND Then
dbms_output.put_line('Student record does not exist for this class');

END;
/

Upvotes: 0

Cyrille MODIANO
Cyrille MODIANO

Reputation: 2376

You need to handle the fact that the student is not in the class as an exception:

Create or Replace PROCEDURE ChangeGrade (
  p_sID enrolling.sID%type,
  p_classID enrolling.classID%type,
  p_grade enrolling.grade%type  )
AS
 l_enrolled NUMBER;

BEGIN
  SELECT sID INTO l_enrolled 
  FROM Enrolling
  WHERE sID = p_sID
  AND classID = p_classID;

  IF l_enrolled = p_sID THEN
    update Enrolling set GRADE = p_grade WHERE sID = p_sID and classID = p_classID;
    dbms_output.put_line('Student grade has been changed.');
  END IF;

  EXCEPTION WHEN NO_DATA_FOUND
    dbms_output.put_line('Student record does not exist for this class');
END;
/

I Also added a parameter for the grade because it's logical to pass it as well.

Upvotes: 2

Dmitriy
Dmitriy

Reputation: 5565

Here are the problems in your code:

  1. This query is meaningless:

    SELECT sID into p_id_enrolled 
  FROM Enrolling
  WHERE sID = p_sID
  AND classID = p_classID

    You select sID into p_id_enrolled, but in WHERE clause you filter sID = p_sID, so p_id_enrolled will always be equal to p_sID, and you don't need this select statement at all.

  2. This update statement updates the whole table:

    UPDATE Enrolling
   SET GRADE = 'A';

    You need to add a filtering clause to update only one row.

  3. In a case of student is not enrolled in class, the query returns no rows, and you will get NO_DATA_FOUND exception. To process it, you need to catch exception or calculate count of students.

I would recommend to use following:

Create or Replace procedure ChangeGrade (
  p_sID enrolling.sID%type,
  p_classID enrolling.classID%type )
AS
  cnt NUMBER;

BEGIN
  SELECT count(*) into cnt
    FROM Enrolling
   WHERE sID = p_sID
     AND classID = p_classID;

  IF cnt = 1 THEN
    update Enrolling
       set GRADE = 'A'
     where sID = p_sID
       AND classID = p_classID;
    dbms_output.put_line('Student grade has been changed.');
  ELSE
    dbms_output.put_line('Student record does not exist.');
  END IF;
END;
/

Upvotes: 1

Mike
Mike

Reputation: 2005

Create or Replace procedure ChangeGrade (
  p_sID enrolling.sID%type
  p_classID enrolling.classID%type )
AS
BEGIN
  update Enrolling
     set GRADE = 'A'
   where sID = p_sID
     AND classID = p_classID;
  IF SQL%ROWCOUNT > 0 THEN
    dbms_output.put_line('Student grade has been changed.');
  ELSE
    dbms_output.put_line('Student record does not exist.');
  END IF;
END;

Upvotes: 5

JanFi86
JanFi86

Reputation: 529

I do not know what kind of error is printed out, but it seems like you are missing ';' in the end of some commands (in the if statement and after select into).

Upvotes: 0

Related Questions