PL/SQL User's Guide and Reference Release 2 (9.2) Part Number A96624-01 |
|
PL/SQL, Oracle's procedural extension of SQL, is an advanced fourth-generation programming language (4GL). It offers modern features such as data encapsulation, overloading, collection types, exception handling, and information hiding. PL/SQL also offers seamless SQL access, tight integration with the Oracle server and tools, portability, and security.
This guide explains all the concepts behind PL/SQL and illustrates every facet of the language. Good programming style is stressed throughout and supported by numerous examples. Using this guide, you learn PL/SQL quickly and efficiently.
This preface contains these topics:
Anyone developing PL/SQL-based applications for Oracle will benefit from reading this guide. Written especially for programmers, this comprehensive treatment of PL/SQL will also be of value to systems analysts, project managers, and others interested in database applications. To use this guide effectively, you need a working knowledge of Oracle, SQL, and a 3GL such as Ada, C, or COBOL.
You will not find installation instructions or system-specific information in this guide. For that kind of information, see the Oracle installation or user's guide for your system.
The PL/SQL User's Guide and Reference contains:
Chapter 1, "Overview of PL/SQL"
This chapter surveys the main features of PL/SQL and points out the advantages they offer. It also acquaints you with the basic concepts behind PL/SQL and the general appearance of PL/SQL programs.
Chapter 2, "Fundamentals of PL/SQL"
This chapter focuses on the small-scale aspects of PL/SQL. It discusses lexical units, scalar datatypes, user-defined subtypes, data conversion, expressions, assignments, block structure, declarations, and scope.
This chapter discusses PL/SQL's predefined datatypes, which include integer, floating point, character, Boolean, date, collection, reference, and LOB types. It also discusses user-defined subtypes and data conversion.
Chapter 4, "PL/SQL Control Structures"
This chapter shows you how to structure the flow of control through a PL/SQL program. It describes conditional, iterative, and sequential control. You learn how to apply simple but powerful control structures such as IF
-THEN
-ELSE
, CASE
, and WHILE
-LOOP
.
Chapter 5, "PL/SQL Collections and Records"
This chapter focuses on the composite datatypes TABLE
, VARRAY
, and RECORD
. You learn how to reference and manipulate whole collections of data, and how to treat related but dissimilar data as a logical unit. You also learn how to improve performance by bulk-binding collections.
Chapter 6, "Interaction Between PL/SQL and Oracle"
This chapter shows you how PL/SQL supports the SQL commands, functions, and operators that let you manipulate Oracle data. You also learn how to manage cursors, process transactions, and safeguard your database.
Chapter 7, "Handling PL/SQL Errors"
This chapter provides an in-depth discussion of error reporting and recovery. You learn how to detect and handle errors using PL/SQL exceptions.
Chapter 8, "PL/SQL Subprograms"
This chapter shows you how to write and use subprograms. It discusses procedures, functions, forward declarations, actual and formal parameters, positional and named notation, parameter modes, the NOCOPY
compiler hint, parameter default values, aliasing, overloading, invoker rights, and recursion.
This chapter shows you how to bundle related PL/SQL types, items, and subprograms into a package. Once written, your general-purpose package is compiled, then stored in an Oracle database, where its contents can be shared by many applications.
Chapter 10, "PL/SQL Object Types"
This chapter introduces you to object-oriented programming based on object types, which provide abstract templates for real-world objects. You learn how to define object types and manipulate objects.
Chapter 11, "Native Dynamic SQL"
This chapter shows you how to use dynamic SQL, an advanced programming technique that makes your applications more flexible and versatile. You learn two simple ways to write programs that can build and process SQL statements "on the fly" at run time.
Chapter 12, "Tuning PL/SQL Applications"
This chapter shows you how to tune PL/SQL-based applications. You learn how to make small adjustments that improve performance.
Chapter 13, "PL/SQL Language Elements"
This chapter uses syntax diagrams to show how commands, parameters, and other language elements are sequenced to form PL/SQL statements. Also, it provides usage notes and short examples to help you become fluent in PL/SQL quickly.
Appendix A, "Sample PL/SQL Programs"
This appendix provides several PL/SQL programs to guide you in writing your own. The sample programs illustrate important concepts and features.
Appendix B, "CHAR versus VARCHAR2 Semantics"
This appendix explains the subtle but important semantic differences between the CHAR
and VARCHAR2
base types.
Appendix C, "PL/SQL Wrap Utility"
This appendix shows you how to run the Wrap Utility, a standalone programming utility that enables you to deliver PL/SQL applications without exposing your source code.
Appendix D, "PL/SQL Name Resolution"
Thus appendix explains how PL/SQL resolves references to names in potentially ambiguous SQL and procedural statements.
Appendix E, "PL/SQL Program Limits"
This appendix helps you deal with the program limits imposed by the PL/SQL compilation and run-time system.
Appendix F, "List of PL/SQL Reserved Words"
This appendix lists those words reserved for use by PL/SQL.
For more information, see these Oracle resources:
Various aspects of PL/SQL programming, in particular details for triggers and stored procedures, are covered in Oracle9i Application Developer's Guide - Fundamentals
For extensive information on object-oriented programming using both PL/SQL and SQL features, see Oracle9i Application Developer's Guide - Object-Relational Features
For information about programming with large objects (LOBs), see Oracle9i Application Developer's Guide - Large Objects (LOBs)
For SQL information, see the Oracle9i SQL Reference and Oracle9i Database Administrator's Guide. For basic Oracle concepts, see Oracle9i Database Concepts.
In North America, printed documentation is available for sale in the Oracle Store at
http://oraclestore.oracle.com/
Customers in Europe, the Middle East, and Africa (EMEA) can purchase documentation from
http://www.oraclebookshop.com/
Other customers can contact their Oracle representative to purchase printed documentation.
To download free release notes, installation documentation, white papers, or other collateral, please visit the Oracle Technology Network (OTN). You must register online before using OTN; registration is free and can be done at
http://otn.oracle.com/admin/account/membership.html
If you already have a username and password for OTN, then you can go directly to the documentation section of the OTN Web site at
http://otn.oracle.com/docs/index.htm
To access the database documentation search engine directly, please visit
http://tahiti.oracle.com/
This search engine has a number of features that you might find useful, such as searching for examples, looking up SQL and PL/SQL syntax, and formatting large numbers of search results into a "virtual book".
This section describes the conventions used in the text and code examples of this documentation set. It describes:
We use various conventions in text to help you more quickly identify special terms. The following table describes those conventions and provides examples of their use.
Code examples illustrate SQL, PL/SQL, SQL*Plus, or other command-line statements. They are displayed in a monospace (fixed-width) font and separated from normal text as shown in this example:
SELECT username FROM dba_users WHERE username = 'MIGRATE';
The following table describes typographic conventions used in code examples and provides examples of their use.
Most programming examples in this guide use two sample database tables named dept
and emp
. Their definitions follow:
CREATE TABLE dept ( deptno NUMBER(2) NOT NULL, dname VARCHAR2(14), loc VARCHAR2(13)); CREATE TABLE emp ( empno NUMBER(4) NOT NULL, ename VARCHAR2(10), job VARCHAR2(9), mgr NUMBER(4), hiredate DATE, sal NUMBER(7,2), comm NUMBER(7,2), deptno NUMBER(2));
Respectively, the dept
and emp
tables contain the following rows of data:
DEPTNO DNAME LOC ------- ---------- --------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ------- --------- ------ --------- ------ ------ ------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 30 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10
Our goal is to make Oracle products, services, and supporting documentation accessible, with good usability, to the disabled community. To that end, our documentation includes features that make information available to users of assistive technology. This documentation is available in HTML format, and contains markup to facilitate access by the disabled community. Standards will continue to evolve over time, and Oracle Corporation is actively engaged with other market-leading technology vendors to address technical obstacles so that our documentation can be accessible to all of our customers. For additional information, visit the Oracle Accessibility Program Web site at
http://www.oracle.com/accessibility/
JAWS, a Windows screen reader, may not always correctly read the code examples in this document. The conventions for writing code require that closing braces should appear on an otherwise empty line; however, JAWS may not always read a line of text that consists solely of a bracket or brace.
This documentation may contain links to Web sites of other companies or organizations that Oracle Corporation does not own or control. Oracle Corporation neither evaluates nor makes any representations regarding the accessibility of these Web sites.
|
Copyright © 1996, 2002 Oracle Corporation. All Rights Reserved. |
|