Defining records, PL-SQL Programming

Assignment Help:

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...


Related Discussions:- Defining records

Using operator ref - manipulating objects pl sql, Using Operator REF: ...

Using Operator REF: You can retrieve refs by using the operator REF that, like VALUE, takes as its argument a correlation variable. In the illustration below, you retrieve one

Effects of null for unique specification - sql, Effects of NULL for UNIQUE ...

Effects of NULL for UNIQUE Specification When a UNIQUE specification u for base table t includes a column c that is not subject to a NOT NULL constraint, the appearance of sev

Albeit simple method , Albeit simple method : These all the truth tabl...

Albeit simple method : These all the truth tables give us our first as albeit simple method for proving a theorem: where check whether it can be written in propositional logic

Assignment source not a literal - variable, Assignment Source Not a Literal...

Assignment Source Not a Literal - Variable Syntax: SET SN = SID (SUBSTRING (SN.C FROM 1 FOR 1)||'5');

Committing and rolling back - autonomous transaction, Committing and Rollin...

Committing and Rolling Back The COMMIT and ROLLBACK end the active autonomous transaction but do not exit the autonomous routine. As the figure shows, if one transaction ends,

Develop a job management site, Lightweight system to provide and take info ...

Lightweight system to provide and take info from workers in the field and office, have basic design outlined already just require build and implementation Desired Skills CSS,

Procedures in pl/sql, Procedures   The procedure is a subprogram which...

Procedures   The procedure is a subprogram which performs a specific action. You write procedures using the syntax as shown below: PROCEDURE name [(parameter[, parameter, .

Using commit, Using COMMIT The COMMIT statements end the present trans...

Using COMMIT The COMMIT statements end the present transaction and make permanent any changes made during that transaction. Till you commit the changes, other users cannot acc

Parameter and keyword description - records, Parameter and Keyword Descript...

Parameter and Keyword Description: record_type_name: This identifies the user-defined type specifier that is used in the subsequent declarations of the records. NOT N

Using %type-declarations in sql, Using %TYPE The %TYPE attribute gives th...

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

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd