EnterpriseDB: Stored Procedures Oracle Tips by Burleson Consulting

A stored procedure is procedural code that is compiled and stored in the database.  You call the stored procedure from other stored procedures, client applications or from SQL.  Chapter 4, Programming in EnterpriseDB, will go into tremendous detail of EnterpriseDB's procedural language, SPL and will touch briefly on creating stored procedures in several other languages that are available inside EnterpriseDB.

PROCEDUREs, FUNCTIONs and PACKAGEs are all collectively called "stored procedures".  A more accurate name might be "stored code" but for some reason they have always been called "stored procedures".

CREATE PROCEDURE/FUNCTION/PACKAGE

Because I will be going into so much more detail in the next chapter, I will just show some very basic SPL syntax examples here.

Here is a function that returns the string, 'Hello World'.

CREATE OR REPLACE FUNCTION make_output RETURN VARCHAR2 AS
BEGIN
  RETURN 'Hello World';
END;

And here is a procedure that calls the make_output function defined above and displays it to the terminal.

CREATE OR REPLACE PROCEDURE do_output AS
BEGIN
  DBMS_OUTPUT.put_line( make_output );
END;

Here is a package that combines the above two stored procedures into a single package.

CREATE OR REPLACE PACKAGE my_output_maker AS
  FUNCTION make_output RETURN VARCHAR2;
  PROCEDURE do_output;
END;

CREATE OR REPLACE PACKAGE BODY my_output_maker AS
  FUNCTION make_output RETURN VARCHAR2 AS
  BEGIN
    RETURN 'Hello World';
  END;
  PROCEDURE do_output AS
  BEGIN
    DBMS_OUTPUT.put_line( make_output );
  END;
END;

ALTER FUNCTION

You can alter a function to rename it or assign a new owner but it is normally easier to just recreate the function.

GRANT PROCEDURE/FUNCTION/PACKAGE

You can grant execute on functions, procedures or packages to allow someone to run them.

GRANT EXECUTE ON make_output TO BOB;
GRANT EXECUTE ON do_output TO JSCHMOE;
GRANT EXECUTE ON my_output_maker() TO BILL;

You do not need to grant execute on the individual functions and procedures in a package.  Grant execute on the package and all functions and procedures will be executable by the user.

REVOKE PROCEDURE/FUNCTION/PACKAGE

Revoke works like it does on most everything else.

REVOKE EXECUTE ON FUNCTION make_output FROM BOB;
REVOKE EXECUTE ON PROCEDURE do_output FROM JSCHMOE;
REVOKE EXECUTE ON PACKAGE my_output_maker FROM BILL;

DROP PROCEDURE/FUNCTION/PACKAGE

You can drop a stored procedure using the DROP command.

DROP PROCEDURE do_output;
DROP FUNCTION make_output;
DROP PACKAGE my_output_maker;

This is an excerpt from the book "EnterpriseDB: The Definitive Reference" by Rampant TechPress.