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
Product-specific Packages The Oracle and different Oracle tools are supplied with the product-specific packages which help you to build the PL/SQL-based applications. For illu

CHECK Constraints in SQL A CHECK constraint is a table constraint defined using the key word CHECK, as already illustrated in several examples in this chapter. In particular,

Example of NOT EXISTS Operator - SQL Example is a translation into SQL of the corresponding example, which is included there merely to show that for any scalar comparison the

PITS Depressions in secondary cell wall is called pit. A pit present on the free cell wall surface without its partner is called Blind pit. It consists of 2 parts -

Controlling Autonomous Transactions The first SQL statement in an autonomous routine starts a transaction. Whenever one transaction ends, the next SQL statement starts the oth

Product-specific Packages The Oracle and different Oracle tools are supplied with the product-specific packages which help you to build the PL/SQL-based applications. For illus

Parameter Modes: You do not require to specify a parameter mode for the input bind arguments (those used, for illustration, in the WHERE clause) as the mode defaults to IN. Th

EXIT Statement   You can use the EXIT statement to exit a loop. The EXIT statement has 2 forms: the conditional EXIT WHEN and the unconditional EXIT. With the either form, you

Manipulating Individual Elements Faraway you have manipulated an entire collection. Within the SQL, to manipulate the individual elements of the collection, and then use the ope

Obtaining a natural join by specifying the common columns Synatax: SELECT * FROM IS_CALLED JOIN IS_ENROLLED_ON USING ( StudentId ) However, a named columns join doe