Oracle PL/SQL Quick Help : Developers
A PL/SQL Block consists of three sections:
1.The Declaration section (optional).
2.The Execution section (mandatory).
3.The Exception (or Error) Handling section (optional).
Sample below :
DECLARE
Variable declaration
BEGIN
Program Execution
EXCEPTION
Exception handling
END;
Variable declaration
BEGIN
Program Execution
EXCEPTION
Exception handling
END;
PL/SQL Variables
The General Syntax to declare a variable is:
variable_name datatype [NOT NULL := value ];
variable_name is the name of the variable.
datatype is a valid PL/SQL datatype.
NOT NULL is an optional specification on the variable.
value or DEFAULT value is also an optional specification, where you can initialize a variable.
Each variable declaration is a separate statement and must be terminated by a semicolon.
Example :
DECLARE Empcode number(4);
EmpName varchar2(10) NOT NULL := “Abhi”;
assign values to variables
• variable_name:= value;
• SELECT column_nameINTO variable_name FROM table_name [WHERE condition];
• variable_name:= value;
• SELECT column_nameINTO variable_name FROM table_name [WHERE condition];
Scope of Variables
• Local variables – These are declared in a inner block and cannot be referenced by outside Blocks.
• Global variables – These are declared in a outer block and can be referenced by its itself and by its inner blocks.
• Example :
DECLARE
var_num number; // Global variables
BEGIN
var_num := 100;
DECLARE
var_sqr number; // Local variables
BEGIN
var_sqr := var_num * var_num2
END;
END;
PL/SQL Constants
The General Syntax to declare a constant is: constant_name CONSTANT datatype := VALUE;
Example :
DECLARE empCode CONSTANT number (3) := 10;
Conditional Statements in PL/SQL
IF condition 1 THEN
statement 1;
statement 2;
ELSIF condtion2 THEN
statement 3;
ELSE
statement 4;
END IF
Parameters in Procedure and Functions
1) IN type parameter: These types of parameters are used to send values to stored procedures.
CREATE [OR REPLACE] PROCEDURE procedure_name ( param_name1 IN datatype, param_name12 IN datatype … )
2) OUT type parameter: These types of parameters are used to get values from stored procedures. This is similar to a return type in functions.
CREATE [OR REPLACE] PROCEDURE proc2 (param_name OUT datatype)
3) IN OUT parameter: These types of parameters are used to send values and get values from stored procedures.
CREATE [OR REPLACE] PROCEDURE proc3 (param_name IN OUT datatype)
Types of Exception
a) Named System Exceptions
b) Unnamed System Exceptions
c) User-defined Exceptions
Named System Exceptions
• CURSOR_ALREADY_OPEN
When you open a cursor that is already open.
ORA-06511
• INVALID_CURSOR
When you perform an invalid operation on a cursor like closing a cursor, fetch data from a cursor that is not opened.
ORA-01001
• NO_DATA_FOUND
When a SELECT…INTO clause does not return any row from a table.
ORA-01403
• TOO_MANY_ROWS
When you SELECT or fetch more than one row into a record or variable.
ORA-01422
• ZERO_DIVIDE
When you attempt to divide a number by zero.
ORA-01476
Example :
BEGIN
Execution section
EXCEPTION WHEN NO_DATA_FOUND THEN
dbms_output.put_line (‘No record found.’);
END;
User-defined Exceptions
DECLARE
userDefinedExp EXCEPTION;
BEGIN
RAISE userDefinedExp;
EXCEPTION WHEN userDefinedExp THEN
raise_application_error(-2100, ‘The User Defined Exception.’);
END;
RAISE_APPLICATION_ERROR
• RAISE_APPLICATION_ERROR is a built-in procedure in oracle which is used to display the user-defined error messages along with the error number whose range is in between -20000 and -20999.
• RAISE_APPLICATION_ERROR (error_number, error_message);
• The Error number must be between -20000 and -20999
• The Error_message is the message you want to display when the error occurs.
• The Error number must be between -20000 and -20999
• The Error_message is the message you want to display when the error occurs.
Unnamed System Exceptions
DECLARE
rec_exception EXCEPTION;
PRAGMA
EXCEPTION_INIT (rec_exception, -2296);
BEGIN
EXCEPTION
WHEN rec_exception THEN
Dbms_output.put_line(‘Unnamed System Exceptions.’);
END;
