Skip to Main Content
Oracle PL/SQL Language Pocket Reference, 4th Edition
book

Oracle PL/SQL Language Pocket Reference, 4th Edition

by Steven Feuerstein, Bill Pribyl, Chip Dawes
October 2007
Intermediate to advanced content levelIntermediate to advanced
178 pages
2h 50m
English
O'Reilly Media, Inc.
Content preview from Oracle PL/SQL Language Pocket Reference, 4th Edition

Stored Procedures and Functions

PL/SQL allows you to create a variety of named program units, or containers, for your code. These include:

Procedure

A program that executes one or more statements

Function

A program that returns a value

Trigger

A program that executes in response to database changes

Package

A container for procedures, functions, and data structures

Object type

Oracle’s version of an object-oriented class; object types can contain member procedures and functions

The following sections describe stored procedures and functions. Later sections describe triggers, packages, and object types.

Procedures

Procedures are program units that execute one or more statements and can receive or return zero or more values through their parameter lists. The syntax of a procedure is:

CREATE [OR REPLACE] PROCEDURE name
   [ (parameter
 [,parameter]) ]
   [AUTHID { CURRENT_USER | DEFINER } ]
{ IS | AS }
   declaration_section
BEGIN
   executable_section
[EXCEPTION
   exception_section]
END [name];

Inside a PL/SQL executable section, a procedure is called as a standalone executable statement:

apply_discount(new_company_id, 0.15);

Many execution environments, such as SQL*Plus, also support ANSI SQL’s CALL syntax:

CALL apply_discount(new_company_id, 0.15);

However, SQL*Plus programmers commonly invoke procedures with the SQL*Plus EXECUTE command:

EXECUTE apply_discount(new_company_id, 0.15);

or the equivalent anonymous block:

BEGIN
   apply_discount(new_company_id, 0.15);
END;

Functions

Functions are program units that execute zero ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Oracle PL/SQL Language Pocket Reference, 5th Edition

Oracle PL/SQL Language Pocket Reference, 5th Edition

Steven Feuerstein, Bill Pribyl, Chip Dawes

Publisher Resources

ISBN: 9780596514044Errata Page