Unit 1 Introduction To RDBMS

Unit 1 Introduction To RDBMS

What is RDBMS?

RDBMS stands for Relational Database Management System. RDBMS is the basis for SQL, and for all modern database systems like MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access.

A Relational database management system (RDBMS) is a database management system (DBMS) that is based on the relational model as introduced by E. F. Codd.

Features:

1. First of all, its number one feature is the ability to store data in tables. The fact that the very storage of data is in a structured form can significantly reduce iteration time.
2. Data persists in the form of rows and columns and allows for a facility primary key to define unique identification of rows.
3. It creates indexes for quicker data retrieval.
4. Allows for various types of data integrity like (i) Entity Integrity; wherein no duplicate rows in a table exist, (ii)Domain Integrity; that enforces valid entries for a given column by filtering the type, the format, or the wide use of values, (iii)Referential Integrity; which disables the deletion of rows that are in use by other records and (iv)User Defined Integrity;providing some specific business rules that do not fall into the above three.
5. Also allows for the virtual table creation which provides a safe means to store and secure sensitive content.
6. Common column implementation and also multi user accessibility is included in the RDBMS features.

Multi-User Access

RDBMSs allow multiple database users to access a database simultaneously. Built-in locking and transactions management functionality allow users to access data as it is being changed, prevents collisions between two users updating the data, and keeps users from accessing partially updated records.

Privileges

Authorization and privilege control features in an RDBMS allow the database administrator to restrict access to authorized users, and grant privileges to individual users based on the types of database tasks they need to perform. Authorization can be defined based on the remote client IP address in combination with user authorization, restricting access to specific external computer systems.

Network Access

RDBMSs provide access to the database through a server daemon, a specialized software program that listens for requests on a network, and allows database clients to connect to and use the database. Users do not need to be able to log in to the physical computer system to use the database, providing convenience for the users and a layer of security for the database. Network access allows developers to build desktop tools and Web applications to interact with databases.

Speed

The relational database model is not the fastest data structure. RDBMS advantages, such as simplicity, make the slower speed a fair trade-off. Optimizations built into an RDBMS, and the design of the databases, enhance performance, allowing RDBMSs to perform more than fast enough for most applications and data sets. Improvements in technology, increasing processor speeds and decreasing memory and storage costs allow systems administrators to build incredibly fast systems that can overcome any database performance shortcomings.

Maintenance

RDBMSs feature maintenance utilities that provide database administrators with tools to easily maintain, test, repair and back up the databases housed in the system. Many of the functions can be automated using built-in automation in the RDBMS, or automation tools available on the operating system.

Language

RDBMSs support a generic language called “Structured Query Language” (SQL). The SQL syntax is simple, and the language uses standard English language keywords and phrasing, making it fairly intuitive and easy to learn. Many RDBMSs add non-SQL, database-specific keywords, functions and features to the SQL language.

Advantages of RDBMS

The potential of this relational model has been more than justified by its state-of-the-art features. However a brief definition of its advantages also needs to be mentioned to provide totality to its usefulness.

1. Data is stored only once and hence multiple record changes are not required. Also deletion and modification of data becomes simpler and storage efficiency is very high.
2. Complex queries can be carried out using the Structure Query Language. Terms like ‘Insert’, ‘Update’, ‘Delete’, ‘Create’ and ‘Drop’ are keywords in SQL that help in accessing a particular data of choice.
3. Better security is offered by the creation of tables. Certain tables can be protected by this system. Users can set access barriers to limit access to the available content. It is very useful in companies where a manager can decide which data is provided to the employees and customers. Thus a customized level of data protection can be enabled.
4. Provision for future requirements as new data can easily be added and appended to the existing tables and can be made consistent with the previously available content. This is a feature that no flat file database has.

Disadvantages of RDBMS

Like there are two sides to a coin, RDBMS houses a few drawbacks as well.

1. The prime disadvantage of this effective system is its cost of execution. To set up a relational database management system, a special software needs to be purchased. Once it is bought, setting up of data is a tedious task. There will be millions of lines of content to be transferred to the tables. Some cases require the assistance of a programmer and a team of data entry specialists. Care must be taken to ensure secure data does not slip into the wrong hands at the time of data entry.
2. Simple text data can be easily added and appended. However, newer forms of data can be confusing. Complex images, numbers, designs are not easy to be categorized into tables and presents a problem.
3. Structure limits are another drawback. Certain fields in tables have a character limit.
4. Isolated databases can be created if large chunks of information are separated from each other. Connecting such large volumes of data is not easy.

5.      Uses of RDBMS

6.      They find application in disciplines like: Banking, airlines, universities, manufacturing and HR.

7.      Using RDBMS can bring a systematic view to raw data. It is easy to understand and execute and hence enables better decision making as well.

8.      It ensures effective running of an accounting system. Moreover, ticket service and passenger information documentation in airlines, student databases in universities and product details along with consumer demand of these products in industries also comes under the wide usage scope of RDBMS.

9.      So it was all about Relational Database Management System and its advantages, uses, features, disadvantages and how it is different from DBMS.

10.  DIFFERENCE BETWEEN DBMS & RDBMS

11.  The main differences between DBMS and RDBMS are given below:

No.	DBMS	RDBMS
1)	DBMS applications store data as file.	RDBMS applications store data in a tabular form.
2)	In DBMS, data is generally stored in either a hierarchical form or a navigational form.	In RDBMS, the tables have an identifier called primary key and the data values are stored in the form of tables.
3)	Normalization is not present in DBMS.	Normalization is present in RDBMS.
4)	DBMS does not apply any security with regards to data manipulation.	RDBMS defines the integrity constraint for the purpose of ACID (Atomocity, Consistency, Isolation and Durability) property.
5)	DBMS uses file system to store data, so there will be no relation between the tables.	in RDBMS, data values are stored in the form of tables, so a relationship between these data values will be stored in the form of a table as well.
6)	DBMS has to provide some uniform methods to access the stored information.	RDBMS system supports a tabular structure of the data and a relationship between them to access the stored information.
7)	DBMS does not support distributed database.	RDBMS supports distributed database.
8)	DBMS is meant to be for small organization and deal with small data. it supports single user.	RDBMS is designed to handle large amount of data. it supports multiple users.

RDBMS product

**** RDBMS Vendors ****	**** RDBMS Product ****
Computer Associates	INGRES
IBM	DB2
INFORMIX Software	INFORMIX
Oracle Corporation	Oracle
Microsoft Corporation	MS Access
Microsoft Corporation	SQL Server
MySQL AB	MySQL
PostgreSQL Dvlp Grp	PostgreSQL
Sybase	Sybase 11

MS Access

Microsoft Access is a Database Management System (DBMS) from Microsoft that combines the relational Microsoft Jet Database Engine with a graphical user interface and softwaredevelopment tools. It is a part of the Microsoft Office suite of applications, included in the professional and higher editions. This is an introductory tutorial that covers the basics of MS Access.

Features

MS Access Table 

Table is the main feature offered by Microsoft Access. It functions to save data. For example, you can save the data of the stored goods. If you do not make table, you cannot save data. If there is no saved data, the data will not be able to be processed. 

Data Entry Features 

The first feature offered by Microsoft Access is related to data entry. Rather than you enter data manually, you can enter data with Microsoft Access faster and more easily. There are many data entry features offered. For example, you can create table and select objects more easily. That is why it is considered as one of the most useful Microsoft Access features. 

Import & Export 

You can also import data from another source to the table you have created simply. Then, you can save it later. Besides that, you can also export data from Microsoft Access with various options of file format. So, you can choose the format that you want based on your desire. 

MS Access Reports 

Then, you can also make a report from the table that is selected or other sources of data with only one click. It is very simple and easy, is not it? You just need to use the icon of Report Wizard that is available in the toolbar. 

MS Access Queries 

Queries can be considered as a feature offered by Microsoft Access that functions to process data in the tables become understandable information. For example, you can classify the top seller by your customers. It can be based on one table, two tables, or more. With this feature, you can also show the certain data on a table. Besides, you can also show data from more than one table as long as the tables are related each other. Then, you can also operate calculation. 

MS Access Forms 

Forms also belong to one of the features of MS Access. It functions to represent to the user or receive input data from the user in the table or query in form of buttons, grids, etc. From in this program is usually used if you work in master detail transaction.

MySQL

MySQL is an open source SQL database, which is developed by a Swedish company – MySQL AB. MySQL is pronounced as "my ess-que-ell," in contrast with SQL, pronounced "sequel."
MySQL is supporting many different platforms including Microsoft Windows, the major Linux distributions, UNIX, and Mac OS X.
MySQL has free and paid versions, depending on its usage (non-commercial/commercial) and features. MySQL comes with a very fast, multi-threaded, multi-user and robust SQL database server.

Features

• High Performance.
• High Availability.
• Scalability and Flexibility Run anything.
• Robust Transactional Support.
• Web and Data Warehouse Strengths.
• Strong Data Protection.
• Comprehensive Application Development.
• Management Ease.
• Open Source Freedom and 24 x 7 Support.
• Lowest Total Cost of Ownership.

ORACLE

It is a very large multi-user based database management system. Oracle is a relational database management system developed by 'Oracle Corporation'.
Oracle works to efficiently manage its resources, a database of information among the multiple clients requesting and sending data in the network.
It is an excellent database server choice for client/server computing. Oracle supports all major operating systems for both clients and servers, including MSDOS, NetWare, UnixWare, OS/2 and most UNIX flavors.

Features

• Concurrency
• Read Consistency
• Portability
• Self-managing database
• SQL*Plus
• Scheduler
• Resource Manager
• Data Warehousing
• Materialized views
• Table compression
• Parallel Execution
• Analytic SQL
• Data mining
• Partitioning

Unit-2 PLSQL

The PL/SQL programming language was developed by Oracle Corporation in the late 1980s as procedural extension language for SQL and the Oracle relational database. Following are certain notable facts about PL/SQL −

·        PL/SQL is a completely portable, high-performance transaction-processing language.

·        PL/SQL provides a built-in, interpreted and OS independent programming environment.

·        PL/SQL can also directly be called from the command-line SQL*Plus interface.

·        Direct call can also be made from external programming language calls to database.

·        PL/SQL's general syntax is based on that of ADA and Pascal programming language.

·        Apart from Oracle, PL/SQL is available in TimesTen in-memory database and IBM DB2.

Features of PL/SQL

PL/SQL has the following features −

• PL/SQL is tightly integrated with SQL.
• It offers extensive error checking.
• It offers numerous data types.
• It offers a variety of programming structures.
• It supports structured programming through functions and procedures.
• It supports object-oriented programming.
• It supports the development of web applications and server pages.

Advantages of PL/SQL

PL/SQL has the following advantages −

·        SQL is the standard database language and PL/SQL is strongly integrated with SQL. PL/SQL supports both static and dynamic SQL. Static SQL supports DML operations and transaction control from PL/SQL block. In Dynamic SQL, SQL allows embedding DDL statements in PL/SQL blocks.

·        PL/SQL allows sending an entire block of statements to the database at one time. This reduces network traffic and provides high performance for the applications.

·        PL/SQL gives high productivity to programmers as it can query, transform, and update data in a database.

·        PL/SQL saves time on design and debugging by strong features, such as exception handling, encapsulation, data hiding, and object-oriented data types.

·        Applications written in PL/SQL are fully portable.

·        PL/SQL provides high security level.

·        PL/SQL provides access to predefined SQL packages.

·        PL/SQL provides support for Object-Oriented Programming.

·        PL/SQL provides support for developing Web Applications and Server Pages.

PL/SQL Scalar Data Types and Subtypes

PL/SQL Scalar Data Types and Subtypes come under the following categories −

S.No	Date Type & Description
1	Numeric Numeric values on which arithmetic operations are performed.
2	Character Alphanumeric values that represent single characters or strings of characters.
3	Boolean Logical values on which logical operations are performed.
4	Datetime Dates and times.
5	CHAR Fixed-length character string with maximum size of 32,767 bytes
6	VARCHAR2 Variable-length character string with maximum size of 32,767 bytes

DECLARE

   num1 INTEGER;

BEGIN

   null;

END;

PLSQL Block

we will discuss the Basic Syntax of PL/SQL which is a block-structured language; this means that the PL/SQL programs are divided and written in logical blocks of code. Each block consists of three sub-parts −

S.No	Sections & Description
1	Declarations This section starts with the keyword DECLARE. It is an optional section and defines all variables, cursors, subprograms, and other elements to be used in the program.
2	Executable Commands This section is enclosed between the keywords BEGIN and END and it is a mandatory section. It consists of the executable PL/SQL statements of the program. It should have at least one executable line of code, which may be just a NULL command to indicate that nothing should be executed.
3	Exception Handling This section starts with the keyword EXCEPTION. This optional section contains exception(s) that handle errors in the program.

Every PL/SQL statement ends with a semicolon (;). PL/SQL blocks can be nested within other PL/SQL blocks using BEGIN and END. Following is the basic structure of a PL/SQL block −

DECLARE 

   <declarations section> 

BEGIN 

   <executable command(s)>

EXCEPTION 

   <exception handling> 

END;

The 'Hello World' Example

DECLARE 

   message  varchar2(20):= 'Hello, World!'; 

BEGIN 

   dbms_output.put_line(message); 

END; 

/ 

The end; line signals the end of the PL/SQL block. To run the code from the SQL command line, you may need to type / at the beginning of the first blank line after the last line of the code. When the above code is executed at the SQL prompt, it produces the following result –

Output-

Hello World  

PL/SQL procedure successfully completed.

Cursor

A cursor is a pointer to this context area. PL/SQL controls the context area through a cursor. A cursor holds the rows (one or more) returned by a SQL statement. The set of rows the cursor holds is referred to as the active set.

You can name a cursor so that it could be referred to in a program to fetch and process the rows returned by the SQL statement, one at a time. There are two types of cursors −

• Implicit cursors
• Explicit cursors

Implicit Cursors

Implicit cursors are automatically created by Oracle whenever an SQL statement is executed, when there is no explicit cursor for the statement. Programmers cannot control the implicit cursors and the information in it.

Whenever a DML statement (INSERT, UPDATE and DELETE) is issued, an implicit cursor is associated with this statement. For INSERT operations, the cursor holds the data that needs to be inserted. For UPDATE and DELETE operations, the cursor identifies the rows that would be affected.

In PL/SQL, you can refer to the most recent implicit cursor as the SQL cursor, which always has attributes such as %FOUND, %ISOPEN, %NOTFOUND, and %ROWCOUNT. The SQL cursor has additional attributes, %BULK_ROWCOUNT and %BULK_EXCEPTIONS, designed for use with the FORALL statement. The following table provides the description of the most used attributes −

S.No	Attribute & Description
1	%FOUND Returns TRUE if an INSERT, UPDATE, or DELETE statement affected one or more rows or a SELECT INTO statement returned one or more rows. Otherwise, it returns FALSE.
2	%NOTFOUND The logical opposite of %FOUND. It returns TRUE if an INSERT, UPDATE, or DELETE statement affected no rows, or a SELECT INTO statement returned no rows. Otherwise, it returns FALSE.
3	%ISOPEN Always returns FALSE for implicit cursors, because Oracle closes the SQL cursor automatically after executing its associated SQL statement.
4	%ROWCOUNT Returns the number of rows affected by an INSERT, UPDATE, or DELETE statement, or returned by a SELECT INTO statement.

e.g

DECLARE 

   total_rows number(2);

BEGIN

   UPDATE customers

   SET salary = salary + 500;

   IF sql%notfound THEN

      dbms_output.put_line('no customers selected');

   ELSIF sql%found THEN

      total_rows := sql%rowcount;

      dbms_output.put_line( total_rows || ' customers selected ');

   END IF; 

END;

/     

 

 

Explicit Cursors

Explicit cursors are programmer-defined cursors for gaining more control over the context area. An explicit cursor should be defined in the declaration section of the PL/SQL Block. It is created on a SELECT Statement which returns more than one row.

The syntax for creating an explicit cursor is −

CURSOR cursor_name IS select_statement; 

Working with an explicit cursor includes the following steps −

• Declaring the cursor for initializing the memory
• Opening the cursor for allocating the memory
• Fetching the cursor for retrieving the data
• Closing the cursor to release the allocated memory

Declaring the Cursor

Declaring the cursor defines the cursor with a name and the associated SELECT statement. For example −

CURSOR c_customers IS 

   SELECT id, name, address FROM customers; 

Opening the Cursor

Opening the cursor allocates the memory for the cursor and makes it ready for fetching the rows returned by the SQL statement into it. For example, we will open the above defined cursor as follows −

OPEN c_customers; 

Fetching the Cursor

Fetching the cursor involves accessing one row at a time. For example, we will fetch rows from the above-opened cursor as follows −

FETCH c_customers INTO c_id, c_name, c_addr; 

Closing the Cursor

Closing the cursor means releasing the allocated memory. For example, we will close the above-opened cursor as follows −

CLOSE c_customers;

Example

Following is a complete example to illustrate the concepts of explicit cursors &minua;

DECLARE 

   c_id customers.id%type; 

   c_name customerS.No.ame%type; 

   c_addr customers.address%type; 

   CURSOR c_customers is 

      SELECT id, name, address FROM customers; 

BEGIN 

   OPEN c_customers; 

   LOOP 

   FETCH c_customers into c_id, c_name, c_addr; 

      EXIT WHEN c_customers%notfound; 

      dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr); 

   END LOOP; 

   CLOSE c_customers; 

END; 

/

Creating a Procedure

A procedure is created with the CREATE OR REPLACE PROCEDUREstatement. The simplified syntax for the CREATE OR REPLACE PROCEDURE statement is as follows −

CREATE [OR REPLACE] PROCEDURE procedure_name 

[(parameter_name [IN | OUT | IN OUT] type [, ...])] 

{IS | AS} 

BEGIN 

  < procedure_body > 

END procedure_name; 

Where,

·        procedure-name specifies the name of the procedure.

·        [OR REPLACE] option allows the modification of an existing procedure.

·        The optional parameter list contains name, mode and types of the parameters. IN represents the value that will be passed from outside and OUT represents the parameter that will be used to return a value outside of the procedure.

·        procedure-body contains the executable part.

·        The AS keyword is used instead of the IS keyword for creating a standalone procedure.

Example

The following example creates a simple procedure that displays the string 'Hello World!' on the screen when executed.

CREATE OR REPLACE PROCEDURE greetings 

AS 

BEGIN 

   dbms_output.put_line('Hello World!'); 

END; 

/

When the above code is executed using the SQL prompt, it will produce the following result −

Procedure created.

Executing a Standalone Procedure

A standalone procedure can be called in two ways −

·        Using the EXECUTE keyword

·        Calling the name of the procedure from a PL/SQL block

The above procedure named 'greetings' can be called with the EXECUTE keyword as −

EXECUTE greetings;

The above call will display −

Hello World

PL/SQL procedure successfully completed.

The procedure can also be called from another PL/SQL block −

BEGIN 

   greetings; 

END; 

/

The above call will display −

Hello World  

PL/SQL procedure successfully completed. 

Deleting a Standalone Procedure

A standalone procedure is deleted with the DROP PROCEDURE statement. Syntax for deleting a procedure is −

DROP PROCEDURE procedure-name; 

You can drop the greetings procedure by using the following statement −

DROP PROCEDURE greetings; 

Parameter Modes in PL/SQL Subprograms

The following table lists out the parameter modes in PL/SQL subprograms −

S.No	Parameter Mode & Description
1	IN An IN parameter lets you pass a value to the subprogram. It is a read-only parameter. Inside the subprogram, an IN parameter acts like a constant. It cannot be assigned a value. You can pass a constant, literal, initialized variable, or expression as an IN parameter. You can also initialize it to a default value; however, in that case, it is omitted from the subprogram call. It is the default mode of parameter passing. Parameters are passed by reference.
2	OUT An OUT parameter returns a value to the calling program. Inside the subprogram, an OUT parameter acts like a variable. You can change its value and reference the value after assigning it. The actual parameter must be variable and it is passed by value.
3	IN OUT An IN OUT parameter passes an initial value to a subprogram and returns an updated value to the caller. It can be assigned a value and the value can be read. The actual parameter corresponding to an IN OUT formal parameter must be a variable, not a constant or an expression. Formal parameter must be assigned a value. Actual parameter is passed by value.

IN & OUT Mode Example 1

This program finds the minimum of two values. Here, the procedure takes two numbers using the IN mode and returns their minimum using the OUT parameters.

DECLARE 

   a number; 

   b number; 

   c number;

PROCEDURE findMin(x IN number, y IN number, z OUT number) IS 

BEGIN 

   IF x < y THEN 

      z:= x; 

   ELSE 

      z:= y; 

   END IF; 

END;   

BEGIN 

   a:= 23; 

   b:= 45; 

   findMin(a, b, c); 

   dbms_output.put_line(' Minimum of (23, 45) : ' || c); 

END; 

/

When the above code is executed at the SQL prompt, it produces the following result −

Minimum of (23, 45) : 23  

PL/SQL procedure successfully completed.

Creating a Function

A standalone function is created using the CREATE FUNCTION statement. The simplified syntax for the CREATE OR REPLACE PROCEDURE statement is as follows −

CREATE [OR REPLACE] FUNCTION function_name 

[(parameter_name [IN | OUT | IN OUT] type [, ...])] 

RETURN return_datatype 

{IS | AS} 

BEGIN 

   < function_body > 

END [function_name];

Where,

·        function-name specifies the name of the function.

·        [OR REPLACE] option allows the modification of an existing function.

·        The optional parameter list contains name, mode and types of the parameters. IN represents the value that will be passed from outside and OUT represents the parameter that will be used to return a value outside of the procedure.

·        The function must contain a return statement.

·        The RETURN clause specifies the data type you are going to return from the function.

·        function-body contains the executable part.

·        The AS keyword is used instead of the IS keyword for creating a standalone function.

Example

The following example illustrates how to create and call a standalone function. This function returns the total number of CUSTOMERS in the customers table.

We will use the CUSTOMERS table, which we had created in the PL/SQL Variables chapter −

Select * from customers; 

+----+----------+-----+-----------+----------+ 

| ID | NAME     | AGE | ADDRESS   | SALARY   | 

+----+----------+-----+-----------+----------+ 

|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 | 

|  2 | Khilan   |  25 | Delhi     |  1500.00 | 

|  3 | kaushik  |  23 | Kota      |  2000.00 | 

|  4 | Chaitali |  25 | Mumbai    |  6500.00 | 

|  5 | Hardik   |  27 | Bhopal    |  8500.00 | 

|  6 | Komal    |  22 | MP        |  4500.00 | 

+----+----------+-----+-----------+----------+  

CREATE OR REPLACE FUNCTION totalCustomers 

RETURN number IS 

   total number(2) := 0; 

BEGIN 

   SELECT count(*) into total 

   FROM customers; 

   RETURN total; 

END; 

/ 

When the above code is executed using the SQL prompt, it will produce the following result −

Function created.

Calling a Function

While creating a function, you give a definition of what the function has to do. To use a function, you will have to call that function to perform the defined task. When a program calls a function, the program control is transferred to the called function.

A called function performs the defined task and when its return statement is executed or when the last end statement is reached, it returns the program control back to the main program.

To call a function, you simply need to pass the required parameters along with the function name and if the function returns a value, then you can store the returned value. Following program calls the function totalCustomers from an anonymous block −

DECLARE 

   c number(2); 

BEGIN 

   c := totalCustomers(); 

   dbms_output.put_line('Total no. of Customers: ' || c); 

END; 

/

When the above code is executed at the SQL prompt, it produces the following result −

Total no. of Customers: 6  

PL/SQL procedure successfully completed. 

Creating Triggers

The syntax for creating a trigger is −

CREATE [OR REPLACE ] TRIGGER trigger_name 

{BEFORE | AFTER | INSTEAD OF } 

{INSERT [OR] | UPDATE [OR] | DELETE} 

[OF col_name] 

ON table_name 

[REFERENCING OLD AS o NEW AS n] 

[FOR EACH ROW] 

WHEN (condition)  

DECLARE

   Declaration-statements

BEGIN 

   Executable-statements

EXCEPTION

   Exception-handling-statements

END;

Where,

·        CREATE [OR REPLACE] TRIGGER trigger_name − Creates or replaces an existing trigger with the trigger_name.

·        {BEFORE | AFTER | INSTEAD OF} − This specifies when the trigger will be executed. The INSTEAD OF clause is used for creating trigger on a view.

·        {INSERT [OR] | UPDATE [OR] | DELETE} − This specifies the DML operation.

·        [OF col_name] − This specifies the column name that will be updated.

·        [ON table_name] − This specifies the name of the table associated with the trigger.

·        [REFERENCING OLD AS o NEW AS n] − This allows you to refer new and old values for various DML statements, such as INSERT, UPDATE, and DELETE.

·        [FOR EACH ROW] − This specifies a row-level trigger, i.e., the trigger will be executed for each row being affected. Otherwise the trigger will execute just once when the SQL statement is executed, which is called a table level trigger.

·        WHEN (condition) − This provides a condition for rows for which the trigger would fire. This clause is valid only for row-level triggers.

Exception

An exception is an error condition during a program execution. PL/SQL supports programmers to catch such conditions using EXCEPTION block in the program and an appropriate action is taken against the error condition. There are two types of exceptions −

• System-defined exceptions
• User-defined exceptions

Syntax for Exception Handling

The general syntax for exception handling is as follows. Here you can list down as many exceptions as you can handle. The default exception will be handled using WHEN others THEN −

DECLARE 

   <declarations section> 

BEGIN 

   <executable command(s)> 

EXCEPTION 

   <exception handling goes here > 

   WHEN exception1 THEN  

      exception1-handling-statements  

   WHEN exception2  THEN  

      exception2-handling-statements  

   WHEN exception3 THEN  

      exception3-handling-statements 

   ........ 

   WHEN others THEN 

      exception3-handling-statements 

END;

Example

Let us write a code to illustrate the concept. We will be using the CUSTOMERS table we had created and used in the previous chapters −

DECLARE 

   c_id customers.id%type := 8; 

   c_name customerS.Name%type; 

   c_addr customers.address%type; 

BEGIN 

   SELECT  name, address INTO  c_name, c_addr 

   FROM customers 

   WHERE id = c_id;  

   DBMS_OUTPUT.PUT_LINE ('Name: '||  c_name); 

   DBMS_OUTPUT.PUT_LINE ('Address: ' || c_addr); 

EXCEPTION 

   WHEN no_data_found THEN 

      dbms_output.put_line('No such customer!'); 

   WHEN others THEN 

      dbms_output.put_line('Error!'); 

END; 

/

When the above code is executed at the SQL prompt, it produces the following result −

No such customer!  

PL/SQL procedure successfully completed. 

The above program displays the name and address of a customer whose ID is given. Since there is no customer with ID value 8 in our database, the program raises the run-time exception NO_DATA_FOUND, which is captured in the EXCEPTION block.

Raising Exceptions

Exceptions are raised by the database server automatically whenever there is any internal database error, but exceptions can be raised explicitly by the programmer by using the command RAISE. Following is the simple syntax for raising an exception −

DECLARE 

   exception_name EXCEPTION; 

BEGIN 

   IF condition THEN 

      RAISE exception_name; 

   END IF; 

EXCEPTION 

   WHEN exception_name THEN 

   statement; 

END; 

You can use the above syntax in raising the Oracle standard exception or any user-defined exception. In the next section, we will give you an example on raising a user-defined exception. You can raise the Oracle standard exceptions in a similar way.

 

User-defined Exceptions

PL/SQL allows you to define your own exceptions according to the need of your program. A user-defined exception must be declared and then raised explicitly, using either a RAISE statement or the procedure DBMS_STANDARD.RAISE_APPLICATION_ERROR.

The syntax for declaring an exception is −

DECLARE 

   my-exception EXCEPTION; 

Example

The following example illustrates the concept. This program asks for a customer ID, when the user enters an invalid ID, the exception invalid_id is raised.

DECLARE 

   c_id customers.id%type := &cc_id; 

   c_name customerS.Name%type; 

   c_addr customers.address%type;  

   -- user defined exception 

   ex_invalid_id  EXCEPTION; 

BEGIN 

   IF c_id <= 0 THEN 

      RAISE ex_invalid_id; 

   ELSE 

      SELECT  name, address INTO  c_name, c_addr 

      FROM customers 

      WHERE id = c_id;

      DBMS_OUTPUT.PUT_LINE ('Name: '||  c_name);  

      DBMS_OUTPUT.PUT_LINE ('Address: ' || c_addr); 

   END IF; 

EXCEPTION 

   WHEN ex_invalid_id THEN 

      dbms_output.put_line('ID must be greater than zero!'); 

   WHEN no_data_found THEN 

      dbms_output.put_line('No such customer!'); 

   WHEN others THEN 

      dbms_output.put_line('Error!');  

END; 

/

When the above code is executed at the SQL prompt, it produces the following result −

Enter value for cc_id: -6 (let's enter a value -6) 

old  2: c_id customers.id%type := &cc_id; 

new  2: c_id customers.id%type := -6; 

ID must be greater than zero! 

PL/SQL procedure successfully completed. 

Pre-defined Exceptions

PL/SQL provides many pre-defined exceptions, which are executed when any database rule is violated by a program. For example, the predefined exception NO_DATA_FOUND is raised when a SELECT INTO statement returns no rows. The following table lists few of the important pre-defined exceptions −

Exception	Oracle Error	SQLCODE	Description
ACCESS_INTO_NULL	06530	-6530	It is raised when a null object is automatically assigned a value.
CASE_NOT_FOUND	06592	-6592	It is raised when none of the choices in the WHEN clause of a CASE statement is selected, and there is no ELSE clause.
DUP_VAL_ON_INDEX	00001	-1	It is raised when duplicate values are attempted to be stored in a column with unique index.
INVALID_CURSOR	01001	-1001	It is raised when attempts are made to make a cursor operation that is not allowed, such as closing an unopened cursor.
INVALID_NUMBER	01722	-1722	It is raised when the conversion of a character string into a number fails because the string does not represent a valid number.
LOGIN_DENIED	01017	-1017	It is raised when a program attempts to log on to the database with an invalid username or password.
NO_DATA_FOUND	01403	+100	It is raised when a SELECT INTO statement returns no rows.
NOT_LOGGED_ON	01012	-1012	It is raised when a database call is issued without being connected to the database.
PROGRAM_ERROR	06501	-6501	It is raised when PL/SQL has an internal problem.
ROWTYPE_MISMATCH	06504	-6504	It is raised when a cursor fetches value in a variable having incompatible data type.
STORAGE_ERROR	06500	-6500	It is raised when PL/SQL ran out of memory or memory was corrupted.
TOO_MANY_ROWS	01422	-1422	It is raised when a SELECT INTO statement returns more than one row.
VALUE_ERROR	06502	-6502	It is raised when an arithmetic, conversion, truncation, or sizeconstraint error occurs.
ZERO_DIVIDE	01476	1476	It is raised when an attempt is made to divide a number by zero.