char data type --->display string [message #9834] |
Sun, 07 December 2003 19:58 |
confused
Messages: 5 Registered: December 2000
|
Junior Member |
|
|
how do i get the below to print 'cell phone' only when
contacttype FROM contact = c? contacttype is type char.
also how does one add data from another table, like if i have a person table, how would i add the persons name to the below?
confused
set serveroutput on
DECLARE
CURSOR MYCUR IS SELECT contacttype FROM contact;
contact_var MYCUR%ROWTYPE;
a CONTACT.CONTACTTYPE%TYPE;
BEGIN
OPEN MYCUR;
LOOP
dbms_output.enable;
FETCH MYCUR INTO contact_var;
EXIT WHEN MYCUR%NOTFOUND;
Dbms_output.put_line('cellphone');
END LOOP;
CLOSE MYCUR;
END;
.
run
|
|
|
Re: char data type --->display string [message #9838 is a reply to message #9834] |
Mon, 08 December 2003 01:12 |
|
Maaher
Messages: 7065 Registered: December 2001
|
Senior Member |
|
|
1. There is a cursor for loop which is more efficient, takes care of the open, fetch and close for you. It is used in the example below.
2. Let's assume your table looks like this:
SQL> desc CONTACT
Name Null? Type
------------------------------- -------- ----
CONTACT_ID NOT NULL NUMBER
CONTACT_TYPE VARCHAR2(1)
PERSON_ID NOT NULL NUMBER PERSON_ID refers to the persons_table. Then you could write something like:Declare
-- Create the cursor that fetches the names
-- for persons appearing in the contacts table
-- with contact_type "C"
Cursor c_contacts
Is
Select p.name person_name
From persons p
, contacts c
Where c.person_id = p.person_id
And c.contact_type = 'C';
Begin
-- the record variable needs no explicit declaration
For rec In c_contacts
Loop
-- display "cell phone"
dbms_output.put_line('cell phone');
-- display persons name
dbms_output.put_line('person: '||rec.person_name);
End Loop;
End;
/ Notice that the record variable is being declared implicitly, as is the OPEN, FETCH and CLOSE. This syntax is a lot easier, and you don't need to worry about EXIT WHEN... and things like that.
The code isn't tested, so typos are possible ;)
HTH,
MHE
|
|
|