unit 5 notes on rdbms, Lecture notes of Computer science

Download unit 5 notes on rdbms and more Lecture notes Computer science in PDF only on Docsity!

RDBMS & ORACLE

Handled By

Mrs. S. N. Santhalakshmi

Mrs. T.Baby

COMPOSITE DATA TYPES

● Like scalar data types.

● Scalar data types are atomic store single value.

● Composite data types , Groups or collections.

● Examples

• (^) RECORD
• (^) TABLE
• (^) nested TABLE and
• (^) VARRAY.

● (^) A PL/SQL record is based on

• (^) a cursor,
• (^) a table’s row,or
• (^) a user-defined record type. ● (^) A record can be explicitly declared based on a cursor or a table: CURSOR cursorname IS SELECT query; Recordname CursorName%ROWTYPE; ● (^) A record can also be based on another composite data type called TABLE.

Creating a PL/SQL Record TYPE recordtypename IS RECORD (fieldname1 datatype|variable%TYPE|table.column%TYPE| table%ROWTYPE[[NOT NULL]:=|DEFAULT Expression] [,fieldname2…… ,fieldName3……); recordname recordtypename;

Declaration with the % TYPE attribute TYPE employee_rectype IS RECORD (e_id NUMBER(3) NOT NULL:= e_last employee.Lname%TYPE, e_first employee.Fname%TYPE, e_sal employee.salary%TYPE); employee_rec employee_rectype; ● (^) The NOT NULL constraint can be used for any field to prevent Null values ,

• (^) But that field must be initialized with a value.

Referencing Fields in a Record ● (^) A field in a record has a name that is given in the RECORD-type definition. ● (^) Cannot reference a field by its name only ,

• (^) Must use the record name as a qualifier recordname.fieldname ● (^) The record name and field name are joined by a dot(.) ● (^) You can use a field in as assignment statement to assign a value to it.For example, employee_rec.e_sal:=100000; employee_rec.e_last:=’jordan’; Record Name Field Name

● (^) A record can be assigned to another record if both record have the same structure. ● (^) A record can be set to NULL ,and

• (^) All fields will be set to NULL. ● (^) The record declared with % ROWTYPE has the same structure as the table’s row .For example, emp_rec employee%ROWTYPE ● (^) emp_rec assumes the structure of the EMPLOYEE table. ● (^) The fields in emp_rec take
• (^) column names and their data types from the table.

● (^) It is advantageous to use %ROWTYPE ,

• (^) It does not require you to know the column names and their data types in the underlying table. ● (^) If u change the data type and/or size of a column ,
• (^) The record is created at execution time and
• (^) Is defined with the updated table structure.

Nested Records ● (^) Including a record into another record as a field is called the enclosing record. DECLARE TYPE address_rectype IS RECORD (first VARCHAR2(15), last VARCHAR2(15), street VARCHAR2(25), city VARCHAR2(15), state CHAR(2), zip CHAR(5)); TYPE all_address_rectype IS RECORD (home_address address_rectype, bus_address address_rectype, vacation_address address_rectype); address_rec all_address_rectype;

PL/SQL TABLE ● Single- dimensional structure with a collection of elements that store the same type of value. ●It is like an array in other programming languages. ●A table is a dynamic structure that is not constrained. DECLARING A PL/SQL TABLE ●A PL/SQL TABLE declaration is done in two steps , like a record declaration.

• (^) Declaration a PL/SQL table type with a TYPE statement.
• (^) Declaration on actual table based on the type declared in the previous step.

● (^) The table consists of two columns, the index/primary key column and the data column. ● (^) We define the actual table based on the table declared earlier Syntax Table name tabletypename; Example Deptname_table deptname_table_type; Major_table mojor_table type

TABLE STRUCTURE ● (^) Table structure contains a primary key column and a data column.

• (^) cannot name these columns. type BINARY INTEGERS any valid type ● (^) There is no limit on the number of element ,
• (^) Cannot initialize elements of a table at declaration time Primary key column Data column 1 Sales 2 Marketing 3 information system 4 Finance 5 Production

● (^) Use an expression or a value other then a BINARY_INTEGER value , and PL/SQL will convert it. /25.7 is rounded to 26./** Deptname_table (25.7) := Training; /5’ ||00’ is converted to 500./** Deptname_table (5,||’);:=Research’; / v_num+7 is evaluate./** Deptname_table (v_num+7) :=`Development’;

Assign values to the rows in a table in three ways.

1. Direct assignment
2. Assignment in a loop
3. A Reigate assignment 1. DIRECT ASSIGNMENT ● (^) Using assignment statement. ● (^) Only a few assignment are to be made. ● (^) For entire database table , a looping method is preferable.