SQL - Stored procedures

> (Data|State) Management and Processing > (Data Type|Data Structure) > (Relation|Table) - Tabular data > Structured Query Language (SQL)

1 - About

A stored procedure is a compiled SQL program, consisting of one or more SQL statements, which resides and runs on the target database.

A Stored Procedure transformation is an important tool for populating and maintaining databases. Database administrators create stored procedures to automate tasks that are too complicated for standard SQL statements.

A stored procedure is a precompiled collection of:

  • Transact-SQL (SQL Server),
  • PL-SQL (Oracle Database)
  • or other database procedural statements and optional flow control statements, similar to an executable script.

Stored procedures are stored and run within the database. You can run a stored procedure with the EXECUTE SQL statement in a database client tool, just as you can run SQL statements.

Unlike standard SQL, however, stored procedures allow:

  • user-defined variables,
  • conditional statements,
  • and other powerful programming features.

Not all databases support stored procedures, and stored procedure syntax varies depending on the database.

You might use stored procedures to complete the following tasks:

  • Check the status of a target database before loading data into it.
  • Determine if enough space exists in a database.
  • Perform a specialized calculation.
  • Drop and recreate indexes.

Database developers and programmers use stored procedures for various tasks within databases, since stored procedures allow greater flexibility than SQL statements.

Stored procedures also provide:

  • error handling
  • and logging necessary for critical tasks.

Developers create stored procedures in the database using the client tools provided with the database.

Advertising

3 - Benefits

Stored procedures offer the following benefits:

  • They encapsulate code. The database operation appears once, in the stored procedure, and not multiple times throughout the application source. This improves debugging as well as maintainability.
  • Changes to the database schema affect the source code in only one place, the stored procedure. Any schema changes become a database administration task rather than code revision.
  • Since the stored procedures reside on the server, you can set tighter security restrictions. This saves more trusted permissions for the well-protected stored procedure themselves.
  • Because stored procedures are compiled and stored outside the client application, they can use more sensitive variables within the SQL syntax, such as passwords or personal data.
  • Using stored procedures reduces network traffic.
data/type/relation/sql/stored_procedure.txt · Last modified: 2017/09/13 16:15 by gerardnico