%TYPE %ROWTYPE
TYPE AND ROWTYPE
-
- Rank: DBA Sênior
- Posts: 365
- Joined: Tue, 24 May 2005 2:24 pm
- Location: Araraquara - SP
Cristiano (Tineks)
Araraquara - SP
Araraquara - SP
% 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]
Example2
's
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
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
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;
-
- Rank: DBA Pleno
- Posts: 264
- Joined: Sun, 19 Aug 2007 8:18 pm
- Location: Ribeirão Preto - SP
Lucas de Souza
OCA Developer
Analista de sistemas
OCA Developer
Analista de sistemas
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
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
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
type t_tab_rec_privs_neg is table of privs_neg%rowtype index by binary_integer;
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
-
- Rank: DBA Sênior
- Posts: 365
- Joined: Tue, 24 May 2005 2:24 pm
- Location: Araraquara - SP
Cristiano (Tineks)
Araraquara - SP
Araraquara - SP
In this example we will have the following result.gokden wrote: I had never thinking and nor heard of using% rowtype the way it is in Example 2
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.
Below is another example with RowType, but using it as a funcao return. .
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.
- dr_gori
- Moderador
- Posts: 5024
- Joined: Mon, 03 May 2004 3:08 pm
- Location: Portland, OR USA
- Contact:
Thomas F. G
Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered
Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered
Array is a variable that holds a series of items by changing only her "index" ...
Example:
etc.
See this link: http://en.wikipedia.org/wiki/Array
Example:
vnome[1] := 'Joao';
vnome[2] := 'Pedro';
See this link: http://en.wikipedia.org/wiki/Array
-
- Rank: Estagiário Sênior
- Posts: 11
- Joined: Thu, 26 Aug 2010 10:51 am
- Location: São Paulo
- Contact:
Cool,
did not know that% rowtype could also be used as a cursor, thanks for the explanation !!!
did not know that% rowtype could also be used as a cursor, thanks for the explanation !!!
-
- Information
-
Who is online
Users browsing this forum: No registered users and 1 guest