Delicious One

Tuesday, June 10, 2008

Oracle PL/SQL Programming

Search my Sites

Part I: Programming in PL/SQL

Chapter 1. Introduction to PL/SQL
Chapter 2. Creating and Running PL/SQL Code
Chapter 3. Language Fundamentals

Part II: PL/SQL Program Structure

Chapter 4. Conditional and Sequential Control
Chapter 5. Iterative Processing with Loops
Chapter 6. Exception Handlers

Part III: PL/SQL Program Data

Chapter 7. Working with Program Data
Chapter 8. Strings
Chapter 9. Numbers
Chapter 10. Dates and Timestamps
Chapter 11. Records
Chapter 12. Collections
Chapter 13. Miscellaneous Datatypes

Part IV: SQL in PL/SQL

Chapter 14. DML and Transaction Management
Chapter 15. Data Retrieval
Chapter 16. Dynamic SQL and Dynamic PL/SQL

Part V: PL/SQL Application Construction

Chapter 17. Procedures, Functions, and Parameters
Chapter 18. Packages
Chapter 19. Triggers
Chapter 20. Managing PL/SQL Code
Chapter 21. I/O and PL/SQL

Part VI: Advanced PL/SQL Topics

Chapter 22. Application Security and PL/SQL
Chapter 23. Inside PL/SQL
Chapter 24. Globalization and Localization in PL/SQL
Chapter 25. Object-Oriented Aspects of PL/SQL
Chapter 26. Calling Java from PL/SQL
Chapter 27. External Procedures

Programming in PL/SQL

This first part of the book introduces PL/SQL, explains how to create and run code, and presents language fundamentals।Chapter 1 asks the fundamental questions: Where did PL/SQL come from? What is it good for?What are the main features of the language?Chapter 2 is designed to get you up and running PL/SQL programs as quickly as possible; it contains clear, straightforward instructions for executing PL/SQL code in SQL*Plus and a few other common environments।Chapter 3 answers basic questions about the language structure and keywords:What makes up a PL/SQL statement? What is the PL/SQL block structure all about? How do I write comments in PL/SQL?

Introduction to PL/SQL

PL/SQL stands for "Procedural Language extensions to the Structured Query Language." SQL is the now-ubiquitous language for both querying and updatingnever mind the nameof relational databases. Oracle Corporation introduced PL/SQL to overcome some limitations in SQL and to provide a more complete programming solution for those who sought to build mission-critical applications to run against the Oracle database. This chapter introduces PL/SQL, its origins, and its various versions. It also offers a guide to additional resources
for PL/SQL developers and some words of advice.


Millions of application developers and database administrators around the world use software provided by Oracle Corporation to build complex systems that manage vast quantities of data. At the heart of much of Oracle's software is PL/SQLa programming language that provides procedural extensions to Oracle's version of SQL (Structured Query Language) and serves as the programming language within the Oracle Developer toolset (most notably Forms Developer and Reports Developer).
PL/SQL figures prominently as an enabling technology in almost every new product released by Oracle Corporation. Software professionals use PL/SQL to perform many kinds of programming functions , including:
  • Implementing crucial business rules in the Oracle Server with PL/SQL-based stored procedures and database triggers
  • Generating and managing XML documents entirely within the database
  • Linking web pages to an Oracle database
  • Implementing and automating database administration tasksfrom establishing row-level security to managing rollback segments within PL/SQL programs
PL/SQL was modeled after Ada,[*]a programming language designed for the U.S. Department of Defense. Ada is a high-level language that emphasizes data abstraction, information hiding, and other key elements of modern design strategies. As a result of this very smart
design decision by Oracle, PL/SQL is a powerful language that incorporates many of the most advanced elements of procedural languages, including:
[*] The language was named "Ada" in honor of Ada Lovelace, a mathematician who is regarded by many to have
been the world's first computer programmer. For more information about Ada, visit
  • A full range of datatypes from number to string, and including complex data structures such as records (which are similar to rows in a relational table), collections (which are Oracle's version of arrays), and XMLType (for managing XML documents in
  • Oracle and through PL/SQL)
  • An explicit and highly readable block structure that makes it easy to enhance and maintain PL/SQL applications
  • Conditional, iterative, and sequential control statements , including a CASE statement and three different kinds of loops
  • Exception handlers for use in event-based error handling
  • Named, reusable code elements such as functions, procedures, triggers, object types (akin to object-oriented classes), and packages (collections of related programs and variables)
PL/SQL is integrated tightly into Oracle's SQL language: you can execute SQL statements directly from your procedural program without having to rely on any kind of intermediate API (Application Programming Interface) such as JDBC (Java DataBase Connectivity) or ODBC (Open DataBase Connectivity)। Conversely, you can also call your own PL/SQL functions from within a SQL statement.

Oracle developers who want to be successful in the 21st century must learn to use PL/SQL to full advantage। This is a two-step process।First, you must become familiar with and learn how to use the language's ever-expanding set of features; and second, after gaining competence in the individual features, you must learn how to put these constructs together to build complex applications।

For these reasons and more, Oracle developers need a solid, comprehensive resource for the base PL/SQL language. You need to know the basic building blocks of PL/SQL, but you also need to learn by example so that you can avoid some of the trial and error. As with any programming language, PL/SQL has a right way and many wrong ways (or at least "not as right" ways) to handle just about any task. It is our hope that this book will help you learn how to use the PL/SQL language in the most effective and efficient way possible.

What Is PL/SQL?

Oracle's PL/SQL language has several defining characteristics:

It is a highly structured, readable, and accessible language

If you are new to programming, PL/SQL is a great place to start. You will find that it is an easy language to learn and is rich with keywords and structure that clearly express the intent of your code. If you are experienced in other programming languages, you will very easily adapt to the new syntax।

It is a standard and portable language for Oracle development

If you write a PL/SQL procedure or function to execute from within the Oracle database sitting on your laptop, you can move that same procedure to a database on your corporate network and execute it there without any changes (assuming compatibility of Oracle versions, of course!). "Write once, run everywhere" was the mantra of PL/SQL long before Java appeared। For PL/SQL, though, "everywhere" means "everywhere there is an Oracle database."

It is an embedded language

PL/SQL was not designed to be used as a standalone language, but instead to be invoked from within a host environment।So, for example, you can run PL/SQL programs from within the database (through, say, the SQL*Plus interface). Alternatively, you can define and execute PL/SQL programs from within an Oracle Developer form or report (this approach is called client-side PL/SQL)। You cannot, however, create a PL/SQL executable that runs all by itself.

It is a high-performance, highly integrated database language

These days, you have a number of choices when it comes to writing software to run against the Oracle database. You can use Java and JDBC; you can use Visual Basic and ODBC; you can go with Delphi, C++, and so on। You will find, however,that it is easier to write highly efficient code to access the Oracle database in PL/SQL than it is in any other language. In particular, Oracle offers certain PL/SQL-specific enhancements such as the FORALL statement that can improve database performance by an order of magnitude or more।

Integration with SQL

One of the most important aspects of PL/SQL is its tight integration with SQL. You don't need to rely on any intermediate software "glue"
such as ODBC (Open DataBase Connectivity) or JDBC (Java DataBase Connectivity) to run SQL statements in your PL/SQL programs.
Instead, you just insert the UPDATE or SELECT into your code, as shown here:
2 l_book_count INTEGER;
6 INTO l_book_count
7 FROM books
9 9
11 'Steven has written (or co-written) ' ||
12 l_book_count ||
13 ' books.');
15 -- Oh, and I changed my name, so...
16 UPDATE books
17 SET author = REPLACE (author, 'STEVEN', 'STEPHEN')
19 END;

The Origins of PL/SQL

Oracle has a history of leading the software industry in providing declarative, non-procedural approaches to designing both databases and applications. The Oracle Server technology is among the most advanced, powerful, and stable relational databases in the world. Its application development tools, such as Oracle Forms, offer high levels of productivity by relying heavily on a "paint-your-screen"approach in which extensive default capabilities allow developers to avoid heavy customized programming efforts।

1.2.1. The Early Years of PL/SQL

In Oracle's early years, the declarative approach of SQL, combined with its groundbreaking relational technology, was enough to satisfy developers. But as the industry matured, expectations rose, and requirements became more stringent. Developers needed to get "under the skin" of the products. They needed to build complicated formulas, exceptions, and rules into their forms and database scripts.
In 1988, Oracle Corporation released Oracle Version 6, a major advance in its relational database technology. A key component of that version was the so-called "procedural option" or PL/SQL. At roughly the same time, Oracle released its long-awaited upgrade to SQL*Forms Version 2.3 (the original name for the product now known as Oracle Forms or Forms Developer). SQL*Forms V3.реж incorporated the PL/SQL engine for the first time on the tools side, allowing developers to code their procedural logic in a natural,straightforward manner.
This first release of PL/SQL was very limited in its capabilities। On the server side, you could use PL/SQL only to build"batch-processing" scripts of procedural and SQL statements. You could not construct a modular application or store business rules in the server. On the client side, SQL*Forms V3.0 did allow you to create procedures and functions, although support for functions was not documented, and was therefore not used by many developers for years. In addition, this release of PL/SQL did not implement array support and could not interact with the operating system (for input or output).

1.2.2. Improved Application Portability

The concern about portability might seem odd to those of us familiar with Oracle Corporation's marketing and technical strategies। One of the hallmarks of the Oracle solution from the early 1980s was its portability. At the time that PL/SQL came along, the C-based RDBMS ran on many different operating systems and hardware platforms. SQL*Plus and SQL*Forms adapted easily to a variety of terminal configurations. Yet for all that coverage, there were still many applications that needed the more sophisticated and granular control offered by such host languages as COBOL, C, and FORTRAN,As soon as a developer stepped outside the port-neutral Oracle tools,the resulting application would no longer be portable.The PL/SQL language was (and is) intended to widen the range of application requirements that can be handled entirely in oepareting system-independent programming tools. Today, Java and other programming languages offer similar portability. Yet PL/SQL stands out as an early pioneer in this field and, of course, it continues to allow developers to write highly portable application code।

1.2.3. Improved Execution Authority and Transaction Integrity

An even more fundamental issue than portability was execution authority. The RDBMS and the SQL language let you tightly control access to, and changes in, any particular database table. For example, with the GRANT command, you can make sure that only certain roles and users can perform an UPDATE on a given table. On the other hand, this GRANT statement can't ensure that the full set of UPDATEs performed by a user or application is done correctly. In other words, the database can't guarantee the integrity of a transaction that spans more than one table, as is common with most business transactions.The PL/SQL language provided tight control and management over logical transactions. One way PL/SQL does this is with The implementation of execution authority. Instead of granting to a role or user the authority to update a table, you grant privleges only to execute a procedure, which controls and provides access to the underlying data structures. The procedure is owned by a separate Oracle RDBMS account (the "definer" of the program), which, in turn, is granted the actual update privileges on those tables needed to perform the transaction. The procedure therefore becomes the "gatekeeper" for the transaction. The only way that a program (whether it's an Oracle Forms application or a Pro*C executable) can execute the transfer is through the procedure. In this way, the overall application transaction integrity is guaranteed.

1.2.4. Humble Beginnings, Steady Improvement

As powerful as SQL is, it simply does not offer the flexibility and power developers need to create full-blown applications. Oracle's PL/SQL language ensures that we can stay entirely within the operating system-independent Oracle environment and still write highly efficient applications that meet our users' requirements.
PL/SQL has come a long way from its humble beginnings. With PL/SQL 1.0, it was not uncommon for a developer to have to tell his or her manager, "You can't do that with PL/SQL." Today, that statement has moved from fact to excuse. If you are ever confronted with arequirement and find yourself saying, "There's no way to do that," please don't repeat it to your manager. Instead, dig deeper into The language, or explore the range of PL/SQL packages offered by Oracle. It is extremely likely that PL/SQL today will, in fact, allow you to do pretty much whatever you need to do.

1.2.5. The Significance of Oracle Database 10g PL/SQL

Oracle Database 10g PL/SQL is impressive, but not particularly because it offers many new language features and extended capabilities. Instead, it demonstrates Oracle's continuing strong commitment to supporting this language and the millions of PL/SQL।The PL/SQL development team spent over five years redesigning the compiler to support three critical capabilities:
Automatic, transparent optimization of code
Starting with Oracle Database 10g Release 1, Oracle automatically restructures our code to improve its performance. After extensive testing and benchmarking, Oracle estimates that the non-SQL portions of your PL/SQL program can run twice As fast as they did in Oracle9i Database.
Compile-time warnings
Starting with Oracle Database 10g Release 1, the compiler can detect potential runtime problems with your code, such As identifying lines of code that will never be run. This process, also known as lint checking, has long been desired by PL/SQL developers.
Preprocessor support
Starting with Oracle Database 10g Release 2, within your PL/SQL programs you can now specify conditional logic that is processed as your code is compiled. This "ifdef" feature allows you to, among other things, specify different code to be compiled for different versions of Oracle (which will come in handy primarily in future releases of Oracle).


My Album

My Album
View IT

Listen to This song