Skip to Main Content
Oracle PL/SQL Language Pocket Reference
book

Oracle PL/SQL Language Pocket Reference

by Bill Pribyl, Steven Feuerstein, Chip Dawes
April 1999
Intermediate to advanced content levelIntermediate to advanced
99 pages
2h 4m
English
O'Reilly Media, Inc.
Content preview from Oracle PL/SQL Language Pocket Reference

Named Program Units

The PL/SQL programming language allows you to create a variety of named program units (containers for code). They include:

Procedure

A program that executes one or more statements

Function

A program that returns a value

Package

A container for procedures, functions, and data structures

Triggers

Programs that execute in response to database changes

Object type

Oracle8’s version of a SQL3 named row type; object types can contain member procedures and functions

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 ] -- Oracle8i
   [DETERMINISTIC]                   -- Oracle8i
IS | AS
   declaration_section
BEGIN
   executable_section
[EXCEPTION
   exception_section]
END [name];

A procedure is called as a standalone executable PL/SQL statement:

apply_discount(new_company_id, 0.15) --15% discount

Functions

Functions are program units that execute one or more statements and return a value through the RETURN clause. Functions can also receive or return zero or more values through their parameter lists. The syntax of a function is:

CREATE [OR REPLACE] FUNCTION name [ (parameter [,parameter]) ] RETURN return_datatype [AUTHID CURRENT_USER | DEFINER ] -- Oracle8i [DETERMINISTIC] -- Oracle8i [PARALLEL_ENABLE] -- Oracle8i IS | AS [declaration_section] BEGIN executable_section [EXCEPTION exception_section] ...
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
Pro Oracle Spatial for Oracle Database 11g

Pro Oracle Spatial for Oracle Database 11g

Albert Godfrind, Euro Beinat, Ravi Kothuri

Publisher Resources

ISBN: 1565924576Catalog PageErrata