[WEBINAR] The New Normal: Dealing with the Reality of an Unsecure World

Stored Procedure

Definition - What does Stored Procedure mean?

A stored procedure is a subroutine available to connected relational database system applications. Stored procedures must be called or invoked, as they are sets of SQL and programming commands that perform very specific functions. Most major relational database systems (e.g., SQL Server, Oracle, MySQL, Postgres and others) provide support for stored procedures.

This term is also known as proc or storedproc.

Techopedia explains Stored Procedure

Stored procedures are used when an application needs to perform a complex task using relational database information. An example might be a loan loan application to determine a customer’s repayment ability and creditworthiness. To check the customer’s repayment ability, the loan officer compares the customer’s average monthly income to his monthly account withdrawal sum over a 24-month period. To verify creditworthiness, the loan officer submits the customer’s ID or social security number to a credit reporting website.

Both actions above are complex and difficult to achieve using basic SQL commands. In addition, the customer loan approval process may be performed at varying times for different customers (i.e., the same action is repeated several times), but different customer information is associated with each action.

The above situation is an ideal example of when storedprocs should be used--i.e., when performing a complex action using a combination of SQL, Procedural Language/Structured Query Language (PL/SQL) or external programming language, e.g., Java or C++. Second, the same action is performed repeatedly, and the only changes are the parameters or data to be processed.

Stored procedures usually provide a performance benefit over writing application code, for the following two reasons:

  • They do not incur extra inter-program communication between the database and external application.
  • Do not need to be compiled and executed for each instance, as storedprocs are compiled only once.

Stored procedures are stored as part of the database’s data dictionary, rather than the application that references the database. When storedprocs call other storedprocs, this is known as a setup of nested stored procedures.

Techopedia Deals

Connect with us

Techopedia on Linkedin
Techopedia on Linkedin
"Techopedia" on Twitter

Sign up for Techopedia's Free Newsletter!

Email Newsletter

Join thousands of others with our weekly newsletter

Free Whitepaper: The Path to Hybrid Cloud
Free Whitepaper: The Path to Hybrid Cloud:
The Path to Hybrid Cloud: Intelligent Bursting To Amazon Web Services & Microsoft Azure
Free E-Book: Public Cloud Guide
Free E-Book: Public Cloud Guide:
This white paper is for leaders of Operations, Engineering, or Infrastructure teams who are creating or executing an IT roadmap.
Free Tool: Virtual Health Monitor
Free Tool: Virtual Health Monitor:
Virtual Health Monitor is a free virtualization monitoring and reporting tool for VMware, Hyper-V, RHEV, and XenServer environments.
Free 30 Day Trial – Turbonomic
Free 30 Day Trial – Turbonomic:
Turbonomic delivers an autonomic platform where virtual and cloud environments self-manage in real-time to assure application performance.