The CREATE PROCEDURE Statement
|
The CREATE PROCEDURE StatementThe CREATE PROCEDURE statement defines a procedure in the database. The clauses define the name and parameters as well as the procedure body consisting of one or more SQL PL statements. Figure 2.1 describes the syntax of the CREATE PROCEDURE statement and the clauses that apply to SQL procedures. Figure 2.1. CREATE PROCEDURE statement syntax for DB2 UDB LUW.>>-CREATE PROCEDURE--procedure-name-----------------------------> >--+----------------------------------------------------+--*----> '-(--+------------------------------------------+--)-' | .-,------------------------------------. | | V .-IN----. | | '---+-------+--parameter-name--data-type-+-' +-OUT---+ '-INOUT-' >--+--------------------------+--*-------------------------------> '-SPECIFIC--specific-name--' .-DYNAMIC RESULT SETS 0--------. .-MODIFIES SQL DATA-. >--+------------------------------+--*--+-------------------+---> '-DYNAMIC RESULT SETS--integer-' +-CONTAINS SQL------+ '-READS SQL DATA----' .-NOT DETERMINISTIC-. .-CALLED ON NULL INPUT-. >--*--+-------------------+--*--+----------------------+--*-----> '-DETERMINISTIC-----' .-INHERIT SPECIAL REGISTERS-. .-OLD SAVEPOINT LEVEL-. >--+---------------------------+--*--+---------------------+----> '-NEW SAVEPOINT LEVEL-' .-LANGUAGE SQL-. .-EXTERNAL ACTION----. >--*--+--------------+--*--+--------------------+--*------------> '-NO EXTERNAL ACTION-' >--+------------------------------+--*--------------------------> '-PARAMETER CCSID--+-ASCII---+-' '-UNICODE-' >--| SQL-procedure-body |-------------------------------------->< SQL-procedure-body: |---SQL-procedure-statement-------------------------------------| Note We discuss the differences in the CREATE PROCEDURE statement for iSeries and zSeries platforms at the end of this chapter. Many clauses are available for the CREATE PROCEDURE statement; however, it is often appropriate to just use the defaults. When you issue the CREATE PROCEDURE statement, DB2 defines the procedure in the database and makes entries in the catalog tables to record the existence of the procedure. Procedure NameThe procedure name specifies the procedure being defined. The name is an SQL identifier that can be a maximum of 128 characters. It must begin with a letter and may be followed by additional letters, digits, or the underscore character "_". These are examples of valid procedure names: UPDATE_EMPLOYEE_SALARY READEMP These examples are of unqualified procedure names. When a procedure name is unqualified, the procedure schema is determined by the CURRENT SCHEMA special register. The DB2 special register will be discussed later in this chapter. By default, the CURRENT SCHEMA is the authorization ID of the current user. Procedure names may also be qualified by a schema name explicitly. These are examples of valid qualified procedure names: HRAPP.UPDATE_EMPLOYEE_SALARY DB2ADMIN.READEMP Similar to the procedure name, the schema name is an SQL identifier with a maximum of 128 characters or 10 characters on iSeries. The schema name cannot begin with SYS. Note On iSeries, the schema name actually can start with SYS, but as a best practice you should not start a schema name with SYS or Q. These types of schema names are typically used for system schemas. The qualified procedure name does not necessarily need to be unique in the database. The combination of qualified procedure name and number of parameters (procedure signature), however, must uniquely identify a procedure. This topic will be discussed in more detail in the following section. The SPECIFIC clause is important when there is a requirement to define procedures with the same name but with a different number of parameters. To fully understand the purpose of this clause, we must discuss parameters. ParametersThe parameters for a procedure are defined in the CREATE PROCEDURE statement. The definition of each parameter consists of three parts:
The list of parameters is enclosed in parentheses "( )", and each parameter definition is delimited by a comma ",". Figure 2.2 is an example of a partial CREATE PROCEDURE statement that defines the procedure and its parameters. Figure 2.2. Defining procedure parameters.CREATE PROCEDURE update_employee_salary ( IN P_EMPID INTEGER , IN P_PERCENTINCR DECIMAL(4,2) , INOUT P_EMPCOUNT INTEGER , OUT P_UPDATED_SALARY DECIMAL(5,2)) . . . A procedure may be defined without any parameters, and the parentheses can be omitted, as in this example: CREATE PROCEDURE increase_salary LANGUAGE SQL BEGIN . . . To allow back-level DB2 support, parentheses may be coded in the CREATE PROCEDURE statement. CREATE PROCEDURE increase_salary() LANGUAGE SQL BEGIN . . . On LUW and iSeries, it is possible to define multiple procedures with the same qualified name but different numbers of parameters. This is called overloading, and the procedures are referred to as overloaded procedures. Figures 2.3 and 2.4 show examples of overloaded procedures. zSeries does not support overloaded procedures. Therefore, examples run in zSeries have to be created with different procedure names. Figure 2.3. Procedure sum with three parameters.CREATE PROCEDURE sum( IN p_a INTEGER , IN p_b INTEGER , OUT p_s INTEGER) LANGUAGE SQL SPECIFIC sum_ab -- applies to LUW and iSeries -- WLM ENVIRONMENT <env> -- applies to zSeries BEGIN SET p_s = p_a + p_b; END Figure 2.4. Procedure sum with four parameters.CREATE PROCEDURE sum( IN p_a INTEGER , IN p_b INTEGER , IN p_c INTEGER , OUT p_s INTEGER) LANGUAGE SQL SPECIFIC sum_abc -- applies to LUW and iSeries -- WLM ENVIRONMENT <env> -- applies to zSeries BEGIN SET p_s = p_a + p_b + p_c; END Note DB2 does not allow you to define two procedures with the same schema name, same procedure name, and same number of parameters even if the parameters are of different types. However, DB2 (including zSeries) does support User-Defined Functions (UDFs) overloaded with the same schema, same name, and same number of parameters, given that the data types of the parameters are different. For more information about UDFs, refer to Chapter 9, "User-Defined Functions and Triggers". In the examples, two procedures have the same name, sum. The first procedure has three parameters, and the second has four parameters. When sum is called, DB2 determines which version of the procedure to execute based on the number of parameters. Note that each procedure is defined with a unique specific name. Specific names will be discussed in the next section. Note To invoke SQL procedures, use the Command-Line Processor (CLP) for LUW and zSeries or the iSeries Navigator. The following statement can be used to invoke the SQL procedure: CALL sum(100,200,?) This call results in the sum procedure in Figure 2.3 to be executed because there are three parameters. Note that because the third parameter is an output parameter, a "?" must be specified in its place. Executing the following statement invokes the sum procedure in Figure 2.4 because there are four parameters. CALL sum(100,200,300,?) If you attempt to call a procedure where there is no procedure defined in the database with the same number of parameters, an error occurs, as in this example: CALL sum(100,200,300,400,?) This call fails because a procedure named sum with five parameters does not exist. Specific NameSPECIFIC is an optional clause that defines a unique name for a procedure. Specific names are particularly useful when there are multiple procedures defined with the same name but have a different number of parameters (also known as overloaded procedures, as discussed in the previous section). In this case, each procedure would be given a different specific name which would be used to drop or comment on the stored procedure. Attempting to drop an overloaded procedure using only the procedure name would result in ambiguity and error. Tip We recommend that you use a specific name for a procedure for easier management. However, this option is not supported on zSeries for SQL procedures because overloaded procedures are not supported. The following example illustrates the use of SPECIFIC name when two procedures with the same name are defined. Consider the two sum procedures defined in Figures 2.3 and 2.4. To drop the procedure sum, issue the following DROP PROCEDURE statement: DROP PROCEDURE sum This statement fails with SQLCODE -476 (SQLSTATE 42725) because the procedure is ambiguous. DB2 cannot determine which of the two procedures called sum should be dropped. To drop a particular version of the sum procedure, you must either specify the procedure parameters with the DROP PROCEDURE statement or use the DROP SPECIFIC PROCEDURE statement. These valid statements drop the procedure: DROP PROCEDURE sum(INTEGER,INTEGER,INTEGER) DROP SPECIFIC PROCEDURE sum_ab Note On zSeries, these two DROP statements are not supported. By using DROP SPECIFIC PROCEDURE, DB2 knows that it should drop the procedure with the specific name sum_ab. The specific name can also be used with the COMMENT ON statement, as in this example: COMMENT ON SPECIFIC PROCEDURE sum_abc IS 'THIS IS THE 3 PARM VERSION OF THE PROCEDURE' The specific name is an SQL identifier with a maximum length of 18 characters on LUW and 128 characters on iSeries. The name can be unqualified or qualified by a schema name. If it is qualified, it must use the same schema name as the procedure name. The specific name can be the same name as its procedure name. The qualified specific name must be unique among specific procedure names. In LUW, if the specific name is not explicitly specified when creating a procedure, DB2 generates a unique name for the procedure. The generated unique name consists of SQL and a character timestamp: SQLyymmddhhmmsshhn On iSeries, if the specific name is not specified, DB2 uses the procedure name as the specific name. If a procedure already exists with that specific name, a unique name is generated using a portion of the procedure name and a sequence number. DYNAMIC RESULT SETSThe DYNAMIC RESULT SETS clause specifies the maximum number of result sets you are returning. Handling result sets is explained in detail in Chapter 5, "Understanding and Using Cursors and Result Sets," and Chapter 8, "Nested SQL Procedures." CONTAINS SQL, READS SQL DATA, MODIFIES SQL DATAThe SQL data access indication clause restricts the type of SQL statements that can be executed by the procedure. The default, MODIFIES SQL DATA, is the least restrictive and indicates that any supported SQL statements can be executed. When CONTAINS SQL is specified, then only statements that do not read or modify data are allowed in the procedure. Examples of such statements are PREPARE, the SET special register, and SQL control statements. READS SQL DATA can be specified if the procedure contains only statements that do not modify SQL data. Refer to the SQL Reference of the corresponding platform for statements allowed in the SQL procedure for each access indicator. DETERMINISTIC or NOT DETERMINISTICThis clause allows you to specify the procedure as DETERMINISTIC if it returns the same results for each invocation of identical input parameters. You can also specify NOT DETERMINISTIC, the default, if the results depend on the input values and/or other values which may change, such as the current date or time. Identifying a procedure as DETERMINISTIC allows DB2 to perform additional optimizations to improve performance because DB2 can just call it once, cache the result, and reuse it. CALLED ON NULL INPUTThis clause indicates that the procedure will always be called even if its input parameters are null. This behavior is the default, and is the only value that can be specified. This clause is optional and is usually left out. INHERIT SPECIAL REGISTERSSpecial registers are memory registers that allow DB2 to provide information to an application about its environment. Refer to the section "DB2 Special Registers" for more information. INHERIT SPECIAL REGISTERS is an optional clause and indicates that updateable special registers in the procedure will inherit their initial values from the environment of the invoking statement. Special register inheritance is the default behavior on all platforms. Note On zSeries, some special registers inherit their initial values from different sources. Refer to the zSeries SQL Reference for details. OLD SAVEPOINT LEVEL, NEW SAVEPOINT LEVELThis clause is available only in LUW and iSeries. A save point level refers to the scope of reference for any save point related statements. All save point names in the same save point level must be unique. The OLD SAVEPOINT LEVEL means that any SAVEPOINT statements issued within the procedure are created in the same save point level as the caller of the procedure. Thus, any save point created inside the stored procedure must not have the same name as those defined at the caller. This is the default behavior. The NEW SAVEPOINT LEVEL, on the other hand, creates a new save point level when the stored procedure is called. Any save points set within the procedure are created at a level that is nested deeper than the level at which this procedure was invoked. Therefore, names of any new save point set within the procedure will not conflict with any existing save points. You can also make stored procedure calls from inline SQL such as SQL functions, triggers, and stand-alone code. Such a procedure will be executed as if it were created in the NEW SAVEPOINT LEVEL mode even if the OLD SAVEPOINT LEVEL was specified. Save points are discussed in more detail in Chapter 10, "Leveraging DB2 Application Development Features." LANGUAGE SQLLANGUAGE SQL identifies this procedure as an SQL procedure, and indicates that the body of the procedure will be specified in the CREATE PROCEDURE statement body. LANGUAGE SQL is an optional clause for LUW. For iSeries and zSeries, LANGUAGE SQL must be specified. Furthermore, on iSeries, it must be specified as the first clause. The LANGUAGE keyword is required when creating procedures in other languages such as Java or C. Note To increase portability, always use the LANGUAGE SQL clause and ensure it is the first clause. EXTERNAL ACTION or NO EXTERNAL ACTIONThis clause is only available on LUW. If the SQL procedure takes some action that changes the state of an object not managed by DB2, specify the EXTERNAL ACTION. Otherwise, use NO EXTERNAL ACTION so that DB2 can use certain optimizations that assume the procedure has no external impact. PARAMETER CCSIDCCSID stands for Coded Character Set ID. This clause specifies the encoding scheme used for all string data passed into and out of the stored procedure for LUW and zSeries. Possible values are ASCII, UNICODE, and EBCDIC (for zSeries only). On iSeries, the specification of the CCSID is at a more granular level. You specify the CCSID for each string parameter of the procedure (CHAR, VARCHAR, CLOB, GRAPHIC, VARGRAPHIC, and DBCLOB). There are numerous options for specifying CCSID on iSeries. Refer to the iSeries SQL Reference for a listing of valid CCSIDs and the allowed conversions between these CCSIDs. On zSeries, the CCSID can be specified at either the parameter level, the subsystem level in the field DEF ENCODING SCHEME on installation panel DSNTIPF, or at the SQL procedure level. The default is the value specified at the parameter level or the subsystem level. If specified at both the parameter level and the SQL procedure, they must be the same CCSID value for all parameters. Tip In practice, this clause is rarely used. It can be used to overwrite the default character set as specified by the code page of the database. SQL Procedure BodyFor SQL procedures, the logic of the procedure is contained in the SQL procedure body of the CREATE PROCEDURE statement. The SQL procedure body can consist of a single SQL statement or several SQL statements in the form of a compound SQL statement. The next section explains the details of writing the SQL procedure body. |
|
EAN: 2147483647
Pages: 205