Oracle development topics: % Type and % rowtype

1.Use% Type

In many cases,PL/SQLVariables 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,StudentsTableFirst_nameThe column type isVarchar2 (20 ),We can declare a variable as follows:

Declare

V_firstname varchar2 (20 );

However, ifFirst_nameWhat will happen when the column definition is changed (for example, the table is changed,First_nameThe current type is changedVarchar2 (25))? This will cause allPL/SQLCodeMust be modified. If you have manyPL/SQLCode, which may be time-consuming and error-prone.

In this case, you can use"% Type"Attribute instead of hard encoding the variable type.

For example:

Declare

V_firstnameStudents. first_name % Type;

 

Use% Type, v_firstnameThe variable will be the sameStudentsTableFirst_nameThe column type is the same (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.

Use% TypeIs a very good programming style, because it makesPL/SQLIt is more flexible and more suitable for updating database definitions.

 

2.Use% Rowtype

 

2.1 PL/SQLRecord

PL/SQLThe record type is similarCThe structure in the language is a composite type that is 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 );

 

This3Variables are logically correlated because they pointStudentsDifferent fields in the 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.2Record assignment

AvailableSelectThe statement assigns a value to the record, which retrieves data from the database and stores the data in the record. Note that the fields in the record should match those in the query result list.

Select studentid, firstname, lastname

Into v_studentinfo

From students where studentid = 32;

 

2.3Use% Rowtype

 

InPL/SQLDeclare a record as havingDatabase rows of the same typeIs very common.PL/SQLProvided% RowtypeTo make this operation more convenient.

For example:

Declare

V_roomrecord rooms % rowtype;

A record will be defined, and the fields in this record willRoomsThe columns in the table correspond to each other.