Oracle Database 11g SQL | Oracle Press

Oracle Database 11g SQL

Authors: Jason Price

Published: November 2007

eISBN: 9780071596138 0071596135 | ISBN: 9780071498500

Contents

Acknowledgments

Introduction

1 Introduction

What Is a Relational Database?

Introducing the Structured Query Language (SQL)

Using SQL*Plus

Starting SQL*Plus

Starting SQL*Plus from the Command Line

Performing a SELECT Statement Using SQL*Plus

SQL Developer

Creating the Store Schema

Running the SQL*Plus Script to Create the Store Schema

Data Definition Language (DDL) Statements Used to Create the Store Schema

Adding, Modifying, and Removing Rows

Adding a Row to a Table

Modifying an Existing Row in a Table

Removing a Row from a Table

The BINARY_FLOAT and BINARY_DOUBLE Types

Benefits of BINARY_FLOAT and BINARY_DOUBLE

Using BINARY_FLOAT and BINARY_DOUBLE in a Table

Special Values

Quitting SQL*Plus

Introducing Oracle PL/SQL

Summary

2 Retrieving Information from Database Tables

Performing Single Table SELECT Statements

Retrieving All Columns from a Table

Specifying Rows to Retrieve Using the WHERE Clause

Row Identifiers

Row Numbers

Performing Arithmetic

Performing Date Arithmetic

Using Columns in Arithmetic

Using Column Aliases

Combining Column Output Using Concatenation

Null Values

Displaying Distinct Rows

Comparing Values

Using the SQL Operators

Using the LIKE Operator

Using the IN Operator

Using the BETWEEN Operator

Using the Logical Operators

Operator Precedence

Sorting Rows Using the ORDER BY Clause

Performing SELECT Statements That Use Two Tables

Using Table Aliases

Cartesian Products

Performing SELECT Statements That Use More than Two Tables

Join Conditions and Join Types

Non-equijoins

Outer Joins

Self Joins

Performing Joins Using the SQL/92 Syntax

Performing Inner Joins on Two Tables Using SQL/92

Simplifying Joins with the USING Keyword

Performing Inner Joins on More than Two Tables Using SQL/92

Performing Inner Joins on Multiple Columns Using SQL/92

Performing Outer Joins Using SQL/92

Performing Self Joins Using SQL/92

Performing Cross Joins Using SQL/92

Summary

3 Using SQL*Plus

Viewing the Structure of a Table

Editing SQL Statements

Saving, Retrieving, and Running Files

Formatting Columns

Setting the Page Size

Setting the Line Size

Clearing Column Formatting

Using Variables

Temporary Variables

Defined Variables

Creating Simple Reports

Using Temporary Variables in a Script

Using Defined Variables in a Script

Passing a Value to a Variable in a Script

Adding a Header and Footer

Computing Subtotals

Getting Help from SQL*Plus

Automatically Generating SQL Statements

Disconnecting from the Database and Exiting SQL*Plus

Summary

4 Using Simple Functions

Using Single-Row Functions

Character Functions

Numeric Functions

Conversion Functions

Regular Expression Functions

Using Aggregate Functions

AVG()

COUNT()

MAX() and MIN()

STDDEV()

SUM()

VARIANCE()

Grouping Rows

Using the GROUP BY Clause to Group Rows

Incorrect Usage of Aggregate Function Calls

Using the HAVING Clause to Filter Groups of Rows

Using the WHERE and GROUP BY Clauses Together

Using the WHERE, GROUP BY, and HAVING Clauses Together

Summary

5 Storing and Processing Dates and Times

Simple Examples of Storing and Retrieving Dates

Converting Datetimes Using TO_CHAR() and TO_DATE()

Using TO_CHAR() to Convert a Datetime to a String

Using TO_DATE() to Convert a String to a Datetime

Setting the Default Date Format

How Oracle Interprets Two-Digit Years

Using the YY Format

Using the RR Format

Using Datetime Functions

ADD_MONTHS()

LAST_DAY()

MONTHS_BETWEEN()

NEXT_DAY()

ROUND()

SYSDATE

TRUNC()

Using Time Zones

Time Zone Functions

The Database Time Zone and Session Time Zone

Obtaining Time Zone Offsets

Obtaining Time Zone Names

Converting a Datetime from One Time Zone to Another

Using Timestamps

Using the Timestamp Types

Timestamp Functions

Using Time Intervals

Using the INTERVAL YEAR TO MONTH Type

Using the INTERVAL DAY TO SECOND Type

Time Interval Functions

Summary

6 Subqueries

Types of Subqueries

Writing Single-Row Subqueries

Subqueries in a WHERE Clause

Using Other Single-Row Operators

Subqueries in a HAVING Clause

Subqueries in a FROM Clause (Inline Views)

Errors You Might Encounter

Writing Multiple-Row Subqueries

Using IN with a Multiple-Row Subquery

Using ANY with a Multiple-Row Subquery

Using ALL with a Multiple-Row Subquery

Writing Multiple-Column Subqueries

Writing Correlated Subqueries

A Correlated Subquery Example

Using EXISTS and NOT EXISTS with a Correlated Subquery

Writing Nested Subqueries

Writing UPDATE and DELETE Statements Containing Subqueries

Writing an UPDATE Statement Containing a Subquery

Writing a DELETE Statement Containing a Subquery

Summary

7 Advanced Queries

Using the Set Operators

The Example Tables

Using the UNION ALL Operator

Using the UNION Operator

Using the INTERSECT Operator

Using the MINUS Operator

Combining Set Operators

Using the TRANSLATE() Function

Using the DECODE() Function

Using the CASE Expression

Using Simple CASE Expressions

Using Searched CASE Expressions

Hierarchical Queries

The Example Data

Using the CONNECT BY and START WITH Clauses

Using the LEVEL Pseudo Column

Formatting the Results from a Hierarchical Query

Starting at a Node Other than the Root

Using a Subquery in a START WITH Clause

Traversing Upward Through the Tree

Eliminating Nodes and Branches from a Hierarchical Query

Including Other Conditions in a Hierarchical Query

Using the Extended GROUP BY Clauses

The Example Tables

Using the ROLLUP Clause

Using the CUBE Clause

Using the GROUPING() Function

Using the GROUPING SETS Clause

Using the GROUPING_ID() Function

Using a Column Multiple Times in a GROUP BY Clause

Using the GROUP_ID() Function

Using the Analytic Functions

The Example Table

Using the Ranking Functions

Using the Inverse Percentile Functions

Using the Window Functions

Using the Reporting Functions

Using the LAG() and LEAD() Functions

Using the FIRST and LAST Functions

Using the Linear Regression Functions

Using the Hypothetical Rank and Distribution Functions

Using the MODEL Clause

An Example of the MODEL Clause

Using Positional and Symbolic Notation to Access Cells

Accessing a Range of Cells Using BETWEEN and AND

Accessing All Cells Using ANY and IS ANY

Getting the Current Value of a Dimension Using CURRENTV()

Accessing Cells Using a FOR Loop

Handling Null and Missing Values

Updating Existing Cells

Using the PIVOT and UNPIVOT Clauses

A Simple Example of the PIVOT Clause

Pivoting on Multiple Columns

Using Multiple Aggregate Functions in a Pivot

Using the UNPIVOT Clause

Summary

8 Changing Table Contents

Adding Rows Using the INSERT Statement

Omitting the Column List

Specifying a Null Value for a Column

Including Single and Double Quotes in a Column Value

Copying Rows from One Table to Another

Modifying Rows Using the UPDATE Statement

The RETURNING Clause

Removing Rows Using the DELETE Statement

Database Integrity

Enforcement of Primary Key Constraints

Enforcement of Foreign Key Constraints

Using Default Values

Merging Rows Using MERGE

Database Transactions

Committing and Rolling Back a Transaction

Starting and Ending a Transaction

Savepoints

ACID Transaction Properties

Concurrent Transactions

Transaction Locking

Transaction Isolation Levels

A SERIALIZABLE Transaction Example

Query Flashbacks

Granting the Privilege for Using Flashbacks

Time Query Flashbacks

System Change Number Query Flashbacks

Summary

9 Users, Privileges, and Roles

Users

Creating a User

Changing a User's Password

Deleting a User

System Privileges

Granting System Privileges to a User

Checking System Privileges Granted to a User

Making Use of System Privileges

Revoking System Privileges from a User

Object Privileges

Granting Object Privileges to a User

Checking Object Privileges Made

Checking Object Privileges Received

Making Use of Object Privileges

Synonyms

Public Synonyms

Revoking Object Privileges

Roles

Creating Roles

Granting Privileges to Roles

Granting Roles to a User

Checking Roles Granted to a User

Checking System Privileges Granted to a Role

Checking Object Privileges Granted to a Role

Making Use of Privileges Granted to a Role

Default Roles

Revoking a Role

Revoking Privileges from a Role

Dropping a Role

Auditing

Privileges Required to Perform Auditing

Auditing Examples

Audit Trail Views

Summary

10 Creating Tables, Sequences, Indexes, and Views

Tables

Creating a Table

Getting Information on Tables

Getting Information on Columns in Tables

Altering a Table

Renaming a Table

Adding a Comment to a Table

Truncating a Table

Dropping a Table

Sequences

Creating a Sequence

Retrieving Information on Sequences

Using a Sequence

Populating a Primary Key Using a Sequence

Modifying a Sequence

Dropping a Sequence

Indexes

Creating a B-tree Index

Creating a Function-Based Index

Retrieving Information on Indexes

Retrieving Information on the Indexes on a Column

Modifying an Index

Dropping an Index

Creating a Bitmap Index

Views

Creating and Using a View

Modifying a View

Dropping a View

Flashback Data Archives

Summary

11 Introducing PL/SQL Programming

Block Structure

Variables and Types

Conditional Logic

Loops

Simple Loops

WHILE Loops

FOR Loops

Cursors

Step 1: Declare the Variables to Store the Column Values

Step 2: Declare the Cursor

Step 3: Open the Cursor

Step 4: Fetch the Rows from the Cursor

Step 5: Close the Cursor

Complete Example: product_cursor.sql

Cursors and FOR Loops

OPEN-FOR Statement

Unconstrained Cursors

Exceptions

ZERO_DIVIDE Exception

DUP_VAL_ON_INDEX Exception

INVALID_NUMBER Exception

OTHERS Exception

Procedures

Creating a Procedure

Calling a Procedure

Getting Information on Procedures

Dropping a Procedure

Viewing Errors in a Procedure

Functions

Creating a Function

Calling a Function

Getting Information on Functions

Dropping a Function

Packages

Creating a Package Specification

Creating a Package Body

Calling Functions and Procedures in a Package

Getting Information on Functions and Procedures in a Package

Dropping a Package

Triggers

When a Trigger Fires

Set Up for the Example Trigger

Creating a Trigger

Firing a Trigger

Getting Information on Triggers

Disabling and Enabling a Trigger

Dropping a Trigger

New Oracle Database 11g PL/SQL Features

SIMPLE_INTEGER Type

Sequences in PL/SQL

PL/SQL Native Machine Code Generation

Summary

12 Database Objects

Introducing Objects

Creating Object Types

Using DESCRIBE to Get Information on Object Types

Using Object Types in Database Tables

Column Objects

Object Tables

Object Identifiers and Object References

Comparing Object Values

Using Objects in PL/SQL

The get_products() Function

The display_product() Procedure

The insert_product() Procedure

The update_product_price() Procedure

The get_product() Function

The update_product() Procedure

The get_product_ref() Function

The delete_product() Procedure

The product_lifecycle() Procedure

The product_lifecycle2() Procedure

Type Inheritance

Using a Subtype Object in Place of a Supertype Object

SQL Examples

PL/SQL Examples

NOT SUBSTITUTABLE Objects

Other Useful Object Functions

IS OF()

TREAT()

SYS_TYPEID()

NOT INSTANTIABLE Object Types

User-Defined Constructors

Overriding Methods

Generalized Invocation

Summary

13 Collections

Introducing Collections

Creating Collection Types

Creating a Varray Type

Creating a Nested Table Type

Using a Collection Type to Define a Column in a Table

Using a Varray Type to Define a Column in a Table

Using a Nested Table Type to Define a Column in a Table

Getting Information on Collections

Getting Information on a Varray

Getting Information on a Nested Table

Populating a Collection with Elements

Populating a Varray with Elements

Populating a Nested Table with Elements

Retrieving Elements from Collections

Retrieving Elements from a Varray

Retrieving Elements from a Nested Table

Using TABLE() to Treat a Collection as a Series of Rows

Using TABLE() with a Varray

Using TABLE() with a Nested Table

Modifying Elements of Collections

Modifying Elements of a Varray

Modifying Elements of a Nested Table

Using a Map Method to Compare the Contents of Nested Tables

Using CAST() to Convert Collections from One Type to Another

Using CAST() to Convert a Varray to a Nested Table

Using CAST() to Convert a Nested Table to a Varray

Using Collections in PL/SQL

Manipulating a Varray

Manipulating a Nested Table

PL/SQL Collection Methods

Multilevel Collections

Oracle Database 10g Enhancements to Collections

Associative Arrays

Changing the Size of an Element Type

Increasing the Number of Elements in a Varray

Using Varrays in Temporary Tables

Using a Different Tablespace for a Nested Table’s Storage Table

ANSI Support for Nested Tables

Summary

14 Large Objects

Introducing Large Objects (LOBs)

The Example Files

Large Object Types

Creating Tables Containing Large Objects

Using Large Objects in SQL

Using CLOBs and BLOBs

Using BFILEs

Using Large Objects in PL/SQL

APPEND()

CLOSE()

COMPARE()

COPY()

CREATETEMPORARY()

ERASE()

FILECLOSE()

FILECLOSEALL()

FILEEXISTS()

FILEGETNAME()

FILEISOPEN()

FILEOPEN()

FREETEMPORARY()

GETCHUNKSIZE()

GET_STORAGE_LIMIT()

GETLENGTH()

INSTR()

ISOPEN()

ISTEMPORARY()

LOADFROMFILE()

LOADBLOBFROMFILE()

LOADCLOBFROMFILE()

OPEN()

READ()

SUBSTR()

TRIM()

WRITE()

WRITEAPPEND()

Example PL/SQL Procedures

LONG and LONG RAW Types

The Example Tables

Adding Data to LONG and LONG RAW Columns

Converting LONG and LONG RAW Columns to LOBs

Oracle Database 10g Enhancements to Large Objects

Implicit Conversion Between CLOB and NCLOB Objects

Use of the :new Attribute When Using LOBs in a Trigger

Oracle Database 11g Enhancements to Large Objects

Encrypting LOB Data

Compressing LOB Data

Removing Duplicate LOB Data

Summary

15 Running SQL Using Java

Getting Started

Configuring Your Computer

Setting the ORACLE_HOME Environment Variable

Setting the JAVA_HOME Environment Variable

Setting the PATH Environment Variable

Setting the CLASSPATH Environment Variable

Setting the LD_LIBRARY_PATH Environment Variable

The Oracle JDBC Drivers

The Thin Driver

The OCI Driver

The Server-Side Internal Driver

The Server-Side Thin Driver

Importing the JDBC Packages

Registering the Oracle JDBC Drivers

Opening a Database Connection

Connecting to the Database Using getConnection()

The Database URL

Connecting to the Database Using an Oracle Data Source

Creating a JDBC Statement Object

Retrieving Rows from the Database

Step 1: Create and Populate a ResultSet Object

Step 2: Read the Column Values from the ResultSet Object

Step 3: Close the ResultSet Object

Adding Rows to the Database

Modifying Rows in the Database

Deleting Rows from the Database

Handling Numbers

Handling Database Null Values

Controlling Database Transactions

Performing Data Definition Language Statements

Handling Exceptions

Closing Your JDBC Objects

Example Program: BasicExample1.java

Compile BasicExample1

Run BasicExample1

Prepared SQL Statements

Example Program: BasicExample2.java

The Oracle JDBC Extensions

The oracle.sql Package

The oracle.jdbc Package

Example Program: BasicExample3.java

Summary

16 SQL Tuning

Introducing SQL Tuning

Use a WHERE Clause to Filter Rows

Use Table Joins Rather than Multiple Queries

Use Fully Qualified Column References When Performing Joins

Use CASE Expressions Rather than Multiple Queries

Add Indexes to Tables

Use WHERE Rather than HAVING

Use UNION ALL Rather than UNION

Use EXISTS Rather than IN

Use EXISTS Rather than DISTINCT

Use GROUPING SETS Rather than CUBE

Use Bind Variables

Non-Identical SQL Statements

Identical SQL Statements That Use Bind Variables

Listing and Printing Bind Variables

Using a Bind Variable to Store a Value Returned by a PL/SQL Function

Using a Bind Variable to Store Rows from a REFCURSOR

Comparing the Cost of Performing Queries

Examining Execution Plans

Comparing Execution Plans

Passing Hints to the Optimizer

Additional Tuning Tools

Oracle Enterprise Manager Diagnostics Pack

Automatic Database Diagnostic Monitor

Summary

17 XML and the Oracle Database

Introducing XML

Generating XML from Relational Data

XMLELEMENT()

XMLATTRIBUTES()

XMLFOREST()

XMLAGG()

XMLCOLATTVAL()

XMLCONCAT()

XMLPARSE()

XMLPI()

XMLCOMMENT()

XMLSEQUENCE()

XMLSERIALIZE()

A PL/SQL Example That Writes XML Data to a File

XMLQUERY()

Saving XML in the Database

The Example XML File

Creating the Example XML Schema

Retrieving Information from the Example XML Schema

Updating Information in the Example XML Schema

Summary

A: Oracle Data Types

Oracle SQL Types

Oracle PL/SQL Types

Index