PL/SQL
Brief overview of RDBMS - 1/1-3
Database Models
Introduction
Nowadays, it is hard to think of any computerized application that does not use a database. Oracle (meaning-a temple in Greek mythology) is a powerful database management software used for mission critical operations. In this chapter, we understand the core concepts of Oracle. SQL or Sequel is the language of relational databases. We learn interacting with the Oracle database and creating various objects such as tables, views, indexes and retrieving useful information.
Overview of Oracle RDBMS and SQL
Data used to be stored in flat file systems during the initial stages of the information era. As technology improved, innovative ways were invented to store data in an efficient manner. The concept of database was born and various data models such as hierarchical, network were used. But an article published by an IBM scientist called Dr.Codd changed the database scenario. He provided easy yet powerful means of storing and retrieving data. The databases that follow Codd’s principles are called Relational Databases.
Relational Database Management System
The relational model allows data to be represented in a simple row-column format.
Each datafield is considered as a column and each record is considered as a row of a table. Consider a set of raw data, which has to be organized in a particular fashion. Initially the entities and corresponding attributes are recognized and defined. Then the data that have the same attributes are put into tables and the tables are related.
For example, the artist, album and order_details are grouped into individual tables as given below.
ARTIST
ARID
ANAME
AR1
AR2
AR3
AR4
AR5
Artist1
Artist2
Artist3
Artist4
Artist5
ALBUM
ALID
ALBUM NAME
ARID
AL1
AL2
AL3
AL4
AL5
Album 1
Album 2
Album 3
Album 4
Album 5
AR1
AR2
AR1
AR3
AR4
ORDER_DETAILS
OID
ALID
QTY
O1
O2
O3
O4
O5
AL1
AL2
AL3
AL2
AL4
2
1
3
2
1
Different relationships between the various tables are achieved by mathematical set functions namely JOIN and UNION. It can also be seen from the above tables that each of them has one common column.
Suppose, we want to find the artist name for the order O4. We start searching from the order_details table. We find that the album ID for the order O4 is AL2. We take this information and search the “album” table. There, we find that the artist for the album AL2 is AR2. Finally, we search the “artist” table with this artist ID and find that the name of the artist is Artist2.
Thus, the common information in the tables can be used to pullout other details.
The Relational Data Base Management System is now the accepted model on which major database systems are built. Oracle has introduced added functionality to this by incorporating object oriented capabilities and hence now it is known as the Object Relational Database Management System (ORDBMS). An idea of this system is given below.
Object Relational Model
As the name implies, the object relational model supports both object oriented and relational concepts. It eliminates certain discrepancies in the relational model. In this model, it is possible to provide well-defined interfaces for the application. A structure once created can be reused (this is the fundamental OOP philosophy). By combining the object oriented and relational concepts, Oracle now offers the best of both worlds.
Overview of SQL
Structured Query Language (SQL) is the language of Relational databases. It is a nonprocedural language. It concentrates on the “what to do” part of data access or data modification and leaves the “how to do” part to the database engine. Oracle adheres to the ANSI standard for SQL and has its own flavor called SQL * Plus. Different categories of SQL statements are available serving different purposes. They are,
• Query access statement
• Data Manipulation Language (DML) statements
• Data Definition Language (DDL) statements
• Data Control Language (DCL) statements
• Transaction control statements
Query access statement
The Select statement is the most basic SQL statement and is used for data retrieval.
DML statements
Insert, Update and Delete statements fall under this category. They are used for data manipulation such as adding, modifying and removing records respectively.
DDL statements
Create, Alter and Drop statements are treated as DDL statements. With the help of these statements, database objects such as tables, indexes, sequences can be added, modified or removed.
DCL statements
Grant and Revoke are the DCL statements. They are used to controlling user access to database objects.
Transaction Control statements
A transaction is a logical unit of work and the transaction control statements such as commit, rollback and savepoint are used to manage transactions.
SQL syntax
The syntactical requirements for working with SQL statements are,
• An SQL statement can span several lines and the only condition is – it should end with a semicolon.
• SQL is not case sensitive, but when referring to column values, we have to use the case as represented in the table
SQL vs SQL*Plus
SQL is a standard language common to all relational databases. SQL is a database language used for storing and retrieving data from the database. Most Relational Database Management Systems provide extensions to SQL to make it easier for application developers.
SQL*Plus is an Oracle specific program which accepts SQL commands and PL/SQL blocks and executes them. SQL*Plus enables manipulation of SQL commands and PL/SQL blocks. It also performs many additional tasks as well. Through SQL*Plus, we can
• enter, edit, store, retrieve, and run SQL commands and PL/SQL blocks
• list column definitions for any table
• access and copy data between SQL databases
• send messages to and accept responses from an end user
Codd’s rules & Normalization c 2/24-36
Codd Rules are being considered as the basis for a
database management system to be known as RDBMS(Relational Database Management System)
Following are the type of Codd Rules
1. Foundation rule : This rule states that any DBMS that claims to be a RDBMS should having a mechanism for defining, storing , accessing the objects and data.
1. For Defining the Data and how the data is stored - Data Definition Language
2. For Manipulation of data in the stored Location - Data Manipulation Language
3. For Accessing the Data that has been stored - Data Control Language
4. To control the data that is being inserted,modified,deleted - Transaction Control Language.
2. Information Rule
All the values in the system to be a RDBMS, should be stored in the form of Tables .
3. Guaranteed Access
All the objects or information that is stored in the table should be accessible with the help of the main key the primary key for the faster retrieval of data.
4. Systematic Null Rule
In RDBMS, Treatment of Null should be considered. This satisfies basic rule of set theory , a set without a value is known as null set. In RDBMS, a column without a value is known as Null Column.
5. Dynamic Catalog Rule
All the information of the RDBMS should be stored in the common form and common accessible object, the data dictionary.
6. Comprehensive Data Sub Language
All the data that is stored in the RDBMS should be accessible by any of the languages
1. Data Manipulation Language
2. Data Control Language
3. Transaction Control Language Using SQL*Plus Tool
4. Batch Operations by Pl/SQL , Database Triggers.
etc.,
7. View Updating Rule
All the views should be updateable. The views should have columns that is mandatorly to be filled in like the primary key, not null .
8. High level Language Rule
The RDBMS should have the capacity to perform
1. Complex Queries as a single operation
2. Inserting of data from multiple tables into a Single.
9. Phyical data Independece Rule
Application systems and terminal activities must be logically unimpaired
whenever any changes are made to the way in which the physical data is stored,
the storage structures which are used, or the access methods.
10. Logical Data Independence Rule
Application systems and terminal activities must be logically unimpaired
whenever any changes are made to the way in which the logical data is organised,
such as when a table is dispersed to several tables to optimised storage or
performance.
11. Integrity Independence Rule
Integrity rules or mechanism defined to the objects of RDBMS should be stored in the form of tables in the Data dictionary.
12. Distribution of data Rule.
Rdbms, should provide the facility in the distributing the data across various locations logically.Have a mechanism in retrieving the data, whenever the data is asked for.
2. Describe the normalization types and their meaning.
Normalisation is the process of maintaining the data redundancy.
Following are the different type of Normalisation
1st Nf - First Normalisation Form :
It states that all the data should be presented in the form of tables.
Any data inserted in the row must have the same number of columns.
Any data entered in the attribute should be single valued attribute only.
2nd NF - Second Normalisation Form : It states that any non-key column should be functionally dependent on the primary key
3rd NF - Third Normalisation Form : It states that a table violates the 3rd NF , if non-key attribute is dependent on another non-key attribute.
The non-key column in the table should be dependent functionally with the primary key .
4th NF - Fourth Normalisation Form : The row should not contain two or more multivalued independent facts.
5th NF- Fifth Normalisation Form :
Boyce Codd Form
Boyce Codd Form arises when the table is encountering data with the following situations
* When the table has multiple candidate keys
* The candidate keys are composite
* The candidate keys overlap.
Oracle Data types 1/34-36
Connecting to Oracle Database c3/52-53
Oracle Datatypes
In order to create a table we need to specify a datatype for individual columns in the create table command. Oracle supports the following datatypes, to achieve the above requirements.
Character Datatypes
The following are the character datatypes supported by Oracle.
Char datatype
The Char datatype is used when a fixed length character string is required. It can store alphanumeric values. The column length of such a datatype can vary between 1 - 2000 bytes. By default it is one byte.
Varchar2 datatype
The Varchar2 datatype supports a variable length character string. It also stores alphanumeric values. The size for this datatype ranges from 1 - 4000 bytes. While defining this datatype, we should specify the size. Using varchar2, saves disk space when compared to char.
nchar and nvarchar2 are other character datatypes that store single-byte or multibyte text strings.
Long datatype
This datatype is used to store variable character length. Maximum size is 2 GB. Long datatype has several characters similar to varchar2 datatype. Its length would be restricted based on the memory space available in the computer. The following restriction needs to be fulfilled when a long datatype attribute is cast on a column in a table.
• Only one column in a table can have long datatype. This should not contain unique
or primary key constraints.
• The column cannot be indexed.
• Cannot use long datatype in order by clause.
• Procedures or stored procedures cannot accept long datatype as arguments.
Number datatype
The number datatype can store positive numbers, negative numbers, zeroes, fixed-point numbers, and floating point numbers with a precision of 38.
• column_name number { p = 38, s = 0}
• column_name number(p) { fixed point}
• column_name number(p, s) {floating point}
Where p is the precision which refers to the total number of digits, it varies between 1 to 38, s is the scale which refers to number of digits to the right of the decimal point, which varies between -84 and 127.
Date datatype
Date datatype is used to store date and time in a table. Oracle database makes use of its own format to store date in a fixed length of 7 bytes each for century, month, day, year, hour, minute and second. Default date datatype is “dd-mon-yy”. To view system’s date and time, we can use the SQL function called sysdate ( ). Valid date is from Jan 1, 4712 BC to Dec 31, 4712 AD.
Raw datatype
Raw datatype is used to store byte-oriented data like binary data or byte strings and the maximum size of this datatype is 2000 bytes. While using this datatype the size should be mentioned because by default it does not specify any size. Only storage and retrieval of data are possible, manipulations of data cannot be done. Raw datatype can be indexed.
Long raw datatype
Long Raw datatype is used to store binary data of variable length, which can have a maximum size of 2 GB. This datatype cannot be indexed. Further all limitations faced by long datatype also holds good for long raw datatype.
In addition to the above mentioned data types Oracle supports:
• CLOB
• BLOB
• BFILE
LOB datatypes
LOB is otherwise known as Large Object data types. They can store unstructured information such as sound clips, video files etc., up to 4 gigabytes in size. They allow efficient, random, piece-wise access to the data. The LOB types store values, which are known as locators. These locators store the location of the large objects. The location may be out-of-line (not within the table) or in an external file. LOB values can be operated upon through the use of the locators. The DBMS_LOB package can be used to manipulate LOBs. LOBs can be either external or internal depending on their location with regard to the database. Data stored in a LOB column is known as LOB value.
Internal LOBs are stored in the database tablespace. This provides efficient access and optimization of space. External LOBs are also referred to as BFILE. These are stored in operating system files outside the database tablespace. These files use reference semantics. They may be stored in CD-ROMs, PhotoCDs or hard disks etc. But the storage cannot extend from one device to another. The external LOBs do not participate in transactions.
The different internal LOBs are mentioned below :
CLOB
A column with its data type as CLOB stores character objects with single byte characters. It cannot contain character sets of varying widths. A table can have multiple columns with CLOB as its data type.
BLOB
A column with its data type as BLOB can store large binary objects such as graphics, video clips and sound files. A table can have multiple columns with BLOB as its data type.
BFILE
A BFILE column stores file pointers to LOBs managed by file systems external to the database. A BFILE column may contain filenames for photos stored on a CD-ROM.
Connecting to Database
SQLPLUS
Data Definition Language (DDL)
o Create, Alter and Drop 1/33
Data Manipulation Language (DML)
o Insert , Update , Delete 1/43 c 15/ 318-328
The Data Definition Language (DDL) commands are used to create various database objects. A table is created by using the create table DDL command.
Example 1.33
The following command creates a table “artist”.
Create table artist (artist_id char(5), artist_name varchar2(25), artist_profile varchar2(100));
Note: Temporary tables can be created using create global temporary table command. They are available until a transaction or a user session ends.
A table can also be created from another table. The syntax is
Create table table_name as select_statement;
alter table order_details set unused column amount;
This unused column can be dropped permanently by issuing the following command.
alter table order_details drop unused columns;
The second method is to drop the column directly as follows.
alter table order_details drop column amount;
Dropping, Renaming and Truncating Tables
A table can be dropped by using the drop table DDL command as follows.
drop table artist;
Inserting Data into a Table
The insert into DML command is used to add new rows to a table. To add a row of data to specific columns, we can issue the following command.
insert into table_name (column1,column2) values(column1_value,column2_value);
To add a row of data to all columns in a table, the following command can be issued,
insert into table_name values (value_list);
The values must be entered in the same order as they are defined in the table. Character and date type data should be entered within single quotes.
A table can be populated by executing a query as follows.
Insert into table_name (select_statement);
Updating Data
The update DML command is used to make changes to existing data in a table.
The following command updates the all rows.
update table_name set column_name=value;
To update specific rows that satisfy certain condition, we can write
update table_name set column_name=
More than one column can be updated as follows.
update table_name set column1=value1, column2=value2 where
A subquery can also be used in the update statement.
update table_name set column_name = (select_statement) where
Deleting Data
The delete from DML command is used for deleting rows of data from a table. The following syntax can be used for deleting rows that satisfy a condition.
delete from table_name where
To delete all rows from a table,
delete from table_name;
The above command is similar to truncate table command. But truncate table commits the transaction and there is no way to get back the data.
Transaction Control – 1/44-45
Data Control Language 1/4, 1/54-55
Transaction Control
A transaction is a logical unit of work. All changes made to the database can be referred to as a transaction. Transaction changes can be made permanent to a database only if they are committed. A transaction begins with an executable SQL statement and ends explicitly with either rollback or commit statements and implicitly, i.e., automatically, when a DDL statement is used.
The commands that define a transaction are,
• set transaction
• commit
• rollback
• savepoint
Set transaction
This command initiates the beginning of a transaction. This command is optional as a transaction starts automatically when an SQL statement is executed.
Commit
This command is used to end a transaction. Only with the help of the commit command, transaction changes can be made permanent to the database. This command also erases all savepoints in the transaction thus releasing the transaction locks. The syntax is given below.
commit;
Savepoint
Savepoints are like markers to divide a very lengthy transaction to smaller ones. They are used to identify a point in a transaction to which we can later rollback. Thus savepoint is used in conjunction with rollback, to rollback portions of the current transaction.
The general syntax for the savepoint is
savepoint savepoint_id;
Rollback
A rollback command is used to undo the work done in the current transaction. We can either rollback the entire transaction so that all changes made by SQL statements are undone, or rollback a transaction to a save point so that the SQL statements after the save point are rolled back. The syntax is given below. To rollback the entire transaction, we give
rollback;
To rollback to a particular stage in a transaction, i.e., a savepoint, we say
rollback to savepoint savepoint_id;
Example 1.41
update order_details1 set order_qty=10 where order_id='or001';
savepoint point1;
update order_details1 set order_qty=10 where order_id='or002';
rollback to savepoint point1;
commit;
Here, the transaction is rolled back up to the savepoint point1. Thus, the second update statement is rolled back.
DCL statements
Grant and Revoke are the DCL statements. They are used to controlling user access to database objects.
Granting and Revoking Object Privileges
Every activity in a database is governed by privileges. There are two types of privileges that are available in Oracle. They are,
• System privileges
• Object privileges
System privileges include the ability to perform administrative tasks like creating most objects and object privileges refer to the ability to access those objects for querying, adding, updating or removing data. Granting of objects privilege can be done by issuing grant command. In order to grant object privilege, the grantor of the privilege must determine the level of access a user requires on the object and the privilege must be granted.
Available Object Privileges
After the object gets created, the creator of the object will grant privileges to the users of the object. The object privileges are select, insert, update, delete, alter, index, references and execute.
Example 1.50
To give the user “Vasu” the privilege to select and update data on “order_details” table,
grant select, insert on order_details to vasu;
To revoke certain privilege, we can issue the revoke command as follows.
revoke insert on dept from vasu;
Different Select Statements 1/5-9
Data Retrieval using SQL
As mentioned earlier, data can be retrieved from database objects such as tables and views using the select statement.
Writing Simple Queries
In its simplest form, a select statement can be written as follows:
SELECT * | column1[,column2,column3..columnN] FROM table_name;
“Select” and “From” are keywords. The column names in a table are represented by column1, column2…columnN. Instead of specifying all column names, * can be used.
Let us analyze the features of select statement with examples.
“Mellifluous Melody Makers” is a music supermarket having branches in major cities of the country. It has developed an automated search engine that helps its customers easily identify the album they need. The search engine is based on Oracle.
The structure of the table Artist is given in the following table (Table 1.1)
Column Name
Datatype
artist_id char(5) artist_name varchar2(25) artist_ profile varchar2(100)
The structure of the table Album is given in the following table (Table 1.2)
Column Name
Datatype
album_id
char(5)
album_name
varchar2(50)
artist_id
char(5)
Company
varchar2(30)
composer
varchar2(50)
category
varchar2(25)
date_of_release
Date
language
varchar2(15)
price
number(5,2)
The structure of the table Order_details is given below
Column Name
Datatype
order_id
char(5)
album_id
char(5)
order_date
Date
order_qty
number(3)
Amount
number(10)
Example 1.1
To display the information from “Artist” table, the following select statement is executed.
select * from artist;
We can also specify specific columns to be retrieved. To retrieve artist id and artist name from Artist table, we execute
select artist_id, artist_name from artist;
The output will be displayed as
ARTIST_ID ARTIST_NAME
--------------- -------------------------
ar001 hariharan
ar002 pankaj udas
ar003 gulam ali
ar004 spb
ar005 back street boys
ar006 spice girls
6 rows selected.
The commands are executed from SQL prompt in Oracle SQL * Plus application. This result has been formatted for better documentation. Formatting the output is discussed later.
The distinct keyword can be used in a select statement to eliminate data repetition. For example, the following select statement displays the album categories.
select category from album;
The output will be,
CATEGORY
-------------------------
ghazal
ghazal
ghazal
ghazal
film song
film song
pop
pop
film song
The following select statement uses distinct keyword.
select distinct category from album;
The output will be,
CATEGORY
-------------------------
film song
ghazal
pop
Using arithmetic expressions
Arithmetic expressions can be used along with the column names in a select statement.
Example 1.2
To find a discount of 5% on all the albums, we can write the following select statement
select album_name,price,price-(price * 0.05) from album;
Here is the sample output
ALBUM_NAME PRICE PRICE-(PRICE * 0.05)
---------------------- ------------- -----------------------------
kash 50 47.5
hazir 50 47.5
Using column aliases instead of column names
Column names used in a table may be cryptic and may not convey the exact value stored in it. We can use column aliases to provide headings for columns.
Example 1.3
The output of Example 1.2 shows the discount. But the column name price-(price * 0.05) does not convey the actual meaning. Here is the SQL statement that uses the column alias “discounted_price”.
select album_name,price,price-(price * 0.05) as discounted_price from album;
The keyword as specifies an alias. Alias can also be specified without the key word. The above statement can also be written as follows
select album_name,price,price-(price * 0.05) discounted_price from album;
Concatenating columns
The output of a select statement can be displayed as a normal English sentence by concatenating the column names. The string operator || is used for concatenation.
Example 1.4
The name and the price of all albums can be displayed in a simple English sentence format by executing the following select statement
select 'the album name is ' || album_name || ' and the list price is ' || price from album;
strings are enclosed within single quotes (‘) in Oracle and the || string operator concatenates the column names while displaying the output. A sample output is given below.
'THEALBUMNAMEIS'||ALBUM_NAME||'ANDTHELISTPRICEIS'||PRICE
-------------------------------------------------------------------
the album name is kash and the list price is 50
the album name is hazir and the list price is 50
the album name is sham-e-ghazal and the list price is 60
the album name is aadab arz hai and the list price is 65
the album name is hits of spb-vol1 and the list price is 45
the album name is hits of spb-vol2 and the list price is 45
the album name is my heart beats for you and the list price is 60
the album name is feeling lonely and the list price is 60
Operators in SQL-PLUS 1/9-12
The Where Clause
SQL provides a where clause that can be used in a select statement to restrict the rows to be displayed. The where clause consists of one or more conditions and only those rows that satisfy the condition(s) are displayed. Arithmetic, Relational and Logical operators can be used in a where clause.
Example 1.5
To display the titles that cost Rs. 55 or more, the following select statement is executed.
select album_name,price from album where price >=55;
The relational operator >= used in the where clause restricts the rows.
The output will be,
ALBUM_NAME PRICE
-------------------------------------------------- ---------
sham-e-ghazal 60
aadab arz hai 65
my heart beats for you 60
feeling lonely 60
The Logical operators AND and OR are used to combine two conditions.
Example 1.6
To display the “ghazal” titles produced by “hmv”, the following select statement is executed.
select album_name,company,language from album where company='hmv' and language='urdu';
The and operator combines two conditions company=’hmv’ and language=’urdu’.
Not is another logical operator, which is used for negating a condition.
Example 1.7
To display the titles produced by all companies except “hmv”, the following select statement can be executed.
select album_name,company from album where not company='hmv';
The relational operator “!=” can be used instead of “not” in the above query.
select album_name,company from album where company != 'hmv';
Special Operators – Like, Between and In
SQL provides three special operators namely Like, Between and In.
Like operator
It is used to search for rows by specifying a string pattern with the where clause. The following wildcards can be used in the pattern.
% (percent) - specifies zero or more characters
_ (underscore) – specifies a single character
Example 1.8
To display the albums that have the word “hits” in them, the following select statement is executed.
select album_name,company,language from album where album_name like '%hits%';
The string patten ‘%hits%’ specifies that the word “hits” should be embedded anywhere in the album name
Here is the output:
ALBUM_NAME COMPANY LANGUAGE
----------------- -------------------- --------------------
hits of spb-vol1 tips tamil
hits of spb-vol2 tips tamil
Between operator
This operator compares a column value in each row to check if it falls between the lower and upper limits. It works with numeric, date and string values.
Example 1.9
Suppose, a customer wants to see a list of albums released in the month of June ’01. The following query will fetch the required result.
select album_name,date_of_release from album where
date_of_release between '01-jun-01' and '30-jun-01';
“Between” is an alternative to “and” operator.
In operator
It compares a column value with a list of possible values.
Example 1.10
To list the titles produced by “hmv” and “magnasound”, the following select statement can be used.
select album_name,company from album where company in ('hmv','magnasound');
Sorting data using Order by clause
The “order by” clause is used to arrange data based on one or more column values. It can arrange data in ascending or descending order.
Example 1.11
To list the companies and the albums in alphabetical order, we can use the following select statement.
select company,album_name from album order by company,album_name;
Here is the output:
COMPANY ALBUM_NAME
------------------------------ -------------------------
hmv aadab arz hai
hmv sham-e-ghazal
magnasound hazir
magnasound kash
sony music my heart beats for you
star music feeling lonely
tips hits of spb-vol1
tips hits of spb-vol2
Note: the desc keyword can be used to arrange data in descending order.
SQL*PLUS function 1/12-19, 1/23-25
Group Functions
Single Row Functions
A function takes one or more arguments, performs some operation and returns a value. Oracle provides many built-in functions that can be broadly classified as,
• Single row functions
• Group functions
In this section, we concentrate on single row functions. Group functions are covered later.
A single row function works with one record at a time. The built-in single row functions can be classified as,
• String functions
• Numeric functions
• Date functions
• Conversion functions
• Miscellaneous functions
String functions
These functions work with character data type and they are also called as Character functions. The following table lists some of the string functions supported by Oracle.
Function
Description
CONCAT
Concatenates two strings. It works similar to ||.
LOWER
Converts a string to lower case
UPPER
Converts a string to upper case
INITCAP
Converts a string to proper case i.e., capitalizes the first letter of every word.
LENGTH
Finds the length of a string.
LTRIM
Trims off a string from the left side. It optionally takes a set of characters. If the set is specified, the string is trimmed off for all the occurrences of any one of the characters in the set.
RTRIM
Trims off a string from the right side. It optionally takes a set of characters. If the set is specified, the string is trimmed off for all the occurrences of any one of the characters in the set.
SUBSTR
Extracts a sub string from a string. It takes three arguments- the string, starting position of the sub string and the number of characters that form the sub string.
INSTR
Finds the location of a character in a string.
REPLACE
Replaces a set of characters in a string with another set of characters
Most of the functions are self-explanatory, so the following example covers only those functions that require further explanation.
For this example, we use a tiny utility table DUAL given in Oracle for testing purpose.
Example 1.12
Select ltrim('abcbctrimmed','abc') from dual;
The “ltrim” function searches for the characters a,b and c from the left side of the string ‘abcbctrimmed’ and stops the search when it encounters a character which is not in the set.
The above select statement will produce the output: trimmed
Select substr('welcome to SQL world',12,3) from dual;
The “substr” function starts extracting characters from the 12th position and extracts 3 characters. If the third argument (3) is not specified, “substr” will extract all characters from the 12th position.
The above select statement will produce the output: SQL
Select instr('welcome to SQL world','S') from dual;
The “instr” function searches for the character ‘S’ and returns the position. If the character specified is not found in the string, “instr” will return 0.
The output of the above select statement will be: 12
Select replace('ram','m','him') from dual;
The “replace” function replaces ‘m’ in ‘ram’ with ‘him’.
The output will be: rahim
If the replacement string ‘him’ is omitted, then the “replace” function will cut ‘m’ from ‘ram’.
Numeric functions
The following table lists the numeric functions that work with one record at a time.
Function
Description
Example
ABS (x)
Finds absolute value of a number.
abs (-8)=8; abs (8)=8
CEIL (x)
Finds the smallest integer that is greater than or equal to x. Simply put, it is used to round up a number.
ceil (8.7)=9
ceil (-8.7)=-8
FLOOR (x)
Finds the largest integer that is smaller than or equal to x. It rounds down a number.
floor (8.7)=8
floor (-8.7)=-9
MOD (x, y)
Returns the remainder after dividing x by y
mod (22.2,21)=1.2
ROUND (x, y)
Rounds x to the precision of y on the right side of the decimal point. If y is negative, rounds x on the left side of the decimal point.
Round (125.57,1)=125.6
Round (125.57,-1)=130
TRUNC (x, y)
Truncates x to the precision of y on the right side of the decimal point.
Trunc (123.65,1)=123.6
Trunc (126.65,-1)=120
POWER (x, y)
Returns x raised to the power of y.
Power (3,2)=9
Power (3.4, 2.7)=27.226579
SQRT (x)
Finds square root of x.
Sqrt (9)=3
The trigonometric functions such as SIN, COS, TAN, ASIN, ACOS, and ATAN are also single row numeric functions.
The functions “round” and “trunc” can also be used with date data type, but they have different formats.
Date functions
The date functions operate on date data type. In this section, we explore some of the most commonly used date functions.
Function
Description
Add_months (d,m)
Returns a date after adding m months to d
Last_day (d)
Returns a date corresponding to the last day of the month specified in d.
Months_between (d1, d2)
Returns the number of months between d1 and d2. It can return a decimal value.
Round (d, format)
Returns a date by rounding off d to the unit specified by format.
Next_day (d,day)
Returns a date which falls on the day specified by day after the date specified by d.
Trunc (d, format)
Returns a date by truncating d to the unit specified by format.
Let us see an example that handles date functions.
Example 1.13
select sysdate,add_months(sysdate,2) from dual;
This select statement displays current date and the date after two months. The output will be,
SYSDATE ADD_MONTH
--------- ---------
25-SEP-01 25-NOV-01
sysdate refers to current system date in Oracle.
select sysdate,last_day(sysdate) from dual;
This select statement displays current date and the last day of the month specified in current date. The output will be,
SYSDATE LAST_DAY(
--------- ---------
25-SEP-01 30-SEP-01
select sysdate,date_of_release,months_between(sysdate,date_of_release) from album;
The above select statement displays current date, date of release of various albums and the difference in month between the two dates. The output will be,
SYSDATE DATE_OF_R MONTHS_BETWEEN(SYSDATE,DATE_OF_RELEASE)
--------- --------- ---------------------------------------
26-SEP-01 01-JUN-01 3.8196069
26-SEP-01 01-JUL-01 2.8196069
26-SEP-01 24-JUL-01 2.0776714
26-SEP-01 25-MAR-01 6.0454133
26-SEP-01 01-JAN-01 8.8196069
26-SEP-01 01-FEB-01 7.8196069
26-SEP-01 10-APR-01 5.5292843
26-SEP-01 10-APR-01 5.5292843
The difference is displayed in decimals. So, we use the “round” function (numeric type) to round off the difference. The select statement will be,
select sysdate,date_of_release,round(months_between(sysdate,date_of_release)) as month_diff from album;
The output will be,
SYSDATE DATE_OF_R MONTH_DIFF
--------- --------- ----------
26-SEP-01 01-JUN-01 4
26-SEP-01 01-JUL-01 3
26-SEP-01 24-JUL-01 2
26-SEP-01 25-MAR-01 6
26-SEP-01 01-JAN-01 9
26-SEP-01 01-FEB-01 8
26-SEP-01 10-APR-01 6
26-SEP-01 10-APR-01 6
select sysdate,round(sysdate,'mm') from dual;
The above select statement rounds off current date to the first day of the nearest month. The output will be,
SYSDATE ROUND(SYS
--------- ---------
26-SEP-01 01-OCT-01
The formats can be,
y – to round off to the first day of the nearest year (as on July 1)
mm- to round off to the first day of the nearest month (as on the sixteenth day of the month)
ww- to round off to a date that falls on the week day as the first day of the nearest year
w- to round off to a date that falls on the same week day as the first day of the nearest month
select next_day(sysdate,'mon') from dual;
This select statement displays the date when the next Monday falls. The output will be
NEXT_DAY(
---------
01-OCT-01
We can use the full form such as Sunday, Monday or the short form such as sun, mon.
Conversion functions
They are used to convert values from one data type to another. Oracle provides the following conversion functions:
• To_Char
• To_Date
• To_Number
To_Char function
It is used to covert date or number data type to character data type. When used with date data type, it takes the following format.
To_Char (d, fmt)
d is the date to be formatted and fmt is the required format. The following table lists commonly used date formats.
Format
Description
Yyyy
Four digit year
Month
Fully spelled out month (January, February…)
Mon
Abbreviated month (Jan, Feb…)
D
Number of days in week (1,2…7)
DD
Number of days in month
Day
Fully spelled out day (Sunday, Monday…)
Example 1.14
select album_name,to_char(date_of_release,'ddth, month yyyy') from album;
This select statement displays album names and the release dates. The output will be,
ALBUM_NAME TO_CHAR(DATE_OF_RELE
-------------------------------------------------- --------------------
kash 01st, june 2001
hazir 01st, july 2001
sham-e-ghazal 24th, july 2001
aadab arz hai 25th, march 2001
hits of spb-vol1 01st, january 2001
hits of spb-vol2 01st, february 2001
my heart beats for you 10th, april 2001
When used with number data type, the “To_Char” function takes the following format:
To_Char (n,fmt)
Here, n represents a number and fmt, the format. Here is a listing of the formats used with numbers.
9 - It represents a digit and suppresses leading zeros.
0- it represents a digit and displays leading zeros.
$ - it represents currency sign.
Example 1.15
The following select statement converts a number to character type and formats it.
select to_char(9000,'$99,999.99') from dual;
The output will be,
TO_CHAR(900
-----------
$9,000.00
To_Date function
This function is used to convert character type data to date type. The syntax of this function is,
To_date (ch, fmt)
Here, ch represents the character data and fmt represents its format. The format is optional. If omitted, ch should be in the default date format viz., dd-mon-yy.
Example 1.16
The following select statement converts the character string ’01 july 2001’ to a valid date.
select to_date('01 july 2001','dd month yyyy') from dual;
To_Number function
This function converts character type data to number type. The syntax is,
To_number ( ch, fmt)
Example 1.17
The following statement converts the character string ‘$1,000’ to a valid number.
select to_number('$1,000','$9,999') from dual;
The format should match the characters. If the format is omitted, the characters should represent only digits.
Aggregating Data using Group Functions
Group functions and their use
A group function performs a data operation on groups of rows, rather than on single rows, and returns a single value. It is often used with a group by clause (to be explained later) in the select statement.
The following table lists group functions
Function
Description
AVG (X)
Finds the average of column x in all rows returned by the select statement
COUNT (X)
Counts the number of non-NULL values returned by the select statement for column x
MAX (X)
Finds the maximum value in column x in all rows returned by the select statement
MIN (X)
Finds the minimum value in column x in all rows returned by the select statement
STDDEV (X)
Finds the standard deviation for all values in column x in all rows returned by the select statement
SUM (X)
Finds the sum of column x in all rows returned by the select statement
VARIANCE (X)
Finds the variance for all values in column x in all rows returned by the select statement
Let us see an example that applies group functions.
Example 1.20
select avg(price) from album;
The output will be,
AVG(PRICE)
----------
53.888889
select count(*) from artist;
This select statement displays the number of rows in the “artist” table. The wildcard “*” indicates all columns in the table. The Oracle SQL processing mechanism takes a long time in resolving *. So, to find the number of records in a table, we can use ROWID or 1 in place of *.
ROWID is a pseudocolumn containing a unique value for each row in a table. It is not actually a part of the table; it is stored internally within Oracle indexes.
Example 1.21
select min(price),max(price) from album;
This select statement finds the maximum and minimum prices. The output will be,
MIN(PRICE) MAX(PRICE)
---------- ----------
45 65
Group functions ignore NULL values by default.
The Group By Clause
A select statement can use group by clause to logically group data using group functions. The group by clause provides summary information.
Example 1.22
The following select statement finds the average price of various album categories.
select category,avg(price) from album group by category;
The output will be,
CATEGORY AVG(PRICE)
------------------------- ----------
film song 46.666667
ghazal 56.25
pop 60
The Having Clause
This clause specifies a condition that acts upon the rows returned by the “group by” clause. It is useful when we want to filter information after grouping rows.
Example 1.23
If we want to display only those album categories that cost, on an average, Rs. 50 or more, we can execute the following select statement.
select category,avg(price) from album group by category
having avg(price)>=50;
The output will be,
CATEGORY AVG(PRICE)
------------------------- ----------
ghazal 56.25
pop 60
The difference between “where” and “having” clauses is,
The where clause always precedes the “group by” clause and restricts rows before they are grouped. The having clause succeeds the “group by” clause and works on the resultant rows generated by the “group by” clause.
Set Operators
Relating data through join concept 1/20-22
Joins 3/81-89
Equi joins
Non-equi joins
Set operators combine the results of two component queries into a single result. Queries containing set operators are called compound queries. Table 3-9 lists SQL set operators.
Operator
Returns
UNION
All rows selected by either query.
UNION ALL
All rows selected by either query, including all duplicates.
INTERSECT
All distinct rows selected by both queries.
MINUS
All distinct rows selected by the first query but not the second.
All set operators have equal precedence. If a SQL statement contains multiple set operators, Oracle evaluates them from the left to right if no parentheses explicitly specify another order.
Displaying Data from Multiple Tables
In an RDBMS, data is stored in different related tables. Two tables can be related or joined when a column value appears in both tables. One of these two tables is referred to as the parent table and the other, the child table. The common column in the parent table is the primary key for that table and the common column in the child table is the foreign key for that table (the primary key and foreign key concepts will be covered in a later section of this chapter).
The select statement can be used to retrieve information from more than one table.
Joins
A query that combines rows from two or more tables is called a join. A join condition is specified with the where clause of the select query. The join condition compares one or more columns in one table with one or more columns in another table and retrieves the records.
A join can be classified into any of the following types based on the join condition.
• Equi join
• Outer join
• Self join
Equi join
A join with a join condition containing an equality operator is called an equi join or inner join. An equi join combines rows that have equivalent values for the specified columns.
The syntax is,
Select select_list From table1, table2…tableN
Where table1.column1=table2.column2 and
table2.column3=table3.column4 and…tableM.columnM=tableN.columnN
The select_list can include columns and expressions from the specified tables.
Example 1.18
The following select statement displays the order details of various album titles.
select order_id,order_date,album_name,order_qty
from album,order_details
where album.album_id=order_details.album_id;
The output will be,
ORDER ORDER_DAT ALBUM_NAME ORDER_QTY
----- --------- -------------------------------------------------- ---------
or001 12-AUG-01 kash 2
or002 13-AUG-01 kash 1
or003 07-AUG-01 hazir 1
or004 17-SEP-01 sham-e-ghazal 3
or005 27-AUG-01 aadab arz hai 1
or006 12-SEP-01 hits of spb-vol1 1
or007 02-SEP-01 hits of spb-vol2 1
or008 30-AUG-01 my heart beats for you 2
or009 01-SEP-01 feeling lonely 2
If a column specified in the select list exists in more than one table, the table name should be used along with the column name. For example, the column album_id is available in album and order_details tables. The following select statement uses the table name “album” as a qualifier for the column album_id.
select album.album_id,order_id,order_date,order_qty
from album,order_details
where album.album_id=order_details.album_id;
When we use two or more tables, we can use table aliases for easy reference. The above select statement can also be written as
select album.album_id,order_id,order_date,order_qty
from album a,order_details b
where a.album_id=b.album_id;
Outer join
An outer join returns all rows that satisfy the join condition along with other rows from one of the tables that do not satisfy the join condition. For example, the above equi join only returns the album titles that have been ordered at least once. But, there may be album titles in the album table that may not have been ordered yet. An outer join will retrieve those album titles also.
Example 1.19
select album.album_id,album_name,order_id,order_date
from album,order_details
where album.album_id =order_details.album_id (+);
The join condition in this select statement contains a special join operator (+) at the right end. This operator denotes that data in the “album” table can correspond either to values in “order_details” table or to NULL if there is no corresponding value in “order_details”.
The output will be,
ALBUM ALBUM_NAME ORDER ORDER_DAT
----- -------------------------------------------------- ----- ---------
al001 kash or001 12-AUG-01
al001 kash or002 13-AUG-01
al002 hazir or003 07-AUG-01
al003 sham-e-ghazal or004 17-SEP-01
al004 aadab arz hai or005 27-AUG-01
al005 hits of spb-vol1 or006 12-SEP-01
al006 hits of spb-vol2 or007 02-SEP-01
al007 my heart beats for you or008 30-AUG-01
al008 feeling lonely or009 01-SEP-01
al009 gandarva ganangal
This result tells that album “al009” has not been ordered by customers, yet.
NULL represents absence of information.
Self join
Sometimes, it may be required to perform a join using only one table. Such a join is called self join. The join operation is performed by using two copies of the same table. At least two conditions are needed in the where clause of a self join to produce required output.
Cartesian product
The select statement that performs a join must contain the right number of join conditions. Otherwise, the query will result in a Cartesian product, which is rarely useful. If a table containing “M” rows and another table containing “N” rows are joined without a join condition, then the resultant Cartesian product will have M x N rows.
If “N” tables are joined, then there must be at least N-1 join conditions to avoid Cartesian product.
Usage of Subqueries 1/25-28
Subqueries
A select statement nested within another select statement is called a subquery. The subquery limits the output of the main query (or parent query) by producing an intermediate result set. Subqueries can be used to perform complex operations.
The points to be remembered while writing subqueries are,
• A subquery should be enclosed within parenthesis
• A subquery is evaluated before its parent query
• A subquery cannot have the order by clause
Types of Subqueries
Different types of subqueries are,
• Single-row subquery
• Multiple-row subquery
• Inline views
• Multi-column subquery
Single-row subquery
This type of subquery returns only one value as expected by the main query.
Example 1.24
Suppose that we want to know the album name corresponding to an order with order_id “or001”. We have album_id in order_details table. The following select statement uses a subquery and displays the album name.
select album_name from album where
album_id=(select album_id from order_details where order_id='or001');
The output will be,
ALBUM_NAME
--------------------------------------------------
kash
The subquery returns only one album_id corresponding to the order_id “or001”. The parent query uses this value in its “where” clause and displays the result.
Multiple-row subquery
This type of subquery returns more than one value and the parent query uses these values in its condition.
Example 1.25
The following select statement provides information about various artists who have released at least one album.
select artist_id,artist_name from artist where
artist_id in (select artist_id from album);
The output will be,
ARTIS ARTIST_NAME
----- -------------------------
ar001 hariharan
ar002 pankaj udas
ar003 gulam ali
ar004 spb
ar005 back street boys
ar006 spice girls
ar007 jesudas
The subquery returns all artist_id values from “album” table and the parent table uses these values as “scope values” for the “in” operator and performs comparison.
Correlated multiple-row subqueries
Before knowing about correlated subqueries, a short description about “scope of column values” is needed.
The columns specified in the parent query have global scope, i.e., they can be used in a subquery. The columns specified in the subquery have local scope and they cannot be referenced outside the subquery.
A subquery that uses global scope columns is called a correlated subquery. A correlated subquery is evaluated once for each row processed by the parent select statement.
The general syntax of a correlated subquery is as follows:
Select select_list From table1 t_alias1
Where
(Select column_list From table2 t_alias2
Where t_alias1.column
Keywords related to single or multiple row subqueries
An exists operator used in a subquery allows the user to specify the results of a select statement according to a special subquery operation. The “exists” operation returns a Boolean value TRUE or FALSE based on whether or not the subquery obtains data when it runs.
Example 1.26
The following select statement illustrates a correlated subquery that uses the “exists” operator.
select album_name from album a
where exists (select b.album_id from order_details b
where a.album_id=b.album_id and b.order_qty>=2);
For each row in the parent statement, the subquery is executed. If the “exists” operation returns TRUE, the corresponding album name is displayed.
The output will be,
ALBUM_NAME
--------------------------------------------------
kash
sham-e-ghazal
my heart beats for you
feeling lonely
Multiple-Column Subqueries
These subqueries we have used so far contained only one column’s worth of data to compare at the main query level. The main query can be set up to handle multiple columns in each row returned, too. When a subquery returns data from more than one column, it will be called a “multiple-column subquery”.
Example 1.27
Consider the following table “cricketer”.
NAME AGE COUNTRY
--------------- --------- --------------------
sachin 27 india
dravid 28 india
ganguly 28 india
steve waugh 36 australia
ricky ponting 26 australia
To find the oldest cricketer in each country, we can write a multi-column subquery as follows.
select name,age from cricketer where (country,age) in
(select country,max(age) from cricketer group by country);
Inline view
A subquery placed in the “from” clause (instead of the where clause) of the parent query is called an inline view. The syntax of this subquery is,
Select select_list from table1, (select select_list from …) table2 where condition;
Example 1.28
The query in the previous example is rewritten here.
select a.name,b.maxage,b.country
from
(select country,max(age) maxage from cricketer group by country)
b, cricketer a where b.maxage=a.age;
Introduction to Integrity Constraints
Implementation of Constraints 1/38-42
Constraints
Maintaining security and integrity of a database is the most important factor in judging the success of a system. This integrity can be applied to different degrees of severity. An Integrity Constraint is a mechanism used by Oracle to prevent invalid data entry into the table. In other words constraints are used for enforcing rules that the columns in a table have to conform with.
The following are the various types of integrity constraints:-
• Domain integrity constraints
• Entity integrity constraints
• Referential integrity constraints
Domain integrity constraints
These constraints set a range, and any violations that take place will prevent the user from performing the manipulation that caused the breach. There are basically two types of domain integrity constraints.
• Not Null constraint
• Check constraint
By default, the tables can contain null values. The enforcement of not null constraint in a table ensures that the table contains values. Oracle will not validate the record until this is satisfied. The other type of constraint available under this classification is the check constraint. This can be defined to allow only a particular range of values. When the demarcation specified in this range is violated Oracle rejects the record.
Entity integrity constraint
Entity integrity constraints are of two types.
• Unique constraints
• Primary Key constraints
The unique constraint designates a column or a group of columns as a unique key. This constraint allows only unique values to be stored in the column. Oracle rejects duplication of records when the unique key constraint is used.
The primary key constraint is similar to the unique key constraint. The primary key constraint just like the former avoids duplication of values. Its need is best felt when a relation has to be set between tables, because in addition to preventing duplication it also does not allow null values.
Referential Integrity constraint
The Referential Integrity constraint enforces relationship between tables. It designates a column or combination of columns as a foreign key. The foreign key establishes a relationship with a specified primary or unique key in another table, called the referenced key. In this relationship, the table containing the foreign key is called the child table and the table containing the referenced key is called the parent table.
Defining and Managing Constraints
There are two methods available to define constraints namely the table constraint method and the column constraint method. In the former method, the constraint is defined at the table level at the end of the columns. In the later method, the constraint is defined as part of a column. Except not null, all constraints can be defined at the table level or at the column level.
Defining Primary Key
Example 1.37
The following example defines a primary key for the table artist.
At the table level,
create table artist
(artist_id char(5),
artist_name varchar2(25) not null,
artist_profile varchar2(100),
constraint pk_artist_id primary key(artist_id));
At the column level,
create table artist
(artist_id char(5) primary key,
artist_name varchar2(25) not null,
artist_profile varchar2(100));
Note: A composite primary key can only be created at the table level.
Defining Foreign Key
The following example demonstrates foreign key.
Example 1.38
create table album
(album_id char(5),
album_name varchar2(50) not null,
artist_id char(5),
company varchar2(30),
composer varchar2(50),
category char(25),
date_of_release date,
language varchar2(15),
price number(5,2),
constraint pk_album_id primary key(album_id),
constraint fk_artist_id foreign key(artist_id) references artist(artist_id));
We can add the key words on delete cascade with the foreign key constraint as shown below to enable record deletion in the parent table.
…
constraint fk_artist_id foreign key(artist_id) reference artist(artist_id) on delete cascade);
Defining Check and Unique constraints
The following example provides ways to define check and unique constraints.
Example 1.39
create table order_details
(order_id char(5),
album_id char(5),
order_date date not null,
order_qty number(3) check (order_qty>0),
amount number(10,2),
constraint pk_order_id primary key(order_id),
constraint fk_album_id foreign key(album_id) references album(album_id));
Here, the check constraint restricts invalid order quantity. A check constraint cannot refer to another column or row in any table and it cannot refer to special keywords such as sysdate, rowid.
create table artist
(artist_id char(5),
artist_name varchar2(25) not null,
artist_profile varchar2(100),
constraint pk_artist_id primary key(artist_id),
constraint uk_artist_name unique(artist_name));
Here, the unique constraint ensures that artist names will be unique.
Indexes and Constraints
Indexes are created to support integrity constraints such as primary key and unique constraint. When a primary key or unique constraint is defined, an index will be created. If the primary key or unique constraint is given a user-defined name, that name will be given to the index also. If user-defined names are not available, Oracle generates names for the constraints. The names will be in the format SYS_Cxxxxxx, where xxxxxx is a six-digit number.
Modifying Constraints
Constraints can be added, disabled, enabled and removed. The following example explains adding a constraint through alter table command.
Example 1.40
alter table album add (constraint ck_category check(category in ('ghazal','pop','film song','other')));
This SQL statement adds a check constraint.
Disabling Constraints
A constraint can be enabled or disabled. When defined, it is enabled automatically. The following SQL statement disables a constraint.
alter table artist disable constraint pk_artist_id;
If a foreign key refers to the primary key, we can still disable the primary key by adding cascade keyword.
Enabling Constraints
The following SQL statement enables the primary key constraint.
alter table artist enable constraint pk_artist_id;
Note: When a constraint is disabled, data becomes vulnerable.
Validate and Novalidate keywords
These keywords are used in conjunction with enable and disable key words. The validate keyword reinforces the constraint on all records. Let us consider the following SQL statement.
alter table artist enable novalidate primary key;
Here, the primary key is enabled. But the data entered during the period when it was disabled will not be checked as the novalidate option is also used.
Dropping a Constraint
A constraint can be dropped as follows:
alter table artist drop constraint pk_artist_id;
Or
alter table artist drop primary key;
Concept of Locking 1/45-45
Locks
Locking is a mechanism that prevents data from being changed by more than one user at a time. Locks can be categorized into table-level locks and row-level locks.
A Table-level lock provides the privilege to change data in a table only to the user holding the lock on the table. During the lock period, no other user can make changes to the table.
A row-level lock allows the user to exclusively lock one or more rows or a table leaving other rows to be changed by other users.
Table Partitions c/401-407
hash_partitioning_clause
specifies that the table is to be partitioned using the hash method. Oracle assigns rows to partitions using a hash function on values found in columns designated as the partitioning key
column_list
is an ordered list of columns used to determine into which partition a row belongs (the partitioning key).
range_partitioning_clause
PARTITION BY RANGE
Specifies that the table is partitioned on ranges of values from column_list. For an index-organized table, column_list must be a subset of the primary key columns of the table.
Column_list
is an ordered list of columns used to determine into which partition a row belongs (the partitioning key).
Composite_partitioning_clause
Specifies that table is to be first range partitioned, and then the partitions further partitioned into hash subpartitions. This combination of range partitioning and hash subpartitioning is called composite partitioning.
Subpartition_clause
Specifies that Oracle should subpartition by hash each partition in table. The subpartitioning column_list is unrelated to the partitioning key.
SUBPARTITIONS quantity
Spe`cifies the default number of subpartitions in each partition of table, and optionally one or more tablespaces in which they are to be stored.
The default value is 1. If you do not specify the subpartition_clause here, Oracle will create each partition with one hash subpartition unless you subsequently specify the partition_level_hash_subpartitioning clause.
row_movement_clause
determines whether a row can be moved to a different partition or subpartition because of a change to one or more of its key values during an update operation.
Database Objects - Synonyms, Sequences, Views and Indexes 1/46-54
Control user Access 1/54-55
In this section, we will look at various database objects views, synonyms, sequences and indexes. Data dictionary is also covered along with views.
Views
A view is similar to a virtual table. It is a memory structure where the results of a select statement are stored. In reality, manipulating the data from the view is similar to manipulating it from the table. We can also insert data into the view.
The advantages of a view are,
• It provides extra security to data.
• It can hide real column names that are difficult to understand and display simple names.
• It can hide data complexity by combining appropriate information from multiple tables.
Creating Simple views
There are different types of view created in Oracle. Simple view is the first type of view, which is created from the data in one table. All single row operations are allowed in simple view. The options that are not allowed in this type of view include,
• Order by clauses
• References to more than one table via a table join, grouping or set operations
• Group by clauses and
• Queries with the distinct keyword
Example 1.42
Create view album_view as
(select * from album where company=’hmv’);
This command creates a view based on the “album” table.
Changing Data in a Table through Simple Views
A user can insert data into the underlying table of a view if he has the necessary privileges. But there are several restrictions being imposed while inserting or updating data to a table in simple view. They are as follows,
• If the with check option is used, the user cannot perform manipulations on the data such as insertion, deletion or updation of the data.
• If the select statement creating the view contains group by or order by clauses, the users may not insert, delete or update data on the table.
• Manipulations on the data are impossible if the simple views are created with the read only option.
• No data can be inserted on the table underlying a simple view that contains references to any virtual column such as ROWID, CURRVAL, NEXTVAL and ROWNUM.
Complex Views
A Complex view draws data from multiple tables and it allows all the operations that are allowed in simple view. It can contain single-row operations and references to virtual columns. It can also contain group by clauses. However, in most of the circumstances, data manipulation statements are not permitted on the underlying tables for complex views.
Example 1.43
The following view collects data from album and order_details tables.
Create view album_order as
(select a.order_id,a.album_id,b.album_name,a.order_date,a.order_qty
from order_details a, album b
where a.album_id = b.album_id);
Updatable or Modifiable Join Views
We can set up a complex view that allows underlying tables to be updated. This is called as updatable join view or modifiable join view.
Data can be modified on a complex view of if all the following conditions are satisfied:
• The statement must affect only one of the tables in the join.
• For update statements, all columns changed must be retrieved from a key-preserved table. If the view is created with the check option, join columns and columns taken from tables that are referenced more than once in the view cannot be part of the update.
• For insert statements, all columns in which values are inserted must come from a key-preserved table, and the view must not have been created with check option.
• For delete statements, there may be one key-preserved table in the join. This table may be present more than once in the join, unless the view has been created with check option.
Note: Key-preserved table is a table whose primary-key columns are present in the view.
Modifying Views
The alter view statement can be issued to revalidate all the references in the view, but to alter the definition of a view, create or replace view statement has to be used. When this statement is issued, Oracle overwrites the old with new definition of the view.
Note: The drop view command can be used to drop views.
Data Dictionary
The data dictionary is an indispensable asset to every developer and DBA. It contains just about any kind of information and it is always looked up to resolve queries about various database objects.
Dictionary Views
There are many tables available in the dictionary and they keep track of many of the database objects. The SYS user is the only user allowed to update the dictionary tables. As the data dictionary tables contain vital information, the information available in these tables is provided in the form of views.
The data dictionary views get data from the data dictionary tables and present useful information. The dictionary views are divided into three categories namely. They are,
• USER_views
• DBA_views
• All_views
USER_views
They allow a user to see all relevant database objects that are owned by the user. The user views start with USER_. If a user User1 grants another user User2 to access a table “artist” in his schema, then User2 can refer to that table as User1.artist. But when User2 tries to access USER_TABLES view, he will not get any information about “artist” because the table belongs to User1.
ALL_Views
They allow a user to see the database objects where he has necessary access permissions. They start with ALL_ and provide information about database objects created by the user or granted to the user. It also displays objects granted to PUBLIC user.
DBA_Views
These views provide information about every database object. They have the prefix DBA_.
The scope of these views differs significantly. The views DBA_TABLES, ALL_TABLES and USER_TABLES provide information about tables in the database, but the depth of information displayed will be different. The scope narrows down from DBA_Views to USER_Views.
The following table lists some of the dictionary views.
Dictionary view
Description
USER_,ALL_,DBA_TABLES
Provide information about tables in the database
USER_,ALL_,DBA_CONSTRAINTS
Provide information about constraints in the database
USER_,ALL_,DBA_COLUMNS
Provide information about columns in tables in the database
USER_,ALL_,DBA_VIEWS
Provide information about views in the database
Querying the Dictionary
The following examples illustrate the data dictionary concepts we have covered. We assume that the user has logged on to Oracle with user name ‘SCOTT’.
Example 1.44
To list the tables owned by the user ‘SCOTT’,
select table_name from all_tables where owner='SCOTT';
or
select table_name from user_tables;
The output will be,
TABLE_NAME
------------------------------
ACCOUNT
BONUS
DEPT
EMP
RECEIPT
SALGRADE
To list the constraints for the tables,
select constraint_name,constraint_type,table_name from user_constraints;
The output will be,
CONSTRAINT_NAME C TABLE_NAME
------------------------------ - ------------------------------
PK_DEPT P DEPT
PK_EMP P EMP
FK_DEPTNO R EMP
Other Database Objects
The database objects such as sequences, index and synonyms are covered in this section.
Sequences
Sometimes, it may be useful to auto-generate values such as invoice number and bill number. A sequence object can generate integer values according to rules specified at the time of its creation.
The create sequence statement is used to create a sequence.
Various clauses of this statement are explained in the following table.
Clause
Description
Start with n
It allows us to specify the first value generated by the sequence.
Increment by n
It defines the number by which the sequence gets incremented every time the NEXTVAL virtual column is referenced
Minvalue n
It defines the minimum value produced by the sequence
Maxvalue n
It defines the maximum value produced by the sequence
Cycle
It allows the sequence to recycle values produced when the maxvalue or minvalue is reached
After the sequence is created, it is referenced by using CURRVAL and NEXTVAL pseudocolumns. The current and the next value of the sequence can be viewed by using the select statement. Since sequences are not tables, DUAL table acts as a virtual table from which the virtual column data is retrieved.
Note: A sequence can be modified by using alter sequence command and dropped by using the drop sequence command.
Example 1.45
The following command creates a sequence.
Create sequence inv_no
Start with 1
Increment by 1
Maxvalue 100;
This command creates a sequence inv_no. After the sequence is created, we can use CURRVAL and NEXTVAL to fetch current value and next value of the sequence.
Indexes
Indexes are optional structures associated with tables. We can create indexes explicitly to speed up SQL statement execution on a table. Similar to the indexes in books that help us to locate information faster, an Oracle index provides a faster access path to table data.
When an index key column is used in the SQL statement’s where clause, then the index points directly to the location of the rows containing those values. Indexes are the primary means of reducing disk I/O operations when used appropriately. The absence or presence of an index does not require a change in the wording of any SQL statement. An index is merely a fast access path to the data; it affects only the speed of execution.
B-Tree Index Structure
This is the traditional index type. This structure contains data placed in layered, branching order from top to bottom. The midpoint of the entire list is placed at the top of the tree and is called the root node. Then, the midpoints of each half of the remaining two lists are placed at the next level, and so on.
The individual nodes not only holds the data values of a column, but also stores some information about the column value’s row location on the disk. This can be simply called as ROWID. ROWID directly points out the location of the table row corresponding to the column value. Using ROWID, Oracle finds all the data that is associated with the row in the table.
Bitmap Index Structure
The other type of index available in Oracle is the bitmap index. This is similar to a lookup table having rows that corresponds to all unique data values in the column being indexed. Each row in this index contains four columns.
• The first column contains the unique value for the column being indexed.
• The second column contains the start ROWID for all rows in the table.
• The next column contains the end ROWID for all rows in the table.
• The last column contains a bitmap pattern, in which one bit for each row in the table.
If the value in the indexed column for that row matches with this unique value, then the bit is set to 1 otherwise it is set to 0. We have to be careful when adding distinct values to a column with a bitmap index, because these indexes work better when there are few distinct values allowed for a column.
Creating Indexes
B-tree index can be created on a column manually by using the create index statement containing unique keyword. This is similar to creating UNIQUE or PRIMARY KEY constraint on a table. We can index a column that contains NULL or repeated values by eliminating the unique keyword. When an index is created based on more than one column, it is called composite index. We can also create a reverse-key index where the contents of the index correspond to a reversed set of data from the indexed column. We can find such reverse-key in Oracle Parallel Server environments in order to improve parallel query performance. The unique keyword can be substituted with bitmap keyword to create bitmap index.
Example 1.46
To create a unique index based on artist name in the artist table,
Create unique index a_name on artist(artist_name);
Function-based Indexes
Traditional B-tree indexes cannot be used when the where clause contains columns that participate in functions or operations. The function-based index is designed to improve the query performance by making it to define an index that works when the where clause contains operations on columns.
Example 1.47
The following command creates a index based on the expression price * 0.10.
Create index ind_album on album(price * 0.10);
To use function-based index, we have to issue the following alter session statements.
alter session set query_rewrite_enabled = true;
alter session set query_rewrite_integrity = trusted;
Note: An index can be removed by executing drop index command.
Synonyms
A synonym is an alias or alternative name to a table. It is used to
• Simplify SQL statements.
• Hide the name and owner of an object.
• Provide location transparency for remote objects of a distributed database
• Provide public access to an object.
Synonyms can either be private or public. The former is created by normal user, which is available only to that person whereas the latter is created by the DBA or any user having create public synonym privilege, which can be availed by any database user. The syntax for creating a synonym is given below.
create [public] synonym
Example 1.48
The user User1 creates a synonym for the table album as follows.
create public synonym album_s for user1.album;
Now, another user can use this synonym to access the table as follows.
select * from album_s;
The drop synonym command is used to drop private synonyms and the drop public synonym command to drop public synonyms.
Controlling User Access
Security is an important aspect that has to be taken care of in a database. In most organizations, it consists of a set of functions handled either by the DBA or by the security administrator. This person is responsible for creating new users and determines the accessibility of objects in the database. Oracle provides options for limiting users access to the database and for controlling privileges of the users. The Oracle database security model consists of two parts. The first part consists of password authentication for all users and the second part consists of controlling which database objects a user may access, the level of access a user may have to the object, and whether the user has the authority to place new objects into the Oracle database.
Creating users
Users are created in Oracle with the create user command. When a user is created, along with a password, several storage and database usage options are also set up.
Example 1.49
To create a user “Vasu”,
create user vasu identified by vasu;
To change the password for vasu,
alter user vasu identified by sharma;
ORDBMS Vs RDBMS
Objects of Oracle 8
The Relational Data Base Management System is now the accepted model on which major database systems are built. Oracle has introduced added functionality to this by incorporating object oriented capabilities and hence now it is known as the Object Relational Database Management System (ORDBMS). An idea of this system is given below.
Object Relational Model
As the name implies, the object relational model supports both object oriented and relational concepts. It eliminates certain discrepancies in the relational model. In this model, it is possible to provide well-defined interfaces for the application. A structure once created can be reused (this is the fundamental OOP philosophy). By combining the object oriented and relational concepts, Oracle now offers the best of both worlds.
• Introduction to PL/SQL 2/1-10
• Data types and their Usage 2/18-24 ADD SOME MORE TYPES
• Variable Declaration 2/6-7 c 25/513-516
Introduction
Oracle Corporation provides a programming language PL/SQL (Procedural Language/SQL) to help developers create powerful programming code that works in tandem with SQL. PL/SQL extends SQL by adding control structures found in other procedural languages. Procedural constructs blend seamlessly with Oracle SQL, resulting in a powerful, structured language. PL/SQL is unique as it combines the flexibility of SQL with the power and configurability of a third generation language like C. PL/SQL can be used in the Oracle database in the Oracle server as well as in client side application development tools.
Overview of PL/SQL
PL/SQL is a completely portable, high performance transaction processing language. It has been upgraded to support directly most of the new features of the Oracle 8i RDBMS.
Advantages of PL/SQL
PL/SQL offers many benefits to the database developer. Some benefits are listed below:
Support for SQL
PL/SQL allows us to use all SQL data manipulation commands, transaction control commands, SQL functions (except
group functions), operators and pseudocolumns, thus allowing us to manipulate data values in a table more flexibly and effectively.
Higher productivity
PL/SQL can be used to include procedural constructs. Further, PL/SQL remains the same in all environments.
Better performance
Without PL/SQL, Oracle must process SQL statements one at a time. With PL/SQL, an entire block of statements can be processed in a single command line statement. This reduces the time taken to communicate between the application and the Oracle server, thus enhancing performance.
Portability
Applications written in PL/SQL are portable to any operating system or platform on which Oracle is installed.
Integration with Oracle
Both PL/SQL and Oracle have their foundations in SQL. PL/SQL supports all the SQL data types and it integrates PL/SQL with the Oracle data dictionary.
Anatomy of a PL/SQL block
A PL/SQL block can be divided into three components, namely, declaration section, executable section and exception handling section. The following diagram (Fig 2.1) illustrates the PL/SQL block.
The order of these sections is given below:
DECLARE
declarations
BEGIN
executable statements
EXCEPTION
handlers
END;
Variables and objects can be declared in the declarative part, which can be used in the executable part for further manipulations. All procedural statements are included between the BEGIN and END statements. Errors that occur during execution are dealt in the exception handling part.
A PL/SQL block supports the following character set:
• Uppercase and lowercase letters. They are not case sensitive except within strings and character literals.
• Numerals from 0 to 9.
• All special symbols and characters.
• Tab, space and carriage return.
PL/SQL text can contain groups of characters known as lexical units. The following are the lexical units:
• Identifiers (such as product and invoice).
• Literals (such as 123.24 and ‘Chennai’)
• Comments (-- represents a comment)
• Delimiters (simple symbol such as * and compound symbol such as :=)
PL/SQL Program Constructs
PL/SQL offers modularity by providing different programming constructs. The modularity approach of PL/SQL enables a developer to create program modules that improve software reusability and hide the complexity of the execution of a specific operation behind a name.
Let us learn about the programming constructs.
Named PL/SQL constructs
A PL/SQL block can be classified as,
• Named block
• Anonymous block.
A named block can be stored and referenced by other PL/SQL blocks or by the user from the SQL prompt.
Named blocks are divided into,
• Procedures
• Functions
• Packages
• Triggers
Procedure
It is a named block that consists of a series of statements that perform a specific action. It can accept zero or more parameters and return zero or more values.
The general syntax is,
PROCEDURE name [(parameter[, parameter, ...])] IS
[local declarations]
BEGIN
executable statements
[EXCEPTION
exception handlers]
END [name];
The parameter list can hold any of the following modes, namely, in (default), out and in out. The “in” parameter mode is used to pass values to the procedure, the “out” parameter mode is used to specify return values. The “in out” parameter mode is used to pass initial values to the procedure when invoked and it also returns updated values to the caller.
Function
It is a named block that performs a specific action by accepting zero or more parameters and returns only one value.
The general syntax is,
FUNCTION name [(parameter[, parameter, ...])] RETURN datatype IS
[local declarations]
BEGIN
executable statements
[EXCEPTION
exception handlers]
END [name];
Package
It is a collection of procedures and functions. It has two parts – a specification and a body. The specification lists procedures and functions available in the package, their parameters, and constants. The body part contains the code for the procedures and functions.
Packages can be created using the following commands.
• Create Package command
• Create Package body command
The “create package” command defines the package specification. The syntax is,
create package
begin
(executable statements)
end [package_name];
The “create package body” command contains the actual code and it has the following syntax:
create package body
begin
(executable statements)
end [Package _ name];
Trigger
It consists of a series of PL/SQL statements and is attached to a database table. It gets executed when a triggering event such as insert, update or delete occurs in the table. The general syntax is,
Create or replace Trigger
[ before/after] [insert/update/delete] on
[referencing {old [as] old / new [as] new}]
[for each statement/ for each row ] [when
Triggers can be defined to fire once for an entire table when the triggering event occurs, or for each row modified by the triggering event. A trigger can also be set to fire only when one column in a row changes.
Anonymous PL/SQL blocks
An anonymous block consists of a series of PL/SQL statements that are parsed and executed all at the same time. The general syntax is,
DECLARE
declarations
BEGIN
executable statements
EXCEPTION
handlers
END;
Variables in PL/SQL
Simply put a variable is a name for an address in the memory, which can be used to store a value. We can declare variables in the declarative part and use them elsewhere in the body of PL/SQL block. Variables are declared by using the “: =” symbol. Variable names should not contain ampersands (&), hyphens (-) and spaces.
At the time of declaring a variable, its datatype should be specified. PL/SQL uses database datatypes and its own datatypes.
The database datatypes are,
• Number – to store any number
• Char – to store alphanumeric strings (fixed length)
• Varchar2 – to store alphanumeric string (variable length)
• Date – to store dates
• Long – to store large blocks of text, up to 2GB in length
• Raw – to store smaller blocks of data in binary format
• Long Raw – to store large blocks of data in binary format
• Rowid – to store special values called ROWIDs
• BLOB, CLOB, NCLOB, BFILE – to store large objects
The non-database datatypes are,
• Dec, Decimal, Real, Double_precision – to store numbers with decimals
• Int, Integer, Smallint, Natural, Positive, Numeric - to store whole numbers
• Character and Varchar - to store char and varchar2 data
• Boolean - to store TRUE or FALSE
• Table/Record –to store arrays and composite data respectively
The following statement declares a variable and initializes the value.
emp_name Varchar(25) := ‘Suresh’;
Note: A variable can be defined to not accept NULL values by including the NOT NULL keyword.
Constant
A constant is used to define a literal value. We can declare constants in the declarative part and can use them elsewhere in the executable part. To declare a constant, we can make use of the keyword constant. This keyword must precede the data type as shown below.
Salary constant real := 7000.00;
Note: The value of a constant cannot be changed anywhere in the program.
Subtype
PL/SQL allows user-defined datatypes to be defined as subtypes to standard datatypes. The subtype keyword is used to declare those user-defined datatypes. The following block declares a subtype.
Declare
Subtype holidays as Date;
this_day holidays;
Begin
--
This declaration block declares a subtype “holidays”. Then a variable this_day is declared of type “holidays”.
• Control Structures 2/11-18
Writing Executable Statements and Control Structures
We now have a fair idea about PL/SQL. Let us concentrate on the executable section of the PL/SQL block.
The executable section stores all the statements that get processed by Oracle when the block is called. This is the only section that is required for the PL/SQL block to compile.
Nesting PL/SQL blocks
A PL/SQL block can be nested inside another block.
The following structure illustrates nesting of anonymous blocks.
Declare
…
Begin
…
Declare
…
Begin
…
End;
…
End;
The sub-block must appear inside the execution section of the main block.
Example 2.5
This example shows nesting of anonymous blocks.
declare
i integer;
begin
i := 8;
declare
j integer;
begin
j := i + 1;
dbms_output.put_line ( 'The value of j is '|| j);
i := i+1;
end;
dbms_output.put_line ('The value of i is ' || i);
end;
/
In this example, the main block declares an integer i. The sub-block declares an integer j and uses i in its execution section.
The output will be,
The value of j is 9
The value of i is 9
PL/SQL procedure successfully completed.
Scope of variables
An important aspect to be considered when using sub-blocks is the scope of variables used in the sub-blocks. In general, variables declared by the main block have local scope in the main block and global scope in its sub-blocks. Variables declared by a sub-block are local to the sub-block and are not visible to its main block or any other sub-blocks.
In Example 2.5, the variable i declared in the main block is available to the sub-block. If we make any reference to the variable j in the main block, it will generate an error.
Writing Control Structures
The control structures are part and parcel of any “complete” programming language and PL/SQL is no exception. In addition to SQL commands, PL/SQL can also process data using flow of control statements. The flow of control statements can be classified under the following categories:
• Conditional control
• Iterative control
• Sequential control
Conditional Control
A sequence of statements can be executed based on a certain condition using if conditional control statement. There are three forms of if statements available, namely, if-then, if-then-else and if-then- elsif. The simplest form of an if statement is the if-then statement. The syntax is
if condition then
sequence of statements;
end if;
The sequence of statements is executed only if the condition evaluates to true. If it is false or NULL, then, the control passes to the statement after ‘end if’. An ‘else’ clause in the ‘if- then’ statement defines the steps or processes to be performed if the condition is false or NULL. An ‘if-then-elsif’ statement can be used to select one of several mutually exclusive alternatives.
Example 2.6
The following example illustrates the if-then-elsif construct.
declare
dholday date :=to_date('30-sep-01');
begin
if rtrim(upper(to_char(dholday,'day'))) = 'SUNDAY' then
dbms_output.put_line ('Its your day... enjoy...');
elsif rtrim(upper(to_char(dholday,'day'))) = 'SATURDAY' then
dbms_output.put_line ('Sorry.. one more day...');
else
dbms_output.put_line ('Go yaar! today is working day...');
end if;
end;
The output will be,
Its your day... enjoy...
Iterative control
A sequence of statements can be executed any number of times using loop constructs. Loops can be broadly classified into:
• Simple loop
• While loop
• For loop
Simple loop
The loop-exit-end loop statement is the simplest form of loop. The keyword loop is placed before the first statement in the sequence. The keyword end loop is placed after the last statement in the sequence. The exit keyword specifies that the process should break out of the loop. The exit when keywords can be used instead of exit to specify a condition that tells when the process should break out of the loop.
The general syntax is,
loop
…
…
exit when
end loop;
Example 2.7
declare
i number:=10;
begin
loop
dbms_output.put_line(to_char(i));
i := i -1;
exit when i = 0;
end loop;
dbms_output.put_line('countdown complete...');
end;
The output will be,
10
9
8
7
6
5
4
3
2
1
countdown complete...
While-loop
The While loop statement includes a condition associated with a sequence of statement. If the condition evaluates to true, then the sequence of statements will be executed, and again control resumes at the beginning of the loop. If the condition evaluates to false, then the loop is bypassed and the control passes to the next statement after end loop.
Syntax for a while loop follows.
while
loop
sequence_of_statements;
end loop;
For loop
The number of iterations for a while loop is unknown until the loop terminates, whereas the number of iterations in a for loop is known before the loop gets executed.
The syntax is given below.
For counter in [Reverse] lowerbound .. upperbound
loop
sequence_of_statements;
end loop;
By default, iteration proceeds from lowerbound to upper bound. If we use the optional keyword reverse, then, iteration proceeds downwards from upper bound to lowerbound.
Sequential Control
The Goto statement is a sequential control statement and it allows us to branch to a label unconditionally. The label, which is enclosed within double angle brackets (<< and >>), must precede an executable SQL statement or a PL/SQL block. When executed, the goto statement transfers control to the labeled statement or block.
Interacting with the Oracle Database
The previous sections provided a strong base on the PL/SQL concepts. This section concentrates on using PL/SQL with database objects.
Using select statements in PL/SQL block
PL/SQL is seamlessly integrated with the Oracle database. When a select statement is used in a PL/SQL block, the into clause specified with the select statement fetches column values and places them into the variables.
Example 2.9
The following example simply gets a record from the album table and displays the information.
declare
aid char(5);
aname varchar2(50);
begin
select album_id,album_name into aid, aname from album
where album_id='al001';
dbms_output.put_line ('album id is ' || aid);
dbms_output.put_line ('album name is ' || aname);
end;
/
The output will be,
album id is al001
album name is kash
Declaring PL/SQL Variable Types Dynamically
When working with database tables, the PL/SQL variables should have the same datatype and length as that of the columns. PL/SQL provides an easy way to declare variables to match the columns in all aspects.
%type attribute
This attribute is used when declaring variables that refer to the database columns. The general syntax is,
variable_name table_name.column_name%TYPE;
Example 2.10
The code in Example 2.8 is rewritten here. Here, the variables aid and aname make use of the %type attribute to refer to the columns album_id and album_name respectively.
declare
aid album.album_id%type;
aname album.album_name%type;
begin
select album_id,album_name into aid, aname from album
where album_id='al001';
dbms_output.put_line ('album id is ' || aid);
dbms_output.put_line ('album name is ' || aname);
end;
Note: We can use the %type attribute while writing DML statements in PL/SQL also.
• Introduction to Error Handling 2/27-31
• Exceptions, Cursor Management 2/21-27
Handling Exceptions and Errors
We know that an error is called exception in PL/SQL and it can be trapped and handled by exception handlers. A PL/SQL block contains an exception section for handling exceptions. There are three types of exceptions in PL/SQL. They are,
• Pre-defined exception
• User-defined exception
• Internal exception
Pre-defined Exception
A pre-defined exception is defined by Oracle to handle common errors that may occur in the database. Let us discuss some of the most common pre-defined exceptions.
No_Data_Found
This exception is raised when a select statement returns no rows, or a deleted element in the nested table is referenced, or an uninitialized element in an index-by table is referenced.
Invalid_Cursor
This exception is raised when an illegal cursor operation such as closing an unopened cursor occurs.
Invalid_Number
It means that the conversion of character string to a number fails in a SQL statement, because the character string does not represent a valid number.
Cursor_Already_Open
When we try to open a cursor that is already open, this exception will occur. A cursor must be closed before it can be reopened. A cursor for loop automatically opens the cursor to which it refers. Hence, a cursor cannot be opened inside the loop.
Too_Many_Rows
This means that a “select into” statement has returned more than one row.
Zero_Divide
When we try to divide a number by zero, this exception occurs.
Note: The others exception handler can be used to trap unanticipated errors.
Example 2.16
The following PL/SQL block raises an exception and the program exits smoothly.
declare
aname album.album_name%type;
begin
select album_name into aname from album;
exception
when too_many_rows then
dbms_output.put_line ('Select statement returned more than one row');
end;
User-defined Exception
A user-defined exception is an error that is defined by the program. The error that it indicates need not necessarily be an Oracle error. It is used as a means of enforcing business rules in situations where an Oracle error may not necessarily occur. Pre-defined exceptions are implicitly raised. But a user-defined exception must have explicit code to raise it.
A user-defined exception is written as follows:
Declaring the exception
In the declaration section of the PL/SQL block, the exception is declared. The exception name declared in the declaration section will be used in the rest of the PL/SQL block. The following line declares an exception.
qty_exp EXCEPTION;
Testing the exception
The execution section of the PL/SQL block should raise the exception by specifying some condition as explained below
If order_qty>qty_in_hand then
Raise qty_exp;
…
…
Handling the exception
The exception that is raised in the execution section is handled in the exception section of the PL/SQL block. A when clause must be specified to handle the exception and take necessary remedial actions.
Exception
When qty_exp Then
Dbms_output.put_line (‘ Order quantity exceeds stock in hand’);
…
…
Example 2.17
Declare
order_rec order_details%rowtype;
cur_date date;
date_excep Exception;
ordid order_details.order_id%type;
Begin
select sysdate into cur_date from dual;
Select * into order_rec from order_details where order_id='or001';
If order_rec.order_date >cur_date then
ordid := order_rec.order_id;
Raise date_excep;
End if;
Exception
When date_excep then
dbms_output.put_line ('Order date of '|| ordid || ' is later than current date');
End;
This PL/SQL block raises an exception date_excep when the order date of the order “or001” is later than the current date.
Internal Exceptions
A named exception can be associated with a particular Oracle error. This gives the ability to trap the error specifically. A pragma is a compiler directive. These are processed at compile time and not at run time. The PRAGMA EXCEPTION_INIT tells the compiler to associate an exception name with an Oracle error number. This allows referencing of any internal exception by name and writing a handler for it.
The EXCEPTION_INIT pragma can be used as shown below:
pragma exception_init(exception name, Oracle error number);
The exception name is the name of the exception declared and Oracle error number is the desired code to be associated with the named exception. The pragma must be within the declarative section.
Example 2.18
The following PL/SQL block restricts duplicates values for artist_id.
declare
primary_key_excep exception;
pragma exception_init(primary_key_excep,-1);
begin
insert into artist values
('ar007','srikumar','superb filmi singer');
exception
when primary_key_excep then
dbms_output.put_line('duplicate artist id - primary key violation');
end;
Note: When an exception is raised, control is given to the exception handler. After the exception is handled, the PL/SQL block will be exited.
Exception Handling in Nested Blocks
When an exception is raised, control flows to the exception section and an appropriate exception handler handles it. To handle unanticipated errors, we can have the others handler.
Suppose, a procedure first calls another procedure Second. When an exception say divide_by_zero, is raised in Second and if there are no exception handlers in Second, control will be passed to the exception handler in First. If there is a handler to handle divide_by_zero exception, it will take necessary action. Otherwise, an error message will be displayed. The same principle works for sub-blocks also.
Customizing Error Messages
We can define customized error messages that suit our application needs. The Oracle procedure raise_application_error() is used to define error messages and error numbers.
The syntax is,
raise_application_error(error_number, error_message);
The error number should be in the range –20000 and –20999. Error message should not exceed 512 characters.
Example 2.19
The following example illustrates the use of raise_application_error.
Declare
order_rec order_details%rowtype;
cur_date date;
date_excep Exception;
ordid order_details.order_id%type;
Begin
select sysdate into cur_date from dual;
Select * into order_rec from order_details where order_id='or002';
If order_rec.order_date >cur_date then
ordid := order_rec.order_id;
Raise date_excep;
End if;
Exception
When date_excep then
raise_application_error (-20999,'Order date of '|| ordid || ' is later than current date');
End;
Handling Cursors
We know that a cursor is a pointer to the memory area (context area) where SQL statements are processed. There are two types of cursors in PL/SQL. They are,
• Implicit cursor
• Explicit cursor
Implicit cursor
The cursor that points out to an unnamed context area, where SQL statements are processed by Oracle or the PL/SQL mechanism, is called an implicit cursor. Every SQL statement including update, insert, delete is handled by the implicit cursor. The implicit cursor also handles those select statements that do not execute in explicit cursors.
Explicit cursor
An explicit cursor is a pointer to the context area, which is assigned a name. It can handle any type of select statement. Explicit cursor enables a developer to have more control over the execution of a select statement.
Cursor attributes
A cursor attribute is used to find the status of a cursor. Different types of cursor attributes are,
• %found
• %notfound
• %rowcount
• %isopen
%found
This attribute is used to find whether an SQL statement processed any row of data successfully. It evaluates to true, if data was processed successfully.
%notfound
This attribute is the logical opposite of %found. It evaluates to true when the cursor operation was not successful.
%rowcount
This attribute tells the number of rows processed.
%isopen
It tells whether the cursor is open and ready for use.
When an explicit cursor is used, the cursor attributes are referenced in the following format:
mycursor%cursor_attribute
Here, mycursor is the name of the cursor.
To use the cursor attributes with an implicit cursor,
SQL%cursor_attribute
Here, SQL represents the most recently executed SQL statement.
Working with Explicit Cursors
There are four steps to be followed while working with an explicit cursor. They are,
• Declaring the cursor
• Opening the cursor
• Fetching data
• Closing the cursor
A cursor is declared by using the following syntax.
CURSOR cursor_name IS select_statement;
After declaring the cursor, we can open it for processing by using the following syntax.
open Cursor_name;
The open statement executes the select statement, identifies the result set of the select statement and positions the cursor before the first row of the result set.
After opening the cursor, the current row can be fetched for PL/SQL processing. The fetch statement retrieves the current row and advances the cursor to the next row to fetch the remaining rows. The syntax is,
fetch cursor_name into PL/SQL_variable;
To close a cursor, we use the close statement. The syntax is.
Close cursor_name;
Example 2.13
The following PL/SQL block updates the price of albums based on the category. Ghazal albums will be priced at Rs.15 above the current price, Pop albums Rs.10 above the current price. The price of the remaining categories is increased by 5 rupees.
Declare
aid album.album_id%type;
cat album.category%type;
price_incr album.price%type;
Cursor cat_cursor is
Select album_id,category from album;
Begin
Open cat_cursor;
Loop
Fetch cat_cursor into aid,cat;
Exit When cat_cursor%NotFound;
If ltrim(cat) = 'ghazal' then
price_incr :=15.00;
ElsIf ltrim(cat) = 'pop' then
price_incr :=10.00;
Else
price_incr :=5.00;
End If;
Update album set price = price + price_incr Where album_id = aid;
End Loop;
Close cat_cursor;
End;
The Cursor For Loop
A cursor for loop implicitly opens a cursor, repeatedly fetches rows from the result set, and then, closes the cursor when all rows have been processed.
Example 2.14
This example performs the same operation as in Example 2.12. It uses a cursor for loop.
Declare
Cursor cat_cursor is
Select album_id,category from album;
price_incr album.price%type;
Begin
For cat_rec in cat_cursor loop
If ltrim(cat_rec.category) = 'ghazal' then
price_incr :=15.00;
ElsIf ltrim(cat_rec.category) = 'pop' then
price_incr :=10.00;
Else
price_incr :=5.00;
End If;
Update album set price = price + price_incr Where album_id =
cat_rec.album_id;
End Loop;
End;
The advantages of cursor for loop are,
• It opens, fetches and closes the cursor automatically.
• It defines a record to store the fetched results automatically.
In this example, we have not defined a record cat_rec. the cursor for loop automatically defines it.
Advanced Cursor Concepts
Explicit cursors are more powerful in Oracle 8i. Advanced concepts such as passing parameters to cursors, using the for update clause are covered in this section.
Parameterized Cursors
So far, we have declared cursors that would fetch all records from a table or those records that satisfy a specified “hard-coded” value. But, we can declare cursors in such a way that the values from which data will be selected can be specified at the time the cursor is opened. Parameters are used to build this type of cursors.
The syntax is,
CURSOR cursor_name (parameter1, parameter2…parameterN) IS select_statement;
Example 2.15
The following example illustrates a parameterized cursor. The PL/SQL block displays album name and category of album titles from “hmv” and “tips”.
Declare
aname album.album_name%type;
cat album.category%type;
Cursor cat_cursor(cmp in varchar2) is
Select album_name,category from album where company=cmp;
Begin
Open cat_cursor('hmv');
Loop
Fetch cat_cursor into aname,cat;
Exit When cat_cursor%NotFound;
dbms_output.put_line ('album from hmv');
dbms_output.put_line ('album name ' || aname);
dbms_output.put_line ('album category ' || cat);
dbms_output.put_line('');
End Loop;
Close cat_cursor;
Open cat_cursor('tips');
Loop
Fetch cat_cursor into aname,cat;
Exit When cat_cursor%NotFound;
dbms_output.put_line ('album from tips');
dbms_output.put_line ('album id ' || aname);
dbms_output.put_line ('album category ' || cat);
dbms_output.put_line('');
End Loop;
Close cat_cursor;
End;
In this example, the cursor is declared to accept a company name. The company name is given at the time of opening the cursor. Thus, the cursor can be reused with various values for company name.
For Update Clause
The for update clause can be used to write a select statement that fetches data from a table and places a share row exclusive lock on each of the rows.
select * from artist for update;
The above SQL statement states that the records fetched will be locked. The “for update” clause can also be used in cursors.
Declare
Cursor sampcur is
Select * from artist for update;
Begin
…
The rows will be locked until the cursor is processed.
Note: Subqueries can also be used in a cursor.
• Database Triggers c 11/297-308 , c 26/547-562
Trigger
It consists of a series of PL/SQL statements and is attached to a database table. It gets executed when a triggering event such as insert, update or delete occurs in the table. The general syntax is,
Create or replace Trigger
[ before/after] [insert/update/delete] on
[referencing {old [as] old / new [as] new}]
[for each statement/ for each row ] [when
Triggers can be defined to fire once for an entire table when the triggering event occurs, or for each row modified by the triggering event. A trigger can also be set to fire only when one column in a row changes.
Executing Triggers
A trigger fires when a triggering event occurs.
Example 2.4
The following trigger fires whenever a record is inserted to the “artist” table.
create or replace trigger artist_trig
after insert on artist
begin
dbms_output.put_line ('Record successfully inserted');
end;
Packages
• Package specification & body c 27/563-585
Package
It is a collection of procedures and functions. It has two parts – a specification and a body. The specification lists procedures and functions available in the package, their parameters, and constants. The body part contains the code for the procedures and functions.
Packages can be created using the following commands.
• Create Package command
• Create Package body command
The “create package” command defines the package specification. The syntax is,
create package
begin
(executable statements)
end [package_name];
The “create package body” command contains the actual code and it has the following syntax:
create package body
begin
(executable statements)
end [Package _ name];
CREATE PACKAGE emp_mgmt AS
FUNCTION hire(ename VARCHAR2, job VARCHAR2, mgr NUMBER,
sal NUMBER, comm NUMBER, deptno NUMBER)
RETURN NUMBER;
FUNCTION create_dept(dname VARCHAR2, loc VARCHAR2)
RETURN NUMBER;
PROCEDURE remove_emp(empno NUMBER);
PROCEDURE remove_dept(deptno NUMBER);
PROCEDURE increase_sal(empno NUMBER, sal_incr NUMBER);
PROCEDURE increase_comm(empno NUMBER, comm_incr NUMBER);
no_comm EXCEPTION;
no_sal EXCEPTION;
END emp_mgmt;
This SQL statement creates the body of the EMP_MGMT package:
CREATE PACKAGE BODY emp_mgmt AS
tot_emps NUMBER;
tot_depts NUMBER;
FUNCTION hire
(ename VARCHAR2,
job VARCHAR2,
mgr NUMBER,
sal NUMBER,
comm NUMBER,
deptno NUMBER)
RETURN NUMBER IS
new_empno NUMBER(4);
BEGIN
SELECT empseq.NEXTVAL
INTO new_empno
FROM DUAL;
INSERT INTO emp
VALUES (new_empno, ename, job, mgr, sal, comm, deptno,
tot_emps := tot_emps + 1;
RETURN(new_empno);
END;
FUNCTION create_dept(dname VARCHAR2, loc VARCHAR2)
RETURN NUMBER IS
new_deptno NUMBER(4);
BEGIN
SELECT deptseq.NEXTVAL
INTO new_deptno
FROM dual;
INSERT INTO dept
VALUES (new_deptno, dname, loc);
tot_depts := tot_depts + 1;
RETURN(new_deptno);
END;
PROCEDURE remove_emp(empno NUMBER) IS
BEGIN
DELETE FROM emp
WHERE emp.empno = remove_emp.empno;
tot_emps := tot_emps - 1;
END;
PROCEDURE remove_dept(deptno NUMBER) IS
BEGIN
DELETE FROM dept
WHERE dept.deptno = remove_dept.deptno;
tot_depts := tot_depts - 1;
SELECT COUNT(*)
INTO tot_emps
FROM emp;
/* In case Oracle deleted employees from the EMP table
to enforce referential integrity constraints, reset
the value of the variable TOT_EMPS to the total
number of employees in the EMP table. */
END;
PROCEDURE increase_sal(empno NUMBER, sal_incr NUMBER) IS
curr_sal NUMBER(7,2);
BEGIN
SELECT sal
INTO curr_sal
FROM emp
WHERE emp.empno = increase_sal.empno;
IF curr_sal IS NULL
THEN RAISE no_sal;
ELSE
UPDATE emp
SET sal = sal + sal_incr
WHERE empno = empno;
END IF;
END;
PROCEDURE increase_comm(empno NUMBER, comm_incr NUMBER) IS
curr_comm NUMBER(7,2);
BEGIN
SELECT comm
INTO curr_comm
FROM emp
WHERE emp.empno = increase_comm.empno
IF curr_comm IS NULL
THEN RAISE no_comm;
ELSE
UPDATE emp
SET comm = comm + comm_incr;
END IF;
END;
END emp_mgmt;
• PL/SQL tables and records 2/18-21
Working with Composite Data Types
PL/SQL provides constructs that deal with composite datatypes. We can create composite data types such as PL/SQL records, PL/SQL tables.
Creating PL/SQL records
A PL/SQL record is a variable comprised of one or more elements. An element in a record can be of scalar type or of record type. The TYPE record_name IS RECORD statement is used to declare a record. The following skeletal structure declares a record.
Declare
Type product_template is record (
product_id varchar2(5),
product_name varchar2(25));
product product_template;
After declaring a record, we can assign values. The following PL/SQL block assigns values to the record declared above.
Declare
Type product_template is record (
product_id varchar2(5),
product_name varchar2(25));
product product_template;
Begin
product.product_id := 'P0001';
product.product_name := 'Reynolds Pen';
dbms_output.put_line ('product id is ' || product.product_id);
dbms_output.put_line ('product name is ' || product.product_name);
End;
The output will be,
product id is P0001
product name is Reynolds Pen
%rowtype attribute
This attribute provides a record type that represents a row in a table. The record can store an entire row of data selected from the table.
The syntax is,
variable_name table_name%ROWTYPE;
Example 2.11
The following PL/SQL block uses %rowtype to retrieve a row from the “artist” table.
declare
art artist%rowtype;
begin
select * into art
from artist where artist_id='ar001';
dbms_output.put_line (art.artist_id);
dbms_output.put_line (art.artist_name);
dbms_output.put_line (art.artist_profile);
end;
The output will be,
ar001
hariharan
versatile singer- excelling in ghazals and filmi music
Creating PL/SQL Tables
A PL/SQL table is a one-dimensional array of homogeneous elements. It can represent a collection of items say employee names.
There are two types of PL/SQL tables available namely index-by table and nested table. The difference between them is – a nested table can be stored in the Oracle database, the index-by table cannot.
The syntax for an index-by table is,
TYPE roll_no_type IS TABLE OF INTEGER INDEX BY BINARY_INTEGER;
The table roll_no_type can store integers.
The syntax for a nested table is,
TYPE roll_no_type IS TABLE OF INTEGER
Assigning Values to PL/SQL Tables
Oracle provides a special feature called a constructor to initialize a nested table. A constructor is a function, which allows us to construct a table based on elements passed to that function.
Example 2.12
The following PL/SQL block constructs a nested table.
declare
type roll_no_type is table of integer;
rollno roll_no_type;
i binary_integer;
begin
rollno := roll_no_type (1,2,3,4,5);
for i in 1..5 loop
dbms_output.put_line (rollno(i));
end loop;
end;
To construct index-by tables, we have to use for loop to assign values to table elements.
After initializing a table, we can manipulate it in different ways. The following table lists some of the table attributes.
Attribute
Description
Count
Gives the number of elements in the table
Exists (n)
Determines whether the element at the nth position is NULL.
First, Last, Prior, Next
These attributes refer to the first, last, previous and next elements.
Note: We can construct two-dimensional arrays by creating tables of records.
• Varying arrays 2/20-21
• Member Procedures 2/4-5, c 27/553-555
• Functions 2/4-5, c 27/553-555
• Dynamic SQL
It is a named block that consists of a series of statements that perform a specific action. It can accept zero or more parameters and return zero or more values.
The general syntax is,
PROCEDURE name [(parameter[, parameter, ...])] IS
[local declarations]
BEGIN
executable statements
[EXCEPTION
exception handlers]
END [name];
The parameter list can hold any of the following modes, namely, in (default), out and in out. The “in” parameter mode is used to pass values to the procedure, the “out” parameter mode is used to specify return values. The “in out” parameter mode is used to pass initial values to the procedure when invoked and it also returns updated values to the caller.
Function
It is a named block that performs a specific action by accepting zero or more parameters and returns only one value.
The general syntax is,
FUNCTION name [(parameter[, parameter, ...])] RETURN datatype IS
[local declarations]
BEGIN
executable statements
[EXCEPTION
exception handlers]
END [name];
Executing Procedures
A procedure can be executed at the SQL prompt using the execute command (we can use the short form exec).
Example 2.2
The following procedure calculates simple interest.
create or replace procedure simpin(p number, n number, r number) is
sint number(8,2);
begin
sint := p * n *(r/100);
dbms_output.put_line ('simple interest is ' || sint);
end;
/
To execute this procedure,
exec simpin(4500,3,12);
The output will be,
simple interest is 1620
Executing Functions
A function can be called in an anonymous block.
Example 2.3
The following function calculates circumference of a circle.
create or replace function find_circum(
radius in number
) return number is
pi constant number(5,4) := 3.1415;
circum number(10,2);
Begin
circum := 2.0 * pi * radius;
return (circum);
End;
This function is called in the following anonymous block.
declare
radius number := 50;
circum number;
begin
circum := find_circum(radius);
dbms_output.put_line ('circum ' || circum);
end;
/
The output will be,
circum 314.15
PL/SQL procedure successfully completed.
Executing Packaged Procedures and Functions
While using packaged procedures and functions, the package name should also be specified.
No comments:
Post a Comment