Oracle PL-SQL入门指南：编写存储过程详解

Oracle

需积分: 9 52 浏览量更新于2024-07-19 收藏 14.65MB PDF 举报

身份认证购VIP最低享 7 折!

领优惠券(最高得80元）

《入门Oracle PL/SQL编程》是一本专为理解并掌握Oracle数据库中存储过程编程而编写的教程。在当今IT行业中，Oracle作为一款强大的关系型数据库管理系统，其PL/SQL语言对于数据管理和业务逻辑实现至关重要。本书以详尽的章节结构引导读者逐步探索PL/SQL的世界。首先，"Relational SQL"章节涵盖了SQL基础知识，讲解如何在Oracle环境下执行关系型查询和操作，这对于理解后续PL/SQL编程的基础至关重要。SQL是PL/SQL的一部分，它提供了一种标准的方式来与数据库交互，包括数据检索、更新和删除等操作。接下来，"Blocks, Functions, and Procedures"章节深入探讨了PL/SQL的基本构造块，如块（Blocks）、函数（Functions）和过程（Procedures）。这些是程序设计的核心元素，它们允许开发者组织代码、实现可重用逻辑和封装复杂操作。 "Types, Variables, and Scope"部分介绍了PL/SQL中的数据类型、变量以及作用域，这对于处理和管理数据库中的数据至关重要。理解这些概念有助于编写更高效、可维护的代码。 "Single Row Processing"和"Multirow Processing"章节分别关注单行操作和多行操作，解释了如何处理不同数量的数据集，这对于处理批量数据或循环逻辑时极为实用。 "Object-Relational SQL"章节则扩展了SQL的概念，介绍对象关系模型，使读者能够更好地利用Oracle的面向对象特性进行更高级的数据管理和操作。 "Troubleshooting"章节提供了故障排除的技巧和策略，帮助读者识别并解决在编程过程中可能出现的问题，提升解决问题的能力。 "Testing"章节着重于测试方法和工具，确保PL/SQL程序的正确性和可靠性，这是任何开发流程中不可或缺的一环。 "Documenting"和"Examples"章节强调了代码文档化的重要性，同时给出了实际的编程示例，帮助读者通过实例学习和实践。最后，附录A介绍了如何下载、安装和使用Oracle，以及PL/SQL开发环境的配置，这对于初次接触Oracle的读者来说非常实用。综上，这本书不仅涵盖了PL/SQL语言的基础知识，还深入到实际应用中，旨在帮助读者成为具备Oracle PL/SQL编程技能的专业人士。通过阅读这本书，无论是初学者还是进阶者，都能系统地提升在Oracle数据库中的编程能力。

资源详情

资源推荐

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?

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

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.

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 );

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.

剩余479页未读，继续阅读

sygwin_net

粉丝: 40
资源: 43

Oracle PL-SQL入门指南：编写存储过程详解

Beginning Oracle SQL

Oracle Sql基础（beginning oracle sql中文版）

130|console:/ # logcat -s alogd --------- beginning of kernel --------- beginning of system --------- beginning of main

--------- beginning of system --------- beginning of main这两条是什么意思？

--------- beginning of system --------- beginning of main

--------- beginning of system 和 --------- beginning of main 区别

echo $ORACLE_HOME SP2-0734: unknown command beginning "echo $ORAC..." - rest of line ignored.

oracle declare

sqlserver的数据导入ES里

(error) ERR unknown command `redis-cli`, with args beginning with: `-a`, `123456`,

--------- beginning of crash找原因

(error) ERR unknown command `redis-cli`, with args beginning with: `-a`, `redispwd`, `--start`,

--------- beginning of crash之后怎么找原因

(error) ERR unknown command `redis-cli`, with args beginning with: `--eval`, `test.lua`, `5`,这是什么异常

卡夫卡客户端如何用时间索引偏移量

kafka中 --from-beginning的意思

./kafka-console-consumer.sh --bootstrap-server <kafka-broker>:<port> --topic <topic> 最后几条消息

ERR unknown command `redis-cli`, with args beginning with: `-h`, `192.168.3.249`, `-p`, `6379`,

最新资源