Use of%type and%rowtype in Oracle

1,%TYPE

In order for a newly defined variable to conform to the data type of another defined variable (usually a column of a table), Oracle provides a way to define the%type, and when the data type of the referenced variable changes, the data type of the newly defined variable will change, This definition is similar to "VAR" in other programming languages, and its type is not determined by itself. Instead, it is determined by the object associated with it. This allows you to define the data type of the variable without modifying the SQL statement, and when you do not know exactly what type of variable it is.

DECLAREv_org_name Sf_org.org_name%TYPE;--same as Org_name typev_parent_id sf_org. parent_id%TYPE;--same as parent_id typeBEGIN    SELECTorg_name,parent_id intov_org_name,v_parent_id fromsf_org SoWHEREso.org_id=&org_id; Dbms_output. Put_Line ('Department Name:' ||v_org_name); Dbms_output. Put_Line ('Parent Department Code:' ||To_char (v_parent_id)); END;

2,%rowtype

If a table has more columns, using %rowtype to define a variable that represents a row of records in the table is much simpler than using%type to define the variables that represent the columns in the table, and it is not easy to omit and error. This increases the maintainability of the program.

To make the data type of a variable correspond to the data type of each column recorded in a table,Oracle provides a way to%rowtype the definition. When the data type of some columns of a table has changed, the data type of the newly defined variable will automatically follow its change, easily consistent, and without modifying the PL/SQL program. The data type of a variable can only be defined in this way when the structure of the referenced table and its data type are not known exactly.

A row of records can hold column data for the entire data row queried from one table or cursor. Each column of a row of records has the same name and data type as the columns of a row in the table.

DECLAREV_sf_org_rec sf_org%ROWTYPE;--same as each column in the sf_org tableBEGIN    SELECT *  intoV_sf_org_rec fromsf_org SoWHEREso.org_id=&org_id; Dbms_output. Put_Line ('Department ID:' ||To_char (v_sf_org_rec.org_id)); Dbms_output. Put_Line ('Department Name:' ||v_sf_org_rec.org_name); Dbms_output. Put_Line ('Parent Department Code:' ||To_char (V_sf_org_rec.  parent_id)); END;

Note: The above code runs in SQL Plus

Use of%type and%rowtype in Oracle