Course Reference Material
Oracle PL/SQL : Procedures, Functions, and Packages
NETg course 61134
Copyright © 2002 National Education Training Group, Inc.
All rights reserved. No part of the material protected by this copyright may be reproduced or utilized in any form or by any means, electronic or mechanical, including photocopying, recording, broadcasting, or by any information storage and retrieval system, without permission in writing from National Education Training Group, Inc.
Skill Builder is a registered trademark of National Education Tra 24324o1422y ining Group, Inc. All other trademarks referenced are the trademark, service mark, or registered trademark of their respective holders. National Education Tra 24324o1422y ining Group, Inc. is not affiliated with any company or any other product or vendor mentioned in this course and its accompanying materials.
The software and technology used to implement this course contain trade secrets that NETg considers to be confidential and proprietary information, and your right to use this material is subject to the restrictions in the license agreement under which you obtained it.
Objectives.......... ..... ...... .......... ..... ...... .......... ..... ...... .......... ..... ...... ... 4
Subprograms: Procedures.......... ..... ...... .......... ..... ...... .......... ..... ...... .......... ..... ...... .......... ..... ...... . 4
Subprograms: Overview.......... ..... ...... .......... ..... ...... .......... ..... ...... .......... ..... ...... .......... ..... ...... ... 4
Creating Procedures.......... ..... ...... .......... ..... ...... .......... ..... ...... .......... ..... ...... .......... ..... ...... ......... 4
Invoking Procedures.......... ..... ...... .......... ..... ...... .......... ..... ...... .......... ..... ...... .......... ..... ...... ........ 5
Subprograms: Procedures.......... ..... ...... .......... ..... ...... .......... ..... ...... .......... ..... ...... .......... ..... ...... . 6
Creating Functions.......... ..... ...... .......... ..... ...... .......... ..... ...... .......... ..... ...... .......... ..... ...... ........... 6
Invoking Functions.......... ..... ...... .......... ..... ...... .......... ..... ...... .......... ..... ...... .......... ..... ...... .......... 6
Procedures and Functions.......... ..... ...... .......... ..... ...... .......... ..... ...... .......... ..... ...... .............................. 6
PL/SQL Packages.......... ..... ...... .......... ..... ...... .......... ..... ...... .......... ..... ...... .......... ..... ...... ............... 7
Package Concepts.......... ..... ...... .......... ..... ...... .......... ..... ...... .......... ..... ...... .......... ..... ...... ............. 7
Developing PL/SQL Packages.......... ..... ...... .......... ..... ...... .......... ..... ...... .......... ..... ...... ....................... 7
Creating PL/SQL Packages.......... ..... ...... .......... ..... ...... .......... ..... ...... .......... ..... ...... ............................. 7
Working with PL/SQL Packages.......... ..... ...... .......... ..... ...... .......... ..... ...... .......... ..... ...... .................... 7
Advanced Package Concepts.......... ..... ...... .......... ..... ...... .......... ..... ...... .......... ..... ...... ............................. 8
Overloading Packages.......... ..... ...... .......... ..... ...... .......... ..... ...... .......... ..... ...... .......... ..... ...... ...... 8
Procedures and Functions in Packages.......... ..... ...... .......... ..... ...... .......... ..... ...... .......... ..... ...... ........ 8
Persistent State of Variables and Cursors.......... ..... ...... .......... ..... ...... .......... ..... ...... .......... ..... ...... .... 8
Glossary.......... ..... ...... .......... ..... ...... .......... ..... ...... .......... ..... ...... ...... 9
Alphabetical List of Terms.......... ..... ...... .......... ..... ...... .......... ..... ...... .......... ..... ...... .......... ..... ...... . 9
Total number of objectives = 48
Note: (ULT) is the unit, lesson, topic ID.
Subprograms: Overview
Subprograms: Block Structure (ULT 112): Match the various sections of a subprogram with their contents.
Subprograms: Benefits (ULT 113): Identify the benefits of the PL/SQL subprograms.
Developing Environments (ULT 114): Identify the features of development environments.
Procedure: Developing (ULT 115): Sequence the steps that are used to develop a procedure.
Creating Procedures
Subprograms: Creation (ULT 121): Match the CREATE PROCEDURE statement clauses with their description.
Subprograms: Creating (ULT 122): Create a subprogram by using iSQL*Plus.
Parameter Modes: Introduction (ULT 123): Identify the features of the different types of parameter modes.
IN Parameter: Declaring (ULT 124): Declare an IN parameter for a procedure by using iSQL*Plus.
OUT Parameter: Declaring (ULT 125): Declare an OUT parameter for a procedure by using iSQL*Plus.
IN OUT Parameter: Declaring (ULT 126): Declare an IN OUT parameter for a procedure by using iSQL*Plus
Invoking Procedures
Passing Parameters (ULT 131): Match the different methods for passing parameters with their descriptions.
Default Parameter: Passing (ULT 132): Declare a default parameter to a procedure by using iSQL*Plus.
Invoking Procedures: Anonymous Blocks (ULT 133): invoke a procedure from an anonymous block by using iSQL*Plus.
Procedures from Procedures: Invoking (ULT 134): Perform the steps to invoke a procedure from another procedure by using iSQL*Plus.
Procedure within a Procedure: Features (ULT 135): Identify the features of a procedure that is declared within another procedure.
Exceptions in a Procedure: Handle (ULT 136): Sequence the steps that are followed to handle an exception.
Creating Functions
Functions: Features (ULT 212): Identify the features of a function.
Functions: Creation Syntax (ULT 213): Match the clauses of the CREATE FUNCTION statement with their descriptions.
Functions: Creating (ULT 214): Create a function by using iSQL*Plus.
Invoking Functions
Functions: Invoking (ULT 221): Invoke a function by using iSQL*Plus.
Functions in SQL Statements: Uses (ULT 222): identify the advantages of user-defined functions in SQL expressions.
Functions: Restrictions (ULT 223): Identify the restrictions when calling user-defined functions from SQL expressions.
Procedures and Functions
Subprograms: Removing (ULT 231): remove subprograms by using iSQL*Plus.
Procedures and Functions: Comparison (ULT 232): Identify the differences between a procedure and a function.
Package Concepts
Packages: Overview (ULT 312): Identify the features of packages.
Packages: Components (ULT 313): Identify the features of package components.
Packages: Advantages (ULT 314): Identify the advantages of using packages.
Developing PL/SQL Packages
Packages: Development Steps (ULT 321): Sequence the steps that are involved in developing a package.
Packages: Development Guidelines (ULT 322): Identify the rules that you follow when developing packages.
Creating PL/SQL Packages
CREATE PACKAGE: Clauses (ULT 331): Match the CREATE PACKAGE clauses with their descriptions.
CREATE PACKAGE: Using (ULT 332): Create a package specification by using iSQL*Plus.
CREATE PACKAGE BODY: Clauses (ULT 333): Match the CREATE PACKAGE BODY clauses with their descriptions.
CREATE PACKAGE BODY: Using (ULT 334): Create a package body by using iSQL*Plus.
Bodiless Packages: Declaring (ULT 335): Declare a bodiless package by using isqlplus..
Working with PL/SQL Packages
Package Constructs: Invoking (ULT 341): Invoke a package construct by using iSQL*Plus.
Global Variable: Referencing (ULT 342): Identify the methods used to reference a global variable within a package.
Package Constructs: Scope (ULT 343): Identify the scopes of the constructs within a package.
Packages: Removing (ULT 344): Drop a package by using iSQL*Plus.
Overloading Packages
Overloading: Concepts (ULT 412): Identify the features of overloaded subprograms.
Subprograms: Overloading (ULT 413): Overload a procedure by using iSQL*Plus.
Procedures and Functions in Packages
Forward Declaration: Guidelines (ULT 421): Identify the guidelines that you follow to call private procedures within a package.
Packaged Procedures: Forward Declaring (ULT 422): Forward declare a procedure in a package by using iSQL*Plus.
Package Function: Creating (ULT 423): Define a package function by using iSQL*Plus.
Package Function: In SQL Statements (ULT 424): Invoke a packaged function from SQL statements by using iSQL*Plus.
Index by Table of Records: Declaring (ULT 425): Declare the index by table of records parameter to the function declared within a package by using iSQL*Plus.
Package Functions: Restrictions (ULT 426): Identify the restrictions imposed on a function declared within a package.
Persistent State of Variables and Cursors
Persistent State: Package Variables (ULT 431): Identify the features of persistent package variables.
Package Cursors: Persistent State (ULT 432): Identify the effect of the persistent state of a package cursor on a dependent processor.
The anonymous block is an unnamed block. The anonymous block is used at the point where it is executed. The anonymous block has a DECLARE keyword instead of the header section of a named block.
The keyword BEGIN separates the declaration section from the executable section of the subprogram. It is mandatory for the keyword BEGIN to be included in a named subprogram.
Blocks are the units of a PL/SQL program. Blocks are either subprograms/named blocks or anonymous blocks.
A breakpoint only occurs in Procedure Builder. Breakpoints give you control of where and when each line is executed. This helps you to debug a subprogram.
The client-side area is the user's own development area that is most likely located on their local computer. Subprograms developed in this area can be edited, compiled, and deleted. However, if Procedure Builder is closed, any subprograms on the client-side are lost. Client-side subprograms can be debugged using Procedure Builder.
A cursor is a temporary memory area in the PL/SQL environment.
The declaration section occurs between the keywords IS and BEGIN. The declaration section is where local identifiers are stated. This section is optional.
The keyword EXCEPTION occurs between the executable section and the exceptions section of the subprogram. The exceptions are handled in the executable section. It is optional for the EXCEPTION section to be included.
The executable section occurs between the keywords BEGIN and EXECEPTION, or BEGIN and END;. The executable section is the body of the subprogram and comprises of SQL and PL/SQL statements. This section is mandatory.
A function is a type of subprogram. Functions take in parameters and can be invoked. Generally functions are used to compute and return a value. For example, the total_salary function has a parameter that is passed to the empno, the function retrieves the employee's monthly salary from the sal column in the EMP table using the employee's number. The function contains a RETURN value; line that for this example is RETURN (sal * 12);. This line occurs after the SQL and PL/SQL statements and exits the block.
A Graphical User Interface is an environment of icons, windows, and dialog boxes.
The header section occurs before the keyword IS. The header section is where the subprogram name, type (that is, procedure or function), and parameters are stated. This section is mandatory for named blocks and specifies how other PL/SQL blocks call the subprogram. In functions, this section contains the RETURN datatype.
The keyword IS separates the header section from the declaration section of the subprogram. It is mandatory for the keyword IS to be included.
A package logically groups related PL/SQL types, items, and subprograms into one container. A package consists of two sections, the specification and the body. The specification of the package acts as the interface, which is seen by other packages, while the body is where the actions of the package are written.
In the header section of a subprogram, parameters are stated. Parameters allow information to be passed between the subprogram and the calling program.
A procedure is a type of subprogram. Procedures take in parameters and can be invoked. Generally procedures are used to perform an action. For example, if the empno parameter was passed to the get_ename procedure, the procedure would retrieve the employee's name from the EMP table using the employee's number.
Procedure Builder is a GUI environment used to develop subprograms and has two areas of development. These are client-side and server-side. You can create subprograms in Procedure Builder using the Program Units node (client-side), or using the Stored Program Units node (server-side). The Object Navigator window shows your current location. The Program Unit editor allows you to edit, compile, and delete subprograms. The Stored Program Unit editor allows you to edit, compile, and drop subprograms.
The Program Unit editor is a window in Procedure Builder where you can edit a client-side program unit. To edit a server-side program unit, you use the Stored Program Unit editor.
The Server-side area is on the Oracle Database. The user can store subprograms on the server using SQL*Plus or Procedure Builder. By storing subprograms on the server, you can grant other users access to the subprograms.
SQL*Plus is a tool used to develop subprograms to store on the server. SQL*Plus uses the PL/SQL engine in the Oracle 8i server. You can type subprograms directly at the prompt or a script can be created and edited in the default text editor (for example, Notepad).
Subprograms are named PL/SQL blocks. There are two types, procedures and functions. Subprograms are also known as named blocks. Subprograms can take parameters and you can invoke them on the client or the server. You can store subprograms on the server.
|
