Exception Handling

Exception Handling

In PL/SQL, a warning or error condition is called an exception. Exceptions can be internally defined (by the run-time system) or user defined. Examples of internally defined exceptions include division by zero and out of memory. Some common internal exceptions have predefined names, such as ZERO_DIVIDE and STORAGE_ERROR. The other internal exceptions can be given names.

When an error occurs, an exception is raised. That is, normal execution stops and control transfers to the exception-handling part of your PL/SQL block or subprogram. Internal exceptions are raised implicitly (automatically) by the run-time system. User-defined exceptions must be raised explicitly by RAISE statements, which can also raise predefined exceptions.

To handle raised exceptions, you write separate routines called exception handlers. After an exception handler runs, the current block stops executing and the enclosing block resumes with the next statement. If there is no enclosing block, control returns to the host environment

Example for handling a run time exception given below

Write a PL/SQL to find the factorial of a number entered by the user.Handle any error generated due to invalid data type entered by the user.

DECLARE

num NUMBER
answer NUMBER

BEGIN

num:=# --Here if the value entered by the user is not avalid data type, for eg: if it is --a character data then it will it raise run time error named value_error
ans:=1;
FOR i IN 1..num
LOOP ans:=ans*i;
END LOOP:
DBMS_OUTPUT.PUT_LINE('Fact is:'ans);

EXCEPTION
WHEN VALUE_ERROR
THEN DBMS_OUTPUT.PUT_LINE('The data entered id not valid');
END:

GOTO Statements

GOTO Statements
The GOTO statement lets you branch to a label unconditionally. The label, an undeclared identifier enclosed by double angle brackets, must precede an executable statement or a PL/SQL block. When executed, the GOTO statement transfers control to the labeled statement or block, as the following example shows:

IF rating > 90 THEN
GOTO calc_raise; -- branch to label
END IF;
...
<>
IF job_title = 'SALESMAN' THEN -- control resumes here
amount := commission * 0.25;
ELSE
amount := salary * 0.10;
END IF;

SQL statements in PL/SQL

PL/SQL fully supports all SQL data manipulation statements (except EXPLAIN PLAN), transaction control statements, functions, pseudocolumns, and operators. PL/SQL also supports dynamic SQL, which enables you to execute SQL data definition, data control, and session control statements dynamically.
The following code block shows how to run DML statements in PL/SQL. Basically they look similar to the SQL. Note that the SELECT statement retrieves the single-row value and store into a variable using INTO clause.
DECLARE emp_sal employee.sal%TYPE;BEGIN
INSERT INTO employee VALUES (1, 'Ann Theja', 10000); UPDATE employee SET sal = sal + 5000 WHERE emp_id = 1;
SELECT sal INTO emp_sal FROM employee WHERE emp_id = 6;
DBMS_OUTPUT.PUT_LINE('Salary increased to ' emp_sal);
DELETE FROM employee WHERE emp_id = 1; COMMIT;END;

Executable Commands Section

Executable Commands Section

The Executable commands section starts with the keyword begin.It consists of SQL and PL/SQl executable statements.It may also contain flow control and looping statements,cursoe execution commands etc.
Eg;
Declare
pi constant number(7,2):=3.14;
rad number(4)
area number(7,2)
Begin
For i in 1 to 4
Loop
area:=pi*power(rad,2)
insert into areas values(rad,area);
End Loop;
end;

Inserting Comments in a PL/SQL Block

PL/SQL supports two comment styles: single-line and multi-line.
Single line commenting:Begin the comment with -- (two hyphens). End the comment with a line break.
Multi line commenting:Begin and end the comment with a slash and an asterisk (/*).
Eg:
Declare
--declaring variables
pi constant number(7,2):=3.14;
rad number(4);
area number(7,2);
Begin
/* The following line computes the area of a circle using pi, which
is the ratio between the circumference and diameter. */
area := pi *power(radius,2);
End;

Substitution Variable

A substitution variable is a user variable name preceded by one or two ampersands (&). The substitution variable is used for accepting a value from keyboard.You can assign values to a variable using substitution variable.
Eg: rad:=&radius;

Dispalying Messages

DBMS_OUTPUT:The DBMS_OUTPUT package enables you to display messages on the screen.
There are two procedures under this package that is put_line and put:
PUT_LINE:You can place an entire line of information into the buffer by calling PUT_LINE.When you call PUT_LINE, the item that you specify is automatically followed by an end-of-line marker.
To display messages to the user the SERVEROUTPUT should be set to ON.SERVEROUTPUT is a SQLPlus parameter.
Syntax:
SQL>Set Serverouput on;

Flow Control Statements
flow-of-control statements such as IF-THEN-ELSE, FOR-LOOP, WHILE-LOOP, EXIT-WHEN, and GOTO are supported by PL/SQL. Collectively, these statements can handle any situation.

Conditional Control Statement

The IF-THEN-ELSE statement lets you execute a sequence of statements conditionally. The IF clause checks a condition; the THEN clause defines what to do if the condition is true; the ELSE clause defines what to do if the condition is false or null.

The Syntax of IF clause is given below.

IF
THEN
ELSE

END IF;

Nested IF Statements:
You can nest if conditions within each other.
Syntax:
IF
then
IF
then
end if;
ELSE
end if;

Use of ELSEIF clause:

ELSEIF clause can be used instead of nested if clause.
That way, your code will be easier to read and understand. Compare the following IF statements:

IF condition1 THEN IF condition1 THEN
statement1; statement1;
ELSE ELSIF condition2 THEN
IF condition2 THEN statement2;
statement2; ELSIF condition3 THEN
ELSE statement3;
IF condition3 THEN END IF;
statement3;
END IF;
END IF;
END IF;

Sample program
Consider the program below, which processes a bank transaction. Before allowing you to withdraw 500 from account 3, it makes sure the account has sufficient funds to cover the withdrawal. If the funds are available, the program debits the account. Otherwise, the program inserts a record into an audit table.

DECLARE
acct_balance NUMBER(11,2);
acct CONSTANT NUMBER(4) := 3;
debit_amt CONSTANT NUMBER(5,2) := 500.00;
BEGIN
SELECT bal INTO acct_balance FROM accounts
WHERE account_id = acct
FOR UPDATE OF bal;
IF acct_balance >= debit_amt THEN
UPDATE accounts SET bal = bal - debit_amt
WHERE account_id = acct;
ELSE
INSERT INTO temp VALUES
(acct, acct_balance, 'Insufficient funds');
-- insert account, current balance, and message
END IF;
COMMIT;
END;

NULL Statement

The NULL statement explicitly specifies inaction; it does nothing other than pass control to the next statement. It can, however, improve readability. In a construct allowing alternative actions, the NULL statement serves as a placeholder. It tells readers that the associated alternative has not been overlooked, but that indeed no action is necessary. In the following example, the NULL statement shows that no action is taken for unnamed exceptions:
EXCEPTION
WHEN ZERO_DIVIDE THEN
ROLLBACK;
WHEN VALUE_ERROR THEN
INSERT INTO errors VALUES ...
COMMIT;
WHEN OTHERS THEN
NULL;
END;

Each clause in an IF statement must contain at least one executable statement. The NULL statement is executable, so you can use it in clauses that correspond to circumstances in which no action is taken.

Iterative Control

LOOP statements let you execute a sequence of statements multiple times. You place the keyword LOOP before the first statement in the sequence and the keywords END LOOP after the last statement in the sequence. The following example shows the simplest kind of loop, which repeats a sequence of statements continually:

Simple Loop statement

The simplest form of LOOP statement is the basic (or infinite) loop, which encloses a sequence of statements between the keywords LOOP and END LOOP, as follows
Syntax:
LOOP
sequence of statements
END LOOP;

The EXIT-WHEN statement lets you complete a loop if further processing is impossible or undesirable. When the EXIT statement is encountered, the condition in the WHEN clause is evaluated. If the condition is true, the loop completes and control passes to the next statement. In the following example, the loop completes when the value of rad exceeds 10:
LOOP
...
area := pi*power(rad,2)

EXIT WHEN rad > 10; -- exit loop if condition is true
END LOOP;

FOR_LOOP Statement

The FOR-LOOP statement lets you specify a range of integers, then execute a sequence of statements once for each integer in the range.
Syntax:
FOR counter IN [REVERSE] lower_bound..higher_bound
LOOP
sequence_of_statements
END LOOP;


Let us take the example for calculating area for a circle for different values of radius in the range of 1 to 10.(lower_bound =1 and higher_bound=10)

Eg;
DECLARE
pi CONSTANT NUMBER(7,2):=3.14;
rad NUMBER(4)
area NUMBER(7,2)
BEGIN
FOR i IN 1 .. 10
LOOP
area:=pi*power(rad,2)
INSERT INTO AREA VALUES(rad,area);
END LOOP;
END;

By default, iteration proceeds upward from the lower bound to the higher bound. However, as the example below shows, if you use the keyword REVERSE, iteration proceeds downward from the higher bound to the lower bound. After each iteration, the loop counter is decremented.
FOR i IN REVERSE 1..3
LOOP -- assign the values 3,2,1 to i
sequence_of_statements -- executes three times
END LOOP;

Note:Inside a FOR loop, the loop counter can be referenced like a constant but cannot be assigned values to it.

WHILE_LOOP Statement

The WHILE-LOOP statement associates a condition with a sequence of statements. Before each iteration of the loop, the condition is evaluated. If the condition is true, the sequence of statements is executed, then control resumes at the top of the loop. If the condition is false or null, the loop is bypassed and control passes to the next statement.
Syntax:
WHILE
LOOP
Statements
END LOOP;

Let us take the example for calculating area for a circle for different values of radius in the range of 1 to 10.
Eg;
DECLARE
pi CONSTANT NUMBER(7,2):=3.14;
rad NUMBER(4):=1
area NUMBER(7,2)
BEGIN
WHILE RAD <11
LOOP
area:=pi*power(rad,2)
INSERT INTO AREA VALUES(rad,area);
END LOOP;

How to Edit,Save and Execute a PL/SQL block?

How to Edit,Save and Execute a PL/SQL block?

Editing or creating new sql file:
You can use the edit command for editing an existing file or creating new one

SQL> ed c:\new.sql

If the file new.sql not exixting, then a new file named new.sql will be created.

After entering this command, a note pad file will be opened.You can write the PL/SQL block in this file.

Saving sql file:
Remeber you should save this file as sql file.That is the file extension should be .sql

Executing the Pl/SQL block:

You can execute the pl/sql sql block stored in a file by entering the command
@filename or exec[ute] filename

SQL> @ c:\new.sql

or
SQL> exec new.sql

Assigning values to a variable

Assigning values to a variable

You can assign an initial value to a variable in the declaration section.By default, variables are initialized to NULL.
You can assign values to a variable by using an assignment operator (:=), a colon followed by an equal sign. You place the variable to the left of the operator and an expression to the right. Remember, reserved word cannot be used as a variable.
Eg:
pi number(5,2):=3.14;


Database Values
You can use the SELECT statement to have Oracle assign values to a variable. For each item in the select list, there must be a corresponding, type-compatible variable in the INTO list. An example follows:
DECLARE
my_empno emp.empno%TYPE;
my_ename emp.ename%TYPE;
wages NUMBER(7,2);
BEGIN
...
SELECT ename, sal + comm
INTO last_name, wages FROM emp
WHERE empno = emp_id;

Let us take an example of finding the area of a circle.the result is stored in atable named AREAS,which has two columns to store radius and area values.The area of circle is calculated by squaring the value fot the circle radius nad multiplying that the constant pi.
Before writing the PL/SQL block,you need to create atable named AREAS from the sql prompt.
SQL> Create table areas (radius number(2),area number(7,2));
For writing the PL/SQL block a new file named area.sql is created.
SQL>ed area.sql
PL/SQL block:
Declare
pi constant number(7,2)=3.14;
radius number(5)
area number(14,2)
Begin
Radius:=3
area:=pi*power(radius,2)
insert into areas values(radius,area);
end;
Note:Pl/SQL block always ends with the keyword end
Now save the changes into area.sql file and close the notepad window.
You can execute the PL/SQL block from the SQL command prompt.
SQL>@area.sql
PL/SQl procedure successfully completed..
Now you can verify the result by querying the table area.
SQL>Select * from areas;
Radius Area
3 25.27

DECLARARTIVE PART

DECLARARTIVE PART:
You can declare variables and constants in the declarative part of the PL/SQL block.The declaration section starts with the declare keyword ,follwed by a list of variables and cursor definitions.

Declaring Variable
Declarations allocate storage space for a value, specify its datatype, and name the storage location so that you can reference it.
eg:
name varchar(20);
salary number(7,2);

Here the first declaration names a variable of type varchar. The second declaration names a variable of type number .You can also assign value to the variable at the time of declaration.
By default, variables are initialized to NULL.

Note:PL/SQL does not allow forward references. You must declare a variable or constant before referencing it in other statements, including other declarative statements. For example, the following declaration of maxi is illegal:
maxi INTEGER := 2 * mini; -- illegal
mini INTEGER := 15;

Declaring Constants

In the declaration of a constant, the keyword CONSTANT must precede the type specifier, as the following example shows:
pi CONSTANT number(5,2) := 3.14;
You can also assign constant values via the default keyword.
pi number (9,7) default 3.14

Assigning pre-defined data type to a variable

You can also use pre-defined data type for declaring a variable.The %TYPE attribute is used for this purpose.The %TYPE attribute provides the datatype of a pre defined variable or database column. In the following example, %TYPE provides the datatype of a variable:
Eg:
Bonus number(7,2)
DA bonus%type;
So here the variable DA use the same data type of the variable bonus.
You can also use the %type attribute to declare variables based on definitions of column in a table.
%ROWTYPE:Allows you to declare record type that represent a row in a table.


Eg:
Consider the table AREA,which contains two fields area and radius.
If you want to declare a variable based on the datatype of field radius ,you can use the following statement in the declarative part
rad AREA.radius%type.
So here the variable rad use the same data type of the field radius in the table area.

All about PL/SQL

INTRODUCTION TO PL/SQL

What is PL/SQL?

PL/SQL is Oracle's procedural language extension to SQL. PL/SQL enables you to mix SQL statements with procedural constructs. With PL/SQL, you can define and execute PL/SQL program units such as procedures, functions, and packages.
PL/SQL program units generally are categorized as anonymous blocks and stored procedures.
An anonymous block is a PL/SQL block that appears within your application and it is not named or stored in the database. In many applications, PL/SQL blocks can appear wherever SQL statements can appear.
A stored procedure is a PL/SQL block that Oracle stores in the database and can be called by name from an application. When you create a stored procedure, Oracle parses the procedure and stores its parsed representation in the database. Oracle also allows you to create and store functions (which are similar to procedures) and packages (which are groups of procedures and functions).

Why Use PL/SQL?

Since SQL is a non-procedural language we can not use SQL statements to manipulate Oracle data with procedural features like data flow control statements, variable declaration etc.SQL language also lacks the error handling feature. So an application developer can overcome this limitations by the use of PL/SQL ,which combines the data manipulating power of SQL with the data processing power of procedural languages . The error handling feature is also an important feature provide by PL/SQL.
Another fact is by using PL/SQL block we can minimize the network traffic. The reason behind this is,PL/SQl send an entire block of statements to the Oracle engine at one time.

The Structure of PL/SQL block

A PL/SQL block is having three parts:

A Declarative part , an Executable part and an Exception handling part

The declarative part and Exception handling parts are optional. The order of the parts is logical. First comes the declarative part, in which items can be declared. Once declared, items can be manipulated in the executable part. Exceptions raised during execution can be dealt with in the exception-handling part.