using a for loop in PL/SQL

Question

Write a PL/SQL program that accomplishes the following: (a) Declare a cursor called c_students that selects the following fields: StudentId, Lname, Fname, and Major from the Students table Where the major is ‘Accounting’ (b) Retrieve the records listed above into v_sid, v_lname, v_fname, v_major (c) Display the retrieved records.

Use the for.. loop to retrieve all the rows from the Student table where the students last name is ‘Black’. Display these rows.

SOLUTION: I have managed to complete parts a, b and c successfully as follows:

DROP TABLE IF EXISTS students;
CREATE TABLE students(
    StudentId NUMBER(3),
    LName VARCHAR2(30),
    FNAme VARCHAR2(30),
    Major VARCHAR2(30));
INSERT INTO students VALUES(130,'Black','Judy','Accounting');
INSERT INTO students VALUES(132,'Maw','Richard','Marketing');
INSERT INTO students VALUES(134,'Bessner','Bryan','Finance');
INSERT INTO students VALUES(136,'Shaw','Tim','Accounting');
INSERT INTO students VALUES(138,'Black','Conrad','Sales');

COMMIT;

DECLARE
    cursor c_students IS
    --for rec in (select * from students where 
        select * from students where major like 'Accounting';
    v_sid Number(3);
    v_lname VARCHAR2(30);
    v_fname VARCHAR2(30);
    v_major VARCHAR2(30);
BEGIN   
    open c_students;
    Loop
        Fetch c_students into v_sid, v_lname, v_fname, v_major;
        IF c_students%NOTFOUND Then
            Exit;
        End If;
        dbms_output.put_line(v_sid||' '||v_lname||' '||v_fname||' '||v_major);
    End Loop;
    close c_students;
End;
/

=================================================================================

Now the last part, after c, states that I need to accomplish the same thing I did above with a for loop and I can't for the life of me figure out how to do it in a for loop. I know that the for-loop doesn't get declared and starts in the Begin section, but I don't know where to go from there. Any help would be greatly appreciated. Thank you!

www2.cis.gsu.edu/dmcdonald/cis8040/… Page 32

Anjan Biswas
– Anjan Biswas

2013-07-22 22:53:31 +00:00
Commented Jul 22, 2013 at 22:53 — Anjan Biswas
– Anjan Biswas, Commented Jul 22, 2013 at 22:53
OR tutorialspoint.com/plsql/plsql_loops.htm

Anjan Biswas
– Anjan Biswas

2013-07-22 22:54:13 +00:00
Commented Jul 22, 2013 at 22:54 — Anjan Biswas
– Anjan Biswas, Commented Jul 22, 2013 at 22:54
Or, if all else fails, Try Reading The Manual.

Bob Jarvis - Слава Україні
– Bob Jarvis - Слава Україні

2013-07-23 01:00:07 +00:00
Commented Jul 23, 2013 at 1:00 — Bob Jarvis - Слава Україні
– Bob Jarvis - Слава Україні, Commented Jul 23, 2013 at 1:00

Sebas · Accepted Answer · 2013-07-22 22:27:24Z

1

Replace the open loop etc with the following. Then you can reference your cursor's columns through the rec variable.

For rec in c_students
Loop
...
End loop;

edited Jul 22, 2013 at 22:27

Sebas

21.6k9 gold badges60 silver badges109 bronze badges

answered Jul 22, 2013 at 22:11

verbanicm

2,5461 gold badge16 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

using a for loop in PL/SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related