Preface
This book emphasizes core concepts of the Oracle Database Server: database tables, indexes, tablespaces, constraints, triggers, and PL/SQL. Mastering these core components is essential to applications development. Whether you are a web developer or a client/server or backend programmer, the content in this book will help you realize the potential of implementing logic in the database server with declarative and procedural constraints, using PL/SQL triggers and stored procedures.
Who Will Benefit from This Book?
This is a programmer's book. Some programming knowledge is helpful but not essential. No prior knowledge of Oracle is required. Developers transitioning from other databases to an Oracle environment can benefit from the detailed explanations, numerous figures, and many code examples that illustrate Oracle structures. The discussion on triggers and PL/SQL is geared toward an individual who wants a quick transition from other programming paradigms to the PL/SQL model. I have worked on Oracle projects staffed with non-Oracle database developers and understand the confusion with terminology. A consistent focus of this text is to explain the basic paradigm, which helps readers who are new to the technology as well as experienced developers who are cross-training.
This text can help developers who are transitioning from non-relational databases to Oracle. Chapter 1 addresses the beginnings of SQL. Developers from hierarchical and network databases will find this chapter a place in which that they can put SQL into perspective. The early part of my career included five years on a hierarchical database and three years on a network database. I understand the transition needed to switch to Oracle, and the explanations in this text should address the needs of non-relational developers who are cross-training.
Developers who naturally “dig” into a subject will benefit from the discussion on the Oracle data dictionary, addressed extensively in Chapter 5. This chapter illustrates extracting constraint definitions from the data dictionary. A curious developer can expand this simple technique to extract unlimited information from the data dictionary. This is highly beneficial to anxious learners. Readers of the previous editions have mentioned this as one of the most helpful parts of the book because it taught them how to explore and learn on their own.
Developers often find themselves performing database administration—usually on a development server. This text is a good starting point for beginning database administration. Developers are naturally in touch with the logical world of tables and tablespaces. The discussion on the physical aspects of the database, tablespace data files, is an excellent starting point for a developer who needs to understand the physical aspects of an Oracle environment.
Foremost is the emphasis on building business logic in an Oracle database. A consistent theme in the text is to make the database work for you. An application team that capitalizes on declarative constraints can produce an “intelligent” database in which all data conforms to strict business-rule logic. Furthermore, when the team incorporates triggers with PL/SQL, the database integrity extends to a point where it can perform logical operations on the end-user's behalf. This can include complex business-rule enforcement as well as actions such as automatic email notification, which is illustrated in Chapter 11.
How Is This Book Structured?
Chapters 1 and 2
Chapters 1 and 2 cover Oracle tables, column types, and how to use SQL*Plus to interact with the database. Some highly useful features of SQL*Plus are described including SQL*Plus commands to show a SQL execution plan and its statistics. These chapters concentrate on terminology, explaining the intuitive meaning of the terms SQL, SQL*Plus, and PL/SQL.
Chapters 3, 4, and 5
Chapters 3, 4, and 5 thoroughly cover declarative constraints including extracting constraint definitions from the Oracle data dictionary. These chapters (a) provide you with the knowledge to build a database with high integrity—something every client wants, (b) empower you with the skill to extract a wide variety of information from the data dictionary—a skill everyone needs, and (c) provide insight into declarative constraints and help you understand how they work.
Chapters 6, 7, and 8
Chapters 6, 7, and 8 cover database triggers, also called procedural constraints. Chapters 6 and 7 provide an intuitive understanding with numerous figures to illustrate table row and statement level triggers. This intuitive understanding is critical in helping you choose what type of trigger you should write. Chapter 8 illustrates the use of database triggers and PL/SQL for complex rule enforcement.
Chapter 9
Chapter 9 helps you get started quickly with compiling PL/SQL programs. A rapid learning approach is to code while you learn. This chapter helps you understand the PL/SQL coding environment. After completing this chapter, you will have a basic understanding of how to code, compile, and execute PL/SQL procedures. The following two chapters provide the technical content of PL/SQL while this chapter introduces the means to code and test as you learn.
Chapter 10
Chapter 10 looks at PL/SQL from a software engineering perspective. It is not enough just to know the PL/SQL syntax. Before a language bears meaning, one needs to understand the PL/SQL paradigm and the model for PL/SQL program units: the procedure, function, and package. This chapter teaches you PL/SQL program units conceptually and encourages you to think about when and how to use these program units during the design phase.
Chapter 11
Chapter 11 covers PL/SQL language features. Particular attention is given to PL/SQL constructs and built-in SQL functions, emphasizing writing simple and easy-to-understand algorithms. The chapter concludes with a section that illustrates an application using a database trigger to send email notification using the Oracle built-in Alerts package.
All scripts in the text were run against Oracle 9i Release 2 and 10g Version 10.1.0.
Conventions
The use of upper and lower case is intended to make clear the identity of SQL and PL/SQL keywords. All SQL and PL/SQL code is written in lower case except for language keywords, which are upper case. The following, taken from Chapter 3, illustrates this writing style.
CREATE SEQUENCE student_vehicles_pk_seq;
Quoted expressions are often in upper case as well as all references to Oracle built-in functions. The following, taken from Chapter 11, includes references to two Oracle built-in functions (NEWTIME and SYSDATE) written in upper case.
pst_date := NEW_TIME(SYSDATE, 'EST','PST'),
Oracle provides may built-in packages. The sample code in this text refers to all built-in packages in lower case. This decision was made to make the code more readable and easier on the eyes. Excessive upper case text can be rough on the corneas. The following, from Chapter 11, illustrates the choice of lower case when writing references to the Oracle built-in package DBMS_ALERT.
dbms_alert.register('OUT_OF_STOCK_ITEM'),
The PL/SQL code examples often include counter variable declarations; these are often in upper case. The following, from Chapter 11, declares a variable, ID. This is not a keyword but is easier to read in upper case.
ID VARCHAR2(10),
Finally non-code text, that is, the paragraphs of the text, always refers to code items in upper case. The text of a paragraph may refer to a constraint name or PL/SQL variable name, and always, the code identifier is written in upper case within the body of a paragraph.
Acknowledgments
I would like to thank the technical review and editing team: Pat Starr, Kathryn Tyser, and Linda Owens. Thanks also to Jeffrey Pepper at Prentice Hall for diligence, and the thorough editing from copyeditor Laura Burgess, and Jessica Balch at Pine Tree Composition.
Kevin Owens