TYPE AND ROWTYPE

[EUMAGNUN]

Can someone explain the difference between these two guys:?:

Select all

%TYPE   %ROWTYPE

[Tineks]

% Type you use to inherit the characteristics of a field, the% rowtype already inherits the characteristics of several fields of one once, follows 2 examples, in the background the two do exactly the same thing, but one has% type and The other% rowtype.
Note: You will see that the first example has a more extensive code, as it was declared the variables using the% type, in the second example, using the% rowtype the code was more filled .. but there comes the common sense tb, if you are to set up a query to seek a single field do not support% type, now if you are to get multiple fields there already compensates to use the% rowtype ..

example1

Select all

 
DECLARE 
   CURSOR cur_DADOS (p_NOME_CARRO MENS_ERRO.NOME%TYPE) IS 
   SELECT ID, NOME, ID_CARRO, DATA 
     FROM MENS_ERRO; 
    
   v_ID        MENS_ERRO.ID%TYPE;   
   v_NOME      MENS_ERRO.NOME%TYPE; 
   v_ID_CARRO  MENS_ERRO.ID_CARRO%TYPE; 
   v_DATA      MENS_ERRO.DATA%TYPE; 
BEGIN 
   OPEN  cur_DADOS ('GOL'); 
   FETCH cur_DADOS INTO v_ID, v_NOME, v_ID_CARRO, v_DATA; 
   CLOSE cur_DADOS; 
END; 

]

Example2

Select all

 
DECLARE 
   CURSOR cur_DADOS (p_NOME_CARRO MENS_ERRO.NOME%TYPE) IS 
   SELECT * 
     FROM MENS_ERRO; 
    
   v_DADOS     cur_DADOS%ROWTYPE; 
BEGIN 
   OPEN  cur_DADOS ('GOL'); 
   FETCH cur_DADOS INTO v_DADOS; 
   CLOSE cur_DADOS;    
END; 

's

[gokden]

Explaining for you is the following ...
The% Type inherits the values ??of a table field. In the same way that is in Example 1
I had never thought and nor heard of using% rowtype the way it is in Example 2
plus the% rowtype, inherits the values ??of an 'array Type 'or a bank table ...
Example: In creating a Type

Select all

 
type t_tab_rec_privs_neg is table of privs_neg%rowtype index by binary_integer; 

He created a Type with the same fields as the table in Rowtype,
tended ?? = D

MORE In this example 2 I do not know what will happen to the v_dados
more tenho printing that it becomes an array with the cursor fields 'curp_dados'.

Exile: If the Cur_dados had another field, the V: V-Date would turn an array based on the fields that was declared at the time of creating the cursor ...
I think this:
Let's wait for someone else's experienced to explain = D

[Tineks]

I had never thinking and nor heard of using% rowtype the way it is in Example 2

In this example we will have the following result.

Select all

 
SQL> select * from mens_erro; 
 
NOME                                      ID_CARRO DATA             ID 
---------------------------------------- --------- --------- --------- 
Alberto                                          1 12-DEC-07         1 
Maria                                            2 09-DEC-07         2 
Zuleica                                          1 15-DEC-07         3 
Claudia                                          2 30-DEC-07         4 
 
SQL> DECLARE  
  2     CURSOR cur_DADOS (p_ID_CLIENTE MENS_ERRO.ID%TYPE) IS  
  3     SELECT *  
  4       FROM MENS_ERRO 
  5      WHERE ID = p_ID_CLIENTE; 
  6       
  7     v_DADOS     cur_DADOS%ROWTYPE;  
  8  BEGIN  
  9     OPEN  cur_DADOS (1);  
 10     FETCH cur_DADOS INTO v_DADOS;  
 11     CLOSE cur_DADOS;     
 12      
 13     dbms_output.put_line(' # '||v_DADOS.ID); 
 14     dbms_output.put_line(' Nome....: '||v_DADOS.NOME); 
 15     dbms_output.put_line(' Data....: '||v_DADOS.DATA); 
 16     dbms_output.put_line(' IdCarro.: '||v_DADOS.ID_CARRO); 
 17  END; 
 18  / 
# 1 
Nome....: Alberto 
Data....: 12-DEC-07 
IdCarro.: 1 
 
PL/SQL procedure successfully completed. 

As you said, you can be used as array ..

Below is another example with RowType, but using it as a funcao return. .

Select all

 
SQL> DECLARE 
  2   
  3     r_RESULTADO   mens_erro%ROWTYPE;  
  4   
  5     FUNCTION fun_RETORNA_DADOS (p_ID  MENS_ERRO.ID%TYPE) RETURN mens_erro%ROWTYPE IS 
  6        v_DADOS   mens_erro%ROWTYPE;  
  7     BEGIN 
  8        SELECT *  
  9          INTO v_DADOS 
 10          FROM MENS_ERRO WHERE ID = p_ID; 
 11         
 12        RETURN v_DADOS; 
 13     END;      
 14   
 15  BEGIN 
 16     r_RESULTADO := fun_RETORNA_DADOS(1); 
 17      
 18     dbms_output.put_line(' Nome : '||r_RESULTADO.NOME); 
 19     dbms_output.put_line(' Data : '||r_RESULTADO.DATA); 
 20     dbms_output.put_line(' IdCar: '||r_RESULTADO.ID_CARRO); 
 21  END; 
 22  / 
Nome : Alberto 
Data : 12-DEC-07 
IdCar: 1 
 
PL/SQL procedure successfully completed. 

[] 's

[EUMAGNUN]

What would become a Array?

[dr_gori]

Array is a variable that holds a series of items by changing only her "index" ...

Example:

Select all

vnome[1] := 'Joao'; 
vnome[2] := 'Pedro';

etc.

See this link: http://en.wikipedia.org/wiki/Array

[wmendes.miranda]

Cool,

did not know that% rowtype could also be used as a cursor, thanks for the explanation !!!