The ALTER PROCEDURE data description statement modifies the definition of a procedure in the dictionary. Using the ALTER PROCEDURE statement, you can:
The ability to change attributes other than language, external name, and the maximum number of dynamic result sets is a CA IDMS extension of the SQL standard.
To issue an ALTER PROCEDURE statement, you must either own or hold the ALTER privilege on the procedure named in the statement.
►►─── ALTER PROCEDURE ─┬──────────────────────┬─ procedure-identifier ────────► └─── schema-name. ─────┘ ►─┬─ ADD parameter-definition ────────────────────────────┬──────────────────►◄ │ ┌────────── , ───────────┐ │ ├─ ADD ( ─▼─ parameter-definition ─┴─ ) ────────────────┤ ├─ EXTERNAL NAME external-routine-name ─────────────────┤ ├─ ESTIMATED ROWS row-count ────────────────────────────┤ ├─ ESTIMATED IOS io-count ──────────────────────────────┤ ├─ LOCAL WORK AREA local-stge-size ─────────────────────┤ ├─ GLOBAL WORK AREA global-stge-size ─┬────────────────┬┤ │ └─ KEY ┬ key-ID ┬┘│ │ └─ NULL ─┘ │ ├─ USER MODE ───────────────────────────────────────────┤ ├─ SYSTEM MODE ─────────────────────────────────────────┤ ├─ PROTOCOL ───────────────────────┬─── IDMS ──────┬────┤ │ └─── ADS ───────┘ │ ├─ language-clause ─────────────────────────────────────┤ ├─ TIMESTAMP timestamp-value ───────────────────────────┤ ├─ DEFAULT DATABASE ───────────────┬─── NULL ──────┬────┤ │ └─── CURRENT ───┘ │ ├─ TRANSACTION SHARING ─────────────┬── ON ────────┬────┤ │ ├── OFF ───────┤ │ │ └── DEFAULT ───┘ │ └─ DYNAMIC RESULT SETS maximum-dynamic-result-sets ─────┘
Expansion of parameter-definition
►►─── parameter-name ── data-type ─┬────────────────┬─────────────────────────►◄ └─ WITH DEFAULT ─┘
Expansion of language-clause
►►─── LANGUAGE ────────────────────┬─ ADS ──────────┬──────────────────────────►◄ ├─ ASSEMBLER ────┤ ├─ COBOL ────────┤ ├─ PLI ──────────┤ └─ SQL ──────────┘
Specifies the name of the procedure being modified. Procedure-identifier must identify a procedure defined in the dictionary.
Identifies the schema associated with the named procedure. If you do not specify a schema-name it defaults to:
Defines one or more new parameters to be associated with the procedure. New parameters are added, in the order specified, after the last existing parameter.
For a description of parameter-definition, see CREATE PROCEDURE. Descriptions for the expansion parameters are located at the end of this section.
Specifies the one- to eight-character name of the program which CA IDMS calls to process references to the procedure.
Specifies an integer value, in the range of 0 through 2,147,483,647, which represents the average number of rows that the procedure returns for a given set of input parameters.
Specifies an integer value, in the range of 0 through 2,147,483,647, which represents the average number of disk accesses that the procedure generates for a given set of input parameters.
Specifies an integer, in the range of 0 through 32767, which represents the size, in bytes, of a local storage area that CA IDMS allocates at runtime and passes to the procedure on each invocation.
Specifies an integer, in the range of 0 through 32767, which represents the size, in bytes, of a global storage area that CA IDMS allocates at runtime and passes to the procedure on each invocation.
CA IDMS allocates a global storage area once within a transaction and retains it until the transaction terminates.
Specifies the one- to four-character identifier for the global storage area. CA IDMS passes the same piece of global storage within a transaction to all routines that have the same global storage key.
If you do not specify a storage key, its value remains unchanged. To remove a storage key, specify NULL as the key.
Specifies that the procedure should execute as a user-mode application program within CA IDMS. Do not specify user mode for procedures specified with protocol ADS, such as is the case with procedures written as CA ADS mapless dialogs or written in SQL.
Specifies that the procedure should execute as a system mode application program. To execute as a system mode application, the program must be fully reentrant and be written in either:
Specifies the environment.
Use IDMS for SQL-invoked functions that are written in COBOL, PL/I, or Assembler.
Use ADS for SQL-invoked functions that are written in SQL or CA ADS. The name of the dialog that will be loaded and run when the SQL function is invoked is given by the external-routine-name in the EXTERNAL NAME clause. Setting the protocol to ADS, requires the function to have its mode set to system.
Specifies the programming language of the procedure.
Specifies the value of the synchronization stamp to be assigned to the procedure. Timestamp-value must be a valid external representation of a timestamp.
Specifies whether a default database should be established for database sessions started by the procedure.
Specifies that no default database should be established.
Specifies that the database to which the SQL session is connected should become the default for any database session started by the procedure.
Specifies whether transaction sharing should be enabled for database sessions started by the procedure. If transaction sharing is enabled for a procedure's database session, it will share the current SQL session's transaction.
Specifies that transaction sharing should be enabled.
Specifies that transaction sharing should be disabled.
Specifies that the transaction sharing setting that is in effect when the procedure is invoked should be retained.
Defines the maximum number of result sets that a procedure invocation can return to its caller. A result set is a sequence of rows specified by a cursor-specification, created by the opening of a cursor and ranged over that cursor.
Defines an integer in the range 0-32767 specifying the maximum number of result sets a procedure can return.
Parameters for Expansion of parameter-definition
Specifies a 1- to 32-character name of a parameter to be passed to the table procedure. Parameter-name must:
All parameters are implicitly nullable. Input parameters can be assigned NULL as a parameter value and output parameters can return NULL.
Defines the data type for the named parameter. For expanded data-type syntax, see Expansion of Data-type.
Directs CA IDMS to pass a default value for the named parameter if no value for the parameter is specified.
The default value for a parameter is based on its data type:
|
Column data type |
Default value |
|---|---|
|
CHARACTER |
Blanks |
|
VARCHAR |
A character string literal with a length of zero (that is, '') |
|
GRAPHIC |
Double-byte blanks |
|
VARGRAPHIC |
A double-byte character string literal with a length of zero |
|
DATE |
The value in the CURRENT DATE special register |
|
TIME |
The value in the CURRENT TIME special register |
|
TIMESTAMP |
The value in the CURRENT TIMESTAMP special register |
|
All numeric data types |
0 (zero) |
Parameters for Expansion of language-clause
Specifies that the SQL routine is written in the CA ADS language.
Specifies that the SQL routine is written in the assembler language.
Specifies that the SQL routine is written in the COBOL language.
Specifies that the SQL routine is written in the PL/I language.
Specifies that the SQL routine is written in the SQL language.
Note: The ability to specify ADS or ASSEMBLER as a language is a CA IDMS extension.
Changing the language of a procedure
A procedure with language SQL cannot be changed to any other language, and a procedure whose language is not SQL cannot be changed to language SQL.
Specifying a Synchronization Stamp
When defining or altering a procedure, you can specify a value for its synchronization stamp. You should use care when doing so because the purpose of the stamp is to enable the detection of discrepancies between an entity and its definition. If explicitly specified, you must set the synchronization stamp to a new value following a change so that the change is detectable by the runtime system.
If not specified, the synchronization stamp is automatically set to the current date and time.
Note: For more information about creating a procedure, see CREATE PROCEDURE.
Adding Parameters to a Procedure
The following ALTER PROCEDURE statement adds two new parameters to the EMP.GET_BONUS procedure:
alter procedure emp.get_bonus
add (start_month char (2),
start_year char (2));
|
Copyright © 2014 CA.
All rights reserved.
|
|