Running stored procedures from SQL Connection

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:

Disadvantages:

Invoking a stored procedure

Use one of the following methods to invoke a stored procedure in an SQL Connection program:

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 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.