Procedures and Functions

PROCEDURES AND FUNCTIONS

in Oracle it is possible to create a procedure or function that is compiled and stored in the database
can then be called in the same manner as the built-in functions
similar to functions found in third generation languages
use a function if a value is to be returned

SYNTAX

procedure statements

END;

function statements

END;

Procedure Example:

The following procedure adds a row to the stock table: create or replace procedure add_stock
     (stsym stock.stocksymbol%type,
      stname stock.stockname%type,
      price stock.currentprice%type)
      as
      begin
            insert into stock values (stsym, stname, price);
      end;
/

Function Example:

The following function returns the total value of the stock in a given portfolio; the portfolio number is passed as a parameter: create or replace function current_value
     (portno portfolio.portfoliono%type) return number
     as
        total_value number;
     begin
        select sum(numbershares * currentprice)
            into total_value
            from holding, stock
            where holding.stocksymbol = stock.stocksymbol
                    and portfoliono = portno;
        return (total_value);
     end;
/

Debugging Note:

If you get this type of message:

Warning: Function created with compilation errors.

use the SQL*PLUS command SHOW ERRORS to display the errors.

Data Dictionary Entries for Functions and Procedures:

USER_OBJECTS:

select using OBJECT_TYPE FUNCTION or PROCEDURE

USER_SOURCE:

contains the source code for all functions and procedures

Executing Procedures:

Once a procedure has been created, it can be called from within a PL/SQL block. The procedure call is similar to calls in 3GL languages. The following code will call the ADD_STOCK procedure created above:

    BEGIN
        add_stock('RIM', 'Research in Motion', 66.90);
    END;

From within SQL*Plus, a procedure can be executed using the EXECUTE command. (Note: this command can not be used in PL/SQL). To execute the ADD_STOCK command in SQL*Plus:

execute add_stock('RIM', 'Research in Motion', 66.90);

Executing Functions:

Functions are used in the same manner as the built-in scalar functions provided by Oracle. Since a function returns a value, it must be used as part of an expression. The following example calls the CURRENT_VALUE function. The SELECT statement would display all the portfolios in the Portfolio table and the total value of the shares held in each portfolio:

select portfoliono, current_value(portfoliono) from portfolio;