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:

DECLARE

TYPE DeptRec IS RECORD (

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.

DECLARE

TYPE TimeRec IS RECORD (

seconds SMALLINT,

minutes SMALLINT,

hours SMALLINT);

TYPE FlightRec IS RECORD (

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.

DECLARE

TYPE EmpRec IS RECORD (

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
Effects of NULL The numeric variable X, perhaps of type INTEGER, might be assigned NULL. In that case the result of evaluating X + 1 is NULL, and so SET Y = X + 1 assigns NULL

UNNEST operator in SQL The inverse operator of GROUP is UNGROUP. SQL has an operator, UNNEST, that can be used for similar purposes, but its method of invocation is somewhat p

Many of the reports generated from the system calculate the total dollars in a shopper''s purchases. Follow these steps to create a function named TOT_PURCH_SF that accepts a shopp

Fetching Across Commits The FOR UPDATE clauses acquire exclusive all row locks. All rows are locked when you open the cursor, and when you commit your transaction they are unl

Recursive Subprograms The recursive subprogram is the one that calls itself. Think of a recursive call as a call to a few other subprograms that does the similar task as your

Block Structure The PL/SQL is a block-structured language. That is, the fundamental units (procedures, anonymous blocks, and functions) that make up a PL/SQL program are logi

Project Description: I have two types of data sources. One that is a list in SharePoint and another that is an access desktop database. The access desktop database is fairly com

The Package Body The package specification is implemented by the package body. That is, the package body has the definition of every cursor and the subprogram declared in the p

IN Mode An IN parameter pass the values to the subprogram being called. Within the subprogram, an IN parameter acts like a constant. And hence, it cannot be assigned a value.

NULL Statement The NULL statement clearly specifies in action; it does nothing other than to pass control to the next statement. It can, though, improve the readability. In a