ALTER FUNCTION

Function

It recompiles a function.

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 recompiles a specified schema-level function.

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 PACKAGE

Function

It recompiles a package.

Syntax

<alter package statement> ::=
    ALTER PACKAGE package_name <package compile clause>
    ;

<package compile clause> ::=
    COMPILE [PACKAGE|SPECIFICATION|BODY]

Invocation and Access Rules

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

• The owner of that package

• (ALTER PACKAGE or CONTROL SCHEMA) ON SCHEMA for the schema to which the package belongs

• ALTER ANY PACKAGE ON DATABASE

Syntax Rules and Parameters

• Package name

  • It is a name of package to be compiled.

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

• Package compile clause

  • PACKAGE: It recompiles both the specification and the body. (Default)

  • SPECIFICATION: It recompiles only the specification.

  • BODY: It recompiles only the body.

Description

It recompiles the specified package.
The execution code of the compiled package is stored in the plan cache.

Examples

ALTER PACKAGE PKG1 COMPILE;
Package altered.

ALTER PACKAGE PKG1 COMPILE PACKAGE;
Package altered.

ALTER PACKAGE PKG1 COMPILE BODY;
Package altered.

Compatibility

It is ALTER MODULE statement in the SQL standard.

For More Information

Refer to the followings.

• CREATE PACKAGE

• CREATE PACKAGE BODY

• DROP PACKAGE

ALTER PROCEDURE

Function

It recompiles a procedure.

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 recompiles a specified schema-level procedure.

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 function statement> ::= 
    CREATE [ OR REPLACE ] FUNCTION <function name> [ ( <parameter list> ) ]
       <return clause> 
       [ <function characteristics> ] 
       { IS | AS }  
       <item declaration>  
       BEGIN 
       <pl statement list> 
       END [ <function name> ]
    ; 

<parameter list> ::= 
      <parameter name> [ <parameter mode> ] <datatype> [ <parameter default> ] [ , ... ] 
 
<parameter mode> ::= 
      IN 
    | OUT 
    | IN OUT 
 
<parameter default> ::= 
      { := | DEFAULT } <value expression> 
 
<return clause> ::= 
      RETURN <datatype> 
    | RETURN TABLE ( <table function column list> ) 
 
<table function column list> ::=  
      <column name> <datatype> [ , ... ] 
 
<function 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 with a new 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.function_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.

parameter name

It defines a parameter name of the function. 
The name of each parameter should be unique in a function. 
In other words, the function's parameter and PL item can not have the same name. 
The length of a parameter name should be shorter than 128 bytes. 
The maximum number of parameters available in a single function is limitless.

parameter mode

It sets each parameter mode. 
The parameter modes are IN, OUT, and IN OUT.
If the parameter mode is not specified, the default mode is IN.

parameter default

It is the default value of the parameter.
The parameter with the specified parameter default can be omitted when executing the function. 
If the parameter is not specified but omitted, then the default value is <value expression> specified when defining the parameter.
The datatype of <value expression> should be the datatype of the parameter.
All parameters defined after the parameter having <parameter default> should have <parameter default>.

return clause

It defines the return type of the function. 
It is defined as follows in <return clause>.

• RETURN <datatype>

  • It defines the datatype of the return value returned by the function.

• RETURN TABLE ( <table function column list> )

  • It defines the table type of the returned result set.

table function column list

It is the column name of the result set returned by the table function.
The length of a column name should be shorter than 128 bytes.
The number of columns are limitless.
Each column name is unique in <table function column list>.
The column name can be as same as the parameter name and the declare item name.
The column defined in <table function column list> can not be referenced in PL block of the function.

function 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 PACKAGE

Function

It defines the spec about public items to be used in a package.

Syntax

<create package statement> ::=
    CREATE [ OR REPLACE ]  
        PACKAGE package_name 
        [ <package_characteristics> ]
        { IS | AS }
       <item_declaration> 
    END
    ;

<package_characteristics> ::=
    AUTHID CURRENT_USER | AUTHID DEFINER

<item_declaration> ::=    <variable declaration> 
                        | <cursor declaration>
                        | <user-defined type declaration> 
                        | <function declaration> 
                        | <procedure declaration> 
                        | <user exception declaration>
                        | <cursor definition>

Invocation and Access Rules

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

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

  • (CREATE PACKAGE or CONTROL SCHEMA) ON SCHEMA for the schema to which the package belongs

  • CREATE ANY PACKAGE ON DATABASE

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

  • The owner of that package

  • (DROP PACKAGE or CONTROL SCHEMA) ON SCHEMA for the schema to which the package belongs

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

Syntax Rules and Parameters

OR REPLACE

It replaces an existing package specification when the package already exists.

PACKAGE NAME

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

Package Characteristics

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

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

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

Item Declaration

It declares a public variable, a type cursor, a cursor variable, a function spec, and a procedure spec which are accessible from out of the package. 
A function and a procedure in the package should be defined through create package body statement.
The cursor which was declared in a package without SQL should be defined through create package body statement. 
Also, a function, a procedure, an argument of a cursor and the returning type in a package should be as same as those defined in a body statement.

Description

It creates the schema-level package specification. 
All public items in the created package can be referred by another procedure/ function/ package/ anonymous block.

The information about package creation can be viewed in MODULES table in DEFINITION_SCHEMA or INFORMATION_SCHEMA. 
The list of each public procedure/ function in the package can be viewed in ROUTINES table in DEFINITION_SCHEMA or INFORMATION_SCHEMA. 
The definition about parameters of each public procedure/ function in the package can be viewed in PARAMETERS table in DEFINITION_SCHEMA or INFORMATION_SCHEMA.
Private procedure/ function can be viewed in MODULE_BODY table. 
If the information about objects referred by a procedure/ function/ variable in the package are temporarily unstable, try to recompile it by using <alter package> statement.

Examples

CREATE OR REPLACE PACKAGE PKG1
IS
  V1 INTEGER;
  PROCEDURE PROC1;
  FUNCTION FUNC1 RETURN INTEGER;
END;
/

Package created.

Compatibility

It is CREATE MODULE statement in the SQL standard.

For More Information

Refer to the followings,

• CREATE PACKAGE BODY

• DROP PACKAGE

• ALTER PACKAGE

CREATE PACKAGE BODY

Function

It creates the definition about procedure/ function/ cursors to be used in the package.

Syntax

<create package body statement> ::=
    CREATE [ OR REPLACE ] PACKAGE BODY package_name 
    { IS | AS } 
       <item_declaration> 
       <cursor_definition> 
       <routine_definition>
    [ BEGIN <initialization part> ]
    END
    ;

<item_declaration> ::=    VARIABLE_DECLARATION 
                        | CURSOR_DECLARATION
                        | USER_DEFINED_TYPE_DEFINITION 
                        | FUNCTION_DECLARATION 
                        | PROCEDURE_DECLARATION  
                        | USER_EXCEPTION_DECLARATION

<routine_definition> ::= FUNCTION_DEFINITION
                       | PROCEDURE_DEFINITION

<initialization part> ::= <pl_stmt_list>

Invocation and Access Rules

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

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

  • (CREATE PACKAGE or CONTROL SCHEMA) ON SCHEMA for the schema to which the package belongs

  • CREATE ANY PACKAGE ON DATABASE

• The package spec should be defined in advance.

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

  • The owner of that package

  • (DROP PACKAGE or CONTROL SCHEMA) ON SCHEMA for the schema to which the package belongs

  • DROP ANY PACKAGE ON DATABASE

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

Syntax Rules and Parameters

OR REPLACE

It replaces an existing package body definition when the package body already exists.

PACKAGE NAME

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

Item Declaration

It declares PSM identifiers (variable, type, cursor, function spec, procedure spec, exception) to be used in a package body.
Routines declared in a package spec should be defined in a package body.
The cursor which was declared in a package spec without SQL should be defined in a package body. 

Also, a function, a procedure, an argument of a cursor and the returning type in a package should be as same as those defined in a body statement.

Initialization Part

It describes statements which are performed only once to initialize internal variables while creating a package instance.

Description

It creates the schema-level package specification. 
All public items in the created package can be referred by another procedure/ function/ package/ anonymous block.

The information about package body creation can be viewed in MODULES_BODY table in DEFINITION_SCHEMA or INFORMATION_SCHEMA. 
The list of each private items in a package body can not be viewed in DEFINITION_SCHEMA or INFORMATION_SCHEMA. 
If the information about objects referred by a procedure/ function/ variable in the package are temporarily unstable, try to recompile it by using <alter package body> statement.

The created package body can be dropped by using <drop package> or <drop package body> statement.

Examples

CREATE OR REPLACE PACKAGE PKG1
IS
  V1 INTEGER;
  PROCEDURE PROC1;
  FUNCTION FUNC1 RETURN INTEGER;
END;
/

Package created.

CREATE OR REPLACE PACKAGE BODY PKG1
IS
  FUNCTION FUNC1 RETURN INTEGER
  IS
  BEGIN
      RETURN V1;
  END;

  PROCEDURE PROC1
  IS
  BEGIN
      IF V1 IS NULL
      THEN
          V1 := 10;
      ELSE
          V1 := V1 + 10;
      END IF;
  END;

END;
/

Package created.

Compatibility

The SQL standard does not define it.

For More Information

Refer to the followings.

• CREATE PACKAGE

• DROP PACKAGE

• ALTER PACKAGE

CREATE PROCEDURE

Function

It defines a schema-level procedure.

Syntax

<create procedure statement> ::=
      CREATE [ OR REPLACE ] PROCEDURE <procedure name> [ ( <parameter list> ) ]
      { IS | AS } 
      <item declaration>  
      BEGIN
      <pl statement list> 
      END [ <procedure name> ]
      ;
 
<parameter list> ::= 
      <parameter name> [ <parameter mode> ] <datatype> [ <parameter default> ] [ , ... ] 

<parameter mode> ::= 
      IN 
    | OUT 
    | IN OUT 
 
<parameter default> ::= 
      { := | DEFAULT } <value expression> 

<procedure 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 with a new function when the procedure already exists.

procedure 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.procedure_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.

parameter name

It defines a parameter name of the procedure. 
The name of each parameter should be unique in a procedure. 
In other words, the procedure's parameter and PL item can not have the same name. 
The length of a parameter name should be shorter than 128 bytes. 
The maximum number of parameters available in a single procedure is limitless.

parameter mode

It sets each parameter mode. 
The parameter modes are IN, OUT, and IN OUT.
If the parameter mode is not specified, the default mode is IN.

parameter default

It is the default value of the parameter.
The parameter with the specified parameter default can be omitted when executing the procedure. 
If the parameter is not specified but omitted, then the default value is <value expression> specified when defining the parameter.
The datatype of <value expression> should be the datatype of the parameter.
All parameters defined after the parameter having <parameter default> should have <parameter default>.

procedure 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 PACKAGE

Function

It drops a package (of only body or both spec/ body).

Syntax

<drop package statement> ::=
    DROP PACKAGE [BODY] [ IF EXISTS ] package_name
    ;

Invocation and Access Rules

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

• The owner of that package

• (DROP PACKAGE or CONTROL SCHEMA) ON SCHEMA for the schema to which the package belongs

• DROP ANY PACKAGE ON DATABASE

Syntax Rules and Parameters

BODY

It drops only the body object in the package of the given name. If the keyword BODY is not specified it drops both the package specification and the body.

IF EXISTS

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

PACKAGE NAME

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

Description

It drops the specified package object.

Examples

CREATE PACKAGE PKG1
  IS
    V1 INTEGER;
    FUNCTION FUNC1 (A1 INTEGER) RETURN INTEGER;
  END;
  /

Package created.

DROP PACKAGE IF EXISTS PKG1;

Package dropped.

Compatibility

It is DRO MODULE statement in the SQL standard.

For More Information

Refer to the followings.

• CREATE PACKAGE

• CREATE PACKAGE BODY

• ALTER PACKAGE

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