Oracle PL / SQL For Dummies

Note: Supplemental materials are not guaranteed with Rental or Used book purchases.

ISBN: 9780764599576 | 0764599577
Cover: Paperback
Copyright: 6/13/2006

Rent

(Recommended)

$28.25

Term

Due

Price

Quarter

Aug 16

$28.25

*This item is part of an exclusive publisher rental program and requires an additional convenience fee. This fee will be reflected in the shopping cart.
Buy New

In Stock Usually Ships in 24-48 Hours

$30.72

Find tips for creating efficient PL/SQL codeIf you know a bit about SQL, this book will make PL/SQL programming painless!The Oracle has spoken-you need to get up to speed on PL/SQL programming, right? We predict it'll be a breeze with this book! You'll find out about code structures, best practices, and code naming standards, how to use conditions and loops, where to place PL/SQL code in system projects, ways to manipulate data, and more.Discover how to Write efficient, easy-to-maintain code Test and debug PL/SQL routines Integrate SQL and PL/SQL Apply PL/SQL best practices Use new features introduced in Oracle 9i and 10g

Introduction

(1)

About This Book

(1)

Foolish Assumptions

(1)

How This Book Is Organized

(2)

Part I: Basic PL/SQL Concepts

(1)

Part II: Getting Started with PL/SQL

(1)

Part III: Standards and Structures

(1)

Part IV: PL/SQL Data Manipulations

(1)

Part V: Taking PL/SQL to the Next Level

(1)

Part VI: The Part of Tens

(1)

Icons Used in This Book

(1)

Where to Go from Here

(3)

Part I: Basic PL/SQL Concepts

(32)

PL/SQL and Your Database

(14)

Knowing Just Enough about Relational Databases

(7)

What makes a database ``relational''?

(2)

Understanding basic database terminology

(1)

Introducing database normalization

(3)

What is a DBMS?

(1)

The Scoop on SQL and PL/SQL

(3)

The purpose of SQL and PL/SQL

(1)

The difference between SQL and PL/SQL

(1)

What's new in Oracle SQL and PL/SQL?

(1)

What Is PL/SQL Good For?

(4)

Using database triggers

(1)

Scripting with speed

(1)

Keeping code server-side

(1)

Programming for Oracle Developer

(2)

The PL/SQL Environment

(16)

Setting Up an Oracle Environment

(2)

Hardware and Software Requirements

(1)

Accessing the Oracle Technology Network

(1)

Installing the Database

(1)

Working with Code

(3)

SQL *Plus

(2)

Oracle SQL Developer

(1)

Third-party tools

(1)

Establishing a Database Connection

(3)

Checking the operating services

(1)

Connecting with your username

(1)

Unlocking (or locking) accounts

(1)

Resetting passwords

(1)

Setting up the server to communicate

(1)

The Basics of PL/SQL Programs

(3)

Writing a simple program

(2)

Creating stored procedures

(1)

Passing parameters to procedures

(1)

Examining the Sample Data

(2)

The Scott/Tiger schema

(1)

The Human Resources (HR) and Order Entry (OE) schemas

(1)

Part II: Getting Started with PL/SQL

(124)

Laying the Groundwork: PL/SQL Fundamentals

(44)

PL/SQL As a Programming Language

(1)

Anonymous PL/SQL Blocks

(1)

Introducing the Lexical Set of Elements

(3)

Identifiers

(1)

Delimiters

(1)

Literals

(1)

Comments

(1)

Working with Constants and Variables

(7)

Declaring variables

(2)

Assigning values to variables

(1)

Literals as variable values

(2)

Understanding the scope of variables

(2)

Building Expressions with Operators

(3)

Running Anonymous Blocks of Code

(3)

Identifying common mistakes

(1)

Spotting compilation errors

(1)

Recognizing semicolon-related errors

(2)

Creating Reusable PL/SQL Code

(8)

Wrapping a task into a procedure

(2)

Returning values with functions

(2)

Parameters of subprograms

(4)

Storing PL/SQL in the Database

(9)

Database procedures and functions

(1)

Packages

(2)

Triggers

(2)

Interpreting and fixing compilation errors

(3)

Checking Out PL/SQL Extras

(9)

Overloading calls

(2)

Resolving calls to subprograms

(2)

Recursion

(2)

Compiler hints and directives

(1)

Built-in packages

(2)

Controlling Program Flow

(20)

Creating Condition Statements

(12)

IF...THEN statements

(1)

IF...ELSE statements

(2)

Case statements

(2)

Comparing with NULL

(4)

Handling conditions

(2)

Looping the Loop

(8)

Simple loops

(2)

Nested loops

(1)

While loop

100

(2)

For loop

102

(3)

Handling Exceptions

105

(22)

Understanding Exception Basics

106

(1)

Adding Exception Handlers to Your Code

107

(3)

Understanding Different Exception Types

110

(1)

Predefined Exceptions in PL/SQL Code

111

(3)

Adding User-Defined Exceptions

114

(4)

Assigning a code to a user-defined exception

115

(1)

Including error messages in user-defined exceptions

116

(2)

Propagation of Exceptions

118

(8)

Seeing propagation of exceptions in action

118

(4)

Handling exceptions without halting the program

122

(2)

Avoiding exceptions raised in declaration part and exception handler

124

(2)

Writing Exceptional Exceptions

126

(1)

PL/SQL and SQL Working Together

127

(36)

Cursors: What They Are and How to Use Them

128

(9)

Returning more than one piece of information

129

(3)

Looping through multiple records

132

(1)

Placing cursors in nested loops

133

(1)

Passing parameters to cursors

134

(3)

Knowing Where to Declare Cursors

137

(5)

Defining cursors in the header of the program unit

138

(1)

Defining cursors in the local PL/SQL block

138

(1)

Defining cursors in the package body

139

(1)

Defining cursors in the package spec

140

(2)

Being Explicitly Smart with Implicit Cursors

142

(2)

Retrieving a single row: The basic syntax

142

(1)

Handling exceptions in implicit cursors

143

(1)

Returning an implicit cursor into a record

143

(1)

Accessing Status Info by Using Cursor Variables

144

(4)

Checking the status of explicit cursors

145

(1)

Checking the status of implicit cursors

146

(2)

Updating Records Fetched from Cursors

148

(2)

Using a simple Update statement

148

(1)

Updating with logical operators

148

(2)

Taking a Shortcut with Cursor for Loops

150

(5)

Comparing Cursor for loops to cursors with the Loop command

150

(2)

When do Cursor for loops simplify exception handling?

152

(1)

When Cursor for loops make your life harder

153

(2)

Knowing what record is processing

155

(1)

Referencing Functions in SQL

155

(8)

Important facts to remember

157

(3)

Getting good performance with functions

160

(3)

Part III: Standards and Structures

163

(56)

Putting Your Code in the Right Place

165

(22)

Putting Code in the Database

165

(18)

Managing code

166

(1)

Packaging code in the database

166

(8)

Placing triggers on tables

174

(3)

Building Instead of trigger views

177

(5)

Advantages of putting code in the database

182

(1)

Disadvantages of putting code in the database

182

(1)

Putting Code in the Application Server (Middle-Tier Approach)

183

(2)

Advantages of the middle-tier approach

184

(1)

Disadvantages of the middle-tier approach

184

(1)

Placing code in the view layer

185

(1)

Where Should You Place the Business Logic?

185

(2)

Creating Naming Standards

187

(14)

What's in a Naming Standard?

187

(2)

Oracle database influences

188

(1)

Java influences

188

(1)

Modern application development tools and their influences

189

(1)

Setting Naming Standards for Common Code Elements

189

(10)

Basic objects

190

(1)

Variables

191

(2)

Program units: Procedures, packages, functions, triggers

193

(1)

Parameters in code objects

194

(2)

Exceptions

196

(1)

User-defined datatypes

196

(1)

Collections

197

(1)

Filenames

198

(1)

Making Sure Your Organization Follows Standards

199

(2)

Creating Coding Standards

201

(18)

Why Standards Are Important

201

(1)

Universal Truths

202

(12)

Don't hard-code any constant value

202

(3)

Don't make your program units too big or too small

205

(1)

Put each data element on its own line

206

(1)

Too many comments are much better than too few comments

207

(3)

Avoid global variables

210

(1)

Indent carefully

210

(1)

Be careful with capitalization

211

(1)

Use generic variable datatype declarations

211

(2)

Limit line length

213

(1)

Use explicit data conversion for dates

213

(1)

Use synonyms

213

(1)

Developing SQL Code Consistently

214

(5)

Using a new line

214

(1)

Using explicit column lists

214

(1)

Prefixing (and suffixing) column names from multiple tables

215

(1)

Giving columns aliases

216

(1)

Using parentheses in complex mathematical and logical expressions

216

(1)

Using white space intelligently

217

(1)

Writing save exception handlers

217

(1)

Packaging stored program units

217

(2)

Part IV: PL/SQL Data Manipulations

219

(70)

Basic Datatypes

221

(32)

Introducing the Main Datatype Groups

221

(1)

Working with Numeric Datatypes

222

(7)

Using the Number datatype

222

(3)

Boosting performance with Binary_Integer

225

(1)

Using Binary_Float and Binary_Double for complex calculations

226

(2)

Handling numeric datatypes in built-in functions

228

(1)

Keeping Track of Date and Time

229

(12)

Selecting the info you want from Date

229

(5)

Using Timestamp

234

(1)

Using Timestamp with Time Zone

235

(1)

Storing elapsed time with Interval

236

(1)

Working with dates and built-in functions

237

(4)

Storing Logical Values with Boolean

241

(1)

Processing Characters and Strings

242

(11)

Limiting variable length with Char versus Varchar2

242

(2)

Useful character built-in functions

244

(6)

Extending your options with regular expressions

250

(3)

Advanced Datatypes

253

(36)

Handling Large Objects in the Database

253

(2)

Using internal large objects (Clob, Blob)

254

(1)

Creating pointers with external large objects

254

(1)

Working with Large Objects

255

(4)

Populating BFILE

255

(1)

Loading data to the Clob by using Bfile

256

(1)

Loading a page to a Blob

257

(1)

Performing basic string operations on CLOBs

258

(1)

Keeping Code Consistent with User-Defined Subtypes

259

(1)

Defining Your Own Datatypes

260

(11)

Records

261

(6)

Object types

267

(4)

Grouping Sets of Data into Collections

271

(12)

Using Varrays

272

(3)

Nesting variable data sets in tables

275

(5)

Associative arrays (index-by tables)

280

(3)

Speeding Up Data Collection with Bulk Operations

283

(6)

Using the Bulk Collect command

284

(2)

Adding a limit to Bulk Collect

286

(3)

Part V: Taking PL/SQL to the Next Level

289

(66)

Transaction Control

291

(22)

Using Transactions to Maintain Data Consistency

292

(1)

Committing or Rolling Back Changes to the Database

293

(5)

Firing explicit and implicit commits

294

(1)

Rolling back changes

294

(3)

Knowing when to use implicit rollbacks

297

(1)

Resolving deadlocks

298

(1)

Autonomous Transactions

298

(15)

Setting up the syntax for an autonomous transactions

299

(1)

Handling auditing and security with autonomous transactions

300

(2)

Autonomous transaction features

302

(6)

Applying autonomous transactions to other real-world situations

308

(5)

Dynamic SQL and PL/SQL

313

(22)

Taking Advantage of Dynamic SQL

313

(1)

A Simple Execute Immediate

314

(2)

Building SQL on the Fly

316

(9)

Improve performance with bind variables

317

(7)

Return information using cursor variables

324

(1)

Building DDL on the Fly

325

(2)

Using Quoted Strings with Dynamic SQL

327

(1)

Working with Advanced Datatypes

328

(7)

Using Bulk Collect with dynamic SQL

329

(1)

Dynamic Open...For

330

(5)

PL/SQL Best Practices

335

(20)

Why Are Best Practices Important?

335

(1)

Laying the Groundwork for Good Coding

336

(2)

Understanding the big picture

336

(1)

Communicating effectively

337

(1)

Creating a code specification

337

(1)

Writing Code with Best Practices in Mind

338

(5)

Stub out your code

338

(1)

Check the architecture as you go

339

(1)

Prove code works with test cases

340

(1)

Use code libraries

340

(1)

Keep the code maintainable

341

(1)

Don't forget about performance

341

(1)

Compile as you go

341

(1)

Debug the timesaving way

342

(1)

Testing Your Code

343

(6)

What are you testing?

345

(1)

Creating a testing architecture

345

(1)

Performance and load testing

345

(1)

Tuning performance

346

(2)

``Good enough is best''

348

(1)

Coding the Agile Way

349

(3)

Working together in Agile teams

349

(1)

Programming in pairs

350

(1)

Delivering code quickly

350

(1)

Test first

351

(1)

Keeping Up-to-Date with Oracle

352

(3)

Conventional wisdom isn't always right

352

(1)

Buy books

353

(1)

Go to conferences

353

(1)

Join your local Oracle user group

354

(1)

Use online resources

354

(1)

Part VI: The Part of Tens

355

(42)

Ten PL/SQL Tips

357

(20)

Use Select into Correctly

357

(3)

Don't Assume Column Order in DML Statements

360

(2)

Use the Nocopy Command

362

(2)

Be Careful of Function Calls That Impact Performance

364

(2)

Beware of Implicit Commits

366

(3)

Use Only One Return Statement per Function

369

(1)

Save Debugging Time with When Others

370

(2)

Know When to Pass Parameters or Use Global Variables

372

(1)

Use Implicit Cursors When Appropriate

373

(2)

Remember That Dates Are Numbers

375

(2)

Ten Common Mistakes to Avoid in PL/SQL

377

(20)

Catching an Exception with When Others Then Null

377

(2)

Forgetting to Handle Null Values

379

(1)

Creating Unintended Boolean Expressions

380

(2)

Forgetting to Close an Explicit Cursor

382

(2)

Starting Endless Loops

384

(2)

Reinventing the Wheel

386

(2)

Converting Datatypes Implicitly

388

(3)

Cutting and Pasting Code

391

(2)

Ignoring Code Readability

393

(2)

Assuming Code Doesn't Need Comments

395

(2)

Index

397

Please wait while the item is added to your bag...

FREE SHIPPING

Amazon no longer offers textbook rentals. We do!

Oracle PL / SQL For Dummies

Summary

Author Biography

Table of Contents

Supplemental Materials