Oracle Pl/sql Interactive Workbook

In this unique workbook pedagogy with hands-on exercises, programming projects and a free Web-based training module, the author covers every key Oracle SQL concept: SQL*Plus, DDL, DML, DQL, the Oracle Data Dictionary, and more! Pub. 11/02.

Acknowledgments

xi

From the Editor

xiii

Introduction

xv

Programming Concepts

1

(22)

The Nature of a Computer Program and Programming Languages

2

(8)

Understanding the Nature of Computer Programs and Programming Languages

5

(1)

Understanding the Differences Between Compiled and Interpreted Languages

6

(4)

Good Programming Practices

10

(12)

Structured Programming

11

(6)

Understanding the Nature of Good Programming Practices

17

(1)

Understanding Formatting Guidelines

18

(4)

Test Your Thinking

22

(1)

PL/SQL Concepts

23

(26)

PL/SQL in Client-Server Architecture

24

(13)

Using PL/SQL Anonymous Blocks

31

(1)

Understanding How PL/SQL Gets Executed

32

(5)

PL/SQL in SQL *Plus

37

(11)

Using Substitution Variables

42

(1)

Using DBMS_OUTPUT.PUT_LINE Statement

43

(5)

Test Your Thinking

48

(1)

General Programming Language Fundamentals

49

(18)

PL/SQL Programming Fundamentals

50

(15)

Making Use of PL/SQL Language Components

52

(1)

Making Use of PL/SQL Variables

52

(1)

Making Use of Anchored Datatypes

53

(1)

Declaring and Initilizing Variables

53

(2)

Understanding Scope of a Block, Nested Blocks, and Labels

55

(10)

Test Your Thinking

65

(2)

SQL in PL/SQL

67

(22)

Making use of DML in PL/QL

68

(9)

Using the Select Into Syntax for Variable Initialization

69

(2)

Using DML in a PL/SQL Block

71

(1)

Making Use of a Sequence in a PL/SQL Block

72

(5)

Making Use of Savepoint

77

(10)

Making Use of COMMIT, ROOLBACK, and SAVEPOINT in a PL/SQL Block

81

(6)

Test Your Thinking

87

(2)

Conditional Control

89

(40)

IF Statements

90

(16)

Using the IF-THEN Statement

95

(2)

Using the IF-THEN-ELSE Statement

97

(9)

ELSIF Statements

106

(11)

Using the ELSIF Statement

111

(6)

Nested IF Statements

117

(9)

Using Nested IF Statements

120

(6)

Test Your Thinking

126

(3)

Exception Handling and Built-In Exceptions

129

(24)

Handling Errors

130

(8)

Understanding the Importance of Error Handling

134

(4)

Built-In Exceptions

138

(13)

Using Built-In Exceptions

144

(7)

Test Your Thinking

151

(2)

Iterative Control

153

(50)

Simple Loops

154

(13)

Using Simple Loops with EXIT Conditions

158

(2)

Using Simple Loops with EXIT WHEN Conditions

160

(7)

WHILE Loops

167

(11)

Using WHILE Loops

172

(6)

Numeric FOR Loops

178

(14)

Using Numeric FOR Loops with the IN Option

183

(2)

Using Numeric FOR Loops with the REVERSE Option

185

(7)

Nested Loops

192

(9)

Using Nested Loops

195

(6)

Test Your Thinking

201

(2)

Introduction to Cursors

203

(38)

Cursor Manipulation

204

(17)

Processing an Explicit Cursor

208

(3)

Making use of Cursor Attributes

211

(2)

Putting It all Together

213

(8)

Using Cursor FOR Loops and Nesting Cursors

221

(8)

Using Cursor FOR LOOPS

222

(1)

Processing Nested Cursors

222

(7)

Using PARAMETERS with Cursors and FOR UPDATE Cursors

229

(11)

Using Parameters in a Cursor

230

(2)

Using a FOR UPDATE Cursor

232

(3)

WHERE CURRENT OF Clause

235

(5)

Test Your Thinking

240

(1)

Exceptions

241

(40)

Exception Scope

242

(11)

Understanding the Scope of an Exception

246

(7)

USER-DEFINED Exceptions

253

(12)

Using User-Defined Exceptions

259

(6)

Exception Propagation

265

(15)

Understanding How Exceptions Propagate

272

(1)

Reraising Exceptions

273

(7)

Test Your Thinking

280

(1)

Exceptions: Advanced Concepts

281

(30)

RAISE_APPLICATION_ERROR

282

(12)

Using RAISE_APPLICATION _ERROR

286

(8)

EXCEPTION_INIT pragma

294

(8)

Using EXCEPTION_INIT Pragma

297

(5)

SQLCODE and SQLERRM

302

(8)

Using SQLCODE and SQLERRM

306

(4)

Test Your Thinking

310

(1)

Procedures

311

(14)

Creating Procedures

312

(6)

Creating Procedures

314

(1)

Querying the Data Dictionary for Information on Procedures

315

(3)

Passing Parameters IN and OUT of Procedures

318

(5)

Using In and OUT Parameters with Procedures

320

(3)

Test Your Thinking

323

(2)

Functions

325

(10)

Creating and Using Functions

326

(8)

Creating Stored Functions

328

(1)

Making Use of Functions

328

(1)

Invoking Functions in SQL Statements

329

(1)

Writing Complex Functions

329

(5)

Test Your Thinking

334

(1)

Packages

335

(18)

The Benefits of Utilizing Packages

336

(15)

Creating the Package Specification

338

(1)

Creating the Package Body

339

(1)

Calling Stored Packages

340

(1)

Creating Private Objects

341

(2)

Creating Package Variables and Cursors

343

(8)

Test Your Thinking

351

(2)

Stored Code

353

(22)

Advanced Features of Stored Code

354

(19)

Getting Stored Code Information from the Data Dictionary

354

(3)

Enforcing Purity Level with RESTRICT_REFERENCES Pragma

357

(2)

Overloading Modules

359

(14)

Test Your Thinking

373

(2)

Triggers

375

(28)

What Triggers Are

376

(14)

Understanding What a Trigger Is

382

(2)

Using BEFORE and AFTER Triggers

384

(6)

Types of Triggers

390

(5)

Using Row and Statement Triggers

392

(3)

Mutating Table Issues

395

(6)

Understanding Mutating Tables

397

(4)

Test Your Thinking

401

(2)

PL/SQL Tables

403

(14)

Making Use of PL/SQL Tables

404

(11)

Declaring and Referencing a PL/SQL Table

405

(3)

Making Use of PL/SQL Table Attributes

408

(7)

Test Your Thinking

415

(2)

Appendix A Answers to Self-Review Questions

417

(10)

Appendix B PL/SQL Formatting Guide

427

(6)

Appendix C Student Database Scheme

433

(12)

Index

445

What is included with this book?

The New copy of this book will include any supplemental materials advertised. Please check the title of the book to determine if it should include any access cards, study guides, lab manuals, CDs, etc.

The Used, Rental and eBook copies of this book are not guaranteed to include any supplemental materials. Typically, only the book itself is included. This is true even if the title states it includes any access cards, study guides, lab manuals, CDs, etc.

FREE SHIPPING

Amazon no longer offers textbook rentals. We do!

Oracle Pl/sql Interactive Workbook

Summary

Table of Contents

Supplemental Materials