Use of % TYPE and % ROWTYPE in ORACLE, % type % rowtype

Use of % TYPE and % ROWTYPE in ORACLE, % type % rowtype
 1% TYPE description

To make the data TYPE of a variable consistent with that of another defined variable (especially a column in a table), Oracle provides the % TYPE definition method. When the data type of the referenced variable changes, the Data Type of the newly defined variable automatically follows the change, which is easy to be consistent and does not need to be modified by the PL/SQL program. When you cannot accurately know the Data Type of the referenced variable, you can only use this method to define the data type of the variable.

2% ROWTYPE description

If a table has many columns, use % ROWTYPE to define a variable that represents a record in a row of the table, which is much simpler than using % TYPE to define the variables of each column in the table, it is not easy to omit or make mistakes. This will increase the maintainability of the program.

To make the data type of a variable consistent with the data type of each column recorded in a table, Oracle provides the % ROWTYPE definition method. When the data type of some columns in the table changes, the Data Type of the newly defined variable automatically follows the change, which is easy to be consistent and does not need to be modified by the PL/SQL program. When you cannot know the structure and data type of the referenced table, you can only use this method to define the data type of the variable.

A row of records can store the column data of the entire data row queried from a table or cursor. Each column in a record has the same name and data type as each column in a table.

3. Example 3.1 Data Preparation

 

-- Create table SF_ORG (ORG_ID int not null, -- Organization primary key IDORG_NAME VARCHAR2 (50), -- Organization Name PARENT_ID INT -- Organization parent level) -- insert into SF_ORG (ORG_ID, ORG_NAME, PARENT_ID) VALUES (1, 'level 1 Department 1', 0); -- level 2 Department insert into SF_ORG (ORG_ID, ORG_NAME, PARENT_ID) VALUES (2, 'second-level department 2', 1); insert into SF_ORG (ORG_ID, ORG_NAME, PARENT_ID) VALUES (3, 'second-level department 3', 1 ); insert into SF_ORG (ORG_ID, ORG_NAME, PARENT_ID) VALUES (4, 'level 2 Department 4', 1 );

3.2% TYPE

Declare two variables with the same data type as the PARENT_ID and ORG_NAME columns in the SF_ORG table, and then use the replace variable & ORG_ID to accept the input Organization Code, query and display the Organization Name and parent department ID. Note: When using a variable defined by % TYPE, use the "." operator to specify the table name qualifier.

Shows the execution result:

The SQL statement in type01.txt is as follows:

 

DECLARE V_ORG_NAME BEGIN % TYPE; -- same as ORG_NAME V_PARENT_ID SF_ORG.PARENT_ID % TYPE; -- same as PARENT_ID begin select ORG_NAME, PARENT_ID INTO V_ORG_NAME, V_PARENT_ID FROM SF_ORG so where so. ORG_ID = & ORG_ID; DBMS_OUTPUT.PUT_LINE ('department name: '| V_ORG_NAME); DBMS_OUTPUT.PUT_LINE ('superior department code:' | TO_CHAR (V_PARENT_ID); END;

1.1.3.3% ROWTYPE

Declare the row record variable V_SF_ORG_REC, which has the same column name and data type as in the SF_ORG table, and then use the replace variable & ORG_ID to accept the input Organization Code, query and display the Organization Name and parent department ID. Note: When using a variable defined by % ROWTYPE, use the "." operator to specify a variable name qualified word.

Shows the execution result:

The SQL statement in rowtype01.txt is as follows:

DECLARE V_SF_ORG_REC SF_ORG % ROWTYPE; -- same as columns in the SF_ORG table, begin select * INTO V_SF_ORG_REC FROM SF_ORG so where so. ORG_ID = & ORG_ID; DBMS_OUTPUT.PUT_LINE ('department ID: '| TO_CHAR (bytes); DBMS_OUTPUT.PUT_LINE ('department name:' | bytes); DBMS_OUTPUT.PUT_LINE ('superior department code: '| TO_CHAR (V_SF_ORG_REC.PARENT_ID); END;

Oracle % rowtype usage

This indicates that the row data type stores a row of data. A row of data can contain multiple columns, similar to a row of data in the table or a row of data in the cursor, for example:
Vs_row1 table % rowtype;
Vs_row2 cursor % rowtype;

[Switch] How to Use % TYPE and % ROWTYPE in Oracle PL/SQL

In many cases, PL/SQL variables can be used to store data in database tables. In this case, the variable should have the same type as the table column. For example, if the first_name column type of the students table is VARCHAR2 (20), we can DECLARE a variable as follows: DECLARE v_FirstName VARCHAR2 (20 ); but what will happen if the definition of the first_name column is changed (for example, if the table is changed, the current type of first_name is changed to VARCHAR2 (25 ))? All PL/SQL code that uses this column must be modified. If you have a lot of PL/SQL code, such processing may be time-consuming and error-prone. In this case, you can use the "% TYPE" attribute instead of hard encoding the variable TYPE. For example, DECLARE v_FirstName students. first_name % TYPE; Use the % TYPE and v_FirstName variables to share the same TYPE as the first_name column in the students table (it can be understood that the two are bounded ). This type is determined every time an anonymous block or nameblock runs the statement block and compiles stored objects (processes, functions, packages, object classes, and triggers. Using % TYPE is a good programming style because it makes PL/SQL more flexible and more suitable for updating database definitions. 2. Use % ROWTYPE2.1 PL/SQL to record PL/SQL record types similar to the structure in C language. It is a composite type and user-defined. Record provides a mechanism for processing independent variables, but also as a whole unit-related variable. See DECLARE v_StudentID NUMBER (5); v_FirstName VARCHAR2 (20); v_LastName VARCHAR2 (20); these three variables are logically correlated, because they direct to different fields in the students table. If a record type is declared for these variables, the relationship between them is obvious and can be processed as a unit. DECLARE/* Define a record type to hold common student informationi */TYPE t_StudentRecord is record (StudentID NUMBER (5), FirstName VARCHAR2 (20), LastName VARCHAR2 (20 ); /* Declare a variable of this type. */v_StudentInfo t_StudentRecord; 2.2 you can assign values to records using the SELECT statement. This will retrieve data from the database and store the data in records. Note that the fields in the record should match those in the query result list. SELECT studentID, firstName, lastNameinto v_StudentInfofrom students where studentID = 32; 2.3 it is common to declare a record as a database row of the same type in PL/SQL using % ROWTYPE. PL/SQL provides the % ROWTYPE operator to facilitate such operations.