Once a subprogram is created in the database, it might require changes due to new or modified requirements, changes in data, programmer errors or refactoring to improve the quality of a code base. In most situations, a stored procedure (or other subprogram) will require changes over time.
create or replace procedure test1 is begin dbms_output.put_line('Hello from a replaced stored procedure'); end; / exec test1 begin test1; end; /
In order to avoid explicitly dropping a stored procedure and re-creating it after each change, the Oracle proprietary REPLACE keyword can be used so that an existing object is replaced by a new one with the same name and type.
create or replace function test2 return varchar2 is begin return 'Hello world from a function!'; end; / select test2 from dual; begin dbms_output.put_line(test2()); end; / drop function test2;
A function is distinguished from a stored procedure in that it returns a value. The type (but not the length or precision) of the return value must be specified, and the RETURN keyword is used to return the value in the body of the function. The function can be called from a query - a stored procedure cannot.