Stored Procedures and Functions

Stored procedures and functions (subprograms) can be compiled and stored in an Oracle Database XE, ready to be executed. Once compiled, it is a schema object known as a stored procedure or stored function, which can be referenced or called any number of times by multiple applications connected to Oracle Database XE. Both stored procedures and functions can accept parameters when they are executed (called). To execute a stored procedure or function, you only need to include its object name.

Procedures and functions that are created outside of a package are called stored or standalone subprograms. Procedures and functions defined within a package are known as packaged subprograms. Procedures and functions nested inside other subprograms or within a PL/SQL block are known as local subprograms, which cannot be referenced by other applications and exist only inside of the enclosing block. For information about subprograms in PL/SQL blocks, see "Using Local PL/SQL Procedures and Functions in PL/SQL Blocks".

Stored procedures and functions are the key to modular, reusable PL/SQL code. Wherever you might use a JAR file in Java, a module in Perl, a shared library in C++, or a DLL in Visual Basic, you can use PL/SQL stored procedures, stored functions, and packages.

You can call stored procedures or functions from a database trigger, another stored subprogram, or interactively from SQL Command Line (SQL*Plus). You can also configure a Web server so that the HTML for a Web page is generated by a stored subprogram, making it simple to provide a Web interface for data entry and report generation.

Procedures and functions are stored in a compact compiled form. When called, they are loaded and processed immediately. Subprograms take advantage of shared memory, so that only one copy of a subprogram is loaded into memory for execution by multiple users.