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

oracle

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

身份认证购VIP最低享 7 折!

领优惠券(最高得80元）

资源详情

资源推荐

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必知必会

oracle与pl/sql下载及安装

Oracle PL / SQL – INSERT触发器之前的示例

PL/SQL软件操作

PL/SQL面试怎么达到面试要求

pl/sql14.0.6和Oracle19c有冲突吗

dbatools for pl/sql

Oracle PL / SQL – INSTEAD OF触发器示例

怎么确保是pl/sql环境而不是sql环境

pl/sql developer使用手册

pl/sql developer和sql developer 软件的区别。

pl/sql developer使用教程

成都笔试——PL/SQL准备

怎么查看pl/sql操作记录

PL/SQL developer oracle乱码

pl/sql: statement ignored

PL/SQL Developer连接Oracle数据库

最新资源