Here we will be discussing about Oracle PLSQL Block Structure and Oracle PLSQL Variable
What is PL/SQL
Why Use PLSQL
PLSQL Block Structure
PLSQL Block Types
Difference Between Function and Procedure
Oracle PLSQL Variables
Scope of Oracle PLSQL Variables
What is PL/SQL
PL/SQL is Oracle’s proprietary extension to SQL that allowsfor procedural language capabilities. It gives all the features of modern software engineering such as data encapsulation, exception handling, information hiding and object orientation
Why Use PLSQL
1) Integration with development tools like Oracle forms, Reports
2) Multiple statement can be grouped into single plsql block and send to the oracle server which result in less network traffic
3) Modularized program development: Breakdown the complex logic/problem into set of manageable, well defined, logical modules and implement them using blocks
4) Portability: Since it is native to Oracle server, it can be moved easily
5) It provide procedural language control structures like if condition, looping
6) It has the capability to handle errors and raise exception based on it
PLSQL Block Structure
A plsql block is made in three section Declare, executable and exception.
| DECLARE(Optional) – this section contains variables, constants, cursors, and user-defined exceptions.
BEGIN(Mandatory) EXCEPTION (optional)- this section contains the error handlers. END(Mandatory);
|
So Declare and exception section are Optional.
Example Block structure
| DECLARE
l_number NUMBER; BEGIN l_number := 1; Dbms_output.put_line(l_number); Exception When others then Dbms_output.put_line(‘Exception occurred’); END; / |
Important points to Note down
1) All the variables and constants are defined in declare section
2) Place a semicolon ; at the end of sql statement or plsql control statement
3) In PL/SQL all the errors are handled in the Exception block.
4) Begin and End are mandatory statements indicating begin and end of the PL/SQL Block.
5) Variables and Constants must be declared first before they can be used.
6) Values can be assigned to variables directly using the “:=” assignment operator, by way of a SELECT … INTO statement or When used as OUT or IN OUT parameter from a procedure.
7) Section keyword Declare, Begin ,exception are not followed by semicolon
8) End and all other plsql statement requires a semicolon to terminate the statement
PLSQL Block Types
Anonymous: Anonymous Blocks are unnamed blocks. They are declared at the point in application where they need to be executed
| DECLARE
l_number NUMBER; BEGIN l_number := 1; Dbms_output.put_line(l_number); Exception When others then Dbms_output.put_line(‘Exception occurred’); END; /
|
Function: These are names PLSQL block that can accept parameter and compute some function and return it. It can be stored in Oracle server or application
| Syntax
FUNCTION name [(parameter[, parameter, …])] RETURN datatype IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];
Example Create or replace function FUNC return number As l_number NUMBER; BEGIN Select count(*) into l_number from emp; return(l_number) END; /
|
Procedure: These are names PLSQL block that can accept parameter and process some information and may or may return values. It can be stored in Oracle server or application
| Syntax
PROCEDURE name [(parameter[, parameter, …])] IS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];
Create or replace procedure remove_emp (emp_id number) As BEGIN Delete from emp where employee_id=emp_id; END; /
|
Difference Between Function and Procedure
| Function | Procedure |
| Function must return a value | Procedure need not |
| Syntax | Syntax |
| FUNCTION name (argument list …..) Return datatype is | PROCEDURE name (parameter list…..) |
| local variable declarations | is |
| Begin | local variable declarations |
| executable statements | BEGIN |
| Exception | Executable statements. |
| execution handlers | Exception. |
| End; | exception handlers |
| end; | |
| Function can be used in SQL with some restriction | Procedure cannot be called directly from SQL. |
Oracle PLSQL Variable
1) Variables and constants must be declared for use in procedural and SQL code, although the datatypes available in SQL are only a subset of those available in PL/SQL.
2) All variables and constants must be declared before they are referenced.
3) Remember that each variable must be declared on a separate line and multiple variables cannot be defined in the same statement.
4) The declarations of variables and constants are similar, but constant definitions must contain the CONSTANT keyword and must be assigned a value as part of the definition. Subsequent attempts to assign a value to a constant will result in an error
5) %TYPE – allows for unknown column datatypes when assigning a variable. That is used to declare a variable that is of the same type as a specified table’s column
6) BOOLEAN variables can only accept values of TRUE, FALSE, and NULL.
7) ( : ) is used to prefix a bind variable.
8) By default Oracle PLSQL variable are initialized as Null. Unless you explicitly assigned a value to the variable, it is undefined
9) We can have Not Null on variable, then we must assign some value to it. We can change the value of that variable but we cannot make it null
10.We can initialized variable using the assignment operator (:=) or using Define
General syntax
| variable_name datatype [NOT NULL := value ]; |
1) variable_name is the name of the variable.
2) datatype is a valid PL/SQL datatype.
Example of declaring Variables:
| v_name varchar2(100);
creation_date Date; v_num number default 5; v_num1 number not null := 2; v_emp varchar2(20) := Null; Var5 varchar2(20) default Null;
|
Example of declaring Constants:
| v_global constant number := 200;
Constants cannot be changed. You must initialize constants at the time of declaration.
|
Scope of Oracle PLSQL Variable
PL/SQL allows the nesting of Blocks within Blocks i.e, the Execution section of an outer block can contain inner blocks. Therefore, a variable which is accessible to an outer Block is also accessible to all nested inner Blocks. The variables declared in the inner blocks are not accessible to outer blocks. Based on their declaration we can classify variables into two types.
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.
Types of Variable
1) Scalar datatype: It holds single value. The main datatypes are the datatypes which are supported as column type in Oracle server. PLSQL also support Boolean type
2) Composite type: These are records and allows groups of field to be defined and manipulated in PLSQL. Composite datatypes also known as collection are table, record ,Varray and nested table. Composite datatype has internal components which can be manipulated individually
Oracle PLSQL Block Syntax and Guidelines
1) Character and date literals must be enclosed in single quotation marks
2) Place multiple line comments between /* and */
3) Most of the sql functions can be used in PLSQL .We have char,date,number function available in PLSQL just like SQL
4) group by function are not available in PLSQL. It can be only used in the sql statement in the PLSQL
5) We should use proper indentation to make the code readable
6) It is recommended to write DML statement in Upper case , PLSQL Keywords ,Datatypes in Upper case and Put identifiers and parameter in lower case for better readability and maintenance
Related links
Most commonly asked 25 Oracle PlSQL interview questions