PL/SQL Block
PL/SQL block has 3 sections:
- Declare
- Begin
- Exception
Followed by END statement.
PL/SQL Block
PL/SQL blocks consist of codes that describes the process that has to be executed. Blocks consists, both, SQL statements, as well as PL/SQL instructions.
DECLARE Section:
Cursors, triggers and other oracle objects are declared in this section. These declared variables can be used in SQL statements for data manipulation.
BEGIN Section:
This section contains the main logic which is responsible for data retrieval and manipulation.
EXCEPTION Section:
This section handles the errors that might occur between BEGIN and EXCEPTION
END Section:
This section indicates the end of a PL/SQL block.
Basic Examples:
- The first example doesn’t have a DECLARE section:
BEGIN
NULL;
END;BEGIN
DBMS_OUTPUT.PUT_LINE('Hello World!');
END;
/
OUTPUT
Hello World!- In this example, we will DECLARE 2 variables and print their addition:
DECLARE
-- declare variable a and b
-- and these variables have integer datatype
a number;
b number;
BEGIN
a:= 7;
b:= 77;
dbms_output.put_line('Sum of the number is: ' || a + b);
END;
/
OUTPUT
Sum of the number is: 84