1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Need some help with a procedure I'm having trouble with

Discussion in 'SQL PL/SQL' started by tastybrownies, Nov 26, 2009.

  1. tastybrownies

    tastybrownies Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    Hello everyone, after hours and hours of trying to do this on my own, I figured I would put my problem up on the forum.

    I'm trying to create a procedure to assign grades to students. I have several tables; one of them is called registration and it contains studentid and sectionID that is filled in with information. I also have a grade table that has all the same info entered in registration(done with a trigger) but there is also a NULL grade field. So now I am ready to assign grades and I'm having a problem.

    We are supposed to assign a letter grade based on the class name's last letter. So essentially, if the last letter is between 'A' or 'F' then the grade updated to the grade table is 'A' and so on. When I try to update it toward the bottom, it reads what letter the last row was and not all 15, so i can't have multiple grades of a,b,c,etc.

    I have all the information i need, i just can't loop the variable for the last class letter.

    Code (SQL):
    CREATE OR REPLACE PROCEDURE SKELETON
    IS
    v_sectionID registration.sectionid%TYPE;
    v_firstName student.firstname%TYPE;
    v_lastname student.lastname%TYPE;
    v_courseLetter course.coursename%TYPE;
    v_grade_studentID grade.studentid%TYPE;
    v_courseid course.courseid%TYPE;
    v_studentID grade.studentid%TYPE;
    v_grade_sectionID grade.sectionID%TYPE;

    CURSOR grade_cursor
    IS
    SELECT registration.studentid, student.firstname, student.lastname, registration.sectionid, course.courseid, substr(coursename, LENGTH(coursename))
    FROM registration, SECTION, course, student
    WHERE registration.studentid = student.studentid
    AND registration.sectionid = SECTION.sectionid
    AND SECTION.courseid = course.courseid
    ORDER BY registration.studentid;

    BEGIN
     
     
      OPEN grade_cursor;
       
      LOOP
     

      FETCH grade_cursor INTO v_studentID, v_firstName, v_lastName, v_grade_sectionID, v_courseid, v_courseLetter;

       
    EXIT WHEN grade_cursor%NOTFOUND;


      DBMS_OUTPUT.PUT_LINE('StudentID = ' || v_studentID || ','  || ' First Name = ' || v_firstName || ',' || ' Last Name = ' || v_lastName || ',' || ' Section ID = ' || v_grade_sectionID || 'Course ID = ' || v_courseid || ',' || ' Class Letter = ' || v_courseLetter
      );

      UPDATE grade
       SET grade =
       (CASE
        WHEN v_courseLetter = 'g' THEN 'A'
        ELSE 'n'    
       END);
    END LOOP;

    --this was just a test.........
    dbms_output.put_line(v_courseLetter);


    -- Last Step: closing the cursor
    CLOSE grade_cursor;
    END;
     
  2. M_Anas_O

    M_Anas_O Forum Advisor

    Messages:
    95
    Likes Received:
    9
    Trophy Points:
    160
    Location:
    Austria
    Hi,

    If I get your problem right, at the end of program run, you are finding all records with the same grade.

    This is because your update statement does not have any where clause.

    Code (SQL):
    UPDATE grade   SET grade =    (CASE    WHEN v_courseLetter = 'g' THEN 'A'    ELSE 'n'       END);
    So its updating all records with current grade everytime, at the end resulting in grade of last cursor record in all records.

    Add a suitable where clause to fix this.

    Regards,
    Mohammed
     
  3. tastybrownies

    tastybrownies Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    The problem is when I read the variable v_courseLetter. Instead of it being all of the course letters it is just one. So when I read the variable it only picks up one letter and not all of them, but who knows.

    I will try and mess with it some more, thank you for your input.
     
  4. M_Anas_O

    M_Anas_O Forum Advisor

    Messages:
    95
    Likes Received:
    9
    Trophy Points:
    160
    Location:
    Austria
    Hi,

    When you say "The problem is when I read the variable v_courseLetter",
    I think you are referring to statement :

    Code (SQL):
      DBMS_OUTPUT.PUT_LINE('StudentID = ' || v_studentID || ','  || ' First Name = ' || v_firstName || ',' || ' Last Name = ' || v_lastName || ',' || ' Section ID = ' || v_grade_sectionID || 'Course ID = ' || v_courseid || ',' || ' Class Letter = ' || v_courseLetter  
    );
    Did you run you cursor query seperately to see if it gets desired output?

    Regards,
    Mohammed
     
  5. tastybrownies

    tastybrownies Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    No the output is actually working great, with no problems at all. It's when I try to read v_courseLetter to see whether or not the field has a certain value. Something like this,

    Code (SQL):
    UPDATE grade
    SET grade =
    (CASE
    WHEN v_courseLetter BETWEEN 'a' AND 'f' THEN 'A'
    WHEN v_courseLetter BETWEEN 'g' AND 'k' THEN 'B'
    WHEN v_courseLetter BETWEEN 'l' AND 'p' THEN 'C'
    WHEN v_courseLetter BETWEEN 'q' AND 't' THEN 'D'
    WHEN v_courseLetter BETWEEN 'u' AND 'z' THEN 'E'
    END);
     
    It doesn't read all the values, I just get the last one from when I did the output lines(in this case it's a g).
     
  6. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Again, you missed the Where clause for the UPDATE.