Stored functions are a powerful tool in database programming for handling complex logic and repetitive code. In the Oracle mode of the GBase 8s database, developers are provided with a special syntax—the WITH FUNCTION statement, which allows the temporary definition and use of stored functions within a single query. This feature enhances the flexibility and efficiency of SQL queries. This article provides a detailed introduction to the use and examples of the WITH FUNCTION statement in the GBase database.

Overview of the WITH FUNCTION Statement

The WITH FUNCTION statement is supported only in the Oracle mode of GBase 8s. It is used to temporarily declare and define stored functions. Functions defined with WITH FUNCTION are not stored in system tables and are only effective within the current SQL statement.

Syntax Illustration

Description and Limitations

• The <WITH FUNCTION> statement defines a function <function_declaration> that is effective only within the <with_select_block> of the SELECT query. The <with_select_block> follows the same syntax rules as other SELECT query blocks in GBase 8s.
• Before the SELECT keyword in a SELECT query statement, you can use <WITH FUNCTION> to define one or more custom functions. These functions can then be used in subsequent SELECT statements, maintaining consistency with the GBase 8s behavior.
• If a custom function with the same name exists in the database, the stored function defined by WITH FUNCTION takes precedence.

Example Code

For instance, the following code declares two temporary stored functions, add_string and doesnt_it, and uses them in a nested manner.

WITH 
FUNCTION add_string(p_string IN VARCHAR2) RETURN VARCHAR2 
IS
    l_buffer VARCHAR2(100); 
BEGIN 
    l_buffer := p_string || ' works!'; 
    RETURN l_buffer; 
END; 
FUNCTION doesnt_it(p_string IN VARCHAR2) RETURN VARCHAR2 
IS 
    l_buffer VARCHAR2(100); 
BEGIN 
    l_buffer := p_string || ' Doesn''t it?'; 
    RETURN l_buffer; 
END; 
SELECT doesnt_it(add_string('Yes, it')) AS outVal FROM DUAL;
/

Result:

OUTVAL
--------
Yes, it works! Doesn't it?

In this example, we define two functions, add_string and doesnt_it, and use them in a nested fashion within the SELECT statement. The final output is Yes, it works! Doesn't it?, demonstrating how to use the WITH FUNCTION statement to achieve complex string operations.

Through this article, you should have gained a deeper understanding of the WITH FUNCTION statement in the GBase 8s database. The ability to temporarily define stored functions can not only simplify complex SQL queries but also enhance code modularity and reusability. In subsequent articles, we will continue to explore other features of the GBase 8s database. Thank you for reading.