Chapter 9. User Defined Routines

Knowledge to identify usage of User Defined Functions

[Note]

Functional types of routines

There are different functional types of routines. Each functional type provides support for invoking routines from different interfaces for different purposes. Each functional type of routine provides a different set of features and SQL support.

  • Procedures, also called stored procedures, serve as sub-routine extensions to client applications, routines, triggers, and dynamic compound statements. Procedures are invoked by executing the CALL statement with a reference to a procedure. Procedures can have input, output, and input-output parameters, can execute a wide variety of SQL statements, and return multiple result sets to the caller.

  • Functions are relationships between sets of input data values and a set of result values. Functions enable you to extend and customize SQL. Functions are invoked from within elements of SQL statements such as a select-list, expression, or a FROM clause. There are four types of functions: aggregate functions, scalar functions, row functions, and table functions.

  • Methods allow you to access user-defined type attributes as well as to define additional behaviors for user-defined types. A structured type is a user-defined data type containing one or more named attributes, each of which has a data type. Attributes are properties that describe an instance of a type. A geometric shape, for example, might have attributes such as its list of Cartesian coordinates. A method is generally implemented for a structured type as an operation on the attributes of the structured type. For a geometric shape a method might calculate the volume of the shape.

Routines: Functions

Functions are relationships between sets of input data values and a set of result values. They enable you to extend and customize SQL. Functions are invoked from within elements of SQL statements such as a select-list or a FROM clause. There are four types of functions:

  1. Aggregate functions

    Also called a column function, this type of function returns a scalar value that is the result of an evaluation over a set of like input values. The similar input values can, for example, be specified by a column within a table, or by tuples in a VALUES clause. This set of values is called the argument set. For example, the following query finds the total quantity of bolts that are in stock or on order by using the SUM aggregate function:

    								
    SELECT SUM (qinstock + qonorder)
    FROM inventory
    WHERE description LIKE '%Bolt%'
    
    								

  2. Scalar functions

    A scalar function is a function that, for each set of one or more scalar parameters, returns a single scalar value. Examples of scalar functions include the LENGTH function, and the SUBSTR function. Scalar functions can also be created that do complex mathematical calculations on function input parameters. Scalar functions can be referenced anywhere that an expression is valid within an SQL statement, such as in a select-list, or in a FROM clause. The following example shows a query that references the built-in LENGTH scalar function:

    SELECT lastname, LENGTH(lastname)
    FROM employee
    								

  3. Row functions

    A row function is a function that for each set of one or more scalar parameters returns a single row. Row functions can only be used as a transform function mapping attributes of a structured type into built-in data type values in a row.

  4. Table functions

    Table functions are functions that for a group of sets of one or more parameters, return a table to the SQL statement that references it. Table functions can only be referenced in the FROM clause of a SELECT statement. The table that is returned by a table function can participate in joins, grouping operations, set operations such as UNION, and any operation that could be applied to a read-only view. The following example demonstrates an SQL table function that updates an inventory table and returns the result set of a query on the updated inventory table:

    CREATE FUNCTION updateInv(itemNo VARCHAR(20), amount INTEGER)
       RETURNS TABLE (productName VARCHAR(20),
                      quantity INTEGER)
       LANGUAGE SQL
       MODIFIES SQL DATA
       BEGIN ATOMIC
     
         UPDATE Inventory as I
           SET quantity = quantity + amount
             WHERE I.itemID = itemNo;
     
         RETURN
           SELECT I.itemName, I.quantity
             FROM Inventory as I
               WHERE I.itemID = itemNo;
       END
    								

There are system-defined functions that are ready-to-use, or users can create user-defined functions (UDF). Functions can be implemented as SQL functions or as external functions. SQL functions are easy to implement. External methods provide support for flexible logic implementation, allow a user to develop method logic in their preferred programming language, and provide the ability to store intermediate values.

Professional hosting     Belorussian informational portal         Free SCWCD 1.4 Study Guide     Free SCDJWS 1.4 Study Guide     SCDJWS 1.4 Quiz     Free IBM Certified Associate Developer Study Guide     IBM Test 000-287. Enterprise Application Development with IBM WebSphere Studio, V5.0 Study Guide     SCDJWS 5.0 Quiz