PL/SQL Block Structure
What is PL/SQL block? PL/SQL block structure divided into three logical blocks. First,
BEGIN block and
END; keywords are compulsory. However, the other two blocks
EXCEPTION are optional block. Technically,
END; is not a block, it is only keyword to end of PL/SQL program.
PL/SQL code is not executed in single line format like SQL. It is always executed by a grouping of code into a single segment called blocks.
PL/SQL block structure follows the divide-and-conquer approach to solve the problem stepwise.
PL/SQL block Structure
PL/SQL Block Structure
Variables and constants are declared, initialized within this section.
Variables and Constants: In this block, declare and initialize variables (and constants). You must have to declare variables and constants in the declarative block before referencing them in a procedural statement.
Declare Variables and Assigning values: You can define a variable name, data type of a variable, and its size. Date type can be CHAR, VARCHAR2, DATE, NUMBER, INT, or any other.
DECLARE -- DECLARE block, declare and initialize values designation VARCHAR2(30); eno number(5) := 5; id BOOLEAN; inter INTERVAL YEAR(2) TO MONTH; BEGIN -- BEGIN block, also assign values designation := UPPER('Web Developer'); id := TRUE; inter := INTERVAL '45' YEAR; END; /
Declare Constants and Assigning values: Constants are declared the same as a variable, but you have to add the CONSTANT keyword before defining the data type. Once you define, a constant value, you can't change the value.
designation CONSTANT VARCHAR2(30) := 'Web Developer';
BEGIN block is a procedural statement block which will implement the actual programming logic. This section contains conditional statements (if...else), looping statements (for, while) and Branching Statements (goto), etc.
PL/SQL easily detects a user-defined or predefined error condition. PL/SQL is famous for smartly handling errors by giving suitable user-friendly messages. Errors can be rise due to the wrong syntax, bad logical, or not passing validation rules.
You can also define exception in your declarative block, and later you can execute it by RAISE statement.
DECLARE check_exist EXCEPTION; -- declare exception type ... BEGIN .... RAISE check_exist; -- raise exception .... EXCEPTION WHEN check_exist THEN -- execute raise exception ..... END; /
- BEGIN block, and
END;keyword are compulsory of any PL/SQL program.
- Whereas, the DECLARE and EXCEPTION block are optional.