Oracle 9i Tutorial

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;

Leave a Reply