Mastering SQL for Data Science: A Comprehensive Guide Day-11
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. Declare the name of the user defined exception within the declaration section.
2. Raise the exception explicitly within the executable portion of the block using the raise statement.
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]datatype, (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 rom 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;
/