Answer to Question #268396 in Databases | SQL | Oracle | MS Access for tumi

Question

Question 5                                                                                                                                                         [15]

Create a procedure named subj_info that receives subject code as a parameter and displays information as shown in the sample output below. The procedure makes use of a CURSOR to retrieve and display the required information. Execute the procedure using any subject code.

Show how you would do this.

When you invoke the procedure with SP1 as a subject code, this is the output:

SLEEPY,07-APR-72 registered for SYSTEMS PROGRAMMING 1 on 10-JAN-97

SMUTS,12-JAN-70 registered for SYSTEMS PROGRAMMING 1 on 10-JAN-97

Answer 1

SOLUTION TO THE ABOVE QUESTION

In this question lets assume we have a table named subjects with name, 
sub_code,sub_reg_date columns


CREATE  PROCEDURE subj_info (
    subject_code    VARCHAR2,
    subject_refcur    IN OUT SYS_REFCURSOR
)
IS
BEGIN
    OPEN subject_refcur  FOR SELECT name, sub_reg_date FROM subjects WHERE sub_code = subject_code;
END;


//Invoking the subj_info procedure
DECLARE
    s_name            subjects.name%TYPE;
    s_sub_reg_date    subjects.sub_reg_date%TYPE;
    s_subject_code    subjects.sub_code%TYPE := subject_code;
    subject_refcur  SYS_REFCURSOR;
BEGIN

    student_by_subject_code(s_subject_code, subject_refcur ); 
    LOOP
        FETCH subject_refcur  INTO s_name, s_sub_reg_date
        EXIT WHEN subject_refcur%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(s_name || '    registred for SYSTEMS PROGRAMMING 1 on ' || sub_reg_date);
    END LOOP;
    CLOSE subject_refcur;
END;