SQL$HELP72.HLB

    Deletes a routine definition, external or stored, from an Oracle
    Rdb database. External routine refers to both external functions
    and external procedures. Stored routine refers to both stored
    functions and stored procedures.

1 – Environment

    You can use the DROP FUNCTION and DROP PROCEDURE statements:

    o  In interactive SQL

    o  Embedded in host language programs to be precompiled

    o  As part of a procedure in an SQL module

    o  In dynamic SQL as a statement to be dynamically executed

2 – Format

  (B)0[m[1;4mDROP[m[1m qwq> [1;4mFUNCTION[m[1m qqwq> <routine-name> [mq[1mwqwqqqqqqqqqqqqqqqwqwq>[m
  [1m      mq> [1;4mPROCEDURE[m[1m qj                   x[m [1mtq> [1;4mRESTRICT[m [1mqqqu x [m
  [1m                                         x[m [1mtq> [1;4mCASCADE[m[1m qqqqu x [m
                                           [1mx[m [1mtq> [1;4mRESRICT[m[1m qqqqu[m [1mx[m
  [1m                                         x mq> [1;4mIF[m[1m [1;4mEXISTS[m [1mqqj x [m
                                           [1mmqqqqqqqqqq<qqqqqqqqj[m

3 – Arguments

3.1 – CASCADE

    Deletes the routine definition even when there are dependencies
    on the specified routine. Any referencing routines are marked
    invalid.

3.2 – IF_EXISTS

    Prevents SQL command language from displaying error messages if
    the referenced object does not exist in the database.

3.3 – RESTRICT

    Prevents the removal of an external or stored routine definition
    when the routine is referenced by any other object within an
    Oracle Rdb database.

    RESTRICT is the default.

3.4 – routine-name

    Identifies the name of the external or stored routine definition
    in the Oracle Rdb database.

4 – Examples

    Example 1: Deleting an external function definition from an
    Oracle Rdb database

    If you want to alter an external function definition, you must
    first delete it and then create it again with the changes you
    plan. This example shows how to delete the COSINE_F function.

    SQL> DROP FUNCTION cosine_f RESTRICT;

    Example 2: Deleting a routine from a stored module

    The DROP FUNCTION and DROP PROCEDURE statements can be used to
    drop routines from a stored module. If the routine is referenced
    by other objects then the CASCADE option may be required to
    successfully drop the routine.

    See also the DROP FUNCTION and DROP PROCEDURE clauses of ALTER
    MODULE which can be used to perform the same task.

    This example removes a function from the stored module TIME_
    ROUTINES that is no longer in use.

    SQL> set dialect 'sql99';
    SQL> create database filename junk;
    SQL>
    SQL> create module TIME_ROUTINES
    cont>
    cont>     function GET_TIME ()
    cont>     returns TIME (2);
    cont>     return CURRENT_TIME (2);
    cont>
    cont>     function DAY_OF_WEEK (in :dt date)
    cont>     returns VARCHAR(10);
    cont>     return case EXTRACT (weekday from :dt)
    cont>             when 1 then 'Monday'
    cont>             when 2 then 'Tuesday'
    cont>             when 3 then 'Wednesday'
    cont>             when 4 then 'Thursday'
    cont>             when 5 then 'Friday'
    cont>             when 6 then 'Saturday'
    cont>             when 7 then 'Sunday'
    cont>             else '***'
    cont>            end;
    cont>
    cont> end module;
    SQL>
    SQL> show module TIME_ROUTINES;
    Information for module TIME_ROUTINES

     Header:
     TIME_ROUTINES
     No description found
     Module ID is: 1

     Routines in module TIME_ROUTINES:
         DAY_OF_WEEK
         GET_TIME

    SQL> drop function GET_TIME cascade;
    SQL> show module TIME_ROUTINES;
    Information for module TIME_ROUTINES

     Header:
     TIME_ROUTINES
     No description found
     Module ID is: 1

     Routines in module TIME_ROUTINES:
         DAY_OF_WEEK

    SQL>