Covers Oracle DBMS versions 10, 11, 12, and XE
Sams Teach Yourself Oracle PL/SQL in 10 Minutes offers straightforward, practical answers when you need fast results. By working through the book’s 26 lessons of 10 minutes or less, you’ll learn what you need to know to take advantage of Oracle’s PL/SQL language.
This handy pocket guide starts with simple data retrieval and moves on to more complex topics, including the use of joins, subqueries, regular expression and full text-based searches, stored procedures, cursors, triggers, table constraints, and much more.
10 minutes is all you need to learn how to...
- Use PL/SQL in the Oracle environments and tools
- Construct complex PL/SQL statements using multiple clauses and operators
- Retrieve, sort, and format database contents
- Pinpoint the data you need using a variety of filtering techniques
- Use string, date-time, and math functions to manipulate your data
- Join two or more related tables
- Insert, update, and delete data
- Create and alter database tables
- Work with views, stored procedures, cursors, triggers, and more
Table of Contents:
Introduction 1
What Is This Book? 1
Who Is This Book For? 2
Companion Website 3
Conventions Used in This Book 3
Chapter 1: Understanding SQL 5
Database Basics 5
What Is SQL? 10
Try It Yourself 11
Summary 12
Chapter 2: Getting Started with Oracle and PL/SQL 13
What Is Oracle? 13
Getting Set Up 16
Summary 20
Chapter 3: Working with Oracle 21
Creating a Working Environment 21
Making the Connection 25
A Quick Introduction to Oracle SQL Developer 27
Creating and Populating the Example Tables 28
One More Look at Oracle SQL Developer 32
Summary 32
Chapter 4: Retrieving Data 33
The SELECT Statement 33
Retrieving Individual Columns 33
Retrieving Multiple Columns 36
Retrieving All Columns 37
Retrieving Distinct Rows 38
Using Fully Qualified Table Names 40
Using Comments 40
Summary 42
Chapter 5: Sorting Retrieved Data 43
Sorting Data 43
Sorting by Multiple Columns 45
Specifying Sort Direction 47
Summary 49
Chapter 6: Filtering Data 51
Using the WHERE Clause 51
The WHERE Clause Operators 53
Summary 59
Chapter 7: Advanced Data Filtering 61
Combining WHERE Clauses 61
Using the IN Operator 65
Using the NOT Operator 67
Summary 68
Chapter 8: Using Wildcard Filtering 69
Using the LIKE Operator 69
Tips for Using Wildcards 74
Summary 74
Chapter 9: Searching Using Regular Expressions 75
Understanding Regular Expressions 75
Using Oracle PL/SQL Regular Expressions 76
Summary 89
Chapter 10: Creating Calculated Fields 91
Understanding Calculated Fields 91
Concatenating Fields 92
Performing Mathematical Calculations 96
Summary 98
Chapter 11: Using Data Manipulation Functions 99
Understanding Functions 99
Using Functions 100
Summary 107
Chapter 12: Summarizing Data 109
Using Aggregate Functions 109
Aggregates on Distinct Values 117
Combining Aggregate Functions 118
Summary 119
Chapter 13: Grouping Data 121
Understanding Data Grouping 121
Creating Groups 122
Filtering Groups 123
Grouping and Sorting 126
SELECT Clause Ordering 129
Summary 129
Chapter 14: Working with Subqueries 131
Understanding Subqueries 131
Filtering by Subquery 131
Using Subqueries as Calculated Fields 136
Summary 140
Chapter 15: Joining Tables 141
Understanding Joins 141
Creating a Join 144
Summary 152
Chapter 16: Creating Advanced Joins 153
Using Table Aliases 153
Using Different Join Types 154
Using Joins with Aggregate Functions 160
Using Joins and Join Conditions 161
Summary 162
Chapter 17: Combining Queries 163
Understanding Combined Queries 163
Creating Combined Queries 164
Summary 169
Chapter 18: Inserting Data 171
Understanding Data Insertion 171
Inserting Complete Rows 172
Inserting Retrieved Data 176
Summary 178
Chapter 19: Updating and Deleting Data 179
Updating Data 179
Deleting Data 181
Guidelines for Updating and Deleting Data 183
Summary 184
Chapter 20: Creating and Manipulating Tables 185
Creating Tables 185
Updating Tables 190
Deleting Tables 193
Renaming Tables 193
Summary 194
Chapter 21: Using Views 195
Understanding Views 195
Using Views 197
Summary 204
Chapter 22: Working with Stored Procedures 205
Understanding Stored Procedures 205
Why Use Stored Procedures 206
Using Stored Procedures 207
Summary 214
Chapter 23: Using Cursors 215
Understanding Cursors 215
Working with Cursors 216
Summary 222
Chapter 24: Using Triggers 223
Understanding Triggers 223
Creating Triggers 224
Dropping Triggers 225
Using Triggers 225
Summary 232
Chapter 25: Managing Transaction Processing 233
Understanding Transaction Processing 233
Controlling Transactions 235
Summary 238
Chapter 26: Managing Security 239
Understanding Access Control 239
Managing Users 240
Summary 244
Appendix A: The Example Tables 245
Understanding the Sample Tables 245
Appendix B: Oracle PL/SQL Datatypes 251
String Datatypes 252
Numeric Datatypes 253
Appendix C: Oracle PL/SQL Reserved Words and Keywords 255
Index 259
About the Author :
Ben Forta has three decades of experience in the computer industry in product design and development, support, training, and marketing. As Adobe Inc.’s Senior Director of Education Initiatives, he spends a considerable amount of time teaching, talking, and writing about Adobe products, coding and application development, creativity, and digital literacy and provides feedback to help shape the future direction of Adobe products.
Ben is the author of more than 40 books, including the world’s bestselling title on SQL, as well as titles on topics as diverse as Regular Expressions, mobile development, and Adobe ColdFusion. More than 750,000 copies of his books are in print in English, and titles have been translated into fifteen languages. Many of these titles are used as textbooks in colleges and universities worldwide.
Education is Ben’s passion. Between writing, lecturing, and in-classroom experience, Ben has dedicated his professional and personal lives to teaching, inspiring, and sharing his love for technology and creativity. He is immensely grateful to have had the opportunity to share with millions worldwide.
Ben is also a successful entrepreneur with experience creating, building, and selling start-ups. He is a sought-after public speaker, a writer, and a blogger, and he presents on education and development topics worldwide.
