Defining records, PL-SQL Programming

Defining and Declaring Records

To create records, you have to define a RECORD type, and then declare records of that type. You may also define RECORD types in the declarative part of any PL/SQL subprogram, block,  or package using the syntax as shown:

TYPE type_name IS RECORD (field_declaration[,field_declaration]...);

where field_declaration stands for

field_name field_type [[NOT NULL] {:= | DEFAULT} expression]

And where the type_name is a type specifier used later to declare the records, field_ type is any PL/SQL datatype except REF CURSOR, and expression yields a value of the similar type as the field_type.

Note:  Unlike the TABLE and VARRAY types, the RECORD types cannot be CREATED and stored in the database.

You can use %TYPE & %ROWTYPE to state the field types. In the illustration shown below, you define a RECORD type named DeptRec:



dept_id dept.deptno%TYPE,

dept_name VARCHAR2(15),

dept_loc VARCHAR2(15));

Notice that the field declarations are just like the variable declarations. Each field has an exclusive name and specific datatype. Therefore, the value of a record is actually a collection of values, each of some of the simpler type.

As the illustration below shows, the PL/SQL defines records that contain collections, objects, and other records (known as the nested records). Though, the object types cannot have attributes of type RECORD.



seconds SMALLINT,

minutes SMALLINT,

hours SMALLINT);


flight_no INTEGER,

plane_id VARCHAR2(10),

captain Employee, -- declare object

passengers PassengerList, -- declare varray

depart_time TimeRec, -- declare nested record

airport_code VARCHAR2(10));

The illustration later shows that you can specify a RECORD type in the RETURN clause of a function specification. That permits the function to return a user-defined record of the same type.



emp_id INTEGER

last_name VARCHAR2(15),

dept_num INTEGER(2),

job_title VARCHAR2(15),

salary REAL(7,2));


FUNCTION nth_highest_salary (n INTEGER) RETURN EmpRec IS...

Posted Date: 10/4/2012 3:35:49 AM | Location : United States

Related Discussions:- Defining records, Assignment Help, Ask Question on Defining records, Get Answer, Expert's Help, Defining records Discussions

Write discussion on Defining records
Your posts are moderated
Related Questions
CURRVAL and NEXTVAL The series is a schema object which generates the sequential numbers. Whenever you form a sequence, you can specify its primary value and an increment. T

SQL Is a Database Language: The commands given to a DBMS by an application are written in the database language of the DBMS. The term data sublanguage is sometimes used instea

Manipulating Objects: You can use an object type in the CREATE TABLE statement to indicate the datatype of a column. When the table is created once, you can use the SQL statem

Using %TYPE The %TYPE attribute gives the datatype of a variable or the database column. In the example below, the %TYPE gives the datatype of a variable: credit REAL(7,2); debi

UNION ALL - SQL Further varieties of UNION arise when we replace the key word DISTINCT by ALL in any of the foregoing examples, as in Example. ALL specifies that if row r appe

Relational Algebra - SQL It describes some operators, that together constitute an algebra that is not only relationally complete but also irreducibly so (very nearly- apart f

Comparison Operators The Comparison operators can compare one expression to another. The outcome is always true, false, or null. Usually, you use a comparison operators in condi

Declaring Records Whenever you define a RECORD type, you may declare records of that type, as the illustration shows: DECLARE TYPE StockItem IS RECORD ( item_no INTEG

Magento change address format depending on store Project Description: What I need is that depending on the store in which the customer bought the address should change the fo

Parameter and Keyword Description: label_name: This is an undeclared identifier which optionally labels a loop. When used, the label_name should be enclosed by double ang