Main features of PL/SQL

PL/SQL combines the data-manipulating power of SQL with the processing power of procedural languages. When you can solve a problem with SQL, you can issue SQL statements from your PL/SQL program, without learning new APIs. Like other procedural programming languages, PL/SQL lets you declare constants and variables, control program flow, define subprograms, and trap run-time errors. You can break complex problems into easily understandable subprograms, which you can reuse in multiple applications.

Topics: 

• Error Handling 
• Blocks
• Variables and Constants
• Subprograms
• Packages 
• Triggers
• Input and Output
• Data Abstraction
• Control Statements 
• Conditional Compilation
• Processing a Query Result Set One Row at a Time

  

Error Handling

PL/SQL makes it easy to detect and handle errors. When an error occurs, PL/SQL raises an exception. Normal execution stops and control transfers to the exception-handling part of the PL/SQL block. You do not have to check every operation to ensure that it succeeded, as in a C program. For more information, see PL/SQL Error Handling

See Also: Oracle Database Advanced Application Developer's Guide for information about developing PSPs

 
Blocks

The basic unit of a PL/SQL source program is the block, which groups related declarations and statements.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 have a label.

 For syntax details, see Block

• Declarations are local to the block and cease to exist when the block completes execution, helping to avoid cluttered name spaces for variables and subprograms.

• Blocks can be nested: Because a block is an executable statement, it can appear in another block wherever an executable statement is allowed.

You can submit a block to an interactive tool (such as SQL*Plus or Enterprise Manager) or embed it in an Oracle Precompiler or OCI program. The interactive tool or program runs the block only once. The block is not stored in the database, and for that reason, it is called an anonymous block (even if it has a label).

An anonymous block is compiled each time it is loaded into memory, and its compilation has three stages:

1. Syntax checking: PL/SQL syntax is checked, and a parse tree is generated.

2. Semantic checking: Type checking and further processing on the parse tree.

3. Code generation

Variables and Constants

 PL/SQL lets you declare variables and constants, and then use them wherever you can use an expression. As the program runs, the values of variables can change, but the values of constants cannot. For more information, see Declarations and "Assigning Values to Variables

Note: An anonymous block is a SQL statement.