This topic includes the following sections:
A stored procedure is a pre-compiled, ready-to-execute command stored in a database and managed as a database object. Stored procedures run as stand-alone programs on the database server and are invoked by internal or external requests. Internal requests originate within the database and are invoked by other stored procedures, triggers, and agents. External requests originate from remote client applications, local server applications, and other database systems.
One of the primary advantages of stored procedures is that they enable you to move application code and business logic to the server. There are other advantages, and there are some disadvantages.
Advantages:
- Less redundancy. A stored procedure is available to all applications that access a database. This enables you to store business logic in one place—the database—rather than in each application.
- Consistency. A stored procedure provides a single process for all users, eliminating inconsistencies that are likely to occur when logic is coded separately in each application that accesses the database.
- Maintainability. Stored procedures recognize changes to database schemas. Although stored procedures are pre-compiled, stored procedures verify column definitions when they are run. If there are changes, these changes and any necessary data type conversions are made at runtime.
- Manageable, well-defined logic modules. Stored procedures are modified independently of the application source code and perform a single task.
- Faster execution. It’s often faster to run stored procedures than to run lengthy or repetitive SQL operations. See related disadvantage below.
- Reduced network traffic. An operation that would otherwise send hundreds of lines of SQL code over a network can be made into a stored procedure, which requires the network to handle only one statement: the statement that invokes the stored procedure.
- Security. A user can be given permission to execute a stored procedure even if the user doesn’t have permission to execute the procedure’s statements directly. You can create a very secure and extensible environment by creating applications that use only stored procedures.
- Additional prefetch information. With a single database call, client applications can retrieve the result of an operation as well as the number of rows in the result set.
Disadvantages:
- Lack of portability. Stored procedures are not portable from one brand of database to another. For example, SQL Server and Sybase cannot run stored procedures created for Oracle, and Oracle cannot run stored procedures created for SQL Server or Sybase.
- Potential for reduced performance. Overburdening the server with stored procedure processing, in addition to standard RDBMS tasks, may degrade database performance.
- Difficult debugging. Nested stored procedures (stored procedures called by other stored procedures) are difficult to debug. Stored procedures invoked from event-driven triggers are even more difficult to debug.
- Reduced stability. If a stored procedure that was installed as an external DLL and run within the address space of the database engine fails, the server may also fail.
Invoking a stored procedure
Use one of the following methods to invoke a stored procedure in an SQL Connection program:
- To fetch data from a SQL Server result set, use the %SSC_OPEN / %SSC_MOVE method illustrated in the example programs stp_sqlsrv1.dbl and stp_sqlsrv2.dbl. Note that this method works only with SQL Server, and you must include the EXECUTE command in the SQL statement that invokes the stored procedure.
- If the stored procedure requires parameters, use %SSC_EXECIO. This works with any database supported by SQL Connection, but it cannot be used to retrieve data from a SQL Server result set. For SQL Server, you do not need to include the EXECUTE command. You can just pass the name of the stored procedure followed by parameters. See the example programs stp_ora.dbl and stp_odbc.dbl.
- For other cases, use %SSC_EXECIO or %SSC_EXECUTE. These work with any database supported by SQL Connection.
For an illustration of the function call flow for the latter two methods, see Stored Procedure. For information on the example programs, see Sample programs.
Notes on Oracle stored procedures
For Oracle databases, stored procedures are called subprograms. There are two types of Oracle subprogram, both of which are written in PL/SQL, a procedural language extension of SQL. These two types are procedures and functions. Procedures and functions are similar. Both are typically written to perform a single task, but functions return a value, so you can use them within SQL expressions. This includes WHERE clauses in SQL statements and control structures within PL/SQL. However, to use a procedure, you must pass the procedure by name (in an %SSC_OPEN call), and you must use the %SSC_EXECIO function. For an example, see stp_ora.dbl, a file included in the Connectivity Series distribution.
In essence, subprograms are named PL/SQL blocks that have been compiled into p-code and stored in an Oracle database. Once the p-code is in the database, it is ready to run. Subprograms may take and return user-supplied parameters, and any application connected to a database can access the database’s subprograms by name. When an application accesses a subprogram, the subprogram is passed to the PL/SQL engine, which maintains a single copy of the subprogram for all applications to use.
Subprograms are created and modified with the CREATE OR REPLACE PROCEDURE statement. Packages are created and modified with the CREATE OR REPLACE PACKAGE statement.
Refer to Oracle documentation to learn more about creating packages and writing PL/SQL subprograms.
For information on invoking stored procedures, see Invoking a stored procedure.
Using packages to group subprograms
Related Oracle subprograms can be grouped into packages. Packages are named PL/SQL modules that provide a convenient method for grouping logically related components (types, items, and subprograms). Packages also enable you to create public and private components. Public components can be called from and shared with internal and external callers. Private components are available only to components within the same package.
If a subprogram is part of a package, the PL/SQL engine loads the entire package the first time the subprogram is used. Thereafter, calls to any of the components in the package are processed immediately and without additional overhead. Public variables and cursors persist for the duration of a session and remain unaffected by transactions.
Notes on SQL Server stored procedures
SQL Server refers to its version of SQL as Transact-SQL (T-SQL). T-SQL includes not only standard SQL, but also procedural language extensions that enable you to create user-defined stored procedures. In SQL Server, stored procedures are named T-SQL blocks that may take and return user-supplied parameters. Stored procedures are parsed, optimized, and then saved in the database. When a stored procedure is called, the T-SQL processor loads the procedure, runs the procedure, and then retains the executable image. Subsequent calls to the stored procedure use this cached in-memory version, reducing system overhead and improving performance.
In addition to user-defined stored procedures, SQL Server includes two other types of stored procedure: system stored procedures and extended stored procedures. System stored procedures are used to perform many administrative functions. These procedures are created and stored in the master database and their names begin with the sp_ prefix. Extended stored procedures enable you to create external routines in programming languages that enable you to create dynamic-link libraries (DLLs). Extended stored procedures are run and appear to end users as user-defined stored procedures.
SQL Server stored procedures are created with the T-SQL CREATE PROCEDURE statement and modified with the ALTER PROCEDURE statement. See Microsoft documentation for information on T-SQL and writing stored procedures.
For information on invoking stored procedures, see Invoking a stored procedure.
