5.20 Local functions

---

5.20 Local functions

Oracle allows procedures or functions - called Local Procedures or Local Functions - to be created and referenced within the body of a Stored Procedure. When converting this to DB2, the Local Procedure or Local Function must be created outside of the Stored Procedure that references it, before it can be used. The example below shows how a Local Function created and referenced in an Oracle Stored procedure would be converted to DB2.

Example 5-42 shows the Oracle source code of the function.

Example 5-42: Oracle procedure with Local function

 CREATE OR REPLACE PROCEDURE callLocalFunc AS   /* Local declarations, which include a cursor, variable, and a      function. */   CURSOR c_AllEmployees IS     SELECT first_name, last_name       FROM employees;   v_FormattedName VARCHAR2(50);   /* Function which will return the first and last name      concatenated together, separated by a space. */   FUNCTION FormatName(p_FirstName IN VARCHAR2,                       p_LastName IN VARCHAR2)     RETURN VARCHAR2 IS   BEGIN     RETURN p_FirstName || ' ' || p_LastName;   END FormatName; -- Begin main block. BEGIN   FOR v_EmployeeRecord IN c_AllEmployees LOOP     v_FormattedName :=       FormatName(v_ EmployeeRecord.first_name,                  v_ EmployeeRecord.last_name);     DBMS_OUTPUT.PUT_LINE(v_FormattedName);   END LOOP; END callLocalFunc; 

Example 5-43 shows the DB2 conversion of the function.

Listing 5-43: DB2 Conversion of Oracle Procedure with Local Function

 CREATE FUNCTION FormatName(p_FirstName VARCHAR(20),        --[1]                            p_LastName VARCHAR(20))     RETURNS VARCHAR(41) LANGUAGE SQL BEGIN ATOMIC    RETURN p_FirstName || '' || p_LastName; END! CREATE PROCEDURE callFunc () LANGUAGE SQL BEGIN  DECLARE c_AllEmployees CURSOR WITH RETURN FOR    SELECT FormatName(first_name, last_name) FROM employees;  OPEN c_AllEmployees; END! 

Notes

[1] 

The function must be created before the procedure that references it.

---