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

blank

 

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.

 292 views last month,  2 views today

About Joel Medeiros

blank
I am an experienced Business and Data Analyst, ETL and PL/SQL with over 13 years of experience. My technical skills are focus on Oracle mainly in developing with tools such as PL/SQL, ODI and RIB. You also can check all my post right here https://ittutorial.org/author/joel-medeiros/ Please, feel free to keep in touch by e-mail or Linkedin at: joel.medeiros01@gmail.com | https://www.linkedin.com/in/joel-medeiros/

Check Also

blank

Alter System Flush Buffer Cache in Oracle

Hi, I will explain Alter System Flush Buffer Cache in Oracle in this post.  3,518 views …

Leave a Reply