How to Use % Type and % rowtype in Oracle PL/SQL (post)

Source: Internet
Author: User

How to Use % Type and % rowtype in Oracle PL/SQL
1. Use % Type
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 ))? This will cause all PL/SQL statements that use this column Code 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;
By using the % Type and v_firstname variables, the Data Type of the first_name column in the students table is the same (it can be understood as setting the two States ).
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 % rowtype
2.1 PL/SQL records
The PL/SQL record type is 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 point 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 record assignment
You can assign values to a record using the SELECT statement, 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.3 use % rowtype
It is common to declare 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
V_roomrecord rooms % rowtype;
A record will be defined, and the fields in the record will correspond to the columns in the rooms table

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.