Mastering SQL for Data Science: A Comprehensive Guide
Day-11
Exception
Handling & Stored Procedure
v Exceptions and Trapping Errors:
An Exception is raised when an error occurs.
In case of error, normal execution stops and the control is transferred to the
exception handling part of PL/SQL block. Sometimes the oracle server or the
user application causes an error to occur during runtime processing. These
errors are known as exception.
Ø Exception:
One
of the features of PL/SQL is the exception handling mechanism. By using
exceptions and exception handlers, we can make our PL/SQL programs robust and
able to deal with both unexpected and expected errors during execution. Errors
can be classified into runtime error and compile time error.
Exceptions
are designed for run time error handling. Errors that occur during the
compilation time are detected by PL/SQL engine and reported back to the user.
When
an error occurs, an exception is raised. When this happens, control is passed
to the exception handler, which is the separate section of the PL/SQL block.
There are two types of Exceptions;
Predefined and User defined
ü Predefined Exception:
The predefined exception is raised implicitly / automatically when PL/SQL block or any of its statements violets oracle rule. Those errors, which are frequently occur, are assign as a predefined exception by oracle.
Syntax:
Declare
……
Begin
……
Exception
When <exception 1> then
<statements>;
When <exception 2> then
<statements>;
E.g:
declare
n temp.no%type;
nm temp.name%type;
c temp.city%type;
begin
n:=&n;
select no,name,city
into n,nm,c
from temp
where no=n;
dbms_output.put_line(n||' '||nm||' '||c);
exception
when no_data_found then
dbms_output.put_line('please enter valid no');
end;
/
ü User-defined Exception:
Unlike
internal exceptions, user-defined exceptions should be explicitly specified.
The user-defined exception must be declared in the declaration part of PL/SQL
block and it can be explicitly raised with raise statement within begin block.
Declaration of user defined exception is declares a name of error that the
PL/SQL code block recognizes. The raise exception procedure should only be used
when oracle does not raise its own exception or when processing is undesirable
or impossible to complete.
Steps for trapping a user defined error
include the following;
1) 1. Declare the name of the user defined
exception within the declaration section.
2) 2. Raise the exception explicitly within the
executable portion of the block using the raise statement.
3) 3. Reference the declared exception with an
error-handling routine.
E.g:
declare
payment
number;
i_pay
exception;
begin
payment:=&payment;
if payment not between 10000 and
20000 then
raise
i_pay;
end if;
exception
when i_pay then
dbms_output.put_line('please enter valid payment between 10000
to 20000');
end;
/
v Sub Programs:
So far we have seen
PL/SQL block which are executed by interactively entering the block at the SQL
prompt or by writing the PL/SQL statements in a user named file and executing
the block at SQL prompt using @ command.
Any PL/SQL block
consists of some hundreds statements, in such cases it is necessary to break
the entire block into smaller modules depending on your requirements. So, your
block became more easy to understand and efficient to perform operation and
maintenance.
Store sub program are
compiled at time of creation and stored in the database itself.
Procedures and functions
are sub programs having group of SQL, PL/SQL and java enables statements you to
mode code that enforce the business rules from your application to database.
The only real difference between a procedure and a function is that a function
will include a single return value. A stored procedure or function has the
following characteristics;
1)
It has a name:
This is the name by
which the stored procedure or function is called and referenced.
2)
It takes parameter:
These are the values sent to the stored procedure or function from the
application.
3)
It returns values:
A stored procedure or function can return one or more values based on the
purpose of the procedure or function.
Ø Procedure:
A procedure is a one
kind of subprogram, which is designed and created to perform a specific
operation on data in your database. A procedure takes zero or more input
parameters and returns zero or more output parameters.
Syntax:
Create or replace procedure
<procedure_name>[(argument1)[in|out|inout]data
type,(argument2)[in|out|inout]datatype……….)]
Is
[<local
variable declaration>]
Begin
Executable
statements;
End;
/
The procedure is made up
two parts; the declaration and the body of the procedure. The declaration part
begins with the keyword procedure and ends with the last parameter declaration.
The body begins with the keyword is and ends with the keyword end. The procedure
body is further divided into three parts; declarative, executable part same as
PL/SQL block. Parameters can be define in following format;
Argument
[parameter mode] data type
There are three types of parameters mode; In, Out, Inout
IN Mode:
ü Default parameter mode
ü Used to pass values to the procedure
ü Formal parameter can be a constant, literal, initialized variable or
expression
ü Used for reading purpose
OUT Mode:
ü Used to return values to the caller
ü Formal parameter cannot be used, in an expression, but should be assigned a
value.
ü Used for writing purpose.
INOUT Mode:
ü Used to pass values to the procedure as well as return values to the caller
ü Formal parameter acts like an initialized variable and should be assigned a
value.
ü Used for reading and writing purpose.
Note:
In a
procedure declaration, it is illegal to constrain char and varchar2 parameter
with length and number parameter with precision and scale
E.g: Procedure without a call block
create or replace procedure p1(n in number)
is
nm student.name%type;
begin
select name
into nm
from student
where no=n;
dbms_output.put_line(n||' '||nm);
exception
when no_data_found then
dbms_output.put_line('your
record is not available');
end;
/
For execute above procedure
Exec p1(5);
E.g: procedure with a call block
create or replace
procedure p_emp(n
in number,nm out varchar2,
c out varchar2, s out number)
is
begin
select name,city,sal
into nm,c,s
from employee
where no=n;
end;
/
--call block
declare
n employee.no%type;
nm employee.name%type;
c employee.city%type;
s employee.sal%type;
begin
n:=&n;
p_emp(n,nm,c,s);
dbms_output.put_line(n||'
'||nm||' '||c||' ' ||s);
end;
/
0 Comments