[ORACLE][Program with PLSQL 02].pdf

(2307 KB) Pobierz
Oracle9i: Program with PL/SQL
Student Guide • Volume 2
40054GC11
Production 1.1
October 2001
D34005
Authors
Nagavalli Pataballa
Priya Nathan
Copyright © Oracle Corporation, 1999, 2000, 2001. All rights reserved.
This documentation contains proprietary information of Oracle Corporation. It is
provided under a license agreement containing restrictions on use and disclosure
and is also protected by copyright law. Reverse engineering of the software is
prohibited. If this documentation is delivered to a U.S. Government Agency of the
Department of Defense, then it is delivered with Restricted Rights and the following
legend is applicable:
Restricted Rights Legend
Use, duplication or disclosure by the Government is subject to restrictions for
commercial computer software and shall be deemed to be Restricted Rights software
under Federal law, as set forth in subparagraph (c)(1)(ii) of DFARS 252.227-7013,
Rights in Technical Data and Computer Software (October 1988).
This material or any portion of it may not be copied in any form or by any means
without the express prior written permission of Oracle Corporation. Any other copying
is a violation of copyright law and may result in civil and/or criminal penalties.
If this documentation is delivered to a U.S. Government Agency not within the
Department of Defense, then it is delivered with “Restricted Rights,” as defined in
FAR 52.227-14, Rights in Data-General, including Alternate III (June 1987).
The information in this document is subject to change without notice. If you find any
problems in the documentation, please report them in writing to Education Products,
Oracle Corporation, 500 Oracle Parkway, Box SB-6, Redwood Shores, CA 94065.
Oracle Corporation does not warrant that this document is error-free.
All references to Oracle and Oracle products are trademarks or registered
trademarks of Oracle Corporation.
All other products or company names are used for identification purposes only, and
may be trademarks of their respective owners.
Technical Contributors
and Reviewers
Anna Atkinson
Bryan Roberts
Caroline Pereda
Cesljas Zarco
Coley William
Daniel Gabel
Dr. Christoph Burandt
Hakan Lindfors
Helen Robertson
John Hoff
Lachlan Williams
Laszlo Czinkoczki
Laura Pezzini
Linda Boldt
Marco Verbeek
Natarajan Senthil
Priya Vennapusa
Roger Abuzalaf
Ruediger Steffan
Sarah Jones
Stefan Lindblad
Susan Dee
Publisher
Sheryl Domingue
Contents
Curriculum Map
I
Introduction
Course Objectives I-2
About PL/SQL I-3
PL/SQL Environment I-4
Benefits of PL/SQL I-5
Benefits of Subprograms I-10
Invoking Stored Procedures and Functions I-11
Summary I-12
Declaring Variables
Objectives 1-2
PL/SQL Block Structure 1-3
Executing Statements and PL/SQL Blocks 1-4
Block Types 1-5
Program Constructs 1-6
Use of Variables 1-7
Handling Variables in PL/SQL 1-8
Types of Variables 1-9
Using iSQL*Plus Variables Within PL/SQL Blocks 1-10
Types of Variables 1-11
Declaring PL/SQL Variables 1-12
Guidelines for Declaring PL/SQL Variables 1-13
Naming Rules 1-14
Variable Initialization and Keywords 1-15
Scalar Data Types 1-17
Base Scalar Data Types 1-18
Scalar Variable Declarations 1-22
The %TYPE Attribute 1-23
Declaring Variables with the %TYPE Attribute 1-24
Declaring Boolean Variables 1-25
Composite Data Types 1-26
LOB Data Type Variables 1-27
Bind Variables 1-28
Using Bind Variables 1-30
Referencing Non-PL/SQL Variables 1-31
DBMS_OUTPUT.PUT_LINE 1-32
Summary 1-33
Practice 1 Overview 1-35
1
iii
2
Writing Executable Statements
Objectives 2-2
PL/SQL Block Syntax and Guidelines 2-3
Identifiers 2-5
PL/SQL Block Syntax and Guidelines 2-6
Commenting Code 2-7
SQL Functions in PL/SQL 2-8
SQL Functions in PL/SQL: Examples 2-9
Data Type Conversion 2-10
Nested Blocks and Variable Scope 2-13
Identifier Scope 2-15
Qualify an Identifier 2-16
Determining Variable Scope 2-17
Operators in PL/SQL 2-18
Programming Guidelines 2-20
Indenting Code 2-21
Summary 2-22
Practice 2 Overview 2-23
3
Interacting with the Oracle Server
Objectives 3-2
SQL Statements in PL/SQL 3-3
SELECT Statements in PL/SQL 3-4
Retrieving Data in PL/SQL 3-7
Naming Conventions 3-9
Manipulating Data Using PL/SQL 3-10
Inserting Data 3-11
Updating Data 3-12
Deleting Data 3-13
Merging Rows 3-14
Naming Conventions 3-16
SQL Cursor 3-18
SQL Cursor Attributes 3-19
Transaction Control Statements 3-21
Summary 3-22
Practice 3 Overview 3-24
iv
4
Writing Control Structures
Objectives 4-2
Controlling PL/SQL Flow of Execution 4-3
IF Statements 4-4
Simple IF Statements 4-5
Compound IF Statements 4-6
IF-THEN-ELSE Statement Execution Flow 4-7
IF-THEN-ELSE Statements 4-8
IF-THEN-ELSIF Statement Execution Flow 4-9
IF-THEN-ELSIF Statements 4-11
CASE Expressions 4-12
CASE Expressions: Example 4-13
Handling Nulls 4-15
Logic Tables 4-16
Boolean Conditions 4-17
Iterative Control: LOOP Statements 4-18
Basic Loops 4-19
WHILE Loops 4-21
FOR Loops 4-23
Guidelines While Using Loops 4-26
Nested Loops and Labels 4-27
Summary 4-29
Practice 4 Overview 4-30
Working with Composite Data Types
Objectives 5-2
Composite Data Types 5-3
PL/SQL Records 5-4
Creating a PL/SQL Record 5-5
PL/SQL Record Structure 5-7
The %ROWTYPE Attribute 5-8
Advantages of Using %ROWTYPE 5-10
The %ROWTYPE Attribute 5-11
INDEX BY Tables 5-13
Creating an INDEX BY Table 5-14
INDEX BY Table Structure 5-15
Creating an INDEX BY Table 5-16
Using INDEX BY Table Methods 5-17
INDEX BY Table of Records 5-18
Example of INDEX BY Table of Records 5-19
Summary 5-20
Practice 5 Overview 5-21
5
v

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika:

Zgłoś jeśli naruszono regulamin