RSS

9.1.4 Put It All Together

12 May

DECLARE

v_sid student.student_id%TYPE;

CURSOR c_student IS

SELECT student_id

FROM student

WHERE student_id < 110;

BEGIN

OPEN c_student;

LOOP

FETCH c_student INTO v_sid;

EXIT WHEN c_student%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(‘STUDENT ID: ‘||v_sid);

END LOOP;

CLOSE c_student;

EXCEPTION

WHEN OTHERS THEN

IF c_student%ISOPEN THEN

CLOSE c_student;

END IF;

END;

 

 

DECLARE

v_sid student.student_id%TYPE;

CURSOR c_student IS

SELECT student_id

FROM student

WHERE student_id < 110;

BEGIN

OPEN c_student;

LOOP

FETCH c_student INTO v_sid;

IF c_student%FOUND THEN

DBMS_OUTPUT.PUT_LINE(‘JUST FETCHED ROW ‘ || TO_CHAR(c_student%ROWCOUNT) || ‘ STUDENT ID: ‘ || v_sid);

ELSE

EXIT;

END IF;

END LOOP;

CLOSE c_student;

EXCEPTION

WHEN OTHERS THEN

IF c_student%ISOPEN THEN

CLOSE c_student;

END IF;

END;

 

 

SET SERVEROUTPUT ON

DECLARE

CURSOR c_student_enroll IS

SELECT s.student_id, first_name, last_name,

COUNT(*) enroll,

(CASE

WHEN count(*) = 1 THEN ‘ class.’

WHEN count(*) IS NULL THEN ‘ no classes.’

ELSE ‘ classes.’

END) class

FROM student s, enrollment e

WHERE s.student_id = e.student_id

AND s.student_id <110

GROUP BY s.student_id, first_name, last_name;

r_student_enroll    c_student_enroll%ROWTYPE;

BEGIN

OPEN c_student_enroll;

LOOP

FETCH c_student_enroll INTO r_student_enroll;

EXIT WHEN c_student_enroll%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(‘Student INFO: ID ‘||

r_student_enroll.student_id||‘ is ‘||

r_student_enroll.first_name|| ‘ ‘ ||

r_student_enroll.last_name||

‘ is enrolled in ‘||r_student_enroll.enroll||

r_student_enroll.class);

END LOOP;

CLOSE c_student_enroll;

EXCEPTION

WHEN OTHERS THEN

IF c_student_enroll %ISOPEN THEN

CLOSE c_student_enroll;

END IF;

END;

Remember that the CASE syntax was introduced in Oracle 9i.This means that the preceding statement will not run in Oracle 8 or 8i.You can change the CASE statement to a DECODE statement as follows:

DECODE( count(*), 1, ‘ class. ‘, null, ‘no classes.’, ‘classes’) class

Advertisements
 
Leave a comment

Posted by on 05/12/2010 in ORACLE

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

 
%d bloggers like this: