Back to the basics: %TYPE and %ROWTYPE declaration
Posted by decipherinfosys on September 10, 2008
Normally in PL/SQL or in any programming language, when we declare a variable, we have to declare its data type as well whether variable is varchar or number or date. Oracle provides special qualifiers %TYPE and %ROWTYPE to declare the variables, which are going to hold values from the database columns or rows specifically. As usual we will start with our example of creating table.
CREATE TABLE TEST
%TYPE: We can use this qualifier to declare the variables, which are going to hold column values of the table. It can also be used to assign the data type to a variable, which is not going to hold the database value but can have same data type as of the column. Assume that we have following table. It always corresponds to specific column of the table. What is the benefit of using %TYPE?
• We don’t have to worry about exact data type of the column for which we are declaring variable.
• Also if later on, data type changes or column length gets increased or decreased, we don’t need to change the program. Oracle takes care of it automatically at run time.
%ROWTYPE: Unlike %TYPE, this qualifier or PL/SQL data type is used for entire row or group of columns, which are specified in cursor. Essentially it represents the record, which corresponds to the row in the database table. Again advantage of using %ROWTYPE is to avoid the declaration of individual variable for each column. One thing to remember is that we have to use dot (.) notation to reference the fields. Also database column and corresponding variable will have the same name and data type.
Following code snippet shows both %TYPE and %ROWTYPE declaration.
/* %TYPE variable declaration */
CURSOR C1 IS
SELECT ID, obj_type, obj_name, create_date
WHERE ROWNUM < 2;
FETCH C1 INTO test_rec;
Dbms_output.put_line(‘Object Name = ‘ || test_rec.obj_name);
Dbms_output.put_line(‘Object Type = ‘ || test_rec.obj_type);
WHEN OTHERS THEN
Apart from obvious advantages shown above, it also makes code concise and readable. Another major advantage which people are not aware is that one can insert the record using PL/SQL record type and can update the row as well using record type rather than stating the individual column list. We have covered this in detail in this blog entry.