Blocks in PL/SQL
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 -- declare variable a, b and c -- and these three variables datatype are integer a number; b number; c number; BEGIN a:= 10; b:= 100; --find largest number --take it in c variable IF a > b THEN c:= a; ELSE c:= b; END IF; dbms_output.put_line( ' Maximum number in 10 and 100: ' || c); END ; / -- Program 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 variable a, b and c -- and these three variables datatype are integer DECLARE a number; b number; c number; --Function return largest number of -- two given 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 ; / -- Program End |
Output:
Maximum number in 10 and 100: 100
Contact Us