Your First PL/SQL Function

by William Ryan

PL/SQL is a pretty powerful langauge that allows you to do many things.  One of those things is creating functions.  You can do some amazing things with them, and the complexity level is up to you.  However, the intent of this article isn't to show you how hard it is to create Functions, it's to show you how easy it is.

The first thing you do is the standard CREATE OR REPLACE syntax (I really wish Microsoft would copy this construct into T-SQL).

CREATE OR REPLACE FUNCTION someFunction(p_Parameter1 NUMBER, p_Parameter2 VARCHAR) RETURN NUMBER

All this line did was declare the function and supply a parameter list.  Next, we define any local variables that we many need

IS    v_SalesTax NUMBER(3,2)    v_Total NUMBER(10) Next, we write our code out BEGIN   SELECT PRI.Price * v_SalesTax    INTO v_Total     FROM Products PRO       , PRICES PRI         WHERE   PRO.ItemID = PRI.ItemID          AND SomeFied > SomeValue            RETURN v_Total END; /

This is pretty straightforward.  All we do is declare two variables, v_SalesTax, v_Total.  From there, we fire a basic query and load the result of Price * Sales tax into v_Total which we then use as the return value.  If you are familiar with T-SQL Programming, you use SELECT INTO in roughly the same fashion as SET but that's about all there is to it.