background image

<< Contents | Contents 3 >>

<< Contents | Contents 3 >>

Contents 2

iv

Using Datetime Functions ...................................................................................................... 2-18
Using Data Type Conversion Functions............................................................................... 2-20
Using Aggregate Functions .................................................................................................... 2-23
Using NULL Value Functions................................................................................................ 2-25
Using Conditional Functions ................................................................................................. 2-26

Adding, Changing, and Deleting Data

............................................................................................. 2-27

Inserting Information...................................................................................................................... 2-27
Updating Information .................................................................................................................... 2-28
Deleting Information ...................................................................................................................... 2-29

Controlling Transactions

...................................................................................................................... 2-30

Committing Transaction Changes ................................................................................................ 2-30
Rolling Back Transaction Changes ............................................................................................... 2-31
Setting Savepoints ........................................................................................................................... 2-32

3 Creating and Using Database Objects

Using Data Types

...................................................................................................................................... 3-1

Creating and Using Tables

...................................................................................................................... 3-2

Creating a Table.................................................................................................................................. 3-3
Ensuring Data Integrity..................................................................................................................... 3-6

Understanding Types of Data Integrity Constraints ............................................................. 3-6
Adding Integrity Constraints .................................................................................................... 3-7

Adding Data to a Table, Modifying, and Deleting..................................................................... 3-13
Indexing Tables ............................................................................................................................... 3-17
Dropping Tables.............................................................................................................................. 3-20

Using Views

............................................................................................................................................ 3-21

Creating a View ............................................................................................................................... 3-21
Updating a View ............................................................................................................................. 3-23
Dropping a View ............................................................................................................................. 3-24

Using Sequences

.................................................................................................................................... 3-25

Creating a Sequence........................................................................................................................ 3-25
Dropping a Sequence...................................................................................................................... 3-27

Using Synonyms

.................................................................................................................................... 3-28

4 Developing and Using Stored Procedures

Overview of Stored Procedures

............................................................................................................. 4-1

Creating and Using Standalone Procedures and Functions

............................................................. 4-2

Creating Procedures and Functions ................................................................................................ 4-2
Modifying Procedures and Functions............................................................................................. 4-7
Testing Procedures and Functions .................................................................................................. 4-7
Dropping Procedures and Functions .............................................................................................. 4-8

Creating and Using Packages

................................................................................................................. 4-9

Guidelines for Packages ................................................................................................................. 4-10
Creating a Package.......................................................................................................................... 4-11
Modifying a Package ...................................................................................................................... 4-12
Dropping a Package........................................................................................................................ 4-14

Using Variables and Constants

........................................................................................................... 4-15

PL/SQL Data Types........................................................................................................................ 4-15