ALTER PROCEDURE

ALTER PROCEDURE— change a stored procedure

Syntax

ALTER PROCEDURE [schema.++]++ name
    [ ( [ [ IN | INOUT | OUT ] arg_name arg_type
          [ ,[ IN | INOUT | OUT ] arg_name arg_type ]... ] ) ]
    [ RETURNS
        table_name ( column_name column_type [ ,column_name column_type ]... )
        [ ,table_name ( column_name column_type [ ,column_name column_type ]... ) ]... ]
    [ LANGUAGE SQL | LANGUAGE JAVA ]
    [ SECURITY INVOKER | SECURITY DEFINER ]
{
    EXTERNAL 'sp_routine'
|
    AS
        sql_code_block
    END_PROCEDURE;
}

Description

ALTER PROCEDURE uses the exact same syntax as CREATE PROCEDURE (see CREATE PROCEDURE) and will succeed only if the procedure already exists. To change a stored procedure, you need to supply the body and the parameters of the procedure. ALTER PROCEDURE does not modify the privileges, nor the owner, of a procedure. If User A, for instance, creates the procedure and then grants to User B the right to modify it, the procedure will still have User A as the creator (important if SECURITY DEFINER is specified) and all the rights that User A granted User B.

Arguments

If the procedure declares one or more arguments, they will appear as variables to all the statements of the procedure. Each argument to the stored procedure is declared as IN, INOUT, or OUT followed by the argument name and its type.

IN declares the argument as an input variable passed to the procedure. The final value of an IN parameter is instead discarded.

You can specify a result set as an IN parameter to a stored procedure. See Using a Result Set as Input to a Stored Procedure.

INOUT declares the argument as an input and output variable passed to the procedure. It can be modified by the procedure and returned out again to the caller.

OUT declares the argument as an output variable that can be modified by the procedure and returned out to the caller.

An argument that doesn’t specify the direction is assumed to be a IN argument.

See arg_name and arg_type parameters.

Return Values

A stored procedure can optionally return one or more result sets. A result set is a temporary, in-memory table containing the results being returned from the stored procedure. To designate that the stored procedure is to return one or more result sets, use RETURNS followed by a comma separated list of table definitions, table_name ( field [,field]…), that represent the result sets being returned. See table_name, column_name and column_type parameters below.

If a procedure is declared as RETURNS table_name…, a temporary, in-memory table with the specified structure will appear in the database, and its content will be returned to the caller as an output result set. This temporary, in-memory table cannot be accessed outside of the stored procedure.

If the current connection already has a temporary, in-memory table defined with the same name, references to such table from inside the stored procedure will actually be referencing this RETURNS table.

If the current schema already has a permanent table defined with the same name, it must be accessed within the stored procedure by using its fully qualified name (i.e. schema.tablename).

Language

The language for a stored procedure, user defined function or trigger may be SQL or Java. This is specified by declaring the procedure, function or trigger with LANGUAGE SQL or LANGUAGE JAVA. The default language, if none is specified, is SQL. SQL stored procedures, UDFs, and triggers are defined by sql_code_block. See SQL Procedural Language for more information.

Stored procedures, user defined functions and triggers written in Java must use the EXTERNAL keyword. See Using Embedded Java Stored Procedures.

Security

You may declare a stored procedure or user defined function to work in two different ways with respect to the calling user’s privileges on database objects affected. The procedure or function may be declared in one of two ways:

SECURITY INVOKER

Checks the privileges of the calling user. If a stored procedure or user defined function is inserting, selecting, and/or updating one or more tables, the calling user has to have the appropriate privilege for the operation on each table.

SECURITY DEFINER

Specifies that the stored procedure or user defined function is to be executed with the privileges of the user that created it. For example, the calling user may not have privileges to SELECT or INSERT INTO tables when referencing them outside the stored procedure or user defined function, but will be allowed access when executing the stored procedure or function. If a security level is not specified, this is the default.

Whoever gets the execution rights on the procedure can access the same tables/schema/sequence/etc.. of the creator of the procedure, even if the current user is still the invoker.

External Definition

Declaring a stored procedure as EXTERNAL is done to describe a dynamically loadable Java stored procedure that is to be executed. For LANGUAGE JAVA, the EXTERNAL stored procedure defined by sp_routine must be in the format classid:fully_qualified_class.method, where classid is the ID used in the CREATE JAVACLASS command used to upload to the NuoDB server the JAR with the defined code. See CREATE JAVACLASS.

SQL Procedural Language - `ALTER PROCEDURE` body

If defining this stored procedure in SQL, sql_code_block is one or more SQL statements. See SQL Procedural Language for more information.

System Tables

The body of the procedure is stored in SYSTEM.PROCEDURES in the PROCEDURETEXT column. See PROCEDURES System Table Description.

Parameters

schema: Name of the schema in which the procedure will be created. The default is the current schema as defined by the US E command. If schema is provided, and the schema does not exist, it will be created.
name: Optionally schema-qualified name of the stored procedure.
arg_name: Specifies the variable name for the input and/or output parameter to the stored procedure.
arg_type: Specifies the data type of the input and/or output parameter for the stored procedure.
table_name: Specifies the name of the temporary, in-memory table containing the result set being returned from the stored procedure.
column_name: Column name of a column in the temporary, in-memory table containing the result set being returned from the stored procedure.
column_type: Column data type of a column in the temporary, in-memory table containing the result set being returned from the stored procedure.

When creating or altering multi-statement stored procedures in nuosql, the SET DELIMITER command must be used prior to defining the stored procedure (see SET). This new delimiter is used to indicate the end of the stored procedure definition and will execute the CREATE PROCEDURE statement. See the examples below.

Example

Use ALTER PROCEDURE to change security

CREATE PROCEDURE prc_sec() SECURITY INVOKER AS THROW 'message'; END_PROCEDURE;

ALTER PROCEDURE prc_sec() SECURITY DEFINER AS THROW 'message'; END_PROCEDURE;