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;

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];

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.

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;