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>