PL/SQL For Practitioners – #1 Block Structure

Hi,

PL/SQL is a block-structured language whose code is organized into blocks. A PL/SQL block consists of three sections, declaration (non-required), executable (required), and exception-handling (non-required) sections. All kinds of structures that a developer might create in PL/SQL such as procedures, functions, packages, and types are based on this primary structure. It will vary from one object to another, but they will be very similar.

The basic structure

DECLARE --(optional)
  -- Here is where you can declare variables in general when needed.

BEGIN      --(required)
  -- Executable code

[EXCEPTION -- (optional)
  -- error-handling if needed
END;

Example 1

This is a simple anonymous block that will show the current date.

DECLARE 
  CUR_DATE DATE; 
BEGIN 
  CUR_DATE := sysdate; 
  DBMS_OUTPUT.PUT_LINE('THE CURRENT DATE IS: ' || CUR_DATE); 
END;

Example 2

Another example might be the following script. It is responsible to check whether the city has more than 1000000 inhabitants.

DECLARE
    COUNT_BIG_CITIES NUMBER := 0;
BEGIN
    FOR C IN (SELECT POPULATION FROM CITY)
    LOOP
      IF C.POPULATION > 1000000 THEN
        COUNT_BIG_CITIES := COUNT_BIG_CITIES + 1;
      END IF;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('There are ' || COUNT_BIG_CITIES || ' big cities'); 
END;

Result

Well, this is only a small example of what we can do with blocks in PL/SQL. This is the central structure for almost everything we do while developing for Oracle PL/SQL.

This post is part of a series about the most common structures in PL/SQL. You can check the rest of them in the link below.

PL/SQL for real world – 8 features that you have to know

Please write down in the comments any further questions you may have. I’ll be happy to help you.