intTypePromotion=1
zunia.vn Tuyển sinh 2024 dành cho Gen-Z zunia.vn zunia.vn
ADSENSE

Oracle9i : The Complete Reference

Chia sẻ: Phung Tuyet | Ngày: | Loại File: PDF | Số trang:0

79
lượt xem
2
download
 
  Download Vui lòng tải xuống để xem tài liệu đầy đủ

The most reknowned Oracle Resourse- Fully Updated for Oracle 9i. Master all the powerful features of Oracle9i using the exclusive information inside this indispensable resource. Get comprehensive information on all the features of Oracle 9i. Written by best-selling authors and Oracle gurus Kevin Loney, George Koch, and the experts at TUSC, Oracle9I: The Complete Reference covers critical relational, object-relational, and Web database concepts, as well as SQL, SQL*PLUS, PL/SQL, Java, JDBC, SQLJ, and XML programming. You'll also get full details on database administration techniques- and much more. ...

Chủ đề:
Lưu

Nội dung Text: Oracle9i : The Complete Reference

  1. Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Front Matter ORACLE Color profile: Generic CMYK printer profile Composite Default screen Blind Folio FM:i Oracle9i: The Complete Reference Kevin Loney George Koch And the Experts at TUSC McGraw-Hill/Osborne New York Chicago San Francisco Lisbon London Madrid Mexico City Milan New Delhi San Juan Seoul Singapore Sydney Toronto P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp Friday, July 19, 2002 4:10:30 PM
  2. Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Front Matter ORACLE Color profile: Generic CMYK printer profile Composite Default screen Blind Folio FM:ii McGraw-Hill/Osborne 2600 Tenth Street Berkeley, California 94710 U.S.A. To arrange bulk purchase discounts for sales promotions, premiums, or fund-raisers, please contact McGraw-Hill/Osborne at the above address. For information on translations or book distributors outside the U.S.A., please see the International Contact Information page immediately following the index of this book. Oracle 9i: The Complete Reference Copyright © 2002 by The McGraw-Hill Companies, Inc. (Publisher). All rights reserved. Printed in the United States of America. Except as permitted under the Copyright Act of 1976, no part of this publication may be reproduced or distributed in any form or by any means, or stored in a database or retrieval system, without the prior written permission of Publisher. Oracle is a registered trademark and Oracle9i is a trademark or registered trademark of Oracle Corporation. 1234567890 DOC DOC 0198765432 Book p/n 0-07-222707-9 and CD p/n 0-07-222708-7 parts of ISBN 0-07-222521-1 Publisher Copy Editor Brandon A. Nordin Margaret Berson Vice President & Associate Publisher Proofreader Scott Rogers Cheryl Abel Acquisitions Editor Indexer Lisa McClain James Minkin Project Editor Computer Designers LeeAnn Pickrell Tabitha M. Cagan, Lucie Ericksen Acquisitions Coordinator Illustrators Athena Honore Michael Mueller, Lyssa Wald Technical Editor Cover Series Design Bob Bryla Damore Johann Design, Inc. This book was composed with Corel VENTURA™ Publisher. Information has been obtained by Publisher from sources believed to be reliable. However, because of the possibility of human or mechanical error by our sources, Publisher, or others, Publisher does not guarantee to the accuracy, adequacy, or completeness of any information included in this work and is not responsible for any errors or omissions or the results obtained from the use of such information. Oracle Corporation does not make any representations or warranties as to the accuracy, adequacy or completeness of any information contained in this Work, and is not responsible for any errors or omissions. P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp Friday, July 19, 2002 4:10:31 PM
  3. Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Front Matter ORACLE Color profile: Generic CMYK printer profile Composite Default screen Blind Folio FM:iii To my parents, and to Sue, Emily, Rachel, and Jane —K.L. To Elwood Brant, Jr. (Woody), 1949–1990 —G.K. P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp Friday, July 19, 2002 4:10:31 PM
  4. Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Front Matter ORACLE Color profile: Generic CMYK printer profile Composite Default screen Blind Folio FM:iv About the Authors Kevin Loney is a senior management technical consultant with TUSC (http://www.tusc.com), an Oracle-focused consultancy headquartered in Chicago. He is an expert in the administration, tuning, security, recovery, design, and development of Oracle databases and applications. An Oracle DBA and developer since 1987, he is the primary author of numerous books, including Oracle9i DBA Handbook, Oracle9i Instant Scripts, and Oracle8 Advanced Tuning and Administration, all published by Oracle Press. He is a frequent presenter at local and international Oracle user groups. George Koch is a leading authority on relational database applications. A popular speaker and widely published author, he is also the creator of THESIS, the securities trading, accounting, and portfolio management system that was the first major commercial applications product in the world to employ a relational database (Oracle) and provide English language querying to its users. He is a former senior vice president of Oracle Corporation. P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp Friday, July 19, 2002 4:10:31 PM
  5. Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Chapter 55 ORACLE Color profile: Generic CMYK printer profile Composite Default screen Blind Folio 55:v Contents At a Glance PART I Critical Database Concepts 1 Sharing Knowledge and Success . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 2 The Dangers in a Relational Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15 3 The Basic Parts of Speech in SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41 4 The Basics of Object-Relational Databases ...................................... 69 5 Introduction to Web-Enabled Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83 PART II SQL and SQL*PLUS 6 Basic SQL*PLUS Reports and Commands . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91 7 Getting Text Information and Changing It . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113 8 Playing the Numbers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 141 9 Dates: Then, Now, and the Difference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 165 10 Conversion and Transformation Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 193 11 Grouping Things Together . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 205 12 When One Query Depends upon Another . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 219 13 Some Complex Possibilities .................................................. 237 14 Building a Report in SQL*PLUS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 255 15 Changing Data: insert, update, merge, and delete . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 279 16 Advanced Use of Functions and Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 297 17 DECODE and CASE: if, then, and else in SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 311 18 Creating, Dropping, and Altering Tables and Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 325 19 By What Authority? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 355 20 Changing the Oracle Surroundings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 379 v P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp Friday, July 19, 2002 4:10:32 PM
  6. Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Front Matter ORACLE Color profile: Generic CMYK printer profile Composite Default screen Blind Folio FM:vi vi Oracle9i: The Complete Reference 21 Using SQL*Loader to Load Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 393 22 Accessing Remote Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 409 23 Using Materialized Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 423 24 Using Oracle Text for Text Searches . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 447 25 Using External Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 465 26 Using Flashback Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 479 PART III PL/SQL 27 An Introduction to PL/SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 489 28 Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 509 29 Procedures, Functions, and Packages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 529 PART IV Object-Relational Databases 30 Implementing Types, Object Views, and Methods . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 551 31 Collectors (Nested Tables and Varying Arrays) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 567 32 Using Large Objects ........................................................ 581 33 Advanced Object-Oriented Concepts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 607 PART V Java in Oracle 34 An Introduction to Java . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 627 35 JDBC and SQLJ Programming . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 645 36 Java Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 663 PART VI Hitchhiker’s Guides 37 The Hitchhiker’s Guide to the Oracle9i Data Dictionary . . . . . . . . . . . . . . . . . . . . . . . . . . . 673 38 The Hitchhiker’s Guide to the Oracle Optimizer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 721 39 The Hitchhiker’s Guide to Oracle9iAS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 769 40 The Hitchhiker’s Guide to Database Administration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 791 41 The Hitchhiker’s Guide to XML in Oracle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 827 PART VII Alphabetical Reference Alphabetical Reference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 843 P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp Friday, July 19, 2002 4:10:32 PM
  7. Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Front Matter ORACLE Color profile: Generic CMYK printer profile Composite Default screen Blind Folio FM:vii Acknowledgments his book is dedicated to my family, who have allowed me the time to write it. T Thank you for your patience and support and love. Beyond that, this book is dedicated to the memory of two people: Stephen Jay Gould and Matthew Horning. Stephen Jay Gould inspired me to be a technical writer—his steady growth as a writer and his clarity of thought and expression made me believe this kind of writing was a possibility. He passed away as this book was being finished, and the world is poorer for his passing. Matthew Horning was an Oracle DBA I worked with for several weeks during the summer of 2001 on the upper floors of #1 World Trade Center in New York City. He had the misfortune of being in the office early on 9/11. His coworkers were inspiring to work with during the recovery efforts that followed. In his obituary, Matt’s family asked that donations in his memory be given to Heifer International (http://www.heifer.org). If an act of compassion can come out of such destruction, then there may always be hope. As Gould noted, “Ordinary kindness trumps paroxysmal evil by at least a million events to one.” This book is the product of many hands, and countless hours from many people. My thanks go out to all those who helped, whether through their comments, feedback, edits, or suggestions. For additional information about the book, see the publisher’s site (http://www.osborne.com) and my site (http://www.kevinloney.com). Additional articles and presentations can be found on the company site at http://www.tusc.com. Thanks to all of my colleagues at TUSC: s To the contributors and reviewers there, including Brad Brown, Jay Urban, and Mike Holder. s To the exemplary management, including Jake Van der Vort, Rich Niemiec, Joe Trezzo, Brad Brown, and others. It’s a delight to work with an executive team who understands the requirements of this kind of undertaking and who shares a commitment to professional altruism. Thanks to the rest of the management team there who actively pursue the professional traits TUSC values. s To my peers at TUSC, including Mike Ault, Bill Callahan, Patrick Callahan, Holly Clawson, Judy Corley, Mark Greenhalgh, Andy Hamilton, Mike Killough, Allen Peterson, Randy Swanson, Bob Taylor, Bob Yingst, and many others for their insights and contributions. vii P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp Friday, July 19, 2002 4:10:32 PM
  8. Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Front Matter ORACLE Color profile: Generic CMYK printer profile Composite Default screen Blind Folio FM:viii viii Oracle9i: The Complete Reference Special thanks to Bob Bryla, who served as technical editor for this edition. His thoroughness, corrections, and suggestions were greatly appreciated. Imagine being a technical editor on a 1400 page book covering such a range of content and you can begin to appreciate Bob’s task— then do it while working on my schedule! Thanks to my colleagues and friends, including Eyal Aronoff, John Beresniewicz, Steve Bobrowski, Rachel Carmichael, Steven Feuerstein, Mike McDonnell, Marlene Theriault, Mike Janesch, Craig Warman, and Vinny Smith. This book has benefited from the knowledge they have shared, and I have benefited from their friendship and guidance. Thanks to the folks at McGraw-Hill/Osborne who guided this product through its stages: Scott Rogers, LeeAnn Pickrell, Athena Honore, Lisa McClain, and Jeremy Judson, and the others at Osborne with whom I never directly worked. Thanks also to the Oracle component of Oracle Press. This book would not have been possible without the earlier excellent work of George Koch and Robert Muller. Thanks to the writers and friends along the way: Jerry Gross; Jan Riess; Robert Meissner; Marie Paretti; Br. Declan Kane, CFX; Br. William Griffin, CFX; Chris O’Neill; Cheryl Bittner; Bill Fleming; and Mike Restuccia. Thanks to the First State Oracle User Group board (Pete Silva, Phil Stewart, Earl Shaffer, and Lori Kaupas) for its support (http://www.fsoug.org). Special thanks to Sue, Emily, Rachel, Jane, and the rest of the home team. As always, this has been a joint effort. —Kevin Loney P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp Friday, July 19, 2002 4:10:32 PM
  9. Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Front Matter ORACLE Color profile: Generic CMYK printer profile Composite Default screen Blind Folio FM:ix Preface The Intriguing History of This Book first encountered Oracle in 1982, in the process of evaluating database management I systems for a major commercial application that my company was preparing to design and build. At its conclusion, our evaluation was characterized by ComputerWorld as the single-most “grueling” study of DBMSs that had ever been conducted. The study was so tough on the vendors whose products we examined that word of it made the press as far away as New Zealand and publications as far afield as the Christian Science Monitor. We began the study with 108 candidate companies, then narrowed the field to sixteen finalists, including most of the major database vendors of the time, and all types of databases: network, hierarchical, relational, and others. After the rigorous final round of questions, two of the major vendors participating asked that the results of the study of their products never be published. A salesman from a third vendor quit his job at the end of one of the sessions. We knew how to ask tough questions. Oracle, known then as Relational Software, Inc., had fewer than 25 employees at the time, and only a few major accounts. Nevertheless, when the study was completed, we announced Oracle as the winner. We declared that Oracle was technically the best product on the market, and that the management team at RSI looked capable enough to carry the company forward successfully. Our radical proclamation was made at a time when few people even knew what the term relational meant, and those who did had very few positive things to say about it. Many IS executives loudly criticized our conclusions and predicted that Oracle and the relational database would go nowhere. Oracle today is the largest database company, and the second largest software company in the world. The relational database is now the world standard. Koch Systems Corporation, the company I owned and ran at the time, became Oracle’s first Valued Added Reseller. We developed the world’s first major commercial relational application, a securities trading and accounting system called THESIS. This product was used by major banks and corporations to manage their investment portfolios. Even IBM bought THESIS, and it allowed Oracle to be installed at IBM headquarters in spite of vigorous internal opposition. After all, IBM was the leading database company at the time, with IMS and DB2 as their flagship products. Oracle was continuing to refine its young product, to understand the kinds of features and functionality that would make it productive and useful in the business world, and our development efforts at Koch Systems contributed to that refinement. Some of Oracle’s features were the direct ix P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp Friday, July 19, 2002 4:10:32 PM
  10. Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Front Matter ORACLE Color profile: Generic CMYK printer profile Composite Default screen Blind Folio FM:x x Oracle9i: The Complete Reference results of requests that we made of Oracle’s developers, and our outspoken advocacy of an end-user bias in application design and naming conventions has influenced a generation of programmers who learned Oracle in our shop or read articles which we published. All of this intimate involvement with the development and use of Oracle led us to an early and unmatched expertise with the product and its capabilities. Since I have always loved sharing discoveries and knowledge—to help shorten the learning time necessary with new technologies and ideas, and save others the cost of making the same mistakes I did—I decided to turn what we’d learned into a book. Oracle: The Complete Reference was conceived in 1988 to pull together all of the fundamental commands and techniques used across the Oracle product line, as well as give solid guidance in how to develop applications using Oracle and SQL. Part I of the book was aimed both at developers and end-users, so that they could share a common language and understanding during the application development process: developers and end users working side by side—a wild concept when the book was first conceived. Linda Allen, a respected literary agent in San Francisco, introduced me to Liz Fisher, then the editor at McGraw-Hill/Osborne. Liz liked the idea very much. Contracts were drawn, and the first edition was scheduled to be released in 1989. But a now-departed senior executive at McGraw-Hill heard of the project and instantly canceled its development, pronouncing that Oracle is a flash in the pan. It is going nowhere. A year later, when Oracle Corporation had again doubled in size and the senior executive was gone, the effort was restarted, and the first edition finally arrived in 1990. Almost immediately, it became the No. 1 book in its category, a position it has maintained for over a decade. In July of 1990, I was hired by Oracle to run its Applications Division. I became senior vice president of the company and guided the division (with a lot of talented help) to worldwide success. While at Oracle, I also introduced McGraw-Hill/Osborne to Oracle senior management, and after opposition from an Oracle vice president who didn’t see any value in the idea (he’s no longer with Oracle), Oracle Press was born. Oracle Press is now the leading publisher of Oracle-based reference manuals in the world. In 1992, Bob Muller, a former developer at both Koch Systems and Oracle, took over responsibilities for technical updates to the book, as my duties at Oracle precluded any more than editorial review of changes. This produced Oracle7: The Complete Reference. This was Bob’s first published book, and he has since gone on to write several other popular books on development and database design. In 1994, I left Oracle to fulfill a long-held desire—full time ministry—and today I’m the pastor of Church of the Resurrection (http://www.resurrection.org) in West Chicago, Illinois. I continue to write in publications as diverse as the Wall Street Journal and Christianity Today, and I’ve recently published a book in England, The Country Parson’s Advice to His Parishioner, from Monarch Books. I also sit on the board of directors of Apropos, a leading call center applications company, but I no longer work in Oracle application development. Also in 1994, Kevin Loney, a highly respected independent Oracle consultant and author (http://www.kevinloney.com), took over the updating and rewriting responsibilities for the third edition of the book, and has continued ever since. He has contributed major new sections (such as the Hitchhiker’s Guides, the PL/SQL, Java, and ORDBMS sections, among others), and fully integrated new Oracle product features into all sections of the book. He has also integrated many readers’ comments into the structure and content of the book, making its current form the product of both its readers and its authors. Those efforts have allowed Oracle: The Complete Reference to P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp Friday, July 19, 2002 4:10:33 PM
  11. Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Front Matter ORACLE Color profile: Generic CMYK printer profile Composite Default screen Blind Folio FM:xi xi Preface stay at the top of its field and continue to be the single-most comprehensive guide to Oracle, still unmatched in range, content, and authority. I am a real fan of Kevin’s and am most impressed by his knowledge and thoroughness. Oracle: The Complete Reference is now available in eight languages, and is found on the desks of developers and Oracle product users all over the world. Not only has it been No. 1 in its category (with two editions out, it was once both No. 1 and No. 4), it has also been regularly in the top 100 of all books sold through Amazon.com. At one point it was the No. 7 best-selling book of all books sold in Brazil! Its reputation and enduring success are unparalleled in its marketplace. Like Oracle itself, the book has survived and prospered in spite of the recurring predictions of failure from many quarters. Perhaps this brief history can be an encouragement to others who face opposition but have a clear vision of what is needed in the years ahead. As Winston Churchill said, “Never give in, never give in, never give in—in nothing great or small, large or petty—never give in except to convictions of honor and good sense.” George Byron Koch GeorgeKoch@GeorgeKoch.com Wheaton, Illinois P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp Friday, July 19, 2002 4:10:33 PM
  12. Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Front Matter ORACLE Color profile: Generic CMYK printer profile Composite Default screen Blind Folio FM:xiii Introduction racle is the most widely used database in the world. It runs on virtually every O kind of computer. It functions virtually identically on all these machines, so when you learn it on one, you can use it on any other. This fact makes knowledgeable Oracle users and developers very much in demand, and makes your Oracle knowledge and skills very portable. Oracle documentation is thoroughgoing and voluminous, currently spanning multiple CDs. Oracle9i: The Complete Reference is the first entity that has gathered all of the major Oracle definitions, commands, functions, features, and products together in a single, massive core reference—one volume that every Oracle user and developer can keep handy on his or her desk. The audience for this book will usually fall into one of three categories: s An Oracle end user Oracle can easily be used for simple operations such as entering data and running standard reports. But such an approach would ignore its great power; it would be like buying a high-performance racing car, and then pulling it around with a horse. With the introduction provided in the first two sections of this book, even an end user with little or no data processing background can become a proficient Oracle user—generating ad hoc, English-language reports; guiding developers in the creation of new features and functions; and improving the speed and accuracy of the real work done in a business. The language of the book is simple, clear English without data processing jargon, and with few assumptions about previous knowledge of computers or databases. It will help beginners to become experts with an easy-to-follow format and numerous real examples. s A developer who is new to Oracle With as many volumes of documentation as Oracle provides, finding a key command or concept can be a time-consuming effort. This book attempts to provide a more organized and efficient manner of learning the essentials of the product. The format coaches a developer new to Oracle quickly through the basic concepts, covers areas of common difficulty, examines misunderstanding of the product and relational development, and sets clear guidelines for effective application building. xiii P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp Friday, July 19, 2002 4:10:33 PM
  13. Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Front Matter ORACLE Color profile: Generic CMYK printer profile Composite Default screen Blind Folio FM:xiv xiv Oracle9i: The Complete Reference s An experienced Oracle developer As with any product of great breadth and sophistication, there are important issues about which little, if anything, has been published. Knowledge comes through long experience, but is often not transferred to others. This book delves deeply into many such subject areas (such as precedence in UNION, INTERSECTION, and MINUS operators; inheritance and CONNECT BY; eliminating NOT IN with an outer join; using external tables; implementing the object-relational and Java options; and many others). The text also reveals many common misconceptions and suggests rigorous guidelines for naming conventions, application development techniques, and design and performance issues. How This Book Is Organized There are seven major parts to this book and a CD-ROM. Part I is an introduction to “Critical Database Concepts.” These chapters are essential reading for any Oracle user, new or veteran, from key-entry clerk to database administrator. They establish the common vocabulary that both end users and developers can use to coherently and intelligently share concepts and assure the success of any development effort. This introductory section is intended for both developers and end users of Oracle. It explores the basic ideas and vocabulary of relational databases and points out the dangers, classical errors, and profound opportunities in relational database applications. Part II, “SQL and SQL*Plus,” teaches the theory and techniques of relational database systems and applications, including SQL (Structured Query Language) and SQLPLUS. The section begins with relatively few assumptions about data processing knowledge on the part of the reader, and then advances step by step, through some very deep issues and complex techniques. The method very consciously uses clear, conversational English, with unique and interesting examples, and strictly avoids the use of undefined terms or jargon. This section is aimed primarily at developers and end users who are new to Oracle, or need a quick review of certain Oracle features. It moves step by step through the basic capabilities of SQL and Oracle’s interactive query facility, SQLPLUS. When you’ve completed this section you should have a thorough understanding of all SQL key words, functions, and operators. You should be able to produce complex reports, create tables, and insert, update, and delete data from an Oracle database. The later chapters of Part II provide some very advanced methods in SQLPLUS, Oracle’s simple, command-line interface, and in-depth descriptions of the new and very powerful features of Oracle. This is intended for developers who are already familiar with Oracle, and especially those familiar with previous versions of Oracle, but who have discovered needs they couldn’t readily fill. Some of these techniques are previously unpublished and, in some cases, have been thought impossible. The tips and advanced techniques covered here demonstrate how to use Oracle in powerful and creative ways. These include taking advantage of distributed database capabilities, loading data files, and performing advanced text-based searches. They also include the latest features, such as external tables, flashback queries, and new datatypes and functions. Part III, “PL/SQL,” provides coverage of PL/SQL. The topics include a review of PL/SQL structures, plus triggers, stored procedures, and packages. Part IV, “Object-Relational Databases,” provides extensive coverage of object-oriented features such as abstract datatypes, methods, object views, object tables, nested tables, varying arrays, and large objects. P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp Friday, July 19, 2002 4:10:33 PM
  14. Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Front Matter ORACLE Color profile: Generic CMYK printer profile Composite Default screen Blind Folio FM:xv xv Introduction Part V, “Java in Oracle,” provides coverage of the Java features in the Oracle database. This section includes an overview of Java syntax as well as chapters on JDBC and SQLJ and Java stored procedures. Part VI contains several “hitchhiker” guides: to the data dictionary, database optimizer, Oracle9i Application Server, database administration, and Oracle’s XML implementation. These guides provide an overview of areas that developers may need to use in their application development and administration. Part VII, the “Alphabetical Reference,” is the complete reference for the Oracle server—a book unto itself. Reading the introductory pages to this reference will make its use much more effective and understandable. This section contains references for most major Oracle commands, keywords, products, features and functions, with extensive cross-referencing of topics. The reference is intended for use by both developers and users of Oracle but assumes some familiarity with the products. To make the most productive use of any of the entries, it would be worthwhile to read the first four pages of the reference. These explain in greater detail what is and is not included and how to read the entries. The CD that accompanies this book contains a special electronic edition of Oracle9i: The Complete Reference. Now, with this electronic version, you can easily store all of the valuable information contained in the book on your PC while the print version of the book remains in your office or home. The CD also contains the table creation statements and row insertions for all of the tables used in this book. For anyone learning Oracle, having these tables available on your own Oracle ID, or on a practice ID, will make trying or expanding on the examples very easy. Style Conventions Used in This Book Except when testing for an equality (such as, City = 'CHICAGO'), Oracle ignores upper- and lowercase. In the formal listing of commands, functions, and their format (syntax) in the Alphabetical Reference, this book will follow Oracle’s documentation style of putting all SQL in UPPERCASE, and all variables in lowercase italic. Most users and developers of Oracle, however, never key all their SQL in uppercase. It’s too much trouble, and Oracle doesn’t care anyway. This book, therefore, will follow somewhat different style conventions in its examples (as opposed to its formal command and function formats, mentioned earlier), primarily for readability. They are as follows: s Italic and boldface will not be used in example listings. s select, from, where, order by, having, and group by will be in lowercase. s SQLPLUS commands will be in lowercase: column, set, save, ttitle, and so on. s SQL operators and functions will be in uppercase, such as IN, BETWEEN, UPPER, SOUNDEX, and so on. s Columns will use upper- and lowercase, as in Feature, EastWest, Longitude, and so on. s Tables will be in uppercase, such as in NEWSPAPER, WEATHER, LOCATION, and so on. P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp Friday, July 19, 2002 4:10:34 PM
  15. Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Chapter 1 ORACLE Color profile: Generic CMYK printer profile Composite Default screen Blind Folio 1:1 PART I Critical Database Concepts P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp Friday, July 19, 2002 4:10:34 PM
  16. Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Chapter 1 ORACLE Color profile: Generic CMYK printer profile Composite Default screen Blind Folio 1:2 P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp Friday, July 19, 2002 4:10:34 PM
  17. Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Chapter 1 ORACLE Color profile: Generic CMYK printer profile Composite Default screen Blind Folio 1:3 CHAPTER 1 Sharing Knowledge and Success P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp Friday, July 19, 2002 4:10:35 PM
  18. Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Chapter 1 ORACLE Color profile: Generic CMYK printer profile Composite Default screen Blind Folio 1:4 4 Part I: Critical Database Concepts or an Oracle9i application to be built and used rapidly and effectively, users and F developers must share a common language and a deep and common understanding of both the business application and the Oracle tools. This is a new approach to development. Historically, the systems analyst studied the business requirements and built an application to meet those needs. The user was involved only in describing the business and, perhaps, in reviewing the functionality of the application after it was completed. With the new tools and approaches available, and especially with Oracle, applications can be built that more closely match the needs and work habits of the business—but only if a common understanding exists. This book is aimed specifically at fostering this understanding, and at providing the means for both user and developer to exploit Oracle’s full potential. The end user will know details about the business that the developer will not comprehend. The developer will understand internal functions and features of Oracle and the computer environment that will be too technically complex for the end user. But these areas of exclusive expertise will be minor compared with what both end users and developers can share in using Oracle. There is a remarkable opportunity here. It is no secret that “business” people and “systems” people have been in conflict for decades. Reasons for this include differences in knowledge, culture, professional interests and goals, and the alienation that simple physical separation between groups can often produce. To be fair, this syndrome is not peculiar to data processing. The same thing occurs between people in accounting, personnel, or senior management, as members of each group gather apart from other groups on a separate floor or in a separate building or city. Relations between the individuals from one group and another become formalized, strained, and abnormal. Artificial barriers and procedures that stem from this isolationism become established, and these also contribute to the syndrome. This is all very well, you say, and may be interesting to sociologists, but what does it have to do with Oracle? Because Oracle isn’t cloaked in arcane language that only systems professionals can comprehend, it fundamentally changes the nature of the relationship between business and systems people. Anybody can understand it. Anybody can use it. Information that previously was trapped in computer systems until someone in systems created a new report and released it now is accessible, instantly, to a business person, simply by typing an English query. This changes the rules of the game. Where Oracle is used, it has radically improved the understanding between the two camps, has increased their knowledge of one another, and has even begun to normalize relations between them. This has also produced superior applications and end results. Since its first release, Oracle has been based on the easily understood relational model (explained shortly), so nonprogrammers can readily understand what Oracle does and how it does it. This makes it approachable and unimposing. Furthermore, Oracle was created to run identically on virtually any kind of computer. Thus, it doesn’t matter which manufacturer sold you your equipment; Oracle works on it. These features all contributed directly to the profound success of the product and the company. In a marketplace populated by computer companies with “proprietary” hardware, “proprietary” operating systems, “proprietary” databases, and “proprietary” applications, Oracle gives business users and systems departments new control over their lives and futures. They are no longer bound to the database product of a single hardware vendor. Oracle runs on nearly every kind of computer. This is a basic revolution in the workplace and in application development, with consequences that will extend far into the future. P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp Friday, July 19, 2002 4:10:35 PM
  19. Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Chapter 1 ORACLE Color profile: Generic CMYK printer profile Composite Default screen Blind Folio 1:5 5 Chapter 1: Sharing Knowledge and Success Some individuals neither accept nor understand this yet, nor do they realize just how vital it is that the dated and artificial barriers between “users” and “systems” continue to fall. But the advent of cooperative development will profoundly affect applications and their usefulness. However, many application developers have fallen into an easy trap with Oracle: carrying forward unhelpful methods from previous-generation system designs. There is a lot to unlearn. Many of the techniques (and limitations) that were indispensable to a previous generation of systems are not only unnecessary in designing with Oracle; they are positively counterproductive. In the process of explaining Oracle, the burden of these old habits and approaches must be lifted. Refreshing new possibilities are available. Throughout this book, the intent will be to explain Oracle in a way that is clear and simple, in terms that both users and developers can understand and share. Outdated or inappropriate design and management techniques will be exposed and replaced. The Cooperative Approach Oracle is an object-relational database. A relational database is an extremely simple way of thinking about and managing the data used in a business. It is nothing more than a collection of tables of data. We all encounter tables every day: weather reports, stock charts, sports scores. These are all tables, with column headings and rows of information simply presented. Even so, the relational approach can be sophisticated and powerful enough for even the most complex of businesses. An object-relational database supports all of the features of a relational database while also supporting object-oriented concepts and features. Unfortunately, the very people who can benefit most from a relational database—the business users—usually understand it the least. Application developers, who must build systems that these users need to do their jobs, often find relational concepts difficult to explain in simple terms. A common language is needed to make this cooperative approach work. The first two parts of this book explain, in readily understandable terms, just what a relational database is and how to use it effectively in business. It may seem that this discussion is for the benefit of “users” only. An experienced relational application designer may be inclined to skip these early chapters and simply use the book as a primary source Oracle reference. Resist that temptation! Although much of this material may seem like elementary review, it is an opportunity for an application designer to acquire a clear, consistent, and workable terminology with which to talk to users about their needs and how these needs might be quickly met. If you are an application designer, this discussion may also help you unlearn some unnecessary and probably unconscious design habits. Many of these habits will be uncovered in the course of introducing the relational approach. It is important to realize that even Oracle’s power can be diminished considerably by design methods appropriate only to nonrelational development. If you are an end user, understanding the basic ideas behind object-relational databases will help you express your needs cogently to application developers and comprehend how those needs can be met. An average person working in a business role can go from beginner to expert in short order. With Oracle, you’ll have the power to get and use information, have hands-on control over reports and data, and possess a clear-eyed understanding of what the application does and how it does it. Oracle gives you, the user, the ability to control an application or query facility expertly and know whether you are getting all the available flexibility and power. You also will be able to unburden programmers of their least favorite task: writing new reports. In large organizations, as much as 95 percent of all programming backlog is composed P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp Friday, July 19, 2002 4:10:35 PM
  20. Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Chapter 1 ORACLE Color profile: Generic CMYK printer profile Composite Default screen Blind Folio 1:6 6 Part I: Critical Database Concepts of new report requests. Because you can write your own reports, in minutes instead of months, you will be delighted to have the responsibility. Everyone Has “Data” A library keeps lists of members, books, and fines. The owner of a baseball card collection keeps track of players’ names, dates, averages, and card values. In any business, certain pieces of information about customers, products, prices, financial status, and so on must be saved. These pieces of information are called data. Information philosophers like to say that data is just data until it is organized in a meaningful way, at which point it becomes “information.” If this is true, then Oracle is also a means of easily turning data into information. Oracle will sort through and manipulate data to reveal pieces of knowledge hidden there—such as totals, buying trends, or other relationships—which are as yet undiscovered. You will learn how to make these discoveries. The main point here is that you have data, and you do three basic things with it: acquire it, store it, and retrieve it. Once you’ve achieved the basics, you can make computations with data, move it from one place to another, or modify it. This is called processing, and, fundamentally, it involves the same three steps that affect how information is organized. You could do all of this with a cigar box, pencil, and paper, but as the volume of data increases, your tools tend to change. You may use a file cabinet, calculators, pencils, and paper. While at some point it makes sense to make the leap to computers, your tasks remain the same. A relational database management system (often called an RDBMS for short) such as Oracle gives you a way of doing these tasks in an understandable and reasonably uncomplicated way. Oracle basically does three things: s Lets you put data into it s Keeps the data s Lets you get the data out and work with it Figure 1-1 shows how simple this process is. Oracle supports this in-keep-out approach and provides clever tools that allow you considerable sophistication in how the data is captured, edited, modified, and put in; how you keep it securely; and how you get it out to manipulate and report on it. An object-relational database management system (ORDBMS) extends the capabilities of the RDBMS to support object-oriented concepts. You can use Oracle as an RDBMS or take advantage of its object-oriented features. The Familiar Language of Oracle The information stored in Oracle is kept in tables—much like the weather table from a daily newspaper shown in Figure 1-2. This table has four columns: City, Temperature, Humidity, and Condition. It also has a row for each city from Athens to Sydney. Last, it has a table name: WEATHER. P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp Friday, July 19, 2002 4:10:36 PM
ADSENSE

CÓ THỂ BẠN MUỐN DOWNLOAD

 

Đồng bộ tài khoản
2=>2