Oracle PL / SQL For Dummies

Michael Rosenblum, Paul Dorsey

• Publisher: For Dummies
• Year: 2011
• ISBN:
  9781118054796
• Edition: 1
• Topic: Computer Science
• Subtopic: Software Development
• Index: Computer Science

Find tips for creating efficient PL/SQL code

If you know a bit about SQL, this book will make PL/SQL programming painless!

The Oracle has spoken—you need to get up to speed on PL/SQL programming, right? We predict it'll be a breeze with this book! You'll find out about code structures, best practices, and code naming standards, how to use conditions and loops, where to place PL/SQL code in system projects, ways to manipulate data, and more.

Discover how to

• Write efficient, easy-to-maintain code
• Test and debug PL/SQL routines
• Integrate SQL and PL/SQL
• Apply PL/SQL best practices
• Use new features introduced in Oracle 9i and 10g

Part I

Basic PL/SQL Concepts

In this part . . .

Part I includes two chapters to get you started with PL/SQL. Because you need to understand something about relational databases to be a good PL/SQL programmer, Chapter 1 provides a quick overview of the most important concepts. It also reviews some database terminology and explains some of the differences between SQL and PL/SQL. For those with very little programming experience, Chapter 1 includes a very brief explanation of the basic structure and syntax of PL/SQL and explains where it is most useful.

Chapter 2 describes the total PL/SQL environment and explains how to set up this environment so you can begin writing code. You discover how to set up a database, connect to it, and access the Oracle sample schemas that you can use to practice.

Chapter 1

PL/SQL and Your Database

In This Chapter

Getting to know relational databases

Understanding database terminology

Finding out about Oracle

Using SQL and PL/SQL

Discovering what PL/SQL is good for

PL/SQL is an extension to the industry-standard SQL language. Oracle Corporation developed PL/SQL and released the first version in 1991. PL/SQL is an easy-to-use procedural language that interacts seamlessly with the Oracle database. Server-side PL/SQL is part of the Oracle database and needs no explicit installation or licensing.

This chapter introduces you to PL/SQL and provides some basics about relational databases.

Knowing Just Enough about Relational Databases

Building a system in Oracle or some other relational database product does not automatically make it a relational database. Similarly, you can design a perfectly good relational database and implement it in something other than a relational database product. We discuss two important areas:

What do people mean by relational database?

What is the Oracle relational database product?

What makes a database “relational”?

When a database is described as relational, it has been designed to conform (at least mostly) to a set of practices called the rules of normalization. A normalized database is one that follows the rules of normalization.

For example, in an organization, you have employees who work in specific departments. Each employee and department has a number and a name. You could organize this information as shown in Table 1-1.

If you structure your data this way and make certain changes to it, you’ll have problems. For example, deleting all the employees in the Purchasing department will eliminate the department itself. If you change the name of the Marketing department to “Advertising,” you would need to change the record of each employee in that department.

Using the principles of relational databases, the Employee and Department data can be restructured into two separate tables (DEPT and EMP), as shown in Tables 1-2 and 1-3.

Table 1-2 A Sample Relational DEPT Table

DeptNo	DeptName
10	Marketing
20	Purchasing

Table 1-3 A Sample Relational EMP Table

EmpNo	EName	DeptNo
101	Abigail	10
102	Bob	20
103	Carolyn	10
104	Doug	20
105	Evelyn	10

By using this structure, you can examine the EMP table to find out that Doug works in department 20. Then you can check the DEPT table to find out that department 20 is Purchasing. You might think that Table 1-1 looks more efficient. However, retrieving the information you need in a number of different ways is much easier with the two-table structure. Joining the information in the two tables for more efficient retrieval is exactly the problem that relational databases were designed to solve.

When the tables are implemented in the database, the information in the two tables is linked by using special columns called foreign keys. In the example, the DeptNo column is the foreign key linking the Department and Employee tables.

Tables 1-4 and 1-5 show another common database structure, namely a purchase order (PURCH_ORDER table) for an item and the information details associated with the purchase order (PURCH_ORDER_DTL table).

Table 1-4 A Sample Relational PURCH_ORDER Table

PO_Nbr	Date
450	12/10/2006
451	2/26/2006
452	3/17/2006
453	6/5/2006

A purchase order can include many items. Table 1-5 shows that Purchase Order 451 includes three separate items. The link (forei...

Table of contents

• Title
• Contents
• Introduction
• Part I : Basic PL/SQL Concepts
• Part II : Getting Started with PL/SQL
• Part III : Standards and Structures
• Part IV : PL/SQL Data Manipulations
• Part V : Taking PL/SQL to the Next Level
• Part VI : The Part of Tens