Home » SQL & PL/SQL » SQL & PL/SQL » type and rowtype
type and rowtype [message #599373] Thu, 24 October 2013 00:14 Go to next message
nataliafoster26
Messages: 64
Registered: October 2013
Member
this must be one of the most ask question, from research i done
just to get it straight here is a example
(
pnLeadID p_lead.lead_id%TYPE ,
)
IS
v_lead_rec p_lead%ROWTYPE;

v_lead_rec := dml_p_lead.get_rec(pnLeadID);

-- %TYPE is used to declare a field with the same type as
-- that of a specified table's column:
-- %ROWTYPE is used to declare a record with the same types as
-- found in the specified database table, view or cursor:
so pnleadid it gets the collumn in p_lead table
so for v_lead_rec is saying what ever is in the collumn pnleadid =v_lead_rec

did i get it right ? correct me if im wrong
thanks
Re: type and rowtype [message #599375 is a reply to message #599373] Thu, 24 October 2013 00:26 Go to previous messageGo to next message
Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
"pnLeadID p_lead.lead_id%TYPE" says that "pnLeadID" variable is of the same datatype as a column named "lead_id" that belongs to "p_lead" table.
Suppose that this table is created as
create table p_lead
  (lead_id      number(2),
   lead_name    varchar2(20)
  );

You could have declared "pnleadid" to be NUMBER(2). However, some day in the future, you figure out that you need to store 100 into that column. All your code that uses declaration "pnLeadID number(2)" would fail, and you'd have to modify it all over your code.

But, if you declare it as above - "pnLeadID p_lead.lead_id%type", no problem.


As of "v_lead_rec p_lead%ROWTYPE", it says that this variable is supposed to contain the whole record that belongs to "p_lead" table, which consists of two columns: "lead_id" and "lead_name". It means that you can
select * into v_lead_rec from p_lead where ...
i.e. you don't have to declare two (or many, if table contains many columns) variables to manipulate those values; see an example & note the difference:
declare
  l_lead_id   p_lead.oead_id%type;
  l_lead_name p_lead.lead_name%type;

  l_lead_rec  p_lead%rowtype;
begin
  select lead_id, lead_name
    into l_lead_id, l_lead_name
    from p_lead
    where ...

  select *
    into l_lead_rec
    from p_lead
    where ...

  dbms_output.put_line(l_lead_rec.lead_id);
  dbms_output.put_line(l_lead_rec.lead_name);
end;
Re: type and rowtype [message #599379 is a reply to message #599375] Thu, 24 October 2013 00:52 Go to previous messageGo to next message
nataliafoster26
Messages: 64
Registered: October 2013
Member
can you comment on this line
v_lead_rec := dml_p_lead.get_rec(pnLeadID);
if im correct
it takes an input of pnLEND_ID and returns the matching row for the input pnLEND_ID from the P_LEND table as a record.

[Updated on: Thu, 24 October 2013 00:56]

Report message to a moderator

Re: type and rowtype [message #599380 is a reply to message #599379] Thu, 24 October 2013 01:06 Go to previous message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
if im correct it takes an input of pnLEND_ID and returns the matching row for the input pnLEND_ID from the P_LEND table as a record.


How can we know what the function does? It is your function, read its code.
The only thing we can say is that it takes something that is of the same type than p_lead.lead_id column and returns something that is of the same type than a row of p_lead table.

Previous Topic: How to extract first 3 characters from each word in a string/sentence and separate with underscore?
Next Topic: How to update character set of a excel sheet through pl/sql
Goto Forum:
  


Current Time: Thu Apr 25 17:15:16 CDT 2024