ALTER FUNCTION

Function

It compiles a function again.

Syntax

<alter function statement> ::=
    ALTER FUNCTION function_name COMPILE
    ;

Invocation and Access Rules

One of the following privileges is required to perform <alter function statement>.

• The owner of that function

• (ALTER PROCEDURE or CONTROL SCHEMA) ON SCHEMA for the schema to which the function belongs

• ALTER ANY PROCEDURE ON DATABASE

Syntax Rules and Parameters

• function Name

  • It is the function name to be compiled.

  • It can define the schema to which the function belongs, such as schema_name.func_name. If schema_name is omitted, the default schema name of the user performing the statement is used.

Description

It compiles a specified schema-level function again.

Examples

gSQL> ALTER FUNCTION FUNC1 COMPILE;

Function altered.

Compatibility

It is a statement altering characteristics specified when performing CREATE in the SQL standard, and astatement recreating a plan of a function in GOLDILOCKS.

For More Information

Refer to the followings.

• CREATE FUNCTION

• DROP FUNCTION

ALTER PROCEDURE

Function

It compiles a procedure again.

Syntax

<alter procedure statement> ::=
    ALTER PROCEDURE proc_name COMPILE
    ;

Invocation and Access Rules

One of the following privileges is required to perform <alter procedure statement>.

• The owner of that procedure

• (ALTER PROCEDURE or CONTROL SCHEMA) ON SCHEMA for the schema to which the procedure belongs

• ALTER ANY PROCEDURE ON DATABASE

Syntax Rules and Parameters

• Proc Name

  • It is the procedure name to be compiled.

  • It can define the schema to which the procedure belongs, such as schema_name.proc_name. If schema_name is omitted, the default schema name of the user performing the statement is used.

Description

It compiles a specified schema-level procedure again.

Examples

gSQL> CREATE OR REPLACE PROCEDURE PROC1( A1 INTEGER )
IS
BEGIN
  INSERT INTO T1 VALUES( A1 );
END;
/

ERR-01000(16409): Warning: Routine definition has compilation errors
ERR-HY000(17032): PSM compilation error : 
(1) at (5:15): ERR-17053: schema or table object does not exist
Procedure created.

gSQL> CALL PROC1(1);

ERR-HY000(17032): PSM compilation error : 
(1) at (5:15): ERR-17053: schema or table object does not exist

gSQL> CREATE TABLE T1( I1 INTEGER );

Table created.

gSQL> COMMIT;

Commit complete.

gSQL> ALTER PROCEDURE PROC1 COMPILE;

Procedure altered.

gSQL> COMMIT;

Commit complete.

gSQL> CALL PROC1(2);

Procedure Call complete.

gSQL> SELECT * FROM T1;

I1
--
 2

1 row selected.

Compatibility

It is a statement altering characteristics specified when performing CREATE in the SQL standard, and astatement recreating a plan of a procedure in GOLDILOCKS.

For More Information

Refer to the followings.

• CREATE PROCEDURE

• DROP PROCEDURE

CALL Statement

Function

It performs a schema-level procedure or a function.

Syntax

<call statement> ::= 
    <sql call statement> | <odbc procedure call escape sequence>
    ;

<sql call statement> ::=
    CALL proc_name [ ( value_expr [ , value_expr ] .. ) ]  [ INTO { '?' | { host_param [ indicator_param ] } } ]

<odbc procedure call escape sequence> ::=
    '{' [ ? = ] CALL proc_name [ ( value_expr [ , value_expr ] .. ) ] '}'

Invocation and Access Rules

One of the following privileges is required to perform <call statement>.

• The EXECUTE privilege for that procedure

• (EXECUTE PROCEDURE or CONTROL SCHEMA) ON SCHEMA for the schema to which the procedure belongs

• EXECUTE ANY PROCEDURE ON DATABASE

Syntax Rules and Parameters

• proc_name

  • It is a name of a procedure/ function to be executed.

  • It may includes a schema to which the procedure belongs such as Schema_name.Proc_name.

• value_expr

  • It expresses an argument value which were transferred to the procedure. It can use a bind parameter such as '?' or ':V1'.

Description

It executes a schema-level SQL procedure or a function by using specified arguments.

A function of <sql call statement> form returns the result value by using a host variable expression or a dynamic bind parameter (?) after INTO clause.

<odbc procedure call escape sequence> form is a standard statement to call PROCEDURE in ODBC/ JDBC, and GOLDILOCKS supports this statement in a server. (It can also be used in a tool such as gsql.) A function returns the result value by using assign expressions ( ? = ) at the front.

Examples

Call Procedure

gSQL> CREATE OR REPLACE PROCEDURE PROC1
(
  A1 INTEGER
)
IS
BEGIN
  DBMS_OUTPUT.PUT_LINE('A1=' || A1);
END;
/

Procedure created.

gSQL> \var v1 INTEGER;
gSQL> \exec :v1 := 123;
gSQL> CALL PROC1(:v1);
A1=123

Procedure Call complete.

Call Function

CREATE OR REPLACE FUNCTION FUNC1
(
  A1 INTEGER
)
RETURN INTEGER
IS
BEGIN
    return A1;
END;
/

Function created.


gSQL> \var v1 INTEGER;
gSQL> \var v2 INTEGER;
gSQL> \exec :v1 := 123;
gSQL> CALL FUNC1(:v1) INTO :v2;
Procedure Call complete.

gSQL> \print v2;
 V2
---
123

Compatibility

The SQL standard allows only the call for a PROCEDURE, so it does not define below [INTO] clause.

CREATE FUNCTION

Function

It defines a schema-level function.

Syntax

<create procedure statement> ::=
    CREATE [ OR REPLACE ]  
        FUNCTION func_name [ ( { param_name [IN|OUT|INOUT] datatype [ { := | DEFAULT } init_expr ] } [, ...] ) ]
        RETURN datatype
        [ <func_characteristics> ]
        { IS | AS } <item_declaration> BEGIN <pl_stmt_list> END
    ;

<func_characteristics> ::=
    DETERMINISTIC | AUTHID CURRENT_USER | AUTHID DEFINER

Invocation and Access Rules

The user should satisfy the following conditions to perform <create function statement>.

• One of the following privileges is required to create a function.

  • (CREATE PROCEDURE or CONTROL SCHEMA) ON SCHEMA for the schema to which the function belongs

  • CREATE ANY PROCEDURE ON DATABASE

• If a function already exists when using OR REPLACE clause, then one of the following privileges dropping the existing function is required.

  • The owner of that function

  • (DROP PROCEDURE or CONTROL SCHEMA) ON SCHEMA for the schema to which the function belongs

  • DROP ANY PROCEDURE ON DATABASE

• The user who performed the statement becomes the owner of the created function.

Syntax Rules and Parameters

OR REPLACE

It replaces an existing function when the function already exists.

FUNCTION NAME

It is a name of function to be created, and it should be a unique name in a schema.
It can define the schema to which the function belongs, such as schema_name.func_name. If schema_name is omitted, the default schema name of the user performing the statement is used.
The length of a function name should be shorter than 128 bytes.

PARAM NAME

It defines a name of an argument to be used in a function.
The name of each argument should be unique in a function.
The length of each argument name should be shorter than 128 bytes.
It does not limit the maximum number of an argument to be used in a single function.

Bind Type

It specifies a bind type of each argument.
If it is not specified, the default type is IN.

Func_Characteristics

It defines options to perform a function. It should be specified only once per a single item.

• DETERMINISTIC: The function always returns the same result value when the same argument values are input.

• AUTHID CURRENT_USER: SQLs which are being performed during performing a function are interpreted and performed according to an authorization of an invoker.

• AUTHID DEFINER: SQLs which are being performed during performing a function are interpreted and performed according to an authorization of a definer.

Item Declaration

It declares items such as a local variable to be used within a function.
It can declare all items which can be declared in a PL block.

PL Stmt List

It is a body section of a function, and it lists PL statements to be performed.
It can not use a bind parameter such as '?' or ':V1'. within a function.

Description

It defines a schema-level SQL function. The created fucntion can be called from all expressions.

The definition of a function can be viewed in ROUTINES table of INFORMATION_SCHEMA. The definition of a function parameter can be viewed in PARAMETERS table of INFORMATION_SCHEMA.

If a function becomes temporarily unstable due to absences of related objects, then it can try to recreate a plan by using <alter function> statement.

The created function can be dropped by using <drop function> statement.
The maximum number of functions to be created is not limited. Therefore, they can be created as many as the storage space is available.

Examples

gSQL> CREATE OR REPLACE FUNCTION FUNC1( A1 INTEGER, A2 INTEGER )
  RETURN INTEGER
  IS
    V1 INTEGER;
  BEGIN

    SELECT COUNT(*)
      INTO V1
      FROM T1
      WHERE T1.I1 >= A1 AND T1.I1 <= A2;

    RETURN V1;
  END;
  /

Function created.

Compatibility

The SQL standard does not define OR REPLACE clause.

For More Information

Refer to the followings.

• DROP FUNCTION

• ALTER FUNCTION

CREATE PROCEDURE

Function

It defines a schema-level procedure.

Syntax

<create procedure statement> ::=
    CREATE [ OR REPLACE ]  
        PROCEDURE proc_name [ ( { param_name [IN|OUT|INOUT] datatype [ { := | DEFAULT } init_expr ] } [, ...] ) ]
        [ <proc_characteristics> ]
        { IS | AS } <item_declaration> BEGIN <pl_stmt_list> END
    ;

<proc_characteristics> ::=
    AUTHID CURRENT_USER | AUTHID DEFINER

Invocation and Access Rules

The user should satisfy the following conditions to perform <create procedure statement>.

• One of the following privileges is required to create a procedure.

  • (CREATE PROCEDURE or CONTROL SCHEMA) ON SCHEMA for the schema to which the procedure belongs

  • CREATE ANY PROCEDURE ON DATABASE

• If a procedure already exists when using OR REPLACE clause, then one of the following privileges dropping the existing procedure is required.

  • The owner of that procedure

  • (DROP PROCEDURE or CONTROL SCHEMA) ON SCHEMA for the schema to which the procedure belongs

  • DROP ANY PROCEDURE ON DATABASE

• The user who performed the statement becomes the owner of the created procedure.

Syntax Rules and Parameters

OR REPLACE

It replaces an existing procedure when the procedure already exists.

PROC NAME

It is a name of procedure to be created, and it should be a unique name in a schema.
It can define the schema to which the procedure belongs, such as schema_name.proc_name. If schema_name is omitted, the default schema name of the user performing the statement is used.
The length of a procedure name should be shorter than 128 bytes.

PARAM NAME

It defines a name of an argument to be used in a procedure.
The name of each argument should be unique in a procedure.
The length of each argument name should be shorter than 128 bytes.
It does not limit the maximum number of an argument to be used in a single procedure.

Bind Type

It specifies a bind type of each argument.
If it is not specified, the default type is IN.

proc_characteristics

It defines options to perform a procedure. It should be specified only once per a single item.

• AUTHID CURRENT_USER: SQLs which are being performed during performing a procedure are interpreted and performed according to an authorization of an invoker.

• AUTHID DEFINER: SQLs which are being performed during performing a procedure are interpreted and performed according to an authorization of a definer.

Item Declaration

It declares items such as a local variable to be used within a procedure.
It can declare all items which can be declared in a PL block.

PL Stmt List

It is a body section of a procedure, and it lists PL statements to be performed.
It can not use a bind parameter such as '?' or ':V1'. within a procedure.

Description

It defines a schema-level SQL procedure. The created procedure can be called from CALL statement, anonymous block, or other procedure/ function.

The definition of a procedure can be viewed in ROUTINES table of INFORMATION_SCHEMA. The definition of a procedure parameter can be viewed in PARAMETERS table of INFORMATION_SCHEMA.

If a procedure becomes temporarily unstable due to absences of related objects, then it can try to recreate a plan by using <alter procedure> statement.

The created procedure can be dropped by using <drop procedure> statement.
The maximum number of procedures to be created is not limited. Therefore, they can be created as many as the storage space is available.

Examples

gSQL> CREATE OR REPLACE PROCEDURE PROC1( A1 INTEGER, A2 INTEGER )

  IS
    V1 INTEGER;
  BEGIN

    SELECT COUNT(*)
      INTO V1
      FROM T1
      WHERE T1.I1 >= A1 AND T1.I1 <= A2;

    DBMS_OUTPUT.PUT_LINE( 'V1 = ' || V1 );
  END;
  /

Procedure created.

Compatibility

The SQL standard does not define the following clauses.

For More Information

Refer to the followings.

• DROP PROCEDURE

• ALTER PROCEDURE

DROP FUNCTION

Function

It drops a function.

Syntax

<drop function statement> ::=
    DROP FUNCTION [ IF EXISTS ] func_name
    ;

Invocation and Access Rules

One of the following privileges is required to perform <drop function statement>.

• The owner of that function

• (DROP PROCEDURE or CONTROL SCHEMA) ON SCHEMA for the schema to which the function belongs

• DROP ANY PROCEDURE ON DATABASE

Syntax Rules and Parameters

IF EXISTS

Even when the function does not exist, an error does not occur.

FUNC NAME

It is the function name to be dropped.
It can define the schema to which the function belongs, such as schema_name.func_name. If schema_name is omitted, the default schema name of the user performing the statement is used.

Description

It drops a specified schema-level function.

Examples

gSQL> CREATE OR REPLACE FUNCTION FUNC1
RETURN INTEGER
 IS
    V1 INTEGER;
  BEGIN
    V1 := 10;
    RETURN V1;
  END;
  /

Function created.


COMMIT;

Commit complete.
gSQL> DROP FUNCTION FUNC1;

Function dropped.

Compatibility

The SQL standard does not define the following clauses.

For More Information

Refer to the followings.

• CREATE FUNCTION

• ALTER FUNCTION

DROP PROCEDURE

Function

It drops a procedure.

Syntax

<drop procedure statement> ::=
    DROP PROCEDURE [ IF EXISTS ] proc_name
    ;

Invocation and Access Rules

One of the following privileges is required to perform <drop procedure statement>.

• The owner of that procedure

• (DROP PROCEDURE or CONTROL SCHEMA) ON SCHEMA for the schema to which the procedure belongs

• DROP ANY PROCEDURE ON DATABASE

Syntax Rules and Parameters

IF EXISTS

Even when the procedure does not exist, an error does not occur.

PROC NAME

It is the procedure name to be dropped.
It can define the schema to which the procedure belongs, such as schema_name.proc_name. If schema_name is omitted, the default schema name of the user performing the statement is used.

Description

It drops a specified schema-level procedure.

Examples

gSQL> CREATE OR REPLACE PROCEDURE PROC1( A1 INTEGER, A2 INTEGER )
  IS
    V1 INTEGER;
  BEGIN

    SELECT COUNT(*)
      INTO V1
      FROM T1
      WHERE T1.I1 >= A1 AND T1.I1 <= A2;

    DBMS_OUTPUT.PUT_LINE( 'V1 = ' || V1 );
  END;
  /

Procedure created.


COMMIT;

Commit complete.


gSQL> DROP PROCEDURE PROC1;

Procedure dropped.

Compatibility

The SQL standard does not define the following clauses.

For More Information

Refer to the followings.

• CREATE PROCEDURE

• ALTER PROCEDURE