Home arrow Oracle arrow Subprocedures and Oracle PL/SQL Subprograms

Subprocedures and Oracle PL/SQL Subprograms

In this second part of a six-part series on Oracle PL/SQL subprograms, you will learn how Oracle implements subprograms, and how to create a stored procedure. This article is excerpted from chapter 36 of the book Beginning PHP and Oracle: From Novice to Professional, written by W. Jason Gilmore and Bob Bryla (Apress; ISBN: 1590597702).

  1. Subprocedures and Oracle PL/SQL Subprograms
  2. Creating a Stored Procedure
By: Apress Publishing
Rating: starstarstarstarstar / 1
March 07, 2011

print this article



How Oracle Implements Subprograms

Although the term stored procedures is commonly bandied about, Oracle actually implements three procedural variants, which are collectively referred to as subprograms:

  1. Stored procedures: Stored procedures support execution of SQL statements such asSELECT,INSERT,UPDATE, andDELETE. They also can set parameters that can be referenced later from outside of the procedure.
  2. Stored functions: Stored functions support execution only of theSELECTstatement, accept only input parameters, and must return one and only one value. Furthermore, you can invoke a stored function directly into a SQL command just like you might do with standard Oracle functions such asCOUNT()andTO_DATE()
  3. Anonymous blocks: Anonymous blocks are much like stored procedures and functions except that they cannot be stored in the database and referenced directly because they are, as the name implies, anonymous. They do not have a name or parameters; you either run them in the SQL Commands or SQL Developer GUI application, or you can embed them within a stored procedure or function to isolate functionality.

Generally speaking, you use subprograms when you need to work with data found in the database, perhaps to retrieve rows or insert, update, and delete values; whereas you use stored functions to manipulate that data or perform special calculations. In fact, the syntax presented throughout this chapter is practically identical for both variations, except that the term procedure is swapped out for function. For example, the commandDROP PROCEDURE procedure_name  is used to delete an existing stored procedure, whileDROP FUNCTION function_name is used to delete an existing stored function.

>>> More Oracle Articles          >>> More By Apress Publishing

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates


Dev Shed Tutorial Topics: