Oracle 11g: PL/SQL Basics

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 As shown in the prototype, PL/SQL requires only the execution section for an anonymous-block program. The execution section starts with a B...

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT Chapter excerpts from Oracle books Writing single-row and multiple-row subqueries List the types of SQL subqueries Using subqueries in SQL Define SQL subqueries Oracle 11g: Backup and recovery concepts Migrating to Oracle: Expert Secrets to Migrate from SQL Server and MySQL Oracle Database 11g SQL Tuning Upgrading to Oracle Database 11g Tuning the Oracle database with initialization parameters Protecting your online Oracle data Oracle development languages How to check SQL query construction with the Mimer Validator Understanding SQL string functions The top advice from Oracle experts in 2008 What's the difference between an SQL inner join and equijoin? Using LEFT OUTER JOIN query to get zero row counts in SQL How to return multiple values for THEN clause in an SQL CASE expression Can I concatenate row values in SQL? Should I try to avoid a LEFT OUTER JOIN in SQL? Tips for derived tables in SQL and using FULL OUTER JOINs How to write an SQL query for two foreign keys to the same table

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

EGIN 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:

You must enable the SQL*Plus SERVEROUTPUT variable to print content to the console. The hello_world.sql print a message to the console:

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:

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: 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:

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:

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.

Rate this Tip To rate tips, you must be a member of SearchOracle.com. Register now to start rating these tips. Log in if you are already a member. DISCLAIMER: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.