Oracle Database 10g PL/SQL 101 / Edition 1

by Christopher Allen

Overview

Publisher's Note: Products purchased from Third Party sellers are not guaranteed by the publisher for quality, authenticity, or access to any online entitlements included with the product.

Get The Ins-and-Outs of SQL and PL/SQL From an Oracle Expert

Perform database functions quickly and easily using the comprehensive information in this step-by-step guide. Oracle professional Christopher Allen begins with database basics and progresses to plain-language explanations and real-world examples of SQL statements and PL/SQL programs. You’ll learn how to manage, query, and manipulate data, use SQL*Plus, create SQL functions, and write practical PL/SQL applications. Review questions, chapter summaries, and hands-on projects will have you understanding and exploiting the powerful features of Oracle Database 10g in no time.

• Create tables, rows, records, columns, and fields
• Store text, numbers, dates, pictures, and soundfiles in tables and columns
• Define, manipulate, retrieve, and delete data, using SQL commands
• Select, sort, and filter records with SQL functions and procedures
• Write SQL commands to declare variables using anchored types
• Use SAVEPOINT and ROLLBACK commands to ensure multilevel undo
• Perform time-based and hierarchical data queries
• Edit and reuse commands in script files, using the SQL*Plus program
• Use PL/SQL views, sequences, procedures, functions, and packages
• Use triggers to automate auditing, security, and maintenance tasks

Product Details

ISBN-13:	9780072255409
Publisher:	McGraw Hill LLC
Publication date:	08/10/2004
Series:	Oracle Press
Pages:	416
Product dimensions:	7.30(w) x 9.00(h) x 0.86(d)

About the Author

Christopher Allen (Santa Monica, CA) has been a database professional for almost 20 ears. He is an OCP DBA and an OCP Application Developer and has worked on numerous Oracle Financials implementations. In addition, he has taught several hundred computer classes to adults, resulting in extensive experience communicating technical information in ways that are easy to absorb and easy to remember. His published works include the Oracle Certified Professional Application Developer Exam Guide (co-author, Oracle Press), Hard Disk Smarts (John Wiley Publishing), and four books on Lotus (Que). He also served as technical editor for Microsoft's DOS 5 and DOS 6 manuals. He is currently employed at Metier, an Oracle partner company that provides consulting services in the Los Angeles area.

Table of Contents

	Acknowledgments	xiii
	Introduction	xv
Part I	Database Basics
1	Introduction to Databases	3
	What Exactly Is a Database?	4
	How Is a Database Different from a Spreadsheet?	7
	How Will Knowing This Help You?	11
	History of SQL	12
	SQL Command Categories	13
	Chapter Summary	15
	Chapter Questions	16
	Answers to Chapter Questions	18
2	Storing and Retrieving Data: The Basics	21
	Putting Your Toe in the Water	22
	Creating Tables	26
	Inserting Data--Additional Techniques	41
	Viewing Data--Additional Techniques	46
	Chapter Summary	54
	Chapter Questions	56
	Answers to Chapter Questions	58
3	Advanced Data Manipulation	61
	Limit Which Records You Select	62
	Change the Order of Records	72
	Show Only Unique Values	75
	Select from DUAL	78
	Modify Data In a Table	78
	Remove Records from a Table	81
	Transaction Control	83
	Chapter Summary	89
	Chapter Questions	90
	Answers to Chapter Questions	92
4	Controlling SQL*Plus	93
	Edit Prior Commands	94
	Copy and Paste	99
	Clear the SQL*Plus Screen	102
	Customize the SQL*Plus Environment	103
	Produce More Readable Output	106
	Spool Output to Disk	113
	SQL Script Files	114
	Chapter Summary	118
	Chapter Questions	119
	Answers to Chapter Questions	120
Part II	Advanced SQL
5	SQL Functions	123
	Commonly Used Single-Row Functions	125
	Commonly Used Group Functions	174
	Chapter Summary	179
	Chapter Questions	180
	Answers to Chapter Questions	182
6	Using Indexes and Constraints	183
	Indexes	186
	Ensure Data Integrity: Constraints	195
	Relationships Between Tables	211
	Write Subqueries	230
	Chapter Summary	235
	Chapter Questions	236
	Answers to Chapter Questions	238
7	Other Useful Oracle Techniques	239
	Transfer Data Between Tables	244
	Rename Tables	253
	Alter a Table's Structure	253
	Views	260
	Other Database Objects	264
	Chapter Summary	273
	Chapter Questions	274
	Answers to Chapter Questions	277
Part III	Create Programs Using PL/SQL
8	Introduction to PL/SQL	281
	What Is PL/SQL?	282
	Structure of a PL/SQL Block	289
	Create a Simple PL/SQL Procedure	293
	PL/SQL Variables and Constants	296
	Control Structures in PL/SQL	300
	Error Handling	313
	Chapter Summary	319
	Chapter Questions	319
	Answers to Chapter Questions	321
9	More PL/SQL Tools	323
	Coding Conventions	327
	More on PL/SQL and Oracle Server Interaction	329
	Timing Operations	341
	PL/SQL Packages	345
	Triggers	351
	XML	359
	Chapter Summary	359
	Chapter Questions	360
	Answers to Chapter Questions	361
	Glossary of Common Oracle Terms	363
	Index	375