深入学习PL/SQL：21天教程（第2版）

PLSQL

需积分: 3 30 浏览量更新于2024-08-02 收藏 8.99MB PDF 举报

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

30元优惠券

"Teach Yourself PLSQL in 21 Days, 2nd Edition.pdf" 这篇PDF文档是《21天学会PL/SQL》的第二版，由Sams Publishing出版，版权于2000年。这本书旨在帮助读者在21天内掌握Oracle数据库的PL/SQL编程语言。 PL/SQL（Procedural Language/Structured Query Language）是Oracle数据库系统中的一个强类型、结构化编程语言，它结合了SQL查询功能和过程式编程的特点，用于数据库管理、数据处理和应用开发。通过学习PL/SQL，开发者可以编写存储过程、函数、触发器、游标以及处理数据库错误，从而实现对数据库的高级操作和复杂的业务逻辑。在书中，作者会逐步引导读者了解以下核心知识点： 1. **基础语法**：包括变量声明、数据类型、常量、运算符、流程控制语句（如IF-THEN-ELSE、CASE、WHILE、FOR循环）以及异常处理。 2. **PL/SQL块结构**：PL/SQL程序由声明部分、执行部分和异常处理部分组成，理解块结构是学习PL/SQL的基础。 3. **游标**：游标是处理单行记录的机制，常用于循环遍历查询结果集，进行逐行处理。 4. **存储过程和函数**：如何定义、调用和管理这些可重用的代码单元，以及如何返回值和参数传递。 5. **触发器**：触发器是在特定数据库事件（如INSERT、UPDATE、DELETE）发生时自动执行的PL/SQL代码，用于实现业务规则或审计需求。 6. **包（Packages）**：包是组织相关过程、函数和类型的一个集合，有助于代码管理，提高代码复用性。 7. **索引和性能优化**：了解如何使用PL/SQL与数据库的索引配合，以提升查询性能。 8. **事务处理**：掌握事务的开始、提交、回滚以及并发控制，确保数据一致性。 9. **错误处理和调试**：学习如何捕获和处理PL/SQL运行时错误，以及使用DBMS_DEBUG_JDWP等工具进行调试。 10. **数据库连接和数据操纵**：学习如何使用PL/SQL语句连接数据库，进行数据的插入、更新、删除和查询。此外，书中的实例和练习将帮助读者巩固所学，确保他们在21天的学习过程中能逐步掌握PL/SQL编程技能。尽管书中的内容可能已经过时（考虑到出版日期是2000年），但PL/SQL的基本概念和使用方法仍然适用于现代Oracle数据库版本。请记住，虽然书籍提供了学习路线图，但实际掌握任何技术都需要实践和时间。阅读本书的同时，搭配实际的数据库操作将有助于更好地理解和应用PL/SQL知识。

资源详情

资源推荐

Contents xv

The GETLENGTH Function ................................................................................420

The

INSTR Function ........................................................................................420

The

READ Procedure ........................................................................................421

The

SUBSTR Function ......................................................................................421

The

TRIM Procedure ........................................................................................422

The

WRITE Procedure ......................................................................................422

Exploring Internal

LOB Examples, Using the DBMS_LOB Package ........................423

Creating a Table..............................................................................................423

Adding Data to the Table................................................................................423

Populating the

LOBS Table with the COPY Procedure ......................................424

Manipulating Internal

LOBs by Using APPEND and WRITE ..............................425

Analyzing the Contents of an Internal

LOB ....................................................427

Using

TRIM and ERASE to Edit CLOBs ..............................................................428

Temporary

LOBs....................................................................................................430

Managing Temporary

LOBs ............................................................................430

Creating Temporary

LOBs................................................................................430

Summary..............................................................................................................431

Q&A ....................................................................................................................431

Workshop ............................................................................................................432

Quiz ................................................................................................................432

Exercise ..........................................................................................................432

WEEK 2 In Review 433

WEEK 3 At a Glance 435

AY 15 Managing Transactions and Locks 437

Types of Transactions ..........................................................................................438

Starting a Transaction ..........................................................................................438

Ending a Transaction ..........................................................................................439

Canceling a Transaction ......................................................................................441

Exploring the Two-Phase Commit ......................................................................441

Creating Bookmarks with Savepoints ................................................................442

Using Release Option ..........................................................................................443

Using Locking......................................................................................................443

Locking Tables ..............................................................................................443

Locking Rows ................................................................................................445

Explicit Locks ................................................................................................445

The

DBMS_LOCK Package..................................................................................446

Using Other Locks..........................................................................................449

Monitoring Locks ..........................................................................................449

00 7982 fm 11/30/99 1:24 PM Page xv

Summary..............................................................................................................450

Q&A ....................................................................................................................450

Workshop ............................................................................................................450

Quiz ................................................................................................................450

Exercise ..........................................................................................................450

AY 16 Generating Dynamic SQL 451

What Is Dynamic SQL and Why Use It? ............................................................452

Two Approaches to Dynamic SQL......................................................................452

Using the

DBMS_SQL Package................................................................................453

Using the

DBMS_SQL Package with Non-Query DDL and DML

Statements....................................................................................................454

Using the

DBMS_SQL Package with Queries ....................................................460

Using the

DBMS_SQL Package with Anonymous PL/SQL Blocks ..................467

DBMS_SQL Error Handling................................................................................471

Using

LAST_ROW_COUNT ..................................................................................471

Using

LAST_ROW_ID ........................................................................................471

Using

LAST_ERROR_POSITION..........................................................................474

Using Native Dynamic SQL................................................................................474

Executing DML and DDL..............................................................................474

Executing SQL Queries..................................................................................476

Executing PL/SQL Blocks ............................................................................479

Summary..............................................................................................................480

Q&A ....................................................................................................................480

Workshop ............................................................................................................481

Quiz ................................................................................................................481

Exercise ..........................................................................................................481

AY 17 Writing to Files and the Display 483

Exploring the

DBMS_OUTPUT Package ..................................................................484

Enabling the

DBMS_OUTPUT Package................................................................484

Disabling the

DBMS_OUTPUT Package ..............................................................485

Writing Data to the Buffer ............................................................................486

Reading Data from the Buffer........................................................................487

Exceptions Raised from the

DBMS_OUTPUT Package ......................................491

Reading and Writing Files with the

UTL_FILE Package ......................................491

File Input ........................................................................................................492

Using

UTL_FILE Procedures and Functions....................................................493

An Example of Using

UTL_FILE ....................................................................500

Exploring

TEXT_IO with Client Files ..................................................................504

Summary..............................................................................................................506

Q&A ....................................................................................................................506

Workshop ............................................................................................................507

Quiz ................................................................................................................507

Exercise ..........................................................................................................507

xvi Sams Teach Yourself PL/SQL in 21 Days, Second Edition

00 7982 fm 11/30/99 1:24 PM Page xvi

DAY 18 Managing Database Jobs 509

Submitting Jobs by Using the

DBMS_JOB Package ..............................................510

Using Background Processes ..............................................................................510

Executing Jobs ....................................................................................................512

Submitting Jobs to the Job Queue by Using

SUBMIT......................................512

Using

RUN to Execute Jobs Immediately ........................................................514

Exploring the Job Environment......................................................................515

Viewing Jobs........................................................................................................517

Samples for Viewing Jobs ..............................................................................519

Exploring Job Management ................................................................................519

Removing a Job..............................................................................................520

Altering a Job ................................................................................................520

Importing and Exporting Jobs ........................................................................521

Handling Broken Jobs ....................................................................................521

Hands-on Practice in Job Management ..............................................................522

Creating Procedures to Submit as Jobs ..........................................................522

Submitting All Jobs to the Job Queue............................................................524

Running All Three Jobs Immediately ............................................................524

Viewing Information About the Jobs ............................................................525

Altering the Job by Running the

HELLO Job ..................................................525

Removing the Hello Job ................................................................................526

Summary..............................................................................................................528

Q&A ....................................................................................................................528

Workshop ............................................................................................................529

Quiz ................................................................................................................529

Exercises ........................................................................................................529

AY 19 Alerting and Communicating with Other Procedures:

The

DBMS_ALERT and DBMS_PIPE Packages 531

The

DBMS_ALERT Package ....................................................................................532

Using

SIGNAL to Issue an Alert ............................................................................533

Registering for an Alert ......................................................................................533

Waiting for a Specific Alert ................................................................................534

Waiting for Any Registered Alert ........................................................................535

Removing One Alert ............................................................................................535

Removing All Alerts ............................................................................................536

Polling and Events with the

SET_DEFAULTS Procedure ........................................536

Demonstrating the

DBMS_ALERT Package..............................................................536

Creating the Backup Database ......................................................................537

Creating the Trigger to Signal an Alert ..........................................................538

Waiting for the Alert ......................................................................................539

The

DBMS_PIPE Package ......................................................................................543

Public Versus Private Pipes ............................................................................543

Using Pipes ....................................................................................................544

Contents xvii

00 7982 fm 11/30/99 1:24 PM Page xvii

The Functions and Procedures of DBMS_PIPE ................................................546

An Example of Using Pipes ..........................................................................550

Other Pipe Functions and Procedures ............................................................553

Other Uses for Pipes ......................................................................................555

Alerts Versus Pipes ..............................................................................................555

Summary..............................................................................................................556

Q&A ....................................................................................................................556

Workshop ............................................................................................................557

Quiz ................................................................................................................557

Exercises ........................................................................................................558

AY 20 PL/SQL and Java 559

Using Oracle’s Java Engine ................................................................................560

Will PL/SQL Go Away? ......................................................................................561

Creating a Java Procedure....................................................................................561

Prerequisites Needed ......................................................................................561

The

SS_CONSTANTS Class ................................................................................562

Loading the Class ..........................................................................................563

Publishing the

SSThresh Method ..................................................................565

Calling

SS_THRESH from SQL*Plus................................................................566

Calling

SS_THRESH from PL/SQL ..................................................................567

Dropping the

SSConstants Class ..................................................................568

Calling Java from PL/SQL ..................................................................................569

The

Employee Class ........................................................................................569

Publishing a Java Method ..............................................................................571

Datatype Compatibility ..................................................................................574

Using the

OUT and IN OUT Arguments............................................................575

Calling PL/SQL from Java Using SQLJ..............................................................578

Using SQLJ to Execute an Anonymous PL/SQL Block................................578

Using SQLJ to Call a PL/SQL Procedure or Function ..................................579

Summary..............................................................................................................582

Q&A ....................................................................................................................583

Workshop ............................................................................................................583

Quiz ................................................................................................................584

Exercise ..........................................................................................................584

AY 21 Advanced Queuing 585

What Is AQ? ........................................................................................................586

Removing Some of the Mystery from AQ ....................................................586

Key Features of AQ ........................................................................................587

Configuring Your Server to Use AQ ....................................................................588

Exploring an AQ Scenario ..................................................................................590

Creating a Message Queue ..................................................................................591

xviii Sams Teach Yourself PL/SQL in 21 Days, Second Edition

00 7982 fm 11/30/99 1:24 PM Page xviii

Defining a Type for the Payload ....................................................................591

Creating the Queue Table ..............................................................................592

Creating the Queue ........................................................................................595

Starting and Stopping a Queue ............................................................................597

Placing Messages in a Queue ..............................................................................598

Enqueue-Related Record Types ....................................................................599

Enqueuing a Message ....................................................................................602

Retrieving Messages from a Queue ....................................................................604

Dequeue-Related Record Types ....................................................................604

Dequeuing a Message ....................................................................................606

Using Other AQ Procedures ..............................................................................608

Dropping a Queue ..........................................................................................608

Dropping a Queue Table ................................................................................609

Granting and Revoking Access to a Queue....................................................609

Summary..............................................................................................................610

Q&A ....................................................................................................................611

Workshop ............................................................................................................611

Quiz ................................................................................................................611

Exercises ........................................................................................................612

WEEK 3 In Review 613

PPENDIX A Answers 615

Index 659

Contents xix

00 7982 fm 11/30/99 1:24 PM Page xix

剩余716页未读，继续阅读

parkusher

粉丝: 1
资源: 87

深入学习PL/SQL：21天教程（第2版）

PLSQL.Developer8.0.4.1514中文注册安装包.ISO

plsql_developer7.0中文手册.pdf

PLSQL+Developer10.0.3.1701.rar

人大金仓提供的sql和plsql速查手册.pdf

sql和plsql速查手册.pdf

PLSQL 执行计划 oracle,plsql执行计划,统计信息.docx

plsql if else if

PLSQL数据库在动态视图iih.v_szsh_yaofang中添加新的去除重复值过滤条件

plsql in 字符串

ebs plsql 输出pdf报表

帮我出具一份ngnix负载均衡和plsql主从库的配置书

plsql 提示 Access violation at address 0000000000411B6B in module 'plsqldev.exe' Read of address 00000000000E980是什么意思

PLSQL怎么设置中文

彻底卸载plsqldeveloper

plsql 12 无法加载oci.dll

plsql与oracle的tnsnames.ora

plsqldev901.exe

Oracle-PLSQL-银行管理系统简单实现.

PLSQl在动态视图iih.v_szsh_yaofang中添加新的去除重复值过滤条件

plsql怎么设置字体大小

最新资源