{"id":14423,"date":"2020-04-18T15:17:18","date_gmt":"2020-04-18T15:17:18","guid":{"rendered":"https:\/\/ittutorial.org\/?p=14423"},"modified":"2020-04-27T23:13:01","modified_gmt":"2020-04-27T23:13:01","slug":"pl-sql-for-practitioners-1-block-structure","status":"publish","type":"post","link":"https:\/\/ittutorial.org\/pl-sql-for-practitioners-1-block-structure\/","title":{"rendered":"PL\/SQL For Practitioners – #1 Block Structure"},"content":{"rendered":"

Hi,<\/p>\n

 <\/p>\n

PL\/SQL is a block-structured language whose code is organized into blocks. A PL\/SQL block consists of three sections, declaration (non-required)<\/strong>, executable (required)<\/strong>, and exception-handling (non-required)<\/strong> 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.<\/p>\n

 <\/p>\n

The basic structure<\/h2>\n
DECLARE --(optional)\r\n  -- Here is where you can declare variables in general when needed.\r\n\r\nBEGIN      --(required)\r\n  -- Executable code\r\n\r\n[EXCEPTION -- (optional)\r\n  -- error-handling if needed\r\nEND;\r\n<\/pre>\n
 <\/p>\n
Example 1<\/h2>\n
This is a simple anonymous block that will show the current date.<\/p>\n
DECLARE \r\n  CUR_DATE DATE; \r\nBEGIN \r\n  CUR_DATE := sysdate; \r\n  DBMS_OUTPUT.PUT_LINE('THE CURRENT DATE IS: ' || CUR_DATE); \r\nEND;<\/pre>\n
\"\"<\/p>\n
Example 2<\/h2>\n
Another example might be the following script. It is responsible to check whether the city has more than 1000000 inhabitants.<\/p>\n
DECLARE\r\n \u00a0 \u00a0COUNT_BIG_CITIES NUMBER := 0;\r\nBEGIN\r\n \u00a0 \u00a0FOR C IN (SELECT POPULATION FROM CITY)\r\n \u00a0 \u00a0LOOP\r\n \u00a0 \u00a0 \u00a0IF C.POPULATION > 1000000 THEN\r\n \u00a0 \u00a0 \u00a0 \u00a0COUNT_BIG_CITIES := COUNT_BIG_CITIES + 1;\r\n \u00a0 \u00a0 \u00a0END IF;\r\n \u00a0 \u00a0END LOOP;\r\n \u00a0 \u00a0DBMS_OUTPUT.PUT_LINE('There are ' || COUNT_BIG_CITIES || ' big cities'); \r\nEND;<\/pre>\n
Result<\/strong><\/p>\n
\"\"<\/p>\n
 <\/p>\n
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.<\/p>\n
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.<\/p>\n
PL\/SQL for real world – 8 features that you have to know<\/a><\/p><\/blockquote>\n