Create a stored procedure

After you create the body of the stored procedure, you add it by using the CREATE [OR REPLACE] PROCEDURE command. You enter this command at a SQL command prompt.

The CREATE PROCEDURE command creates a procedure. To use this command, you must be the admin user, own the database, own the schema (multi-schema systems), or have the Create Procedure privilege. The CREATE OR REPLACE PROCEDURE command creates a procedure if one does not already exist with the specified signature, or updates an existing procedure with the matching signature for the new (or different) field values. To replace a procedure, you must have the Alter privilege.

For example, the following procedure customer() writes a customer name string to the database log by using the RAISE NOTICE statement:

TEST.TESTSCH(USR)=> CREATE PROCEDURE customer()
RETURNS INT4 LANGUAGE NZPLSQL AS
BEGIN_PROC
    BEGIN
        RAISE NOTICE 'The customer name is alpha';
    END;
END_PROC;
CREATE PROCEDURE

Since the execution user permissions were not specified on the command line, the command uses the default of EXECUTE AS OWNER.

If you want to change the customer procedure to specify a new return value, you can use a CREATE OR REPLACE PROCEDURE command similar to the following, but you must specify all the required fields (such as language and the complete procedure body) even if their values did not change, as follows:

TEST.TESTSCH(USR)=> CREATE OR REPLACE PROCEDURE customer()
RETURNS INT8 LANGUAGE NZPLSQL AS
BEGIN_PROC
    BEGIN
        RAISE NOTICE 'The customer name is alpha';
    END;
END_PROC;
CREATE PROCEDURE

You can also use the ALTER PROCEDURE command to modify certain characteristics of a defined procedure.