select * into tp_2 from tb_test1 where field1 = 1; dbms_output.put_line(tp_2.field2);
dbms_output.put_line('a'); end;
%ROWTYPE Attribute The %ROWTYPE attribute provides a record type that represents a row in a table (or view). The record can store an entire row of data selected from the table or fetched by a cursor. In the example below, you declare two records. The first record stores a row selected from the "emp" table. The second record stores a row fetched by the "c1" cursor. DECLARE emp_rec emp%ROWTYPE; CURSOR c1 IS SELECT deptno, dname, loc FROM dept; dept_rec c1%ROWTYPE; ... Columns in a row and corresponding fields in a record have the same names and datatypes. In the following example, you select column values into a record named "emp_rec": DECLARE emp_rec emp%ROWTYPE; ... BEGIN SELECT * INTO emp_rec FROM emp WHERE ... ... END; The column values returned by the SELECT statement are stored in fields. To reference a field, you use dot notation. For example, you might reference the "deptno" field as follows: IF emp_rec.deptno = 20 THEN ... In addition, you can assign the value of an expression to a specific field, as the following examples show: emp_rec.ename := 'JOHNSON'; emp_rec.sal := emp_rec.sal * 1.05; Aggregate Assignment A %ROWTYPE declaration cannot include an initialization clause. However, there are two ways to assign values to all fields in a record at once. First, PL/SQL allows aggregate assignment between entire records if their declarations refer to the same table or cursor. For example, the following assignments are legal: DECLARE dept_rec1 dept%ROWTYPE; dept_rec2 dept%ROWTYPE; CURSOR c1 IS SELECT deptno, dname, loc FROM dept; dept_rec3 c1%ROWTYPE; dept_rec4 c1%ROWTYPE; BEGIN ... dept_rec1 := dept_rec2; dept_rec4 := dept_rec3; ... But, because "dept_rec2" is based on a table and "dept_rec3" is based on a cursor, the following assignment is illegal: dept_rec2 := dept_rec3; -- illegal Second, you can assign a list of column values to a record by using the SELECT or FETCH statement, as the example below shows. The column names must appear in the order in which they were defined by the CREATE TABLE or CREATE VIEW statement. DECLARE dept_rec dept%ROWTYPE; ... BEGIN SELECT deptno, dname, loc INTO dept_rec FROM dept WHERE deptno = 30; ... END; However, you cannot assign a list of column values to a record by using an assignment statement. So, the following syntax is illegal: record_name := (value1, value2, value3, ...); -- illegal Although you can retrieve entire records, you cannot insert them. For example, the following statement is illegal: INSERT INTO dept VALUES (dept_rec1); -- illegal Using Aliases Select-items fetched by a cursor associated with %ROWTYPE must have simple names or, if they are expressions, must have aliases. In the following example, you use an alias called "wages": DECLARE CURSOR my_cursor IS SELECT sal + NVL(comm, 0) wages, ename FROM emp; my_rec my_cursor%ROWTYPE; BEGIN OPEN my_cursor; LOOP FETCH my_cursor INTO my_rec; EXIT WHEN my_cursor%NOTFOUND; IF my_rec.wages > 2000 THEN INSERT INTO temp VALUES (NULL, my_rec.wages, my_rec.ename); END IF; END LOOP; CLOSE my_cursor; END; See also: Datatypes, %TYPE Attribute, Records, Assignments
TP_2 tb_test1%ROWTYPE;begin
select * into tp_2 from tb_test1 where field1 = 1;
dbms_output.put_line(tp_2.field2);
dbms_output.put_line('a');
end;
in a table (or view). The record can store an entire row of data
selected from the table or fetched by a cursor. In the example below,
you declare two records. The first record stores a row selected from
the "emp" table. The second record stores a row fetched by the "c1"
cursor. DECLARE
emp_rec emp%ROWTYPE; CURSOR c1 IS SELECT deptno, dname, loc FROM dept;
dept_rec c1%ROWTYPE;
... Columns in a row and corresponding fields in a record have the same
names and datatypes. In the following example, you select column
values into a record named "emp_rec": DECLARE
emp_rec emp%ROWTYPE;
...
BEGIN
SELECT * INTO emp_rec FROM emp WHERE ... ...
END; The column values returned by the SELECT statement are stored in
fields. To reference a field, you use dot notation. For example,
you might reference the "deptno" field as follows: IF emp_rec.deptno = 20 THEN ... In addition, you can assign the value of an expression to a specific
field, as the following examples show: emp_rec.ename := 'JOHNSON';
emp_rec.sal := emp_rec.sal * 1.05; Aggregate Assignment A %ROWTYPE declaration cannot include an initialization clause.
However, there are two ways to assign values to all fields in a record
at once. First, PL/SQL allows aggregate assignment between entire
records if their declarations refer to the same table or cursor.
For example, the following assignments are legal: DECLARE
dept_rec1 dept%ROWTYPE;
dept_rec2 dept%ROWTYPE; CURSOR c1 IS SELECT deptno, dname, loc FROM dept;
dept_rec3 c1%ROWTYPE;
dept_rec4 c1%ROWTYPE;
BEGIN
...
dept_rec1 := dept_rec2;
dept_rec4 := dept_rec3;
... But, because "dept_rec2" is based on a table and "dept_rec3" is based
on a cursor, the following assignment is illegal: dept_rec2 := dept_rec3; -- illegal Second, you can assign a list of column values to a record by using the
SELECT or FETCH statement, as the example below shows. The column names
must appear in the order in which they were defined by the CREATE TABLE
or CREATE VIEW statement. DECLARE
dept_rec dept%ROWTYPE;
...
BEGIN
SELECT deptno, dname, loc INTO dept_rec FROM dept
WHERE deptno = 30; ...
END; However, you cannot assign a list of column values to a record by using
an assignment statement. So, the following syntax is illegal: record_name := (value1, value2, value3, ...); -- illegal Although you can retrieve entire records, you cannot insert them. For
example, the following statement is illegal: INSERT INTO dept VALUES (dept_rec1); -- illegal
Using Aliases Select-items fetched by a cursor associated with %ROWTYPE must have
simple names or, if they are expressions, must have aliases. In the
following example, you use an alias called "wages": DECLARE
CURSOR my_cursor IS SELECT sal + NVL(comm, 0) wages, ename
FROM emp;
my_rec my_cursor%ROWTYPE;
BEGIN
OPEN my_cursor;
LOOP
FETCH my_cursor INTO my_rec; EXIT WHEN my_cursor%NOTFOUND;
IF my_rec.wages > 2000 THEN
INSERT INTO temp VALUES (NULL, my_rec.wages,
my_rec.ename);
END IF;
END LOOP;
CLOSE my_cursor;
END;
See also: Datatypes, %TYPE Attribute, Records, Assignments