Difference between % TYPE and % ROWTYPE in PL/SQL, % type % rowtype

Difference between % TYPE and % ROWTYPE in PL/SQL, % type % rowtype

% TYPE:

Defines a variable. Its data type is the same as that of a defined data variable or the data type of a column in a database table.
In this case, % TYPE can be used.
The advantages of using the % TYPE feature are:

1. You do not need to know the Data Type of the referenced database column;
2. The data type of the referenced database column can be changed in real time.

Declare -- Define a record type emp_record is record (v_name employees. last_name % type, v_email employees. email % type, v_salary employees. salary % type, v_job_id employees. job_id % type); -- declare the variable v_emp_record emp_record of the custom record type; begin -- use select... into... select last_name, email, salary, job_id into v_emp_record from employees where employee_id = 101; -- print the variable value dbms_output.put_line (v_emp_record.v_name | ', '| v_emp_record.v_email |', '| v_emp_record.v_salary |', '| v_emp_record.v_job_id); end;

Returned results:

Kochhar, NKOCHHAR, 17000, AD_VPPL/SQL procedure successfully completed

% ROWTYPE:
PL/SQL provides the % ROWTYPE operator to return a record type. The data type is consistent with the data structure of the database table.
The advantages of using the % ROWTYPE feature are:
1. You do not need to know the number and Data Type of columns in the referenced database;
2. The number and Data Type of columns in the referenced database can be changed in real time.

Declare -- declare a record type variable v_emp_record employees % rowtype; begin -- use select... into... select * into v_emp_record from employees where employee_id = 101; -- print the variable value dbms_output.put_line (v_emp_record.last_name | ',' | v_emp_record.email | ', '| v_emp_record.salary |', '| v_emp_record.job_id |', '| v_emp_record.hire_date); end;

Returned results:

Kochhar, NKOCHHAR, 17000, AD_VP,-89PL/SQL procedure successfully completed

Record type:
The RECORD type stores logical data as a unit, called the PL/SQL RECORD FIELD. It stores different but logical information.
Syntax for defining record types is as follows:
TYPE record_type IS RECORD

(
Field1 type1 [not null] [: = exp1],
Field2 type2 [not null] [: = exp2],
......
Fieldn typen [not null] [: = expn]

);

Tip: 1) The function of the DBMS_OUTPUT.PUT_LINE process is similar to that of System. out. println () in Java, which directly sends the output result to the standard output.
2) before using the preceding procedure, you must set the Environment Parameter SERVEROUTPUT of SQL * PLUS to ON. Otherwise, the output result will not be displayed:

set serveroutput on

You can use the SELECT statement to assign values to record variables, as long as the record field matches the field in the query result list.

In brief, % type in pl/SQL is different from % rowtype.

% Type matching Field type
% Rowtype matching record type
 
[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.