Advanced SQL functions in Oracle 10g

Richard Walsh Earp

Sikha Saha Bagui

Wordware Publishing, Inc.

Library of Congress Cataloging-in-Publication Data

Earp, Richard, 1940-

Advanced SQL functions in Oracle 10g / by Richard Walsh Earp

and Sikha Saha Bagui.

p. cm.

Includes bibliographical references and index.

ISBN-13: 978-1-59822-021-6

ISBN-10: 1-59822-021-7 (pbk.)

1. SQL (Computer program language) 2. Oracle (Computer file).

I. Bagui, Sikha, 1964-. II. Title.

QA76.73.S67E26 2006

005.13'3--dc22 2005036444

CIP

© 2006, Wordware Publishing, Inc.

All Rights Reserved

2320 Los Rios Boulevard

Plano, Texas 75074

No part of this book may be reproduced in any form or by

any means without permission in writing from

Wordware Publishing, Inc.

Printed in the United States of America

ISBN-13: 978-1-59822-021-6

ISBN-10: 1-59822-021-7

10 9 8 7 6 5 4 3 2 1

0601

Oracle is a registered trademark of Oracle Corporation and/or its affiliates.

Other brand names and product names mentioned in this book are trademarks or service marks of their

respective companies. Any omission or misuse (of any kind) of service marks or trademarks should not be

regarded as intent to infringe on the property of others. The publisher recognizes and respects all marks used by

companies, manufacturers, and developers as a means to distinguish their products.

This book is sold as is, without warranty of any kind, either express or implied, respecting the contents of this

book and any disks or programs that may accompany it, including but not limited to implied warranties for the

book’s quality, performance, merchantability, or fitness for any particular purpose. Neither Wordware Publishing,

Inc. nor its dealers or distributors shall be liable to the purchaser or any other person or entity with respect to

any liability, loss, or damage caused or alleged to have been caused directly or indirectly by this book.

All inquiries for volume purchases of this book should be addressed to Wordware

Publishing, Inc., at the above address. Telephone inquiries may be made by calling:

(972) 423-0090

To my wife, Brenda,

and

my children, Beryl, Rich, Gen, and Mary Jo

R.W.E.

To my father, Santosh Saha, and mother, Ranu Saha,

and

my husband, Subhash Bagui,

and

my sons, Sumon and Sudip,

and

my brother, Pradeep, and nieces, Priyashi and Piyali

S.S.B.

This page intentionally left blank.

Contents

Preface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xi

Acknowledgments ........................ xiii

Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . xv

Chapter 1 Common Oracle Functions: A Function Review.......1

Calling Simple SQL Functions ..................3

Numeric Functions.........................4

Common Numerical Manipulation Functions .......4

Near Value Functions.....................7

Null Value Function . . . . . . . . . . . . . . . . . . . . . 10

Log and Exponential Functions . . . . . . . . . . . . . . 12

Ordinary Trigonometry Functions . . . . . . . . . . . . . 14

Hyperbolic Trig Functions . . . . . . . . . . . . . . . . . 16

String Functions . . . . . . . . . . . . . . . . . . . . . . . . . 18

The INSTR Function . . . . . . . . . . . . . . . . . . . . 18

The SUBSTR Function . . . . . . . . . . . . . . . . . . . 20

The REPLACE Function . . . . . . . . . . . . . . . . . . 23

The TRIM Function . . . . . . . . . . . . . . . . . . . . . 24

Date Functions . . . . . . . . . . . . . . . . . . . . . . . . . . 27

Chapter 2 Reporting Tools in Oracle’s SQL*Plus . . . . . . . . . . . . 31

COLUMN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32

Formatting Numbers. . . . . . . . . . . . . . . . . . . . . . . 35

Scripts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39

Formatting Dates . . . . . . . . . . . . . . . . . . . . . . . . . 41

BREAK . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43

COMPUTE . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45

Remarks in Scripts . . . . . . . . . . . . . . . . . . . . . . . . 48

TTITLE and BTITLE . . . . . . . . . . . . . . . . . . . . . . 49

References . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52

v

Chapter 3 The Analytical Functions in Oracle

(Analytical Functions I). . . . . . . . . . . . . . . . . . . . . . . . . . . 53

What Are Analytical Functions? . . . . . . . . . . . . . . . . 53

The Row-numbering and Ranking Functions . . . . . . . . . 55

The Order in Which the Analytical Function Is

Processed in the SQL Statement . . . . . . . . . . . . . . . . 65

A SELECT with Just a FROM Clause . . . . . . . . . . 66

A SELECT with Ordering . . . . . . . . . . . . . . . . . 66

A WHERE Clause Is Added to the Statement . . . . . . 67

An Analytical Function Is Added to the Statement . . . 67

A Join Is Added to the Statement . . . . . . . . . . . . . 68

The Join Without the Analytical Function . . . . . . 69

Adding Ordering to a Joined Result. . . . . . . . . . 70

Adding an Analytical Function to a Query that

Contains a Join (and Other WHERE Conditions) . . 71

The Order with GROUP BY Is Present . . . . . . . . . . 72

Adding Ordering to the Query Containing the

GROUP BY . . . . . . . . . . . . . . . . . . . . . . . . . . 73

Adding an Analytical Function to the GROUP BY

with ORDER BY Version . . . . . . . . . . . . . . . . . . 74

Changing the Final Ordering after Having Added

an Analytical Function. . . . . . . . . . . . . . . . . . . . 75

Using HAVING with an Analytical Function . . . . . . . 76

Where the Analytical Functions Can be Used in a

SQL Statement . . . . . . . . . . . . . . . . . . . . . . . . . . 77

More Than One Analytical Function May Be Used in

a Single Statement . . . . . . . . . . . . . . . . . . . . . . . . 78

The Performance Implications of Using Analytical

Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80

Nulls and Analytical Functions . . . . . . . . . . . . . . . . . 86

Partitioning with PARTITION_BY. . . . . . . . . . . . . . . 95

A Problem that Uses ROW_NUMBER for a Solution . . . . 96

NTILE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101

RANK, PERCENT_RANK, and CUME_DIST. . . . . . . 105

References . . . . . . . . . . . . . . . . . . . . . . . . . . . . 110

vi

Contents

Chapter 4 Aggregate Functions Used as Analytical Functions

(Analytical Functions II). . . . . . . . . . . . . . . . . . . . . . . . . . 111

The Use of Aggregate Functions in SQL . . . . . . . . . . . 111

RATIO-TO-REPORT. . . . . . . . . . . . . . . . . . . . . . 115

Windowing Subclauses with Physical Offsets in

Aggregate Analytical Functions . . . . . . . . . . . . . . . . 120

An Expanded Example of a Physical Window . . . . . . . . 127

Displaying a Running Total Using SUM as an

Analytical Function . . . . . . . . . . . . . . . . . . . . . . . 131

UNBOUNDED FOLLOWING . . . . . . . . . . . . . . . . 134

Partitioning Aggregate Analytical Functions. . . . . . . . . 135

Logical Windowing . . . . . . . . . . . . . . . . . . . . . . . 137

The Row Comparison Functions — LEAD and LAG . . . . 143

LAG and LEAD Options. . . . . . . . . . . . . . . . . . 146

Chapter 5 The Use of Analytical Functions in Reporting

(Analytical Functions III) . . . . . . . . . . . . . . . . . . . . . . . . . 149

GROUP BY . . . . . . . . . . . . . . . . . . . . . . . . . . . 150

Grouping at Multiple Levels . . . . . . . . . . . . . . . . . . 155

ROLLUP . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 157

CUBE. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 160

GROUPING with ROLLUP and CUBE . . . . . . . . . . . 162

Chapter 6 The MODEL or SPREADSHEET Predicate in

Oracle’s SQL. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 165

The Basic MODEL Clause . . . . . . . . . . . . . . . . . . . 166

Rule 1. The Result Set . . . . . . . . . . . . . . . . . . . 169

Rule 2. PARTITION BY. . . . . . . . . . . . . . . . . . 169

Rule 3. DIMENSION BY . . . . . . . . . . . . . . . . . 170

Rule 4. MEASURES . . . . . . . . . . . . . . . . . . . . 170

RULES that Use Other Columns . . . . . . . . . . . . . . . 174

RULES that Use Several Other Rows to Compute

New Rows . . . . . . . . . . . . . . . . . . . . . . . . . . . . 178

RETURN UPDATED ROWS . . . . . . . . . . . . . . . . . 183

Using Comparison Operators on the LHS . . . . . . . . . . 184

Adding a Summation Row — Using the RHS to

Generate New Rows Using Aggregate Data . . . . . . . . . 186

Summing within a Partition . . . . . . . . . . . . . . . . . . 189

vii

Contents

Aggregation on the RHS with Conditions on the

Aggregate . . . . . . . . . . . . . . . . . . . . . . . . . . . . 191

Revisiting CV with Value Offsets — Using Multiple

MEASURES Values . . . . . . . . . . . . . . . . . . . . . . 193

Ordering of the RHS . . . . . . . . . . . . . . . . . . . . . . 198

AUTOMATIC versus SEQUENTIAL ORDER . . . . . . . 202

The FOR Clause, UPDATE, and UPSERT . . . . . . . . . 206

Iteration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 211

A Square Root Iteration Example . . . . . . . . . . . . 214

References . . . . . . . . . . . . . . . . . . . . . . . . . . . . 221

Chapter 7 Regular Expressions: String Searching and

Oracle 10g . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 223

A Simple Table to Illustrate an RE . . . . . . . . . . . . . . 225

REGEXP_INSTR. . . . . . . . . . . . . . . . . . . . . . . . 226

A Simple RE Using REGEXP_INSTR . . . . . . . . . 230

Metacharacters . . . . . . . . . . . . . . . . . . . . . . . . . 231

Brackets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 237

Ranges (Minus Signs) . . . . . . . . . . . . . . . . . . . . . . 239

REGEXP_LIKE . . . . . . . . . . . . . . . . . . . . . . . . 239

Negating Carets . . . . . . . . . . . . . . . . . . . . . . . . . 241

Bracketed Special Classes . . . . . . . . . . . . . . . . . . . 243

Other Bracketed Classes. . . . . . . . . . . . . . . . . . 246

The Alternation Operator. . . . . . . . . . . . . . . . . . . . 247

Repetition Operators — aka “Quantifiers” . . . . . . . . . . 248

More Advanced Quantifier Repeat Operator

Metacharacters — *, %, and ? . . . . . . . . . . . . . . . . . 251

REGEXP_SUBSTR . . . . . . . . . . . . . . . . . . . . . . 253

Empty Strings and the ? Repetition Character . . . . . 258

REGEXT_REPLACE . . . . . . . . . . . . . . . . . . . . . 259

Grouping . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 261

The Backslash (\) . . . . . . . . . . . . . . . . . . . . . . . . 262

The Backslash as an Escape Character . . . . . . . . . 263

Alternative Quoting Mechanism in Oracle 10g. . . . . . 264

Backreference. . . . . . . . . . . . . . . . . . . . . . . . 265

References . . . . . . . . . . . . . . . . . . . . . . . . . . . . 267

viii

Contents

Chapter 8 Collection and OO SQL in Oracle . . . . . . . . . . . . . . 269

Associative Arrays. . . . . . . . . . . . . . . . . . . . . . . . 270

The OBJECT TYPE — Column Objects . . . . . . . . . . . 273

CREATE a TABLE with the Column Type in It . . . . 274

INSERT Values into a Table with the Column

Type in It . . . . . . . . . . . . . . . . . . . . . . . . . . 275

Display the New Table (SELECT * and SELECT

by Column Name). . . . . . . . . . . . . . . . . . . . . . 275

COLUMN Formatting in SELECT . . . . . . . . . . . 277

SELECTing Only One Column in the Composite . . . . 277

SELECT with a WHERE Clause . . . . . . . . . . . . 278

Using UPDATE with TYPEed Columns. . . . . . . . . 278

Create Row Objects — REF TYPE . . . . . . . . . . . . . . 279

Loading the “row object” Table . . . . . . . . . . . . . . 281

UPDATE Data in a Table of Row Objects . . . . . . . . 283

CREATE a Table that References Our Row Objects. . 284

INSERT Values into a Table that Contains Row

Objects (TCRO). . . . . . . . . . . . . . . . . . . . . . . 284

UPDATE a Table that Contains Row Objects

(TCRO) . . . . . . . . . . . . . . . . . . . . . . . . . . . 285

SELECT from the TCRO — Seeing Row

Addresses . . . . . . . . . . . . . . . . . . . . . . . . . . 286

DEREF (Dereference) the Row Addresses. . . . . 286

One-step INSERTs into a TCRO . . . . . . . . . . . . . 287

SELECTing Individual Columns in TCROs . . . . . . . 288

Deleting Referenced Rows. . . . . . . . . . . . . . . . . 289

The Row Object Table and the VALUE Function . . . 291

Creating User-defined Functions for Column

Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . 292

VARRAYs . . . . . . . . . . . . . . . . . . . . . . . . . . . . 297

CREATE TYPE for VARRAYs . . . . . . . . . . . . . 299

CREATE TABLE with a VARRAY . . . . . . . . . . . 300

Loading a Table with a VARRAY in It — INSERT

VALUEs with Constants . . . . . . . . . . . . . . . . . 301

Manipulating the VARRAY . . . . . . . . . . . . . . . . 302

The TABLE Function . . . . . . . . . . . . . . . . . 303

The VARRAY Self-join . . . . . . . . . . . . . . . . 305

ix

Contents

The THE and VALUE Functions . . . . . . . . . . 306

The CAST Function . . . . . . . . . . . . . . . . . . 308

Using PL/SQL to Create Functions to

Access Elements . . . . . . . . . . . . . . . . . . . . 311

Creating User-defined Functions for VARRAYs. . 320

Nested Tables . . . . . . . . . . . . . . . . . . . . . . . . . . 324

Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 334

References . . . . . . . . . . . . . . . . . . . . . . . . . . . . 335

Chapter 9 SQL and XML . . . . . . . . . . . . . . . . . . . . . . . . . . . 337

What Is XML? . . . . . . . . . . . . . . . . . . . . . . . . . . 338

Displaying XML in a Browser . . . . . . . . . . . . . . . . . 342

SQL to XML . . . . . . . . . . . . . . . . . . . . . . . . . . . 344

Generating XML from “Ordinary” Tables . . . . . . . . 344

XML to SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . 347

References . . . . . . . . . . . . . . . . . . . . . . . . . . . . 355

Appendix A String Functions . . . . . . . . . . . . . . . . . . . . . . . . 357

Appendix B Statistical Functions . . . . . . . . . . . . . . . . . . . . . . 371

Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 392

x

Contents

Preface

Why This Book? Why This Book?

Oracle® 10g has introduced new features into its reper￾toire of SQL instructions that make database queries

more versatile. When programmers use SQL in Oracle,

they inevitably look for easier and new ways to handle

queries. What is needed is a way to introduce SQL

users to the new features of Oracle 10g concisely and

systematically so that database programmers can take

full advantage of the newer capabilities. This book

hopes to meet this need by exploring some common

new SQL features. Each chapter includes numerous

working examples, and Oracle users can run these

examples as they read and work through the book.

Also, many books on Oracle 10g present the language

syntax alone with no in-depth explanation, analysis, or

examples. In this book, we present not only the syntax

for new features and functions, but also a thorough

clarification and breakdown of the different functions,

along with examples of ways they can and should be

used.

Audience and Coverage Audience and Coverage

This book is meant to be used by Oracle professionals

as well as students, but it is not a SQL primer. Readers

of this book are expected to have previously used Ora￾cle, SQL*Plus, and, to some extent, PL/SQL. This book

can be used for individual study or reference, in

advanced Oracle training settings, and in advanced

xi

database classes in schools. It is meant for those famil￾iar with SQL programming since most of the topics

present not only the syntax, queries, and answers, but

also have an analytical programming perspective to

them. This book will allow the Oracle user to use SQL

in new and exciting ways.

This book contains nine chapters. It begins by

reviewing some of the common SQL functions and

techniques to help transition into the newer tools of

Oracle 10g. Chapter 1 reviews common Oracle func￾tions. Chapter 2 covers some common reporting tools

in Oracle’s SQL*Plus. Chapter 3 introduces and dis￾cusses Oracle 10g’s analytical functions, and Chapter 4

discusses Oracle 10g’s aggregate functions that are

used as analytical functions. Chapter 5 looks at the use

of analytical functions in reporting — for example, the

use of GROUP BY, ROLLUP, and CUBE. Chapter 6

discusses the MODEL or SPREADSHEET predicate

in Oracle’s SQL. Chapter 7 covers the new regular

expressions and string functions. Chapter 8 discusses

collections and object-oriented features of Oracle 10g.

Chapter 9 introduces by example the bridges between

SQL and XML, one of the most important topics Ora￾cle professionals are expected to know today.

This book also has two appendices. Appendix A

illustrates string functions with examples, and Appen￾dix B gives examples of some important statistical

functions available in Oracle 10g.

Overall, this book explores advanced new features

of SQL in Oracle 10g from a programmer’s perspective.

The book can be considered a starting point for

research using some of the advanced topics since the

subjects are discussed at length with examples and

sample outputs. Query development is approached

from a logical standpoint, and in many areas perfor￾mance implications of the queries are also discussed.

xii

Preface

Acknowledgments

Our special thanks to the staff at Wordware Pub￾lishing, especially Wes Beckwith, Beth Kohler, Martha

McCuller, and Denise McEvoy.

We would also like to thank President John

Cavanaugh, Dean Jane Halonen, and Provost Sandra

Flake for their inspiration, encouragement, support,

and true leadership. We would also like to express our

gratitude to Dr. Wes Little on the same endeavor. Our

sincere thanks also goes to Dr. Ed Rodgers for his con￾tinuing support and encouragement throughout the

years. We also appreciate Dr. Leonard Ter Haar, chair

of the computer science department, for his advice,

guidance, and support, and encouraging us to complete

this book. Last, but not least, we would like to thank

our fellow faculty members Dr. Jim Bezdek and Dr.

Norman Wilde for their continuous support and

encouragement.

xiii

This page intentionally left blank.