CODEWITHSUNDEEP
sqloracle-databaseplsql
Josh
Josh

Reputation: 164

For loop using VARCHAR2

I'm trying to display the info for each student using this code

DECLARE
    CURSOR cursor1 IS SELECT STUDENTNAME, COURSEID, COURSEDESCRIPTION, COURSECREDITS, GRADE
    FROM STUDENTINFO;
    S_NAME STUDENTINFO.STUDENTNAME%TYPE;
    S_COURSEID STUDENTINFO.COURSEID%TYPE;
    S_COURSEDESCRIPTION STUDENTINFO.COURSEDESCRIPTION%TYPE;
    S_COURSECREDITS STUDENTINFO.COURSECREDITS%TYPE;
    S_GRADE STUDENTINFO.GRADE%TYPE;
BEGIN
    OPEN CURSOR1;
    LOOP 
    FETCH CURSOR1 INTO S_NAME, S_COURSEID, S_COURSEDESCRIPTION, S_COURSECREDITS, S_GRADE;
    EXIT WHEN cursor1%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('Student Name: ' || S_NAME);
    DBMS_OUTPUT.PUT_LINE(S_COURSEID || S_COURSEDESCRIPTION || S_COURSECREDITS || S_GRADE);
    DBMS_OUTPUT.PUT_LINE(CHR(10));
    END LOOP;
    CLOSE CURSOR1;
END;
/

My output should be similar to 

Student Name: John
CMIS 101 Intro to Info. Systems 3 B
CMIS 301 System Analysis 3 C
CMIS 451 Client/Server Systems 3 C

I'm pretty sure I should be using a for loop which I created 

DECLARE
    CURSOR cursor2 IS SELECT STUDENTNAME, COURSEID, COURSEDESCRIPTION, COURSECREDITS, GRADE
    FROM STUDENTINFO;
    search_student STUDENTINFO.STUDENTNAME%TYPE;
BEGIN
    FOR v_Record IN cursor2
    LOOP
        IF v_Record.STUDENTNAME = &SEARCH_STUDENT THEN
            DBMS_OUTPUT.PUT_LINE('Student Name: ' || STUDENTNAME);
            DBMS_OUTPUT.PUT_LINE(COURSEID || COURSEDESCRIPTION || COURSECREDITS || GRADE);
            DBMS_OUTPUT.PUT_LINE(CHR(10));
        END IF;
    END LOOP;
END;
/

However, when I type in a name for search_student, I'm just given the error

Identifier "insertnamehere"

Can I not use VARCHAR2 when searching? Is it only usable with numbers?

Upvotes: 1

Views: 664

Answers (1)

Boneist
Boneist

Reputation: 23588

There are a few issues with your code.

For a start, why use PL/SQL to do this? You should just use a SQL statement directly. I'm going to assume that this is a homework question though (*sigh* - surely there are better examples to use?!).

1. IF v_Record.STUDENTNAME = &SEARCH_STUDENT - when you pass in a value for search_student, the client replaces the term &search_student. So, either you must ensure that the single quotes to specify the studentname is a string when you define the search_student (ie. when prompted for search_student you enter "'SomeName'") OR put the single quotes around the &SEARCH_STUDENT - ie. IF v_Record.STUDENTNAME = '&SEARCH_STUDENT'

2. When referencing the fields returned by the cursor in the for loop, you need to reference the record that the values were fetched into. Therefore DBMS_OUTPUT.PUT_LINE('Student Name: ' || STUDENTNAME); should be DBMS_OUTPUT.PUT_LINE('Student Name: ' || v_Record.STUDENTNAME);

3. Finally, if you're looking to output a single student record at a time, put the filter in the cursor, not in the loop.

Upvotes: 3

Related Questions