Oracle Guides

View logically represents subsets of data from one or more tables. A view can be thought of as a query stored in the database. A view contains no data of its own and is like a window through which data from tables can be viewed or changed. The table on which a view is based is called base table. View is stored in the data dictionary as a SELECT statement. Only the query is stored in the data dictionary, the actual data is not copied any where. This means that creating views does not take any storage space, rather than the space in the data dictionary. A view is created by using the CREATE VIEW statement. The query that defines the view can refer to one or more tables. The syntax for creating the view is as 

CREATE  OR REPLACE VIEW view_name 

AS subquery;

In the syntax: Create or Replace statement drop the view if it already exists and recreates it. subquery is a complete Select statement. The subqurey can also contain Complex SELECT syntax, which includes joins, groups and subqueries. the subquery that defines a view can’t contain an order by clause. The order by clause is specified when you retrieve data from the view. The subquery that defines the view can’t contain the For Update clause and the column should not reference the curval or nextval pseudocolumns. If the table on which a view is based, is altered, then the view become invalid. The ALTER VIEW statement can be used to compile an invalid view or to add or drop constraint. The constraints that can be defined on a view are Primary key, Unique key and foreign key constraints. After creating the view, you can query the data dictionary USER_VIEWS to see the name of the view and view definition.

You can also create views with errors by using the FORCE option. The force option creates the view regardless of whether on not the base table exists NO FORCE is the default option which created the view only if the base table exist. If we try to compile a view created with the Force option, then oracle will return an error that the base table does’t exist. 

A view can be created as read only by using the WITH READ ONLY option. It ensures that views can be used only in queries and we can’t perform any DML operations on such views. Syntax is

Create view view_name

As Subquery

With READ ONLY;

A view can be created by using the with check option which ensure that only rows accessible to the view can be inserted or updated with the WITH CHECK OPTION on a view you can’t update the columns that join the base Tables. Syntax is –

Create view view_name

As Subquery

With CHECK OPTION;

Once you have created a view, you can see the column of view by using the describe command, same in he case of you would for a table.

Removing View: You can drop a view by using the drop view statement. The drop view statement removes the view definition from the database. Dropping views has no effect on the tables on which the view was based. Syntax for dropping view is as:

Drop View view_name;

if you drop a table on which the View is  based, then after dropping the table, the view will remain still but it will become invalid.

Variables are memory locations which can store data values. In other words variable can be used to store data temporarily when the program runs the contents of variables can change their values. The contents of variables can be inserted into the database and the information from the database can be assigned to a variable. PL/SQL commands can modify the variables directly. The variables are declared and initialized in the declaration section and the new values can be assigned to variables in the executable section of the PL/SQL block. In the executable section, the existing value of the variable is replaced with the new value that is assigned to the variable. Every variable has a specific datatype which specifies what kind of information can be stored in it. 

Why we use variables:

Temporary storage of data: Data can be stored temporarily in one or more variables for use when validating data input and for processing later in the data flow process.

Data can be manipulated by using variables without accessing the database and variables can be used for calculations.

Variables provide reusability that is after they are declared, they can be used repeatedly in an application simply b referencing them in other statements, including other declarative statements.

Variables provide data independence and reduced maintenance cost. Variables can be declared using the object types such as % type and % Row type. % type and % Row type are used when we want to declare variables based on the definitions of data base column. By doing this if the underlying definition change, the variable declaration changes automatically at run time. % Rowtype attribute is useful when we want to retrieve a row with the Select * statement. 

Type of Variables:

All PL/SQL variables have a datatype which specifies a storage format, constraints, valid range of values and what kind of information can be stored in variables. Variables can be classified into two types. PL/SQL variables and Non PL/SQL variables

PL/SQL variables include scalar, composite reference and LOB datatype. Non PL/SQL variables are bind and Host variables. A scalar datatype holds a single value and have no internal components. 

A composite datatype have internal components for example records, nested table, and Varray etc.

Reference datatypes are similar to pointer in C and it holds a value called pointer, which designater other program items.

LOB datatype is used for large objects which holds a value called locator, which specifies the location of large objects.

A bind variable is a variable which is declared in the host environment and is used to pass run time values which can be either a number or a character into or out of one or more PL/SQL  program. To declare a Bind variable, the command variable is used for example

VARIABLE RESULT Varchar2 (25):

Declaration syntax: Variables are declared in the declarative section of PL/SQL block. The syntax for declaring a variable is as:

Variable_name [constant] datatype

[Not Null] [: = Value];

Where variable_name is the name of variable, constant constrains that the constant must be initialized, datatype is the datatype and value is the initial value of the variable. 

Fr example:

Declare

V_last Varchar2 (25) : = ‘Kashi’;

V_Sal Constant Number : = 1000;

The variable which is declared as Not Null and constant must be initialized.

Only one variable can be declared in one line.

DML in PL/SQL: The allowable DML statements are select, insert, update and Delete. All of these statement are described as follows:

Select statement: Select statement is used to retrieve data from the database into PL/SQL variables. The general syntax for select statement is as:

Select  Select_list

Into {Varibale_name [, variable_name];

….. | record_name}

From Table

[where condition]’

In the syntax:

Select_list is a list of atleast one column to be selected. It can also include SQL expression, row functions or group functions. Each select_list item is separated by a comma and can optionally be identified by an alias. The compete set of select list item is known as the select list.

Variable_name is the scalar variable that holds the retrieved value. Variable must be type compatible with its associated select list item. The numbers of variables must be same as the number of select list item. Record_name is the PL/SQL record that holds the retrieved values. Records combine related fields in one syntactic unit, so the can be manipulated as a group as well as individually table specifies the database table name, from which to get the data. A single query can select from more than one table, in the case they are separated by commas.

Condition identifies the rows that will be returned by the query.

Guidelines for retrieving data in PL/SQL 

SQL statement is always terminated with a semicolon (;)

The Into clause is mandatory for the select statement when it is embedded in PL/SQL.

The where clause is optional. If used it specifies the criteria for the query. This clause identifies the rows that will be returned by the query. It is made up of Boolean conditions joined by the Boolean operators

The Into clause must contain the same number of variables as database columns in the select clause. The data types for all the variables must be compatible.

Insert statement: The insert statement adds new rows of data to the table. The general syntax for Insert statement is as

Insert into table_referene

(Column_name, column_name,…)

Values (column_values, column_values,

——————-);

In the syntax

Table_reference clause refers to an oracle table

Column_name refers to column in this table

Values clause can contain either SQL expression or PL/SQL expression. If the insert statement contains a Select_statement, the select list should match the columns to be inserted. A query of this type is known as subquery, since it is within another SQL statement.

Update Statement: The update statement is used for modifying existing rows in the table. The general syntax for update statement is as:

Update Table_reference

Set column_name = [expression [, select statement] ………..]

[Where condition];

In the syntax: 

Table_reference clause refers to the table being updated column name is the name of column whose value is to be updated. Expression is a SQL expression. If the statement contains a select_statement, then the number of columns in the select list must match the column in the set clause. Condition identifies the rows that will be updated by the query.

Delete Statement: The delete statement removes rows from a database table. The general syntax for delete statement is as follows:

Delete from table_reference

[Where condition];

Table_reference clause refers to an oracle table and where clause defines the set of rows to be deleted.

SQL is a structured query language (SQL) which is used to manipulate data in the database SQL statements can be divided into following.

Categories:

Data Manipulation Language (DML): DML statements are used to change data in tables or query data in a data base table. But they do not manipulate the structure of the database table DML statements are Insert, Update, Delete and Merge. Select is not considered as a DML statement because it is used to retrieve data from the database.

Data definition language (DDL): DDL statements are used to alter the structure of a schema object commands that changes permission on schema objects are also DDL. DDL statements are create, Alter, Drop, Rename and Truncate

Transaction control language (TCL): Transaction control statements manages the changes made by DML statements changes to the data can be grouped together into logical transactions. Commit, Rollback and savepoint are transaction control statements.

Data Control Language (DCL): DCL statements gives or removes access rights to both the oracle database and the structures within it. DCL statements are collectively known as data control language. Grant and Revoke are the data control languages

Session Control Statements: These statements change the settings for a single database session. Alter session and set role are the session control statements.

System control statements: These statements are responsible for changing the settings for the entire database for example, to enable or disable archiving. Alter system is a session control statement.

The point to be remember about PL/SQL blocks while using DML statements and transaction control commands in PL/SQL block:

The keyword END singles the end of a PL/SQL block, not the end of a transaction just as a block can span multiple transactions, a transaction can span multiple blocks.

PL/SQL does not directly support the data definition language (DDL) statements such as create table, Alter Table or Drop table.

PL/SQL does not support data control language (DCL) statements, such as grant as revoke

DML in PL/SQL : The allowable DML statements are select, Insert, Update and Delete. Select statement is used to retrieve rows from the database table which meet a specific criteria given by the where clause. Insert statement is used for adding new rows to a database table. Update statement modifies the rows in a database table that match the where clause. The where clause in optional. If this clause is omitted then all rows in the table will be modified. Delete statement is used for removing rows from a database table identified by the where clause. The where clause is optional. If we omit the where clause then all rows from the database table will be removed. When SQL statements are executed from SQL*Plus, then the results are displayed on the screen for an update, Insert or Delete statement, SQL*Plus returns the number of rows processed. Select statement returns the rows that match the query and display that rows on to the screen. Rows removed by using the delete statement can be Rolled-back by using the Rollback statement.

SQL is a structured query language which is used to retrieve information from the database. Most of the SQL functions can be used in PL/SQL. These functions help you to manipulate data in the database. The functions available in PL/SQL are as follows:

Single_Row number

Single_Row Character

Data type Conversion

Date

Time Stamp

Greatest and least

Miscellaneous functions

The functions which are available in SQL, but not available in PL/SQL are as follows:

Group function

Decode

Group functions such as AVG, MAX, MIN, SUM, COUNT, STDDEV, Variance operate on set of rows to give one result per group. Therefore these functions are available only in SQL. But in PL/SQL blocks, with SQL statement we can use these functions.

CHR is a SQL function which is used to convert an ASCII code to its. Corresponding character, 10 is the code for a line feed.

SQLCode and SQLERRM are the PL/SQL own error handling functions. SQLCode returns the numeric value for the error code ..SQLERRM function returns the message associated with the error number. These functions are defined in the exception section of the PL/SQL block.

Commenting Code: Comments are used to enhance readability. They are ignored by the PL/SQL compiler. Comments provide information or they do not enforce any conditions or behaviour on logic or data. Comments provide the information to the reads what the purpose of a program is and how it works. Comments should be meaningful and it should not restate what the PL/SQL code itself says. Comment can be of two types: Single line comment and Multiline comment. Single line comments are prefixed with two dashes (- -). Multiline comments are placed between the symbols /* and */. This comment style is also used in C language multiline comments can extend over as many lines as desired. However they can not be nested. One comment has to end before another can begin. Comments makes your programs more understandable. 

Datatype Conversion:

PL/SQL can handle conversions between different families among the scalar datatypes. Datatype conversion is used to convert data to comparable data types. PL/SQL will convert data types dynamically if they are mixed in a statement. Mixed datatypes can result in an error and it will also affect the performance of the program. There are two types of conversions.

Explicit datatype conversion

Implicit datatype conversion

Explicit datatype conversion: The built in conversion functions which are available in SQL are also available in PL/SQL. These functions can be used to convert datatype explicitly between variables in different datatype families.

Operators available in PL/SQL from SQL

These functions are

To_Char, To_Date and

To_Number

The syntax for these functions is as follows:

To_Char (Value, fmt)

To_Date (Value, fmt)

To_Number (Value, fmt)

Where value is a character string, number or date, fmt is a format model used to convert a value.

Implicit datatype conversion: Implicit datatype conversion is done automatically by the Oracle server. PL/SQL can automatically convert between 

Characters and Numbers

Characters and dates

Even though PL/SQL will implicitly convert between datatypes, it is a good programming Practice to use an explicit conversion function. Automatic datatype conversion can also take place when PL/SQL is evaluating expressions.

Set operators are used in compound queries i.e. they combine the results of two or more component queries into one result. Queries containing set operators are called compound queries. All set operators have equal precedence. The number of columns selected in both queries must be the same. If a SQL statement contains multiple set operators, then oracle server evaluates them from left to right. Parenthesis are used to specify the order of evaluation i.e. parenthesis can be used to alter the sequence of execution. Set operators can also be used in subqueries. The order by clause must appear at the end of the statement and it can contain column names, alias or the positional notation. If column name or alias is used in the order by clause, they must be from the first select statement. The number of columns and their datatype in both the select list must be the same. When set operators are used in a query, then oracle server automatically eliminates duplicate rows except in the case of union all operator. The column list in the first select statement decides the column names in the output. By default, the output is sorted in ascending order of the first column of the select clause. The set operators available in oracle are: Union, Union All, Intersect, Minus.

Union: The union operator returns all  rows selected by either query. Union operator returns all rows from multiple tables and it eliminates any duplicate rows. Union operators does not ignore null values. The number of columns and datatype in both queries must be same but the name of column need not to be identical. The default order of sort is ascending which is the first column of the select list.

Union All: It returns all rows selected by either query, including all duplicates. The Union All operator returns all rows from multiple queries. Unlike Union, Union All operator does not eliminate duplicate rows and the output is not sorted by default. While using Union All operator distinct keyword cannot be used. All operators automatically eliminate duplicate rows except the Union All operator.

Intersect: The operator returns distinct rows that are selected by both queries. The intersect operator is used to return all rows that are common to multiple queries. Intersect operator does not ignore null values. If we reverse the order of intersected table then it does not alter the result returned by queries. The number of columns and datatypes of the columns which are selected by the select statement in the query must be identical in the query. While the names of the columns need not be identical.

Minus: The minus operator returns distinct rows which are returned by the first query but are returned by the second query i.e. the first select statement minus the second select statement. The number of columns and datatypes of the columns which are selected by the select statement in the query must be identical in all the select statements used in the query. While the names of the columns need not be identical. All operators eliminate the duplicate rows automatically except the Union All operators which also includes the duplicate.

PL/SQL is the procedural extension to SQL with design features of programming languages. ALL PL/SQL variables have a datatype which specified the storage format, constraints and valid range of values. the datatypes in PL/SQL can be divided into three categories Scalar, composite and reference. Oracle 5 defined two additional categories of datatype: LOB and object types. All of these categories are described as:

Scalar: Scalar datatype holds a single value and have no internal components. Scalar datatypes can be classified into seven categoried: numeric, character, raw, date/interval, rowid, Booban and trusted.

Numeric datatype can store integar or real values. This is further classified into three categories as: Number, PLS_INTEGER and Binary_Integer. The number datatype holds a numeric value and have a precision P and scale S.

Number (P,S)

The precision P can range from 1 to 38 and scale S can range from -84 to 127.

Binary_Integer is used to store signed integer values which have a range from -2147483647 to +2147483647. PLS_Integer but is requires less storage space and is faster than number and Binary_Integer values.

Character datatype is used to hold strings or characters data. The character data type can be of varchar2, char and long type. Char is a fixed length character string while varchar2 is a variable length character string. The maximum length of char database column is 2000 bytes while in case of varchar2 the maximum length is 4000 bytes. Long datatype is a variable length string with a maximum length of 32,760 bytes. It can hold upto 2GB of data. Long variables are very similar to varchar2 variables.

Raw: Raw variables are similar to char variables, except that they are not converted between character sets. The maximum length of RAW variables is 32,767 bytes. The maximum length of RAW datatype column is 2000 bytes. Long RAW data is similar to Long data, except that they are not converted between characters sets. Long RAW data is not interpreted by PL/SQL. The maximum length of Long RAW variable is 32,760 bytes and the maximum length of long RAW database column is 2GB.

Date/Internal: Date/Internal datatype can be divided into three types. Date Time Stamp and Internal Time: stamp and internal are available with oracle 9i and later. Date dataype stores both date and time information. Time stamp is used to store date and time information upto frations of seconds. The interval type is used to store the amount of time between two timestamps.

Rowid: Rowid datatype is divided into two categories as ROWID and UROWID. UROWID is available with oracle 8i and later. The ROWID datatype is similar to database RowID pseudo column. ROWID is a uniqe number for every row in the datatbase. ROWID are not constructed by PL/SQL program they are selected from the ROWID pseudocolumn of a table. ROWID are stored internally as a fixed length binary quantity whose length varies between operating systems. UROWID datatype can store both physical Rowid and logical ROWID.

Boolen: A boolen datatype holds a ture, false or Null values.

Trusted: The only datatype of trusted family is Mislable. This datatype is used in Trusted oracle to store variable length binary labels. The maximum length of Mislabel variable is 255 bytes.

Sequence is a database object which is used to generate unique number. The sequence is generated and incremented by an internal oracle routine. Sequence is a sharable object which can be shared by multiple users to generate unique numbers. The same sequence can be used for multiple tables. Sequences have many purposes in database systems but the most common purpose is to generate Primary keys automatically, which must be unique for each row, sequences are created with the create sequence statement. The syntax for creating the sequence is as:-

CREATE SEQUENCE Sequence_name

[INCREMENT BY  n]

[ START WITH n]

[ { Maxvalue n/ No Maxvalue} ]

[ { Minvalue n/ No Minvalue} ]

[ { Cycle / Nocycle} ]

[ { Cache n/ Nocache} }

In the syntax: - Sequence_name is the name of sequence

INCREMENT BY n  specifies the interval between sequence numbers where n is an integer. The default for this clause is 1 if it is not explicitly specified.

START WITH n: It defines the first numbers that the sequence will generate if this clause is omitted then the sequence starts with 1.

Maxvalue n: It defines the largest number that the sequence will generate. if no maximum value is specified, then the default is no maxvalue which is 10^27 for an ascending sequence and -1 for descending sequence. 

Minvalue n. It defines the minimum value that the sequence will generate. If no minimum value is specified then oracle assumes the default nominvalue which is 1 for ascending sequence and -10^26 for descending sequence.

Cycle : It specifies whether the sequence will continue to generate values after reaching its maximum or minimum value. Cycle option can not  be used if the sequence is to generate primary key values .Nocycle is the default option which specifies the sequence not to repeat numbers after reaching their maximum or minimum value.

Cache : It defines the size of the block of sequence numbers held in memory. The default is 20.

Once the sequence is created, it can be referenced by using the CURRVAL and NEXTVAL Pseudocolumns. Nextval returns the next available sequence value. It returns a unique value every time is referenced, even for different users. Nextval can be issued only for the sequence before Currval contains a value. As sequence is a database object so you can see it in the user objects data dictionary. The Currval and nextval values can be seen from the dual table.

Modifying a sequence: A sequence can be modified by using the ALTER  statement. we can change the increment value, maximum value, minimum value, cycle and cache option. START With value can’t be altered by this if you want to change the START WITH value then firstly you have drop the sequence and then recreate it. Syntax is 

Alter Sequence sequence_name

[ Increment by n]

[ { Maxvalue n | No maxbvalue] }

[{ Minvalue n | No minvalue} ]

[ { cycle | No cycle} ]

[ { Cache n | no chache}  ]

Information about sequence can be viewed in the USER_SEQUENCES data dictionary. 

Gaps in Sequence : - Sequence are independent of the table ,so the same sequence can be used for multiple table .If this is done  then each table can contain gaps in sequential numbers. Gaps in sequence can also occur if the system crashes down.

Removing a sequence: You can drop a sequence by using the Drop Sequence statement. Syntax is:-

Drop sequence sequence_name;

Once a sequence is dropped, then it can’t be refereed.

Controlling User Access

Oracle 9i provides you several methods for controlling user access. When you create users you can specify how they are authenticated, as well as set of variety of attributes. You can also modify user accounts to add and change attributes.

A primary way to control user access is through privileges. The privileges that are included in Oracle are object privileges, system privileges and role privileges. Privileges are the right to execute particular SQL statements. The database Administrator (DBA) has the ability to grant user access to the database and its objects. The users require system privileges to gain access to the database and one require object privileges to manipulate the contents of the objects in the database.

Creating users: the CREATE USER statement is used to create a user and optionally to assign additional attributes to that user. At that point the user does not have any privileges. The DBA can then grant the privileges to that user. The ALTER USER statement is used to assign any combination of account attributes to the user account but for this the account must already exist. The DBA grant the privileges to the user. The privileges determine the actions that the user can do with the objects in the database. The username can be upto 30 characters long an it can contain alphanumeric characters as well as dolor ($), Pound (#) and underscore (_) characters. The syntax for creating the user is as:

CREATE USER user

IDENTIFIED by Password;

In the syntax

User is the name of the user to be created. Password specifies that the user must log in with this password. The keywords identified by Password tells the oracle that the account is a database authenticated account.

Creating a user Authenticated by the host:

Host authentication means that you create a user in oracle whose password is validated by the underlying host system. When the user logs onto the host with a correct password, then oracle gives the user access to oracle without providing another password. These externally identified accounts are sometimes called OPS$ accounts as shown in the example:

Create User OPS$ Kashi

Identified externally;

Later, if kashi wants to connect to oracle from a host system command prompt, then he can specify the following to do so and does not need to provide a password.

C:\windows>c:\oracleora81\bin\sqlplus\

Globally Authenticated User Accounts: These types of accounts are most common in large organizations where a single sign on system is use. The keywords Identified Globally As <directory_name> tell oracle that the account user global authentication.

Assigning Attributes to Accounts:

ALTER USER statement is used to assign any combination of account attributes to the user account but for this the account must already exist users can change the password with the ALTER USER statement. The syntax for ALTER USER statement is as

ALTER USER kashi

IDENTIFIED BY Joshi;

User’s account can be altered to assign default and temporary tablespace, profiles, roles and password restrictions.

Using Profile: Profiles can be used to limit the resources that t user’s session can consume. Some of these limiting resources include connect. Some of these limiting resources include connect time, idle time, logical reads per session etc. The default profile allows unlimited resource usage before using unlimited resource usage, the limit. Ora parameter resource limit must be set to TRUE.

Create User Kashi

Identified By Joshi

Profile instruction;

Alter user kashi Profile business;

PL/SQL is the procedural extension to SQL with design features of programming languages. ALL PL/SQL variables have a datatype which specified the storage format, constraints and valid range of values. the datatypes in PL/SQL can be divided into three categories Scalar, composite and reference. Oracle 5 defined two additional categories of datatype: LOB and object types. All of these categories are described as:

Scalar: Scalar datatype holds a single value and have no internal components. Scalar datatypes can be classified into seven categoried: numeric, character, raw, date/interval, rowid, Booban and trusted.

Numeric datatype can store integar or real values. This is further classified into three categories as: Number, PLS_INTEGER and Binary_Integer. The number datatype holds a numeric value and have a precision P and scale S.

Number (P,S)

The precision P can range from 1 to 38 and scale S can range from -84 to 127.

Binary_Integer is used to store signed integer values which have a range from -2147483647 to +2147483647. PLS_Integer but is requires less storage space and is faster than number and Binary_Integer values.

Character datatype is used to hold strings or characters data. The character data type can be of varchar2, char and long type. Char is a fixed length character string while varchar2 is a variable length character string. The maximum length of char database column is 2000 bytes while in case of varchar2 the maximum length is 4000 bytes. Long datatype is a variable length string with a maximum length of 32,760 bytes. It can hold upto 2GB of data. Long variables are very similar to varchar2 variables.

Raw: Raw variables are similar to char variables, except that they are not converted between character sets. The maximum length of RAW variables is 32,767 bytes. The maximum length of RAW datatype column is 2000 bytes. Long RAW data is similar to Long data, except that they are not converted between characters sets. Long RAW data is not interpreted by PL/SQL. The maximum length of Long RAW variable is 32,760 bytes and the maximum length of long RAW database column is 2GB.

Date/Internal: Date/Internal datatype can be divided into three types. Date Time Stamp and Internal Time: stamp and internal are available with oracle 9i and later. Date dataype stores both date and time information. Time stamp is used to store date and time information upto frations of seconds. The interval type is used to store the amount of time between two timestamps.

Rowid: Rowid datatype is divided into two categories as ROWID and UROWID. UROWID is available with oracle 8i and later. The ROWID datatype is similar to database RowID pseudo column. ROWID is a uniqe number for every row in the datatbase. ROWID are not constructed by PL/SQL program they are selected from the ROWID pseudocolumn of a table. ROWID are stored internally as a fixed length binary quantity whose length varies between operating systems. UROWID datatype can store both physical Rowid and logical ROWID.

Boolen: A boolen datatype holds a ture, false or Null values.

Trusted: The only datatype of trusted family is Mislable. This datatype is used in Trusted oracle to store variable length binary labels. The maximum length of Mislabel variable is 255 bytes.

PL/SQL records is one of the type of composite datatype. Composite data types are user defined datatypes. So it we want to use composite datatype then firstly we have to define the type and then declare variable of that type. PL/SQL records are similar to structures in C. The record data type is used to treat related but dissimilar data as a logical unit. For example: Consider the following declarative section.

Declare

V_Employee ID Number (7);

V_Firstname Varchar2 (10);

V_Lastname Varchar2(10);

All of these variables are logically related because they refer to common fields in the employees table. By declaring a record type for these variables, they can be manipulated as a unit. For example consider the following declarative section:

Declare

Type emp_record_Type is Record

(Employee_ID number (7),

Lastname         Varchar2(10),

First_name Varchar2(10));

Emp_record emp_record_Type;

The general syntax for creating a PL/SQL record is as:

TYPE type_name IS Record

(Field_declaration [,field_declaration] );

Identifier type_name;

Field declaration is as

Field_name {field_type | variable%type

| table column%type | table%row type}

[ Not Null { := | Default } exprn];

In the syntax:

Type_name is the name of record type type_name is the name of field within the record.

Field_name is the datatype of the field  exprn is the initital value. A record can have as many fields as required fields in a record are accessed by name. The dot notation is used to reference or initialize and individual field. The syntax for this is as:

Record_name. field_name

For example, last_name field can be accessed in the emp_record as

emp_record. Job_id

A value can be assigned to a record field as

Emp_record. Last_name := ‘Kashi’;

Not Null constraint can be added to any field to prevent assigning nulls to that field NOT NULL field must be initialized. 

% ROW Type Attribute:

In PL/SQL a record is declared with the same types as a datatype row. To facilitate this, PL/SQL provides the % RowType attribute. The % Rowtype attribute is used to declare a variable according to collection of columns in a database table or view. The fields in the record take their names and datatypes from the columns of the table or view. The record can also store and entire row of data fetched from a cursor or cursor variable. As with %type, any NOTNULL. Constraint defined on the column is not included. However, the length of varchar2 and char columns and the precision and scale for number columns are included %Rowtype attribute is useful when we don’t know about the structure of the underlying database table. This attribute is particulary useful when we want to retrieve and entire row from a table. If we do not use the %Rowtype attribute for retrieving an entire row then we have to declare a variable for each of the columns retrieved by the, select * statement of the table definition changes, the number and datatypes of the underlying database column also changes dynamically. %Rowtype attribute is declared as:

Declare

Emp_Record          employees%rowtype;

The syntax for   %rowtype attribute is as

Declare

identifier reference % Rowtype;

Identifier is the name for record reference is the name of the table, view or cursor on which the record is based.