Here is next part..... PL/SQL Tables and User-Defined Records:- ------------------------------------------------- Part- 10 User-Defined Records:- -------------------------- You can use the %ROWTYPE attribute to declare a record that represents a row in a table or a row fetched from a cursor. But, you cannot specify the datatypes of fields in the record or declare fields of your own. The composite datatype RECORD lifts those restrictions. As you might expect, objects of type RECORD are called records. Records contain uniquely named fields, which can have different datatypes. Suppose you have various data about an employee such as name, salary, and hire date. These items are dissimilar in type but logically related. A record containing a field for each item lets you treat the data as a logical unit. Defining RECORD Types:- ----------------------------- Records must be declared in two steps. First, you define a RECORD type, then declare user-defined records of that type. You can define RECORD types in the declarative part of any block, subprogram, or package using the syntax TYPE record_type_name IS RECORD (field[, field]...); where record_type_name is a type specifier used in subsequent declarations of records and field stands for the following syntax: field_name datatype [[NOT NULL] {:= | DEFAULT} expr] You can use the attributes %TYPE and %ROWTYPE to specify field types. In the following example, you define a RECORD type named DeptRecTyp: DECLARE TYPE DeptRecTyp IS RECORD ( deptno NUMBER(2), dname dept.dname%TYPE, loc dept.loc%TYPE); Notice that the field declarations are like variable declarations. Each field has a unique name and specific datatype. The next example shows that you can initialize a RECORD type. When you declare a record of type TimeTyp, its three fields assume an initial value of zero. DECLARE TYPE TimeTyp IS RECORD ( seconds SMALLINT := 0, minutes SMALLINT := 0, hours SMALLINT := 0); You can add the NOT NULL constraint to any field declaration and so prevent the assigning of nulls to that field. Fields declared as NOT NULL must be initialized. Nested Records:- -------------------- PL/SQL lets you define nested records. That is, a record can be the component of another record, as the following example shows: DECLARE TYPE TimeTyp IS RECORD ( seconds SMALLINT, minutes SMALLINT, hours SMALLINT); TYPE MeetingTyp IS RECORD ( day DATE, time TimeTyp, -- nested record place VARCHAR2(20), purpose VARCHAR2(50)); Function Results:- -------------------- The example below shows that you can specify a RECORD type in the RETURN clause of a function specification. That allows the function to return a user-defined record of the same type. DECLARE TYPE EmpRecTyp IS RECORD (emp_id INTEGER, salary REAL); ... FUNCTION nth_highest_salary (n INTEGER) RETURN EmpRecTyp IS ...
Posted on: Sat, 10 Aug 2013 07:35:57 +0000
Trending Topics
Recently Viewed Topics
© 2015