questions on code in PL/SQL by Example book [message #389586] |
Mon, 02 March 2009 16:41 |
happyjoshua777
Messages: 33 Registered: February 2009 Location: United States
|
Member |
|
|
I have the following code (see below). It complies and runs perfectly well, however i cannot figure out a few things:
1. should it not me 's.first_name, s.last_name' and NOT
just 'first_name, last_name'
2. how exactly does COUNT(*) work? is that a function in PL/SQL
and what does a * do?
3. why do we need to FETCH a cursor into a into a record (r_student_enroll) in order to print out in a loop what the code needs to print out, why not just go through what cursor points to each record at a time, just like a link list in C++? I probably misunderstand something...
thank you very much :)
========================
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||' is '||
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;
/
[mod-edit: added code tags; next time please add them yourself]
[Updated on: Mon, 02 March 2009 18:48] by Moderator Report message to a moderator
|
|
|
|
|
Re: questions on code in PL/SQL by Example book [message #389631 is a reply to message #389610] |
Tue, 03 March 2009 00:07 |
|
Littlefoot
Messages: 21821 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
- As a matter of fact, it *would* matter if these columns existed in both 'student' and 'enrollment' tables.
Here's an example: the first one doesn't use table aliases, the other one does (and that is, in my opinion, the right way to do that, even if it is obvious - to the author - which column belongs to what table. Sooner or later, it won't be that obvious any more. Also, you might add other tables later and find yourself in problems. Therefore, I'd always use table aliases when referring to column names).SQL> select d.deptno, e.ename
2 from emp e, dept d
3 where deptno = deptno
4 and d.deptno = 10;
where deptno = deptno
*
ERROR at line 3:
ORA-00918: column ambiguously defined
SQL> select d.deptno, e.ename
2 from emp e, dept d
3 where d.deptno = e.deptno
4 and d.deptno = 10;
DEPTNO ENAME
---------- ----------
10 CLARK
10 KING
10 MILLER
SQL>
|
|
|
|
|
Re: questions on code in PL/SQL by Example book [message #389789 is a reply to message #389785] |
Tue, 03 March 2009 10:06 |
|
Michel Cadot
Messages: 68704 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
No, a cursor is like a debugger area that gives you where you are in the processing and what the environment variables and values. Fetch is like executing the next step and giving the new value of variables.
Regards
Michel
|
|
|