Home » SQL & PL/SQL » SQL & PL/SQL » questions on code in PL/SQL by Example book
questions on code in PL/SQL by Example book [message #389586] Mon, 02 March 2009 16:41 Go to next message
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 #389588 is a reply to message #389586] Mon, 02 March 2009 16:58 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.

http://tahiti.oracle.com contain whole Oracle Doc set which contains answers to your questions.
Re: questions on code in PL/SQL by Example book [message #389610 is a reply to message #389586] Mon, 02 March 2009 22:51 Go to previous messageGo to next message
Michel Cadot
Messages: 68704
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1. It does not matter
2. Count(*) can't return null
3. A (explicit) cursor is NOT a list, it is more like a break point in a code, you have to run the next call to get the result. Or if you prefer to see it at a linked list, say that fetch is like current_row = current_row.next

SQL Reference
PL/SQL User's Guide and Reference
Application Developer's Guide - Fundamentals

Regards
Michel
Re: questions on code in PL/SQL by Example book [message #389631 is a reply to message #389610] Tue, 03 March 2009 00:07 Go to previous messageGo to next message
Littlefoot
Messages: 21821
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
  1. 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 #389783 is a reply to message #389631] Tue, 03 March 2009 09:55 Go to previous messageGo to next message
happyjoshua777
Messages: 33
Registered: February 2009
Location: United States
Member
i just wanted to clarify.
are you saying that since first_name and last_name are unique to the student table, therefore one does not need to write s.first_name and s.last_name (where s is an alias for student)?

thank you very much Smile

p.s. how do i add tags to my code when i post messages? when i pasted the code and tags were displayed and then after i saw my posting there were no tags and the moderator had to put them in...
Re: questions on code in PL/SQL by Example book [message #389785 is a reply to message #389610] Tue, 03 March 2009 09:59 Go to previous messageGo to next message
happyjoshua777
Messages: 33
Registered: February 2009
Location: United States
Member
can one say that a cursor is like a pointer to the dynamically allocated data (e.g. a like a link list or a record)? is FETCH process than something that puts the data together and etc?
thank you Smile
Re: questions on code in PL/SQL by Example book [message #389789 is a reply to message #389785] Tue, 03 March 2009 10:06 Go to previous message
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
Previous Topic: Top 3 Sal.
Next Topic: Rows into Columns
Goto Forum:
  


Current Time: Sun Nov 03 07:47:16 CST 2024