Oracle Database 12c SQL | Oracle Press

Oracle Database 12c SQL

Authors: Jason Price

Published: August 2013

eISBN: 9780071799362 0071799362 | ISBN: 9780071799355

Cover

Title Page

Copyright Page

About the Author

Acknowledgments

Contents at a Glance

Contents

Introduction

Chapter 1: Introduction

What is a Relational Database?

Introducing Structured Query Language (SQL)

Using SQL*Plus

Starting SQL*Plus

Starting SQL*Plus from the Command Line

Performing a SELECT Statement Using SQL*Plus

Using SQL Developer

Creating the Store Schema

Examining the Script

Running the Script

Examining the Store Data Definition Language Statements

Adding, Modifying, and Removing Rows

Adding a Row to a Table

Modifying an Existing Row in a Table

Removing a Row from a Table

Connecting to and Disconnecting from a Database

Quitting SQL*Plus

Introducing Oracle PL/SQL

Summary

Chapter 2: Retrieving Information from Database Tables

Performing Single Table SELECT Statements

Retrieving All Columns from a Table

Limiting Rows to Retrieve Using the WHERE Clause

Row Identifiers

Row Numbers

Performing Arithmetic

Performing Date Arithmetic

Using Columns in Arithmetic

Arithmetic Operator Precedence

Using Column Aliases

Combining Column Output Using Concatenation

Null Values

Displaying Distinct Rows

Comparing Values

Using the Not Equal Operator

Using the Greater Than Operator

Using the Less Than or Equal to Operator

Using the ANY Operator

Using the ALL Operator

Using the SQL Operators

Using the LIKE Operator

Using the IN Operator

Using the BETWEEN Operator

Using the Logical Operators

Using the AND Operator

Using the OR Operator

Logical 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

Chapter 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

Chapter 4: Using Simple Functions

Types of 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

Chapter 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

Chapter 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

Using Subquery Factoring

Summary

Chapter 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 Recursive Subquery Factoring to Query Hierarchical Data

Using the ROLLUP and CUBE 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 CROSS APPLY and OUTER APPLY

CROSS APPLY

OUTER APPLY

Using LATERAL

Summary

Chapter 8: Analyzing Data

Using 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

Performing Top-N Queries

Using the FETCH FIRST Clause

Using the OFFSET Clause

Using the PERCENT Clause

Using the WITH TIES Clause

Finding Patterns in Data

Finding V-Shaped Data Patterns in the all_sales2 Table

Finding W-Shaped Data Patterns in the all_sales3 Table

Finding V-Shaped Data Patterns in the all_sales3 Table

Summary

Chapter 9: Changing Table Contents

Adding Rows Using the INSERT Statement

Omitting the Column List

Specifying a Null Value for a Column

Including Quote Marks in a Column Value

Copying Rows from One Table to Another

Modifying Rows Using the UPDATE Statement

Returning an Aggregate Function Value Using 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

Chapter 10: Users, Privileges and Roles

A Very Short Introduction to Database Storage

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

Creating Synonyms

Creating 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

Enabling and Disabling Roles

Revoking a Role

Revoking Privileges from a Role

Dropping a Role

Auditing

Privileges Required to Perform Auditing

Auditing Examples

Audit Trail Views

Summary

Chapter 11: 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

Using the BINARY_FLOAT and BINARY_DOUBLE Types

Using DEFAULT ON NULL Columns

Using Visible and Invisible Columns in a Table

Sequences

Creating a Sequence

Retrieving Information on Sequences

Using a Sequence

Populating a Primary Key Using a Sequence

Specifying a Default Column Value Using a Sequence

Using Identity Columns

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

Using Visible and Invisible Columns in a View

Flashback Data Archives

Summary

Chapter 12: 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

Setting up the Example Trigger

Creating a Trigger

Firing a Trigger

Getting Information on Triggers

Disabling and Enabling a Trigger

Dropping a Trigger

Additional PL/SQL Features

SIMPLE_INTEGER Type

Sequences in PL/SQL

PL/SQL Native Machine Code Generation

WITH Clause

Summary

Chapter 13: Database Objects

Introducing Objects

Running the Script to Create the Object Schema

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

Running the Script to Create the Second Object Schema

Inheriting Attributes

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

Running the Script to Create the Third Object Schema

Inheriting Attributes

Summary

Chapter 14: Collections

Introducing Collections

Running the Script to Create the Collection Schema

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

Using PL/SQL Collection Methods

Creating and Using Multilevel Collections

Running the Script to Create the Second Collection Schema

Using Multilevel Collections

Oracle Database 10g Enhancements to Collections

Running the Script to Create the Third Collection Schema

Creating 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

Chapter 15: 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()

GETLENGTH()

GET_STORAGE_LIMIT()

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

Oracle Database 12c Enhancement to Large Objects

Summary

Chapter 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

When to Create a B-Tree Index

When to Create a Bitmap Index

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

Automatic Database Diagnostic Monitor

SQL Tuning Advisor

SQL Access Advisor

SQL Performance Analyzer

Database Replay

Real-Time SQL Monitoring

SQL Plan Management

Summary

Chapter 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

Appendix: Oracle Data Types

Oracle SQL Types

Oracle PL/SQL Types

Index