Oracle Database 2 Day Developer’s Guide Developer's

Oracle%20Database%202%20Day%20Developer's%20Guide

User Manual:

Open the PDF directly: View PDF PDF.
Page Count: 200 [warning: Documents this large are best viewed by clicking the View PDF Link!]

Oracle® Database
2 Day Developer's Guide
11g Release 1 (11.1)
B28843-04
March 2008
Oracle Database 2 Day Developer's Guide, 11g Release 1 (11.1)
B28843-04
Copyright © 2005, 2008, Oracle. All rights reserved.
Primary Author: Roza Leyderman
Contributors: Pat Huey, Sharon Kennedy, Simon Law, Bryn Llewellen, Chuck Murray, Mark Townsend
The Programs (which include both the software and documentation) contain proprietary information; they
are provided under a license agreement containing restrictions on use and disclosure and are also protected
by copyright, patent, and other intellectual and industrial property laws. Reverse engineering, disassembly,
or decompilation of the Programs, except to the extent required to obtain interoperability with other
independently created software or as specified by law, is prohibited.
The information contained in this document is subject to change without notice. If you find any problems in
the documentation, please report them to us in writing. This document is not warranted to be error-free.
Except as may be expressly permitted in your license agreement for these Programs, no part of these
Programs may be reproduced or transmitted in any form or by any means, electronic or mechanical, for any
purpose.
If the Programs are delivered to the United States Government or anyone licensing or using the Programs on
behalf of the United States Government, the following notice is applicable:
U.S. GOVERNMENT RIGHTS Programs, software, databases, and related documentation and technical data
delivered to U.S. Government customers are "commercial computer software" or "commercial technical data"
pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As
such, use, duplication, disclosure, modification, and adaptation of the Programs, including documentation
and technical data, shall be subject to the licensing restrictions set forth in the applicable Oracle license
agreement, and, to the extent applicable, the additional rights set forth in FAR 52.227-19, Commercial
Computer Software--Restricted Rights (June 1987). Oracle USA, Inc., 500 Oracle Parkway, Redwood City, CA
94065.
The Programs are not intended for use in any nuclear, aviation, mass transit, medical, or other inherently
dangerous applications. It shall be the licensee's responsibility to take all appropriate fail-safe, backup,
redundancy and other measures to ensure the safe use of such applications if the Programs are used for such
purposes, and we disclaim liability for any damages caused by such use of the Programs.
Oracle, JD Edwards, PeopleSoft, and Siebel are registered trademarks of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective owners.
The Programs may provide links to Web sites and access to content, products, and services from third
parties. Oracle is not responsible for the availability of, or any content provided on, third-party Web sites.
You bear all risks associated with the use of such content. If you choose to purchase any products or services
from a third party, the relationship is directly between you and the third party. Oracle is not responsible for:
(a) the quality of third-party products or services; or (b) fulfilling any of the terms of the agreement with the
third party, including delivery of products or services and warranty obligations related to purchased
products or services. Oracle is not responsible for any loss or damage of any sort that you may incur from
dealing with any third party.
iii
Contents
Preface ................................................................................................................................................................. ix
Audience....................................................................................................................................................... ix
Documentation Accessibility..................................................................................................................... ix
Related Documents ..................................................................................................................................... x
Conventions ................................................................................................................................................. x
1 Overview of Oracle Database and Development
Roadmap .................................................................................................................................................... 1-1
Overview of Oracle Database Schemas ............................................................................................... 1-2
Introducing Schema Objects............................................................................................................. 1-2
Introducing the HR Schema ............................................................................................................. 1-3
Overview of Application Development with Oracle Database ...................................................... 1-3
Introducing SQL and PL/SQL Development Languages............................................................ 1-3
Introducing SQL Developer ............................................................................................................. 1-4
Introducing SQL*Plus ....................................................................................................................... 1-5
Connecting to the Database.............................................................................................................. 1-7
Unlocking a User Account......................................................................................................... 1-7
Connecting to Oracle Database from SQL*Plus ..................................................................... 1-7
Connecting to Oracle Database from SQL Developer ........................................................... 1-7
Overview of Other Development Environments............................................................................ 1-10
2 Querying and Manipulating Data
Exploring Database Objects................................................................................................................... 2-1
Looking at Schema Object Types..................................................................................................... 2-1
Exploring Tables and Viewing Data ............................................................................................... 2-3
Retrieving Data with Queries ................................................................................................................ 2-6
Selecting Data from a Table.............................................................................................................. 2-7
Using Column Aliases....................................................................................................................... 2-8
Restricting Data to Match Specific Conditions .............................................................................. 2-8
Searching for Patterns in Data....................................................................................................... 2-11
Sorting Data ..................................................................................................................................... 2-13
Using Built-In and Aggregate Functions..................................................................................... 2-14
Using Arithmetic Operators................................................................................................... 2-14
Using Numeric Functions....................................................................................................... 2-14
Using Character Functions ..................................................................................................... 2-15
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
v
Using Variables and Constants..................................................................................................... 4-16
Using Comments...................................................................................................................... 4-16
Using Identifiers....................................................................................................................... 4-16
Declaring Variables and Constants ....................................................................................... 4-16
Declaring Variables with Structure Identical to Database Columns................................ 4-17
Assigning Values to Variables....................................................................................................... 4-19
Assigning Values with the Assignment Operator .............................................................. 4-19
Assigning Values from the Database.................................................................................... 4-20
Controlling Program Flow................................................................................................................... 4-22
Using Conditional Selection Control............................................................................................ 4-22
Using IF...THEN...ELSE Selection Control........................................................................... 4-22
Using CASE...WHEN Selection Control............................................................................... 4-24
Using Iterative Control................................................................................................................... 4-25
Using the FOR...LOOP ............................................................................................................ 4-25
Using the WHILE...LOOP....................................................................................................... 4-27
Using the LOOP...EXIT WHEN ............................................................................................. 4-28
Using Composite Data Structures; Records ..................................................................................... 4-29
Retrieving Data from a Set Using Cursors and Cursor Variables................................................ 4-32
Using Explicit Cursors.................................................................................................................... 4-33
Using Cursor Variables: REF Cursors.......................................................................................... 4-35
Using Collections; Index-By Tables................................................................................................... 4-38
Creating Cursors for Index-by Tables.......................................................................................... 4-39
Defining Index-by Tables............................................................................................................... 4-40
Populating Index-by PLS_INTEGER Tables; BULK COLLECT............................................... 4-40
Populating Index-by VARCHAR2 Tables................................................................................... 4-40
Iterating Through an Index-by Table........................................................................................... 4-41
Handling Errors and Exceptions......................................................................................................... 4-41
Existing PL/SQL and SQL Exceptions......................................................................................... 4-42
Custom Exceptions ......................................................................................................................... 4-43
5 Using Triggers
Designing Triggers................................................................................................................................... 5-1
Types of Triggers................................................................................................................................ 5-2
Timing Triggers.................................................................................................................................. 5-3
Guidelines and Restrictions for Trigger Design ............................................................................ 5-3
Creating and Using Triggers .................................................................................................................. 5-4
Creating a Statement Trigger ........................................................................................................... 5-4
Creating a Row Trigger..................................................................................................................... 5-5
Creating an INSTEAD OF Trigger................................................................................................... 5-7
Creating LOGON and LOGOFF Triggers ...................................................................................... 5-7
Modifying Triggers............................................................................................................................ 5-8
Disabling and Enabling Triggers ..................................................................................................... 5-8
Compiling Triggers............................................................................................................................ 5-9
Dropping Triggers .......................................................................................................................... 5-10
vi
6 Working in a Global Environment
Overview of Globalization..................................................................................................................... 6-1
Globalization Support Features ....................................................................................................... 6-2
Viewing the Current NLS Parameter Values................................................................................. 6-2
Using NLS Parameter Values in the SQL Developer Environment ............................................... 6-4
Changing NLS Parameter Values for All Sessions........................................................................ 6-6
Establishing a Globalization Support Environment......................................................................... 6-7
Choosing a Locale with the NLS_LANG Parameter..................................................................... 6-8
Setting NLS Parameters..................................................................................................................... 6-8
Setting Language and Territory Parameters.................................................................................. 6-9
Using the NLS_LANGUAGE Parameter................................................................................. 6-9
Using the NLS_TERRITORY Parameter............................................................................... 6-10
Setting Date and Time Parameters ............................................................................................... 6-12
Using Date Formats................................................................................................................. 6-12
Using Time Formats ................................................................................................................ 6-14
Setting Calendar Definitions ......................................................................................................... 6-15
Overview of Calendar Formats.............................................................................................. 6-15
Using the NLS_CALENDAR Parameter .............................................................................. 6-16
Using Numeric Formats................................................................................................................. 6-17
Using the NLS_NUMERIC_CHARACTERS Parameter .................................................... 6-18
Using Monetary Parameters.......................................................................................................... 6-19
Overview of Currency Formats ............................................................................................. 6-19
Using the NLS_CURRENCY Parameter............................................................................... 6-19
Using the NLS_ISO_CURRENCY Parameter...................................................................... 6-20
Using the NLS_DUAL_CURRENCY Parameter................................................................. 6-21
Using Linguistic Sort and Search.................................................................................................. 6-22
Using the NLS_SORT Parameter........................................................................................... 6-22
Using the NLS_COMP Parameter......................................................................................... 6-23
Using Case-Insensitive and Accent-Insensitive Search...................................................... 6-25
Using Length Semantics................................................................................................................. 6-25
Using the NLS_LENGTH_SEMANTICS Parameter........................................................... 6-26
Developing Globalized Applications ............................................................................................... 6-27
Overview of Unicode...................................................................................................................... 6-27
Using SQL Character Data Types................................................................................................. 6-28
Using the NCHAR Data Type................................................................................................ 6-28
Using the NVARCHAR2 Data Type..................................................................................... 6-29
Using Unicode String Literals ....................................................................................................... 6-29
NCHAR Literal Replacement........................................................................................................ 6-30
Using Locale-Dependent Functions with NLS Parameters........................................................... 6-30
Specifying NLS Parameters in SQL Functions............................................................................ 6-31
Unacceptable NLS Parameters in SQL Functions ...................................................................... 6-33
7 Deploying a Database Application
Overview of Deployment ....................................................................................................................... 7-1
Deployment Environments .................................................................................................................... 7-1
Planning for Deployment....................................................................................................................... 7-2
Exporting the Database Objects ............................................................................................................ 7-3
vii
Using SQL Developer to Export Database Objects ....................................................................... 7-3
Special Considerations for Exporting Sequences and Triggers................................................... 7-6
Generating a Script for Creating the Sequence and Tables.......................................................... 7-7
Generating a Script for Creating the PL/SQL Objects.................................................................. 7-8
Generating a Script for Creating a Synonym and a View ............................................................ 7-9
Exporting the Data ................................................................................................................................ 7-10
Performing the Installation ................................................................................................................. 7-11
Validating the Installation................................................................................................................... 7-12
Archiving the Installation Scripts...................................................................................................... 7-13
Index
viii
ix
Preface
This guide explains basic concepts behind application development with Oracle
Database. It provides instructions for using the basic features of Oracle Database
through the Structured Query Language (SQL), and Oracle Corporation's proprietary
server-based procedural extension to the SQL database language, Procedural
Language/Structured Query Language (PL/SQL).
Audience
This guide is intended for anyone who is interested in learning about Oracle Database
application development, and is primarily an introduction to application development
for developers who are new to Oracle.
Before using this guide, you should have a general understanding of relational
database concepts and an understanding of the operating system environment that
you will use to develop applications with Oracle Database.
As you become comfortable with technologies described in this guide, Oracle
recommends that you consult other Oracle Database development guides, in particular
the Oracle Database 2 Day + Application Express Developer's Guide, Oracle Database 2 Day
+ Java Developer's Guide, Oracle Database 2 Day + .NET Developer's Guide, and Oracle
Database 2 Day + PHP Developer's Guide.
Documentation Accessibility
Our goal is to make Oracle products, services, and supporting documentation
accessible, with good usability, to the disabled community. To that end, our
documentation includes features that make information available to users of assistive
technology. This documentation is available in HTML format, and contains markup to
facilitate access by the disabled community. Accessibility standards will continue to
evolve over time, and Oracle is actively engaged with other market-leading
technology vendors to address technical obstacles so that our documentation can be
accessible to all of our customers. For more information, visit the Oracle Accessibility
Program Web site at
http://www.oracle.com/accessibility/
Accessibility of Code Examples in Documentation
Screen readers may not always correctly read the code examples in this document. The
conventions for writing code require that closing braces should appear on an
otherwise empty line; however, some screen readers may not always read a line of text
that consists solely of a bracket or brace.
x
Accessibility of Links to External Web Sites in Documentation
This documentation may contain links to Web sites of other companies or
organizations that Oracle does not own or control. Oracle neither evaluates nor makes
any representations regarding the accessibility of these Web sites.
TTY Access to Oracle Support Services
Oracle provides dedicated Text Telephone (TTY) access to Oracle Support Services
within the United States of America 24 hours a day, 7 days a week. For TTY support,
call 800.446.2398. Outside the United States, call +1.407.458.2479.
Related Documents
For more information, see the following documents in Oracle Database 11g Release 1
(11.1) library:
Oracle Database Advanced Application Developer's Guide
Oracle Database Concepts
Oracle Database SQL Language Reference
Oracle Database PL/SQL Language Reference
Conventions
The following text conventions are used in this document:
Convention Meaning
boldface Boldface type indicates graphical user interface elements associated
with an action, or terms defined in text or the glossary.
italic Italic type indicates book titles, emphasis, or placeholder variables for
which you supply particular values.
monospace Monospace type indicates commands within a paragraph, URLs, code
in examples, text that appears on the screen, or text that you enter.
Overview of Oracle Database and Development 1-1
1
Overview of Oracle Database and
Development
This chapter introduces you to application development with Oracle Database.
This chapter contains the following sections:
Roadmap on page 1-1
Overview of Oracle Database Schemas on page 1-2
Overview of Application Development with Oracle Database on page 1-3
Overview of Other Development Environments on page 1-10
Roadmap
You are an Oracle Database developer, someone who has the responsibility of creating
or maintaining the database components of an application that uses the Oracle
technology stack. The discussion in this and following sections assumes that you, or
someone else in your organization, must know how to architect multiuser applications
(two tier or multitier) and understands the advantages of using a relational database
for the data persistence tier.
As a database developer, you need to know how to implement the data model that the
application requires, how to implement the rules for data integrity, and how to
implement the specified functions for accessing and manipulating the application
data.
You know already that you can access an Oracle Database only through a client
program, and that the SQL language is that client program's interface to the Oracle
Database. You will learn how to access the Oracle Database by using two clients that
are packaged with the Oracle Database and designed for developers: SQL Developer
and SQL*Plus. Both allow you to issue the SQL statements you need in order to create
and test your application's database component without doing any client
programming. Programming such clients is outside the scope of this discussion.
To briefly review widely accepted best practices in software engineering, you must
define APIs that model business functions, and hide the implementation. Oracle
Database supports this practice by letting you specify the APIs as PL/SQL
subprograms. The implementation is tables, indexes, constraints, triggers, and the
various SQL statements that modify and fetch table rows. By embedding these SQL
statements in PL/SQL subprograms, and by using Oracle's schema and privilege
mechanisms, you can securely hide the implementation from the client programs.
Many of Oracle's major customers follow this practice strictly: client programs are
allowed to access the database only by calling PL/SQL subprograms. Some customers
Overview of Oracle Database Schemas
1-2 Oracle Database 2 Day Developer's Guide
relax this rule by allowing the client to issue raw SQL SELECT statements, but
requiring it to call PL/SQL subprograms for all business functions that make changes
to the database.
This general discussion sets the charter for your job as an Oracle Database developer:
You need to know about the various types of objects that you can create in the
database, as described in "Exploring Database Objects" on page 2-1.
You need to know the SQL that is used to manage these objects: CREATE, ALTER,
TRUNCATE, and DROP. This is known as data definition language (DDL), and is
described in "Creating and Using Database Objects" on page 3-1.
You need to know the SQL language that is used to maintain application data:
INSERT, UPDATE, DELETE, and MERGE. This is known as data manipulation
language (DML), and is described in "Querying and Manipulating Data" on
page 2-1.
You need to know the SQL language for querying data: SELECT statement and its
various clauses, as described in "Retrieving Data with Queries" on page 2-6.
You need to know about transactions, and the SQL language for controlling them:
COMMIT, SAVEPOINT, and ROLLBACK, as described in "Controlling Transactions"
on page 2-30.
You need to know how to write PL/SQL subprograms and procedural code that
use DDL, DML, transaction control, and queries, as described in "Developing and
Using Stored Procedures" on page 4-1 and "Using Triggers" on page 5-1.
You need to know how to manage your deliverables and how to instantiate your
application in several different databases for the purposes of development itself,
unit testing, integration testing, end-user acceptance testing, education, and
ultimately for deploying your application in a production environment. This
information is in "Deploying a Database Application" on page 7-1.
Overview of Oracle Database Schemas
This section introduces Oracle Database schemas.
Introducing Schema Objects
Oracle Database groups related types of information into logical structures that are
called schemas. When you connect to the database by providing your user name and
password, you name the schema and indicate that you are its owner. Schemas contain
tables, which are the basic units of data storage in the database. Using a table, you can
query for information, update it, insert additional data, and delete. Each table contains
rows that represent the individual data records. The table rows are composed of
columns that represent the various fields of the record.
In addition to tables, schemas contain many other objects. Indexes are optional
structures that can improve the performance of data retrieval from tables. Indexes are
See Also:
Oracle Database Concepts for information about application
architecture
See Also:
Oracle Database Concepts
Overview of Application Development with Oracle Database
Overview of Oracle Database and Development 1-3
created on one or more columns of a table, and are automatically maintained in Oracle
Database. See "Creating and Using Tables" on page 3-2.
Depending on your business needs, you can create a view that combines information
from several different tables into a single presentation. Such views can rely on
information from other views as well as tables. See "Using Views" on page 3-21.
In an application where all records of the table must be distinct, a sequence can
generate a serial list of unique integer numbers for a numeric column that represents
the ID of each record. See "Using Sequences" on page 3-25.
A synonym is an alias for any table, view, sequence, procedure, and so on. Synonyms
are often used for security and convenience, such as masking the ownership of an
object or simplifying SQL statements. See "Using Synonyms" on page 3-28.
Schema-level procedures and functions, and also packages, are collectively known as
stored procedures. Stored procedures are blocks of code that are actually stored in the
database. They are callable from client applications that access a relational database
system. See "Developing and Using Stored Procedures" on page 4-1.
Trigg e r s are procedural code that is automatically executed by the database when
specified events occur in a particular table or view. Triggers can restrict access to
specific data, perform logging, or audit data. See "Using Triggers" on page 5-1.
Introducing the HR Schema
The hr schema is one of the sample schemas that can be installed as part of Oracle
Database. The hr sample schema contains information about employees, their
departments and locations, their work histories, and other related information. Like all
schemas, the hr schema has tables, views, indexes, procedures, functions, and all other
possible attributes of an Oracle Database schema.
You will be using and extending the hr schema to learn how to develop applications
with Oracle Database.
Overview of Application Development with Oracle Database
In this section, you will learn about two programming languages for direct data access
(SQL and PL/SQL), two development tools (SQL Developer and SQL*Plus), a sample
data set (hr schema), and how to connect to an instance of Oracle Database.
Introducing SQL and PL/SQL Development Languages
There are two broad families of computer languages: declarative languages that
describe what should be done, and imperative languages that describe how things
should be done. You are probably already familiar with the Structured Query
See Also:
Oracle Database Concepts for a comprehensive introduction to all
schema objects
See Also:
Oracle Database Sample Schemas for an in-depth description of the
hr sample schema
See Also:
Oracle Database Advanced Application Developer's Guide
Overview of Application Development with Oracle Database
1-4 Oracle Database 2 Day Developer's Guide
Language, or SQL, the database-independent language for defining database objects
and operations. SQL is a set-based, high-level declarative computer language; it
describes a problem by stating criteria for the desired data. Using SQL statements, you
can query tables to display data, create and modify objects, and perform a large
variety of administrative tasks. When you issue a SQL command, the SQL language
compiler automatically generates a procedure to access the database and perform the
desired task.
In contrast, imperative computer languages, such as C, C++, and Java, describe how
to solve the problem by finding the necessary data; they describe computation as
statements that change a program state and can therefore solve a much broader set of
problems.
Procedural Language SQL, or PL/SQL, is a native Oracle language extension to SQL.
It bridges the gap between declarative and imperative program control by adding
procedural elements, such as conditional control and iterative flow constructs. Like
SQL, PL/SQL has a built-in treatment of the relational database domain. PL/SQL
enables you to declare constants and variables, define procedures and functions, use
collections and object types, trap runtime errors, and create functions, packages,
procedures and triggers that can be stored on the database for reuse by applications
that are authored in any of the Oracle programmatic interfaces.
For more information about PL/SQL, see the PL/SQL Oracle Technology Network site
at
http://www.oracle.com/technology/tech/pl_sql/
Introducing SQL Developer
SQL Developer is a graphical user interface for accessing your instance of Oracle
Database. SQL Developer supports development in both the SQL and PL/SQL
languages. It is available in the default installation of Oracle Database. You will use
SQL Developer, both through its navigation hierarchy and through the SQL
worksheet.
Before you run SQL Developer, ensure that you have Java 1.5.0 installed. At a
command prompt, enter the following command:
java -version
Output similar to the following should appear:
java version "1.5.0_06"
Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_06-b05)
Java HotSpot(TM) Client VM (build 1.5.0_06-b05, mixed mode, sharing)
To start SQL Developer:
1. In Linux:
Click the Application menu (on Gnome) or the K menu (on KDE).
See Also:
Oracle Database SQL Language Reference
Oracle Database PL/SQL Language Reference
Oracle Database PL/SQL Packages and Types Reference
"Overview of Other Development Environments" on page 1-10
Overview of Application Development with Oracle Database
Overview of Oracle Database and Development 1-5
Select Oracle - ORACLE_HOME, then Application Development, and then
SQL Developer.
In Windows:
From the Start menu, select All Programs.
Select Oracle - ORACLE_HOME, then Application Development, and then
SQL Developer.
2. When prompted, enter the full path to the Java executable.
For example: C:\jdk1.5.0\bin\java.exe
You only need to specify this path the first time you start SQL Developer.
After the splash screen appears, SQL Developer starts.
For more information about SQL Developer, see the SQL Developer Oracle Technology
Network site at
http://www.oracle.com/technology/products/database/sql_
developer/index.html
Introducing SQL*Plus
SQL*Plus is installed together with Oracle Database. SQL*Plus has a command-line
interface for accessing Oracle Database. You can also access SQL*Plus within SQL
Developer.
To use SQL*Plus on Windows systems:
1. Click the Start icon at the bottom left corner of your screen, and select Run.
2. In the Run window, in the text prompt, enter cmd. Click OK.
See Also:
Oracle Database SQL Developer User's Guide
Overview of Application Development with Oracle Database
1-6 Oracle Database 2 Day Developer's Guide
3. In the cmd.exe window, at the c:>\ prompt, enter sqlplus and press the Enter
button of your keyboard.
SQL*Plus starts and prompts you to authenticate your connection to the database.
Your screen looks something like this:
C:\>sqlplus
SQL*Plus: Release 11.1.0.1.0 - Production on Tue April 3 10:10:11 2007
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Enter user-name:
4. Enter your user name and press Enter.
Your screen looks something like this:
Enter password:
5. Enter your password and press Enter. Note that entering your user name and
password in this manner is secure because your password is not visible on the
screen.
The system connects you to a database instance, and shows a SQL prompt.
Your screen looks something like this:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
You can now start using the SQL command prompt.
6. To close the SQL*Plus session, at the SQL prompt enter the exit command. Note
that you are not shutting down the Oracle Database instance.
SQL> exit
Your screen looks something like this:
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.1.0
With the Partitioning, OLAP and Data Mining options
See Also:
Oracle Database 2 Day + Security Guide
SQL*Plus User's Guide and Reference
Overview of Application Development with Oracle Database
Overview of Oracle Database and Development 1-7
Connecting to the Database
Remember that in Oracle Database, the user and the name of the schema to which the
user connects are the same. This section shows how to create a connection to the hr
schema, one of the sample schemas that ship with Oracle Database. To begin, you
must unlock the hr account.
This section contains the following topics:
Unlocking a User Account
Connecting to Oracle Database from SQL*Plus
Connecting to Oracle Database from SQL Developer
Unlocking a User Account
By default, when the hr schema is installed, it is locked and its password is expired.
Before you can connect to Oracle Database using the hr schema, a user with
administrator privileges needs to unlock the hr account and reset its password.
The following steps show how to unlock the hr account and change its password.
To unlock the hr account and change its password:
1. Start a new SQL* Plus session, and login as a user with administrative privileges,
such as user SYSTEM. See "Introducing SQL*Plus" on page 1-5.
2. At the SQL prompt, enter the following statement:
Remember to choose a password that is secure. See Oracle Database Security Guide
for guidelines on choosing passwords.
SQL> ALTER USER hr ACCOUNT UNLOCK IDENTIFIED BY password;
The system confirms that the hr account is unlocked and its password changed:
User altered
Connecting to Oracle Database from SQL*Plus
When the hr account is unlocked, you can create a new hr connection using the new
password that you set in "Unlocking a User Account" on page 1-7.
To create an HR Connection in SQL*Plus:
1. Close the current connection to Oracle database. See step 6 of "Introducing
SQL*Plus" on page 1-5.
2. Start SQL*Plus. In the cmd.exe window, at the c:>\ prompt, enter sqlplus and
press the Enter button of your keyboard.
3. At the SQL prompt, enter hr, and then the password.
The system connects you to a database instance through the hr schema.
You can close both the connection and the command window.
Connecting to Oracle Database from SQL Developer
When the hr account is unlocked, you can use it to access the hr schema inside the
Oracle Database. In this section, you will be working with Oracle SQL Developer.
See Also:
SQL*Plus User's Guide and Reference
Overview of Application Development with Oracle Database
1-8 Oracle Database 2 Day Developer's Guide
To create an HR connection in SQL Developer:
1. Start SQL Developer.
2. In the Connections pane, click the New Connection icon.
3. In the upper part of the New/Select Database Connection window, enter this
information:
For Connection Name, enter hr_conn.
For Username, enter hr.
For Password, enter the password that the system administrator created after
unlocking the hr account. Note that the password text is masked.
Leave the Save Password option unchecked.
Under the Oracle tab of the New/Select Database Connection window, enter this
information:
For Role, select Default.
For Connection Type, select Basic.
For Hostname, enter localhost.
For Port, enter 1521.
For SID, enter orcl.
At the bottom of the New/Select Database Connection window, click Test.
Overview of Application Development with Oracle Database
Overview of Oracle Database and Development 1-9
4. The connection is tested. At the bottom of the New/Select Database Connection
window, Status is changed to Success.
5. At the bottom of the New/Select Database Connection window, click Connect.
The Oracle SQL Developer window appears, with a new hr_conn connection.
Overview of Other Development Environments
1-10 Oracle Database 2 Day Developer's Guide
You have successfully established a connection to the hr schema.
Overview of Other Development Environments
This section introduces other development environments and languages that you may
choose for developing your own applications.
Oracle Data Provider for .NET, Oracle Database Extensions for .NET and Oracle
Developer Tools for Visual Studio .NET
Oracle Data Provider for .NET (ODP.NET), is a .NET data provider that uses and
extends the Microsoft .NET Framework Class Library. ODP.NET uses the .NET
Framework to expose provider-specific features and data types, so its use of native
Oracle Database APIs bring the features of Oracle Database to .NET applications.
Oracle Database Extensions for .NET provide a Microsoft Common Language Runtime
(CLR) host for Oracle Database, data access through ODP.NET classes, and the Oracle
Deployment Wizard for Visual Studio .NET. Because CLR runs as an external process
on Oracle Database server, this integration enables you to run .NET stored procedures
and functions on Oracle Database in Microsoft Windows XP, 2000, and 2003. These
stored procedures and functions can be authored in any .NET language, such as C#
and VB.NET, and then deployed in Oracle Database using the Oracle Deployment
Wizard for .NET, just like PL/SQL or Java stored procedures.
Oracle Developer Tools provide a graphical user interface to access Oracle Database
functionality through Visual Studio .NET. Oracle Developer Tools include Oracle
Explorer for browsing the database schema, wizards and designers for creating and
altering schema objects, the ability to automatically generate code by dragging schema
objects onto the .NET design form, and a PL/SQL editor with integrated
context-sensitive help. Additionally, the Oracle Data Window enables you to perform
routine database tasks and test stored procedures in the Visual Studio environment,
while the SQL Query Window executes SQL statements and scripts.
For an introduction to .NET application development with Oracle Database, see Oracle
Database 2 Day + .NET Developer's Guide.
Further Oracle Database .NET documentation includes Oracle Data Provider for .NET
Developer's Guide and Oracle Database Extensions for .NET Developer's Guide.
For complete information about Oracle Database .NET APIs, ODP.NET, Oracle
Developer Tools, downloads, tutorials, and related information, see the .NET Oracle
Technology Network site at
http://www.oracle.com/technology/tech/dotnet/
PHP
The Hypertext Preprocessor, PHP, is a powerful interpreted server-side scripting
language for quick Web application development. PHP is an open source language
that is distributed under a BSD-style license. PHP is designed for embedding Oracle
Database access requests directly into HTML pages.
For an introduction to PHP application development with Oracle Database, see the
Oracle Database 2 Day + PHP Developer's Guide.
For complete information about Oracle Database PHP APIs and related information,
see the PHP Oracle Technology Network site at
http://www.oracle.com/technology/tech/php/
Overview of Other Development Environments
Overview of Oracle Database and Development 1-11
Oracle Application Express
Oracle Application Express, APEX, is an application development and deployment
tool that enables you to quickly create secure and scalable Web applications even if
you have limited previous programming experience. The embedded Application
Builder tool assembles an HTML interface or a complete application that uses database
objects, such as tables or stored procedures, into a collection of pages that are linked
together through tabs, buttons, or hypertext links. See Oracle Database 2 Day +
Application Express Developer's Guide for complete information on APEX.
For complete information about APEX and related information, see the APEX Oracle
Technology Network site at
http://www.oracle.com/technology/products/database/application_
express/
Oracle Call Interface and Oracle C++ Call Interface
Oracle Call Interface (OCI) is the native C language API for accessing Oracle Database
directly from C applications. See Oracle Call Interface Programmer's Guide for complete
information on OCI.
Oracle C++ Call Interface (OCCI) is the native C++ language API for accessing Oracle
Database directly from C++ applications. Very similar to the OCI, OCCI supports both
relational and object-oriented programming paradigms. See Oracle C++ Call Interface
Programmer's Guide for complete information on OCCI.
The OCI and OCCI Software Development Kits are also installed as part of the Oracle
Instant Client, which enables you to run applications without installing the standard
Oracle client or having an ORACLE_HOME. Your applications will work without
modification, while using significantly less disk space. Oracle Instant Client is
available from the Instant Client Oracle Technology Network site at
http://www.oracle.com/technology/tech/oci/instantclient/
For complete information about Oracle Database OCI and related information, see the
OCI Oracle Technology Network site at
http://www.oracle.com/technology/tech/oci/
For complete information about Oracle Database OCCI and related information, see
the OCCI Oracle Technology Network site at
http://www.oracle.com/technology/tech/oci/occi/
Oracle Java Database Connectivity
Oracle Java Database Connectivity (JDBC) is an API that enables Java to send SQL
statements to an object-relational database, such as Oracle Database. Oracle Database
JDBC provides complete support for the JDBC 3.0 and JDBC RowSet (JSR-114)
standards, advanced connection caching for both XA and non-XA connections,
exposure of SQL and PL/SQL data types to Java, and fast SQL data access.
Like OCI and OCCI, JDBC is part of the Oracle Instant Client installation, which is
available from the Instant Client Oracle Technology Network site at
http://www.oracle.com/technology/tech/oci/instantclient/
For more information about JDBC APIs, see the Sun Developer Network site at
http://java.sun.com/javase/technologies/database/
For complete information about Oracle Database JDBC APIs, drivers, support and
de-support notices, and similar information, see the Oracle Technology Network site at
Overview of Other Development Environments
1-12 Oracle Database 2 Day Developer's Guide
http://www.oracle.com/technology/tech/java/sqlj_jdbc/
For an introduction on how to use Java to access and modify data in Oracle Database,
see Oracle Database 2 Day + Java Developer's Guide.
Open Database Connectivity
Open Database Connectivity (ODBC) is a set of database access APIs that connect to
the database, prepare, and then run SQL statements on Oracle Database. An
application that uses an ODBC driver can access non-uniform data sources, such as
spreadsheets and comma-delimited files.
The Oracle ODBC driver conforms to ODBC 3.51 specifications. It supports all core
APIs and a subset of Level1 and Level 2 functions. Microsoft supplies the Driver
manager component for the Windows platform. The Oracle Database driver for UNIX
platforms is available on the ODBC Oracle Technology Network site at
http://www.oracle.com/technology/tech/windows/odbc/
For information about unixODBC standards and the latest Driver manager, see the
unixODBC site at
http://www.unixodbc.org/
For information about using the Oracle ODBC driver with Windows, see Oracle
Services for Microsoft Transaction Server Developer's Guide.
For information about how to use the Oracle ODBC driver on Linux, see Oracle
Database Administrator's Reference for Linux and UNIX.
Like OCI, OCCI, and JDBC, ODBC is part of the Oracle Instant Client installation,
which is available from the Instant Client Oracle Technology Network site at
http://www.oracle.com/technology/tech/oci/instantclient/
Querying and Manipulating Data 2-1
2
Querying and Manipulating Data
This section shows how to explore the database, retrieve information from it, change
the information in existing tables, and control transaction processing.
This chapter contains the following sections:
Exploring Database Objects on page 2-1
Retrieving Data with Queries on page 2-6
Adding, Changing, and Deleting Data on page 2-27
Controlling Transactions on page 2-30
Exploring Database Objects
In addition to tables, Oracle Database has many other database objects types. While
some objects have many extensive management options, most of them have similar
properties. For example, every object in a database belongs to only one schema, and
has a unique name with that schema. For that reason, Oracle recommends that your
object naming conventions and practices support clear identification of new objects
and object types with appropriate schemas. All objects that you will use here belong to
the same hr schema. Generally, applications work with objects in the same schema.
When you create your own objects, remember that object names cannot exceed 30
characters, and must start with a letter.
Looking at Schema Object Types on page 2-1
Exploring Tables and Viewing Data on page 2-3
Looking at Schema Object Types
In this section, you will further familiarize yourself with the hr sample schema and its
attributes, or database objects. You will learn how you can view these objects by
browsing with the Oracle SQL Developer.
Start by examining some of the types of objects that each schema has.
To browse the HR schema:
1. Start Oracle SQL Developer.
2. In the SQL Developer navigation hierarchy, under the Connections tab, click the
'plus' sign next to hr_conn.
3. In the Connection Information dialog, authenticate the connection to the hr
schema by providing the password. Click OK.
Exploring Database Objects
2-2 Oracle Database 2 Day Developer's Guide
4. In Connections navigation hierarchy, click the 'plus' sign next to hr_conn to
expand the view on the hr schema database objects.
The schema contains many objects, including tables, views, indexes, packages,
procedures, functions, triggers, types, sequences, and so on. Briefly, here is a definition
of each type of database object that you are likely to use:
Tables are basic units of data storage in an Oracle Database, and hold all
user-accessible data.
Views are customized presentations of data from one or more tables, or even other
views.
Exploring Database Objects
Querying and Manipulating Data 2-3
Indexes are optional structures that are created to increase the performance of data
retrieval on a table.
Functions are PL/SQL programming objects that can be stored and executed in the
database. Functions return a value.
Procedures are PL/SQL programming objects that can be stored and executed in
the database. Procedures do not return a value.
Packages contains procedures or functions that can be stored and executed in the
database.
Triggers are stored procedures or functions that are associated with a table, view,
or event. Triggers can be called before or after an event for follow-up action, to
prevent erroneous operations, to modify new data so that it conforms to explicit
business rules, or to log a record of an operation or an event.
Types associate a fixed set of properties with the values that can be used in a
column of a table, or in an argument of a procedure or function. Oracle Database
treats values of one data type differently from values of another data type.
Sequences are used to generate unique integers; you can use them to automatically
generate primary key values.
Exploring Tables and Viewing Data
In this section, you will learn how to find out about the properties of database tables,
and how to view the data these tables contain.
An Oracle Database table is its basic data container. All data that a user can access is
inside one of the tables of the database schema. Each table is two-dimensional object
that has rows, which are individual records, and columns, which represent the various
fields of each record.
To view a table:
1. In Connections navigation hierarchy, click the 'plus' sign next to Tables to expand
the list of tables in the hr schema.
The expanded list of tables includes the tables countries, departments,
employees, job_history, jobs, locations, and regions.
Exploring Database Objects
2-4 Oracle Database 2 Day Developer's Guide
2. Click the employees table.
On the right-hand side of the Oracle SQL Developer window, under the Columns
tab, a listing of all columns of this table appears: EMPLOYEE_ID, FIRST_NAME,
LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY,
COMMISSION_PCT, MANAGER_ID, and DEPARTMENT_ID. Each column of a table
has an associated data type that defines it as character data, an integer, a
floating-point number, a date, or time information. To see all properties of the
column, move the horizontal scroll bar to the right.
Exploring Database Objects
Querying and Manipulating Data 2-5
3. Click the Constraints tab.
You will see all the constraints that are used on this table including the type of
constraint, the constraint's referenced table, whether the constraint is enabled, and
other properties.
4. Similarly, you can explore the various table properties by clicking on the
appropriate tabs:
Grants describes the privileges for the table
Retrieving Data with Queries
2-6 Oracle Database 2 Day Developer's Guide
Statistics describes the properties of the data in the table, such as number of
records, the number of blocks in memory used by the table, average row
length, and so on.
Column Statistics lists the number of distinct entries for each column, the low
and high values, and so on.
Trigg e r s lists the triggers associated with the table together with the type of
trigger, the triggering event, and so on.
Dependencies lists all the objects that are dependent on this table, such as
triggers and views.
Details lists other details of the table, such as creation date, owner (hr), name,
partitioning information, and so on.
Indexes lists the indexes that are defined on the table columns, together with
their status, type, and so on.
SQL summarizes the preceding information in the definition of the table
employees; it includes column definition, indexes, and so on.
To view data in a table:
On the right-hand side of the Oracle SQL Developer window, click the Data tab.
You will see a listing of all records of this table. Each column of a table has an
associated data type that defines it as character data, an integer, a floating-point
number, a date, or time information. To see all properties of the column, move the
horizontal scroll bar to the right.
Retrieving Data with Queries
A query is an operation that retrieves data from one or more tables or views. A
top-level SELECT statement returns results of a query, and a query nested within
another SQL statement is called a subquery.
This section introduces some types of queries and subqueries.
See Also:
Oracle Database SQL Language Reference
Retrieving Data with Queries
Querying and Manipulating Data 2-7
Selecting Data from a Table
A simple query form looks like this:
SELECT select_list FROM source_list
Here, select_list specifies the columns from which the data is retrieved, and the
source_list specifies the tables or views where these columns are found. The
number of columns, as well as the data type and length of each column, is determined
by the elements of the select list. Note also that the select list can use SQL functions.
To see all columns in a table, use * for select_list.
Example 2–1uses the SELECT statement to return the information you previously saw
by viewing the employees table in the Data window.
Example 2–1 Selecting All Columns in a Table
1. In the SQL Worksheet pane, enter:
SELECT * FROM employees;
2. Above the SQL Worksheet pane, click the Run Script icon. Alternatively, you can
use the F5 shortcut key.
3. Click the Script Output tab, below the SQL Worksheet pane, to see the results of
the query.
EMPLOYEE_ID FIRST_NAME LAST_NAME ...
---------------------- -------------------- ------------------------- ...
100 Steven King ...
101 Neena Kochhar ...
102 Lex De Haan ...
...
107 rows selected
Between running different queries, you can clear both the SQL Worksheet and Script
Output panes by clicking the Eraser icon in the toolbar.
Example 2–2 shows how to use the SELECT statement to return only the columns you
requested in your query, namely first_name, last_name, and hire_date.
Example 2–2 Selecting Specific Columns from a Table
SELECT first_name, last_name, hire_date FROM employees;
These are the results of the query.
FIRST_NAME LAST_NAME HIRE_DATE
---------------------- ------------------- -------------------------
Steven King 17-JUN-87
Neena Kochhar 21-SEP-89
Lex De Haan 13-JAN-93
...
107 rows selected
Retrieving Data with Queries
2-8 Oracle Database 2 Day Developer's Guide
Using Column Aliases
To display a column with a new heading, you can rename a column within your report
by using an alias immediately after the correct name of the column. This alias
effectively renames the item for the duration of the query.
In Example 2–3, the SELECT statement returns the columns you request in your query,
but with the column headings that you specified as aliases: name1, name2, and
hired.
Example 2–3 Using a Simple Column Alias
SELECT first_name name1, last_name name2, hire_date hired FROM employees;
The results of the query follow:
NAME1 NAME2 HIRED
--------------------- -------------------- -------------------------
Steven King 17-JUN-87
Neena Kochhar 21-SEP-89
Lex De Haan 13-JAN-93
...
107 rows selected
If the alias that you want to use contains uppercase and lowercase characters or
spaces, or a combination, you must use double quotation marks (").
Example 2–4 uses a SELECT statement to return the columns with column heading
aliases that you specify: First, Last, and Date Started.
Example 2–4 Using Quoted Alias Columns
SELECT first_name "First", last_name "Last", hire_date "Date Started"
FROM employees;
The results of the query follow.
First Last Date Started
---------------------- -------------------- -------------------------
Steven King 17-JUN-87
Neena Kochhar 21-SEP-89
Lex De Haan 13-JAN-93
...
107 rows selected
Restricting Data to Match Specific Conditions
In addition to the SELECT and FROM keywords, other common clauses are used in
queries. The WHERE clause uses comparison operators to select the rows that should be
retrieved, instead of returning all the rows in the tables.
This table lists the comparison operators that can be used in the WHERE clause.
Comparison Operator Definition
=Tests for equality
!=, <> Tests for inequality
>Tests for greater than
>= Tests for greater than or equal
Retrieving Data with Queries
Querying and Manipulating Data 2-9
The WHERE clause can test a single condition at a time, or combine multiple tests using
the AND clause.
Example 2–5 shows how to use the WHERE clause to return the column values that are
restricted to a single department, which has 90 for its department_id.
Example 2–5 Testing for a Single Condition
SELECT first_name "First", last_name "Last"
FROM employees
WHERE department_id=90;
The results of the query appear.
First Last
-------------------- -------------------------
Steven King
Neena Kochhar
Lex De Haan
3 rows selected
Example 2–6 shows how to use the WHERE ... AND clause to return the rows that are
restricted to two separate condition, to match a salary that is greater or equal to
11,000, and an assigned (not null) commission rate.
Example 2–6 Testing Multiple Conditions
SELECT first_name "First", last_name "Last",
SALARY "Salary", COMMISSION_PCT "%"
FROM employees
WHERE salary >=11000 AND commission_pct IS NOT NULL;
The results of the query appear.
First Last Salary %
-------------------- --------------------- -------------------- -----
John Russell 14000 0.4
Karen Partners 13500 0.3
Alberto Errazuriz 12000 0.3
...
6 rows selected
<Tests for less than
<= Tests for less than or equal
BETWEEN a AND bTests for a fit in the range between two values, inclusive
LIKE Tests for a match in a string, using the wildcard symbols (%)
for zero or multiple characters, or underscore (_) for a single
character
IN() Tests for a match in a specified list of values
NOT IN() Tests that there is no match in a specified list of values
IS NULL Tests that the value is null
IS NOT NULL Tests that the value is not null
Comparison Operator Definition
Retrieving Data with Queries
2-10 Oracle Database 2 Day Developer's Guide
Example 2–7 uses the WHERE clause to return the six rows where the last name starts
with Ma: Mallin, Markle, Marlow, Marvins, Matos, and Mavris. If you use a matching
expression '%ma%' instead (the text ma could appear anywhere in the column), your
results would contain only three rows, for Kumar, Urman, and Vollman.
Example 2–7 Testing for a Matching String
SELECT first_name "First", last_name "Last"
FROM employees
WHERE last_name LIKE 'Ma%';
The results of the query appear.
First Last
-------------------- -------------------------
Jason Mallin
Steven Markle
James Marlow
...
6 rows selected
Example 2–8 shows you how to use the WHERE ... IN clause to find employees who
work in several different departments, matching the DEPARTMENT_ID to a list of
values 100, 110, 120. The result will contain eight rows, with four rows matching the
first value in the list, and the other two rows matching the second value in the list;
there are no matches for 120.
Example 2–8 Testing for a Match in a List of Values
SELECT first_name "First", last_name "Last", department_id "Department"
FROM employees
WHERE department_id IN (100, 110, 120);
The results of the query appear.
First Last Department
-------------------- ------------------------- ----------------------
John Chen 100
Daniel Faviet 100
William Gietz 110
...
8 rows selected
If you want to find employees who work in a particular department, but do not know
the corresponding department_id value, you must look in both the employees and
departments tables. Looking at an intersection of two tables is a JOIN operation.
Fully qualified column names, such as employees.employee_id, are optional.
However, when queries use two or more tables that have the same column name, you
must identify these columns with the table. For example, the
employees.department_id and departments.department_id could be used
together to determine the name of a department in which an employee works.
Note that when using fully qualified column names, the query is more readable if you
use an alias for the name of the table, such as d for departments. The column
departments.department_id then becomes d.department_id, and
employees.department_id becomes e.department_id. You must create these
table aliases in the FROM clause of the query.
Example 2–9 shows the result set containing columns from two separate tables.
Because the column names in the report are unique, they did not need to be qualified
Retrieving Data with Queries
Querying and Manipulating Data 2-11
by the table name. However, because the WHERE clause uses the same column name
from two different tables, you must qualify it.
Example 2–9 Testing for a Value in Another Table
SELECT e.first_name "First", e.last_name "Last", d.department_name "Department"
FROM employees e, departments d
WHERE e.department_id = d.department_id;
The results of the query appear.
First Last Department
-------------------- ------------------------- ------------------------------
Jennifer Whalen Administration
Michael Hartstein Marketing
Pat Fay Marketing
...
106 rows selected
Searching for Patterns in Data
Regular expressions allow you to use standard syntax conventions to search for
complex patterns in character sequences. A regular expression defines the search
pattern by using metacharacters that specify search algorithms, and literals that
specify the characters.
Regular expression functions include REGEXP_INSTR, REGEXP_LIKE, REGEXP_
REPLACE, and REGEXP_SUBSTR.
Example 2–10 shows how to find all managers. The metacharacter | indicates an OR
condition, which you must use because the manager position is specified either as '%_
MGR' or '%_MAN', depending on department. The option i specifies that the match
should be case insensitive.
Example 2–10 Finding a Matching Data Pattern
SELECT first_name "First", last_name "Last", job_id "Job"
FROM employees
WHERE REGEXP_LIKE (job_id, '(_m[an|gr])', 'i');
The results of the query appear.
First Last Job
-------------------- ------------------------- ----------
Nancy Greenberg FI_MGR
Den Raphaely PU_MAN
Matthew Weiss ST_MAN
...
14 rows selected
Example 2–11 shows how the REGEXPR_LIKE expression selects rows where the
last_name has a double vowel (two adjacent occurrences of either a, e, i, o, or u).
See Oracle Database SQL Language Reference for information about the REGEXP_LIKE
condition.
Example 2–11 Finding a Matching Data Pattern (Adjacent Characters)
SELECT first_name "First", last_name "Last"
FROM employees
WHERE REGEXP_LIKE (last_name, '([aeiou])\1', 'i');
Retrieving Data with Queries
2-12 Oracle Database 2 Day Developer's Guide
The results of the query appear.
First Last
-------------------- -------------------------
Harrison Bloom
Lex De Haan
Kevin Feeney
...
8 rows selected
To find a data pattern and replace it with another one, use the REGEXPR_REPLACE.
Example 2–12 replaces the phone numbers of the format 'nnn.nnn.nnnn' with the
format '(nnn) nnn-nnnn'. Note that digits are matched with the metacharacter
[:digit], while the metacharacter {n} indicates the number of occurrences. The
metacharacter '.' typically indicates any character in an expression, so the
metacharacter \ is used as an escape character and makes the following character in
the pattern a literal. This result set shows the telephone numbers in the new format.
See Oracle Database SQL Language Reference for information about the REGEXP_
REPLACE condition.
Example 2–12 Replacing a Data Pattern
SELECT first_name "First", last_name "Last",
phone_number "Old Number",
REGEXP_REPLACE(phone_number,
'([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})',
'(\1) \2-\3') "New Number"
FROM employees
WHERE department_id = 90;
The results of the query appear.
First Last Old Number New Number
-------------------- ---------------------- -------------------- --------------
Steven King 515.123.4567 (515) 123-4567
Neena Kochhar 515.123.4568 (515) 123-4568
Lex De Haan 515.123.4569 (515) 123-4569
3 rows selected
Example 2–13 shows how you can use the REGEXPR_SUBSTR function to find the first
substring that matches a pattern. Note that the metacharacter'+' indicates multiple
occurrences of the pattern. This result set extracts numerals and dashes from the
street_address column. See Oracle Database SQL Language Reference for information
about the REGEXP_SUBSTR expression.
Example 2–13 Returning a Substring
SELECT street_address, REGEXP_SUBSTR(street_address,
'[[:digit:]-]+', 1, 1) "Street Numbers"
FROM locations;
The results of the query appear.
STREET_ADDRESS Street Numbers
---------------------------------------- ----------------------
1297 Via Cola di Rie 1297
93091 Calle della Testa 93091
2017 Shinjuku-ku 2017
...
Retrieving Data with Queries
Querying and Manipulating Data 2-13
23 rows selected
The REGEXPR_INSTR function enables you to find the position of the first substring
that matches a pattern. In Example 2–14, you use REGEXPR_INSTR to find a space
character, ' '. Note that the metacharacter '+' indicates multiple occurrences of the
pattern. This result set shows the position of the first space in each address. See Oracle
Database SQL Language Reference for information about the REGEXP_INSTR expression.
Example 2–14 Returning a Location of a Substring
SELECT street_address, REGEXP_INSTR(street_address, '[ ]+', 1, 1) "Position"
FROM locations;
The results of the query appear.
STREET_ADDRESS Position
---------------------------------------- ----------------------
1297 Via Cola di Rie 5
93091 Calle della Testa 6
2017 Shinjuku-ku 5
...
23 rows selected
The function REGEXPR_COUNT determines the number of times the specified character
pattern repeats in a string. In Example 2–15, REGEXPR_COUNT returns the number of
times the space character occurs in the street_address column of the table
locations. See Oracle Database SQL Language Reference for information about the
REGEXP_COUNT expression.
Example 2–15 Returning the Number of Occurrences of a Substring
SELECT street_address, REGEXP_COUNT(street_address, ' ', 1) "Number of Spaces"
FROM locations;
The results of the query appear.
STREET_ADDRESS Number of Spaces
---------------------------------------- ----------------------
1297 Via Cola di Rie 4
93091 Calle della Testa 3
2017 Shinjuku-ku 1
...
23 rows selected
This result set shows the number of spaces in each address.
Sorting Data
In SQL, the ORDER BY clause is used to identify which columns are used to sort the
resulting data. The sort criteria does not have to be included in the result set, and can
include expressions, column names, arithmetic operations, user-defined functions, and
so on.
Example 2–16 shows an ORDER BY clause that returns the result set sorted in order of
last_name, in ascending order.
See Also:
Oracle Database SQL Language Reference for syntax of regular
expressions
Retrieving Data with Queries
2-14 Oracle Database 2 Day Developer's Guide
Example 2–16 Use Quoted Alias Columns
SELECT first_name "First", last_name "Last", hire_date "Date Started"
FROM employees
ORDER BY last_name;
The results of the query appear.
First Last Date Started
-------------------- ------------------------- -------------------------
Ellen Abel 11-MAY-96
Sundar Ande 24-MAR-00
Mozhe Atkinson 30-OCT-97
...
107 rows selected
Using Built-In and Aggregate Functions
SQL arithmetic operators and other build-in functions allow you to perform
calculations directly on data stored in the tables.
Using Arithmetic Operators
Oracle Database SQL supports the basic arithmetic operators, such as the plus sign (+)
for addition, the minus sign (-) for subtraction, the asterisk (*) for multiplication, and
the forward slash (/) for division. These are evaluated according to standard
arithmetic rules of evaluation order.
In Example 2–17, the result set show the salary earned by employees who are eligible
for commission earnings, in order of the hire date.
Example 2–17 Evaluating an Arithmetic Expression
SELECT first_name "First", last_name "Last", salary * 12 "Annual Compensation"
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY hire_date;
The results of the query appear.
First Last Annual Compensation
-------------------- ------------------------- ----------------------
Janette King 120000
Patrick Sully 114000
Ellen Abel 132000
...
35 rows selected
Using Numeric Functions
Oracle Database has many numeric functions for manipulating numeric values, such
as ROUND for rounding to a specified decimal or TRUNC for truncating to a specified
decimal. These functions all return a single value for each row that is evaluated.
Example 2–18 shows how to determine daily pay, rounded off to the nearest cent.
See Also:
Oracle Database SQL Language Reference for information on all
available SQL functions
Retrieving Data with Queries
Querying and Manipulating Data 2-15
Example 2–18 Rounding off Numeric Data
SELECT first_name "First", last_name "Last",
ROUND(salary/30, 2) "Daily Compensation"
FROM employees;
The results of the query appear.
First Last Daily Compensation
-------------------- ------------------------- ----------------------
Steven King 800
Neena Kochhar 566.67
Lex De Haan 566.67
...
107 rows selected
Example 2–19 shows how to determine daily pay that is truncated at the nearest dollar.
Note that the TRUNC function does not round-up the value.
Example 2–19 Truncating Numeric Data
SELECT first_name "First", last_name "Last",
TRUNC(salary/30, 0) "Daily Compensation"
FROM employees;
The results of the query appear.
First Last Daily Compensation
-------------------- ------------------------- ----------------------
Steven King 800
Neena Kochhar 566
Lex De Haan 566
...
107 rows selected
Using Character Functions
Oracle Database includes an extensive list of character functions for customizing
character values.
These functions can change the case of a character expression to UPPER or LOWER,
remove blanks, concatenate strings, and extract or remove substrings.
Example 2–20 demonstrates how to change the character case of your expression. The
result set shows the results of UPPER, LOWER, and INITCAP functions.
Example 2–20 Changing the Case of Character Data
SELECT UPPER(first_name) "First upper",
LOWER(last_name) "Last lower",
INITCAP(email) "E-Mail"
FROM employees;
The results of the query appear.
First upper Last lower E-Mail
-------------------- ------------------------- -------------------------
STEVEN king Sking
See Also:
Oracle Database SQL Language Reference for information on numeric
SQL functions
Retrieving Data with Queries
2-16 Oracle Database 2 Day Developer's Guide
NEENA kochhar Nkochhar
LEX de haan Ldehaan
To produce information from two separate columns or expressions in the same column
of the report, you can concatenate the separate results by using the concatenation
operator, ||. Note also that in Example 2–21, you are performing a 4-way join
operation. This result set shows that the simple concatenation function in column
Name listed the last_name value immediately after the first_name value, while the
nested concatenation function in column Location separated the city and
country_name values.
Example 2–21 Concatenating Character Data
SELECT e.first_name || ' ' || e.last_name "Name",
l.city || ', ' || c.country_name "Location"
FROM employees e, departments d, locations l, countries c
WHERE e.department_id=d.department_id AND
d.location_id=l.location_id AND
l.country_id=c.country_id
ORDER BY last_name;
The results of the query appear.
Name Location
----------------------------- --------------------------------------------
Ellen Abel Oxford, United Kingdom
Sundar Ande Oxford, United Kingdom
Mozhe Atkinson South San Francisco, United States of America
...
106 rows selected
You can use RTRIM and LTRIM functions to remove characters (by default, spaces)
from the beginning or the end of character data, respectively. The TRIM function
removes both leading and following characters. In Example 2–22, you use a type
conversion function, TO_CHAR. This result set shows that all employees without a
leading M in their last_name values, the MAN missing from the end of the job_id
values, and the leading 0 is missing from the date_hired values.
Example 2–22 Trimming Character Data
SELECT LTRIM(last_name, 'M') "Last Name",
RTRIM(job_id, 'MAN') "Job",
TO_CHAR(TRIM(LEADING 0 FROM hire_date)) "Hired"
FROM employees
WHERE department_id=50;
The results of the query appear.
Last Name Job Hired
------------------------- ---------- ---------
Weiss ST_ 18-JUL-96
Fripp ST_ 10-APR-97
Kaufling ST_ 1-MAY-95
Vollman ST_ 10-OCT-97
ourgos ST_ 16-NOV-99
...
ikkilineni ST_CLERK 28-SEP-98
Landry ST_CLERK 14-JAN-99
arkle ST_CLERK 8-MAR-00
...
Retrieving Data with Queries
Querying and Manipulating Data 2-17
arlow ST_CLERK 16-FEB-97
...
allin ST_CLERK 14-JUN-96
...
Philtanker ST_CLERK 6-FEB-00
...
Patel ST_CLERK 6-APR-98
...
atos ST_CLERK 15-MAR-98
Vargas ST_CLERK 9-JUL-98
Taylor SH_CLERK 24-JAN-98
...
Geoni SH_CLERK 3-FEB-00
...
Cabrio SH_CLERK 7-FEB-99
...
Bell SH_CLERK 4-FEB-96
Everett SH_CLERK 3-MAR-97
cCain SH_CLERK 1-JUL-98
...
45 rows selected
You can use RPAD to add characters (by default, spaces) to the end of character data.
The LPAD function adds characters to the beginning of character data.
In Example 2–23, the result set shows a simple histogram of relative salary values.
Example 2–23 Padding Character Data
SELECT first_name || ' ' || last_name "Name",
RPAD(' ', salary/1000, '$') "Salary"
FROM employees;
The results of the query appear.
Name Salary
-------------------------------------- ----------------
Steven King $$$$$$$$$$$$$$$$$$$$$$$
Neena Kochhar $$$$$$$$$$$$$$$$
Lex De Haan $$$$$$$$$$$$$$$$
...
107 rows selected
You can use SUBSTR to extract only a substring of data, specified by the starting
character position and the total number of characters.
In Example 2–24, you use SUBSTR to abbreviate the first_name value to an initial,
and strip the area code from the phone_number value.
Example 2–24 Extracting a Substring of Character Data
SELECT SUBSTR(first_name, 1, 1) || '. ' || last_name "Name",
SUBSTR(phone_number, 5, 8) "Phone"
FROM employees;
The results of the query appear.
Name Phone
---------------------------- --------
S. King 123.4567
N. Kochhar 123.4568
L. De Haan 123.4569
Retrieving Data with Queries
2-18 Oracle Database 2 Day Developer's Guide
...
107 rows selected
This result set shows the first_name values abbreviated to an initial, and the
phone_number values without the leading area code component.
You can use REPLACE, in combination with SUBSTR, to replace a specific substring if
you know its relative location in the character data.
In Example 2–25, you use SUBSTR in the WHERE clause to replace the abbreviation for a
job code.
Example 2–25 Replacing Substring of Character Data
SELECT SUBSTR(first_name, 1, 1) || '. ' || last_name "Name",
REPLACE(job_id, 'SH', 'SHIPPING') "Job"
FROM employees
WHERE SUBSTR(job_id, 1, 2) = 'SH';
The results of the query appear.
Name Job
----------------------- ------------------------
W. Taylor SHIPPING CLERK
J. Fleaur SHIPPING_CLERK
M. Sullivan SHIPPING_CLERK
...
20 rows selected
This result set shows the first_name values abbreviated to an initial, and the job_
id values were replaced.
Using Datetime Functions
Oracle Database has data functions for manipulating and calculating date and time
data, including interval functions.
In Example 2–26, you will determine the duration of employment in a particular job
for those employees who have switched to a different position. Note that the names
are not unique because employees may hold more than two different positions over
time. See Oracle Database SQL Language Reference for information about the MONTHS_
BETWEEN function.
Example 2–26 Determining the Number of Months Between Dates
SELECT e.first_name || ' ' || e.last_name "Name",
TRUNC(MONTHS_BETWEEN(j.end_date, j.start_date)) "Months Worked"
FROM employees e, job_history j
WHERE e.employee_id = j.employee_id
ORDER BY "Months Worked";
The results of the query appear.
Name Months Worked
---------------------------------------------- -------------
Jonathon Taylor 9
Payam Kaufling 11
See Also:
Oracle Database SQL Language Reference for information on
character SQL functions
Retrieving Data with Queries
Querying and Manipulating Data 2-19
Jonathon Taylor 11
...
10 rows selected
You will notice that this result shows that of the employees who left the company, the
shortest and the longest stays were 9 and 69 months, respectively.
In Example 2–27, you will use the EXTRACT function to determine if employees are in
their sixth calendar year of continuous employment. The EXTRACT function can also
be used in combination with MONTH, DATE, and so on.
Note that the SYSDATE function gives the current date of the system clock. See Oracle
Database SQL Language Reference for information about the SYSDATE function.
Example 2–27 Determining the Years Between Dates
SELECT first_name || ' ' || last_name "Name",
(EXTRACT(YEAR from SYSDATE) - EXTRACT(YEAR FROM hire_date)) "Years Employed"
FROM employees;
The results of the query appear.
Name Years Employed
---------------------------------------------- --------------
Steven King 20
Neena Kochhar 18
Lex De Haan 14
...
107 rows selected
You will notice that this result shows that employee 'Steven King' has worked for the
company the longest, 20 years.
In Example 2–28, you will use the last_day function to determine the last day of the
month in which an employee was hired.
Example 2–28 Getting the Last Day of the Month for a Specified date
SELECT first_name || ' ' || last_name "Name", hire_date "Date Started",
LAST_DAY(hire_date) "End of Month"
FROM employees;
The results of the query appear.
Name Date Started End of Month
------------------------------ ------------------ -------------------------
Steven King 17-JUN-87 30-JUN-87
Neena Kochhar 21-SEP-89 30-SEP-89
Lex De Haan 13-JAN-93 31-JAN-93
...
107 rows selected
You will notice that this result shows the correct end of the month for each hire_
date value.
In Example 2–29, you will use the ADD_MONTHS function to add 6 months to the date
on which an employee was hired. See Oracle Database SQL Language Reference for
information about the ADD_MONTH function.
Example 2–29 Adding Months to a Date
SELECT first_name || ' ' || last_name "Name", hire_date "Date Started",
Retrieving Data with Queries
2-20 Oracle Database 2 Day Developer's Guide
ADD_MONTHS(hire_date, 6) "New Date"
FROM employees;
The results of the query appear.
Name Date Started New Date
------------------------ ------------------------- -------------------------
Steven King 17-JUN-87 17-DEC-87
Neena Kochhar 21-SEP-89 21-MAR-90
Lex De Haan 13-JAN-93 13-JUL-93
...
107 rows selected
In Example 2–30, you will use the SYSTIMESTAMP function determine the current
system time and date. SYSTIMESTAMP is similar to SYSDATE, but also contains time of
day information, including the time zone and fractional seconds. See Oracle Database
SQL Language Reference for information about the SYSTIMESTAMP function.
Note that instead of an hr schema table, you are using the table DUAL, a small table in
the data dictionary that you can reference to guarantee a known result. See Oracle
Database Concepts for information about the DUAL table and Oracle Database SQL
Language Reference for information about selecting from the DUAL table.
Example 2–30 Getting the System Date and Time
SELECT EXTRACT(HOUR FROM SYSTIMESTAMP) || ':' ||
EXTRACT(MINUTE FROM SYSTIMESTAMP) || ':' ||
ROUND(EXTRACT(SECOND FROM SYSTIMESTAMP), 0) || ', ' ||
EXTRACT(MONTH FROM SYSTIMESTAMP) || '/' ||
EXTRACT(DAY FROM SYSTIMESTAMP) || '/' ||
EXTRACT(YEAR FROM SYSTIMESTAMP) "System Time and Date"
FROM DUAL;
The results of the query appear.
System Time and Date
------------------------------------------------------------
18:25:56, 4/5/2007
Your result would change, depending on the current SYSTIMESTAMP value.
Using Data Type Conversion Functions
Oracle Database has data functions for converting between different data types. This is
particularly useful when you need to display data of different data types in the same
column.
There are three general types of conversion functions: for characters (TO_CHAR), for
numbers (TO_NUMBER), for dates (TO_DATE) and for timestamps (TO_TIMESTAMP).
You will use the TO_CHAR function to convert a date into a desired format.
Example 2–31 converts the HIRE_DATE values to a 'FMMonth DD YYYY' format; note
that the FM option removes all leading or trailing blanks from the month name. Other
options you could use include 'DD-MON-YYYY AD', 'MM-DD-YYYY HH24:MI:SS', and
so on.
See Also:
Oracle Database SQL Language Reference for datetime functions
Retrieving Data with Queries
Querying and Manipulating Data 2-21
Example 2–31 Using TO_CHAR to Convert a Date Using a Format Template
SELECT first_name || ' ' || last_name "Name",
TO_CHAR(hire_date, 'FMMonth DD YYYY') "Date Started"
FROM employees;
The results of the query appear.
Name Date Started
---------------------------------------------- -----------------
Steven King June 17 1987
Neena Kochhar September 21 1989
Lex De Haan January 13 1993
...
107 rows selected
Your result set lists all the hire_date values in the new format.
Example 2–32 shows how you can use two standard format tags, Short Date (DS) and
Long Date (DL), to format your date.
Example 2–32 Using TO_CHAR to Convert a Date Using a Standard Format
SELECT first_name || ' ' || last_name "Name",
TO_CHAR(hire_date, 'DS') "Short Date",
TO_CHAR(hire_date, 'DL') "Long Date"
FROM employees;
The results of the query appear.
Name Short Date Long Date
--------------------------- ---------- -------------------------
Steven King 6/17/1987 Wednesday, June 17, 1987
Neera Kochhar 9/21/19889 Thursday, September 21, 1989
Lex De Haen 1/13/1993 Wednesday, January 13, 1993
...
107 rows selected
You can use the TO_CHAR function to convert a number to a desired currency format.
Example 2–33 will convert the salary values to a '$99,999.99' format. See Oracle
Database SQL Language Reference for TO_CHAR.
Example 2–33 Using TO_CHAR to Convert a Number to a Currency Template
SELECT first_name || ' ' || last_name "Name",
TO_CHAR(salary, '$99,999.99') "Salary"
FROM employees;
The results of the query appear.
Name Salary
---------------------------------------------- -----------
Steven King $24,000.00
Neena Kochhar $17,000.00
Lex De Haan $17,000.00
...
107 rows selected
Example 2–34 shows how you can use the TO_NUMBER function to convert a character
into a number that you can subsequently use in calculations. See Oracle Database SQL
Language Reference for TO_NUMBER.
Retrieving Data with Queries
2-22 Oracle Database 2 Day Developer's Guide
Example 2–34 Using TO_NUMBER to Convert a Character to a Number
SELECT first_name || ' ' || last_name "Name",
TO_NUMBER('300') + salary "Proposed Salary"
FROM employees
WHERE SUBSTR(job_id, 4, 5) = 'CLERK';
The results of the query appear.
Name Proposed Salary
---------------------------------------------- ---------------
Alexander Khoo 3400
Shelli Baida 3200
Sigal Tobias 3100
...
45 rows selected
Your result set lists all the proposed salary values for the selected subset of
employees.
You can use the TO_DATE function to convert a character data with a specified format
mode into a date. In Example 2–35, you will use the format model 'Month dd,
YYYY, HH:MI A.M.'; other formats include 'DD-MON-RR', 'FF-Mon-YY
HH24:MI:SI', and so on.
Example 2–35 Using TO_DATE to Convert a Character Data to a Date
SELECT TO_DATE('January 5, 2007, 8:43 A.M.',
'Month dd, YYYY, HH:MI A.M.') "Date"
FROM DUAL;
The results of the query appear.
Date
---------
05-JAN-07
Your result converts the character data, interpreted by the specified format string, into
a DATE type.
Example 2–36 shows how you can use the TO_TIMESTAMP method with format
models such as 'DD-Mon-RR HH24:MI:SS.FF'. See Oracle Database SQL Language
Reference for TO_DATE.
Example 2–36 Using TO_TIMESTAMP to Convert Character Data to a Timestamp
SELECT TO_TIMESTAMP('May 5, 2007, 8:43 A.M.',
'Month dd, YYYY, HH:MI A.M.') "Timestamp"
FROM DUAL;
The results of the query appear.
Timestamp
---------------------------------------------
05-MAY-07 08.43.00.000000000 AM
Your result converts the character data, interpreted by the specified format string, into
a TIMESTAMP type.
Retrieving Data with Queries
Querying and Manipulating Data 2-23
Using Aggregate Functions
Aggregate functions operate on groups of rows, or an entire table or view. By their
nature, these functions provide statistical results for sets, and include average (AVG),
count (COUNT), maximum (MAX), minimum (MIN), standard deviation (STDEV), sum
(SUM), and so on.
Aggregate functions are especially powerful when used it in combination with the
GROUP BY clause, where a query returns a list that is grouped by one or more
columns, with a distinct result for each of the groupings.
You can also use the HAVING clause, which specifies that a query should only return
rows where aggregate values meet the specified conditions.
Example 2–37 shows how you can use the COUNT function and the GROUP BY clause
to determine how many people report to each manager. Note that the wildcard, *, is
used to denote the counting of an entire record.
Example 2–37 Counting the Number of Rows That Satisfy an Expression
SELECT manager_id "Manager",
COUNT(*) "Number of Reports"
FROM employees
GROUP BY manager_id;
The results of the query appear.
Manager Number of Reports
--------- -----------------
1
100 14
123 8
...
19 rows selected
Your result shows how many people report to each manager. Note that one person
does not report to anyone; if you examine the data, you will see that Steven King does
not have a supervisor.
Example 2–38 shows how you can also use the COUNT function with a DISTINCT
option to determine how many distinct values and are in a data set. Here, you will
count the number of departments that have employees.
Example 2–38 Counting a Number of Distinct Values in a Set
SELECT COUNT(DISTINCT department_id) "Number of Departments"
FROM employees;
The results of the query appear.
Number of Departments
---------------------
11
Your result shows that 11 departments have employees. If you look at the
departments table, you will note that it lists 27 departments.
See Also:
Oracle Database SQL Language Reference for data type conversion
functions
Retrieving Data with Queries
2-24 Oracle Database 2 Day Developer's Guide
You can use basic statistical functions, such as MIN, MAX, MEDIAN, AVG, and so on, to
determine the range of salaries across the set. In Example 2–39, you will examine
salaries grouped by job_id, but a similar query could be used to examine salaries
across departments, locations, and so on.
Example 2–39 Determining Statistical Information
SELECT job_id "Job", COUNT(*) "#", MIN(salary) "Minimum",
ROUND(AVG(salary), 0) "Average",
MEDIAN(salary) "Median", MAX(salary) "Maximum",
ROUND(STDDEV(salary)) "Std Dev"
FROM employees
GROUP BY job_id
ORDER BY job_id;
The results of the query appear.
Job # Minimum Average Median Maximum Std Dev
---------- ---------- ---------- ---------- ---------- ---------- ----------
AC_ACCOUNT 1 8300 8300 8300 8300 0
AC_MGR 1 12000 12000 12000 12000 0
AD_ASST 1 4400 4400 4400 4400 0
AD_PRES 1 24000 24000 24000 24000 0
AD_VP 2 17000 17000 17000 17000 0
FI_ACCOUNT 5 6900 7920 7800 9000 766
FI_MGR 1 12000 12000 12000 12000 0
HR_REP 1 6500 6500 6500 6500 0
IT_PROG 5 4200 5760 4800 9000 1926
MK_MAN 1 13000 13000 13000 13000 0
MK_REP 1 6000 6000 6000 6000 0
...
19 rows selected
Your result shows the statistics for 19 different jobs.
If you use the HAVING clause, you can limit your result set to only the kind of values
that interest you. In Example 2–40, you see the salary budget for departments where
the sum of salaries exceeds $1,000,000 annually.
Example 2–40 Limiting the Results Using the HAVING Clause
SELECT Department_id "Department", SUM(salary*12) "All Salaries"
FROM employees
HAVING SUM(salary * 12) >= 1000000
GROUP BY department_id;
The results of the query appear.
Department All Salaries
---------- ------------
50 1876800
80 3654000
Your result shows that only two departments have salary budgets in excess of
$1,000,000.
You can use the RANK function to determine the relative ordered rank of a number, and
use the PERCENT_RANK function to determine the percentile position. In
Example 2–41, you determine these values for a salary of $3,000 over the subset of all
employees who have a 'CLERK' designation in the job_id.
Retrieving Data with Queries
Querying and Manipulating Data 2-25
You can also examine groups using the WITHIN GROUP function.
Example 2–41 Determining RANK and PERCENT_RANK
SELECT RANK(3000) WITHIN GROUP (ORDER BY salary DESC) "Rank",
ROUND(100 * (PERCENT_RANK(3000)
WITHIN GROUP (ORDER BY salary DESC)), 0) "Percentile"
FROM employees
WHERE job_id LIKE '%CLERK';
The results of the query appear.
Rank Percentile
---------- ----------
20 42
Your result shows that a salary of $3,000 is the 20th highest, and that it is in the 42nd
percentile among all employees who have a 'CLERK' designation.
The DENSE_RANK function works much like the RANK function, but the identical
values receive the same rank, and there are no gaps in the ranking. In Example 2–42,
you will determine the DENSE_RANK of $3,000 over the subset of all employees who
have a 'CLERK' designation in the job_id.
Example 2–42 Determining DENSE_RANK:
SELECT DENSE_RANK(3000) WITHIN GROUP (ORDER BY salary DESC) "Rank"
FROM employees
WHERE job_id LIKE '%CLERK';
The results of the query appear.
Rank
----------
12
Your result shows that a salary of $3,000 is the 12th highest using the DESNE_RANK
function. Contrast it with the 20th rank obtained in the previous example that used the
RANK function.
Using NULL Value Functions
To work with NULL values, Oracle Database supplies two functions. NVL substitutes a
specified value if a NULL is encountered, and NVL2 specifies two possible expressions
that could be evaluated (one if none of its component variables is NULL, and another
one if at least one variable is NULL).
In Example 2–43, you will use the NVL and NVL2 functions to determine what the
whole annual compensation would be for each employee, if they were involved in a
$300,000 sale. Note that the commission rate is a multiplier on sales volume, not on
base salary. Note also that the WHERE clause limits the result set to managers.
Example 2–43 Using the NVL and NVL2 Functions
SELECT first_name || ' ' || last_name "Name",
NVL((commission_pct * 100), 0) "Comm Rate",
NVL2(commission_pct,
See Also:
Oracle Database SQL Language Reference for aggregate functions
Retrieving Data with Queries
2-26 Oracle Database 2 Day Developer's Guide
ROUND(salary * 12 + commission_pct * 300000, 2),
salary * 12) "With $300K Sales"
FROM employees
WHERE job_id LIKE '%_M%' AND department_id = 80;
The results of the query appear.
Name Comm Rate With $300K Sales
---------------------------------- ---------------------- -----------------
John Russell 40 288000
Karen Partners 30 252000
Alberto Errazuriz 30 234000
Gerald Cambrault 30 222000
Eleni Zlotkey 20 186000
5 rows selected
Your result shows that in the Comm Rate column, the NVL function replaces a NULL
value by 0. In the With $300K Sales column, the NVL2 function generates values
from two different expressions, depending on the value of the COMMISSION_PCT
value.
Using Conditional Functions
Oracle Database provides two functions that can return values based on multiple
condition values.
The CASE function is equivalent to nestled IF ... THEN ... ELSE statements, as it
compares a value, an expression, or a search condition, and returns a result when it
finds a match.
In Example 2–44, you will use the CASE structure to view prospective salary increases
that would be awarded based on the length of service with the company.
Example 2–44 Using the CASE Function
SELECT first_name || ' ' || last_name "Name",
hire_date "Date Started", salary "Current Pay",
CASE
WHEN hire_date < TO_DATE('01-Jan-90') THEN TRUNC(salary*1.15, 0)
WHEN hire_date < TO_DATE('01-Jan-95') THEN TRUNC(salary*1.10, 0)
WHEN hire_date < TO_DATE('01-Jan-00') THEN TRUNC(salary*1.05, 0)
ELSE salary END "Proposed Salary"
FROM employees;
The results of the query appear.
Name Date Started Current Pay Proposed Salary
-------------------------- ---------------- --------------- -------------------
Steven King 17-JUN-87 24000 27600
Neena Kochhar 21-SEP-89 17000 19550
Lex De Haen 13-JAN-93 17000 18700
...
107 rows selected
Your result shows that the values in the Proposed Salary column have been
adjusted based on the values of Date Started.
The DECODE function compares a value or expression to search values, and returns a
result when it finds a match. If a match is not found, then DECODE returns the default
value, or NULL (if a default value is not specified).
Adding, Changing, and Deleting Data
Querying and Manipulating Data 2-27
In Example 2–45, you will use the DECODE function to assign possible salary increases
based on the job_id value.
Example 2–45 Using the DECODE Function
SELECT first_name || ' ' || last_name "Name",
job_id "Job", salary "Current Pay",
DECODE(job_id,
'PU_CLERK', salary * 1.10,
'SH_CLERK', salary * 1.15,
'ST_CLERK', salary * 1.20,
salary) "Proposed Salary"
FROM employees;
The results of the query appear.
Name Job Current Pay Proposed Salary
-------------------------- ---------------- --------------- -------------------
...
Alexander Khoo PU-CLERK 3100 3410
...
Julia Nayer ST_CLERK 3200 3840
...
Winston Taylor SH_CLERK 3200 3680
...
107 rows selected
Your result shows that the values in the 'Proposed Salary' column have been
adjusted based on the job_id value.
Adding, Changing, and Deleting Data
Adding, changing and deleting operations in the database are commonly called Data
Manipulation Language (DML) statements:
An INSERT statement adds new rows to an existing table.
An UPDATE statement modifies the values of a set of existing table rows.
A DELETE statement removes existing rows from a table.
Because these statements change the data in your table, Oracle recommends that you
use transaction management to group all dependent DML statements together.
Inserting Information
When you use the INSERT statement to add a row of data to a table, the data inserted
must be valid for the data type and size of each column of the table.
The general syntax of the INSERT command looks like the following. Note that the list
of values has to be in the same order as the columns of the table.
INSERT INTO table_name VALUES
(list_of_values_for_new_row);
See Also:
Oracle Database SQL Language Reference for information about the
CASE function
Oracle Database SQL Language Reference for information about the
DECODE function
Adding, Changing, and Deleting Data
2-28 Oracle Database 2 Day Developer's Guide
In Example 2–46, you will use the INSERT function to add a new row to the
employees table.
Example 2–46 Using the INSERT Statement When All Information Is Available
INSERT INTO employees VALUES
(10, 'George', 'Gordon', 'GGORDON', '650.506.2222',
'01-JAN-07', 'SA_REP', 9000, .1, 148, 80);
The results of the query appear.
1 row created.
Your result shows that the new row has been successfully added to the employees
table.
When all of the information is not available at the time a new record is added to the
database, Example 2–47 shows how you can insert values only into the specified
known columns of the table and then set the remaining columns to NULL.
Note that if the columns that are set to NULL are specified with a NOT NULL constraint,
this would generate an error.
Example 2–47 Using the INSERT Statement When Some Information Is Not Available
INSERT INTO employees VALUES
(20, 'John', 'Keats', 'JKEATS', '650.506.3333',
'01-JAN-07', 'SA_REP', NULL, .1, 148, 80);
The results of the query appear.
1 row created.
Your result shows that the new row has been successfully added to the employees
table.
Updating Information
When you use the UPDATE statement to update data in a row of a table, the new data
must be valid for the data type and size of each column of the table.
The general syntax of the UPDATE command looks like the following. Note that the
columns that are altered must be identified, and the matching conditions must be met.
UPDATE table_name
SET column_name = value;
WHERE condition;
To update information in a row that is missing data, the missing data column should
be specified. In Example 2–48, you will update the salary column for a previously
inserted record.
Example 2–48 Using the UPDATE Statement to Add Missing Data
UPDATE employees
SET salary = 8500
See Also:
Oracle Database SQL Language Reference for information about
INSERT
Adding, Changing, and Deleting Data
Querying and Manipulating Data 2-29
WHERE last_name = 'Keats';
The results of the query appear.
1 row updated.
Your result shows that the matching row has been updated.
Example 2–49 shows how you can use the UPDATE statement to update multiple rows.
Example 2–49 Using the UPDATE Statement to Change Data
UPDATE employees
SET commission_pct=commission_pct + 0.05
WHERE department_id = 80;
The results of the query appear.
36 rows updated.
Your result shows that the specified rows are updated.
Deleting Information
Using the DELETE statement, you can delete specific rows in a table. If you want to
delete all the rows in the table, the empty table still exists. If you want to remove the
entire table from the database, use the DROP TABLE statement.
Note that if you accidentally delete rows, you can restore the rows with the ROLLBACK
statement.
Example 2–50 shows how you can use the DELETE statement to remove the data you
added previously.
Note the use of the WHERE clause; without it, all the rows are deleted.
Example 2–50 Using the DELETE Statement
DELETE FROM employees
WHERE hire_date = '1-Jan-2007';
The results of the query appear.
2 rows deleted.
Your result shows that the specified rows are deleted.
See Also:
Oracle Database SQL Language Reference for information about
UPDATE
See Also:
Oracle Database SQL Language Reference for information about
DELETE statement
Oracle Database SQL Language Reference for information about
DROP TABLE
Oracle Database SQL Language Reference for information about
ROLLBACK statement
Controlling Transactions
2-30 Oracle Database 2 Day Developer's Guide
Controlling Transactions
Many applications model business processes that require that several different
operations be performed together, or not at all. For example, if a manager left the
company, a row would be inserted into the job_history table to show when that
person left, and all the employees that report to that manager must be re-assigned
within the employees table. This sequence of operations must be treated as a single
unit, or a transaction.
The following transaction control statements manage the changes made by DML
statements and group them into transactions.
The COMMIT statement ends the current transaction and makes all changes
performed in the transaction permanent. COMMIT also erases all savepoints in the
transaction, and releases transaction locks.
The ROLLBACK statement reverses the work done in the current transaction; it
causes all data changes since the last COMMIT or ROLLBACK to be discarded. The
state of the data is then "rolled back" to the state it had prior to the requested
changes.
The SAVEPOINT statement identifies a point in a transaction to which you can
later roll back.
Oracle recommends that you explicitly end transactions using either a COMMIT or a
ROLLBACK statement. If you do not explicitly commit the transaction and the program
terminates abnormally, then Oracle Database automatically rolls back the last
uncommitted transaction.
Committing Transaction Changes
An explicit COMMIT statement ends your transaction, and makes all the changes in the
database permanent. Until you commit a transaction, you can see all of the changes
made by you to the database, but these changes are not final or visible to other users of
the database instance. Once you commit a transaction, all changes become visible to
other users and their statements that execute after your transaction.
You can undo any changes made prior to an explicit COMMIT by a ROLLBACK
statement.
Example 2–51 shows how to use the COMMIT statement after adding a new row to the
regions table.
Example 2–51 Using the COMMIT Statement
INSERT INTO regions VALUES (5, 'Africa');
COMMIT;
The results of the query and COMMIT statement appear.
Commit complete.
If you manually check the contents of the regions table, you will see that it now has
the new row.
Controlling Transactions
Querying and Manipulating Data 2-31
Rolling Back Transaction Changes
The ROLLBACK statement rolls back all of the transactions you have made since the last
COMMIT statement. If you do not have a preceding COMMIT statement in your
program, it rolls back all operations.
Example 2–52 and Example 2–53 show how to use the ROLLBACK statement to undo
changes to the regions table.
Example 2–52 Changing the REGIONS Table
UPDATE regions
SET region_name = 'Just Middle East'
WHERE region_name = 'Middle East and Africa';
The results of the query appear.
1 row updated.
Manually check the contents of the regions table.
You will see that it now has the updated region_name value.
Example 2–53 Performing a ROLLBACK on the Change to the REGIONS Table
ROLLBACK;
Manually check the contents of the regions table by clicking the Refresh icon. You
will see that the region_name value is changed back to the original value.
See Also:
Oracle Database SQL Language Reference
Controlling Transactions
2-32 Oracle Database 2 Day Developer's Guide
Setting Savepoints
You can use the SAVEPOINT statement to identify a point in a transaction to which you
can later roll back. Because you can use as many savepoints as your application
requires, you can implement greater transaction control in your application.
In Example 2–54, you will use the ROLLBACK statement after adding a new row to the
regions table.
Example 2–54 Using the SAVEPOINT Statement
UPDATE regions
SET region_name = 'Middle East'
WHERE region_name = 'Middle East and Africa';
SAVEPOINT reg_rename;
UPDATE countries
SET region_id = 5
WHERE country_id = 'ZM';
SAVEPOINT zambia;
UPDATE countries
SET region_id = 5
WHERE country_id = 'NG';
SAVEPOINT nigeria;
UPDATE countries
SET region_id = 5
WHERE country_id = 'ZW';
SAVEPOINT zimbabwe;
UPDATE countries
SET region_id = 5
WHERE country_id = 'EG';
SAVEPOINT egypt;
ROLLBACK TO SAVEPOINT nigeria;
COMMIT;
The results for each UPDATE and SAVEPOINT statement follow.
1 row updated.
See Also:
Oracle Database SQL Language Reference
Controlling Transactions
Querying and Manipulating Data 2-33
Savepoint created.
Manually check the contents of the regions table. You may need to click the Refresh
icon. You will see that it now has the updated region_name value.
Next, manually check the contents of the countries table. You may need to click the
Refresh icon. You will see that it now has the updated region_name values for
'Zambia' and 'Nigeria', but not for 'Zimbabwe' and 'Egypt'.
Controlling Transactions
2-34 Oracle Database 2 Day Developer's Guide
You can see that the change in data was reversed by the ROLLBACK to the savepoint
nigeria.
See Also:
Oracle Database SQL Language Reference
Creating and Using Database Objects 3-1
3
Creating and Using Database Objects
In this chapter, you will create and use the types of database objects that were
discussed in "Querying and Manipulating Data".
Note that the statements CREATE TABLE, ALTER TABLE, DROP TABLE, and so on,
use an implicit commit, and cannot be rolled back.
This chapter contains the following sections:
Using Data Types on page 3-1
Creating and Using Tables on page 3-2
Using Views on page 3-21
Using Sequences on page 3-25
Using Synonyms on page 3-28
Using Data Types
Data types associate a set of properties with values so you can use these values in the
database. Depending on the data type, Oracle Database can perform different kinds of
operations on the information in the database. For example, it is possible to calculate a
sum of numeric values but not characters.
Oracle Database supports many kinds of data types, including the most common
VARCHAR2(length), NUMBER(precision, scale), DATE, and also
CHAR(length), CLOB, TIMESTAMP, and others. As you create a table, you must
specify data types for each of its columns and (optionally) indicate the longest value
that can be placed in the column.
Some of the data types and their properties you will use here include the following:
The VARCHAR2 stores variable-length character literals, and is the most efficient
option for storing character data. When creating a VARCHAR2 column in a table,
you must specify the maximum number of characters in a column, which is a
length between 1 and 4,000. In the employees table, the first_name column
has a VARCHAR(20) data type and the LAST_NAME column has a VARCHAR2(25)
data type.
An option to the VARCHAR2 data type, NVARCHAR2 stores Unicode variable-length
character literals.
The CHAR data type stores fixed-length character literals; it uses blanks to pad the
value to the specified string length, which is between 1 and 2,000.
An option to the CHAR2 data type, NCHAR stores Unicode fixed-length character
literals.
Creating and Using Tables
3-2 Oracle Database 2 Day Developer's Guide
The CLOB data type is a character large object data type that contains single-byte
or multibyte characters. The maximum size of a CLOB is (4 gigabytes - 1) x
(database block size).
The NUMBER data type stores zero, and integers and real numbers as positive and
negative fixed numbers with absolute values between 1.0 x 10-130 and 1.0 x 10126
using a fixed-point or floating-point format, with decimal-point precision. Oracle
guarantees that NUMBER data types are portable between different operating
systems, and recommends it for most cases where you need to store numeric data.
You can use the precision option to set the maximum number of digits in the
number, and the scale option to define how many of the digits are to the right of
the decimal separator. In the employees table, the salary column is defined as
NUMBER(8,2), providing 6 digits for the primary unit of currency (dollars,
pounds, marks, and so on) and 2 digits for the secondary unit of currency (cents,
pennies, pfennigs, and so on).
For floating-point numbers, Oracle Database provides the numeric BINARY_
FLOAT and BINARY_DOUBLE data types as enhancements to the basic NUMBER
data type. BINARY_FLOAT (32-bit IEEE 754 format) ranges in absolute value
between 1.17549 x e-38F and 3.40282 x e38Fand BINARY_DOUBLE (64-bit IEEE 754
format) ranges in absolute value between 2.22507485850720 x e-308 and
1.79769313486231 x e308. Both use binary precision that enables faster arithmetic
calculations and often reduces storage requirements.
The DATE data type stores point-in-time values, dates and times; this includes the
century, year, month, day, hours, minutes, and seconds. The valid date range is
from January 1, 4712 BC to December 31, 9999 AD. Oracle Database supports
many different formats for displaying date and time values. In the employees
table, the hire_date column is defined as a DATE.
The TIMESTAMP data type stores values that are precise to fractional seconds, and
is therefore useful in applications that must track event order.
The TIMESTAMP WITH TIME ZONE data type stores time zone information, and
can therefore record date information that must be coordinated across several
geographic regions.
Creating and Using Tables
Tables are the basic unit of data storage in an Oracle database, and hold all
user-accessible data. Tables are two-dimensional objects made up of vertical columns
that represent the fields of the table and horizontal rows that represent the values for
each record in the table.
In this section, you will create all the necessary tables and other schema objects to
implement an employee performance evaluation process for the existing hr schema.
See Also:
Oracle Database SQL Language Reference
See Also:
"Exploring Tables and Viewing Data" on page 2-3
Creating and Using Tables
Creating and Using Database Objects 3-3
Creating a Table
To implement the employee evaluation process, you will need to establish three tables,
performance_parts, evaluations, and scores.
The performance_parts table lists the categories of performance
measurements, and the relative weight for each item.
The evaluations table will contain the employee's information, evaluation date,
and the job, manager and department at the time of evaluation. You must preserve
this information in this table because at any point in the future, the employee may
change job designation, manager, or department.
The scores table contains the scores assigned to each performance category for
each evaluation.
To create a table using SQL Developer interface:
You will create the performance_parts table using the SQL Developer graphical
interface.
1. In the Connections navigation hierarchy, click the plus sign (+) next to hr_conn to
expand the list of schema objects.
2. Right-click Ta b les.
3. Select New Table.
4. In the Create Table window, enter the following information:
For Schema, select HR.
For Name, enter PERFORMANCE_PARTS.
Creating and Using Tables
3-4 Oracle Database 2 Day Developer's Guide
5. Click the default column that was created with the table.
6. Enter the information for the first column in the table as follows:
For Column Name, enter PERFORMANCE_ID.
For Type, enter VARCHAR2.
For Size, enter 2.
Leave the value of Not Null and Primary Key properties. You will come back to
this later, in "Ensuring Data Integrity".
7. Enter information for the second column as follows:
Creating and Using Tables
Creating and Using Database Objects 3-5
Click Add Column.
For Column Name, enter NAME.
For Type, enter VARCHAR2.
For Size, enter 80.
8. Enter information for the third column as follows:
Click Add Column.
For Column Name, enter WEIGHT.
For Type, enter NUMBER.
9. Click OK.
SQL Developer generates the new table, performance_parts.
10. In the Connections navigation hierarchy, click the plus sign (+) next to Tables to
expand the list of tables.
performance_parts is a new table in the hr schema, listed between
locations and regions.
You just created a new table, performance_parts. If you click the table, the table
will appear on the right side of the SQL Developer window, showing its new columns.
If you click the SQL tab, you will see the script that created this table.
In Example 3–1, you will create the evaluations table by entering the information
directly in the SQL Worksheet pane.
Example 3–1 Creating a Table in SQL Script
CREATE TABLE evaluations (
evaluation_id NUMBER(8,0),
employee_id NUMBER(6,0),
evaluation_date DATE,
job_id VARCHAR2(10),
manager_id NUMBER(6,0),
department_id NUMBER(4,0),
total_score NUMBER(3,0)
)
The results of the script follow.
CREATE TABLE succeeded.
You created a new table, evaluations. If you click the table, the table will appear on
the right side of the SQL Developer window, showing its new columns. If you click the
SQL tab, you will see the script that created this table. You may need to click the
Refresh icon.
In Example 3–2, you will create another table, scores, by entering the information in
the SQL Worksheet pane.
Example 3–2 Creating the SCORES Table
CREATE TABLE scores (
evaluation_id NUMBER(8,0),
performance_id VARCHAR2(2),
score NUMBER(1,0)
);
Creating and Using Tables
3-6 Oracle Database 2 Day Developer's Guide
The results of the statement follow.
CREATE TABLE succeed.
You created a new table, scores. If you click the table, the table will appear on the
right side of the SQL Developer window, showing its new columns. If you click the
SQL tab, you will see the script that created this table. You may need to click the
Refresh icon.
Ensuring Data Integrity
The data in the table must satisfy the business rules that are modeled in the
application. Many of these rules can be implemented through integrity constraints
that use the SQL language to explicitly state what type of data values are valid for each
column.
When an integrity constraint applies to a table, all data in the table must conform to
the corresponding rule, so when your application includes a SQL statement that
inserts or modifies data in the table, Oracle Database automatically ensures that the
constraint is satisfied. If you attempt to insert, update, or remove a row that violates a
constraint, the system generates an error, and the statement is rolled back. If you
attempt to apply a new constraint to a populated table, the system may generate an
error if any existing row violates the new constraint.
Because Oracle Database checks that all the data in a table obeys an integrity
constraint much faster than an application can, you can enforce the business rules
defined by integrity constraints more reliably than by including this type of checking
in your application logic.
Understanding Types of Data Integrity Constraints
There are five basic types of integrity constraints:
A NOT NULL constraint ensures that the column contains data (it is not null).
A unique constraint ensures that multiple rows do not have the same value in the
same column. This type of constraint can also be used on combination of columns,
as a composite unique constraint. This constraint ignores null values.
A primary key constraint combines NOT NULL and UNIQUE constraints in a single
declaration; it prevents multiple rows from having the same value in the same
column or combination of columns, and prevents null values.
A foreign key constraint requires that for each value in the column on which the
constraint is defined, there must be a matching value in a specified other table and
column.
A check constraint ensures that a value satisfies a specified condition. Use check
constraints when you need to enforce integrity rules based on logical expressions,
such as comparisons. Oracle recommends that you never use check constraints
when other types of constraints can provide the necessary checking.
See Also:
Oracle Database SQL Language Reference for information on the
CREATE TABLE statement
See Also:
Oracle Database SQL Language Reference for information about
integrity constraints
Creating and Using Tables
Creating and Using Database Objects 3-7
Adding Integrity Constraints
You will now add different types of constraints to the tables you created in "Creating a
Table" on page 3-3.
To Add a NOT NULL Constraint Using the SQL Developer Interface:
You will add a NOT NULL constraint to the table using the SQL Developer graphical
interface.
1. In the Connections navigation hierarchy, click the plus sign (+) next to Tables to
expand the list of tables.
2. Right-click the performance_parts table.
3. Select Edit.
4. In the Edit Table window, follow these steps:
In the Edit Table window, click Columns.
In the Columns list, select NAME.
In the Column Properties section, check Cannot be NULL.
Click OK.
Creating and Using Tables
3-8 Oracle Database 2 Day Developer's Guide
5. In the Confirmation window, click OK.
You have now created a NOT NULL constraint for the name column of the
performance_parts table.
The definition of the name column in the performance_parts table is changed to
the following; note that the constraint is automatically enabled.
"NAME" VARCHAR2(80) NOT NULL ENABLE
Example 3–3 shows how you can add another NOT NULL constraint to the
performance_parts table by entering the required information directly in the SQL
Statement window.
Example 3–3 Adding a NOT NULL Constraint in SQL Script
ALTER TABLE performance_parts
MODIFY weight NOT NULL;
The results of the script follow.
ALTER TABLE performance_parts succeeded.
You just created a NOT NULL constraint for column weight of the performance_
parts table. If you click the SQL tab, you will see that the definition of the weight
column changed. You may need to click the Refresh icon.
"WEIGHT" NUMBER NOT NULL ENABLE
To add a unique constraint using the SQL Developer interface:
You will add a unique constraint to the scores table using the SQL Developer
graphical interface. You could also use the Edit Table window, as in the NOT NULL
constraint, to accomplish this task.
1. In the Connections navigation hierarchy, click the plus sign (+) next to Tables to
expand the list of tables.
2. Right-click the scores table.
3. Select Constraint, and then select Add Unique.
4. In the Add Unique window, enter the following information:
Set the constraint name to SCORES_EVAL_PERF_UNIQUE.
Set Column 1 to EVALUATION_ID.
Set Column 2 to PERFORMANCE _ID.
Click Apply.
Creating and Using Tables
Creating and Using Database Objects 3-9
5. In the Confirmation window, click OK.
You have now created a unique constraint for the scores table.
The following SQL statement was added to your table definition:
CONSTRAINT "SCORES_EVAL_PERF_UNIQUE" UNIQUE ("EVALUATION_ID", "PERFORMANCE_ID")
To add a primary key constraint using the SQL Developer interface:
You will add a primary key constraint to the performance_parts table using the
SQL Developer graphical interface. You could also use the Edit Table window, as in the
NOT NULL constraint, to accomplish this task.
1. In the Connections navigation hierarchy, click the plus sign (+) next to Tables to
expand the list of tables.
2. Right-click the performance_parts table.
3. Select Constraint, and then select Add Primary Key.
4. In the Add Primary Key window, enter the following information:
Set the primary key name to PERF_PERF_ID_PK.
Set Column 1 to PERFORMANCE_ID.
Click Apply.
Creating and Using Tables
3-10 Oracle Database 2 Day Developer's Guide
5. In the Confirmation window, click OK.
You have now created a primary key constraint for the performance_parts
table.
The following SQL statement was added to your table definition:
CONSTRAINT "PERF_PERF_ID_PK" PRIMARY KEY ("PERFORMANCE_ID")
In Example 3–4, you will create a primary key constraint on the evaluations table
by entering the required information directly in the SQL Statement window.
Example 3–4 Adding a Primary Key Constraint in SQL Script
ALTER TABLE evaluations
ADD CONSTRAINT eval_eval_id_pk PRIMARY KEY (evaluation_id);
The results of the script follow.
ALTER TABLE evaluations succeeded.
You just created a primary key eval_eval_id_pk on the evaluations table. If you
click the SQL tab, you will see the following SQL statement was added to your table
definition. You may need to click the Refresh icon.
CONSTRAINT "EVAL_EVAL_ID_PK" PRIMARY KEY ("EVALUATION_ID")
To add a foreign key constraint using the SQL Developer interface:
You will add two foreign key constraints to the scores table using the SQL Developer
graphical interface. You could also use the Edit Table window, as in the NOT NULL
constraint, to accomplish this task.
1. In the Connections navigation hierarchy, the plus sign (+) next to Tables to expand
the list of tables.
2. Right-click the scores table.
3. Select Constraint, and then select Add Foreign Key.
Creating and Using Tables
Creating and Using Database Objects 3-11
4. In the Add Foreign Key window, enter the following information:
Set the foreign key name to SCORES_EVAL_FK.
Set Column Name to EVALUATION_ID.
Set Reference Table Name to EVALUATIONS.
Set Referencing Column to EVALUATION_ID.
Click Apply.
5. In the Confirmation window, click OK.
You have now created a foreign key constraint on the evalution_id column
from the evaluations table.
6. Add another foreign key constraint by repeating steps 2 through 5, with the
following parameters:
Set the foreign key name to SCORES_PERF_FK.
Set Column Name to PERFORMANCE_ID.
Set Reference Table Name to PERFORMANCE_PARTS.
Set Referencing Column to PERFORMANCE_ID.
Click Apply.
The following SQL statements were added to your table definition:
CONSTRAINT "SCORES_EVAL_FK" FOREIGN KEY ("EVALUATION_ID")
REFERENCES "HR"."EVALUATIONS" ("EVALUATION_ID") ENABLE
CONSTRAINT "SCORES_PERF_FK" FOREIGN KEY ("PERFORMANCE_ID")
Creating and Using Tables
3-12 Oracle Database 2 Day Developer's Guide
REFERENCES "HR"."PERFORMANCE_PARTS" ("PERFORMANCE_ID") ENABLE
In Example 3–5, you will create a foreign key constraint on the evaluations table by
entering the required information directly in the SQL Statement window.
Example 3–5 Adding a Foreign Key Constraint in SQL Script
ALTER TABLE evaluations
ADD CONSTRAINT eval_emp_id_fk FOREIGN KEY (employee_id)
REFERENCES employees(employee_id);
The results of the script follow.
ALTER TABLE evaluations succeeded
You have now created a foreign key constraint on the employee_id column from the
employees table. If you click the SQL tab, you will see the following SQL statement
was added to your table definition. You may need to click the Refresh icon.
CONSTRAINT "EVAL_EMP_ID_FK" FOREIGN KEY ("EMPLOYEE_ID")
REFERENCES "HR"."EMPLOYEES" ("EMPLOYEE_ID") ENABLE
To add a check constraint using the SQL Developer interface:
You will add a check constraint to the scores table using the SQL Developer
graphical interface. You could also use the Edit Table window, as in the NOT NULL
constraint, to accomplish this task.
1. In the Connections navigation hierarchy, the plus sign (+) next to Tables to expand
the list of tables.
2. Right-click the scores table.
3. Select Constraint, and then select Add Check.
4. In the Add Check window, enter the following information:
Set the Constraint Name to SCORE_VALID.
Set Check Condition to score >=0 and score <=9.
Set Status to ENABLE.
Click Apply.
Creating and Using Tables
Creating and Using Database Objects 3-13
5. In the Confirmation window, click OK.
You have now created a check constraint on the score column of the scores
table.
The following SQL statement was added to your table definition:
CONSTRAINT "SCORE_VALID" CHECK (score >=0 and score <=9) ENABLE
Adding Data to a Table, Modifying, and Deleting
You can use SQL Developer to enter data into tables, to edit, and to delete existing
data. The following tasks will show these processes for the performance_parts
table.
To add data to a table using the SQL Developer interface:
Follow these steps to add rows of data to the performance_parts table
1. In the Connections navigation hierarchy, double-click the performance_parts
table.
2. Click the Data tab in the performance_parts table display.
3. In the Data pane, click the New Record icon.
Creating and Using Tables
3-14 Oracle Database 2 Day Developer's Guide
4. In the new row, add the following information; you can click directly into the
column, or tab between columns:
Set PERFORMANCE_ID to 'WM'
Set NAME to 'Workload Management'
Set WEIGHT to 0.2
Press the Enter key.
5. Add a second row with the following information: set PERFORMANCE_ID to 'BR,
set NAME to 'Building Relationships, and set WEIGHT to 0.2.
Press the Enter key.
6. Add a third row with the following information: set PERFORMANCE_ID to 'CF', set
NAME to 'Customer Focus', and set WEIGHT to 0.2.
Press the Enter key.
7. Add a fourth row with the following information: set PERFORMANCE_ID to 'CM',
set NAME to 'Communication', and set WEIGHT to 0.2.
Press the Enter key.
8. Add a fifth row with the following information: set PERFORMANCE_ID to 'TW', set
NAME to 'Teamwork', and set WEIGHT to 0.2.
Press the Enter key.
9. Add a sixth row with the following information: set PERFORMANCE_ID to 'RD', set
NAME to 'Results Orientation', and set WEIGHT to 0.2.
Creating and Using Tables
Creating and Using Database Objects 3-15
Press the Enter key.
10. Click the Commit Changes icon.
11. Review and close the Data Editor Log window.
12. Review the new data in the table performance_parts.
You have added 6 rows to the performance_parts table.
To modify table data using the SQL Developer interface:
Follow these steps to change data to the performance_parts table.
1. In the Connections navigation hierarchy, double-click the performance_parts
table.
2. Click the Data tab in the performance_parts table display.
3. In the Data pane, in the 'Workload Management' row, click the weight value,
and enter a new value for '0.3'.
In the 'Building Relationships' row, click the weight value, and enter a
new value for '0.15'.
In the 'Customer Focus' row, click the weight value, and enter a new value for
'0.15'.
Creating and Using Tables
3-16 Oracle Database 2 Day Developer's Guide
4. Press the Enter key.
5. Click the Commit Changes icon.
6. Review and close the Data Editor Log window.
You have now changed values in three rows of the performance_parts table.
To delete table data using the SQL Developer interface:
Imagine that in the company modeled by the hr schema, management decided that
the categories Workload Management and Results Orientation had too much overlap.
You will now remove the row 'Results Orientation' from the performance_
parts table.
1. In the Connections navigation hierarchy, double-click the performance_parts
table.
2. Click the Data tab in the performance_parts table display.
3. In the Data pane, click the 'Results Orientation' row.
4. Click the Delete Selected Row(s) icon.
Creating and Using Tables
Creating and Using Database Objects 3-17
5. Click the Commit Changes icon.
6. Review and close the Data Editor Log window.
You have now removed a row from the performance_parts table.
Indexing Tables
When you define a primary key on a table, Oracle Database implicitly creates an index
on the column that contains the primary key. For example, you can confirm that an
index was created for the evaluations table on its primary key, by looking at its
Indexes pane.
See Also:
Oracle Database SQL Developer User's Guide
Creating and Using Tables
3-18 Oracle Database 2 Day Developer's Guide
In this section, you will learn how to add different types of indexes to the tables you
created earlier.
To create an index using the SQL Developer interface:
Follow these steps to create a new index for the evaluations table.
1. In the Connections navigation hierarchy, right-click the evaluations table.
2. Select Index and then select Create Index.
Alternatively, in the Connections navigation hierarchy, you can right-click Indexes
and select New Index.
3. In the Create Index window, enter the following parameters:
Ensure that the Schema is set to HR.
Set the Name to EVAL_JOB_IX.
Click the Add Column Expression icon, which looks like a 'plus' sign.
Set the Column Name or Expression to JOB_ID.
Set the Order to ASC.
Click OK.
Creating and Using Tables
Creating and Using Database Objects 3-19
You have now created a new index EVAL_JOB_IX on the column JOB_ID in the
evaluations table. You can see this index by finding it in the list of Indexes in the
Connections navigation hierarchy, or by opening the evaluations table and
browsing to the Indexes tab. The following script is the equivalent SQL statement for
creating this index.
CREATE INDEX eval_job_ix ON evaluations (job_id ASC) NOPARALLEL;
To modify an index using SQL Developer interface:
Follow these steps to reverse the sort order of the EVAL_JOB_IX index.
1. In the Connections navigation hierarchy, the plus sign (+) to expand Indexes.
2. Right-click EVAL_JOB_IX, and select Edit.
3. In the Edit Index window, change Order to DESC.
Click OK.
Creating and Using Tables
3-20 Oracle Database 2 Day Developer's Guide
You changed the index. The following script is the equivalent SQL statement for
creating this index:
DROP INDEX eval_job_id;
CREATE INDEX eval_job_ix ON evaluations (job_id DESC) NOPARALLEL;
To delete an index using SQL Developer interface:
Following steps to delete the EVAL_JOB_IX index.
1. In the Connections navigation hierarchy, the plus sign (+) to expand Indexes.
2. Right-click EVAL_JOB_IX, and select Drop.
3. In the Drop window, click Apply.
4. In the Confirmation window, click OK.
You deleted the index EVAL_JOB_IX. The following script is the equivalent SQL
statement for dropping this index.
DROP INDEX "HR"."EVAL_JOB_ID";
Dropping Tables
Sometimes it becomes necessary to delete a table and all its contents from your
schema. To accomplish this, you must use the SQL statement DROP TABLE. You will
use the tables that you already created to learn other concepts, so create a simple table
that you can subsequently delete by running the following script in the SQL Statement
window:
CREATE TABLE temp_table(
id NUMBER(1,0),
name VARCHAR2(10)
);
To delete a table using the SQL Developer interface:
Follow these steps to delete TEMP_TABLE from the hr schema.
1. In the Connections navigation hierarchy, right-click TEMP_TABLE.
2. Select Table, and then select Drop.
See Also:
Oracle Database SQL Language Reference for information on the
CREATE INDEX statement
Oracle Database SQL Language Reference for information on the
ALTER INDEX statement
Oracle Database SQL Language Reference for information on the
DROP INDEX statement
Using Views
Creating and Using Database Objects 3-21
3. In the Drop window, click Apply.
4. In the Confirmation window, click OK.
You deleted the table TEMP_TABLE. The following script is the equivalent SQL
statement for dropping this table.
DROP TABLE "HR"."TEMP_TABLE";
Using Views
Views are logical tables based on one or more tables or views. Views are particularly
useful if your business needs include frequent access to information that is stored in
several different tables.
Creating a View
The standard syntax for creating a view follows:
CREATE VIEW view_name AS query;
To create a view using the SQL Developer interface:
Follow these steps to delete create a new view from the hr schema.
1. In the Connections navigation hierarchy, right-click Views.
2. Select New View.
3. In the Create View window, enter the following parameters:
Ensure that Schema is set to HR.
Set Name to SALESFORCE.
Set the SQL Query to the following:
SELECT first_name || ' ' || last_name "Name", salary*12 "Annual Salary"
FROM employees
WHERE department_id = 80
4. In SQL Parse Results, click Test Synt a x .
See Also:
Oracle Database SQL Language Reference for information on the
DROP TABLE statement
Using Views
3-22 Oracle Database 2 Day Developer's Guide
5. Click OK.
You created a new view. The equivalent SQL statement for creating this view follows:
CREATE VIEW salesforce AS
SELECT first_name || ' ' || last_name "Name",
salary*12 "Annual Salary"
FROM employees
WHERE department_id = 80;
In Example 3–6, you will create a view of all employees in the company and their work
location, similar to the query you used in "Using Character Functions".
Example 3–6 Creating a View in SQL Script
CREATE VIEW emp_locations AS
SELECT e.employee_id,
e.last_name || ', ' || e.first_name name,
d.department_name department,
l.city city,
c.country_name country
FROM employees e, departments d, locations l, countries c
WHERE e.department_id=d.department_id AND
d.location_id=l.location_id AND
l.country_id=c.country_id
ORDER BY last_name;
The results of the script follow.
CREATE VIEW succeeded.
You have now created new view that relies on information in 4 separate tables, or a
4-way JOIN. In the Connections navigation hierarchy, if you click the 'plus' sign next
to Views, you will see emp_locations.
Using Views
Creating and Using Database Objects 3-23
Updating a View
To change the properties of a view in SQL Developer interface:
You will change the salesforce view by adding to it the employees in the Marketing
department, and then rename the view to sales_marketing.
1. In the Connections navigation hierarchy, right-click the salesforce view.
2. Select Edit.
3. In the Edit View window, change the SQL Query by adding the following to the
last line: 'OR department_id = 20'.
Click Tes t S y n t ax.
Click OK.
4. To rename the view, right-click salesforce and select Rename.
Using Views
3-24 Oracle Database 2 Day Developer's Guide
5. In the Rename window, set New View Name to sales_marketing.
Click Apply.
6. In the Confirmation window, click OK.
You changed the view. The equivalent SQL statements for changing and renaming the
view are:
CREATE OR REPLACE VIEW salesforce AS query;
RENAME "SALESFORCE" to SALES_MARKETING;
Dropping a View
To drop a view using the SQL Developer interface:
You will use the DROP VIEW statement to delete the sales_marketing view.
1. In the Connections navigation hierarchy, right-click the sales_marketing view.
2. Select Drop.
3. In the Drop window, click Apply.
4. In the Confirmation window, click OK.
Using Sequences
Creating and Using Database Objects 3-25
You deleted the view. The equivalent SQL statement for dropping the view is:
DROP VIEW sales_marketing;
Using Sequences
Sequences are database objects that generate unique sequential values, which are very
useful when you need unique primary keys. The hr schema already has three such
sequences: departments_seq, employees_seq, and locations_seq.
The sequences are used through these pseudocolumns:
The CURRVAL pseudocolumn returns the current value of a sequence. CURRVAL
can only be used after an initial call to NEXTVAL initializes the sequence.
The NEXTVAL pseudocolumn increments the sequence and returns the next value.
The first time that NEXTVAL is used, it returns the initial value of the sequence.
Subsequent references to NEXTVAL increment the sequence value by the defined
increment, and return the new value.
Note that a sequence is not connected to any other object, except for conventions of
use. When you plan to use a sequence to populate the primary key of a table, Oracle
recommends that you use a naming convention to link the sequence to that table.
Throughout this discussion, the naming convention for such sequences is table_
name_seq.
Creating a Sequence
You can create a sequence in the SQL Developer Interface, or using the SQL Statement
window.
To create a sequence using the SQL Developer interface:
The following steps will create a sequence, evaluations_seq, that you can use for
the primary key of the evaluations table.
1. In the Connections navigation hierarchy, right-click Sequences.
2. Select New Sequence.
See Also:
Oracle Database SQL Language Reference for information on the
CREATE VIEW statement
Oracle Database SQL Language Reference for information on the
DROP VIEW statement
See Also:
Oracle Database SQL Language Reference
Using Sequences
3-26 Oracle Database 2 Day Developer's Guide
3. In the New Sequence window, enter the following parameters:
Ensure that Schema is set to HR.
Set Name to EVALUATIONS_SEQ.
In the Properties tab:
Set Increment to 1.
Set Start with to 1.
Check Order.
Click OK.
You have now created a sequence that can be used for the primary key of the
evaluations table. If you click '+' to expand the Sequence tree, you can see new
sequence. The equivalent SQL statement is:
Using Sequences
Creating and Using Database Objects 3-27
CREATE SEQUENCE evaluations_seq INCREMENT BY 1 START WITH 1 ORDER;
In Example 3–7, you will create another sequence by entering the required information
directly in the SQL Statement window.
Example 3–7 Creating a Sequence Using SQL Script
CREATE SEQUENCE test_seq INCREMENT BY 5 START WITH 5 ORDER;
The results of the script follow.
CREATE SEQUENCE succeeded.
Dropping a Sequence
To delete a sequence, you must use the SQL statement DROP SEQUENCE. To see how a
sequence can be deleted in SQL Developer, you can use the test_seq sequence you
created earlier. If the new sequence does not appear in the Connections hierarchy
navigator, click the refresh icon.
To drop a sequence:
Follow these steps to drop a sequence.
1. In the Connections navigator, right-click the test_seq sequence.
2. In the Drop window, click Apply.
See Also:
Oracle Database SQL Language Reference for information on the
CREATE SEQUENCE statement
Using Synonyms
3-28 Oracle Database 2 Day Developer's Guide
3. In the Confirmation window, click OK.
You have now deleted the sequence test_seq. The equivalent SQL statement
follows:
DROP SEQUENCE "HR"."TEST_SEQ";
Using Synonyms
A synonym is an alias for any schema object and can be used to simplify SQL
statements or even obscure the names of actual database objects for security purposes.
Additionally, if a table is renamed in the database (departments to divisions), you
could create a departments synonym and continue using your application code as
before.
To create a synonym using the SQL Developer interface:
The following steps will create a synonym, positions, that you can use in place of
the jobs schema object.
1. In the Connections navigation hierarchy, right-click Synonyms.
2. Select New Synonym.
3. In the New Synonym window, set the following parameters:
Ensure that Schema is set to HR.
Set Name to POSITIONS.
In the Properties tab:
Select Object Based. This means that the synonym refers to a specific schema
object, such as a table, a view, a sequence, and so on.
Set Object Based to JOBS.
See Also:
Oracle Database SQL Language Reference for information on the
DROP SEQUENCE statement
Using Synonyms
Creating and Using Database Objects 3-29
Click OK.
You created a synonym positions for the jobs table. The equivalent SQL statement
follows:
CREATE SYNONYM positions FOR jobs;
In Example 3–8, you use the new positions synonym in place of the jobs table
name.
Example 3–8 Using a Synonym
SELECT first_name || ' ' || last_name "Name", p.job_title "Position"
FROM employees e, positions p
WHERE e.job_id = p.job_id
ORDER BY last_name;
The results of the query appear.
Name Position
--------------------- -------------------------
Ellen Abel Sales Representative
Sundar Ande Sales Representative
Mozhe Atkinson Stock Clerk
David Austin Programmer
...
197 rows selected
To drop a synonym:
Follow these steps to drop the positions synonym.
1. In the Connections navigator, right-click the positions synonym.
2. Select Drop.
Using Synonyms
3-30 Oracle Database 2 Day Developer's Guide
3. In the Drop window, click Apply.
4. In the Confirmation window, click OK.
You deleted synonym positions. The equivalent SQL statement follows:
DROP SYNONYM positions;
See Also:
Oracle Database SQL Language Reference for information on the
CREATE SYNONYM statement
Oracle Database SQL Language Reference for information on the
DROP SYNONYM statement
Developing and Using Stored Procedures 4-1
4
Developing and Using Stored Procedures
This chapter introduces the use of PL/SQL, the imperative language of Oracle
Database.
This chapter contains the following sections:
Overview of Stored Procedures on page 4-1
Creating and Using Standalone Procedures and Functions on page 4-2
Creating and Using Packages on page 4-9
Using Variables and Constants on page 4-15
Controlling Program Flow on page 4-22
Using Composite Data Structures; Records on page 4-29
Retrieving Data from a Set Using Cursors and Cursor Variables on page 4-32
Using Collections; Index-By Tables on page 4-38
Handling Errors and Exceptions on page 4-41
Overview of Stored Procedures
You already know how to interact with the database using SQL, but it is not sufficient
for building enterprise applications. PL/SQL is a third generation language that has
the expected procedural and namespace constructs, and its tight integration with SQL
makes it possible to build complex and powerful applications. Because PL/SQL is
executed in the database, you can include SQL statements in your code without
having to establish a separate connection.
The main types of program units you can create with PL/SQL and store in the
database are standalone procedures and functions, and packages. Once stored in the
database, these PL/SQL components, collectively known as stored procedures, can be
used as building blocks for several different applications.
While standalone procedures and functions are invaluable for testing pieces of
program logic, Oracle recommends that you place all your code inside a package.
Packages are easier to port to another system, and have the additional benefit of
qualifying the names of your program units with the package name. For example, if
you developed a schema-level procedure called continue in a previous version of
Oracle Database, your code would not compile when you port it to a newer Oracle
Database installation. This is because Oracle recently introduced the statement
CONTINUE that exits the current iteration of a loop and transfers control to the next
iteration. If you developed your procedure inside a package, the procedure package_
name.continue would have been protected from such name capture.
Creating and Using Standalone Procedures and Functions
4-2 Oracle Database 2 Day Developer's Guide
This next section of this chapter is "Creating and Using Standalone Procedures and
Functions" on page 4-2, shows you how to create and use standalone procedures and
functions. You may wish to skip it and move directly to "Creating and Using Packages"
on page 4-9.
Creating and Using Standalone Procedures and Functions
With Oracle Database, you can store programs in the database, so commonly used
code can be written and tested once and then accessed by any application that requires
it. Program units that reside in the database also ensure that when the code is invoked
the data is processed consistently, which leads to ease and consistency of the
application development process.
Schema-level, or standalone subprograms such as functions (which return a value) and
procedures (which do not return a value) are compiled and stored in an Oracle
Database. Once compiled, they become stored procedure or stored function schema
objects, and can be referenced or called by any applications connected to Oracle
Database. At invocation, both stored procedures and functions can accept parameters.
Procedures and functions follow the basic PL/SQL block structure, which consists of
the following elements:
A declarative part, sometimes starting with the keyword DECLARE, identifies
variables and constants used in the application logic. This part is optional.
An executable part, starting with BEGIN and ending with END, contains the
application logic. This part is mandatory.
An exception-handling part, starting with EXCEPTION, handles error conditions
that may be raised in the executable part of the block. This part is optional.
The general form of a PL/SQL block follows. Note also that each stored program unit
has a header that names the unit and identifies it as either a function, procedure, or a
package.
Header AS
[declaration statements
...]
BEGIN
...
[EXCEPTION
...]
END;
Creating Procedures and Functions
The SQL statements for creating procedures and functions are CREATE PROCEDURE
and CREATE FUNCTION, respectively. In practice, it is best to use a CREATE OR
REPLACE statement. The general form of these statements follows.
CREATE OR REPLACE procedure_name(arg1 data_type, ...) AS
BEGIN
....
END procedure_name;
See Also:
Oracle Database PL/SQL Language Reference for information on the
syntax for declaring procedures
Creating and Using Standalone Procedures and Functions
Developing and Using Stored Procedures 4-3
CREATE OR REPLACE procedure_name(arg1 data_type, ...) AS
BEGIN
....
END procedure_name;
To create a procedure:
You will create a procedure add_evaluation that creates a new row in the
evaluations table.
1. In the Connections navigation hierarchy, right-click Procedures.
2. Select New Procedure.
3. In the New Procedure window, set the following parameters:
Ensure that Schema is set to HR.
Set Name to ADD_EVALUATION.
In the Parameters tab, click the Add Column icon ('plus' sign) and specify the first
parameter of the procedure. Set Name to eval_id, set Type to NUMBER, set Mode
to IN, and leave Default Value empty.
Similarly, add the following parameters, in this order:
employee_id: set Type to NUMBER, set Mode to IN, and leave Default Value
empty.
evaluation_date: set Type to DATE, set Mode to IN, and leave Default
Value empty.
job_id: set Type to VARCHAR2, set Mode to IN, and leave Default Value
empty.
manager_id: set Type to NUMBER, set Mode to IN, and leave Default Value
empty
department_id: set Type to NUMBER, set Mode to IN, and leave Default
Value empty
Click OK.
Creating and Using Standalone Procedures and Functions
4-4 Oracle Database 2 Day Developer's Guide
4. The ADD_EVALUATION pane opens with the following code.
Note that the tile of the pane is in italic font, which indicates that the procedure is
not saved in the database.
CREATE OR REPLACE
PROCEDURE ADD_EVALUATION
( evaluation_id IN NUMBER
, employee_id IN NUMBER
, evaluation_date IN DATE
, job_id IN VARCHAR2
, manager_id IN NUMBER
, department_id IN NUMBER
) AS
BEGIN
NULL;
END ADD_EVALUATION;
5. From the File menu, select Save to save the new procedures. Alternatively, use the
CTRL + S key combination.
Note that Oracle Database automatically compiles procedures prior to saving
them.
Creating and Using Standalone Procedures and Functions
Developing and Using Stored Procedures 4-5
Note that the tile of the add_evaluation pane is in regular font, not italic; this
indicates that the procedure is saved to the database
To create a function:
You will create a new function calculate_score, which calculates the weighted
score based on the performance in a particular category.
1. In the Connections navigation hierarchy, right-click Functions.
2. Select New Function.
3. In the New Function window, set the following parameters:
Ensure that Schema is set to HR.
Set Name to CALCULATE_SCORE.
In the Parameters pane, set the <return> Type to NUMBER.
Similarly, add the following parameters, in this order:
cat: set Type to VARCHAR2, set Mode to IN, and leave Default Value empty.
score: set Type to NUMBER, set Mode to IN, and leave Default Value empty
weight: set Type to NUMBER, set Mode to IN, and leave Default Value empty
Click OK.
Creating and Using Standalone Procedures and Functions
4-6 Oracle Database 2 Day Developer's Guide
4. The calculate_score pane opens with the following code.
Note that the tile of the pane is in italic font, which indicates that the procedure is
not saved in the database.
CREATE OR REPLACE
FUNCTION calculate_score
( cat IN VARCHAR2
, score IN NUMBER
, weight IN NUMBER
) RETURN NUMBER AS
BEGIN
RETURN NULL;
END calculate_score;
5. From the File menu, select Save to save the new function. Alternatively, use the
CTRL + S key combination.
Note that Oracle Database automatically compiles functions prior to saving them.
Note that the tile of the calculate_score pane is in regular font, not italic; this
indicates that the procedure is saved to the database
See Also:
Oracle Database SQL Language Reference for information on the
CREATE PROCEDURE statement
Oracle Database SQL Language Reference for information about the
CREATE FUNCTION statement
Creating and Using Standalone Procedures and Functions
Developing and Using Stored Procedures 4-7
Modifying Procedures and Functions
You already created a new procedure and a new function. However, they both consist
of only the subprogram signature. In this section, you will edit a subprogram body.
To modify a function:
You will edit the function calculate_score to determine the weighted value of an
evaluation for a particular category.
1. In the calculate_score pane, replace the body of the function with the
following code. The new code is in bold font.
BEGIN
RETURN score * weight;
END calculate_score;
2. Compile and save the function; you may use the CTRL + S key combination.
Testing Procedures and Functions
Next, you will test the function that you just modified.
To test a function:
You will test the function calculate_score.
1. In the Connections navigator hierarchy, right-click the calculate_score
function. Select Run.
2. In the Run PL/SQL window, click inside the PL/SQL Block pane, and edit the
assignments for the score and weight variables. The new code is in bold font.
v_Return := CALCULATE_SCORE(
CAT => CAT,
SCORE => 8,
WEIGHT => 0.2
);
Click OK.
3. In the Running - Log pane, note the following results:
Connecting to the database hr_conn.
v_Return = 1.6
Process exited.
See Also:
Oracle Database SQL Language Reference for information about the
ALTER PROCEDURE statement
Oracle Database SQL Language Reference for information about the
ALTER FUNCTION statement
Creating and Using Standalone Procedures and Functions
4-8 Oracle Database 2 Day Developer's Guide
Disconnecting from the database hr_conn.
Dropping Procedures and Functions
You can delete a procedure or function from the database using either the Connection
Navigator, or the SQL DROP statement.
To drop a procedure:
You will drop the procedure ADD_EVALUATION.
1. In the Connections navigator hierarchy, right-click the ADD_EVALUATION
function.
Select Drop.
2. In the Drop window, click Apply.
See Also:
Oracle Database SQL Language Reference for information on the
system privileges users need to run procedures and functions
Oracle Database PL/SQL Language Reference for information on how
to use the EXECUTE IMMEDIATE statement for dynamic SQL
Creating and Using Packages
Developing and Using Stored Procedures 4-9
3. In the Confirmation dialog box, click OK.
You dropped the ADD_EVALUATION procedure from the database.
Creating and Using Packages
In the preceding section, you created and tested procedures and functions that were
schema objects. This approach can be useful for testing subsets or small features of
your application.
Enterprise level applications have much greater complexity: some of the interfaces and
types are directly available to user, while others are used only by ohter functions and
procedures and are never called by the user. PL/SQL enables you to formally state the
relationship between these subprograms by placing them in the same package, which
is a schema object that groups and name-qualifies logically related elements such as
PL/SQL types, variables, functions and procedures. Encapsulating these elements
inside a package also prevents, over the life time of the applications, unintended
consequences such as name capture that is discussed in "Overview of Stored
Procedures" on page 4-1.
Procedures and functions that are defined within a package are known as packaged
subprograms. Procedures and functions that are nested within other subprograms or
within a PL/SQL block are called local subprograms; they exist only inside the
enclosing block and cannot be referenced externally.
Another reason that standalone procedures and functions, like the ones in "Creating
and Using Standalone Procedures and Functions" on page 4-2, are limited to
large-scale development is that they can only send and receive scalar parameters
(NUMBER, VARCHAR2, and DATE), but cannot use a composite structure, RECORD,
unless it is defined in a package specification.
Packages usually have two parts: a specification and a body.
The package is defined by the package specification, which declares the types,
variables, constants, exceptions, cursors, functions and procedures that can be
See Also:
Oracle Database SQL Language Reference for information on the
DROP PROCEDURE statement
Oracle Database SQL Language Reference for information on the
DROP FUNCTION statement
Creating and Using Packages
4-10 Oracle Database 2 Day Developer's Guide
referenced from outside of the package. The specification is the interface to the
package. Applications that call the subprograms in a package only need to know the
names and parameters from the package specification.
The standard package specification has this form:
CREATE OR REPLACE PACKAGE package_name AS
type definitions for records, index-by tables
constants
exceptions
global variable declarations
procedure procedure_1(arg1, ...);
...
function function_1(arg1,...) return datat_ype;
...
END package_name;
The package body contains the code that implements these subprograms, the code for
all private subprograms that can only be invoked from within the package, and the
queries for the cursors. You can change the implementation details inside the package
body without invalidating the calling applications.
The package body has this form:
CREATE OR REPLACE PACKAGE BODY package_name AS
PROCEDURE procedure_1(arg1,...) IS
BEGIN
...
EXCEPTION
...
END procedure_1;
...
FUNCTION function_1(arg1,...) RETURN data_type IS result_variable data_type
BEGIN
...
RETURN result_variable;
EXCEPTION
...
END function_1;
...
END package_name;
Guidelines for Packages
You should become familiar with the packages supplied with Oracle Database and
avoid writing code that duplicates existing features.
You should design and define the package specification before writing the
implementation in the package body. In the specification, include only those parts that
must be publicly visible to calling programs, and hide private declarations within the
package body. This prevents unsafe dependencies of other programs on your
implementation details.
Because PL/SQL has a single-pass compiler, you may find that the dependencies
between correct and valid subprograms within the package body prevent you from
See Also:
Oracle Database PL/SQL Language Reference for more information
on the syntax for creating a package
Creating and Using Packages
Developing and Using Stored Procedures 4-11
successfully compiling your package. You then need to declare these unknown
subprograms near the top of the package body, and specify them later. For this reason,
Oracle recommends that you add new elements at the end of the package specification
or body to minimize possible invalidation of dependents.
Creating a Package
You will create a package that encapsulates all the functionality necessary to perform
employee evaluations. After you create the package, "Modifying a Package" on
page 4-12 explains how you modify the package and to create the package body.
To create a package in SQL Developer navigation hierarchy:
1. In the Connections navigation hierarchy, right-click Packages.
2. Select New Package.
3. In the Create PL/SQL Package dialog, set the following parameters:
Ensure that Schema is set to HR.
Set Name to EMP_EVAL.
Click OK.
See Also:
Oracle Database PL/SQL Language Reference for detailed information
on using PL/SQL packages
Oracle Database PL/SQL Packages and Types Reference for default
packages available with Oracle Database
Creating and Using Packages
4-12 Oracle Database 2 Day Developer's Guide
4. The emp_eval pane opens with the following code:
CREATE OR REPLACE PACKAGE emp_eval AS
/* TODO enter package declarations (types, exceptions, methods etc) here */
END emp_eval;
Note that the title of the pane is in italic font, which indicates that the package is
not saved to the database.
5. From the File menu, select Save to compile and save the new package.
Alternatively, use the CTRL + S key combination.
In the Messages - Log pane, the system confirms that the package was created:
EMP_EVAL Compiled.
Note that the title of the emp_eval pane is in regular font, not italic; this indicates
that the procedure is saved to the database.
Example 4–1 shows how to create a package directly in the SQL Worksheet.
Example 4–1 Creating a PL/SQL Package
CREATE OR REPLACE PACKAGE eval AS
/* package */
END eval;
The results of the script follow.
PACKAGE eval Compiled.
Modifying a Package
In this section, you will modify package emp_eval.
To change the package specification:
You will change the package specification of emp_eval by specifying some functions
and procedures.
See Also:
Oracle Database SQL Language Reference for information on the
CREATE PACKAGE statement (for the package specification)
Creating and Using Packages
Developing and Using Stored Procedures 4-13
1. In the Connections navigation hierarchy, select Packages, and then right-click
emp_eval.
2. Select Edit.
3. In the EMP_EVAL pane, edit the package. The new code is in bold font.
create or replace
PACKAGE emp_eval AS
PROCEDURE eval_department(department_id IN NUMBER);
FUNCTION calculate_score(evaluation_id IN NUMBER
, performance_id IN NUMBER)
RETURN NUMBER;
END emp_eval;
4. Compile the package specification.
You will see the following message that confirms that the package compiled
correctly.
EMP_EVAL Compiled.
To create a package body:
You will create a package body for emp_eval by specifying some functions and
procedures.
1. In the Connections navigation hierarchy, right-click emp_eval.
2. Select Create Body.
3. In the emp_eval Body pane, you can see the automatically generated code for the
package body.
Creating and Using Packages
4-14 Oracle Database 2 Day Developer's Guide
CREATE OR REPLACE
PACKAGE BODY emp_eval AS
PROCEDURE eval_department(department_id IN NUMBER) AS
BEGIN
/* TODO implementation required */
NULL;
END eval_department;
FUNCTION calculate_score(evaluation_id IN NUMBER
, performance_id IN NUMBER)
RETURN NUMBER AS
BEGIN
/* TODO implementation required */
RETURN NULL;
END calculate_score;
END emp_eval;
4. Compile and save the package body.
You will see the following message that confirms that the package body compiled
correctly.
EMP_EVAL Body Compiled.
Dropping a Package
You can delete a package from the database either by using the Connections navigator
hierarchy or the SQL DROP statement. When you drop a package, you remove from the
database both the package specification and its package body.
To drop a package:
1. In the Connections navigator hierarchy, select Packages, and then right-click the
EVAL package.
2. Select Drop Package.
See Also:
Oracle Database SQL Language Reference for information on the
CREATE PACKAGE BODY statement
Oracle Database SQL Language Reference for information on the
ALTER PACKAGE statement
Using Variables and Constants
Developing and Using Stored Procedures 4-15
3. In the Drop Package dialog, click Apply.
4. In the Confirmation dialog, click OK.
Using Variables and Constants
One of the significant advantages that PL/SQL offers over SQL is its ability to use
variables and constants in programming constructs.
A variable is defined by the user to hold a specified value of a particular data type.
This value is mutable; it can change at runtime.
A constant holds a value that cannot be changed; the compiler ensures that this value
is immutable and does not compile any code that could change it. You should use
constants in your code instead of direct values because they will make it simpler to
maintenance of your code base over time. When you declare all values that do not
change as constants, this optimizes your compiled code.
PL/SQL Data Types
In addition to the SQL data types such as VARCHAR2, DATE, NUMBER, and so on, Oracle
Database supports data types that you can use only through PL/SQL. These data
types include BOOLEAN, composite data types such as RECORD, reference types such as
REF CURSOR and INDEX BY TABLE, and numerous specialized types that represent
numbers, characters, and date elements. One numeric type, PLS_INTEGER, is
especially useful because it performs binary integer arithmetic and has significant
performance benefits. Note that these PL/SQL types cannot be used at the level of the
schema (and therefore, in tables), but only for types and processes that are defined
within a package.
See Also:
Oracle Database SQL Language Reference for information on the
DROP PACKAGE statement
See Also:
Oracle Database Concepts for information about variables and
constants
Using Variables and Constants
4-16 Oracle Database 2 Day Developer's Guide
Using Variables and Constants
Variables and constants can have any SQL or PL/SQL data type, and are declared in
the declaration block of a subprogram. By default, any variable that is declared has a
value of NULL. When defining a constant, you must use the CONSTANT clause, and
assign a value immediately.
Using Comments
In PL/SQL, in-line comments start with a double hyphen, --, and extend to the end of
the line. Multi-line comments must start with a slash and asterisk, /*, and terminate
with an asterisk and a slash, */.
Using Identifiers
Identifiers name PL/SQL program units such as constants, variables, and
subprograms. All identifiers must have at most 30 characters, and must start with a
letter that is followed by any combination of letters, numerals, and the signs '$', '_',
and '#'. Other characters cannot be used in identifiers.
Note that because PL/SQL is not case-sensitive except in managing string and
character literals, you can use uppercase and lowercase letters interchangeably. This
means that an identifier last_name is equivalent to LAST_NAME. Declaring the
second identifier generates an error.
You should use meaningful names for your variables and constants, and use a good
naming convention. For example, you could start each constant name with 'cons_'.
Also, remember not to use reserved words as identifiers.
Declaring Variables and Constants
You will update the new function of the emp_eval package, calculate_score,
which calculates the final score for the employee evaluation by combing all weighted
scores in different categories.
See Also:
Oracle Database PL/SQL Language Reference for general information
on PL/SQL data types
Oracle Database PL/SQL Language Reference for information about
the PLS_INTEGER
See Also:
Oracle Database PL/SQL Language Reference
See Also:
Oracle Database PL/SQL Language Reference
See Also:
Oracle Database PL/SQL Language Reference for information on the
scope and visibility of identifiers
Oracle Database PL/SQL Language Reference for information how to
collect data on identifiers
Oracle Database PL/SQL Language Reference for information on how
PL/SQL resolves identifier names
Using Variables and Constants
Developing and Using Stored Procedures 4-17
To declare variables and constants:
1. In the Connections navigation hierarchy, click the plus sign (+) beside Packages to
expand the group.
2. Click the 'plus' beside emp_eval to expand the package.
3. Right-click EMP_EVAL Body.
4. Select Edit.
emp_eval Body pane appears.
5. In the emp_eval Body pane, modify function calculate_score by adding
variables and constants, as shown by the following code. New code is bold font.
FUNCTION calculate_score(evaluation_id IN NUMBER
, performance_id IN NUMBER)
RETURN NUMBER AS
n_score NUMBER(1,0); -- a variable
n_weight NUMBER; -- a variable
max_score CONSTANT NUMBER(1,0) := 9; -- a constant limit check
max_weight CONSTANT NUMBER(8,8) := 1; -- a constant limit check
BEGIN
RETURN NULL;
END calculate_score;
6. Use the key combination 'CTRL'+'S' to save the updated package body.
The following message appears in the Messages-Log pane:
EMP_EVAL Body Compiled
Declaring Variables with Structure Identical to Database Columns
In "Declaring Variables and Constants", you modified function calculate_score by
adding two variables, n_score and n_weight. These variables will represent values
from tables in the database: n_score is stored in the scores table, and n_weight is
stored in the performance_parts table. The data types you used for these variables
match the column data type definitions in the tables.
Over time, applications evolve and the column definitions may change; this may
invalidate the calculate_score function. For easier code maintenance, you should
use special qualifiers that declare variables with data types that match the definitions
of the appropriate columns and rows. These qualifiers are %TYPE and %ROWTYPE.
See Also:
Oracle Database PL/SQL Language Reference for information on
assigning values to variables
Using Variables and Constants
4-18 Oracle Database 2 Day Developer's Guide
The %TYPE attribute supplies the data type of a table column or another variable.
This has the advantages of guaranteeing the correct data type assignment, and the
correct implementation of the function at runtime if the data type of the table
column changes.
The %ROWTYPE attribute supplies the definition of a row in a table to a RECORD
variable. Columns in a table row and the corresponding fields in a RECORD have
the same names and data types. The advantages of using %ROWTYPE are the same
as for %TYPE. See "Using Composite Data Structures; Records" on page 4-29 for a
demonstration.
The following task shows how to use the %TYPE attribute in a function. You will edit
the function calculate_score to assign to variables n_score and n_weight the
data types that match the columns of the source tables. Note that the constants max_
score and max_weight will be used to check equivalence to table values, so they too
must match the table types.
To use the %TYPE attribute:
1. In the emp_eval Body pane, modify function calculate_score by changing
the definition of the variables, as shown by the following code. New code is bold
font.
FUNCTION calculate_score(evaluation_id IN scores.evaluation_id%TYPE
, performance_id IN scores.performance_id%TYPE)
RETURN NUMBER AS
n_score scores.score%TYPE; -- from SCORES
n_weight performance_parts.weight%TYPE; -- from PERFORMANCE_PARTS
max_score CONSTANT scores.score%TYPE := 9; -- a constant limit check
max_weight CONSTANT performance_parts.weight%TYPE := 1;
-- a constant limit check
BEGIN
RETURN NULL;
END calculate_score;
2. In the emp_eval package specification, change the declaration of the function
calculate_score.
FUNCTION calculate_score(evaluation_id IN scores.evaluation_id%TYPE
, performance_id IN scores.performance_id%TYPE)
RETURN NUMBER;
3. In the Connections navigation hierarchy, right-click the emp_eval package, and
select Compile. Alternatively, use the Ctrl+Shift+F9 keyboard shortcut.
The following message appears in the Messages-Log pane:
Using Variables and Constants
Developing and Using Stored Procedures 4-19
EMP_EVAL Body Compiled
To use the %ROWTYPE attribute:
Look at the code used in the eval_department procedure in"Using Explicit Cursors"
on page 4-33.
Assigning Values to Variables
You can assign values to a variable in three general ways: through the assignment
operator, by selecting a value into the variable, or by binding a variable. This section
covers the first two methods. Variable binding is described in 2 Day + guides for
Application Express, Java, .NET, and PHP.
Assigning Values with the Assignment Operator
You can assign values to a variable both in the declaration and the body of a
subprogram.
The following code shows the standard declaration of variables and constants. In
procedures and functions, the declaration block does not use the DECLARE keyword;
instead, it follows the AS keyword of the subprogram definition.
Example 4–2 Assigning variable values in a declaration
In the emp_eval Body pane, modify function calculate_score by adding a new
variable running_total. The value of running_total is also the new return value
of the function. You will set the initial value of the return variable to 0. Note that
running_total is declared as a general NUMBER because it will hold a product of
two NUMBERs with different precision and scale. New code is bold font.
FUNCTION calculate_score(evaluation_id IN scores.evaluation_id%TYPE
, performance_id IN scores.performance_id%TYPE)
RETURN NUMBER AS
n_score scores.score%TYPE; -- from SCORES
n_weight performance_parts.weight%TYPE; -- from PERFORMANCE_PARTS
running_total NUMBER := 0; -- used in calculations
max_score CONSTANT scores.score%TYPE := 9; -- a constant limit check
max_weight CONSTANT performance_parts.weight%TYPE:= 1;
-- a constant limit check
BEGIN
RETURN running_total;
END calculate_score;
Compile the emp_eval Body.
See Also:
Oracle Database PL/SQL Language Reference
See Also:
Oracle Database PL/SQL Language Reference
Oracle Database 2 Day + .NET Developer's Guide
Oracle Database 2 Day + PHP Developer's Guide
Oracle Database 2 Day + Java Developer's Guide.
Oracle Database 2 Day + Application Express Developer's Guide
Using Variables and Constants
4-20 Oracle Database 2 Day Developer's Guide
You can also assign values to variables within the body of a subprogram. You will edit
the function calculate_score by using the running_total variable inside the
body of the function to hold a value of an expression.
Example 4–3 Assigning variable values in the body of a function
In the emp_eval Body pane, modify function calculate_score by assigning to the
running_total variable the value of an expression, as shown by the following code.
New code is bold font.
FUNCTION calculate_score(evaluation_id IN scores.evaluation_id%TYPE
, performance_id IN scores.performance_id%TYPE)
RETURN NUMBER AS
n_score scores.score%TYPE; -- from SCORES
n_weight performance_parts.weight%TYPE; -- from PERFORMANCE_PARTS
running_total NUMBER :=0; -- used in calculations
max_score CONSTANT scores.score%TYPE := 9; -- a constant limit check
max_weight CONSTANT performance_parts.weight%TYPE:= 1;
-- a constant limit check
BEGIN
running_total := max_score * max_weight;
RETURN running_total;
END calculate_score;
Compile and save emp_eval Body.
Assigning Values from the Database
The simplest possible assignment of a value is to use the assignment operator (:=) as
you did for the variable running_total in "Assigning Values with the Assignment
Operator" on page 4-19.
However, the purpose of function calculate_score is to perform a calculation
based on values stored in database tables. To use existing database values in a
procedure, function, or package, you must assign these values to a variable by using a
SELECT INTO statement. You can then use the variable in subsequent computations.
Example 4–4 Assigning to a variable a values from the database
In the emp_eval Body pane, modify function calculate_score by assigning the
table values to the variables n_score and n_weight, and then assigning their
product to the running_total variable, as shown by the following code. New code
is bold font.
FUNCTION calculate_score(evaluation_id IN scores.evaluation_id%TYPE
, performance_id IN scores.performance_id%TYPE)
RETURN NUMBER AS
n_score scores.score%TYPE; -- from SCORES
n_weight performance_parts.weight%TYPE; -- from PERFORMANCE_PARTS
running_total NUMBER := 0; -- used in calculations
max_score CONSTANT scores.score%TYPE := 9; -- a constant limit check
max_weight CONSTANT performance_parts.weight%TYPE:= 1;
-- a constant limit check
BEGIN
See Also:
Oracle Database PL/SQL Language Reference for information on
assigning values to variables
Using Variables and Constants
Developing and Using Stored Procedures 4-21
SELECT s.score INTO n_score FROM scores s
WHERE evaluation_id = s.evaluation_id
AND performance_id = s.performance_id;
SELECT p.weight INTO n_weight FROM performance_parts p
WHERE performance_id = p.performance_id;
running_total := n_score * n_weight;
RETURN running_total;
END calculate_score;
Compile and save emp_eval Body.
Similarly, add a new add_eval procedure for inserting new records into the
evaluations table, based on the content of the corresponding row in the
employees table. Note that add_eval is using the sequence evaluations_seq.
Example 4–5 Creating a new table row with values from another table
In the emp_eval Body pane, above the line END emp_eval, add procedure add_
eval, which uses some columns from the employees table to insert rows into the
evaluations table. Note also that you will create the local function add_eval in the
body of the emp_eval package, but not declare it in the package specification. This
means that add_eval may be invoked only within the emp_eval package, by
another subprogram.
PROCEDURE add_eval(employee_id IN employees.employee_id%TYPE, today IN DATE) AS
-- placeholders for variables
job_id employees.job_id%TYPE;
manager_id employees.manager_id%TYPE;
department_id employees.department_id%TYPE;
BEGIN
-- extracting values from employees for later insertion into evaluations
SELECT e.job_id INTO job_id FROM employees e
WHERE employee_id = e.employee_id;
SELECT e.manager_id INTO manager_id FROM employees e
WHERE employee_id = e.employee_id;
SELECT e.department_id INTO department_id FROM employees e
WHERE employee_id = e.employee_id;
-- inserting a new row of values into evaluations table
INSERT INTO evaluations VALUES (
evaluations_seq.NEXTVAL, -- evaluation_id
employee_id, -- employee_id
today, -- evaluation_date
job_id, -- job_id
manager_id, -- manager_id
department_id, -- department_id
0); -- total_score
END add_eval;
Compile and save emp_eval Body.
See Also:
Oracle Database PL/SQL Language Reference for more information
on assigning values to variables
Controlling Program Flow
4-22 Oracle Database 2 Day Developer's Guide
Controlling Program Flow
Control structures are the most powerful feature of the PL/SQL extension to SQL.
They let you manipulate data and process it using conditional selection, iterative
control, and sequential statements. Conditional selection is a situation where you may
have different types of data values, and may need to perform different processing
steps. Iterative control is a situation where you need to perform repetitive process
steps on similar data. In general, all the lines of code in your programs run
sequentially; sequential control means that you are choosing to execute an alternate
labeled programming branch (GOTO statement).
This section will only cover conditional selection and iterative program flow
structures, such as IF...THEN...ELSE, CASE, FOR...LOOP, WHILE...LOOP, and
LOOP...EXIT WHEN.
Using Conditional Selection Control
Conditional selection structures test an expression that evaluates to a BOOLEAN value
TRUE or FALSE. Depending on the value, control structures execute the assigned
sequence of statements. There are two general selection control mechanisms:
IF...THEN...ELSE and its variations, and the CASE statement.
Using IF...THEN...ELSE Selection Control
The IF...THEN...ELSE statement runs a sequence of statements conditionally. If the
test condition evaluates to TRUE, the program runs statements in the THEN clause. If
the condition evaluates to FALSE, the program runs the statements in the ELSE clause.
You can also use this structure for testing multiple conditions if you include the ELSIF
See Also:
Oracle Database PL/SQL Language Reference for an overview of
PL/SQL control structures
See Also:
Oracle Database PL/SQL Language Reference for more information
on IF...THEN...ELSE selection control
Oracle Database PL/SQL Language Reference for more information
on CASE...WHEN selection control
Controlling Program Flow
Developing and Using Stored Procedures 4-23
keyword. The general form of the IF...THEN...[ELSIF]...ELSE statement
follows:
IF condition_1 THEN
...;
ELSIF condition_2 THEN -- optional
...;
ELSE -- optional
...;
END IF;
For example, the sample company could have a rule that an employee evaluation
should be done twice a year (December 31 and June 30) in the first ten years of
employment, but only once a year (December 31) subsequently. You could implement
this rule in an eval_frequency function that determines how many times in each
year an evaluation should be performed by using the IF...THEN...ELSE clause on
the value of the hire_date column.
The function eval_frequency uses the employees.hire_date value to determine
if evaluations should be performed once each year (over 10 years employment) or
twice each year.
Note also that you will create the function eval_frequency in the body of the emp_
eval package, but not declare it in the package specification. This means that eval_
frequency may be invoked only within the emp_eval package, by another
subprogram.
Example 4–6 Using the IF... THEN...ELSE Selection Control
In the emp_eval Body pane, add eval_frequency function immediately before the
END emp_eval; statement, as shown by the following code. The control structures
are in bold font.
FUNCTION eval_frequency (employee_id IN employees.employee_id%TYPE)
RETURN PLS_INTEGER AS
hire_date employees.hire_date%TYPE; -- start of employment
today employees.hire_date%TYPE; -- today's date
eval_freq PLS_INTEGER; -- frequency of evaluations
BEGIN
SELECT SYSDATE INTO today FROM DUAL; -- set today's date
SELECT e.hire_date INTO hire_date -- determine when employee started
FROM employees e
WHERE employee_id = e.employee_id;
IF((hire_date + (INTERVAL '120' MONTH)) < today) THEN
eval_freq := 1;
ELSE
eval_freq := 2;
END IF;
RETURN eval_freq;
END eval_frequency;
Compile and save emp_eval Body.
See Also:
Oracle Database PL/SQL Language Reference for more information
on IF...THEN...ELSE selection control
Controlling Program Flow
4-24 Oracle Database 2 Day Developer's Guide
Using CASE...WHEN Selection Control
The CASE...WHEN construct is a good alternative to nested IF...THEN statements if
the variable that determines the course of action has several possible values. The CASE
evaluates a condition, and performs a different action for each possible value.
Whenever possible, use the CASE...WHEN statement instead of IF...THEN, both for
readability and efficiency. The general form of the CASE...WHEN construct follows:
CASE condition
WHEN value_1 THEN expression_1;
WHEN value_2 THEN expression_2;
...
ELSE expression_default;
END CASE;
Suppose that in the make_evaluation function from "Using IF...THEN...ELSE
Selection Control" on page 4-22, you wanted to notify the hr user if a long-time
employee who holds one of a select positions should be considered for a salary raise.
Depending on the value of employees.job_id, the program logic should notify the
user of the suggested salary raise.
Note that you will use the DBMS_OUTPUT.PUT_LINE procedure, described in Oracle
Database PL/SQL Packages and Types Reference.
Example 4–7 Using CASE...WHEN Conditional Control
In the emp_eval Body pane, edit eval_frequency function to add a job_id
variable and a CASE statement that is based on the value of the job_id, as shown by
the following code. New code is in bold font.
FUNCTION eval_frequency (employee_id IN employees.employee_id%TYPE)
RETURN PLS_INTEGER AS
hire_date employees.hire_date%TYPE; -- start of employment
today employees.hire_date%TYPE; -- today's date
eval_freq PLS_INTEGER; -- frequency of evaluations
job_id employees.job_id%TYPE; -- category of the job
BEGIN
SELECT SYSDATE INTO today FROM DUAL; -- set today's date
SELECT e.hire_date INTO hire_date -- determine when employee started
FROM employees e
WHERE employee_id = e.employee_id;
IF((hire_date + (INTERVAL '120' MONTH)) < today) THEN
eval_freq := 1;
/* Suggesting salary increase based on position */
SELECT e.job_id INTO job_id FROM employees e
WHERE employee_id = e.employee_id;
CASE job_id
WHEN 'PU_CLERK' THEN DBMS_OUTPUT.PUT_LINE(
'Consider 8% salary increase for employee number ' || employee_id);
WHEN 'SH_CLERK' THEN DBMS_OUTPUT.PUT_LINE(
'Consider 7% salary increase for employee number ' || employee_id);
WHEN 'ST_CLERK' THEN DBMS_OUTPUT.PUT_LINE(
'Consider 6% salary increase for employee number ' || employee_id);
WHEN 'HR_REP' THEN DBMS_OUTPUT.PUT_LINE(
'Consider 5% salary increase for employee number ' || employee_id);
WHEN 'PR_REP' THEN DBMS_OUTPUT.PUT_LINE(
'Consider 5% salary increase for employee number ' || employee_id);
WHEN 'MK_REP' THEN DBMS_OUTPUT.PUT_LINE(
Controlling Program Flow
Developing and Using Stored Procedures 4-25
'Consider 4% salary increase for employee number ' || employee_id);
ELSE DBMS_OUTPUT.PUT_LINE(
'Nothing to do for employee #' || employee_id);
END CASE;
ELSE
eval_freq := 2;
END IF;
RETURN eval_freq;
END eval_frequency;
Compile and save emp_eval Body.
Using Iterative Control
Iteration structures, or loops, execute a sequence of statements repeatedly. There are
three basic types of loops, the FOR...LOOP, the WHILE...LOOP, and the
LOOP...EXIT WHEN.
Using the FOR...LOOP
The FOR...LOOP repeats a sequence of steps a defined number of times and uses a
counter variable that must be in the defined range of integers to run the loop. The loop
counter is implicitly declared in the FOR...LOOP statement, and implicitly
incremented every time the loop runs. Note that the value of the loop counter can be
used within the body of the loop, but it cannot be changed programmatically. The
FOR...LOOP statement has the following form:
FOR counter IN integer_1..integer_2 LOOP
...
END LOOP;
Suppose that in addition to recommending that some employees receive a raise, as
described in "Using CASE...WHEN Selection Control" on page 4-24, function eval_
frequency prints how the salary for the employee would change over a set number
of years if this increase in salary continued.
Note that you will use the DBMS_OUTPUT.PUT procedure, described in Oracle Database
PL/SQL Packages and Types Reference.
Example 4–8 Using FOR...LOOP iterative control
In the emp_eval Body pane, edit eval_frequency function so that it uses the
proposed salary increase (sal_raise) that is assigned in the CASE block to print the
proposed salary over a number of years, starting with the current salary, salary. The
new code is in bold font.
FUNCTION eval_frequency (employee_id IN employees.employee_id%TYPE)
RETURN PLS_INTEGER AS
See Also:
Oracle Database PL/SQL Language Reference for more information
on CASE...WHEN selection control
See Also:
Oracle Database PL/SQL Language Reference for more information
on controlling LOOP iterations
Controlling Program Flow
4-26 Oracle Database 2 Day Developer's Guide
hire_date employees.hire_date%TYPE; -- start of employment
today employees.hire_date%TYPE; -- today's date
eval_freq PLS_INTEGER; -- frequency of evaluations
job_id employees.job_id%TYPE; -- category of the job
salary employees.salary%TYPE; -- current salary
sal_raise NUMBER(3,3) := 0; -- proposed % salary increase
BEGIN
SELECT SYSDATE INTO today FROM DUAL; -- set today's date
SELECT e.hire_date INTO hire_date -- determine when employee started
FROM employees e
WHERE employee_id = e.employee_id;
IF((hire_date + (INTERVAL '120' MONTH)) < today) THEN
eval_freq := 1;
/* Suggesting salary increase based on position */
SELECT e.job_id INTO job_id FROM employees e
WHERE employee_id = e.employee_id;
SELECT e.salary INTO salary FROM employees e
WHERE employee_id = e.employee_id;
CASE job_id
WHEN 'PU_CLERK' THEN sal_raise := 0.08;
WHEN 'SH_CLERK' THEN sal_raise := 0.07;
WHEN 'ST_CLERK' THEN sal_raise := 0.06;
WHEN 'HR_REP' THEN sal_raise := 0.05;
WHEN 'PR_REP' THEN sal_raise := 0.05;
WHEN 'MK_REP' THEN sal_raise := 0.04;
ELSE NULL; -- job type does not match ones that should consider increases
END CASE;
/* If a salary raise is not zero, print the salary schedule */
IF (sal_raise != 0) THEN -- start code for salary schedule printout
BEGIN
DBMS_OUTPUT.PUT_LINE('If the salary ' || salary || ' increases by ' ||
ROUND((sal_raise * 100),0) ||
'% each year over 5 years, it would be ');
FOR loop_c IN 1..5 LOOP
salary := salary * (1 + sal_raise);
DBMS_OUTPUT.PUT (ROUND(salary,2) ||', ');
END LOOP;
DBMS_OUTPUT.PUT_LINE('in successive years.');
END;
END IF;
ELSE
eval_freq := 2;
END IF;
RETURN eval_freq;
END eval_frequency;
Compile the emp_eval Body.
Controlling Program Flow
Developing and Using Stored Procedures 4-27
Using the WHILE...LOOP
The WHILE...LOOP repeats as long as a condition holds TRUE. The condition
evaluates at the top of each loop and if TRUE, the statements in the body of the loop
run. If the condition is FALSE or NULL, the control passes to the next statement after
the loop. The general form of the WHILE...LOOP control structure follows.
WHILE condition LOOP
...
END LOOP;
Note that the WHILE...LOOP may run indefinatelly, so use it with care.
Suppose that the EVAL_FREQUENCY function in "Using the FOR...LOOP" on page 4-25
uses the WHILE...LOOP instead of the FOR...LOOP, and terminates after the
proposed salary reaches the upper salary limit for the job_id.
Example 4–9 Using WHILE...LOOP Iterative Control
In the emp_eval Body pane, edit eval_frequency function so that it uses the
proposed salary increase (sal_raise) that is assigned in the CASE block to print the
proposed salary over a number of years and stops when it reaches the maximum level
possible for the job_id. The new code is in bold font.
FUNCTION eval_frequency (employee_id IN employees.employee_id%TYPE)
RETURN PLS_INTEGER AS
hire_date employees.hire_date%TYPE; -- start of employment
today employees.hire_date%TYPE; -- today's date
eval_freq PLS_INTEGER; -- frequency of evaluations
job_id employees.job_id%TYPE; -- category of the job
salary employees.salary%TYPE; -- current salary
sal_raise NUMBER(3,3) := 0; -- proposed % salary increase
sal_max jobs.max_salary%TYPE; -- maximum salary for a job
BEGIN
SELECT SYSDATE INTO today FROM DUAL; -- set today's date
SELECT e.hire_date INTO hire_date -- determine when employee started
FROM employees e
WHERE employee_id = e.employee_id;
IF((hire_date + (INTERVAL '120' MONTH)) < today) THEN
eval_freq := 1;
/* Suggesting salary increase based on position */
SELECT e.job_id INTO job_id FROM employees e
WHERE employee_id = e.employee_id;
SELECT e.salary INTO salary FROM employees e
WHERE employee_id = e.employee_id;
SELECT j.max_salary INTO sal_max FROM jobs j
WHERE job_id = j.job_id;
CASE job_id
WHEN 'PU_CLERK' THEN sal_raise := 0.08;
WHEN 'SH_CLERK' THEN sal_raise := 0.07;
WHEN 'ST_CLERK' THEN sal_raise := 0.06;
WHEN 'HR_REP' THEN sal_raise := 0.05;
WHEN 'PR_REP' THEN sal_raise := 0.05;
See Also:
Oracle Database PL/SQL Language Reference for information on the
syntax of LOOP statements
Controlling Program Flow
4-28 Oracle Database 2 Day Developer's Guide
WHEN 'MK_REP' THEN sal_raise := 0.04;
ELSE NULL;
END CASE;
/* If a salary raise is not zero, print the salary schedule */
IF (sal_raise != 0) THEN -- start code for salary schedule printout
BEGIN
DBMS_OUTPUT.PUT_LINE('If the salary ' || salary || ' increases by ' ||
ROUND((sal_raise * 100),0) ||
'% each year, it would be ');
WHILE salary <= sal_max LOOP
salary := salary * (1 + sal_raise);
DBMS_OUTPUT.PUT (ROUND(salary,2) ||', ');
END LOOP;
DBMS_OUTPUT.PUT_LINE('in successive years.');
END;
END IF;
ELSE
eval_freq := 2;
END IF;
RETURN eval_freq;
END eval_frequency;
Using the LOOP...EXIT WHEN
The LOOP...EXIT WHEN structure enables you to exit the loop if further processing is
undesirable. If the EXIT WHEN condition evaluates to TRUE, the loop exits and control
passes to the next statement.
The eval_frequency function in "Using the WHILE...LOOP" on page 4-27 uses the
WHILE...LOOP. Note that the last computed value may (and typically does) exceed
the maximum possible value for a salary in the last iteration of the loop. If you use the
LOOP_EXIT WHEN construct instead of the WHILE...LOOP, you can have finer
control for terminating the loop.
Example 4–10 Using LOOP...EXIT WHEN Iterative Control
In the emp_eval Body pane, edit eval_frequency function so that it uses the
proposed salary increase (sal_raise) that is assigned in the CASE block to print the
proposed salary over a number of years and stops when it reaches the maximum level
possible for the job_id. The new code is in bold font.
FUNCTION eval_frequency (employee_id IN employees.employee_id%TYPE)
RETURN PLS_INTEGER AS
hire_date employees.hire_date%TYPE; -- start of employment
today employees.hire_date%TYPE; -- today's date
eval_freq PLS_INTEGER; -- frequency of evaluations
job_id employees.job_id%TYPE; -- category of the job
salary employees.salary%TYPE; -- current salary
sal_raise NUMBER(3,3) := 0; -- proposed % salary increase
sal_max jobs.max_salary%TYPE; -- maximum salary for a job
See Also:
Oracle Database PL/SQL Language Reference for more information
on WHILE...LOOP statements
Using Composite Data Structures; Records
Developing and Using Stored Procedures 4-29
BEGIN
SELECT SYSDATE INTO today FROM DUAL; -- set today's date
SELECT e.hire_date INTO hire_date -- determine when employee started
FROM employees e
WHERE employee_id = e.employee_id;
IF((hire_date + (INTERVAL '120' MONTH)) < today) THEN
eval_freq := 1;
/* Suggesting salary increase based on position */
SELECT e.job_id INTO job_id FROM employees e
WHERE employee_id = e.employee_id;
SELECT e.salary INTO salary FROM employees e
WHERE employee_id = e.employee_id;
SELECT j.max_salary INTO sal_max FROM jobs j
WHERE job_id = j.job_id;
CASE job_id
WHEN 'PU_CLERK' THEN sal_raise := 0.08;
WHEN 'SH_CLERK' THEN sal_raise := 0.07;
WHEN 'ST_CLERK' THEN sal_raise := 0.06;
WHEN 'HR_REP' THEN sal_raise := 0.05;
WHEN 'PR_REP' THEN sal_raise := 0.05;
WHEN 'MK_REP' THEN sal_raise := 0.04;
ELSE NULL;
END CASE;
/* If a salary raise is not zero, print the salary schedule */
IF (sal_raise != 0) THEN -- start code for salary schedule printout
BEGIN
DBMS_OUTPUT.PUT_LINE('If the salary ' || salary || ' increases by ' ||
ROUND((sal_raise * 100),0) ||
'% each year, it would be ');
LOOP
salary := salary * (1 + sal_raise);
EXIT WHEN salary > sal_max;
DBMS_OUTPUT.PUT (ROUND(salary,2) ||', ');
END LOOP;
DBMS_OUTPUT.PUT_LINE('in successive years.');
END;
END IF;
ELSE
eval_freq := 2;
END IF;
RETURN eval_freq;
END eval_frequency;
Using Composite Data Structures; Records
A composite data structure, or a record, is a group of related data items stored in
fields, each with its own name and data type. You can think of a record as a variable
that can hold a table row, or some columns from a table row. The fields correspond to
See Also:
Oracle Database PL/SQL Language Reference for more information
on LOOP...EXIT WHEN statement
Using Composite Data Structures; Records
4-30 Oracle Database 2 Day Developer's Guide
table columns. The record structure is very efficient for passing related items to a
subprogram as a single parameter, and for effectively using related fields from
different tables during run time.
You must define a RECORD as a type, and access its fields through the point notation.
The general form for defining and using a record follows:
TYPE record_name IS RECORD( -- define record type
field_1 data_type, -- define fields in record
...
field_n data_type);
...
variable_name record_name; -- define variable of new type
...
BEGIN
...
...variable_name.field1...; -- use fields of new variable
...variable_name.fieldn...;
...
END...;
In the eval_frequency function from "Using the LOOP...EXIT WHEN" on page 4-28,
you used several related parameters. You can use the RECORD construct to combine
some of these items into a single parameter.
You will create a type that will contain the upper and lower limits for a job
specification.
To create a RECORD type:
1. In the Connections navigation hierarchy, click the plus sign (+) beside Packages to
expand the group.
2. Right-click EMP_EVAL.
3. Select Edit.
The emp_eval pane appears. It shows the specification of the emp_eval package.
4. In the emp_eval package specification, immediately before the closing line of the
package specification, END emp_eval, enter the definition of a record type sal_
info, which contains the fields necessary for evaluating salary levels.
TYPE sal_info IS RECORD -- type for salary, limits, raises, and adjustments
( job_id jobs.job_id%type
, sal_min jobs.min_salary%type
, sal_max jobs.max_salary%type
, salary employees.salary%type
, sal_raise NUMBER(3,3) );
5. Compile and save emp_eval.
The following message appears in the Messages-Log pane:
EMP_EVAL Compiled
Once you declare a new RECORD type in the package specification, you can use it
inside the package body to declare variables of that type. You will create a new
procedure, salary_schedule, and invoke it from the eval_frequency function
using a variable of type sal_info.
Note that PL/SQL compilation is a single path process; if a subprogram is declared
after its client subprogram, PL/SQL compiler throws an error. To work around this
Using Composite Data Structures; Records
Developing and Using Stored Procedures 4-31
situation, you could declare all the subprograms that are not already declared in the
package specification at the top of the package body. The definition of the subprogram
can be anywhere within the package body. See step 2 in the following task on
instructions for declaring function eval_frequency and procedures salary_
schedule and add_eval.
To use a RECORD type:
1. In the emp_eval Body pane, add the definition of the salary_schedule
procedure immediately before the END emp_eval statement, as shown by the
following code. Note that this code is similar to the content of the BEGIN...END
block in eval_frequency that executes if the salary raise is nonzero.
PROCEDURE salary_schedule(emp IN sal_info) AS
accumulating_sal NUMBER; -- accumulator
BEGIN
DBMS_OUTPUT.PUT_LINE('If the salary of ' || emp.salary ||
' increases by ' || ROUND((emp.sal_raise * 100),0) ||
'% each year, it would be ');
accumulating_sal := emp.salary; -- assign value of sal to accumulator
WHILE accumulating_sal <= emp.sal_max LOOP
accumulating_sal := accumulating_sal * (1 + emp.sal_raise);
DBMS_OUTPUT.PUT (ROUND( accumulating_sal,2) ||', ');
END LOOP;
DBMS_OUTPUT.PUT_LINE('in successive years.');
END salary_schedule;
2. In the emp_eval Body pane, near the top of the emp_eval body definition, enter
declarations for eval_frequency and salary_schedule. New code is in bold
font.
create or replace
PACKAGE BODY emp_eval AS
/* local subprogram declarations */
FUNCTION eval_frequency (employee_id employees.employee_id%TYPE) RETURN NUMBER;
PROCEDURE salary_schedule(emp IN sal_info);
PROCEDURE add_eval(employee_id IN NUMBER, today IN DATE);
/* subprogram definition */
PROCEDURE eval_department (dept_id IN NUMBER) AS
...
3. In the emp_eval Body pane, edit eval_frequency function so that it uses the
new sal_info type as variable emp_sal, populates its fields, and invokes
salary_schedule. Note that the code that was previously executed if the salary
raise was nonzero is no longer part of this function; it has been incorporated into
the salary_schedule procedure. Note also that the declarations at the top of the
functions changed. New code is in bold font.
FUNCTION eval_frequency (employee_id employees.employee_id%TYPE)
RETURN PLS_INTEGER AS
hire_date employees.hire_date%TYPE; -- start of employment
today employees.hire_date%TYPE; -- today's date
eval_freq PLS_INTEGER; -- frequency of evaluations
emp_sal SAL_INFO; -- record for fields associated
-- with salary review
BEGIN
SELECT SYSDATE INTO today FROM DUAL; -- set today's date
SELECT e.hire_date INTO hire_date -- determine when employee started
Retrieving Data from a Set Using Cursors and Cursor Variables
4-32 Oracle Database 2 Day Developer's Guide
FROM employees e
WHERE employee_id = e.employee_id;
IF((hire_date + (INTERVAL '120' MONTH)) < today) THEN
eval_freq := 1;
/* populate emp_sal */
SELECT e.job_id INTO emp_sal.job_id FROM employees e
WHERE employee_id = e.employee_id;
SELECT j.min_salary INTO emp_sal.sal_min FROM jobs j
WHERE emp_sal.job_id = j.job_id;
SELECT j.max_salary INTO emp_sal.sal_max FROM jobs j
WHERE emp_sal.job_id = j.job_id;
SELECT e.salary INTO emp_sal.salary FROM employees e
WHERE employee_id = e.employee_id;
emp_sal.sal_raise := 0; -- default
CASE emp_sal.job_id
WHEN 'PU_CLERK' THEN emp_sal.sal_raise := 0.08;
WHEN 'SH_CLERK' THEN emp_sal.sal_raise := 0.07;
WHEN 'ST_CLERK' THEN emp_sal.sal_raise := 0.06;
WHEN 'HR_REP' THEN emp_sal.sal_raise := 0.05;
WHEN 'PR_REP' THEN emp_sal.sal_raise := 0.05;
WHEN 'MK_REP' THEN emp_sal.sal_raise := 0.04;
ELSE NULL;
END CASE;
/* If a salary raise is not zero, print the salary schedule */
IF (emp_sal.sal_raise != 0) THEN salary_schedule(emp_sal);
END IF;
ELSE
eval_freq := 2;
END IF;
RETURN eval_freq;
END eval_frequency;
4. Compile and save emp_eval Body.
The following message appears in the Messages - Log pane:
EMP_EVAL Body Compiled
Retrieving Data from a Set Using Cursors and Cursor Variables
A cursor is a type of pointer that is built into PL/SQL for querying the database,
retrieving a set of records (a result set), and enabling the developer to access these
records one row at a time. A cursor is a handle or a name for a private in-memory SQL
area that holds a parsed statement and related information. Oracle Database implicitly
manages cursors. However, there are a few interfaces that enable you to use cursors
explicitly, as a named resource within a program to more effectively parse embedded
SQL statements. The two main types of cursors are therefore defined as:
See Also:
Oracle Database PL/SQL Language Reference for information on
collections and records
Retrieving Data from a Set Using Cursors and Cursor Variables
Developing and Using Stored Procedures 4-33
Implicit cursors can be used in PL/SQL without explicit code to process the
cursor itself. A result set that is returned by the cursors can be used
programmatically, but there is no programmatic control over the cursor itself.
Explicit cursors allow you to programmatically manage the cursor, and give you a
detailed level of control over record access in the result set.
Each user session may have many open cursors, up to the limit set by the initialization
parameter OPEN_CURSORS, which is 50 by default. You should ensure that your
applications close cursors to conserve system memory. If a cursor cannot be opened
because the OPEN_CURSORS limit is reached, contact the database administrator to
alter the OPEN_CURSORS initialization parameter.
Using Explicit Cursors
The implicit cursor, such as in a FOR...LOOP, are generally more efficient than an
explicit cursor. However, explicit cursors may be more appropriate for your program,
and they also allow you to manage specific in-memory areas as a named resource.
An explicit cursor has the attributes described in the following table:
An explicit cursor must be defined as a variable of the same type as the columns it
fetches; the data type of the record is derived from the cursor definition. Explicit
cursors must be opened and may then retrieve rows within a LOOP...EXIT WHEN
structure and then closed. The general form for using cursors follows:
DECLARE
CURSOR cursor_name type IS query_definition;
OPEN cursor_name
LOOP
FETCH record;
EXIT WHEN cursor_name%NOTFOUND;
...; -- process fetched row
END LOOP;
CLOSE cursor_name;
This is what happens during the life time of a cursor:
The OPEN statement parses the query identified by the cursor, binds the inputs,
and ensures that you can successfully fetch records from the result set.
The FETCH statement runs the query, and then finds and retrieves the matching
rows. You will need to define and use local variables as buffers for the data
returned by the cursor, and then process the specific record.
See Also:
Oracle Database Concepts for information about cursors
Cursor Attribute Description
%NOTFOUND
Returns TRUE or FALSE, based on the results of the last fetch.
%FOUND
Returns TRUE or FALSE, based on the results of the last fetch; negation
of the %NOTFOUND results.
%ROWCOUNT
Returns the number of rows fetched. Can be called at any time after the
first fetch. Also returns the number of rows affected from UPDATE and
DELETE statements.
%ISOPEN
Returns TRUE if a cursor is still open.
Retrieving Data from a Set Using Cursors and Cursor Variables
4-34 Oracle Database 2 Day Developer's Guide
The CLOSE statement completes cursor processing and closes the cursor. Note that
once a cursor is closed you cannot retrieve additional records from the result set.
You can implement procedure eval_department, which you declared in "Creating a
Package" on page 4-11, using a cursor for each employee record that matches the
query.
Example 4–11 Using a cursor to retrieve rows form a result set
The cursor emp_cursor fetches individual rows from the result set. Depending on the
value of the eval_frequency function for each row and the time of the year that the
eval_department procedure runs, a new evaluation record is created for the
employee by invoking the add_eval procedure. Note that the buffer variable, emp_
record, is defined as a %ROWTYPE.
In the emp_eval package specification, edit the declaration of procedure eval_
department:
PROCEDURE eval_department(department_id IN employees.department_id%TYPE);
In the emp_eval Body pane, edit eval_department procedure.
PROCEDURE eval_department(department_id IN employees.department_id%TYPE) AS
-- declaring buffer variables for cursor data
emp_record employees%ROWTYPE;
-- declaring variable to monitor if all employees need evaluations
all_evals BOOLEAN;
-- today's date
today DATE;
-- declaring the cursor
CURSOR emp_cursor IS SELECT * FROM employees e
WHERE department_id = e.department_id;
BEGIN
-- determine if all evaluations must be done or just for newer employees;
-- this depends on time of the year
today := SYSDATE;
IF (EXTRACT(MONTH FROM today) < 6) THEN all_evals := FALSE;
ELSE all_evals := TRUE;
END IF;
OPEN emp_cursor;
-- start creating employee evaluations in a specific department
DBMS_OUTPUT.PUT_LINE('Determining evaluations necessary in department # ' ||
department_id);
LOOP
FETCH emp_cursor INTO emp_record; -- getting specific record
EXIT WHEN emp_cursor%NOTFOUND; -- all records are been processed
IF all_evals THEN
add_eval(emp_record.employee_id, today); -- create evals for all
ELSIF (eval_frequency(emp_record.employee_id) = 2) THEN
add_eval(emp_record.employee_id, today); -- create evals; newer employees
END IF;