Oracle Pl/sql By Example

Description

By (author) Rosenzweig Benjamin; By (author) Rakhimov Elena

:

Using PL/SQL for Oracle Database 21c, you can build solutions that deliver unprecedented performance and efficiency in any environment, including the cloud. Oracle PL/SQL by Example, Sixth Edition, teaches all the PL/SQL skills you”ll need, through real-world labs and extensive examples. Now fully updated for the newest version of PL/SQL 21c, it covers everything from basic syntax and program control through the latest optimization and tuning enhancements.

Step by step, you”ll walk through every key task, mastering today”s most valuable Oracle 21c PL/SQL programming techniques on your own. Start by downloading the supporting schema and exercises from informit.com/title/9780138062835. Once you”ve done an exercise, the author doesn”t just present the answer: She offers an in-depth discussion introducing deeper insights and modern best practices.

This book”s approach fully reflects the author”s award-winning experience teaching PL/SQL to professionals at Columbia University in New York City. New database developers and DBAs can use it to get productive fast; experienced PL/SQL programmers will find it to be a superb Oracle Database 21c solutions reference.

New in This Edition

• Updated code examples throughout
• New iteration controls for the FOR LOOP statement, such as stepped range, multiple iterations, collection, and cursor iterations
• Enhancements for PL/SQL qualified expressions
• Performance enhancements for PL/SQL functions, such as SQL macro, and better control of the result cache

Other Topics Covered

• Mastering basic PL/SQL concepts and language fundamentals, and understanding SQL”s role in PL/SQL
• Using conditional and iterative program controls
• Efficiently handling errors and exceptions
• Working with cursors and triggers, including compound triggers
• Using stored procedures, functions, and packages to write modular code that other programs can run
• Working with collections, object-relational features, native dynamic SQL, bulk SQL, and other advanced features

Table of contents:

Preface xvii
Introduction to PL/SQL New Features in Oracle 21c xxiii
Chapter 1 PL/SQL Concepts 1
Lab 1.1: PL/SQL Architecture 2
PL/SQL Architecture 2
PL/SQL Block Structure 5
How PL/SQL Gets Executed 9
Lab 1.2: PL/SQL Development Environment 10
Getting Started with SQL Developer 10
Getting Started with SQL*Plus 13
Executing PL/SQL Scripts 14
Lab 1.3: PL/SQL: The Basics 18
DBMS_OUTPUT.PUT_LINE Statement 18
Substitution Variable Feature 21
Summary 25
Chapter 2 PL/SQL Language Fundamentals 27
Lab 2.1: PL/SQL Language Components 27
PL/SQL Variables 29
PL/SQL Reserved Words 31
Delimiters 32
Literals in PL/SQL 33
Lab 2.2: Anchored Data Types 33
Lab 2.3: Scope of a Variable, Block, Nested Blocks, and Labels 35
Scope of a Variable 35
Nested Blocks and Labels 36
Summary 38
Chapter 3 SQL in PL/SQL 39
Lab 3.1: SQL Statements in PL/SQL 39
Initializing Variables with the SELECT INTO Statement 40
Using DML Statements in a PL/SQL Block 41
Using a Sequence in a PL/SQL Block 43
Lab 3.2: Transaction Control in PL/SQL 44
The COMMIT, ROLLBACK, and SAVEPOINT Statements 44
The SET TRANSACTION Statement 47
Summary 48
Chapter 4 Conditional Control: IF Statements 49
Lab 4.1: IF Statements 50
IF-THEN Statements 50
IF-THEN-ELSE Statements 52
Lab 4.2: ELSIF Statements 55
Lab 4.3: Nested IF Statements 59
Lo