Learning Oracle PL/SQL
Oracle Development Languages
Published on 21. November 2001
424 pages
978-0-596-15582-7 (ISBN)
Description
PL/SQL, Oracle's programming language for stored procedures, delivers a world of possibilities for your database programs. PL/SQL supplements the standard relational database language, SQL, with a wide range of procedural features, including loops, IF-THEN statements, advanced data structures, and rich transactional control--all closely integrated with the Oracle database server.Knowing where to start with Oracle's procedural language is not always obvious to a newcomer, especially considering the language's feature set and the sheer size of the official documentation (not to mention Oracle's ever-increasing number of pre-built PL/SQL programs). But Learning Oracle PL/SQL offers the signposts and guidance you need to come up to speed on the language, delivered in a manageable number of pages while covering all the essentials.Topics include:PL/SQL--what is it, and why use it? Why use PL/SQL instead of Java?Syntax and examples of all core language constructsCreating, using, and reusing stored procedures, functions, and packagesBuilding web-based applications using PL/SQL features available "e;out of the box"e; (such as PL/SQL Server Pages)Securing PL/SQL programs against attackBenefits of third-party developer tools and integrated development environmentsConnecting PL/SQL to email, Java, and the InternetMeticulously crafted with all-new examples downloadable from examples.oreilly.com/learnoracle, the book addresses language features available in all versions of Oracle, from Oracle7 to Oracle8i to Oracle9i.Learning Oracle PL/SQL was written by PL/SQL experts Bill Pribyl and Steven Feuerstein, whose easy-to-read style and attention to detail has made other O'Reilly books (such as the bestselling Oracle PL/SQL Programming) very popular among Oracle developers worldwide. Learning Oracle PL/SQL is meant for a wide range of target audiences, including both beginning programmers and those already experienced with other programming languages. Whether you are a new developer, a crossover programmer from another database system, or a new database administrator who needs to learn PL/SQL, this book will get you well on your way. It is the perfect introduction to Oracle PL/SQL Programming, also by Pribyl and Feuerstein.
More details
Other editions
Additional editions
Bill Pribyl | Steven Feuerstein
Learning Oracle PL/SQL
Book
12/2001
O'Reilly
€47.00
Article not available at the moment
Content
- Intro
- Table of Contents
- Preface
- Is This Book for You?
- Other Books in This Series
- Why This Book?
- Which Oracle and PL/SQL Versions?
- Organization of This Book
- Conventions Used in This Book
- Comments and Questions
- Acknowledgments
- From Bill
- From Steven
- PL/SQL: What, When, and Where
- What Is PL/SQL?
- Why SQL Is Not Enough
- A Meaty PL/SQL Example
- PL/SQL and the Oracle Server
- What PL/SQL Is Not
- Why Use PL/SQL?
- "I'd Rather Use a 'Real' Language Like C!"
- Why Should I Use Stored Procedures at All?
- "Hey, Isn't Java Where It's At?"
- PL/SQL is more concise than Java
- You can call PL/SQL functions directly from SQL
- PL/SQL can run without human intervention (and without any obvious divine intervention)
- Many cool Oracle features are only accessible via PL/SQL
- When Is PL/SQL the Right Tool for the Job?
- The Best of All Worlds
- What You Need to Get Started with PL/SQL
- Hardware and Operating System
- A Licensed Copy of Oracle
- Acquiring Oracle
- Installing Oracle
- A Text Editor
- Fundamentals
- PL/Lingo
- Running Your First PL/SQL Program
- Starting SQL*Plus
- Entering PL/SQL Statements into SQL*Plus
- Saving Scripts to Use Again Later
- Exiting SQL*Plus
- Introduction to Program Structure
- More About Blocks
- Anonymous blocks
- Named blocks: procedures and functions
- Variables
- Datatypes
- Strings
- Numbers
- Dates
- Booleans
- Declaring Variables
- Common Operators
- Assignment Operator
- Arithmetic Operators
- Logical Operators
- Non-Null Comparison Operators
- Test for Nullity
- String Patterns and Wildcards: LIKE, %, _
- String Concatenation: ||
- Conditional Logic
- IF Statements
- CASE Statements
- Simple CASE statement
- Searched CASE statement
- CASE expressions
- Executing in Circles: Loop Statements
- FOR Loop
- Simple (Infinite) Loop
- WHILE Loop
- Simple or WHILE Loop?
- Code Formatting: Requirements and Guidelines
- Upper- or Lowercase?
- Spacing and Line Breaks
- Comments
- Single-line comments
- Multiline comments
- Code comments in this book
- Some Advanced Fundamentals
- NULLs in SQL and PL/SQL
- Null strings
- Converting NULLs
- Can we know for certain?
- Naming Rules for Identifiers
- Scope of Variables
- Advanced User-Defined Datatypes
- Let's Code!
- Some Background on the Example
- The Data Model
- The Physical Database Design
- A First Programming Exercise
- Rationale for the Design
- Identify Input, Process, and Outputs
- Implementing a Stored Procedure to Add a Book
- Analyzing for design
- Syntax to create a procedure
- The add_book procedure
- Where to handle exceptions
- Using the Procedure to Add a Book to the Catalog
- Getting a DATE
- Arguments optional
- Named notation
- Retrieving a Book Count with a Function
- Syntax for Creating a Function
- Code for the book_copy_qty Function
- Using the function
- Some rules about functions
- A "Gotcha" about Exceptions Raised by Functions in the Declaration Section
- Make Your Code Resilient
- A Results-Checking Utility
- A "Unit Tester" for add_book
- Testing the book_copy_qty Function
- Why So Much Trouble?
- Using PL/SQL Packages to Organize Code
- Parts of Packages
- The package specification
- The package body
- Benefits of Using Packages
- Revisiting the Unit Test Program
- Is All This Necessary?
- Going to the Next Level
- Naming Your Files
- Reuse Your Code
- Simplify by Overloading
- Now What?
- Go Web, Young Man
- Introduction to HTML
- Where Do Web Pages Come From?
- HTML Basics
- Accepting Data via Web-Based Forms
- Constructing a form
- Building a web-based library form
- Testing Your HTML
- Using PL/SQL to Create Web Pages
- Embedding HTML in PL/SQL
- Embedding PL/SQL in HTML
- Introduction to PL/SQL Server Pages
- Turning the book-adding HTML form into a PSP
- Back on the soapbox: the separation of concerns
- Handling Form Data
- Introduction to Error Handling
- System problems
- Data problems
- Creating a PL/SQL utility to check for valid numbers
- Checking for a valid date
- Setting Your Own Defaults
- Simple approach: public package variable
- Better approach: private package variable with "get and set" routines
- Further refinement: retrieve default from database
- Improving the Design
- The PL/SQL Details
- Interlude: bundling PL/SQL data items into programmer-defined records
- Using a programmer-defined record to hold book data
- What happens inside process_edits?
- The rest of the PSP
- Preventing Errors
- The HTML and supporting code
- Making the date modifications to the user interface (bookform.psp)
- What Else?
- Fetch!
- What's the Big Deal?
- A Simple-Minded Approach to Retrieving One Row
- Retrieving More than One Row Using a Cursor
- The Parable of the Thirsty Traveler
- How to Code an Explicit Cursor
- Declare
- Open
- Fetch
- Close
- Cursor Attributes
- In a Loop with a Cursor
- Shortcut Number 1: Speedy Declaration of Those Pesky Local Variables
- Anchored declarations
- Record data structures
- Shortcut Number 2: "Do the Right Thing" Loops
- Shortcut Number 3: Unnamed Cursor-FOR Loops
- Presenting Query Results via a Web Page
- Introduction to HTML Tables
- Displaying Oracle Table Data in an HTML Table
- Applying Search Criteria: the PL/SQL Part
- Building a Web-Based Search Page Using Dynamic SQL
- Simple Dynamic SQL Using EXECUTE IMMEDIATE
- Using REF CURSORs to Fetch Multiple Rows from a Dynamic SELECT Statement
- Applying Dynamic SQL Concepts to the Library Application
- Receiving search criteria from the user: the HTML part
- Adding an edit link to each displayed record
- Splitting up many results into multiple pages
- Displaying the total number of hits
- Advanced Data Retrieval Topics
- Preventing Update Anomalies by Locking Data
- Improving Performance of Embedded SELECT Statements
- Bind variables
- Bulk binds
- Implementing More Sophisticated Searching
- Parameterizing Cursors
- Using Strongly Typed Cursor Variables
- Keeping House
- Organize Your Code
- Code in Files
- Set up different directories for different logical groupings of code
- Use clearly understood filenames and informative file extensions
- Store package specifications and bodies in separate files
- Make frequent backup copies
- Build installation scripts
- Code in the Database
- Examine code properties
- View and search code
- View compile errors
- List dependencies
- Use Tools to Write Code Effectively
- Build Code
- Generate Code
- Homegrown generators
- Coding with snippets
- Generating packages around tables
- Debug Code
- Use Testing Utilities
- What's the problem?
- An quick introduction to utPLSQL
- Format and Analyze Code
- Security: Keep the Bad Guys Out
- Oracle Security Primer
- Security Requirements
- Many Rooms in the Oracle House
- Authentication
- Native Oracle database authentication
- External authentication via the operating system
- Advanced authentication services
- So what?
- Authorization
- Roles
- The GRANT Statement
- Organizing Accounts to Improve Security
- Privileges Needed to Build a Stored Program
- System-level
- Table-level
- Program-level
- More Privileges Needed to Execute a Stored Program
- Impact of Role-Based Security on PL/SQL Execution Privileges
- Roles can change
- When roles are not "good enough" for stored procedures
- When roles are "good enough" for stored procedures
- Combining invoker's and definer's rights
- How to Refer to "Things" Owned by Other Accounts
- Synonyms
- Name Resolution in PL/SQL
- Guidelines for Organizing Your "Stuff"
- Analyzing the Library System's Requirements
- Patron Accounts
- Challenges in Securing Web-Based Applications
- Sniff sniff...smells like chicken!
- What state are you in?
- Keeping a Trail of Database Changes
- Logging Data History Using Table-Level Triggers
- Table-level trigger example
- Table-level trigger syntax
- Other uses and types of triggers
- Other Methods to Track Changes
- Special Security Topics for PL/SQL Developers
- Educate the User
- Avoid Known Vulnerabilities in Oracle
- Watch Out for Batch Programs
- Scrutinize Dynamic SQL and PL/SQL
- Use the Virtual Private Database Feature
- Encrypt Data
- Encrypt Source Code
- Communicating with the Outside World
- Sending Internet Email from PL/SQL
- Using Oracle's Built-in Internet Mail Package: UTL_SMTP
- Alternatives to UTL_SMTP
- Using the Mail Sender in the Library System
- Book Transactions and Reservations
- The Reserve Notification Process
- The Trigger
- Receiving Email Inside the Database
- What Are My Options?
- Reading Data with Oracle's Built-in File Utility: UTL_FILE
- Opening and closing files
- What kind of files?
- Examples
- What is this "mode"?
- Loading an entire text file
- Handling exceptions
- Fetching Data from a Remote Web Site
- Fetching Book Catalog Information from the Library of Congress
- About UTL_HTTP
- Steps 1 and 2: Initialize the search and get the session id from the page
- An unpleasant footnote to step 2
- Step 3: Construct a URL to request catalog data
- Step 4: Scan the results and extract catalog data
- Aside: There Must Be a Better Way
- Integration with Other Languages
- A Brief Example of Calling Java from PL/SQL
- Step 1: Get some Java code to do what you want
- Step 2: Compile it
- Step 3: Load it into Oracle
- Step 4: Create a PL/SQL wrapper program
- Step5: Call the PL/SQL wrapper
- A Brief Example of Calling C from PL/SQL
- Step 1: Write the external procedure using C
- Step 2: Compile the C program into a shared library
- Step 3: Tell Oracle about the external procedure using CREATE LIBRARY
- Step 4: Write a PL/SQL wrapper
- Step 5: Call the external procedure from PL/SQL
- Intermediate Topics and Other Diversions
- Riding the Software Lifecycle
- Lists o' Stuff (Collections) in PL/SQL
- What Is a Collection?
- An Example: A Collection of Books
- What's Important About Collections?
- Differences between collection categories
- Collection syntax and discussion
- Initializing and assigning values to collections
- Built-in methods
- Privileges
- Exception-Handling Packages
- Transaction Control
- What Is a Transaction?
- Identify the Transactions in Your Application
- Determine the Effect of Errors on Your Transactions
- Know When to Use the Autonomous Transaction Feature
- The PL/SQL Compiler
- When Does Compilation Happen?
- "Oh DIANA, Won't You Compile for Me..."
- Using Native Compilation
- Managing Patron and Librarian Privileges
- Generic Components
- Computing a random value
- Encrypting a password
- Privilege Management Components
- User Management Security Components
- Adding a new user: Assigning identifiers with an Oracle sequence
- Authenticating by username and password
- Web-Based Security Components
- User Interface Components
- Support utility: The loginweb package
- The login screen: login.psp
- Password-Protecting the Book Editing Page
- Define privilege constants
- Protect the book package
- Add a session id to the book editing page
- Protect the book editing page
- Handle login-related exceptions
- Still More PL/SQL Features
- Object Types
- Large Objects
- XML
- The Code Profiler
- (Pipelined) Table Functions
- More Built-in Packages and Types
- "Making Good" of Database Programming
- The Evidence
- The Problem
- Answering the Objections
- What to Do
- Glossary
- Index
