Oracle SQL : A Guide to SQL*Plus Syntax

by Gennick, Jonathan

• ISBN13: 9780596004415
• ISBN10: 0596004419
• Edition: 2nd
• Format: Paperback
• Copyright: 2003-01-01
• Publisher: Oreilly & Associates Inc

Summary

SQLPlus is available at every Oracle site--from the largest data warehouse to the smallest single-user system--and it's a critical tool for virtually every Oracle user. Despite its wide use, few developers and DBAs know how powerful a tool SQLPlus can be.This pocket reference provides quick reference information that will help you use SQLPlus, Oracle's interactive query tool. It summarizes all of the SQLPlus syntax, including the syntax for the Oracle9i release. This book boils down the most vital information from Gennick's best-selling book, "Oracle SQL8Plus: The Definitive Guide", into an accessible summary and works as a vital companion to the larger book. It concisely describes interacting with SQLPlus, selecting data, formatting reports with SQLPlus, and tuning SQL queries. It also contains quick references to the SQL*Plus commands and format elements.The new 2nd edition of ourOracle SQLPlus Pocket Referenceincludes many new features for Oracle9i (e.g., COALESCE function, searched CASE expressions, new table join syntax, partition operations, MERGE statement, and syntax changes in existing SQLPlus statements such as ACCEPT, DESRIBE, HELP, and SET) It also adds sections on basic SQL (a much-requested feature): INSERT, DELETE, UPDATE, SELECT, and transaction management. The pocket reference size is especially appropriate for this particular subject, since Oracle SQL*Plus is used on every possible platform, it's particularly convenient to be able to carry a small book around from office computer to home computer to laptop.

Author Biography

Jonathan Gennick is an O'Reilly & Associates editor specializing in database and programming titles. Prior to joining O'Reilly, Jonathan amassed some 17 years of programming and database management experience. During the latter part of his career he specialized in relational database programming, and more recently in database management. Jonathan got his start with relational database systems in 1990, first working with Ingres, and later with Digital's Rdb software. During that period he developed a fondness for SQL, and for the challenge of applying SQL creatively in ways that leveraged it's set-oriented capabilities. In 1994 Jonathan made the leap to Oracle, and is now often found writing about it. Recent projects include Oracle SQLLoader (O'Reilly & Associates, 2001); Oracle SQLPlus Pocket Reference (O'Reilly & Associates, 2000); Oracle SQL*Plus: The Definitive Guide (O'Reilly & Associates, 1999); More recently, Jonathan has made forays into other database products, coauthoring Transact-SQL Cookbook (O'Reilly & Associates, 2002) and editing Practical PostgreSQL (O'Reilly & Associates, 2002). Jonathan is certified as an Oracle DBA and is a member of MENSA and the Oracle Technology Network. He holds a Bachelor of Arts degree in Information and Computer Science, with a Business Administration minor, from Andrews University in Berrien Springs, Michigan. Jonathan currently resides in Munising, Michigan with his wife Donna and their two children: Jenny and Jeff. Jonathan may be contacted by email at [e-mail], and you can learn more about him personally by visiting his website at http://gennick.com.

Table of Contents

Introduction	1	(1)
Interacting with SQL*Plus	2	(7)
Starting SQL*Plus	2	(4)
Entering Commands	6	(2)
Strings in SQL*Plus Commands	8	(1)
Specifying Filenames	9	(1)
Naming Variables	9	(1)
Selecting Data	9	(19)
The SELECT Statement	10	(2)
Null Values	12	(2)
CASE Expressions	14	(2)
Table Joins (Oracle8i)	16	(1)
Table Joins (Oracle9i)	17	(6)
Summary Queries	23	(3)
Unions	26	(1)
Partition Operations	27	(1)
Inserting Data	28	(2)
Inserting One Row	28	(1)
Inserting the Results of a Query	29	(1)
Multitable Inserts (Oracle9i)	29	(1)
Updating Data	30	(2)
Simple Updates	31	(1)
Noncorrelated Subqueries in the SET Clause	31	(1)
Correlated Subqueries in the SET Clause	32	(1)
Deleting Data	32	(2)
Simple Deletes	32	(1)
Deleting All Rows (TRUNCATE)	33	(1)
Deleting Duplicate Rows	34	(1)
Merging Data (Oracle9i)	34	(2)
Transaction Management	36	(3)
Set Transaction	37	(1)
Savepoint	38	(1)
Commit	38	(1)
Rollback	38	(1)
Formatting Text Reports	39	(6)
Column Headings	39	(1)
Column Formats	40	(1)
Page Width and Length	40	(1)
Page Headers and Footers	41	(1)
Page Breaks	42	(1)
Report Breaks	43	(2)
Tuning SQL	45	(8)
Creating the Plan Table	45	(1)
Explaining a Query	46	(1)
Querying the Plan Table	47	(2)
Using Optimizer Hints	49	(4)
SQL*Plus Format Elements	53	(6)
Formatting Numbers	53	(2)
Formatting Character Strings	55	(1)
Formatting Dates	56	(3)
SQL*Plus Command Reference	59	(48)
Index	107