Blocks in PL/SQL
Last Updated :
15 Apr, 2023
In PL/SQL, All statements are classified into units that is called Blocks. PL/SQL blocks can include variables, SQL statements, loops, constants, conditional statements and exception handling. Blocks can also build a function or a procedure or a package.
The Declaration section: Code block start with a declaration section, in which memory variables, constants, cursors and other oracle objects can be declared and if required initialized.
The Begin section: Consist of set of SQL and PL/SQL statements, which describe processes that have to be applied to table data. Actual data manipulation, retrieval, looping and branching constructs are specified in this section.
The Exception section: This section deals with handling errors that arise during execution data manipulation statements, which make up PL/SQL code block. Errors can arise due to syntax, logic and/or validation rule.
The End section: This marks the end of a PL/SQL block.
Broadly, PL/SQL blocks are two types: Anonymous blocks and Named blocks are as follows:
1. Anonymous blocks: In PL/SQL, That’s blocks which is not have header are known as anonymous blocks. These blocks do not form the body of a function or triggers or procedure. Example: Here a code example of find greatest number with Anonymous blocks.
SQL
DECLARE
a number;
b number;
c number;
BEGIN
a:= 10;
b:= 100;
IF a > b THEN
c:= a;
ELSE
c:= b;
END IF;
dbms_output.put_line( ' Maximum number in 10 and 100: ' || c);
END ;
/
|
Output:
Maximum number in 10 and 100: 100
2. Named blocks: That’s PL/SQL blocks which having header or labels are known as Named blocks. These blocks can either be subprograms like functions, procedures, packages or Triggers. Example: Here a code example of find greatest number with Named blocks means using function.
SQL
DECLARE
DECLARE
a number;
b number;
c number;
FUNCTION findMax(x IN number, y IN number)
RETURN number
IS
z number;
BEGIN
IF x > y THEN
z:= x;
ELSE
Z:= y;
END IF;
RETURN z;
END ;
BEGIN
a:= 10;
b:= 100;
c := findMax(a, b);
dbms_output.put_line( ' Maximum number in 10 and 100 is: ' || c);
END ;
/
|
Output:
Maximum number in 10 and 100: 100
Share your thoughts in the comments
Please Login to comment...