Oracle 11g Release 2 Advanced Application Developer指南

需积分: 5 71 浏览量更新于2024-06-25 收藏 3.53MB PDF 举报

"Oracle Database Advanced Application Developer's Guide 11g Release 2 (11.2) 是一本针对Oracle数据库高级应用开发者的指南，由Oracle公司出版，主要作者为Sheila Moore，还有多位贡献者参与编写。这本书涵盖了Oracle 11g Release 2版本的相关内容，旨在帮助开发者更深入地理解和利用Oracle数据库进行复杂应用的开发。" 在Oracle Database Advanced Application Developer's Guide 11g Release 2中，你可以找到以下重要的知识点： 1. **PL/SQL编程**：Oracle的PL/SQL是数据库内嵌的编程语言，用于创建存储过程、函数、触发器等。该指南会详细介绍如何编写高效、健壮的PL/SQL代码，包括变量声明、控制流程、异常处理和游标操作。 2. **数据库对象的高级特性**：如索引、物化视图、分区表、复合索引等，这些特性对于优化查询性能和管理大规模数据至关重要。 3. **数据库设计与优化**：包括实体关系模型、数据规范化、数据库架构设计，以及SQL查询优化技术，如执行计划分析、索引选择和统计信息的收集。 4. **分布式数据库系统**：Oracle支持分布式数据库系统，使得数据可以跨多个物理位置进行管理和访问，这包括分布式事务处理、全局命名和分布式查询。 5. **安全性**：讲解如何设置用户权限、角色管理、行级安全策略、数据加密以及审计功能，确保数据库的安全性和合规性。 6. **高级数据类型**：如BFILE、CLOB、BLOB、NCLOB等，这些类型允许存储大对象数据，适用于多媒体应用或需要存储大量文本信息的情况。 7. **Java在Oracle中的应用**：Oracle数据库支持内置的Java虚拟机，开发者可以编写Java PL/SQL包，实现更高级的应用功能。 8. **存储过程和函数的调用接口**：包括使用ODBC、JDBC和.NET等不同编程语言与Oracle数据库交互的方法。 9. **性能监控和调优**：提供工具和方法来监控数据库性能，如SQL*Plus、 tkprof 和AWR报告，以及如何通过调整参数和索引来优化性能。 10. **并发控制和事务管理**：深入理解多用户环境下的并发问题，如死锁检测和解决，以及事务的ACID属性和隔离级别。此指南还涵盖了数据库的备份恢复策略、XML处理、XML数据库、数据仓库和OLAP应用等内容，为开发者提供了全面的Oracle数据库高级应用开发指导。对于那些希望提升Oracle数据库开发技能的专业人士来说，这是一份非常宝贵的参考资料。

xvi

GLOBAL_TXN_PROCESSES Initialization Parameter .................................................... 15-23

Managing Transaction Branches on Oracle RAC.............................................................. 15-23

Managing Instance Recovery in Oracle RAC with DTP Services................................... 15-24

Global Uniqueness of XIDs in Oracle RAC........................................................................ 15-25

Tight and Loose Coupling.................................................................................................... 15-25

SQL-Based Oracle XA Restrictions............................................................................................. 15-25

Rollbacks and Commits ........................................................................................................ 15-26

DDL Statements ..................................................................................................................... 15-26

Session State............................................................................................................................ 15-26

EXEC SQL ............................................................................................................................... 15-26

Miscellaneous Restrictions........................................................................................................... 15-26

16 Developing Applications with the Publish-Subscribe Model

Introduction to the Publish-Subscribe Model................................................................................. 16-1

Publish-Subscribe Architecture ......................................................................................................... 16-2

Database Events............................................................................................................................... 16-2

Oracle Advanced Queuing ............................................................................................................ 16-2

Client Notification........................................................................................................................... 16-2

Publish-Subscribe Concepts ............................................................................................................... 16-3

Examples of a Publish-Subscribe Mechanism ................................................................................ 16-4

17 Using the Identity Code Package

Identity Concepts .................................................................................................................................. 17-1

What is the Identity Code Package? .................................................................................................. 17-5

Using the Identity Code Package ....................................................................................................... 17-6

Storing RFID Tags in Oracle Database Using MGD_ID ADT .................................................. 17-6

Creating a Table with MGD_ID Column Type and Storing EPC Tag Encodings in the

Column 17-6

Constructing MGD_ID Objects to Represent RFID Tags................................................... 17-7

Inserting an MGD_ID Object into a Database Table........................................................... 17-9

Querying MGD_ID Column Type....................................................................................... 17-10

Building a Function-Based Index Using the Member Functions of the MGD_ID Column Type ..

17-10

Using MGD_ID ADT Functions.................................................................................................. 17-10

Using the get_component Function with the MGD_ID Object....................................... 17-11

Parsing Tag Data from Standard Representations............................................................ 17-11

Reconstructing Tag Representations from Fields ............................................................. 17-12

Translating Between Tag Representations......................................................................... 17-13

Defining a Category of Identity Codes and Adding Encoding Schemes to an Existing Category

17-13

Creating a Category of Identity Codes ............................................................................... 17-13

Adding Two Metadata Schemes to a Newly Created Category..................................... 17-14

Identity Code Package Types............................................................................................................ 17-18

DBMS_MGD_ID_UTL Package....................................................................................................... 17-18

Identity Code Metadata Tables and Views.................................................................................... 17-19

Electronic Product Code (EPC) Concepts........................................................................................ 17-21

RFID Technology and EPC v1.1 Coding Schemes ................................................................... 17-21

xvii

Product Code Concepts and Their Current Use....................................................................... 17-22

Electronic Product Code (EPC)............................................................................................ 17-22

Global Trade Identification Number (GTIN) and Serializable Global Trade Identification

Number (SGTIN) 17-24

Serial Shipping Container Code (SSCC)............................................................................. 17-24

Global Location Number (GLN) and Serializable Global Location Number (SGLN). 17-24

Global Returnable Asset Identifier (GRAI) ........................................................................ 17-24

Global Individual Asset Identifier (GIAI) .......................................................................... 17-24

RFID EPC Network................................................................................................................ 17-24

Oracle Database Tag Data Translation Schema ............................................................................ 17-24

18 Schema Object Dependency

Overview of Schema Object Dependencies..................................................................................... 18-1

Querying Object Dependencies ......................................................................................................... 18-4

Object Status .......................................................................................................................................... 18-4

Invalidation of Dependent Objects ................................................................................................... 18-4

Session State and Referenced Packages....................................................................................... 18-8

Security Authorization................................................................................................................... 18-8

Guidelines for Reducing Invalidation.............................................................................................. 18-8

Add Items to End of Package ........................................................................................................ 18-8

Reference Each Table Through a View ........................................................................................ 18-9

Object Revalidation .............................................................................................................................. 18-9

Name Resolution in Schema Scope ................................................................................................. 18-10

Local Dependency Management ...................................................................................................... 18-11

Remote Dependency Management.................................................................................................. 18-11

Dependencies Among Local and Remote Database Procedures ........................................... 18-11

Dependencies Among Other Remote Objects........................................................................... 18-12

Dependencies of Applications..................................................................................................... 18-12

Remote Procedure Call (RPC) Dependency Management.......................................................... 18-12

Time-Stamp Dependency Mode ................................................................................................. 18-12

RPC-Signature Dependency Mode............................................................................................. 18-13

Changing Names and Default Values of Parameters....................................................... 18-15

Changing Specification of Parameter Mode IN................................................................. 18-15

Changing Subprogram Body................................................................................................ 18-15

Changing Data Type Classes of Parameters ...................................................................... 18-16

Changing Package Types...................................................................................................... 18-17

Controlling Dependency Mode................................................................................................... 18-18

Dependency Resolution........................................................................................................ 18-18

Suggestions for Managing Dependencies .......................................................................... 18-19

Shared SQL Dependency Management.......................................................................................... 18-19

19 Edition-Based Redefinition

Editions.................................................................................................................................................... 19-2

Editioned and Noneditioned Objects........................................................................................... 19-2

Editionable and Noneditionable Schema Object Types..................................................... 19-3

Rules for Editioned Objects.................................................................................................... 19-3

xviii

Enabling Editions for a User .................................................................................................. 19-4

Creating an Edition......................................................................................................................... 19-5

Inherited and Actual Objects......................................................................................................... 19-5

Dropping Inherited Objects.................................................................................................... 19-7

Actualizing Referenced Objects............................................................................................. 19-9

Making an Edition Available to Some Users .............................................................................. 19-9

Making an Edition Available to All Users................................................................................... 19-9

Current Edition and Session Edition.......................................................................................... 19-10

Your Initial Session Edition.................................................................................................. 19-10

Changing Your Session Edition........................................................................................... 19-11

Displaying the Names of the Current and Session Editions ........................................... 19-12

When the Current Edition Might Differ from the Session Edition................................. 19-12

Retiring an Edition........................................................................................................................ 19-13

Dropping an Edition..................................................................................................................... 19-14

Editioning Views................................................................................................................................. 19-15

Creating an Editioning View....................................................................................................... 19-16

Partition-Extended Editioning View Names............................................................................. 19-16

Changing the 'Write-ability' of an Editioning View................................................................. 19-16

Replacing an Editioning View..................................................................................................... 19-17

Dropping or Renaming the Base Table...................................................................................... 19-17

Adding Indexes and Constraints to the Base Table ................................................................. 19-17

SQL Optimizer Index Hints......................................................................................................... 19-17

Crossedition Triggers ......................................................................................................................... 19-17

Forward Crossedition Triggers................................................................................................... 19-18

Reverse Crossedition Triggers .................................................................................................... 19-18

Crossedition Trigger Interaction with Editions........................................................................ 19-18

Which Triggers Are Visible .................................................................................................. 19-19

What Kind of Triggers Can Fire........................................................................................... 19-19

Firing Order ............................................................................................................................ 19-20

Crossedition Trigger Execution ........................................................................................... 19-21

Creating a Crossedition Trigger.................................................................................................. 19-21

Coding the Forward Crossedition Trigger Body .............................................................. 19-22

Transforming Data from Pre- to Post-Upgrade Representation ............................................ 19-24

Preventing Lost Updates....................................................................................................... 19-25

Dropping the Crossedition Triggers .......................................................................................... 19-26

Displaying Information About Editions, Editioning Views, and Crossedition Triggers..... 19-26

Using Edition-Based Redefinition to Upgrade an Application .................................................

19-28

Preparing Your Application to Use Editioning Views ............................................................ 19-29

Procedure for Edition-Based Redefinition Using Only Editions ........................................... 19-30

Procedure for Edition-Based Redefinition Using Editioning Views ..................................... 19-32

Procedure for Edition-Based Redefinition Using Crossedition Triggers.............................. 19-33

Rolling Back the Application Upgrade...................................................................................... 19-34

Reclaiming Space Occupied by Unused Table Columns ........................................................ 19-34

Example: Using Edition-Based Redefinition to Upgrade an Application ............................ 19-35

Existing Application.............................................................................................................. 19-35

Preparing the Application to Use Editioning Views ........................................................ 19-36

Using Edition-Based Redefinition to Upgrade the Application ..................................... 19-37

xix

A Multithreaded extproc Agent

Why Use the Multithreaded extproc Agent?...................................................................................... A-1

The Challenge of Dedicated Agent Architecture ......................................................................... A-1

The Advantage of Multithreading.................................................................................................. A-1

Multithreaded extproc Agent Architecture ........................................................................................ A-2

Monitor Thread ................................................................................................................................. A-3

Dispatcher Threads........................................................................................................................... A-4

Task Threads...................................................................................................................................... A-4

Administering the Multithreaded extproc Agent ............................................................................. A-4

Agent Control Utility (agtctl) Commands..................................................................................... A-5

Using agtctl in Single-Line Command Mode................................................................................ A-5

Setting Configuration Parameters for a Multithreaded extproc Agent ............................. A-6

Starting a Multithreaded extproc Agent................................................................................. A-6

Shutting Down a Multithreaded extproc Agent ................................................................... A-6

Examining the Value of Configuration Parameters.............................................................. A-7

Resetting a Configuration Parameter to Its Default Value .................................................. A-7

Deleting an Entry for a Specific SID from the Control File.................................................. A-7

Requesting Help......................................................................................................................... A-7

Using Shell Mode Commands......................................................................................................... A-8

Example: Setting a Configuration Parameter ........................................................................ A-8

Example: Starting a Multithreaded extproc Agent............................................................... A-8

Configuration Parameters for Multithreaded extproc Agent Control...................................... A-8

Index

List of Examples

1–1 Displaying Current Date and Time in Nondefault Format ............................................... 1-12

1–2 Inserting and Displaying Date in Nondefault Formats...................................................... 1-12

1–3 Inserting and Displaying Dates and Times in Nondefault Formats ................................ 1-14

1–4 Accessing Information in a SYS.ANYDATA Column........................................................ 1-19

1–5 Querying the ROWID Pseudocolumn .................................................................................. 1-23

2–1 LOCK TABLE with SHARE MODE...................................................................................... 2-13

2–2 How the Pro*COBOL Precompiler Uses Locks................................................................... 2-22

2–3 Marking a Package Subprogram as Autonomous .............................................................. 2-36

2–4 AFTER SUSPEND Trigger Handles Suspended Storage Allocation................................ 2-38

3–1 Enforcing a Phone Number Format with Regular Expressions........................................... 3-9

3–2 Inserting Phone Numbers in Correct and Incorrect Formats............................................ 3-10

3–3 Using Back References to Reposition Characters................................................................ 3-10

4–1 Function-Based Index for Precomputing Arithmetic Expression........................................ 4-5

4–2 Function-Based Indexes on Object Column............................................................................ 4-6

4–3 Function-Based Index for Faster Case-Insensitive Searches................................................. 4-7

4–4 Function-Based Index for Language-Dependent Sorting ..................................................... 4-7

5–1 Enforcing Business Rules with Constraints ............................................................................ 5-2

5–2 Enforcing Business Rules with Both Constraints and Application Code........................... 5-3

5–3 Inserting NULL Values into Columns with NOT NULL Constraints ................................ 5-5

5–4 Deferring Constraint Checks.................................................................................................. 5-12

5–5 Defining Constraints with the CREATE TABLE Statement .............................................. 5-16

5–6 Defining Constraints with the ALTER TABLE Statement................................................. 5-16

5–7 Creating Enabled Constraints ................................................................................................ 5-18

5–8 Creating Disabled Constraints............................................................................................... 5-19

5–9 Enabling Existing Constraints................................................................................................ 5-19

5–10 Disabling Existing Constraints............................................................................................... 5-20

5–11 Modifying Constraints ............................................................................................................ 5-21

5–12 Renaming a Constraint............................................................................................................ 5-22

5–13 Dropping Constraints.............................................................................................................. 5-23

5–14 Viewing Information About Constraints.............................................................................. 5-25

6–1

Anonymous Block....................................................................................................................... 6-2

6–2 Anonymous Block with Exception Handler for Predefined Error ...................................... 6-3

6–3 Anonymous Block with Exception Handler for User-Defined Exception.......................... 6-3

6–4 Stored Procedure with Parameters........................................................................................... 6-5

6–5 %TYPE and %ROWTYPE Attributes ....................................................................................... 6-6

6–6 Creating PL/SQL Package and Invoking Package Subprogram...................................... 6-10

6–7 Raising ORA-04068.................................................................................................................. 6-14

6–8 Trapping ORA-04068............................................................................................................... 6-14

6–9 DML Statements that Reference Collections........................................................................ 6-16

6–10 SELECT Statements that Reference Collections .................................................................. 6-17

6–11 FOR Loops that Reference Collections and Return DML .................................................. 6-17

6–12 Fetching Data with Cursor Variable ..................................................................................... 6-19

6–13 Cursor Variable with Discriminator...................................................................................... 6-21

6–14 Compile-Time Errors............................................................................................................... 6-22

6–15 Invoking a Subprogram Interactively with SQL*Plus........................................................ 6-30

6–16 Creating and Using a Session Variable with SQL*Plus...................................................... 6-30

6–17 Invoking a Subprogram from Within Another Subprogram ............................................ 6-31

6–18 PL/SQL Function in SQL Expression (Follows Rules)....................................................... 6-36

6–19 PL/SQL Function in SQL Expression (Exception to Rule) ................................................ 6-37

6–20 PRAGMA RESTRICT_REFERENCES................................................................................... 6-42

6–21 PRAGMA RESTRICT REFERENCES with TRUST on Invokee ........................................ 6-43

6–22 PRAGMA RESTRICT REFERENCES with TRUST on Invoker ........................................ 6-43

6–23 Overloaded Package Function with PRAGMA RESTRICT_REFERENCES ................... 6-44

7–1 Is STANDARD and DBMS_STANDARD PL/Scope Identifier Data Available?.............. 7-2

剩余575页未读，继续阅读

weixin_40191861_zj

粉丝: 83
资源: 1万+

Oracle 11g Release 2 Advanced Application Developer指南

Oracle Database Semantic Technologies Developer's Guide11g Relea

履带式拖拉机Creo2.0_三维3D设计图纸.zip

SSM+JSP高校毕业生就业满意度调查统计系统答辩PPT.pptx

SSM+JSP冰淇淋在线购买网站答辩PPT.ppt

SSM+JSP医护系统答辩PPT.pptx

Shapely-1.6.4.post2-cp35-cp35m-win_amd64.whl

SSM+JSP农场信息化管理系统答辩PPT.pptx

基于谱聚类滤波器级剪枝方法用于压缩卷积神经网络

SSM+JSP教学质量评价系统答辩PPT.pptx

射频读写器E710示例程序及源码.zip

最新资源