This integrated learning solution teaches all the Oracle PL/SQL skills you need, hands-on, through real-world labs, extensive examples, exercises, and projects! Completely updated for Oracle 11g, Oracle PL/SQL by Example, Fourth Edition covers all the fundamentals, from PL/SQL syntax and program control through packages and Oracle 11g’s significantly improved triggers.

One step at a time, you’ll walk through every key task, discovering the most important PL/SQL programming techniques on your own. Building on your hands-on learning, the authors share solutions that offer deeper insights and proven best practices. End-of-chapter projects bring together all the techniques you’ve learned, strengthening your understanding through real-world practice.

This book’s approach fully reflects the authors’ award-winning experience teaching PL/SQL programming to professionals at Columbia University. New database developers and DBAs can use its step-by-step instructions to get productive fast; experienced PL/SQL programmers can use this book as a practical solutions reference. Coverage includes

        • Mastering basic PL/SQL concepts and general programming language fundamentals, and understanding SQL’s role in

          PL/SQL

        • Using conditional and iterative program control techniques, including the new CONTINUE and CONTINUE WHEN statements

        • Efficiently handling errors and exceptions

        • Working with cursors and triggers, including Oracle 11g’s powerful new compound triggers

        • Using stored procedures, functions, and packages to write modular code that other programs can execute

        • Working with collections, object-relational features, native dynamic SQL, bulk SQL, and other advanced PL/SQL capabilities

        • Handy reference appendices: PL/SQL formatting guide, sample database schema, ANSI SQL standards reference, and

          more

Benjamin Rosenzweig is a Software Development Manager at Misys. Previously he was a Principal Consultant at Oracle. His experience ranges from creating an electronic Tibetan—English Dictionary in Kathmandu, Nepal, to supporting presentation centers at Goldman Sachs and managing trading systems at TIAA-CREF. As an instructor at Columbia University’s Computer Technology and Application Program, he was awarded the Outstanding Teaching Award. Rosenzweig wrote and presented Oracle Forms Developer: The Complete Video Course, and coauthored Oracle Web Application Programming for PL/SQL Developers.

Elena Silvestrova Rakhimov is Senior Developer and Team Lead at Alea Software. She has more than fifteen years of experience in database development in a wide spectrum of enterprise and business environments, ranging from non-profit organizations to Wall Street. She has taught database programming at Columbia University.

Contents

Acknowledgments xiv

About the Authors xv

Introduction xvii

CHAPTER 1 PL/SQL Concepts 1

    LAB 1.1 PL/SQL in Client/Server Architecture 2

        1.1.1 Use PL/SQL Anonymous Blocks 8

        1.1.2 Understand How PL/SQL Gets Executed 10

    LAB 1.2 PL/SQL in SQL*Plus 12

        1.2.1 Use Substitution Variables 16

        1.2.2 Use the DBMS_OUTPUT.PUT_LINE Statement 17

Chapter 1 Try It Yourself 19

CHAPTER 2 General Programming Language

Fundamentals 21

    LAB 2.1 PL/SQL Programming Fundamentals 22

        2.1.1 Make Use of PL/SQL Language Components 23

        2.1.2 Make Use of PL/SQL Variables 24

        2.1.3 Handle PL/SQL Reserved Words 26

        2.1.4 Make Use of Identifiers in PL/SQL 27

        2.1.5 Make Use of Anchored Datatypes 28

        2.1.6 Declare and Initialize Variables 31

        2.1.7 Understand the Scope of a Block, Nested Blocks, and Labels 34

Chapter 2 Try It Yourself 37

CHAPTER 3 SQL in PL/SQL 39

    LAB 3.1 Making Use of DML in PL/SQL 40

        3.1.1 Use the Select INTO Syntax for Variable Initialization 41

        3.1.2 Use DML in a PL/SQL Block 42

        3.1.3 Make Use of a Sequence in a PL/SQL Block 44

    LAB 3.2 Making Use of SAVEPOINT 45

        3.2.1 Make Use of COMMIT, ROLLBACK, and SAVEPOINT in a PL/SQL Block 48

Chapter 3 Try It Yourself 51

CHAPTER 4 Conditional Control: IF Statements 53

    LAB 4.1 IF Statements 54

        4.1.1 Use the IF-THEN Statement 58

        4.1.2 Use the IF-THEN-ELSE Statement 62

    LAB 4.2 ELSIF Statements 65

        4.2.1 Use the ELSIF Statement 69

    LAB 4.3 Nested IF Statements 74

        4.3.1 Use Nested IF Statements 76

Chapter 4 Try It Yourself 80

CHAPTER 5 Conditional Control: CASE Statements 81

Browse Similar Topics

Top Level Categories:
Information Technology & Software Development
Vendor

Sub-Categories:
Information Technology & Software Development > Databases
Databases > Oracle PL/SQL
Vendor > Oracle