Oracle PL/SQL编程入门：第二版精华解读

需积分: 8 45 浏览量更新于2024-07-18 收藏 15.11MB PDF 举报

" Beginning Oracle PL-SQL 2nd Edition 是一本针对初学者的Oracle PL/SQL编程教程，书中通过丰富的实例来讲解相关概念和技术。" 本书旨在帮助读者掌握编写Oracle数据库存储过程的基础知识，存储过程是PL/SQL的核心应用，它是SQL语言的扩展，用于创建在数据库中执行的程序。本书涵盖了以下主要知识点： 1. **Chapter 1: Relational SQL** - 这一章介绍了关系型SQL的基础，包括查询语句（SELECT）、数据定义（CREATE TABLE）、数据操纵（INSERT, UPDATE, DELETE）以及数据库约束等基本概念，这些都是理解PL/SQL的基础。 2. **Chapter 2: Blocks, Functions, and Procedures** - 详细讲解了PL/SQL中的程序结构，如BEGIN-END块、条件控制（IF-THEN-ELSIF-ELSE, CASE）和循环（WHILE, FOR）。此外，还涵盖了自定义函数和过程的创建与调用。 3. **Chapter 3: Types, Variables, and Scope** - 讨论了变量的声明和使用，包括内置数据类型和用户定义的数据类型。同时，介绍了作用域的概念，理解变量在何处可见和何时消失对于编写可维护的代码至关重要。 4. **Chapter 4: Single Row Processing** - 针对单行数据处理，讲解了如何在PL/SQL中操作单个数据库记录，包括游标（CURSOR）的使用和处理。 5. **Chapter 5: Multirow Processing** - 深入介绍如何处理多行数据，如集合类型（TABLE, VARRAY, INDEX BY TABLE）和 bulk collect 运算符，以及在循环中处理多行数据的方法。 6. **Chapter 6: Object-Relational SQL** - 展示了对象关系特性，包括对象类型、对象变量、对象集合以及继承和多态性等面向对象的概念在Oracle中的实现。 7. **Chapter 7: Troubleshooting** - 提供了调试和解决PL/SQL程序中问题的技巧，包括错误处理（EXCEPTION）和日志记录。 8. **Chapter 8: Testing** - 讲解如何编写和执行测试用例以确保代码质量，包括单元测试和集成测试的策略。 9. **Chapter 9: Documenting** - 强调代码文档的重要性，提供了注释规范和工具，以提高代码的可读性和可维护性。 10. **Chapter 10: Examples** - 提供了大量的实例，以便读者可以实践和巩固所学知识。 11. **Appendix A: How to Download, Install, and Use Oracle** - 附录部分提供了下载、安装和使用Oracle数据库的指南，对于初学者来说非常实用。全书通过系统性的章节设计和丰富的实例，让读者逐步掌握Oracle PL/SQL编程，无论你是数据库管理员还是开发人员，都能从中受益。同时，书后索引方便读者快速查找所需信息，提升学习效率。

CHAPTER 1 ■ RELATIONAL SQL

You can find which publications were written by each genius simply by using the common datum in

both of these tables: the ID. If you look at Codd’s data in Table 1-1, you see he has ID 100. Next, if you look at

ID 100’s (Codd’s) data in Table 1-2, you see he has written two titles:

A Relational Model of Data for Large Shared Data Banks (1970)•

The Relational Model for Database Management (1990)•

These two tables have a relationship, because they share an ID column with the same values. They

don’t have many entries, so figuring out what was written by each author is pretty easy. But what would you

do if there were, say, one million authors with about three publications each? Yes! It’s time for a computer

and a database—a relational database.

A relational database allows you to store multiple tables, like Tables 1-1 and 1-2, in a database

management system (DBMS). By doing so, you can manipulate the data in the tables using a query language

on a computer, instead of a pencil on a sheet of paper. The current query language of choice is Structured

Query Language (SQL). SQL is a set of nonprocedural commands, or language if you will, which you can use

to manipulate the data in tables in a relational database management system (RDBMS).

A table in a relational database is a logical definition for how data is to be organized when it is stored.

For example, in Table 1-3, I decided to order the columns of the database genius data just as it appeared

horizontally in Table 1-1.

Table 1-2. Relational Genius’s Publications (Author Publications)

ID Author ID Title Written

10 100 A Relational Model of Data for Large Shared Data Banks 1970

20 100 The Relational Model for Database Management 1990

30 200 An Introduction to Database Systems 2003

40 200 The Third Manifesto 2000

50 200 Temporal Data and the Relational Model 2002

60 200 Database in Depth: Relational Theory for Practitioners 2005

70 300 The Third Manifesto 2000

80 300 Temporal Data and the Relational Model 2002

Table 1-3. A Table Definition for Table 1-1

Column Number Column Name Data Type

1 ID Number

2 Name Character

3 Birth Date Date

4 Gender Character

Perhaps I could give the relational table defined in Table 1-3 the name geniuses? In practice, a name

like that tends to be too specific. It’s better to find a more general name, like authors or persons. So how do

you document your database design decisions?

www.it-ebooks.info

CHAPTER 1 ■ RELATIONAL SQL

An Entity-Relationship Diagram

Just as home builders have an architectural diagram or blueprint, which enables them to communicate

clearly and objectively about what they are going to build, so do relational database builders. In our case,

the architectural diagram is called an entity-relationship diagram (ERD).

Figure 1-1 is an ERD for the tables shown in Tables 1-1 and 1-2, now named authors and

author_publications. It shows that one author may have zero or more publications. Additionally, it shows

that an author’s ID is his primary key (PK), or unique way to identify him, and an author’s primary key is

also used to identify his publications.

ERDs, like blueprints, may have varying levels of detail, depending on your audience. For example, to

simplify the ERD in Figure 1-1, I’ve left out the data types.

In an ERD, the tables are called entities. The lines that connect the tables—I mean entities—are called

relations. So given an ERD, or even a narrative documentation as in Table 1-3, you can write a script to create

a table.

Data Definition Language (DDL)

To create the authors table, as defined in Table 1-3, in Oracle, you’ll need to create a SQL script

(for SQL*Plus) that in SQL jargon is called Data Definition Language. That is, it’s SQL for defining the

relational database. Listing 1-1 shows the authors table’s DDL.

Listing 1-1. DDL for Creating the Author Table, authors.tab

1 CREATE TABLE authors (

2 id number(38),

3 name varchar2(100),

4 birth_date date,

5 gender varchar2(30) );

The syntax for the CREATE TABLE statement used in Listing 1-1 is as follows:

CREATE TABLE <table_name> (

<column_name_1> <data_type_1>,

<column_name_2> <data_type_2>,

<column_name_N> <data_type_N> );

where <table_name> is the name of the table, <column_name> is the name of a column, and <data_type> is

one of the Oracle data types.

AUTHORS

PK id

name

birth_date

gender

AUTHOR_PUBLICATIONS

FK 1 author_id

PK id

title

written_date

Figure 1-1. An entity-relationship diagram for the authors and author publications tables

www.it-ebooks.info

CHAPTER 1 ■ RELATIONAL SQL

The following are the Oracle data types you’ll use most often:

• VARCHAR2: Allows you to store up to 32,767 bytes (4,000 in versions prior to 12c) ,

data like ABCD..., in a column. You must define the maximum number of characters

(or constrain the number of characters) by specifying the desired number in

parentheses after the keyword VARCHAR2. For example, in Listing 1-1, line 3 specifies

varchar2(100), which means a name can be up to 100 characters in length.

• NUMBER: Allows you to store a decimal number with 38 digits of precision. You do not

need to constrain the size of a number, but you can. You can specify the maximum

number of digits (1, 2, 3, 4 . . .) to the left of a decimal point, followed by a comma (,),

and optionally the maximum number of decimal digits to the right of the decimal

point, by specifying the desired constraint in parentheses after the keyword NUMBER.

For example, if you want to make sure that the id column in Listing 1-1, line 2 is an

integer, you could specify number(38).

• DATE: Allows you to store a date and time value. One great thing about Oracle DATE

types is that they allow you to easily perform time calculations between dates;

just subtract an earlier date from a later date. In addition, it’s also easy to convert

between VARCHAR2 and DATE values. For example, if you want to store the date and

time of birth for someone born on January 1, 1980 at 8 a.m., you could use the Oracle

to_date() function to convert a VARCHAR2 value in a proper format into a DATE data

type as follows: to_date('19800101080000', 'YYYYMMDDHH24MISS').

Of course, there are other Oracle data types, but you’ll end up using VARCHAR2, NUMBER, and DATE most of

the time. The rest are specialized versions of these three, or they are designed for specialized uses.

It’s Your Turn to Create a Table

OK, it’s time to stop reading and start doing. This section gives you chance to put into practice some of what

you’ve learned so far. I’ll be asking you to perform exercises like this as we go along in order for you to verify

that you understood what you just read well enough to put it into practice.

Tip ■ First, if you don’t have access to Oracle, see this book’s appendix. Second, create a directory for the

book’s source code listings. Next, download the source code listings from the Source Code/Download section

of the Apress web site (www.apress.com), and work within the book’s source code listing directory structure

for each chapter. Add your source code listings to the book’s directory structure as you work. Last, create

a shortcut, an icon, or whatever you want to call it, to start SQL*Plus from your working source code listing

directory. Then you won’t need to specify the entire path when trying to execute your scripts in SQL*Plus.

To create the author_publications table, follow these steps.

1. Start your Oracle database if it’s not already running.

2. Start SQL*Plus, logging in as username RPS with password RPS.

3. Open your favorite text editor.

4. Write a DDL script to create a table for author_publications, as in Table 1-2.

www.it-ebooks.info

CHAPTER 1 ■ RELATIONAL SQL

5. Save the DDL script with the same filename as your table name,

but with a .tab extension.

6. Execute your script in SQL*Plus by typing an at sign (@) followed by the filename

at the SQL> prompt.

If you didn’t have any errors in your script, the table will now exist in the database, just waiting for you

to populate it with data. If it didn’t work, try to make some sense out of the error message(s) you got, correct

your script, and then try again.

Listing 1-2 is my try.

Listing 1-2. DDL for Creating the Publication Table, author_publications.tab

1 CREATE TABLE author_publications (

2 id number(38),

3 title varchar2(100),

4 written_date date );

Now you know how to create a table, but what if you put a lot of data in those two tables? Then

accessing them will be very slow, because none of the columns in the tables are indexed.

Indexes

Imagine, if you will, that a new phone book shows up on your doorstep. You put it in the drawer and throw

out the old one. You pull the phone book out on Saturday evening so you can order some pizza, but when

you open it, you get confused. Why? Well, when they printed the last set of phone books, including yours,

they forgot to sort it by last name, first name, and street address. Instead, it’s not sorted at all! The numbers

show up the way they were put into the directory over the years—a complete mess.

You want that pizza! So you start at the beginning of the phone book, reading every last name, first

name, and address until you finally find your local pizza place. Too bad it was closed by the time you found

the phone number! If only that phone book had been sorted, or properly indexed.

Without an index to search on, you had to read through the entire phone book to make sure you got the

right pizza place phone number. Well, the same holds true for the tables you create in a relational database.

You need to carefully analyze how data will be queried from each table, and then create an appropriate

set of indexes. You don’t want to index everything, because that would unnecessarily slow down the process

of inserting, updating, and deleting data. That’s why I said “carefully.”

DDL Again

You should (must, in my opinion) create a unique index on each table’s primary key column(s)—you know,

the one that uniquely identifies entries in the table. In the authors table, that column is id. However, you

will create a unique index on the id column when we talk about constraints in the next section. Instead, let’s

create a unique index on the name, birth_date, and gender columns of the authors table. Listing 1-3 shows

the example.

Listing 1-3. DDL for Creating an Index on the Author Table, authors_uk1.ndx.

1 CREATE UNIQUE INDEX authors_uk1

2 on authors (

3 name,

4 birth_date,

5 gender );

www.it-ebooks.info

CHAPTER 1 ■ RELATIONAL SQL

The syntax for the CREATE INDEX statement is as follows:

CREATE [UNIQUE] INDEX <index_name>

on <table_name> (

<column_name_1>,

<column_name_2>,

<column_name_N> );

where <index_name> is the name of the index, <table_name> is the name of the table, and <column_name>

is the name of a column. The keyword UNIQUE is optional, as denoted by the brackets ([ ]) around it. It just

means that the database must check to make sure that the column’s combination of the values is unique

within the table. Unique indexes are old-fashioned; now it’s more common to see a unique constraint, which

I’ll discuss shortly.

It’s Your Turn to Create an Index

OK, it’s time to stop listening to me talk about indexes, and time for you to start creating one. Here’s the drill.

1. Write a DDL script to create a non-unique index on the author_publications table.

2. Save the DDL script with the same file name as your index name, but with an

.ndx extension.

3. Execute your script in SQL*Plus by typing an at sign (@) followed by the file name

at the SQL> prompt.

You should now have a non-unique index in the database, just waiting for you to query against it. If not,

try to make some sense out of the error message(s) you got, correct your script, and then try again.

My shot at this is shown in Listing 1-4.

Listing 1-4. DDL for Creating an Index on the Title Column in the Publication Table,

author_publications_k1.ndx

1 CREATE INDEX author_publications_k1

2 on author_publications (

3 title );

Now you know how to create a table and an index or two, to make accessing it efficient, but what

prevents someone from entering bad data? Well, that’s what constrains are for.

Constraints

Constraints are rules for a table and its columns that constrain how and what data can be inserted, updated,

or deleted. Constraints are available for both columns and tables.

Column Constraints

Columns may have rules that define what list of values or what kind of values may be entered into them.

Although there are several types of column constraints, the one you will undoubtedly use most often is NOT

NULL. The NOT NULL constraint simply means that a column must have a value. It can’t be unknown, or blank,

or in SQL jargon, NULL. Listing 1-5 shows the authors table script updated with NOT NULL constraints on the

id and name columns.

www.it-ebooks.info

剩余479页未读，继续阅读

Flyrain1015

粉丝: 0
资源: 1

Oracle PL/SQL编程入门：第二版精华解读

Beginning Oracle PL-SQL(Apress,2ed,2015)

C# WinForms YOLOv11-ONNX实例分割模型部署（包含详细的完整的程序和数据）

API接口详解与使用方法

Pycluster-1.59-cp38-cp38-win_amd64.whl

nx二次开发.docx

ujson-5.3.0-cp311-cp311-win_amd64.whl

Java项目: 基于SpringBoot+mysql+vue学生选课系统(含源码+数据库+答辩PPT+开题报告+毕业论文)

GNU 8.1.0直接解压缩可用版

JSON-Fortran is a user-friendly, thread-safe, and object-oriente

代码生成器-【service模板】

最新资源