Subprocedures and Oracle PL/SQL Subprograms

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.