[ORACLE][Database Application Developers-Fundamentals 10g].pdf

Oracle® Database

Application Developer's Guide - Fundamentals

10g Release 2 (10.2)

B14251-01

November 2005

Oracle Database Application Developer’s Guide - Fundamentals, 10g Release 2 (10.2)

B14251-01

Primary Author: Lance Ashdown

Contributing Authors: D. Adams, M. Cowan, R. Moran, J. Melnick, E. Paapanen, J. Russell, R. Strohm

Contributors: D. Alpern, G. Arora, C. Barclay, D. Bronnikov, T. Chang, M. Davidson, G. Doherty, D. Elson,

A. Ganesh, M. Hartstein, J. Huang, N. Jain, R. Jenkins Jr., S. Kotsovolos, S. Kumar, C. Lei, D. Lorentz, V.

Moore, J. Muller, R. Murthy, R. Pang, B. Sinha, S. Vemuri, W. Wang, D. Wong, A. Yalamanchi, Q. Yu

The Programs (which include both the software and documentation) contain proprietary information; they

are provided under a license agreement containing restrictions on use and disclosure and are also protected

by copyright, patent, and other intellectual and industrial property laws. Reverse engineering, disassembly,

or decompilation of the Programs, except to the extent required to obtain interoperability with other

independently created software or as specified by law, is prohibited.

The information contained in this document is subject to change without notice. If you find any problems in

the documentation, please report them to us in writing. This document is not warranted to be error-free.

Except as may be expressly permitted in your license agreement for these Programs, no part of these

Programs may be reproduced or transmitted in any form or by any means, electronic or mechanical, for any

purpose.

If the Programs are delivered to the United States Government or anyone licensing or using the Programs on

behalf of the United States Government, the following notice is applicable:

U.S. GOVERNMENT RIGHTS Programs, software, databases, and related documentation and technical data

delivered to U.S. Government customers are "commercial computer software" or "commercial technical data"

pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As

such, use, duplication, disclosure, modification, and adaptation of the Programs, including documentation

and technical data, shall be subject to the licensing restrictions set forth in the applicable Oracle license

agreement, and, to the extent applicable, the additional rights set forth in FAR 52.227-19, Commercial

Computer Software—Restricted Rights (June 1987). Oracle Corporation, 500 Oracle Parkway, Redwood City,

CA 94065

The Programs are not intended for use in any nuclear, aviation, mass transit, medical, or other inherently

dangerous applications. It shall be the licensee's responsibility to take all appropriate fail-safe, backup,

redundancy and other measures to ensure the safe use of such applications if the Programs are used for such

purposes, and we disclaim liability for any damages caused by such use of the Programs.

Oracle, JD Edwards, PeopleSoft, and Retek are registered trademarks of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective owners.

The Programs may provide links to Web sites and access to content, products, and services from third

parties. Oracle is not responsible for the availability of, or any content provided on, third-party Web sites.

You bear all risks associated with the use of such content. If you choose to purchase any products or services

from a third party, the relationship is directly between you and the third party. Oracle is not responsible for:

(a) the quality of third-party products or services; or (b) fulfilling any of the terms of the agreement with the

third party, including delivery of products or services and warranty obligations related to purchased

products or services. Oracle is not responsible for any loss or damage of any sort that you may incur from

dealing with any third party.

Contents

Preface ............................................................................................................................................................... xix

Audience..................................................................................................................................................... xix

Documentation Accessibility ................................................................................................................... xix

Related Documents ................................................................................................................................... xx

Conventions ............................................................................................................................................... xxi

What's New in Application Development? ................................................................................ xxiii

Oracle Database 10 g Release 2 (10.2) New Features........................................................................... xxiii

Oracle Database 10 g Release 1 (10.1) New Features........................................................................... xxiv

1 Orientation to Oracle Programmatic Environments

Overview of Oracle Application Development ................................................................................. 1-1

Overview of PL/SQL ................................................................................................................................ 1-2

What Is PL/SQL? ............................................................................................................................... 1-2

Advantages of PL/SQL ..................................................................................................................... 1-3

Integration with Oracle Database ............................................................................................. 1-4

High Performance ....................................................................................................................... 1-4

High Productivity ....................................................................................................................... 1-4

Scalability ..................................................................................................................................... 1-4

Manageability .............................................................................................................................. 1-5

Object-Oriented Programming Support .................................................................................. 1-5

Portability..................................................................................................................................... 1-5

Security ......................................................................................................................................... 1-5

Built-In Packages......................................................................................................................... 1-6

PL/SQL Web Development Tools ................................................................................................... 1-6

Overview of Java Support Built Into the Database ........................................................................... 1-6

Overview of Oracle JVM ................................................................................................................... 1-7

Overview of Oracle Extensions to JDBC......................................................................................... 1-8

JDBC Thin Driver ........................................................................................................................ 1-8

JDBC OCI Driver ......................................................................................................................... 1-8

JDBC Server-Side Internal Driver ............................................................................................. 1-9

Oracle Database Extensions to JDBC Standards .................................................................... 1-9

Sample JDBC 2.0 Program ......................................................................................................... 1-9

Sample Pre-2.0 JDBC Program ............................................................................................... 1-10

JDBC in SQLJ Applications..................................................................................................... 1-10

iii

Overview of Oracle SQLJ ............................................................................................................... 1-11

Benefits of SQLJ........................................................................................................................ 1-12

Comparing SQLJ with JDBC .................................................................................................. 1-12

SQLJ Stored Procedures in the Server................................................................................... 1-13

Overview of Oracle JPublisher...................................................................................................... 1-13

Overview of Java Stored Procedures............................................................................................ 1-13

Overview of Oracle Database Web Services ............................................................................... 1-14

Oracle Database as a Web Service Provider ........................................................................ 1-14

Overview of Writing Procedures and Functions in Java........................................................... 1-15

Overview of Writing Database Triggers in Java ................................................................. 1-15

Why Use Java for Stored Procedures and Triggers?........................................................... 1-15

Overview of Pro*C/C++ ....................................................................................................................... 1-15

Implementing a Pro*C/C++ Application .................................................................................... 1-16

Highlights of Pro*C/C++ Features............................................................................................... 1-17

Overview of Pro*COBOL .................................................................................................................... 1-18

How You Implement a Pro*COBOL Application ...................................................................... 1-18

Highlights of Pro*COBOL Features ............................................................................................. 1-19

Overview of OCI and OCCI ................................................................................................................ 1-19

Advantages of OCI.......................................................................................................................... 1-20

Parts of the OCI ............................................................................................................................... 1-20

Procedural and Non-Procedural Elements ................................................................................. 1-21

Building an OCI Application......................................................................................................... 1-21

Overview of Oracle Data Provider for .NET (ODP.NET) .............................................................. 1-22

Using ODP.NET in a Simple Application ................................................................................... 1-22

Overview of Oracle Objects for OLE (OO4O) ................................................................................. 1-23

OO4O Automation Server ............................................................................................................. 1-24

OO4O Object Model ....................................................................................................................... 1-25

OraSession................................................................................................................................. 1-25

OraServer .................................................................................................................................. 1-25

OraDatabase.............................................................................................................................. 1-26

OraDynaset ............................................................................................................................... 1-26

OraField ..................................................................................................................................... 1-26

OraMetaData and OraMDAttribute...................................................................................... 1-27

OraParameters and OraParameter ........................................................................................ 1-27

OraParamArray........................................................................................................................ 1-27

OraSQLStmt.............................................................................................................................. 1-27

OraAQ ....................................................................................................................................... 1-27

OraAQMsg................................................................................................................................ 1-28

OraAQAgent............................................................................................................................. 1-28

Support for Oracle LOB and Object Datatypes........................................................................... 1-28

OraBLOB and OraCLOB ......................................................................................................... 1-29

OraBFILE ................................................................................................................................... 1-29

Oracle Data Control ........................................................................................................................ 1-29

Oracle Objects for OLE C++ Class Library.................................................................................. 1-29

Additional Sources of Information ............................................................................................... 1-29

Choosing a Programming Environment ........................................................................................... 1-30

Choosing Whether to Use OCI or a Precompiler ....................................................................... 1-30

Using Built-In Packages and Libraries ......................................................................................... 1-30

Java Compared to PL/SQL............................................................................................................ 1-31

PL/SQL Is Optimized for Database Access ......................................................................... 1-31

PL/SQL Is Integrated with the Database ............................................................................. 1-31

Both Java and PL/SQL Have Object-Oriented Features .................................................... 1-31

Java Is Used for Open Distributed Applications................................................................. 1-32

Part I SQL for Application Developers

2 SQL Processing for Application Developers

Grouping Operations into Transactions .............................................................................................. 2-1

Deciding How to Group Operations in Transactions ................................................................... 2-1

Improving Transaction Performance ............................................................................................. 2-2

Committing Transactions.................................................................................................................. 2-2

Managing Commit Redo Behavior........................................................................................... 2-3

Rolling Back Transactions ................................................................................................................ 2-4

Defining Transaction Savepoints .................................................................................................... 2-5

An Example of COMMIT, SAVEPOINT, and ROLLBACK ................................................. 2-5

Ensuring Repeatable Reads with Read-Only Transactions ............................................................ 2-5

Using Cursors within Applications ...................................................................................................... 2-6

Declaring and Opening Cursors ..................................................................................................... 2-6

Using a Cursor to Execute Statements Again ................................................................................ 2-7

Closing Cursors ................................................................................................................................. 2-7

Cancelling Cursors ............................................................................................................................ 2-8

Locking Data Explicitly .......................................................................................................................... 2-8

Choosing a Locking Strategy ........................................................................................................... 2-9

When to Lock with ROW SHARE and ROW EXCLUSIVE Mode ....................................... 2-9

When to Lock with SHARE Mode............................................................................................ 2-9

When to Lock with SHARE ROW EXCLUSIVE Mode....................................................... 2-10

When to Lock in EXCLUSIVE Mode..................................................................................... 2-11

Privileges Required ................................................................................................................. 2-11

Letting Oracle Database Control Table Locking......................................................................... 2-11

Explicitly Acquiring Row Locks .................................................................................................. 2-12

About User Locks .................................................................................................................................. 2-13

When to Use User Locks ................................................................................................................ 2-13

Example of a User Lock.................................................................................................................. 2-13

Viewing and Monitoring Locks .................................................................................................... 2-14

Using Serializable Transactions for Concurrency Control ........................................................... 2-14

How Serializable Transactions Interact ....................................................................................... 2-15

Setting the Isolation Level of a Transaction ................................................................................ 2-16

The INITRANS Parameter ..................................................................................................... 2-16

Referential Integrity and Serializable Transactions .................................................................. 2-17

Using SELECT FOR UPDATE................................................................................................ 2-18

READ COMMITTED and SERIALIZABLE Isolation ............................................................... 2-18

Transaction Set Consistency .................................................................................................. 2-18

Comparison of READ COMMITTED and SERIALIZABLE Transactions....................... 2-19

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika: