Oracle PL/SQL编程指南：12c Release 1版

需积分: 10 137 浏览量更新于2024-07-19 收藏 13.44MB PDF 举报

"Oracle PL/SQL Programming" 是一本被Oracle社区誉为最佳的PL/SQL编程指南，专门针对Oracle强大的过程性语言进行了详尽的解释。该书的第六版覆盖了Oracle Database 12c Release 1的新特性，是过去二十年来数以十万计的PL/SQL开发者受益的权威资料。作者Steven Feuerstein与Bill Pribyl以生动幽默的方式，通过丰富的代码示例和最佳实践，帮助读者理解语言基础，探索高级编码技术，并解决实际问题。这本书旨在快速让你的PL/SQL程序运行起来，提供了清晰的执行、追踪、测试、调试和代码管理指导。第六版特别介绍了Oracle 12.1的新功能，如ACCESSIBLE_BY子句、WITH FUNCTION和UDF pragma、BEQUEATH CURRENT_USER用于视图，以及新的条件编译指令。书中包含大量的代码样本，从简单易懂的例子到可重用的打包实用工具，让读者能够优化PL/SQL性能，利用如函数结果缓存和Oracle工具（如PL/Scope和PL/SQL层次结构分析器）。此外，本书还强调了如何构建模块化、易于维护的PL/SQL应用程序，使用包、过程、函数和触发器等技术。这些技术有助于提升代码的可读性和可维护性，降低长期项目的复杂性。无论你是初学者还是经验丰富的开发者，都能从这本书中找到有价值的见解和实用技巧，提升你在Oracle数据库开发中的专业技能。在学习过程中，你可以通过在线版本（http://my.safaribooksonline.com）获取大部分标题的在线版，这尤其适合教育、商业或销售推广用途。若需批量购买或了解更多信息，可以联系O'Reilly Media的公司/机构销售部门：800-998-9938或corporate@oreilly.com。这本书的第六版于2014年1月首次发布，为读者带来了最新的Oracle PL/SQL编程知识和实践经验。

Opening Cursor Variables 523

Fetching from Cursor Variables 524

Rules for Cursor Variables 527

Passing Cursor Variables as Arguments 530

Cursor Variable Restrictions 532

Cursor Expressions 533

Using Cursor Expressions 534

Restrictions on Cursor Expressions 536

16. Dynamic SQL and Dynamic PL/SQL. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 537

NDS Statements 538

The EXECUTE IMMEDIATE Statement 538

The OPEN FOR Statement 543

About the Four Dynamic SQL Methods 548

Binding Variables 550

Argument Modes 551

Duplicate Placeholders 553

Passing NULL Values 554

Working with Objects and Collections 555

Dynamic PL/SQL 557

Build Dynamic PL/SQL Blocks 558

Replace Repetitive Code with Dynamic Blocks 560

Recommendations for NDS 561

Use Invoker Rights for Shared Programs 561

Anticipate and Handle Dynamic Errors 562

Use Binding Rather than Concatenation 564

Minimize the Dangers of Code Injection 566

When to Use DBMS_SQL 569

Obtain Information About Query Columns 569

Meeting Method 4 Dynamic SQL Requirements 571

Minimizing Parsing of Dynamic Cursors 578

Oracle Database 11g New Dynamic SQL Features 579

Enhanced Security for DBMS_SQL 584

Part V. PL/SQL Application Construction

17.

Procedures, Functions, and Parameters. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 591

Modular Code 592

Procedures 593

Calling a Procedure 596

The Procedure Header 596

xiv | Table of Contents

The Procedure Body 596

The END Label 597

The RETURN Statement 597

Functions 597

Structure of a Function 598

The RETURN Datatype 601

The END Label 602

Calling a Function 603

Functions Without Parameters 604

The Function Header 604

The Function Body 605

The RETURN Statement 605

Parameters 607

Defining Parameters 608

Actual and Formal Parameters 608

Parameter Modes 609

Explicit Association of Actual and Formal Parameters in PL/SQL 613

The NOCOPY Parameter Mode Qualifier 617

Default Values 618

Local or Nested Modules 619

Benefits of Local Modularization 620

Scope of Local Modules 623

Sprucing Up Your Code with Nested Subprograms 623

Subprogram Overloading 624

Benefits of Overloading 625

Restrictions on Overloading 628

Overloading with Numeric Types 629

Forward Declarations 630

Advanced Topics 631

Calling Your Function from Inside SQL 631

Table Functions 637

Deterministic Functions 647

Implicit Cursor Results (Oracle Database 12c) 649

Go Forth and Modularize! 650

18.

Packages. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 651

Why Packages? 651

Demonstrating the Power of the Package 652

Some Package-Related Concepts 655

Diagramming Privacy 657

Rules for Building Packages 658

The Package Specification 658

Table of Contents | xv

The Package Body 660

Initializing Packages 662

Rules for Calling Packaged Elements 666

Working with Package Data 667

Global Within a Single Oracle Session 668

Global Public Data 669

Packaged Cursors 669

Serializable Packages 674

When to Use Packages 677

Encapsulate Data Access 677

Avoid Hardcoding Literals 680

Improve Usability of Built-in Features 683

Group Together Logically Related Functionality 683

Cache Static Session Data 684

Packages and Object Types 685

19. Triggers. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 687

DML Triggers 688

DML Trigger Concepts 689

Creating a DML Trigger 691

DML Trigger Example: No Cheating Allowed! 696

Multiple Triggers of the Same Type 702

Who Follows Whom 703

Mutating Table Errors 705

Compound Triggers: Putting It All in One Place 706

DDL Triggers 710

Creating a DDL Trigger 710

Available Events 713

Available Attributes 713

Working with Events and Attributes 715

Dropping the Undroppable 718

The INSTEAD OF CREATE Trigger 719

Database Event Triggers 720

Creating a Database Event Trigger 721

The STARTUP Trigger 722

The SHUTDOWN Trigger 723

The LOGON Trigger 723

The LOGOFF Trigger 723

The SERVERERROR Trigger 724

INSTEAD OF Triggers 728

Creating an INSTEAD OF Trigger 728

The INSTEAD OF INSERT Trigger 730

xvi | Table of Contents

The INSTEAD OF UPDATE Trigger 732

The INSTEAD OF DELETE Trigger 733

Populating the Tables 733

INSTEAD OF Triggers on Nested Tables 734

AFTER SUSPEND Triggers 736

Setting Up for the AFTER SUSPEND Trigger 736

Looking at the Actual Trigger 738

The ORA_SPACE_ERROR_INFO Function 739

The DBMS_RESUMABLE Package 740

Trapped Multiple Times 742

To Fix or Not to Fix? 743

Maintaining Triggers 743

Disabling, Enabling, and Dropping Triggers 743

Creating Disabled Triggers 744

Viewing Triggers 745

Checking the Validity of Triggers 746

20. Managing PL/SQL Code. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 749

Managing Code in the Database 750

Overview of Data Dictionary Views 751

Display Information About Stored Objects 753

Display and Search Source Code 753

Use Program Size to Determine Pinning Requirements 755

Obtain Properties of Stored Code 756

Analyze and Modify Trigger State Through Views 757

Analyze Argument Information 758

Analyze Identifier Usage (Oracle Database 11g’s PL/Scope) 759

Managing Dependencies and Recompiling Code 762

Analyzing Dependencies with Data Dictionary Views 763

Fine-Grained Dependency (Oracle Database 11g) 767

Remote Dependencies 769

Limitations of Oracle’s Remote Invocation Model 772

Recompiling Invalid Program Units 773

Compile-Time Warnings 777

A Quick Example 777

Enabling Compile-Time Warnings 778

Some Handy Warnings 780

Testing PL/SQL Programs 788

Typical, Tawdry Testing Techniques 789

General Advice for Testing PL/SQL Code 793

Automated Testing Options for PL/SQL 794

Tracing PL/SQL Execution 795

Table of Contents | xvii

DBMS_UTILITY.FORMAT_CALL_STACK 796

UTL_CALL_STACK (Oracle Database 12c) 798

DBMS_APPLICATION_INFO 801

Tracing with opp_trace 803

The DBMS_TRACE Facility 804

Debugging PL/SQL Programs 808

The Wrong Way to Debug 809

Debugging Tips and Strategies 811

Using Whitelisting to Control Access to Program Units 816

Protecting Stored Code 818

Restrictions on and Limitations of Wrapping 818

Using the Wrap Executable 819

Dynamic Wrapping with DBMS_DDL 819

Guidelines for Working with Wrapped Code 821

Introduction to Edition-Based Redefinition (Oracle Database 11g Release 2) 821

21. Optimizing PL/SQL Performance. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 825

Tools to Assist in Optimization 827

Analyzing Memory Usage 827

Identifying Bottlenecks in PL/SQL Code 827

Calculating Elapsed Time 833

Choosing the Fastest Program 834

Avoiding Infinite Loops 836

Performance-Related Warnings 837

The Optimizing Compiler 838

Insights on How the Optimizer Works 840

Runtime Optimization of Fetch Loops 843

Data Caching Techniques 844

Package-Based Caching 845

Deterministic Function Caching 850

THe Function Result Cache (Oracle Database 11g) 852

Caching Summary 868

Bulk Processing for Repeated SQL Statement Execution 869

High-Speed Querying with BULK COLLECT 870

High-Speed DML with FORALL 877

Improving Performance with Pipelined Table Functions 888

Replacing Row-Based Inserts with Pipelined Function-Based Loads 889

Tuning Merge Operations with Pipelined Functions 896

Asynchronous Data Unloading with Parallel Pipelined Functions 898

Performance Implications of Partitioning and Streaming Clauses in Parallel

Pipelined Functions 902

Pipelined Functions and the Cost-Based Optimizer 903

xviii | Table of Contents

剩余1381页未读，继续阅读

普通网友

粉丝: 324
资源:
5

Oracle PL/SQL编程指南：12c Release 1版

Oracle PL/SQL Programming 6th edition

Oracle PL SQL Programming 6th 原版PDF by Feuerstein & Pribyl

Oracle PL SQL Programming(6th) epub

oracle pl/sql programming

Oracle PL/SQL Programming

Advanced Oracle PL/SQL Programming with Packages

Oracle PL/SQL Programming, 5th Edition

Oracle PL/SQL programming(5th Edition)

oracle8 pl/sql programming

PLSQLcode in Oracle9i PL/SQL Programming

最新资源