Ocp - Sql&pl_sql(vol3)

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder Volume Three S Participant Guide

Edition 1.1 M03991 T1001E11

Authors

Copyright EĂOracle Corporation, 1992, 1996. All rights reserved.

Neena Kochhar Debby Kramer

This documentation contains proprietary information of Oracle Corporation; it is provided under a license agreement containing restrictions on use and discloĆ sure and is also protected by copyright law. Reverse engineering of the software is prohibited. If this documentation is delivered to a U.S. Government Agency of the Department of Defense, then it is delivered with Restricted Rights and the folĆ lowing legend is applicable:

Technical Contributors and Reviewers

Restricted Rights Legend

Christian Bauwens Debra Bowman Lenny Brunson Jackie Collins Ralf Durben Brian Fry Anthony Holbrook Karlene Jensen Sarah Jones Glenn Maslen Sundar Nagarathnam Sandra Schrick Ulrike Schwinn Rosemarie Truman Jenny Tsai Laura Van Deusen

Use, duplication or disclosure by the Government is subject to restrictions for commercial computer software and shall be deemed to be Restricted Rights softĆ ware under Federal law, and as set forth in subparagraph (c) (1) (ii) of DFARS 252.227Ć7013, Rights in Technical Data and Computer Software (October 1988). This material or any portion of it may not be copied in any form or by any means without the express prior written permission of the Worldwide Education Services group of Oracle Corporation. Any other copying is a violation of copyright law and may result in civil and/or criminal penalties. If this documentation is delivered to a U.S. Government Agency not within the DeĆ partment of Defense, then it is delivered with Restricted Rights," as defined in FAR 52.227Ć14, Rights in DataĆGeneral, including Alternate III (June 1987). The information in this document is subject to change without notice. If you find any problems in the documentation, please report them in writing to Worldwide Education Services, Oracle Corporation, 500 Oracle Parkway, Box 659806, RedĆ wood Shores, CA 94065. Oracle Corporation does not warrant that this document is error free. SQL*Plus, PL/SQL, Procedure Builder, Developer/2000, Oracle7 Server, Oracle Server, Discoverer/2000, and Designer/2000 are trademarks or registered tradeĆ marks of Oracle Corporation. All other products or company names are used for identification purposes only, and may be trademarks of their respective owners.

Publishers Stephanie Jones Kimberly Lee Jennifer Robertson Mark Turangan

18 Overview of PL/SQL

18Ć2

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Objectives This lesson provides an overview of how to create and use PL/SQL program units and subprograms using Oracle Procedure Builder. At the end of this lesson, you should be able to D

Determine the benefits of accessing the Oracle7 database with PL/SQL.

D

Describe basic PL/SQL program constructs.

D

Describe the Oracle Procedure Builder tool.

Overview of PL/SQL

18Ć3

18Ć4

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Overview What Is PL/SQL? PL/SQL (Procedural Language/SQL) is an extension to SQL, incorporating many of the design features of programming languages of recent years. It allows the data manipulation and query statements of SQL to be included within block-structured and procedural units of code, making PL/SQL a powerful transaction processing language. Benefits from PL/SQL You can take advantage of the procedural capabilities of PL/SQL, which are unavailable in SQL. Modularize Program Development D Group logically related statements within blocks. D

Nest sub-blocks inside larger blocks to build powerful programs.

D

Break down a complex problem into a set of manageable, well-defined, logical modules, and implement them with blocks.

Declare Identifiers D Declare variables, constants, cursors, and exceptions, and then use them in SQL and procedural statements. D

Declare variables belonging to simple and composite datatypes.

D

Declare variables dynamically based upon the data structure of tables and columns in the database.

Program with Procedural Language Control Structures D Execute a sequence of statements conditionally. D

Execute a sequence of statements iteratively in a loop.

D

Process individually the rows returned by a multiple-row query with an explicit cursor.

D

Combine PL/SQL with Oracle tools, such as Developer/2000 Forms, to group associated commands together to control execution.

Handle Errors D Process Oracle7 Server errors with exception handling routines. Declare user-defined error conditions and process them with exception handling routines. Continued

D

Overview of PL/SQL

18Ć5

18Ć6

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Overview

continued

Benefits of PL/SQLĊcontinued Portability D Since PL/SQL is native to Oracle, you can move programs to any host environment that supports Oracle and PL/SQL. D

You can also move code between the Oracle7 Server and your application environment using Procedure Builder’s drag-and-drop capabilities.

Integration D PL/SQL plays a central role to both the Oracle7 Server (through stored procedures, database triggers, and packages) and Oracle development tools (through Developer/2000 component triggers). D

Variables and datatypes in PL/SQL and SQL are compatible. Therefore, PL/SQL bridges the gap between convenient access to database technology and the need for procedural programming capabilities.

Improve Performance D PL/SQL can improve the performance of an application. The benefits differ depending upon the execution environment. D

PL/SQL groups SQL statements together within a single block and sends the entire block to the server in a single call, therefore reducing network traffic.

Overview of PL/SQL

18Ć7

18Ć8

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

PL/SQL Block Structure A basic PL/SQL block of code can contain up to three parts. The order in which the sections of the blocks are written is shown below. Section

Description

Inclusion

Declarative

Contains all variables, constants, cursors, and user-defined exceptions that will be referenced within the Executable section.

Optional

Executable

Contains SQL statements to manipulate data in the database and PL/SQL statements to manipulate data in the block.

Mandatory

Exception Handling

Specifies the actions to perform when errors and abnormal conditions arise within the Executable section.

Optional

Note: In PL/SQL, an error or warning is called an exception. Section keywords DECLARE, BEGIN, and EXCEPTION are not followed by semicolons. However, END and all other PL/SQL statements do require a semicolon to terminate the statement. You can string statements together on the same line. However, this method is not recommended for clarity or editing.

Overview of PL/SQL

18Ć9

18Ć10

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

PL/SQL Block Structure

continued

Every unit of PL/SQL comprises one or more blocks. These blocks can be entirely separate or nested one within another. Therefore, one block can represent a small part of another block, which in turn can be part of the whole unit of code. PL/SQL Program Constructs The following list outlines a variety of different PL/SQL program constructs using the basic PL/SQL block. They are available based on the environment where they are executed. Program Construct Anonymous Block

Description

Availability

Unnamed PL/SQL block that is embedded within an application or is issued interactively.

All PL/SQL environments.

Stored Procedure or Named PL/SQL block that can Function accept parameters and can be invoked repeatedly.

Oracle7 Server with Procedural Extension.

Application Procedure or Function

Named PL/SQL block that can accept parameters and can be invoked repeatedly.

Components of Developer/2000, for example Forms.

Package

Named PL/SQL module that Oracle7 Server with groups together related procedures, Procedural Extension. functions, and identifiers.

Database Trigger

PL/SQL block that is associated with a database table and is fired automatically.

Oracle7 Server with Procedural Extension.

Application Trigger

PL/SQL block that is associated with an application event and is fired automatically.

Components of Developer/2000, for example Forms.

Note: A function is similar to a procedure, except that a function must return a value.

Overview of PL/SQL

18Ć11

18Ć12

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

PL/SQL Block Structure

continued

The two types of constructs covered in this course are anonymous blocks and subprograms. Anonymous Blocks Anonymous blocks are unnamed blocks. They are declared at the point in an application where they are to be executed and are passed to the PL/SQL engine for execution at runtime. You can embed an anonymous block within a precompiler program and within SQL*Plus or Server Manager. Triggers in Developer/2000 components consist of such blocks. Subprograms Subprograms are named PL/SQL blocks. You can declare them either as procedures or as functions. Procedures perform actions, and functions return values. Developer/2000 components allow you to declare procedures and functions as part of the application (a form or report), and call them from other procedures, functions, and triggers.

Overview of PL/SQL

18Ć13

18Ć14

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

The PL/SQL Environment PL/SQL is not an Oracle product in its own right; it is a technology employed by the Oracle7 Server and by certain Oracle tools. Blocks of PL/SQL are passed to and processed by a PL/SQL engine, which may reside within the tool or within the Oracle7 Server. The engine used depends on where the PL/SQL is being invoked. PL/SQL Engine at the Oracle7 Server When you submit blocks from a Pro* program, user-exit, SQL*Plus, or Server Manager, the PL/SQL engine in the Oracle7 Server processes them. It breaks the SQL within the block into separate statements and sends them to the SQL Statement Executor. This means that a single transfer is required to send the block from the application to the Oracle7 Server, thus improving performance especially in a client-server network. Stored subprograms can be referenced by any number of applications connected to the database. PL/SQL in Oracle Tools Many Oracle tools, including Developer/2000, have their own PL/SQL engine, which is independent of the engine present in the Oracle7 Server. The engine filters out SQL statements and sends them individually to the SQL Statement Executor in the Oracle7 Server. It processes the remaining procedural statements in the Procedural Statement Executor, which is within the PL/SQL engine. The Procedural Statement Executor processes data that is local to the application (that is already inside the client environment, rather than the database). This reduces work sent to the Oracle7 Server and the number of memory cursors required. Note: Procedures and functions declared as part of a Developer/2000 application are distinct from those stored in the database, although their general structure is the same. Stored subprograms are database objects and are stored in the Data Dictionary. They can be accessed by any number of applications. Application subprograms pass blocks to that application’s local PL/SQL engine. Work is done at the application site, not at the server site.

Overview of PL/SQL

18Ć15

18Ć16

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

About Procedure Builder Oracle Procedure Builder is a tool you can use to create, execute, and debug PL/SQL programs used in your application tools, such as a form or a report, or on the Oracle7 Server through its graphical interface. Integrated PL/SQL Development Environment Procedure Builder’s development environment contains a built-in editor for you to create or edit subprograms. You can compile, test, and debug your code. Unified ClientĆServer PL/SQL Development Application partitioning through Procedure Builder is available to assist you with distribution of logic between client and server. Users can drag and drop a PL/SQL program unit between the client and the server.

Overview of PL/SQL

18Ć17

18Ć18

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

19 Basics of Procedure Builder

Objectives

19Ć2

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Objectives A key feature of procedural programming is the ability to create and debug code quickly and easily. Procedure Builder provides all of the functionality necessary for you to successfully develop and debug PL/SQL programs. This lesson enables you to manipulate PL/SQL code using Procedure Builder. At the end of this lesson, you should be able to D

Identify the advantages of developing and debugging PL/SQL programs in Procedure Builder.

D

Manage program units by using the Object Navigator.

D

Execute program units and SQL statements by using the PL/SQL Interpreter.

D

Define and compile procedures by using the Program Unit editor.

D

Define and compile database procedures by using the Stored Program Unit editor.

D

Set a breakpoint to suspend program execution.

D

Control the execution of an interrupted PL/SQL program unit.

D

Test possible solutions by changing variables and procedures at runtime.

Basics of Procedure Builder

19Ć3

19Ć4

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Overview Procedure Builder is an integrated development environment. It provides for the editing, compiling, testing, and debugging of PL/SQL programs within a single tool. In addition, it enables developers to drag and drop program units between the client and server. You can improve your application development efforts by using the features available in Procedure Builder. You will become familiar with these features by learning about each component’s function. Once you are familiar with the Procedure Builder environment, you will cover the PL/SQL syntax. Procedure Builder Features Component

Description

Object Navigator

Manage PL/SQL constructs. Perform debug actions.

PL/SQL Interpreter

Debug PL/SQL code. Evaluate PL/SQL code in real time.

Program Unit Editor

Create and edit PL/SQL source code.

Stored Program Unit Editor

Create and edit server-side PL/SQL source code.

Database Trigger Editor

Create and edit database triggers.

For more information, see Oracle Procedure Builder Developer’s Guide, Release 1.5.

Basics of Procedure Builder

19Ć5

Procedural Programming Capabilities

19Ć6

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Oracle Procedure Builder Capabilities Procedure Builder is an integrated, interactive environment that you can use to create, execute, and debug PL/SQL programs. Using Procedure Builder, you can build portable database programs quickly and easily. Integrated PL/SQL Development Environment D

Create and edit subprograms.

D

Compile subprograms to perform syntactical error checking.

D

Test subprograms.

D

Debug subprograms.

Unified ClientĆServer PL/SQL Development D

Create database triggers.

D

Create stored procedures and move them to the client to debug.

D

Build and edit client-side libraries.

D

Create client-side program units and move them to the server.

Basics of Procedure Builder

19Ć7

Procedure Builder Components

Procedure Builder Components

19Ć8

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Procedure Builder Components The Object Navigator The Object Navigator provides an outline style interface to browse objects, view the relationships between them, and edit their properties. The Interpreter The Interpreter is the central debugging workspace of Procedure Builder. It is a two-pane window where you display, debug, and run PL/SQL program units. It also interactively supports the evaluation of PL/SQL constructs, SQL commands, and Procedure Builder commands. The Program Unit Editor The easiest and most common place to enter PL/SQL source code is in the Program Unit editor. You can use it to edit, compile, and browse warning and error messages during application development. The Stored Program Unit Editor The Stored Program Unit editor is a GUI environment for editing server-side packages and subprograms. The compile operation submits the source text to the server-side PL/SQL compiler. To debug server-side program units you can drag a copy of the program unit to the client side using the Object Navigator. The Database Trigger Editor The Database Trigger editor is a GUI environment for editing database triggers. The compile operation submits the source text to the server-side PL/SQL compiler. Online Help Oracle Procedure Builder has a help facility that provides assistance at any time during your session. You can access it from the menu bar.

Basics of Procedure Builder

19Ć9

Using the Quick Tour

19Ć10

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Procedure Builder Components

continued

Using the Quick Tour The Quick Tour, another type of online help, introduces you to product terminology, workflow, and theoretical concepts. This is an excellent resource to get started with Procedure Builder. You can invoke the Quick Tour by choosing the Quick Tour menu item from the Help menu. Procedure Builder Basics Become familiar with the Object Navigator to create and manage your PL/SQL objects. Also, learn the terminology used in the Object Navigator. Developing a Program Unit Review each step to develop a program unit. Create, edit, and compile a PL/SQL program unit using the Program Unit and Database Trigger editors. Debugging a Program Unit This section describes how to debug a program unit using the PL/SQL Interpreter. It describes how to set breakpoints, step through your code, and peruse the Stack node to change the value of the variable. Organizing PL/SQL Step through application partitioning between the client and server, including using libraries and moving subprograms between the client and server. Procedure Builder Features Review the kinds of application-building tasks you can perform using Procedure Builder.

Basics of Procedure Builder

19Ć11

Using Cue Cards

19Ć12

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Procedure Builder Components

continued

Using Cue Cards Developer/2000 provides further help in the form of Cue Cards. Their purpose is to provide just-in-time coaching for frequently performed tasks. You can invoke Cue Cards by selecting the Cue Cards menu item from the Help menu. Cue cards contain two types of content: task and concept. Task cards list the steps to complete a task, while concept cards describe a particular concept with static graphics. When invoked, Cue Cards display a list of topics that are divided into two categories: D

Task: What would you like to do?

D

Concept: Learn About

To display the cue card for any of these topics, click the appropriate button to the left of the item. Cue Cards include a set of six buttons that you use to navigate to the information you require. Button

Action

Next Card

Advance one card.

Back

Return to the previous card.

Tell Me

Obtain more information about a topic.

Show Me

View a short movie or animation on a task.

Options

Set Cue Card options.

Exit

Exit Cue Cards.

Basics of Procedure Builder

19Ć13

The Object Navigator 1 5

5 4 2

3

4

1 Navigator drop down list

2 Subobject indicator

4 Object name

5 Find field

19Ć14

3 Type icon

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

The Object Navigator The Object Navigator provides immediate access to every object in your development environment. You can create and manipulate all of your PL/SQL program units, libraries, debug actions, and variables from the Object Navigator. Object Navigator Components Navigator Component

Description

Navigator drop down list

Indicates the name of the currently selected object.

Subobject indicator

Indicates whether an object can be expanded to view its subobjects.

Type icon

Indicates the object type.

Object name

Indicates the name of the object.

Find field

Used to search for a named object.

Basics of Procedure Builder

19Ć15

The Object Navigator Vertical Button Bar

1 2 3 4

1 Open/save

2 Cut/copy/paste

3 Create/delete

4 Expand/collapse/ expand all/collapse all

19Ć16

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

The Object Navigator

continued

The Object Navigator Vertical Button Bar The Vertical Button Bar on the Object Navigator provides a convenient access for many of the actions frequently performed from the File, Edit, and Navigator menus. Elements of the Vertical Button Bar Element

Description

Open

Opens a library from the file system or from the Oracle7 Server.

Save

Saves a library on the file system or on the Oracle7 Server.

Cut

Cuts the selected object and stores it in the clipboard. Cutting an object also cuts any objects owned by that object.

Copy

Makes a copy of the selected object and stores it in the clipboard. Copying an object also copies any objects owned by that object.

Paste

Pastes the cut or copied module into the selected location. Note that objects must be copied to a valid location in the object hierarchy.

Create

Creates a new instance of the currently selected object.

Delete

Deletes the selected object with confirmation.

Expand

Expands the first level of subobjects of the currently selected object.

Collapse

Collapses the first level of subobjects of the currently selected object.

Expand All

Expands all levels of subobjects of the currently selected object.

Collapse All

Collapses all levels of subobjects of the currently selected object.

Basics of Procedure Builder

19Ć17

Objects of the Navigator

19Ć18

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

The Object Navigator

continued

Object Navigator Objects By using the Object Navigator, you can display a hierarchical listing of all objects you have access to during your session. Object Node

Description

Program Units

PL/SQL construct that can be independently recognized and processed by the PL/SQL compiler.

Program Units – Specification

Name, parameter, and return type (functions only) of the program unit.

Program Units – References

Procedures, functions, anonymous blocks, and tables that the program unit references.

Program Units – Referenced By

Procedures, functions, anonymous blocks, and tables that reference the program unit.

Libraries

Collection of PL/SQL packages, procedures, and functions stored in the database or the file system.

Attached Libraries

Referenced libraries stored in the database or the file system.

Built-in Packages

PL/SQL constructs that can be referenced while debugging program units.

Debug Actions

Actions that enable you to monitor or interrupt the execution of PL/SQL program units.

Stack

Chain of subprogram calls, from the initial entry point down to the currently executing subprogram.

Database Objects

Collection of server-side stored program units, libraries, tables, and views.

Basics of Procedure Builder

19Ć19

19Ć20

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

The Object Navigator

continued

Managing Objects Using the Object Navigator Use the Vertical Button Bar and the Find Field on the Object Navigator to manage PL/SQL constructs, debug actions, and database objects. Creating a Library 1.

Select the Libraries object.

2.

Click the Create button. The new library subobject will appear with a default name.

Saving a Library 1.

Select the Library to save.

2.

Select Save As from the File menu. The Save Library dialog box appears.

3.

Enter a new name for the library (optional). Choose either the File System or the Database radio button. Click the OK button to accept the options.

Expanding a Node 1.

Select a valid node.

2.

Click the Expand button or the Expand All button.

Searching for an Object 1.

Type the name, full or partial, of the object you wish to find in the Find Field. As soon as you begin typing, any match found starting from the top of the Object Navigator is highlighted.

2.

Click the Search Forward or Search Backward button to find additional matches of the search criteria in the Object Navigator.

Basics of Procedure Builder

19Ć21

The Interpreter

19Ć22

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

The Interpreter Using the Interpreter, you can define, display, debug, and run PL/SQL program units. To display the Interpreter at any time, choose the PL/SQL Interpreter from the Tools menu. Interpreter Components Component

Description

Program execution buttons Enables you to execute the Step Into, Step Over, Step Out, Go, and Reset buttons. Close button

Closes the Interpreter.

Source pane

Displays a read-only copy of the program unit currently selected.

Interpreter pane

Provides a command line interface to PL/SQL and Procedure Builder.

Scroll bar

Scrolls the window.

Split bar

Changes the relative amount of space occupied by each pane in the Interpreter.

Executing a PL/SQL Procedure From the PL/SQL prompt in the Interpreter pane, enter the name of the procedure. Terminate the command with a semicolon. Syntax procedure_name [(argument1[, argument2, ...])];

Basics of Procedure Builder

19Ć23

The Interpreter Pane 1 2 3

1 Execute SQL DDL statements.

19Ć24

2 Create and execute an anonymous procedure.

3 Execute a SELECT statement.

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

The Interpreter

continued

The Interpreter Pane Using the Interpreter pane, you can interactively evaluate PL/SQL constructs and SQL commands. Executing SQL DDL Statements From the PL/SQL prompt in the Interpreter pane, enter SQL data definition language statements. To create the B_TEST table, enter the following statement: PL/SQL> CREATE TABLE b_test (col1 NUMBER);

Incremental Compilation of PL/SQL From the PL/SQL prompt in the Interpreter pane, enter PL/SQL statements. For example, to insert three rows into the B_TEST table, enter the following statement: PL/SQL> +> +> +> +> +> +>

PROCEDURE testproc IS BEGIN FOR i IN 1 ..3 LOOP INSERT INTO b_test VALUES (i); END LOOP; END;

Note: Details of the FOR loop will be covered in a later lesson. Executing SQL Statements From the PL/SQL prompt in the Interpreter pane, you can execute SQL statements. For example, to select all columns of the B_TEST table, enter the following statement: PL/SQL> SELECT * FROM b_test;

Basics of Procedure Builder

19Ć25

The Program Unit Editor 1 2

3

4

1 Compile and Source Text maintenance buttons

2 Name drop down list

3 Source Text pane

4 Compilation Message pane

19Ć26

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

The Program Unit Editor Create, edit, and compile PL/SQL source code by using the Program Unit editor. Methods to Display the Program Unit Editor D

Choose Program Unit Editor from the Tools menu.

D

Double-click the icon of a program unit in the Object Navigator.

D

Select Program Units in the Object Navigator and click the Create button.

Program Unit Editor Components Component

Description

Compile button

Compiles the program unit appearing in the Source Text pane.

Apply button

Saves any changes made to the program unit in the editor since it was first opened or since the last apply or revert operation.

Revert button

Restores the state of the program unit to the last apply or revert.

New button

Invokes the New Program Unit dialog box.

Delete button

Deletes the current program unit (with confirmation).

Name drop down list

Displays the name and type of the current program unit. You can use the drop down list to select another program unit to edit.

Source Text pane

Allows you to enter and edit PL/SQL program unit source text.

Compilation Message pane

Displays error messages generated as a result of compilation.

Basics of Procedure Builder

19Ć27

Creating a Program Unit 1

2

1 Create the Program Unit.

19Ć28

2 Write the code.

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

The Program Unit Editor

continued

Creating a Program Unit 1.

Select the Program Units object or subobject.

2.

Click the Create button. The Program Unit dialog box appears.

3.

Enter a name and select a subprogram type radio button. Click the OK button to accept these entries. The Program Unit name appears in the Object Navigator.

4.

The Program Unit editor will be displayed. It contains the subprogram name, BEGIN, and END statements. The cursor is automatically positioned on the line beneath the BEGIN keyword.

Navigating Compilation Errors 1.

Click the Compile button in the Program Unit editor. Error messages generated during compilation are displayed in the Compilation Message pane.

2.

Click an error message. The text cursor positions at the location of the error in the Source Text pane.

Exporting a Program Unit to a Text File 1.

Open the Program unit so that it is displayed in the Program Unit editor.

2.

Select Export from the Edit menu. The Export to dialog box appears.

3.

Enter a name and directory location for the exported program unit text file. Click the OK button to accept these entries.

Program units that reside in the Program Units node are lost when you exit Procedure Builder.

Basics of Procedure Builder

19Ć29

The Stored Program Unit 4

1 2

3

1 Owner drop down list

2 Source Text pane

3 Compilation Message pane

4 Name drop down list

19Ć30

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

The Stored Program Unit Editor Create, edit, and compile server-side program units by using the Stored Program Unit editor. Visually and functionally, creating Stored Program Units in Procedure Builder is very similar to creating client-side Program Units. Connecting to the Oracle7 Server When initially invoked, Procedure Builder is a standalone session. To connect to a database, select Connect from the File menu, and enter the appropriate username, password, and database connection string. Methods to Display the Stored Program Unit Editor D

Choose Stored Program Unit Editor from the Tools menu.

D

Double-click the icon of a stored program unit in the Object Navigator.

Stored Program Unit Editor Components Component

Description

Owner drop down list

Displays usernames whose stored program units you can access.

Source Text pane

Allows you to enter and edit PL/SQL stored program unit source text.

Compilation Message pane

Displays error messages generated as a result of compilation.

Name drop down list

Displays a list of the owner’s stored program units.

Save button

Compiles and saves the program unit to the Oracle7 Server.

Basics of Procedure Builder

19Ć31

Debugging a Stored Program Unit

19Ć32

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Debugging a Stored Program Unit Sometimes developers may not want to (or not have the privilege to) debug or refine a stored program unit in place on the server. In such cases, you may choose to drag a copy of the program unit to the client side. Debugging a Stored Program Unit 1.

Locate the appropriate stored program unit in the Object Navigator.

2.

Hold the mouse button down on the node of the program unit to be copied.

3.

Drag the mouse up to the Program Units object and release the mouse button. The selected stored program unit appears as a Program Units subobject.

4.

Perform debug actions.

5.

From the Object Navigator, select the debugged program unit subobject.

6.

Drag the mouse down to the same Stored Program Units Database Objects node and release the mouse button. The selected program unit appears as a Stored Program Units subobject.

Basics of Procedure Builder

19Ć33

Listing Code in the Source Pane 1 2

3

1 Source pane

19Ć34

2 Navigator pane

3 Interpreter pane

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Performing Debug Actions in the Interpreter You can use the Object Navigator to examine and modify parameters in an interrupted program. By invoking the Object Navigator within the Interpreter, you can perform debugging actions entirely within the Interpreter window. Alternatively, you can interact with the Object Navigator and Interpreter windows separately. Invoking the Object Navigator Pane 1.

Select PL/SQL Interpreter from the Tools menu to open the Interpreter if it is not already open.

2.

Select Navigator Pane from the View menu.

3.

The Navigator pane is inserted between the Source and the Interpreter panes.

4.

Drag the split bars to adjust the size of each pane.

Listing Source Text in the Source Pane 1.

Click the Program Units node in the Navigator pane to expand the list. The list of program units is displayed.

2.

Click the object icon of the program unit to be listed. The source code is listed in the Source pane of the Interpreter.

Basics of Procedure Builder

19Ć35

Setting a Breakpoint

1 2

1 Breakpoint

19Ć36

2 Debug action

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Setting a Breakpoint If you encounter errors while compiling or running your application, you should test the code and determine the cause of the error. To effectively determine the cause of the error, you step through the code, line by line. Eventually, you identify the exact line of code causing the error. One method to assist you is a breakpoint, which halts execution at any given point and permits you to examine the status of the code on a line-by-line basis. Setting a Breakpoint 1.

Double-click the executable line of code on which to break. A “B(n)” is placed in the line where the break is set.

2.

The message “Breakpoint #n installed at line i of name” is shown in the Interpreter pane.

Note: Breakpoints also can be set using debugger commands in the Interpreter pane. Test breakpoints by typing the program unit name at the Interpreter PL/SQL prompt. Monitoring Debug Actions Debug actions, like breakpoints, can be viewed in the Object Navigator under the heading Debug Actions. Double-click the Debug Actions icon to view a description of the breakpoint. Remove breakpoints by double-clicking the breakpoint line number.

Basics of Procedure Builder

19Ć37

Debug Commands

1 2

1 Trigger

19Ć38

2 Subprograms

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Setting a Breakpoint

continued

Stepping Through Code When a breakpoint is reached, you can use a set of commands to step through the code. You can execute these commands by clicking the command buttons on the Interpreter toolbar or by entering the command at the Interpreter prompt. Commands to Step Through Code Command

Description

Step Into

Advances execution into the next executable line of code.

Step Over

Bypasses the next executable line of code and advances to the subsequent line.

Step Out

Resumes to end of current level of code, such as the subprogram.

Go

Resumes execution until either the program unit ends or is interrupted again due to a debug action.

Reset

Aborts execution at the current debug levels.

Basics of Procedure Builder

19Ć39

Stepping Through Code 1 2

3

1 Command buttons

19Ć40

2 Breakpoint

3 Debug message

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Setting a Breakpoint

continued

Determining the Cause of Error Once the breakpoint is found at runtime, you can begin stepping through the code. An arrow (=>) indicates the line of code to execute. 1.

Click the Step Into button. A single line of code is executed. The arrow moves to the next line of code.

2.

Repeat step 1 as necessary until the line causing the error is found. The arrow continues to move forward until the erroneous line of code is found. At that time, PL/SQL displays an error message.

Basics of Procedure Builder

19Ć41

Changing a Value

4

1 2

3

1 Click the Stack node

2 Click the variable value

3 Enter the new value

4 Click the Go button

19Ć42

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Examining Local Variables Using Procedure Builder, you can examine and modify local variables and parameters in an interrupted program. The Stack node in the Navigator pane is where you can view and change the values of local variables and parameters associated with the current program unit located in the Call Stack. When debugging code, check for the absence of values as well as incorrect values. Examining Values and Testing the Possible Solution 1.

Click the Stack node in the Object Navigator or Navigator pane to expand it.

2.

Click the value of the variable to edit. For example, select variable 1. The value “1” becomes an editable field.

3.

Enter the new value and click anywhere in the Navigator pane to end the variable editing, for example, enter “3”. The following statement is displayed in the Interpreter pane. (debug1)PL/SQL> debug.seti(’I’,3);

4.

Click the Go button to resume execution through the end of the program unit.

Note: Variables and parameters can also be changed using commands at the Interpreter PL/SQL prompt.

Basics of Procedure Builder

19Ć43

Summary

19Ć44

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Summary There are clear advantages to developing and debugging PL/SQL programs in Procedure Builder: Application Partitioning D

Manage program units using the Object Navigator.

D

Move program units between client and server.

Editors D

Create and compile procedures by using the Program Unit editor.

D

Create and compile database procedures by using the Store Program Unit editor.

Execution Environment D

Execute program units by using the PL/SQL Interpreter.

Debugging D

Suspend program execution by using breakpoints.

D

Step through code to determine the location of the error.

D

Examine local variables to determine the cause of the error.

D

Modify variable values to test possible solutions at runtime.

Basics of Procedure Builder

19Ć45

Practice Overview

19Ć46

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Practice Overview This practice will familiarize you with the Procedure Builder environment and windows. Practice Contents D

Exploring Procedure Builder menus and windows

D

Creating a program unit

D

Executing a program unit

Basics of Procedure Builder

19Ć47

19Ć48

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Practice 19 1.

Load and execute a loop counter. a.

Launch Procedure Builder. Your instructor will give you the login information.

b.

From the menu, load the LABS\p19loop.pls file.

c.

Execute the procedure from the Interpreter pane. Pass a numeric value into the procedure as demonstrated below. Reminder: The Cue Cards can help you get started. PL/SQL> count_loops(4);

2.

Create, compile, and execute a procedure. a.

Create a procedure named MY_MESSAGE as demonstrated below: PROCEDURE my_message IS BEGIN TEXT_IO.PUT_LINE (’Hello World’); END;

b.

Compile the procedure using the Program Unit Editor.

c.

Execute the procedure from the Interpreter pane.

PL/SQL> my_message;

Basics of Procedure Builder

19Ć49

19Ć50

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

20 Modularizing Programming with Subprograms

20Ć2

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Objectives Modularity allows you to break your code into manageable, well-defined logical units. Each of these units in PL/SQL is called a program unit, or a subprogram. PL/SQL has two types of subprograms called procedures and functions. This lesson covers the structure of subprograms and how to invoke them. At the end of this lesson, you should be able to D

Determine the types of program units and where to use them.

D

Explain the differences between and benefits of procedures and functions.

D

Develop subprograms.

D

Invoke subprograms from Procedure Builder.

Modularizing Programming with Subprograms

20Ć3

20Ć4

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Overview Program units are named PL/SQL blocks. They fall into three main categories: D

Procedures to perform actions

D

Functions to compute a value

D

Packages to bundle logically related procedures and functions

Stored or Application Subprogram? These program units can be created in a variety of environments, including server-side stored subprograms or as application subprograms. Concept

Stored Subprogram

Application Subprogram

Location

Is in the database.

Is within the application.

Executed

From any database tool or application.

From only the application in which it was created.

Availability

By way of database security.

Independently of, and in addition to, stored subprograms.

Subprograms are composed of a number of sections: D

A header to name and type the block

D

An optional declarative section to set up local identifiers

D

An executable part to perform the actions

D

An optional exception handling section to handle exceptions

For more information, see PL/SQL User’s Guide and Reference, Release 2.3, “Subprograms.”

Modularizing Programming with Subprograms

20Ć5

20Ć6

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Creating a Subprogram The following steps will assist you to create a subprogram. 1.

Select your environment. If using Procedure Builder, then select either the Program Units node or the Database Objects node and Stored Procedures Units subobject node.

2.

Write your syntax. If using Procedure Builder, enter the syntax in the Program Unit Editor. If using SQL*Plus, write your code in a text editor as a script file.

3.

Compile your code. The source code is complied into p-code. If using Procedure Builder, click the Compile button. If using SQL*Plus, start your file at the SQL prompt.

4.

Invoke the successfully compiled procedure or function.

Guidelines D

SQL*Plus is another development environment for writing and for initial testing of a procedure, although you will need to test the procedure by invoking it from an application.

D

The SQL commands issued to create a stored subprogram are CREATE PROCEDURE or CREATE FUNCTION.

D

The SQL commands issued to remove a stored subprogram are DROP PROCEDURE or DROP FUNCTION.

D

Use CREATE OR REPLACE PROCEDURE or CREATE OR REPLACE FUNCTION so you do not have to issue a DROP statement.

Modularizing Programming with Subprograms

20Ć7

20Ć8

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Creating a Procedure Create a PL/SQL procedure to store a series of actions for later execution. The procedure can contain zero or more parameters, which are arguments that you list when calling the procedure. Syntax PROCEDURE name [(parameter,...)] IS pl/sql_block; where: name

is the procedure name, which adheres to the standard Oracle naming rules.

parameter

is the parameter syntax shown below.

pl/sql_block

is the procedural body that defines the action performed by the procedure.

The parameter syntax is as follows. Syntax parameter_name [IN | OUT | IN OUT] datatype [{:= | DEFAULT} expr] where: parameter_name

is the name of the parameter.

datatype

is the datatype of the parameter, without constraints.

expr

is the value to initialize the parameter.

Guidelines D

Start the PL/SQL block with the keyword IS.

D

Enter any local declarations between IS and BEGIN.

When creating the procedure from Procedure Builder, the CREATE OR REPLACE portion of the syntax is implied. Therefore, when creating the procedure from SQL*Plus, begin the statement with CREATE OR REPLACE.

Modularizing Programming with Subprograms

20Ć9

20Ć10

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Creating a Procedure

continued

Procedural Parameters Transfer values to and from the calling environment through parameters. There are two types that you use. When declaring a procedure, the formal parameter is used to define the values used in the executable portion of the PL/SQL block. The actual parameter, or argument, is referenced when invoking a subprogram. Parameter Modes for Formal Parameters Parameter Mode

Description

IN

Default argument. Passes a value from the calling environment into the subprogram. Formal parameter acts as a constant—you cannot overwrite the value. Actual parameter can be an expression, a constant, a literal, or an initialized variable.

OUT

Must be specified. Returns a value from the procedure to the calling environment. Formal parameter acts as an uninitialized variable. Formal parameter cannot be assigned to another variable or to itself. Actual parameter must be a variable; it cannot be a constant or expression.

IN OUT

Must be specified. Passes a value from the calling environment into the procedure, and returns a possibly different value from the procedure to the calling environment. Formal parameter acts as an initialized variable. Formal parameter can be used as a normal variable; it can be assigned to a value. Actual parameter must be a variable.

Modularizing Programming with Subprograms

20Ć11

20Ć12

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Creating a Procedure

continued

Example Update the salary of the specified employee to the specified amount through a procedure. PROCEDURE change_salary (v_emp_id IN NUMBER, -- formal parameters v_new_salary IN NUMBER) IS BEGIN -- begin PL/SQL block UPDATE s_emp SET salary = v_new_salary WHERE id = v_emp_id; COMMIT; END change_salary; This procedure, when invoked, will take the parameters for the employee number and the new salary, and update the salary for that specified employee. The content of the PL/SQL block will be covered in the lesson “Developing a Simple PL/SQL Block.” Eliminate Unnecessary IN Arguments D

Where possible, derive values in the procedure, or use column default values.

D

Generate the primary key using a database sequence.

D

Record the username from the USER function.

D

Record the current date from the SYSDATE function.

D

Take advantage of business rules to compute input values automatically using a formula.

Modularizing Programming with Subprograms

20Ć13

20Ć14

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Comparing Functions and Procedures A procedure can contain a list of parameters, but does not have to return a value. It is invoked in place of a complete PL/SQL statement. A function differs from a procedure in two ways: D

You invoke the function as part of an expression.

D

A function must return a value.

SQL Functions There are both user-defined functions, covered in this lesson, and SQL functions. You are already familiar with SQL functions. You call the function with actual parameters from within a SQL command, such as SELECT. -- Example Number Functions ROUND (1896,2) TRUNC (1892,-2) -- Example Group Functions SUM (s_emp.salary) AVG (s_ord.total) -- Other System Functions USER -- username SYSDATE -- current date and time s_dept_id.NEXTVAL -- database sequence value A procedure containing one OUT argument can be rewritten as a function containing a RETURN statement.

Modularizing Programming with Subprograms

20Ć15

20Ć16

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Creating a Function Create a PL/SQL function to return a value to the calling environment. You must declare the RETURN datatype in the header section of the function definition, and define the value to be returned in the PL/SQL block. You can also declare a list of parameters to be passed into the function. Syntax FUNCTION name [(parameter,...)] RETURN datatype IS pl/sql_block; where: name

is the function name, which adheres to the standard Oracle naming rules.

parameter

is the parameter syntax. See the syntax in the “Procedure” section of this lesson.

datatype

is the datatype of the value to be returned.

pl/sql_block

is the procedural body that defines the action performed by the procedure.

The RETURN Statement Remember to include a RETURN statement in the PL/SQL block. The function must set the value of the return parameter and be of the same datatype specified in the RETURN clause of the function definition. Multiple RETURN statements are allowed, but only one will be executed by any one call. Typically, the multiple RETURN statements are within an IF statement. When creating the function from Procedure Builder, the CREATE OR REPLACE portion of the syntax is implied. Therefore, when creating the function from SQL*Plus, begin the statement with CREATE OR REPLACE.

Modularizing Programming with Subprograms

20Ć17

20Ć18

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Creating a Function

continued

Example Return the tax based on the value parameter in a function. FUNCTION tax (v_value IN NUMBER) RETURN NUMBER IS BEGIN RETURN (v_value * .07); END tax; Avoid using the OUT and IN OUT modes with functions. Functions are designed to return a single value.

Modularizing Programming with Subprograms

20Ć19

20Ć20

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Invoking Subprograms You can call procedures and functions from any tool or language that supports PL/SQL. From PL/SQL, you can call a procedure with a direct call, and call a function as part of an expression. Invoking Procedures from Procedure Builder Enter the procedure name with any actual parameters, if applicable, at the Procedure Builder Interpreter prompt. Procedure Builder then performs the action specified in the procedure. Example Use the CHANGE_SALARY procedure to change the salary to 1000 for employee number 17. PL/SQL> change_salary (17, 1000); Note: You may want to confirm the changes by issuing a SELECT statement. Invoking Procedures from Another Procedure The example on the facing page illustrates how you can invoke a procedure from another procedure. In the example, procedure CHANGE_SALARY is being invoked from procedure PROCESS_SAL. Similarly, you can invoke a function from a stored procedure or a PL/SQL anonymous block. To invoke procedures from SQL*Plus, use the EXECUTE command. Use SQL*Plus bind variables to hold returned values. You can also use SQL*Plus substitution variables through the ampersand (&) and variable name.

Modularizing Programming with Subprograms

20Ć21

20Ć22

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Invoking Subprograms

continued

Invoking Standalone Functions You can invoke a standalone function created in Procedure Builder. First, you must create a variable called the “identifier” to hold the returned value. Syntax CREATE variable where: variable is one of the following: NUMBER var_name [PRECISION number] [SCALE number]

Specifies a variable, var_name, of the datatype NUMBER. PRECISION determines the maximum number of objects. SCALE determines where rounding should occur.

DATE var_name

Specifies a variable, var_name, of the datatype DATE.

CHAR var_name [LENGTH number]

Specifies a variable, var_name, of the datatype CHAR with an optional length setting in bytes.

After creating the variable, you execute the function. Finally, you view the variable value. Example Display the tax based on a value. Use the TAX function created earlier. PL/SQL> .CREATE NUMBER x PRECISION 4 PL/SQL> :x := tax(100); PL/SQL> TEXT_IO.PUT_LINE (TO_CHAR(:x)); 7 For more information, see Procedure Builder Developer’s Guide, “Working with PL/SQL Constructs.” Note: To invoke functions from SQL*Plus, use the EXECUTE command. Use SQL*Plus global variables to hold returned values. You can also use SQL*Plus substitution variables through the ampersand (&) and variable name. To view the result, use the SQL*Plus PRINT command.

Modularizing Programming with Subprograms

20Ć23

20Ć24

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Invoking Subprograms

continued

Invoking Functions in SQL Statements You can also call functions in SQL expressions. Anywhere a built-in SQL function can be placed, a PL/SQL user-defined function can be placed as well. Advantages D

Permit calculations that are too complex, awkward, or unavailable with SQL.

D

Increase efficiency of queries by performing functions in the server rather than in an application.

Note: This feature is only available with PL/SQL 2.1 or higher and Oracle7 Server release 7.1 or higher. Guidelines D

Only stored functions can be called from SQL statements, procedures cannot.

D

The function acts as a single-row function.

D

The function cannot contain a DML statement—the function cannot modify database tables.

D

All formal parameters must be IN parameters; none can be an OUT or IN OUT parameter.

D

Datatypes used in the function must be Oracle7 Server internal datatypes, such as CHAR and DATE, not PL/SQL datatypes, such as BOOLEAN or RECORD.

D

The RETURN datatype must be an Oracle7 Server internal datatype.

D

You must own or have EXECUTE privileges on the PL/SQL function in order to call it.

Modularizing Programming with Subprograms

20Ć25

20Ć26

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Invoking Subprograms

continued

Calling a Function You can call functions from any of these SQL clauses. D

The select list of a SELECT command

D

The condition of a WHERE and HAVING clause

D

The CONNECT BY, START WITH, ORDER BY, and GROUP BY clauses

D

The VALUES clause of the INSERT command

D

The SET clause of the UPDATE command

Example Use the TAX function to calculate the tax on the total sale for order number 100. PL/SQL> SELECT total, tax(total) +> FROM s_ord +> WHERE id = 100; TOTAL TAX(TOTAL) ------------- ---------601100.00 42077

Modularizing Programming with Subprograms

20Ć27

20Ć28

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Summary D

D

D

D

Subprograms fall into three main categories: D

Procedures to perform actions

D

Functions to compute a value

D

Packages to bundle logically related procedures and functions

A function differs from a procedure in two ways: D

You invoke the function as part of an expression.

D

A function must return a value.

Procedure and function components: D

Header—To name and type the block

D

Declarative—To set up local identifiers (optional)

D

Executable—To perform the actions

D

Exception handling—To handle exceptions (optional)

Invoke subprograms from the Procedure Builder Interpreter prompt.

Modularizing Programming with Subprograms

20Ć29

20Ć30

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Practice Overview This practice guides you through creating a simple procedure and function and invoking those functions from the Interpreter prompt. Practice Contents D

Creating a simple procedure

D

Debugging the procedure

D

Executing the procedure

Note: You use the TEXT_IO built-in package in this procedure to print a result at the Interpreter prompt.

Modularizing Programming with Subprograms

20Ć31

20Ć32

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Practice 20 1.

A procedure can contain IN, OUT, and IN OUT parameters. True / False

2.

A procedure can be used in a SQL statement. True / False

3.

The IN OUT argument mode is the default mode. True / False

4.

Functions are executed as part of an expression. True / False

5.

Name the four parts of the subprogram syntax. a. b. c. d.

6.

Create a procedure called MY_PROCEDURE to output the phrase “My Procedure Works” to the screen. a.

Replace the skeleton text with text from LABS/p20proc.pls.

b.

Compile the code. Make appropriate corrections so code successfully compiles.

c.

Execute the procedure at the Interpreter prompt.

Modularizing Programming with Subprograms

20Ć33

20Ć34

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

21 Developing a Simple PL/SQL Block

21Ć2

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Objectives In this lesson, you create a simple PL/SQL block after learning the various elements that compose a block. At the end of this lesson, you should be able to D

Declare and use variables and constants in PL/SQL.

D

Assign new values to variables within the executable section.

D

Create and execute a named PL/SQL subprogram in Procedure Builder.

Developing a Simple PL/SQL Block

21Ć3

21Ć4

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Overview A PL/SQL block is comprised of up to three sections: declarative (optional), executable (required), and exception handling (optional). Only BEGIN and END keywords are required. Each subprogram contains an additional section, the header (required). You can store and change values within a PL/SQL block by declaring and referencing variables and other identifiers. Handling Variables D

Declare and initialize variables within the declaration section.

D

Assign new values to variables within the executable section.

D

Pass values into PL/SQL blocks through parameters.

D

View the results from a PL/SQL block through output variables.

Note: The END keyword can be optionally followed by the name of the subprogram for clarity.

Developing a Simple PL/SQL Block

21Ć5

21Ć6

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Declaring PL/SQL Variables and Constants You need to declare all identifiers within the declaration section before referencing them within the PL/SQL block. Syntax identifier [CONSTANT] datatype [NOT NULL] [:= | DEFAULT expr]; where: identifier

is the name of the identifier.

CONSTANT

constrains the identifier so that its value cannot change; constants must be initialized.

datatype

is a scalar or composite datatype.

NOT NULL

constrains the variable so that it must contain a value; NOT NULL variables must be initialized.

expr

is any PL/SQL expression that can be a literal, another variable, or an expression involving operators and functions.

Guidelines D

Name the identifier according to the same rules used for SQL objects.

D

You can use naming conventions, for example v_name to represent a variable, and c_name to represent a constant.

D

You have the option of assigning an initial value to variables, unless they are NOT NULL.

D

Initialize the variable to an expression with the assignment operator (:=), or, equivalently, with the DEFAULT reserved word; otherwise, variables are initialized to NULL by default.

D

Declare at most one identifier per line.

For more information, see PL/SQL User’s Guide and Reference, Release 2.3, “Datatypes.”

Developing a Simple PL/SQL Block

21Ć7

21Ć8

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Declaring Scalar Variables PL/SQL supports three datatypes—scalar, composite, and reference—that you can use for declaring variables, constants, and pointers. Scalar Datatypes A scalar datatype holds a single value and has no internal components. Scalar datatypes can be classified into four categories: number, character, date and time, or Boolean. Character and number datatypes have subtypes that associate a base type to a constraint. For example, INTEGER and POSITIVE are subtypes of the NUMBER base type. Datatype

Description

BINARY_INTEGER

Base type for integers between –2147483647 and 2147483647.

NUMBER [(precision,scale)]

Base type for fixed and floating point numbers.

CHAR [(maximum_length)]

Base type for fixed length character data up to 32767 bytes. If you do not specify a maximum_length, the default length is set to 1.

LONG

Base type for variable length character data up to 32760 bytes.

LONG RAW

Base type for binary data up to 32760 bytes.

VARCHAR2(maximum_length)

Base type for variable length character data up to 32767 bytes.

DATE

Base type for dates and times.

BOOLEAN

Base type that stores one of three possible values used for logical calculations: TRUE, FALSE, or NULL.

Note: The above list is abridged. For the complete list, see the PL/SQL User’s Guide and Reference, Release 2.3, “Datatypes.”

Developing a Simple PL/SQL Block

21Ć9

21Ć10

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Declaring Scalar Variables

continued

Examples Declare a variable to store the gender code (M or F). v_gender

CHAR(1);

Declare a variable to count the iterations of a loop and initialize the variable to 0. v_count

BINARY_INTEGER := 0;

Declare a variable to accumulate the total salary for a department and initialize the variable to 0. v_total_sal

NUMBER(9,2) := 0;

Declare a variable to store the ship date of an order, and initialize the variable to one week from today. v_order_date

DATE := SYSDATE + 7;

Declare a constant for the tax rate, which never changes throughout the PL/SQL block. c_tax_rate

CONSTANT NUMBER(3,2) := 8.25;

Declare a flag to indicate whether a piece of data is valid or invalid, and initialize the variable to TRUE. v_valid

BOOLEAN NOT NULL := TRUE;

Developing a Simple PL/SQL Block

21Ć11

21Ć12

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Declaring Scalar Variables

continued

The %TYPE Attribute When you declare PL/SQL variables to hold column values, you must ensure that the variable is of the correct datatype and precision. If it is not, then a PL/SQL error will occur during execution. Rather than hard-coding the datatype and precision of a variable, you can declare a variable according to another previously-declared variable or database column. You do this using the %TYPE attribute. To use the attribute in place of the datatype required in the variable declaration, prefix it with the database table and column names. If referring to a previously-declared variable, prefix the variable name to the attribute. PL/SQL determines the datatype and size of the variable when the block is compiled, so the variable is always compatible with the database column or identifier used to populate the variable. Advantages of Using the %TYPE Attribute D D

The datatype of the underlying database column may be unknown. The datatype of the underlying database column may change at runtime.

Examples Declare variables to store the first and last names for an employee. ... v_last_name v_first_name ...

s_emp.last_name%TYPE; s_emp.first_name%TYPE;

Declare variables to store the balance for a checking account, as well as the minimum balance, which starts out as 10. ... v_balance NUMBER(7,2); v_minimum_balance v_balance%TYPE := 10; ... A NOT NULL column constraint does not apply to variables declared using %TYPE. Therefore, if you declare a variable using the %TYPE attribute using a database column defined as NOT NULL, you can assign the NULL value to the variable.

Developing a Simple PL/SQL Block

21Ć13

21Ć14

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Declaring Composite Datatypes A composite datatype contains internal components and is reusable. Two types of composite datatypes are available in PL/SQL: TABLE and RECORD. PL/SQL Table D

A PL/SQL TABLE datatype is not the same as a database table.

D

A PL/SQL TABLE is similar to a one-dimensional array.

D

A PL/SQL TABLE must contain two components:

D

D

A primary key of datatype BINARY_INTEGER that indexes the PL/SQL TABLE.

D

A column of a scalar datatype, which stores the PL/SQL TABLE elements.

A PL/SQL TABLE can increase dynamically because it is unconstrained.

Developing a Simple PL/SQL Block

21Ć15

21Ć16

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Declaring Composite Datatypes

continued

Declaring PL/SQL Tables 1.

Declare a TABLE datatype.

2.

Declare a variable of that datatype.

Syntax TYPE type_name IS TABLE OF scalar_datatype [NOT NULL] INDEX BY BINARY_INTEGER; identifier type_name; where: type_name

is the name of the TABLE type.

scalar_datatype

is the datatype of the PL/SQL TABLE elements. You can use the %TYPE attribute.

identifier

is the name of the identifier.

Example Declare PL/SQL TABLE variables to store the first name and last name. ... TYPE name_table_type IS TABLE OF VARCHAR2(25) INDEX BY BINARY_INTEGER; first_name_table name_table_type; last_name_table name_table_type; ... The NOT NULL constraint prevents nulls from being assigned to the PL/SQL TABLE of that type. Do not initialize the PL/SQL TABLE.

Developing a Simple PL/SQL Block

21Ć17

21Ć18

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Declaring Composite Datatypes

continued

PL/SQL RECORD D

A PL/SQL RECORD datatype is not the same as a row in a database table.

D

A PL/SQL RECORD is similar in structure to a record in a 3GL.

D

A PL/SQL RECORD must contain one or more components of any scalar, RECORD, or PL/SQL TABLE datatype called fields. These uniquely named fields can have different datatypes.

D

The PL/SQL RECORD allows you to treat this collection of fields as one logical unit.

D

PL/SQL RECORDS are convenient for fetching a row of data from a table for processing in a PL/SQL block.

Developing a Simple PL/SQL Block

21Ć19

21Ć20

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Declaring Composite Datatypes

continued

Declaring PL/SQL Records 1.

Declare a RECORD datatype.

2.

Declare a variable of that datatype.

Syntax TYPE type_name IS RECORD (field_name1 field_type [NOT NULL {:=|DEFAULT} expr], (field_name2 field_type [NOT NULL {:=|DEFAULT} expr], ...); identifier type_name; where: type_name

is the name of the RECORD type.

field_name

is the name of the field.

field_type

is the datatype of the field. You can use the %TYPE and %ROWTYPE attribute.

expr

is any PL/SQL expression that can be a literal, another variable, or an expression involving operators and functions.

identifier

is the name of the identifier.

Example Declare variables to store first name, last name, and gender of a new employee. ... TYPE emp_record_type IS RECORD (last_name VARCHAR2(25), first_name VARCHAR2(25), gender CHAR(1)); employee_record emp_record_type; ... The NOT NULL constraint prevents the assigning of nulls to those fields. Be sure to initialize NOT NULL fields.

Developing a Simple PL/SQL Block

21Ć21

21Ć22

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Declaring Composite Datatypes

continued

Declaring Records with the %ROWTYPE Attribute Declare a record based upon a collection of columns in a database table or view by using the %ROWTYPE attribute. The fields within the record take their names and datatypes from the columns of the table or view. Advantages of Using the %ROWTYPE Attribute D

The number and datatypes of the underlying database columns may be unknown.

D

The number and datatypes of the underlying database columns may change at runtime.

D

Useful when retrieving a row with the SELECT statement.

Example Declare a variable to store the same information about a department as it is stored in the S_DEPT table. ... dept_record ...

s_dept%ROWTYPE;

This declaration creates a record with the same field names and field datatypes as a row in a table. DEPT_RECORD is a record. The fields are: DEPT_RECORD.ID, DEPT_RECORD.NAME, and DEPT_RECORD.REGION_ID.

Developing a Simple PL/SQL Block

21Ć23

21Ć24

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

PL/SQL Block Syntax Rules Because PL/SQL is an extension of SQL, the general syntax rules that apply to SQL are also applicable to the PL/SQL language. Guidelines D

Identifiers can contain up to 30 characters, but they must start with an alphabetic character.

D

Reserved words cannot be used as identifiers unless they are enclosed within double quotation marks (for example, “SELECT”).

D

Do not choose the same name for the identifier as the name of columns in a table used in the block. If PL/SQL identifiers are in the same SQL statements and have the same name as a column, then Oracle assumes that it is the column that is being referenced.

D

Statements can be split across lines, but keywords must not be split.

D

Lexical units (for example, identifiers or literals) can be separated by one or more spaces or other delimiters that cannot be confused as being part of the lexical unit.

D

Character and date literals must be enclosed within single quotation marks.

D

Numeric literals can be represented by either a simple value (for example, –32.5) or by scientific notation (for example, 2E5, meaning 2x10 to the power of 5 = 200000).

D

Multiple line comments can be enclosed by /* and */ symbols. A single line comment begins with --, and the end-of-line marks the comment’s end.

For more information, see PL/SQL User’s Guide and Reference, Release 2.3, “Appendix E” for a list of reserved words.

Developing a Simple PL/SQL Block

21Ć25

21Ć26

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

PL/SQL Block Syntax Rules

continued

Nested Blocks One of the advantages PL/SQL has over SQL is the ability to nest statements. You can nest blocks wherever an executable statement is allowed, thus making the nested block a statement. Therefore, you can break down the executable part of a block into smaller blocks. The exception section can also contain nested blocks. Variable Scope The scope of an object is the region of the program that can refer to the object. You can reference the declared variable within the executable section. An identifier is visible in the block in which it is declared and all nested sub-blocks, procedures, and functions. If the block does not find the identifier declared locally, it looks up to the declarative section of the enclosing (or parent) blocks. The block never looks down to enclosed (or child) blocks or sideways to sibling blocks. Scope applies to all declared objects, including variables, cursors, user-defined exceptions, and constants.

Developing a Simple PL/SQL Block

21Ć27

21Ć28

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Assigning Values to Variables To assign or reassign a value to a variable, you write a PL/SQL assignment statement. You must explicitly name the variable to receive the new value to the left of the assignment operator (:=). Syntax identifier := expr;

plsql_table_name(primary_key_value) := expr;

plsql_record_name.field_name := expr; where: identifier

is the name of the identifier.

plsql_table_name

is the name of the PL/SQL TABLE.

primary_key_value

is the binary integer value that is the index of the PL/SQL TABLE and could also be a variable.

plsql_record_name

is the name of the PL/SQL RECORD.

field_name

is the field from the PL/SQL RECORD.

expr

can be a variable, constant, literal, or function call, but not a database column.

Developing a Simple PL/SQL Block

21Ć29

21Ć30

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Assigning Values to Variables

continued

Examples Set the maximum salary identifier V_MAX_SAL to the value of current salary identifier V_SAL. v_max_sal

:= v_sal;

Store the name “Maduro” in the index identifier of 3 in the PL/SQL TABLE of last names. last_name_table (3) := ’Maduro’; Store basic information for a new employee in a PL/SQL RECORD. emp_record.last_name emp_record.first_name emp_record.gender

:= ’Maduro’; := ’Elena’; := ’F’;

Set the salary to the salary from the S_EMP table. This example produces a compile error. v_sal

:= s_emp.salary;

Remember that string literals must be enclosed with single quotation marks, for example, ’Hello, world’. If there is a single quotation mark in the string, write a single quotation mark twice, for example, ’Account wasn’’t found’.

Developing a Simple PL/SQL Block

21Ć31

21Ć32

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Assigning Values to Variables

continued

Class Exercise Evaluate the PL/SQL block on the opposite page. Determine each of the following values according to the rules of scoping. 1.

The value of V_WEIGHT within the sub-block.

2.

The value of V_NEW_LOCN within the sub-block.

3.

The value of V_WEIGHT within the main block.

4.

The value of V_MESSAGE within the main block.

5.

The value of V_NEW_LOCN within the main block.

Developing a Simple PL/SQL Block

21Ć33

21Ć34

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Assigning Values to Variables

continued

Operators The logical, arithmetic, and concatenation operators used in PL/SQL are the same as those in SQL. In addition, there is an exponential operator (**). You can control the order in which operations are performed by using parentheses, as in SQL. Examples Increment the index for a loop. v_count

:= v_count + 1;

Set the value of a Boolean flag depending upon whether two numbers are equal. v_equal

:= (v_n1 = v_n2);

Validate an employee number if it contains a value. v_valid

:= (v_emp_id IS NOT NULL);

Developing a Simple PL/SQL Block

21Ć35

21Ć36

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Assigning Values to Variables

continued

Functions Most of the functions available in SQL are also valid in PL/SQL expressions: D

Single-row number functions

D

Single-row character functions

D

Datatype conversion functions

D

Date functions

D

Miscellaneous functions

Functions not available in procedural statements: D

GREATEST and LEAST

D

Group functions: AVG, MIN, MAX, COUNT, SUM, STDDEV, and VARIANCE

Group functions apply to groups of rows in a table, and are therefore available only within SQL statements in a PL/SQL block. Examples Build the mailing address for a company. v_mailing_address := v_name||CHR(10)||v_address|| CHR(10)||v_country||CHR(10)||v_zip_code; Convert the name to uppercase. v_last_name

:= UPPER (v_last_name);

Compute the sum of all numbers stored in the NUMBER_TABLE PL/SQL table. This example produces a compile error. v_total

:= SUM(number_table);

CHR is the SQL function that converts an ASCII code to its corresponding character; 10 is the code for a line feed.

Developing a Simple PL/SQL Block

21Ć37

21Ć38

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Assigning Values to Variables

continued

Datatype Conversion Within an expression, you should make sure that datatypes are the same. If mixed datatypes occur in the same expression, you should use the appropriate conversion function from the list below to convert the data. Syntax TO_CHAR (value, fm)

TO_DATE (value, fm)

TO_NUMBER (value, fm) where: value

is a character string, number, or date.

fm

is the format model used to convert value.

Examples Store a value that is composed of the user name and today’s date. This code causes a syntax error. v_comment

:= USER||’: ’||SYSDATE;

To correct the error, convert SYSDATE to a character string with the TO_CHAR conversion function. v_comment

:= USER||’: ’||TO_CHAR(SYSDATE);

PL/SQL will attempt conversion if possible, but the success will depend upon the operations being performed. It is good programming practice to explicitly perform datatype conversions because they can favorably affect performance and remain valid even with a change in software versions.

Developing a Simple PL/SQL Block

21Ć39

21Ć40

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Assigning Values to Variables

continued

Referencing NonĆPL/SQL Variables You can reference variables declared in the host or calling environment in PL/SQL statements, unless the statement is within a procedure, function, or package. This includes host language variables declared in precompiler programs, screen fields in a Developer/2000 Forms application, and SQL*Plus bind variables. To reference host variables, you must prefix the references with a colon (:) to distinguish them from declared PL/SQL variables. Example Store the annual salary in a SQL*Plus global variable. :g_annual_ salary

:= v_salary * 12;

Developing a Simple PL/SQL Block

21Ć41

21Ć42

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Programming Guidelines Adhere to programming guidelines for producing clear code and reducing maintenance when developing a PL/SQL block. Guidelines D

Document code with comments.

D

Develop case conventions for the code.

D

Develop naming conventions for identifiers and other objects.

D

Indent code to enhance readability.

Commenting Code Comment code to document each phase and to assist with debugging. Comment the PL/SQL code with two dashes (--) if the comment is on a single line, or enclose the comment between the symbols /* and */ if the comment spans several lines. Example Compute the yearly salary from the monthly salary. ... v_sal NUMBER(9,2); BEGIN /* Compute the annual salary based on the monthly salary input from the user */ v_sal := v_sal*12; END;

Developing a Simple PL/SQL Block

21Ć43

21Ć44

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Programming Guidelines

continued

Code Conventions A set of guidelines for writing code in uppercase or lowercase helps you to distinguish keywords from named objects. Category

Case Convention

Examples

SQL Commands

Uppercase

SELECT, INSERT

PL/SQL Keywords

Uppercase

DECLARE, BEGIN, IF

Datatypes

Uppercase

VARCHAR2, BOOLEAN

Identifiers and Parameters

Lowercase

v_sal, emp_cursor, g_sal

Database Tables and Columns

Lowercase

s_emp, order_date, id

Code Naming Conventions Develop a set of prefixes and suffixes to distinguish identifiers from other identifiers, from database objects, and from other named objects. Identifier

Naming Convention

Example

Variable

v_name

v_sal

Constant

c_name

c_company_name

Cursor

name_cursor

emp_cursor

Exception

e_name

e_too_many

Table Type

name_table_type

amount_table_type

Table

name_table

order_total_table

Record Type

name_record_type

emp_record_type

Record

name_record

customer_record

SQL*Plus substitution parameter

p_name

p_sal

SQL*Plus global variable

g_name

g_year_sal

Developing a Simple PL/SQL Block

21Ć45

21Ć46

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Programming Guidelines

continued

Indentation For clarity, indent each level of code. This enhances readability. ... BEGIN IF x=0 THEN y=1; END IF; ... END;

Developing a Simple PL/SQL Block

21Ć47

21Ć48

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Summary PL/SQL Blocks D

Declaration section: optional

D

Executable section: required

D

Exception handling section: optional

PL/SQL Identifiers D

Identifiers are defined in the declaration section.

D

Definition can be a scalar datatype with no internal components, such as NUMBER, VARCHAR2, DATE, and BOOLEAN.

D

Definition can be a composite datatype with internal components, such as TABLE and RECORD.

D

Definition can be based on the structure of another variable or a database object by using %TYPE and %ROWTYPE attributes.

D

Identifiers created with NOT NULL or CONSTANT options require initialization.

D

When referencing variables, keep in mind the scoping and conversion rules.

Conventions for Documenting, Writing, and Naming Code D

Document code with comments.

D

Develop conventions for writing text in uppercase and lowercase.

D

Develop conventions for naming identifiers.

D

Indent code to enhance readability.

Developing a Simple PL/SQL Block

21Ć49

21Ć50

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Practice Overview This practice reinforces the basics of PL/SQL learned in this lesson, including datatypes, legal definitions of identifiers, and validation of expressions. You put all these elements together to create a simple PL/SQL block using Oracle Procedure Builder. Practice Contents D

Determining validity of declarations

D

Reviewing scoping and nesting rules

D

Developing a simple subprogram in Oracle Procedure Builder

Developing a Simple PL/SQL Block

21Ć51

21Ć52

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Practice 21 1.

Evaluate each of the following declarations. Determine which of them are not legal and explain why. DECLARE v_id

NUMBER (4);

DECLARE v_x, v_y, v_z

VARCHAR2(10);

DECLARE v_birthdate

DATE NOT NULL;

DECLARE v_in_stock

BOOLEAN := 1;

DECLARE emp_record

emp_record_type;

a.

b.

c.

d.

e.

DECLARE TYPE name_table_type IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER; dept_name_table name_table_type; f.

Developing a Simple PL/SQL Block

21Ć53

Practice 21 2.

continued

Suppose you embed a sub-block within a block, as depicted below. You declare two variables, V_CUSTOMER and V_CREDIT_RATING, within the main block. You also declare two variables,V_CUSTOMER and V_NAME in the sub-block. Determine the values for each variable in each case listed below. a.

The value of V_CUSTOMER in the sub-block is

b.

The value of V_NAME in the sub-block is

c.

The value of V_CREDIT_RATING in the sub-block is

d.

The value of V_CUSTOMER in the main block is

e.

The value of V_NAME in the main block is

f.

The value of V_CREDIT_RATING within the main block is

Scope Example DECLARE v_customer v_credit_rating

VARCHAR2(50) := ’Womansport’; VARCHAR2(10) := ’EXCELLENT’;

BEGIN DECLARE v_customer NUMBER (7) := v_name

SUBĆBLOCK 201; VARCHAR2(25):= ’Unisports’;

BEGIN v_customer

v_name

v_credit_rating

v_name

v_credit_rating

END; v_customer

END;

21Ć54

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Practice 21 3.

continued

Create and execute a procedure named MULTIPLIER that accepts two numbers through variables. The first number should be divided by the second number and have the second number added to the result. The result should be written to a PL/SQL variable and printed to the screen.

If you have time, complete the following exercises. 4.

Build a function named ANN_COMP that computes the total compensation for one year. The annual salary and the annual bonus percentage will be passed to the function, and the bonus will need to be converted from a whole number to a decimal (for example, 15 to .15). If the salary is null, set it to zero before computing the total compensation. Likewise, if the bonus is null, set it to zero before computing the total compensation. Execute the function. Reminder: Use the NVL function to handle null values.

5.

Rewrite the MULTIPLIER procedure in Exercise 3 as a function named MULTI. Execute the function.

Developing a Simple PL/SQL Block

21Ć55

21Ć56

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

22 Interacting with Oracle

22Ć2

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Objectives In this lesson, you access the database and control transactions through SQL statements in PL/SQL. At the end of this lesson, you should be able to D

Use SELECT, INSERT, UPDATE, and DELETE commands in PL/SQL subprograms.

D

Determine the outcome of SQL statements by using implicit cursor attributes.

D

Control transactions within PL/SQL.

Interacting with Oracle

22Ć3

22Ć4

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Overview When you need to extract information from or apply changes to the database, you must use SQL. PL/SQL supports full data manipulation language and transaction control commands within SQL. You can use SELECT statements to populate variables with values queried from a row in a table. Your DML commands can process multiple rows. Comparing SQL and PL/SQL Statement Types D

A PL/SQL block is not a transaction unit. Commits, savepoints, and rollbacks are independent of blocks, but you can issue these commands within a block.

D

PL/SQL does not support data definition language (DDL), such as CREATE TABLE, ALTER TABLE, or DROP TABLE.

D

PL/SQL does not support data control language (DCL), such as GRANT or REVOKE.

D

DBMS_SQL package allows you to issue DDL and DCL statements.

Interacting with Oracle

22Ć5

22Ć6

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Retrieving Data Using PL/SQL Use the SELECT statement to retrieve data from the database. The SELECT statement contains an additional mandatory clause: the INTO clause. In the INTO clause, list the output variables for receiving the data. The SELECT statement must return exactly one row or an error will occur. Abridged Syntax SELECT INTO FROM WHERE

select_list variable_name | record_name table condition;

where: select_list

is a list of at least one column, and can include SQL expressions, row functions, or group functions.

variable_name

is the scalar variable to hold the retrieved value.

record_name

is the PL/SQL RECORD to hold the retrieved values.

table

specifies the database table name.

condition

is composed of column names, expressions, constants, and comparison operators, including PL/SQL variables and constants.

Take advantage of the full range of Oracle7 Server syntax for the SELECT statement. Guidelines D

Terminate each SQL statement with a semicolon (;).

D

Assign values into PL/SQL tables in a loop by declaring an explicit cursor.

D

The INTO clause is required for the SELECT statement when it is embedded within PL/SQL.

D

The WHERE clause is optional, and can be used to specify input variables, constants, literals, or PL/SQL expressions.

Interacting with Oracle

22Ć7

22Ć8

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Retrieving Data Using PL/SQL

continued

Guidelines D

Specify the same number of output variables in the INTO clause as database columns in the SELECT clause. Be sure that they correspond positionally and that their datatypes are compatible.

D

Ensure that the datatype of the identifiers match the datatype of the columns by using the %TYPE attribute. The datatype and number of variables in the INTO clause match those in the SELECT list.

D

Terminate the PL/SQL block with the END statement. You can add the name of the subprogram after the keyword END for clarity.

D

Include at least one RETURN statement in a function.

D

Use group functions, such as SUM, in a SQL statement since group functions apply to groups of rows in a table.

Interacting with Oracle

22Ć9

22Ć10

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Retrieving Data Using PL/SQL

continued

A PL/SQL RECORD can be used to easily create fields that match a database table’s columns. Each field has the same name and datatype as a column in the table. When retrieving all columns from a table, use a PL/SQL RECORD to hold the retrieved values.

Interacting with Oracle

22Ć11

22Ć12

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Retrieving Data Using PL/SQL

continued

Avoid ambiguity in the WHERE clause by adhering to a naming convention that distinguishes database column names from PL/SQL variable names. Example Retrieve the order date and the ship date for the specified order. This example raises an unhandled runtime exception. PROCEDURE order_info (id s_ord.id%TYPE) IS date_ordered s_ord.date_ordered%TYPE; date_shipped s_ord.date_shipped%TYPE; BEGIN SELECT date_ordered, date_shipped INTO date_ordered, date_shipped FROM s_ord WHERE id = id; ... END order_info; PL/SQL> order_info (100); ERROR 0 at line 1, column 0 Unhandled exception ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at line 7 PL/SQL checks whether an identifier is a column in the database; if not, it is assumed to be a PL/SQL identifier.

Interacting with Oracle

22Ć13

22Ć14

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

SELECT Exceptions SELECT statements within a PL/SQL block fall into the “Embedded SQL”ANSI classification. Be sure that the SELECT statement retrieves exactly one row; otherwise an exception is raised. An exception is an error that terminates a PL/SQL block. SELECT Exceptions Condition

Exception

The SELECT statement identifies more than one row.

TOO_MANY_ROWS exception (Oracle7 Server error number -1422).

The SELECT statement does not identify NO_DATA_FOUND exception (Oracle7 any rows. Server error number +1403). Note: Handle the raised exceptions with exception-handling routines, which will be covered in a later lesson. Alternatively, fetch multiple rows one-by-one in a loop by declaring an explicit cursor.

Interacting with Oracle

22Ć15

22Ć16

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

SELECT Exceptions

continued

TOO_MANY_ROWS Exception When more than one record is identified with a SELECT statement, Oracle7 Server raises an error number -1422, also referred to as TOO_MANY_ROWS, which is the predefined exception name. NO_DATA_FOUND Exception When no rows are identified with a SELECT statement, the NO_DATA_FOUND exception is raised, which is also Oracle7 Server error number +1403. In the “Processing Queries by Using Explicit Cursors” and “Error Handling” lessons, options for addressing these exceptions are addressed.

Interacting with Oracle

22Ć17

22Ć18

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Manipulating Data Using PL/SQL Manipulate data in the database by using the DML commands. D

INSERT statement adds new rows of data to the table.

D

UPDATE statement modifies existing rows in the table.

D

DELETE statement removes unwanted rows from the table.

Inserting Data When adding rows to a table, you can eliminate unnecessary IN arguments. D

Use SQL function, such as USER and SYSDATE.

D

Generate primary key values by using database sequences.

D

Derive values in the PL/SQL block.

D

Add column default values.

Interacting with Oracle

22Ć19

22Ć20

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Manipulating Data Using PL/SQL

continued

Updating and Deleting Data There may be ambiguity in the SET clause of the UPDATE statement because although the identifier on the left of the assignment operator is always a database column, the identifier on the right can be either a database column or a PL/SQL variable. Remember that the WHERE clause is used to determine which rows are affected. If no rows are modified, no error occurs, unlike the SELECT statement in PL/SQL.

Interacting with Oracle

22Ć21

22Ć22

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

SQL Cursor Whenever you issue a SQL command, the Server opens an area of memory in which the command is parsed and executed. This area is called a cursor. When the executable part of a block issues a SQL command, PL/SQL creates an implicit cursor, which has the SQL identifier. PL/SQL manages this cursor automatically. An explicit cursor is explicitly declared and named by the programmer. There are four attributes available in PL/SQL that can be applied to cursors. Note: More about explicit cursors will be covered in Lesson 24. SQL Cursor Attributes You can use these attributes in PL/SQL statements as you would functions, but not in SQL commands. They are useful to evaluate the result of a DML operation. PL/SQL does not consider a data manipulation language statement that affects no rows to have failed, unlike the SELECT statement, which returns an exception. Attribute

Description

SQL%ROWCOUNT

Number of rows affected by the most recent SQL statement (an integer value).

SQL%FOUND

Boolean attribute that evaluates to TRUE if the most recent SQL statement affects one or more rows.

SQL%NOTFOUND

Boolean attribute that evaluates to TRUE if the most recent SQL statement does not affect any rows.

SQL%ISOPEN

Always evaluates to FALSE because PL/SQL closes implicit cursors immediately after they are executed.

For more information, see PL/SQL User’s Guide and Reference, Release 2.3, Chapter 4, “Implicit Cursor Attributes” section.

Interacting with Oracle

22Ć23

22Ć24

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

SQL Cursor Attributes

continued

Example Write a procedure to delete rows that have the specified order number from the item table. Print the number of rows deleted to the screen. PROCEDURE del_rows (v_ord_id NUMBER) IS v_rows_deleted NUMBER; BEGIN DELETE FROM s_item WHERE ord_id = v_ord_id; v_rows_deleted := SQL%ROWCOUNT; TEXT_IO.PUT_LINE (TO_CHAR(v_rows_deleted) ||’ rows deleted.’); END del_rows;

Interacting with Oracle

22Ć25

22Ć26

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Controlling Transactions Control the logic of transactions with COMMIT and ROLLBACK SQL commands, rendering some groups of database changes permanent, while discarding others. As with Oracle7, DML transactions start at the first command to follow a COMMIT or ROLLBACK, and end on the next successful COMMIT or ROLLBACK. These actions may occur within a PL/SQL block or as a result of events in the host environment (for example, ending a Procedure Builder session automatically commits the pending transaction). You can also include explicit locking commands (such as LOCK TABLE and SELECT ... FOR UPDATE) in a block. They take effect until the end of the transaction. COMMIT Command COMMIT ends the current transaction by making all pending changes to the database permanent. Syntax COMMIT [WORK]; where: WORK

is for compliance with ANSI standards.

ROLLBACK Command ROLLBACK ends the current transaction by discarding all pending changes. Syntax ROLLBACK [WORK]; where: WORK

is for compliance with ANSI standards.

Note: A transaction is defined as a sequence of SQL statements.

Interacting with Oracle

22Ć27

22Ć28

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Controlling Transactions

continued

SAVEPOINT Command Alter the transactional logic with Oracle7 Server savepoints based upon runtime conditions. SAVEPOINT marks an intermediate point in the transaction processing. Syntax SAVEPOINT savepoint_name;

ROLLBACK TO SAVEPOINT discards pending changes made after the savepoint was marked. Syntax ROLLBACK [WORK] TO [SAVEPOINT] savepoint_name; where: savepoint_name

Interacting with Oracle

is a PL/SQL identifier.

22Ć29

22Ć30

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Summary You can embed SQL within the PL/SQL block. You can use the following valid commands: Statement

Description

SELECT

Retrieves exactly one row into scalar variables or into a record variable.

INSERT

Adds a row.

UPDATE

Modifies one or more existing rows.

DELETE

Removes one or more existing rows.

COMMIT

Makes all pending transactions permanent.

ROLLBACK

Eliminates all pending transactions.

ROLLBACK TO SAVEPOINT

Eliminates all pending transactions since the named savepoint.

You can use SQL implicit cursor attributes to verify the outcome of these statements.

Interacting with Oracle

22Ć31

22Ć32

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Practice Overview In this practice, you create three procedures to input, update, and delete information in a table, all using DML statements within a PL/SQL block. Practice Contents D

Creating a procedure to insert data into a table

D

Creating a procedure to update data in a table

D

Creating a procedure to delete a record from a table

D

Verifying your changes to the table using Oracle Procedure Builder built-ins and implicit cursor attributes

Interacting with Oracle

22Ć33

22Ć34

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Practice 22 If you are not already connected to the database, be sure to connect now. 1.

2.

3.

Create a procedure to insert a new department into the S_DEPT table. a.

Use the S_DEPT_ID sequence generator for the department number.

b.

Create a parameter for the department name.

c.

Leave the region number null for now.

d.

Execute the procedure.

e.

Display the new department that you created.

Create a procedure to update the region number for an existing department. a.

Create a parameter for the department number.

b.

Create a parameter for the region name.

c.

Set the region number to the value that corresponds to the specified region name.

d.

Test the procedure. What happens if you enter a region name that does not exist?

e.

Display the department number, region number, and region name for the updated department.

Create a procedure to delete the department created in Exercise 1. a.

Create a parameter for the department number.

b.

Print to the screen the number of rows affected.

c.

Test the procedure. What happens if you enter a department number that does not exist?

What happens if you enter a department that has employees?

d.

Confirm that the department has been deleted.

Interacting with Oracle

22Ć35

Practice 22

continued

If you have time, complete the following exercise. 4.

Create a procedure named NEW_EMP to insert a new employee into the S_EMP table. a.

Create parameters for first name, last name, and job title.

b.

Use the S_EMP_ID sequence generator for the employee number.

c.

Compute the user ID by concatenating the first letter of the first name with the first seven letters of the last name, and converting all letters to lowercase.

d.

Set the start date to the current date.

e.

To determine the manager number, the department number, and the salary, first find the lowest-paid existing employee having the specified job title. Then, set these three values according to that existing employee. (If several people are tied for lowest-paid, choose any one of them.)

f.

Leave the comments and the commission percent empty.

g.

Verify the record has been added by viewing the employee’s number, userid, and salary.

22Ć36

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

23 Controlling Flow in PL/SQL Blocks

23Ć2

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Objectives You can control the flow of your PL/SQL block by using conditional statements and loops. At the end of this lesson, you should be able to D

Conditionally control processing in a PL/SQL block.

D

Iterate statements by using various types of loops.

Controlling Flow in PL/SQL Blocks

23Ć3

23Ć4

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Overview You can change the logical flow of statements within the PL/SQL block with a number of control structures. This lesson addresses two types of PL/SQL control structures: D

Conditional constructs with the IF statement

D

Looping constructs D

Basic loop to provide repetitive actions without overall conditions

D

FOR loops to provide for iterative control of actions based upon a count

D

WHILE loops to provide iterative control of actions based on a true statement

D

EXIT statement to terminate loops

For more information, see PL/SQL User’s Guide and Reference, Release 2.3, Chapter 3 “Control Structures.”

Controlling Flow in PL/SQL Blocks

23Ć5

23Ć6

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

The IF Statement The structure of the PL/SQL IF statement is similar to the structure of IF statements in other procedural languages. It allows PL/SQL to perform actions selectively based upon conditions. Syntax IF condition THEN statements; [ELSIF condition THEN statements;] [ELSE statements;] END IF; where: condition

is a Boolean variable or expression (TRUE, FALSE, or NULL).

Guidelines D

When writing code, remember the spelling of the keywords. D

ELSIF is one word.

D

END IF is two words.

D

If the controlling Boolean condition is TRUE, the associated sequence of statements is executed; if the controlling Boolean condition is FALSE or NULL, the associated sequence of statements is passed over.

D

Any number of ELSIF clauses are permitted.

D

There can be at most one ELSE clause.

D

Indent the conditionally executed statements for clarity.

Controlling Flow in PL/SQL Blocks

23Ć7

23Ć8

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

The IF Statement

continued

Simple IF Statements PL/SQL executes the conditional statements only if the condition is TRUE. If the condition is FALSE or NULL, then PL/SQL ignores the conditional statements. In either case, control resumes at the next statement in the program following END IF. Example Set the job title to Sales Representative and the region number to 35 if the last name is Dumas. . . . IF v_last_name = ’Dumas’ THEN v_job := ’Sales Representative’; v_region_id := 35; END IF; . . .

IFĆTHENĆELSE Statements If the condition is FALSE or NULL, you can use the ELSE clause to carry out other actions. As with the simple IF statement, control resumes in the program from the END IF. Example Set a flag for orders where there are fewer than five days between order date and ship date. . . . IF v_date_shipped - v_date_ordered < 5 THEN v_ship_flag := ’Acceptable’; ELSE v_ship_flag := ’Unacceptable’; END IF; . . .

Controlling Flow in PL/SQL Blocks

23Ć9

23Ć10

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

The IF Statement

continued

Nested IF Statements Either set of actions of the result of the first IF statement can include further IF statements before specific actions are performed. Each nested IF statement must be terminated with a corresponding END IF. IFĆTHENĆELSIF Statements When possible, however, use the ELSIF clause instead of nesting IF statements. The code is easier to read and understand. The logic is clearly identified. If the action in the ELSE clause consists purely of another IF statement, it is more convenient to use the ELSIF clause. This makes the code clearer by removing the need for nested END IFs at the end of each further set of conditions and actions. Example For a given value entered, return a calculated value. If the entered value is over 100, then the calculated value is two times the entered value. If the entered value is between 50 and 100, then the calculated value is 50% of the starting value. If the entered value is less than 50, then the calculated value is 10% of the starting value. FUNCTION calc_val (v_start IN NUMBER) RETURN NUMBER IS BEGIN IF v_start > 100 THEN RETURN (2 * v_start); ELSIF v_start >= 50 THEN RETURN (.5 * v_start); ELSE RETURN (.1 * v_start); END IF; END calc_val;

Controlling Flow in PL/SQL Blocks

23Ć11

23Ć12

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Building Logical Conditions Build a simple Boolean condition by combining number, character, or date expressions with a comparison operator. In general, handle null values with the IS NULL operator. Null Within Expressions and Comparisons D

Any expression containing a null value evaluates to NULL, with the exception of a concatenated expression, which treats the null value as the empty string.

D

Any simple comparison containing a null value evaluates to NULL.

D

An IS NULL comparison evaluates to TRUE or FALSE.

Boolean Conditions with Logical Operators Build a complex Boolean condition by combining simple Boolean conditions with the logical operators AND, OR, and NOT. In the accompanying logic tables, FALSE takes precedence for an AND condition and TRUE takes precedence in an OR condition.

Controlling Flow in PL/SQL Blocks

23Ć13

23Ć14

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Building Logical Conditions

continued

The AND logic table can help you evaluate the possibilities for the Boolean condition you see below. . . . v_flag := v_reorder_flag AND v_available_flag; . . .

Controlling Flow in PL/SQL Blocks

23Ć15

23Ć16

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Loop Statements PL/SQL provides a number of facilities to structure loops to repeat a statement or sequence of statements multiple times. Basic Loop The simplest loop consists of the body of statements to be repeated enclosed between the delimiters LOOP and END LOOP. Each time the flow of execution reaches the END LOOP statement, control is returned to the corresponding LOOP statement above it. This uncontrolled loop is an infinite loop that is to be avoided. To avoid an infinite loop, add an EXIT statement. The EXIT Statement You can terminate a loop using the EXIT statement. Control passes to the next statement after the END LOOP statement. You can issue EXIT either as an action within an IF statement, or as a standalone statement within the loop. In the latter case, you can attach a WHEN clause to allow conditional termination of the loop. Syntax LOOP statement1; statement2; . . . EXIT [WHEN condition]; END LOOP; where: condition

Controlling Flow in PL/SQL Blocks

is a Boolean variable or expression (TRUE, FALSE, or NULL).

23Ć17

23Ć18

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Loop Statements

continued

Example Insert the first ten new line items for order number 101. . . . v_ord_id s_item.ord_id%TYPE := 101; v_counter NUMBER (2) := 1; BEGIN . . . LOOP INSERT INTO s_item (ord_id, item_id) VALUES (v_ord_id, v_counter); v_counter := v_counter + 1; EXIT WHEN v_counter > 10; END LOOP; . . .

Controlling Flow in PL/SQL Blocks

23Ć19

23Ć20

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Loop Statements

continued

FOR Loop FOR loops have the same general structure as the loops you have already seen. In addition, they have a control statement at the front of the LOOP keyword to determine the number of iterations PL/SQL performs. Syntax FOR index IN [REVERSE] lower_bound..upper_bound LOOP statement1; statement2; . . . END LOOP; where: index

is an implicitly declared integer whose value automatically increases or decreases by 1 on each iteration of the loop until the upper bound is reached.

REVERSE

causes the index to decrement with each iteration from the upper bound to the lower bound.

lower_bound

specifies the lower bound for the range of index values.

upper_bound

specifies the upper bound for the range of index values.

Note: Do not declare the index; it is declared implicitly as an integer.

Controlling Flow in PL/SQL Blocks

23Ć21

23Ć22

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Loop Statements

continued

Example Print the number of times the loop is executed and the last value for the index based on the supplied lower bound and upper bound. PROCEDURE iterate (v_lower NUMBER, v_upper NUMBER) IS v_counter NUMBER(10) := 0; v_output NUMBER(10); BEGIN FOR i IN v_lower..v_upper LOOP v_counter := v_counter + 1; v_output := i; END LOOP; TEXT_IO.PUT_LINE(’Last value is ’||TO_CHAR(v_output) ||’. Total loops = ’||TO_CHAR(v_counter)); END iterate;

Guidelines D

Reference the index within the loop only; it is undefined outside the loop.

D

Reference the existing value of an index within an expression.

D

Do not reference the index as the target of an assignment.

Controlling Flow in PL/SQL Blocks

23Ć23

Condition is evaluated at the beginning of each iteration.

23Ć24

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Loop Statements

continued

WHILE Loop You can use the WHILE loop to repeat a sequence of statements until the controlling condition is no longer TRUE. The condition is evaluated at the start of each iteration. The loop terminates when the condition is FALSE. If the condition is FALSE at the start of the loop, then no further iterations are performed. Syntax WHILE condition LOOP statement1; statement2; . . . END LOOP; where: condition

is a Boolean variable or expression (TRUE, FALSE, or NULL).

If the variables involved in the conditions do not change during the body of the loop, then the condition will remain TRUE, and the loop will not terminate.

Controlling Flow in PL/SQL Blocks

23Ć25

23Ć26

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Loop Statements

continued

Nested Loops and Labels You can nest loops to multiple levels. You may nest FOR loops within WHILE loops, and WHILE loops within FOR loops. Normally, the termination of a nested loop does not terminate the enclosing loop (unless an exception was raised). However, you can label loops and exit the outer loop with the EXIT statement. Label names follow the same rules as other identifiers. A label is placed before a statement, either on the same line or a separate line. Label loops by placing the label before the word LOOP within label delimiters (<>). If the loop is labeled, the label name can optionally be included after the END LOOP statement. ... BEGIN <>LOOP v_counter :=v_counter+1; EXIT WHEN v_counter>10; <>LOOP ... EXIT Outer_loop WHEN total_done = ’YES’; –– Leave both loops EXIT WHEN inner_done = ’YES’; –– Leave inner loop only ... END LOOP Inner_Loop; ... END LOOP Outer_loop; END;

Controlling Flow in PL/SQL Blocks

23Ć27

23Ć28

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Summary Control PL/SQL logic with the conditional structure and loops. Control Structure

Description

IF-THEN-ELSE

Executes a statement or sequence of statements conditionally.

Basic loop

Repeats a statement or sequence of statements indefinitely.

EXIT statement

Terminates a loop.

FOR loop

Repeats a statement or sequence of statements a fixed number of times.

WHILE loop

Repeats a statement or sequence of statements until a condition is no longer TRUE.

Controlling Flow in PL/SQL Blocks

23Ć29

23Ć30

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Practice Overview In this practice, you create procedures that incorporate loops and conditional control structures. Practice Contents D

Performing conditional actions using the IF statement

D

Performing iterative steps by using the loop structure

D

Viewing the values variables during runtime by using Procedure Builder debugging features

Controlling Flow in PL/SQL Blocks

23Ć31

23Ć32

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Practice 23 If you are not already connected to the database, be sure to do so now. 1.

Create a procedure named SET_COMM to conditionally set the commission percentage for a given employee based upon total sales. a.

Prepare this exercise by disabling the constraint on the COMMISSION_PCT column in the S_EMP table. You modify the commission percentage to values that are not in the constraint. PL/SQL> ALTER TABLE s_emp +> DROP CONSTRAINT s_emp_commission_pct_ck;

b.

Create a parameter to accept the employee number from the user.

c.

Find the sum of the totals for all orders placed by that employee.

d.

If the sum is less than 100,000, set the commission percentage for the employee to 10.

e.

If the sum is between 100,000 and 1,000,000 inclusive, set the commission percentage for the employee to 15.

f.

If the sum exceeds 1,000,000, set the commission percentage for the employee to 20.

g.

If no orders appear in the S_ORD table for the given employee, set the commission percentage to 0.

h.

Commit the change.

i.

Test the block and view the changes. Results should appear for some example employees as follows: Employee Number Total Sales Resulting Commission --------------- ------------ --------------------1 0 0 11 1629066.37 20 12 100184.00 15 14 16358.00 10

Controlling Flow in PL/SQL Blocks

23Ć33

Practice 23 2.

continued

Write a procedure named CUST_UPDATE that loops through the region numbers (1 through 5) to update the credit rating of all customers. Do not issue a commit. a.

If the region number is even, then set the credit rating to Excellent (even if the customer’s credit rating is already Excellent), otherwise set the credit rating to Good.

b.

Once the rows are updated, find out how many rows were updated. Print the following statements to the screen based on the number of rows updated.

c.

i.

If less than three rows updated, statement should read “Fewer than 3 customer records updated for region number X”, where X represents the region number.

ii.

Otherwise, it should read “Y rows updated for region number X”, where Y is the number of rows updated, and X is the region number.

Rollback the changes. Set a breakpoint on the conditional test for the number of rows updated. Execute the procedure. Check the Stack node to verify the values of the variables as you Step Into the statements in the loop. Reminder: You can use the Quick Tour, “Debugging a Program Unit” section for assistance.

If you have time, complete the following exercise. 3.

Create a procedure named EMP_MESSAGE that selects the employee last name, start date, and salary based on an employee number provided at execution. Print a message to the screen based on any combination of one of the following criteria. Test employee numbers 2, 5, 16, 17, and 18. Hint: A nested IF is required. Criteria

Message

Salary greater than 1200

Salary more than 1200

Last name contains “R”

Name contains “R”

Start date is in March

March start date

None of the above

**None**

23Ć34

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

24 Processing Queries by Using Explicit Cursors

24Ć2

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Objectives You may need to use a multiple row SELECT statement within PL/SQL to process many rows. To accomplish this, you declare and control explicit cursors, which are used in loops, including the cursor FOR loop. At the end of this lesson, you should be able to D

Explain the difference between implicit and explicit cursors.

D

Declare and use explicit cursors to fetch rows from the database.

D

Create an explicit cursor containing parameters.

D

Write cursor FOR loops.

Processing Queries by Using Explicit Cursors

24Ć3

24Ć4

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Overview The Oracle7 Server uses work areas called “private SQL areas” to execute SQL statements and store processing information. PL/SQL cursors let you name a private SQL area and access its stored information. The cursor directs all phases of processing. Cursor Type

Description

Implicit

Declared by PL/SQL implicitly for all DML and PL/SQL SELECT statements.

Explicit

Declared and named by the programmer and manipulated through specific statements within the block’s executable actions.

Recall that the SELECT statement in PL/SQL must only return a single row. PL/SQL actually attempts to fetch two rows from an implicit cursor: one to satisfy the query, and a second to see if further rows were returned. One method to eliminate this extra fetch is to use an explicit cursor. Explicit Cursor Functions D

Can process beyond the first row returned by the query, row by row.

D

Keep track of which row is currently being processed.

D

Allow the programmer to manually control them in the PL/SQL block.

Processing Queries by Using Explicit Cursors

24Ć5

24Ć6

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Controlling Explicit Cursors Now that you have a conceptual understanding of cursors, review the steps to use them. The syntax for each step follows on the next pages. Controlling Explicit Cursors Using Four Commands 1.

Declare the cursor. Declare the cursor by naming it and defining the structure of the query to be performed within it.

2.

Open the cursor. The OPEN statement executes the query and binds any variables that are referenced. Rows identified by the query are called the active set and are now available for fetching.

3.

Fetch data from the cursor. The FETCH statement loads the current row from the cursor into variables. Each fetch causes the cursor to move its pointer to the next row in the active set. Therefore, each fetch will access a different row returned by the query. In the flow diagram on the left page, each fetch tests the cursor for any existing rows. If rows are found, it loads the current row into variables, else it closes the cursor.

4.

Close the cursor. The CLOSE statement releases the active set of rows. It is now possible to reopen the cursor to establish a fresh active set.

Processing Queries by Using Explicit Cursors

24Ć7

24Ć8

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Controlling Explicit Cursors

1

continued

Declaring the Cursor Use the CURSOR statement to declare an explicit cursor. You can define parameters to allow substitution of values into the query when the cursor is opened. You can also reference variables within the query, but you must declare them before the cursor statement. Syntax DECLARE CURSOR cursor_name IS select_statement; where: cursor_name select_statement

is a PL/SQL identifier. is a SELECT statement without an INTO clause.

Note: Do not include the INTO clause within the cursor declaration because it appears later within the FETCH statement.

Processing Queries by Using Explicit Cursors

24Ć9

24Ć10

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Controlling Explicit Cursors

2

continued

Opening the Cursor Open the cursor to execute the query and identify the active set after specifying values for all input variables. The cursor will now point to the first row in the active set. Syntax OPEN cursor_name; where: cursor_name

is the name of the previously-declared cursor.

Note: If the query returns no rows when the cursor is opened, PL/SQL does not raise an exception. However, you can test the cursor’s status after a fetch.

Processing Queries by Using Explicit Cursors

24Ć11

24Ć12

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Controlling Explicit Cursors

3

continued

Fetching Data from the Cursor Use the FETCH statement to retrieve the current row values into output variables. After the fetch, you can manipulate the variables by further statements. Syntax FETCH cursor_name INTO variable1, variable2, . . .; where: cursor_name variable

is the name of the previously declared cursor. is an output variable to store the results.

Guidelines D

Include the same number of variables within the INTO clause of the FETCH statement as output columns in the SELECT statement, and be sure that the datatypes are compatible.

D

Match each variable to correspond to the columns positionally.

D

Alternatively, define a record for the cursor and reference the record in the FETCH INTO clause.

D

Test to see if the cursor contains rows. If a fetch acquires no values, that is, there are now rows left to process in the active set and no error is recorded.

Processing Queries by Using Explicit Cursors

24Ć13

24Ć14

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Controlling Explicit Cursors

4

continued

Closing the Cursor Close the cursor after completing the processing of the SELECT statement. This step allows the cursor to be reopened, if required. Therefore, you can establish an active set several times. Syntax CLOSE cursor_name; where: cursor_name

is the name of the previously declared cursor.

Note: Do not attempt to fetch data from a cursor once it has been closed or the INVALID_CURSOR exception will be raised.

Processing Queries by Using Explicit Cursors

24Ć15

24Ć16

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Explicit Cursor Attributes As with implicit cursors, there are four attributes for obtaining status information about a cursor. When used, the attribute name is preceded by the cursor identifier. Cursor Attribute

Description

%ISOPEN

Boolean attribute that evaluates to TRUE if the cursor is open.

%NOTFOUND

Boolean attribute that evaluates to TRUE if the most recent fetch does not return a row.

%FOUND

Boolean attribute that evaluates to TRUE until the most recent fetch does not return a row; complement of %NOTFOUND.

%ROWCOUNT

Numeric attribute that evaluates to the total number of rows returned so far.

Note: Do not reference cursor attributes directly within a SQL statement. Controlling Multiple Fetches from Explicit Cursors Typically, when you want to process several rows from an explicit cursor, you define a loop to perform a fetch on each iteration. Eventually, all rows in the active set will be processed, and an unsuccessful fetch sets the %NOTFOUND attribute to TRUE. Use the explicit cursor attributes to test success of each fetch before any further references are made to the cursor. If you omit an exit criteria, an infinite loop will result. For more information, see PL/SQL User’s Guide and Reference, Release 2.3 “Using Cursor Attributes” section.

Processing Queries by Using Explicit Cursors

24Ć17

24Ć18

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Explicit Cursor Attributes

continued

You can only fetch rows when the cursor is open. Determine whether the cursor is open using the %ISOPEN cursor attribute, if necessary. Fetch rows in a loop. Determine when to exit the loop by using cursor attributes. To retrieve an exact number of rows, fetch the rows in a numeric FOR loop, or fetch the rows in a simple loop and determine when to exit the loop by using the %ROWCOUNT cursor attribute. Example Retrieve the first five line items for an order one by one. Print the cumulative total for each product to the screen. PROCEDURE ord_process (v_ord_id IN s_item.ord_id%TYPE) IS v_product_id s_item.product_id%TYPE; v_item_total NUMBER (11,2); v_order_total NUMBER (11,2) := 0; CURSOR item_cursor IS SELECT product_id, price * quantity FROM s_item WHERE ord_id = v_ord_id; BEGIN OPEN item_cursor; LOOP FETCH item_cursor INTO v_product_id, v_item_total; EXIT WHEN item_cursor%ROWCOUNT > 5 OR item_cursor%NOTFOUND; v_order_total := v_order_total + v_item_total; TEXT_IO.PUT_LINE (’Product ID ’|| TO_CHAR(v_product_id)|| ’ has a total of ’|| TO_CHAR(v_order_total,’$999,999,999.99’)); END LOOP; CLOSE item_cursor; END ord_process; Note: If using %ROWCOUNT, add a test for no rows in the cursor by using the %NOTFOUND attribute because the rowcount is not incremented if the fetch does not retrieve any rows.

Processing Queries by Using Explicit Cursors

24Ć19

24Ć20

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Cursors and Records We have already seen that you can define records to use the structure of columns in a table. You can also define a record based on the selected list of columns in an explicit cursor. This is convenient for processing the rows of the active set since you can simply fetch into the record. Therefore, the values of the row are loaded directly into the corresponding fields of the record. In the example, you can select the ROWID pseudo-column, and it will have a corresponding field within the EMP_RECORD PL/SQL record.

Processing Queries by Using Explicit Cursors

24Ć21

24Ć22

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Cursors with Parameters Parameters allow values to be passed to a cursor when it is opened, and used within the query when it executes. This means that you can open an explicit cursor several times in a block, returning a different active set on each occasion. Parameter datatypes are the same as those for scalar variables, but you do not give them sizes. The parameter names are for references within the cursor’s query expression. Syntax CURSOR cursor_name [(parameter_name datatype, ...)] IS select_statement; where: cursor_name

is a PL/SQL identifier for the previously declared cursor.

parameter_name

is the name of a parameter.

datatype

is a scalar datatype of the parameter.

select_statement

is a SELECT statement without the INTO clause.

When the cursor is opened, you pass values to each of the parameters positionally. You can pass values from PL/SQL or host variables as well as literals.

Processing Queries by Using Explicit Cursors

24Ć23

24Ć24

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Cursor FOR Loops A cursor FOR loop processes rows in an explicit cursor. It is a shortcut because the cursor is opened, rows are fetched once for each iteration in the loop, and the cursor is closed automatically when all rows have been processed. The loop itself is terminated automatically at the end of the iteration where the last row was fetched. Syntax FOR record_name IN cursor_name LOOP statement1; statement2; . . . END LOOP; where: record_name cursor_name

is the name of the implicitly-declared record. is a PL/SQL identifier for the previously-declared cursor.

Guidelines D

Do not declare the record that controls the loop. Its scope is only in the loop.

D

Test the cursor attributes during the loop, if required.

D

Supply the parameters for a cursor, if required, in parentheses following the cursor name in the FOR statement.

D

Do not use a cursor FOR loop when the cursor operations have to be handled manually.

Processing Queries by Using Explicit Cursors

24Ć25

24Ć26

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Applying the WHERE CURRENT OF Clause You may want to lock rows before you update or delete rows. Add the FOR UPDATE clause within the cursor query, which locks the affected rows when the cursor is opened. Since the Oracle7 Server releases locks at the end of the transaction, you should not commit across fetches from an explicit cursor if FOR UPDATE is used. Syntax SELECT...FROM...FOR UPDATE [OF column_reference] [NOWAIT] where: column_reference NOWAIT

is a column in the table against which the query is performed. returns an Oracle error if the rows are locked by another session.

When querying multiple tables, you can use the FOR UPDATE clause to confine row locking to particular tables. Rows in a table are locked only if the FOR UPDATE clause refers to a column in that table. WHERE CURRENT OF Clause Additionally, you can write your DELETE or UPDATE statement to contain the WHERE CURRENT OF cursor_name clause to refer to the latest row processed by the FETCH statement. When you use this clause, the cursor you reference must exist and must contain the FOR UPDATE clause in the cursor query, otherwise you will obtain an error. This clause allows you to apply updates and deletes to the currently addressed row without the need to explicitly reference the ROWID pseudo-column.

Processing Queries by Using Explicit Cursors

24Ć27

24Ć28

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Summary Cursor Types D

Implicit cursors are used for all DML statements and single row queries.

D

Explicit cursors are used for queries of zero, one, or more rows.

Explicit Cursor Commands D

Declare the cursor.

D

Open the cursor.

D

Fetch data from the cursor.

D

Close the cursor.

Cursor Attributes Evaluate the status of the cursor by using cursor attributes. Cursors with Parameters Use parameters to open an explicit cursor several times in a block, returning a different active set on each occasion. Cursor FOR Loops Use cursor FOR loops as a shortcut to open the cursor, fetch rows once for each loop iteration, and automatically close the cursor after all rows are processed.

Processing Queries by Using Explicit Cursors

24Ć29

24Ć30

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Practice Overview This practice applies your knowledge of cursors to process a number of rows from a table and populate another table with the results using a cursor FOR loop. Practice Contents D

Declaring and using a cursor to query rows of a table

D

Using a cursor FOR loop

D

Applying cursor attributes to test the cursor status

Processing Queries by Using Explicit Cursors

24Ć31

24Ć32

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Practice 24 1.

Create a procedure named TOP_DOGS1 that determines the top employees with respect to salaries. a.

Prepare for this exercise by creating a new table for storing employees and their salaries. PL/SQL> CREATE TABLE top_dogs +> (name VARCHAR2(25), +> salary NUMBER(11,2));

2.

b.

Add a parameter to accept the user input n for the top number of employees.

c.

Write a cursor FOR loop to get the last names and salaries of the top n people, with respect to salary, from the S_EMP table.

d.

Store the names and salaries in the TOP_DOGS table.

e.

Assume for the moment that no two employees have the same salary.

f.

Test the exercise under a variety of special cases, such as n = 0, n is greater than the number of employees in the S_EMP table.

g.

Empty the TOP_DOGS table after each test.

Create a stored procedure named ADD_STARS to reward all employees by appending an asterisk in the STARS column for every commission point. a.

Prepare for this exercise by creating a new column in the S_EMP table for storing asterisks (*). PL/SQL> ALTER TABLE s_emp +> ADD stars VARCHAR2(100);

b.

Determine the commission percentage for the employee, rounded to the nearest whole number. Also, consider the case where the employee has no commission percent.

c.

Add an asterisk to a string of asterisks for every commission point. For example, if the employee has a commission percentage of 10, then the STARS column will contain ten asterisks.

d.

Update the STARS column for each employee with the string of asterisks.

Processing Queries by Using Explicit Cursors

24Ć33

Practice 24

continued

If you have time, complete the following exercise. 3.

Copy the TOP_DOGS1 procedure from exercise 1, and name it TOP_DOGS2. Modify the TOP_DOGS2 procedure to consider the case where several employees in the exercise 1 have the same salary. For each name listed, all names having the same salary should also be listed. Execute the TOP_DOGS2 procedure. Enter 6, 7, or 8 for n, then Ngao, Dumas, and Quick-To-See should all display. If you enter 9, 10, or 11 for n, then Nagayama, Magee, and Maduro should all display. Remember to empty the TOP_DOGS table after each test.

24Ć34

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

25 Error Handling

25Ć2

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Objectives When you execute PL/SQL code, you may encounter errors. Errors cause the PL/SQL block to halt with an exception. You can trap the exception and perform actions conditionally using exception handlers. At the end of this lesson, you should be able to D

Identify common exceptions.

D

Describe the three basic types of exceptions.

D

Write exception handling routines.

Error Handling

25Ć3

25Ć4

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Overview An exception is an identifier in PL/SQL, raised during the execution of a block that terminates its main body of actions. A block will always terminate when PL/SQL raises an exception, but you specify an exception handler to perform final actions. Two Methods for Raising an Exception D

An Oracle error occurs and the associated exception is raised automatically. For example, if the error ORA-01403 occurs when no rows are retrieved from the database, then PL/SQL raises the exception NO_DATA_FOUND.

D

You raise an exception explicitly by issuing the RAISE statement within the block. The exception being raised may be either user-defined or predefined.

Trapping an Exception If the exception is raised in the executable section of the block, processing branches to the corresponding exception handler in the exception section of the block. If PL/SQL successfully handles the exception, then the exception does not propagate to the enclosing block or environment. Propagating an Exception The other method for handling an exception is to allow it to propagate to the calling environment. If the exception is raised in the executable section of the block and there is no corresponding exception handler, the PL/SQL block terminates with failure.

Error Handling

25Ć5

25Ć6

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Exception Types You can program for exceptions to avoid disruption at runtime. There are three types of exceptions. Exception

Description

Directions for Handling

Predefined Oracle7 One of approximately 20 Server error most errors that occur often in PL/SQL code.

Do not declare, and allow the Oracle7 Server to raise them implicitly.

Non-Predefined Oracle7 Server error

Any other standard Oracle7 Server error.

Declare within the declarative section, and allow the Oracle7 Server to raise them implicitly.

User-defined error

A condition that the developer determines is abnormal.

Declare within the declarative section, and raise explicitly.

Error Handling

25Ć7

25Ć8

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Trapping Exceptions Trap any error by including a corresponding routine within the exception handling section of the PL/SQL block. Syntax EXCEPTION WHEN exception1 [OR exception2 . . .] THEN statement1; statement2; . . . [WHEN exception3 [OR exception4 . . . THEN statement1; statement2; . . .] [WHEN OTHERS THEN statement1; statement2; . . .] where: exception

WHEN OTHERS

is the standard name of a predefined exception or the name of a user-defined exception declared within the declarative section. indicates the exception handling routine for any exception is not listed explicitly.

Guidelines D

Place the WHEN OTHERS clause after all other exception handling clauses.

D

You can have at most one WHEN OTHERS clause.

D

Begin exception-handling section of the block with the keyword EXCEPTION.

D

Define several exception handlers, each with their own set of actions, for the block.

D

When an exception occurs, PL/SQL will process only one handler before leaving the block.

For more information, see Oracle Course Catalog to attend Develop Applications with Database Procedures course.

Error Handling

25Ć9

25Ć10

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Trapping Predefined Oracle7 Server Exceptions Trap a predefined Oracle7 Server error by referencing its standard name within the corresponding exception-handling routine. Sample Predefined Exceptions

Exception Name

Oracle Server Error Number Description

NO_DATA_FOUND

ORA-01403

Single row SELECT returned no data.

TOO_MANY_ROWS

ORA-01422

Single row SELECT returned more than one row.

INVALID_CURSOR

ORA-01001

Illegal cursor operation occurred.

ZERO_DIVIDE

ORA-01476

Attempted to divide by zero.

DUP_VAL_ON_INDEX ORA-00001

Attempted to insert a duplicate value into a column that has a unique index.

You can use the debugging capabilities in Procedure Builder to identify and trap exceptions prior to moving procedures to the database. For more information, see PL/SQL User’s Guide and Reference, Release 2.3 “Predefined Exceptions” section.

Error Handling

25Ć11

25Ć12

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Trapping Predefined Oracle7 Server Exceptions

continued

Example Eliminate all inventory items for a particular product. Be sure the product number is valid. PROCEDURE elim_inventory (v_product_id IN s_product.id%TYPE) IS v_id s_product.id%TYPE; BEGIN SELECT id INTO v_id FROM s_product WHERE id = v_product_id; DELETE FROM s_inventory WHERE product_id = v_product_id; COMMIT; EXCEPTION WHEN NO_DATA_FOUND THEN ROLLBACK; TEXT_IO.PUT_LINE(TO_CHAR(v_product_id)|| ’ is invalid.’); WHEN TOO_MANY_ROWS THEN ROLLBACK; TEXT_IO.PUT_LINE(’Data corruption in S_PRODUCT.’); WHEN OTHERS THEN ROLLBACK; TEXT_IO.PUT_LINE(’Other error occurred.’); END elim_inventory;

Only one exception is raised and handled at any time.

Error Handling

25Ć13

1 2

3

1 Name the exception.

25Ć14

2 Code the pragma EXCEPTION_INIT.

3 Handle the raised exception.

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Trapping NonĆPredefined Oracle7 Server Exceptions Trap a non-predefined Oracle7 Server error by declaring it first, rather than by using the WHEN OTHERS handler. The declared exception will be raised implicitly. Be sure to reference the declared exception in the exception handling section. Trapping a NonĆPredefined Oracle7 Server Exception 1.

Declare the name for the exception within the declarative section. Syntax exception

EXCEPTION;

where: exception 2.

is the name of the exception.

Associate the declared exception with the standard Oracle7 Server error number using the pragma EXCEPTION_INIT statement. Syntax PRAGMA EXCEPTION_INIT (exception, error_number); where: exception error_number

3.

is the previously-declared exception. is a standard Oracle7 Server error number.

Reference the declared exception within the corresponding exception handling routine.

For more information, see Oracle7 Server Messages, Release 7.3.

Error Handling

25Ć15

1 Name the exception.

25Ć16

2 Explicitly raise the exception by using the RAISE statement.

1

1

2

2

3

3

3 Handle the raised exception.

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Trapping UserĆDefined Exceptions Trap a user-defined exception by declaring it and raising it explicitly. Trapping a UserĆDefined Exception 1.

Declare the name for the user-defined exception within the declarative section. Syntax exception

EXCEPTION;

where: exception 2.

is the name of the exception.

Raise the exception explicitly within the executable section using the RAISE statement. Syntax RAISE exception; where: exception

3.

is the previously declared exception.

Reference the declared exception within the corresponding exception handling routine.

Error Handling

25Ć17

25Ć18

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Error Trapping Functions When an exception is trapped in the WHEN OTHERS section, you can use a set of generic functions for identifying those errors. WHEN OTHERS Exception Handler The exception-handling section only traps those exceptions specified; any other exceptions would not be trapped unless you use the WHEN OTHERS exception handler. This will trap any exception not yet handled. For this reason, the WHEN OTHERS is be the last exception handler defined. The WHEN OTHERS handler traps all exceptions not already trapped. Some Oracle tools have their own predefined exceptions that you can raise to cause events in the application. WHEN OTHERS also traps these exceptions. Functions for Error Trapping When an exception occurs, you can identify the associated error code or error message by using two functions. Based on the values of the code or message, you can decide what subsequent action to take based upon the error. Function

Description

SQLCODE

Returns the numeric value for the error code. You can assign it to a NUMBER variable.

SQLERRM

Returns character data containing the message associated with the error number.

Example SQLCODE Values SQLCODE Value

Description

0

No exception encountered.

1

User-defined exception.

+100

NO_DATA_FOUND exception.

negative_number

Another Oracle7 Server error number.

Truncate the value of SQLERRM to a known length before attempting to write it to a variable.

Error Handling

25Ć19

25Ć20

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Propagating Exceptions Instead of trapping an exception within the PL/SQL block, propagate the exception to allow the calling environment to handle it. Each calling environment will have its own way of displaying and accessing errors. Calling Environment Exception Handling Calling Environment

Exception Handling Capabilities

SQL*Plus

Unhandled exception number and message are displayed on the screen.

Procedure Builder

Unhandled exception number and message are displayed on the screen.

Developer/2000 Forms

Unhandled exception number and message are accessible in a trigger by means of the ERROR_CODE and ERROR_TEXT packaged functions.

Precompiler application

Unhandled exception number is accessible through the SQLCA data structure.

An enclosing PL/SQL block

Unhandled exceptions can be trapped by the exception handling routine of the enclosing block.

Propagating an Exception in a SubĆblock When a sub-block handles an exception, it terminates normally, and control resumes in the enclosing block immediately after the sub-block END statement. However, if PL/SQL raises an exception and the current block does not have a handler for that exception, the exception propagates in successive enclosing blocks until it finds a handler. If none of these blocks handle the exception, this causes an unhandled exception in the host environment. When the exception propagates to an enclosing block, the remaining executable actions in that block are bypassed. One advantage of this behavior is that you can enclose statements that require their own exclusive error handling in their own block, while leaving more general exception handling to the enclosing block.

Error Handling

25Ć21

25Ć22

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Summary Program for exceptions that may arise during the execution of the PL/SQL block. Exception Types D

Predefined Oracle7 Server error.

D

Non-predefined Oracle7 Server error.

D

User-defined error.

Handle Exceptions D

Trap the exception within the PL/SQL block in an exception handling routine.

D

Allow the exception to propagate to the calling environment.

Propagate Exceptions D

Propagate an exception through a series of nested blocks.

D

Terminate PL/SQL processing with success by handling the exception in an enclosing block.

D

Terminate the PL/SQL processing with failure by passing the unhandled exception to the calling environment.

Error Handling

25Ć23

25Ć24

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Practice Overview In this practice, you create exception handlers for specific situations. Practice Contents D

Handling named exceptions

D

Creating and invoking user-defined exceptions

Error Handling

25Ć25

25Ć26

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Practice 25 1.

Modify the procedure to handle exceptions. The procedure tries to update region numbers for existing departments. a.

Load the LABS\p25q1a.pls script file. Note: The procedure UPDATE_DEPT2 is loaded and compiled.

b.

Execute the procedure by entering the following text. PL/SQL> update_dept2 (50,’US’);

c.

Write an exception handler for the error to pass a message to the user that the specified region does not exist.

d.

Execute the procedure by entering the following text. PL/SQL> update_dept2 (31,’Asia’);

e.

Write an exception handler for the error to pass a message to the user that the specified region already has a department of that name.

f.

Execute the procedure by entering the following text. PL/SQL> update_dept2 (99,’Europe’);

g.

Write an exception handler for the error to pass a message to the user that the specified department number does not exist. Reminder: Use the SQL attribute %NOTFOUND and raise an exception manually.

Error Handling

25Ć27

Practice 25

continued

If you have time, complete the following exercise. 2.

Write a procedure named SALARY_RANGE that prints the names of the employees that make plus or minus $100 of the salary value entered. a.

If there is no employee within that salary range, then print a message to the user indicating that is the case. Use an exception for this case.

b.

If there are more than 3 employees within that range, then the message should indicate how many employees have that salary range. Your results should look like the list below. Results have been formatted to fit the page. PL/SQL> SALARY_RANGE (1000); Employees who make around $1000 are: Biri, Schwartz, Smith PL/SQL> SALARY_RANGE (900); 4 employee salaries are within $100 of $900 PL/SQL> SALARY_RANGE (2000); No records within the $2000 range.

25Ć28

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

26 Summary of PL/SQL

26Ć2

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Summary PL/SQL (Procedural Language/SQL) is an extension to SQL. It is a language that allows you to combine multiple SQL statements with procedural controls such as identifiers, conditional, and iterative constructs in a PL/SQL block. Benefits from PL/SQL D Modularize program development by grouping similar concepts together and nesting blocks D

Declare identifiers, for example, variables, constants, and cursors

D

Program with procedural language structures, for example, IF statements and loops

D

Exception handling for Oracle7 Server errors and user-defined exceptions

D

Port code between applications and the Oracle7 Server

D

Integrate Oracle7 stored subprograms and application subprograms

D

Improve performance by sending a block of code to the server

PL/SQL Block A PL/SQL block contains code in up to four sections. The header section is where you set the name and parameters for named blocks. The declarative section is optional and contains all variables, constants, cursors, and user-defined exceptions that will be referenced within the executable section. The executable section is required and contains SQL statements to manipulate data in the database and PL/SQL statements to manipulate data in the block. The exception-handling section is optional and specifies the actions to perform when errors and abnormal conditions arise within the executable section. Anonymous Blocks Anonymous blocks are unnamed blocks. They are declared at the point in an application where they are to be executed and are passed to the PL/SQL engine for execution at runtime. You can embed an anonymous block within a precompiler program and within SQL*Plus or Server Manager. Triggers in Developer/2000 components consist of such blocks.

Summary of PL/SQL

26Ć3

26Ć4

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Summary

continued

Procedures Procedures are named PL/SQL blocks that store a series of actions for later execution. The procedure can contain zero or more parameters, which are arguments that you list when calling the procedure. Procedures can either be application specific or stored in the Oracle7 Server. Functions A function returns a value to the calling environment. You must declare the RETURN datatype in the header section of the function definition, and define the value to be returned in the PL/SQL block. You can also declare a list of parameters to be passed into the function. Functions are called as part of an expression. DML in PL/SQL You can use DML statements in your PL/SQL blocks. Use the SELECT statement to retrieve data from the database. D

The SELECT statement contains an additional mandatory clause: the INTO clause. In the INTO clause, list the output variables for receiving the data. The SELECT statement must return exactly one row or an error will occur.

D

The INSERT statement adds new rows of data to the table.

D

The UPDATE statement modifies existing rows in the table.

D

The DELETE statement removes unwanted rows from the table.

D

COMMIT ends the current transaction by making all pending changes to the database permanent.

D

ROLLBACK ends the current transaction by discarding all pending changes.

Summary of PL/SQL

26Ć5

26Ć6

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Summary

continued

IF Conditional Statements The PL/SQL IF statement is similar to the structure of IF statements in other procedural languages. It allows PL/SQL to perform actions selectively based upon conditions. If the controlling Boolean condition is TRUE, the associated sequence of statements is executed; if the controlling Boolean condition is FALSE or NULL, the associated sequence of statements is passed over. Iterative Loop Statements You can use one of three loop structures to repeat a sequence of statements. D The basic loop consists of the body of statements to be repeated enclosed between the delimiters LOOP and END LOOP. This uncontrolled loop is an infinite loop that is to be avoided. To avoid an infinite loop, add an EXIT statement. D FOR loops have a control statement at the front of the LOOP keyword to determine the number of iterations PL/SQL performs. D The WHILE loop repeats a sequence of statements until the controlling condition is no longer TRUE. The condition is evaluated at the start of each iteration. Cursors PL/SQL cursors let you name a private SQL area and access its stored information. The cursor directs all phases of processing. Explicit cursors are declared and named by the programmer and manipulated through specific statements within the block’s executable actions. Control explicit cursors in a loop with four steps: declare, open, fetch, and close. Cursor FOR loops are a shortcut to open the cursor, fetch rows once for each loop iteration, and automatically close the cursor after all rows are processed. Exception Handling An exception is an identifier in PL/SQL, raised during the execution of a block that terminates its main body of actions. A block will always terminate when PL/SQL raises an exception, but you specify an exception handler to perform final actions. Exceptions can be raised either by the Oracle7 Server or by the user. You can either trap an exception in the exception-handling section of the block, or allow it to propagate to the calling environment. Procedure Builder Oracle Procedure Builder is a tool you can use to create, execute, and debug PL/SQL programs used in your application tools, such as a form or report, or on the Oracle7 Server through its graphical interface.

Summary of PL/SQL

26Ć7

26Ć8

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Practice Overview This practice has you build a PL/SQL-based application to insert, update, and delete records in a video store database and generate a report. The database contains only the essential tables. Practice Contents D

Creating procedures and functions

D

Inserting, updating, and deleting data

D

Incorporating IF statements and loops

D

Handling exceptions

Note: If you are new to the class, you can execute the buildvid.sql script in SQL*Plus to create and populate the tables. If you want to drop the work you did in the course summary from the SQL section, you can execute the dropvid.sql script in SQL*Plus to drop their tables. Then, you can execute the buildvid.sql script in SQL*Plus to create and populate the tables.

Summary of PL/SQL

26Ć9

26Ć10

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Practice 26 Entity Relationship Diagram The facing page contains an entity relationship diagram depicting the tables and columns for a small movie rental application. 1.

Complete the database design by writing subprograms to perform the following actions. a.

Create a function named NEW_MEMBER to return the membership number for that new member. Be sure to use a sequence generator to create the unique member numbers. Check the data dictionary for the sequence name.

b.

Create a function named NEW_RENTAL to record a new rental based on a member’s last name. Return a message with the expected due date. If two or more people have the same last name, store the first name, last name, phone, and membership number for all matching last names in the RESULTS table.

c.

Create a procedure named MEM_TITLES to retrieve all the titles rented by a member. Store the results in the RESULTS table.

d.

If a title is rented, create a reservation for that movie. Name your procedure RESERVE_MOVIE.

e.

When a video is returned, change the rental status for that returned copy. When the video is successfully checked in, store a message in the RESULTS table. Check the to see if there are reservations for the film title, and store a message in the RESULTS table. Name your procedure RETURN_RENTAL.

f.

If a video is returned damaged, change its status to DAMAGED. If that copy was the only copy owned by the store, store a message to buy another copy in the RESULTS table. If only one copy remains for rental, store a warning that only one copy is left for rental in the RESULTS table. Name your procedure DAMAGED_COPY.

g.

Create a procedure named TOP_RENTAL to determine the top rental. Allow for more than one top rental. Store the names and a message of the top rentals in the RESULTS table.

Summary of PL/SQL

26Ć11

26Ć12

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder