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 cannot run stored procedures created for Oracle, and Oracle cannot run stored procedures created for SQL Server.
- 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. This method works only with SQL Server. You can include the EXECUTE command in the SQL statement that invokes the stored procedure (as shown in stp_sqlsrv1.dbl).
- If the stored procedure requires parameters, use %SSC_EXECIO. 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, stp_mysql.dbl, and stp_odbc.dbl. %SSC_EXECIO works with any database supported by SQL Connection, but it cannot be used to retrieve data from a SQL Server result set, only data returned by SQL Server out or inout parameters.
For Oracle and MySQL, the program can determine if data was actually returned by %SSC_EXECIO by using the row_count argument of %SSC_GETEMSG. See stp_mysql.dbl for an example of this.
- For other cases, use %SSC_EXECUTE. This works 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 SQL Connection 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 the sample program stp_ora.dbl. (See SQL Connection sample programs.)
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 your Oracle documentation to learn more about creating packages and writing PL/SQL subprograms.
See also Invoking a stored procedure, above.
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.
See also Invoking a stored procedure, above.
