PL/SQL the Block Structured Language

View All Scripts Login to Run Script

Script Name PL/SQL the Block Structured Language
Description This script demonstrates some basic aspects of the PL/SQL block. A PL/SQL block is defined by the keywords DECLARE, BEGIN, EXCEPTION, and END. These keywords divide the block into a declarative part, an executable part, and an exception-handling part. Only the executable part is required. A block can also have a label, essentially giving a name to an otherwise "anonymous" block.
Area PL/SQL General
Contributor Steven Feuerstein
Created Sunday January 28, 2018

Statement 1
An anonymous block with no declaration or exception sections, and just a single "do nothing" statement in the executable section.
Most Trivial Block Ever
```
BEGIN 
   NULL; 
END;
```
```
Statement processed.
```
Statement 2
Another very simple anonymous block, this time doing nothing more than greeting the world.
Hello World!
```
BEGIN 
   DBMS_OUTPUT.put_line ('Hello World'); 
END;
```
```
Hello World
```
Statement 3
The declaration section is optional, but usually present, since most blocks (especially stored program units) use local variables to get the job done. Note that in stored procedures and functions, the declaration section falls between the IS/AS keyword and the BEGIN keyword. DECLARE only appears in anonymous and nested blocks.
Block With Declaration Section
```
DECLARE 
   c_message   CONSTANT VARCHAR2 (100) := 'Hello World'; 
BEGIN 
   DBMS_OUTPUT.put_line (c_message); 
END;
```
```
Hello World
```

Statement 4

Now all three possible sections of a block are present: declaration, executable, exception.

Block With Declaration and Exception Sections

DECLARE  
   l_message   VARCHAR2 (10);  
BEGIN  
   l_message := 'Hello World';  
   DBMS_OUTPUT.put_line (l_message);  
EXCEPTION  
   WHEN VALUE_ERROR  
   THEN  
      DBMS_OUTPUT.put_line ('Variable too small!');  
      RAISE;  
END;

ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at line 10
ORA-06512: at line 4
ORA-06512: at "SYS.DBMS_SQL", line 1721

More Details: https://docs.oracle.com/error-help/db/ora-06502

Statement 5
You can nest one block within another. Why would you do this? To defer the declaration of variables until needed and/or to constrain the propagation of an exception (that is, handle it, and then possibly continue).
Nested Blocks
```
BEGIN  
   BEGIN  
      DBMS_OUTPUT.put_line ('Hello World');  
   END;  
 
   BEGIN  
      DBMS_OUTPUT.put_line ('Hello SQL');  
   END;  
END;
```
```
Variable too small!
Hello World
Hello SQL
```

Statement 6

It's very common for developers to declare many variables at the top of their too-long procedure or function, even if they are not needed till waaaaay down in the executable section.

Front-Loaded Declarations

DECLARE  
   l_message    VARCHAR2 (100) := 'Hello World';  
   l_message2   VARCHAR2 (10);  
BEGIN  
   FOR indx IN 1 .. 5  
   LOOP  
      l_message := l_message || ' and ' || indx;  
   END LOOP;  
  
   DBMS_OUTPUT.put_line (l_message);  
  
   BEGIN  
      l_message2 := SUBSTR (l_message, 1, 10);  
      DBMS_OUTPUT.put_line (l_message2);  
   END;  
END;

Hello World and 1 and 2 and 3 and 4 and 5
Hello Worl

Statement 7

It's easier to understand and maintain your code if you declare your variables as close as possible to where they are used/needed. One way to do that is to use a nested block to give you a declaration section right at that point.

Just In Time Declarations

DECLARE 
   l_message   VARCHAR2 (100) := 'Hello World'; 
BEGIN 
   FOR indx IN 1 .. 5 
   LOOP 
      l_message := l_message || ' and ' || indx; 
   END LOOP; 
 
   DBMS_OUTPUT.put_line (l_message); 
 
   DECLARE 
      l_message2   VARCHAR2 (100); 
   BEGIN 
      l_message2 := SUBSTR (l_message, 1, 100); 
      DBMS_OUTPUT.put_line (l_message2); 
   END; 
END;

Hello World and 1 and 2 and 3 and 4 and 5
Hello World and 1 and 2 and 3 and 4 and 5

Statement 8

Halfway down in your procedure you might need to do a SELECT-INTO and if no rows are found do one thing, else do another. You can put the SELECT-INTO inside its own nested block, handle the NO_DATA_FOUND exception, then take appropriate action.

Nested Block Constrains Exception Propagation

DECLARE 
   l_message   VARCHAR2 (100) := 'Hello World'; 
BEGIN 
   FOR indx IN 1 .. 5 
   LOOP 
      l_message := l_message || ' and ' || indx; 
   END LOOP; 
 
   DBMS_OUTPUT.put_line (l_message); 
 
   DECLARE 
      l_message2   VARCHAR2 (10); 
   BEGIN 
      l_message2 := SUBSTR (l_message, 1, 100); 
      DBMS_OUTPUT.put_line (l_message2); 
   EXCEPTION 
      WHEN VALUE_ERROR 
      THEN 
         /* OK, that failed, but I can continue if I want */ 
         DBMS_OUTPUT.put_line ('Variable too small!'); 
   END; 
 
   FOR indx IN 1 .. 5 
   LOOP 
      l_message := indx || ' and ' || l_message; 
   END LOOP; 
 
   DBMS_OUTPUT.put_line (l_message); 
END;

Hello World and 1 and 2 and 3 and 4 and 5
Variable too small!
5 and 4 and 3 and 2 and 1 and Hello World and 1 and 2 and 3 and 4 and 5

Statement 9

You can also grab all that code in the nested block, and define it as a local or nested subprogram. Then you replace the block with a call to the subprogram. I like this approach because the result is a smaller, more readable main executable section. But you do need to watch out for referencing "global" variables out of scope - for example, inside with_message2, I directly reference l_message. It could instead be passed in as a parameter.

Nested Subprogram Instead of Block

DECLARE 
   l_message   VARCHAR2 (100) := 'Hello SQL'; 
 
   PROCEDURE with_message2 
   IS 
      l_message2   VARCHAR2 (10); 
   BEGIN 
      l_message2 := SUBSTR (l_message, 1, 100); 
      DBMS_OUTPUT.put_line (l_message2); 
   EXCEPTION 
      WHEN VALUE_ERROR 
      THEN 
         /* OK, that failed, but I can continue if I want */ 
         DBMS_OUTPUT.put_line ('Variable too small!'); 
   END; 
BEGIN 
   FOR indx IN 1 .. 5 
   LOOP 
      l_message := l_message || ' and ' || indx; 
   END LOOP; 
 
   DBMS_OUTPUT.put_line (l_message); 
 
   with_message2; 
 
 
   FOR indx IN 1 .. 5 
   LOOP 
      l_message := indx || ' and ' || l_message; 
   END LOOP; 
 
   DBMS_OUTPUT.put_line (l_message); 
END;

Hello SQL and 1 and 2 and 3 and 4 and 5
Variable too small!
5 and 4 and 3 and 2 and 1 and Hello SQL and 1 and 2 and 3 and 4 and 5

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database