Oracle 9i Procedures and Functions Page - 2

Page 2 of 2

Author : Exforsys Inc. Published on: 13th Mar 2005 | Last Updated on: 7th Jan 2011

Oracle 9i Procedures and Functions

Functions:

A function is a PL/SQL subprogram, which is used to compute a value. Function is same like a procedure except for the difference that it have RETURN clause.

Syntax for Function

Ads

Examples

Function without arguments

Function with arguments. Different ways of executing the function.

Privileges for creation and execution of procedures

To create a procedure in current user schema we use the syntax

CREATE OR REPLACE PROCEDURE (<argslist) IS
-------
-------

To create procedure in any schema we use

CREATE OR REPLACE ANY PROCEDURE (<argslist) IS
-------
-------

Ads

To grant EXECUTE privilege to some user on a procedure we write
GRANT EXECUTE ON <procedure-name> TO <user-name>

SQL> SELECT * FROM USER_PROCEDURES;
Lists all the procedures, functions in current user.

SQL> SELECT * FROM USER_PROCEDURES;
Lists all the procedures, functions in all users.

SQL> DROP PROCEDURE <procedure-name> ;
Drops or Removes a procedure from database.

SQL> DROP FUNCTION <function-name> ;
Drops or Removes a function from database.

1 2

Read Next: Oracle 9i Database Triggers

This tutorial is part of a Oracle 9i Training tutorial series. Read it from the beginning and learn yourself.

Oracle 9i Training