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.
Please write down in the comments any further questions you may have. I’ll be happy to help you.