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;
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;
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;
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.
1,232 views last month, 3 views today