Description of % TYPE and % ROWTYPE in Oracle

% TYPE and % ROWTYPE in Oracle 1. Using % TYPE www.2cto.com in many cases, variables should have the same TYPE as columns. For example, if the name column type of the emp table is VARCHAR2 (20), we can DECLARE a variable: DECLARE var_Name VARCHAR2 (20) using a hard-coded statement, however, if the definition of the name column changes, for example, changing the type of the name field to VARCHAR2 (50), all PL/SQL code that uses this variable must be modified. In addition, if you have a lot of PL/SQL code, this kind of processing may be tedious and error-prone. In this case, you can use the hard encoding of the "% TYPE" attribute, similar to java Development, we are used to defining some constants, such as public static final String arg = 'your name'. For example, DECLARE var_name emp. name % TYPE; Use the % TYPE and var_name variable to bind the TYPE of the name column of the emp table, in this way, the object type is determined every time an anonymous block, named block, or stored procedure runs the statement block and compiles the stored object (process, function, package, object class, and trigger, this can be used even if the table definition is modified. Using % TYPE is a good programming style, because it makes PL/SQL more flexible and more suitable for updating database definitions. 2. replace with % ROWTYPE: PL/SQL record types are user-defined. records provide a mechanism for processing independent variables related to the overall unit, it is similar to defining a model class in java development. DECLARE/* Define a record type to hold common student informationi */TYPE t_empRecord is record (var_empId NUMBER (5), var_name VARCHAR2 (20 )); /* Declare a variable of this type. */v_empInfo t_empRecord; assign values to records using the SELECT statement, which retrieves data from the database and stores the data in records. Note that the fields in the record should match those in the query result list. SELECT emp_id, name into v_empInfo from emp where emp_id = 32; % ROWTYPE declares a record as a database row of the same type in PL/SQL. PL/SQL provides the % ROWTYPE operator to facilitate such operations. For example, DECLARE var_emp emp % ROWTYPE; a record is defined and the fields in the record correspond to columns in the emp table. One row of the CURSOR: create or replace procedure PRO_TEMPLATE as limit INTEGER; CURRENT_MAX_ID limit % TYPE; CURSOR TEMPLATE_CUR is select TEMPLET_NAME, TEMPLET_CONTENT FROM SYS_TEMPLET; TEM_INSTANCE TEMPLATE_CUR % ROWTYPE; begin null; end;