Changing an existing version of a native SQL procedure

You can change an option or the procedure body for a particular version of a native SQL procedure. If you want to keep a copy of that stored procedure, consider creating a new version instead of changing the existing version.

Procedure

To change an existing version of a native SQL procedure, issue one of the following statements:
  • Start of changeFL 507The CREATE PROCEDURE statement with the OR REPLACE and the VERSION clause that identifies the version to be replaced.End of change
  • The ALTER PROCEDURE statement with the REPLACE VERSION clause.

    Any option that you do not explicitly specify inherits the system default values. This inheritance occurs even if those options were explicitly specified for a prior version by using a CREATE PROCEDURE statement, ALTER PROCEDURE statement, or REBIND command.

Examples

Example 1
The following ALTER PROCEDURE statement updates version V2 of the UPDATE_BALANCE procedure. 
ALTER PROCEDURE
TEST.UPDATE_BALANCE
REPLACE VERSION V2
(IN CUSTOMER_NO INTEGER,
IN AMOUNT DECIMAL(9,2))
MODIFIES SQL DATA
ASUTIME LIMIT 100
BEGIN
UPDATE ACCOUNTS
SET BAL = BAL + AMOUNT
WHERE CUSTNO = CUSTOMER_NO
AND CUSTSTAT = 'A';
END
Start of changeExample 2End of change
Start of changeFL 507

The following CREATE PROCEDURE statement will replace the version V2 of the UPDATE_BALANCE procedure if version V2 already exists or will create it if version V2 has not yet been defined:

CREATE OR REPLACE PROCEDURE
TEST.UPDATE_BALANCE
(IN CUSTOMER_NO INTEGER,
IN AMOUNT DECIMAL(9,2))
VERSION V2
MODIFIES SQL DATA
ASUTIME LIMIT 100
BEGIN
UPDATE ACCOUNTS
SET BAL = BAL + AMOUNT
WHERE CUSTNO = CUSTOMER_NO
AND CUSTSTAT = 'A';
END
End of change