Скачать или смотреть Deriving Functions from Tables in PLSQL

Learn how to dynamically derive functions from tables in PLSQL, enhancing your database procedures with this practical guide.
---
This video is based on the question https://stackoverflow.com/q/63021841/ asked by the user 'Deepak Sharma' ( https://stackoverflow.com/u/12354924/ ) and on the answer https://stackoverflow.com/a/63023745/ provided by the user 'William Robertson' ( https://stackoverflow.com/u/230471/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Derive function from table in PLSQL

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Deriving Functions from Tables in PLSQL: A Step-by-Step Guide

When working with PL/SQL, you might find yourself needing to perform various operations dynamically based on table data. If you've created a simple function to add two numbers but now need to call different operations defined in a table, you might run into some challenges. In this post, we'll tackle the problem of calling functions dynamically in PL/SQL and provide a practical solution that you can implement right away.

The Problem: Dynamic Function Calls

You’ve successfully created a function called Addition that adds two numbers:

[[See Video to Reveal this Text or Code Snippet]]

However, you now have a table structured as follows:

[[See Video to Reveal this Text or Code Snippet]]

You want to loop through the rows of this table and apply the corresponding functions to the inputs. But when you try to execute the following PL/SQL block, you encounter an error:

[[See Video to Reveal this Text or Code Snippet]]

The issue arises because PL/SQL does not support directly calling functions by their names stored as strings in a table. So, how do we solve this?

The Solution: Create a Generalized Arithmetic Function

Instead of keeping separate functions for every arithmetic operation, we can design a single function that takes in the operation type as a parameter along with the two numbers. This way, the function can execute different operations based on the input.

Step 1: Define the Generalized Function

Here’s how you can create a generalized function called do_the_arith:

[[See Video to Reveal this Text or Code Snippet]]

Step 2: Create Sample Data and Execute the Function

Next, we can simulate some data directly in SQL using a Common Table Expression (CTE). Here's an example of how to write the query and utilize our new function:

[[See Video to Reveal this Text or Code Snippet]]

Example Output

After running the above SQL query, you should expect to see results like these:

[[See Video to Reveal this Text or Code Snippet]]

Conclusion

By consolidating your arithmetic operations into a single function that accepts an operation type, you can dynamically perform calculations based on the values in your table without running into errors. This method enhances the maintainability of your code and makes it easier to apply algorithmically diverse operations in PL/SQL.

Feel free to adapt this approach to suit your specific needs, and happy coding!