Oracle Database 11g SQL

Name: Oracle Database 11g SQL
Brand: McGraw-Hill
Price: 92.19 EUR
Availability: OnlineOnly

Jason Price(Autor*in)

McGraw-Hill (Verlag)

1. Auflage

Erschienen am 5. November 2007

738 Seiten

E-Book

PDF mit Adobe-DRM

Systemvoraussetzungen

978-0-07-159613-8 (ISBN)

92,19 €inkl. 7% MwSt.

Systemvoraussetzungen

für PDF mit Adobe-DRM

E-Book Einzellizenz

Als Download verfügbar

Beschreibung

Weitere Details

Weitere Ausgaben

Inhalt

Cover Page
Oracle Database 11g SQL
Copyright Page
Dedication Page
About the Technical Editor
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

Systemvoraussetzungen

Als PDF speichern Als Link merken