The structure you use in PL/SQL is the foundation for the language as a whole. Once you’ve mastered it, you will then be able to move forward; however, if you do not take the time to get this first step right, your journey will be difficult. Thankfully, it’s quite simple.
The structure is quite basic. You will have areas for your program parameters (these are used to pass values from outside a program to the program itself), your internal variables, the main program code and logic, and various ways to deal with problem situations. Let’s look at the basic form of a PL/SQL block:
[DECLARE]
-- Put Variables Here
BEGIN
-- Put Program Here [EXCEPTION]
-- Put exception handlers here
END;
/That’s it: the basic structure of every PL/SQL program. When we talk about PL/SQL programs, they are referred to as PL/SQL blocks. PL/SQL blocks are simply programs that are complete and that are programmed to run successfully. A PL/SQL program comprises one or more of these blocks, which you can think of as routines. So at the basic level, you only need one block for a valid PL/SQL program, but as you consider writing a more complex program, you will find it easiest if each block addresses a particular task or subtask; it is these structures that you will use in all of your PL/SQL programs to create the most robust code possible. The PL/SQL block structures form the basis for any program you write in PL/SQL. My blog builds upon that fundamental form, each section helping you move toward more complex programs.
In its basic form, you will usually need to declare variables in your PL/SQL program. It is these variables used in the PL/SQL that hold the declarative or working storage area (including constants, local program variables, select statements, data arrays, and such) within your program. These variables are then available for use in your program. So if you need a counter, a data array, data variables, or even Boolean variables, you will declare them here.
Next is the program body or executable section. It is the only section you really need to include in your PL/SQL block because you could write a program without variables or exception handling. It is in this section that you build your program logic and database access. That is why you must always remember BEGIN and END; these are your PL/SQL bookends. It is between these two lines that your program logic is contained.
Ask the Expert
Q: What are the only lines of the PL/SQL block that are required to create a functional program?
A: The only lines of the basic PL/SQL block that are required to create a functional program are
BEGINandEND.
