As an introduction to PL/SQL basics, this chapter introduces and briefly discusses
- Oracle PL/SQL block structure
- Variables, assignments, and operators
- Control structures
- Conditional structures
- Iterative structures
- Stored functions, procedures, and packages
- Transaction scope
- Database triggers
PL/SQL is a case-insensitive programming language, like SQL. While the language is case insensitive, there are many conventions applied to how people write their code. Most choose combinations of uppercase, lowercase, title case, or mixed case. Among these opinions there is no standard approach to follow.
Oracle PL/SQL Block Structure
PL/SQL was developed by modeling concepts of structured programming, static data typing, modularity, and exception management. It extends the ADA programming language. ADA extended the Pascal programming language, including the assignment and comparison operators and single-quote string delimiters.
PL/SQL supports two types of programs: one is an anonymous-block program, and the other is a named-block program. Both types of programs have declaration, execution, and exception handling sections or blocks. Anonymous blocks support batch scripting, and named blocks deliver stored programming units.
The basic prototype for an anonymous-block PL/SQL programs is
[DECLARE] declaration_statements BEGIN execution_statements [EXCEPTION] exception_handling_statements END; /As shown in the prototype, PL/SQL requires only the execution section for an anonymous-block program. The execution section starts with a BEGIN statement and stops at the beginning of the optional EXCEPTION block or the END statement of the program. A semicolon ends the anonymous PL/SQL block, and the forward slash executes the block.
Declaration sections can contain variable definitions and declarations, user-defined PL/SQL type definitions, cursor definitions, reference cursor definitions, and local function or procedure definitions. Execution sections can contain variable assignments, object initializations, conditional structures, iterative structures, nested anonymous PL/SQL blocks, or calls to local or stored named PL/SQL blocks. Exception sections can contain error handling phrases that can use all of the same items as the execution section. The simplest PL/SQL block does nothing. You must have a minimum of one statement inside any execution block, even if it's a NULL statement. The forward slash executes an anonymous PL/SQL block. The following illustrates the most basic anonymous-block program:
BEGIN NULL; END; /
You must enable the SQL*Plus SERVEROUTPUT variable to print content to the console. The hello_world.sql print a message to the console:
-- This is found in hello_world.sql on the publisher's web site. SET SERVEROUTPUT ON SIZE 1000000 BEGIN dbms_output.put_line('Hello World.'); END; /
The SQL*Plus SERVEROUTPUT environment variable opens an output buffer, and the DBMS_ OUTPUT.PUT_LINE() function prints a line of output. All declarations, statements, and blocks are terminated by a semicolon.
SQL*Plus supports the use of substitution variables in the interactive console, which are prefaced by an ampersand, &. Substitution variables are variable-length strings or numbers. You should never assign dynamic values in the declaration block, like substitution variables. The following program defines a variable and assigns it a value:
-- This is found in substitution.sql on the publisher's web site. DECLARE my_var VARCHAR2(30); BEGIN my_var := '&input'; dbms_output.put_line('Hello '|| my_var ); END; /The assignment operator in PL/SQL is a colon plus an equal sign (:=). PL/SQL string literals are delimited by single quotes. Date, numeric, and string literals are covered in Chapter 3. You run anonymous blocks by calling them from Oracle SQL*Plus. The @ symbol in Oracle SQL*Plus loads and executes a script file. The default file extension is .sql, but you can override it with another extension. This means you can call a filename without its .sql extension. The following demonstrates calling the substitution.sql file:
SQL> @substitution.sql Enter value for input: Henry Wadsworth Longfellow old 3: my_var VARCHAR2(30) := '&input'; new 3: my_var VARCHAR2(30) := 'Henry Wadsworth Longfellow'; Hello Henry Wadsworth Longfellow PL/SQL procedure successfully completed.The line starting with old designates where your program accepts a substitution, and new designates the run-time substitution. Assigning a string literal that is too large for the variable fires an exception. Exception blocks manage raised errors. A generic exception handler manages any raised error. You use a WHEN block to catch every raised exception with the generic error handler—OTHERS.
The following exception.sql program demonstrates how an exception block manages an error when the string is too long for the variable:
-- This is found in exception.sql on the publisher's web site. DECLARE my_var VARCHAR2(10); BEGIN my_var := '&input'; dbms_output.put_line('Hello '|| my_var ); EXCEPTION WHEN others THEN dbms_output.put_line(SQLERRM); END; /
The anonymous block changed the definition of the string from 30 characters to 10 characters. The poet's name is now too long to fit in the target variable. Assigning the variable raises an exception. The console output shows the handled and raised exception:
SQL> @exception.sql Enter value for input: Henry Wadsworth Longfellow old 4: my_var := '&input'; new 4: my_var := 'Henry Wadsworth Longfellow'; ORA-06502: PL/SQL: numeric or value error: character string buffer too small PL/SQL procedure successfully completed.You can also have: (a) nested anonymous-block programs in the execution section of an anonymous block; (b) named-block programs in the declaration section that can in turn contain the same type of nested programs; and (c) calls to stored named-block programs.
The outermost programming block controls the total program flow, while nested programming blocks control their subordinate programming flow. Each anonymous- or named-block programming unit can contain an exception section. When a local exception handler fails to manage an exception, it throws the exception to a containing block until it reaches the SQL*Plus environment.
Error stack management is the same whether errors are thrown from called local or named PL/SQL blocks. Error are raised and put in a first-in, last-out queue, which is also known as a stack. You have explored the basic structure of PL/SQL block programs and error stack management. The block structure is foundational knowledge to work in PL/SQL.
Continue reading this chapter by downloading a free .pdf of PL./SQL Basics from Oracle Database 11g PL/SQL Programming .
More on PL/SQL in Oracle 11g:
Get expert answers to your Oracle 11g PL/SQL questions in our Ask the Expert section.
Before the debut of Oracle 11g, PL/SQL expert Steve Feurstein talked to us in this podcast about the PL/SQL changes he'd like to see in the latest database version. Did Oracle make these changes?
Find all the 11g PL/SQL info you need in our Oracle 11g Special report.
What other Oracle 11g PL/SQL resources or articles would you like to see on SearchOracle.com? Email us and let us know.
This was first published in June 2008