An important part of being able to automate processing within a database is through the creation of stored procedures or packages. I have more experience with Oracle where I have created and worked on a variety of packages. So some example so you can see how useful they can be:
- Fuzzy matching algorithm between large datasets (would like to try this in python, or even better replace with a machine learning algorithm / neural network).
- Simulation processing for thousands of scenarios over multiple years.
- Extracting, Transforming and Loading data (ETL) from transaction databases into a data warehouse.
- Automation of reporting through processing and aggregation of data.
Stored procedures and, in my opinion even more sore packages in Oracle, can be incredibly powerful and they are often overlooked. Sure you can manipulate data using pandas but sometimes what is complex and time consuming in pandas could be a simple and easy task in SQL. One good thing, it only takes a single line of code from python to execute them (assuming everything is set up of course).
The example:
The example below is a very simple procedure that you would never create as you can do basic arithmetic in SQL code. However the below does show a good example of how you can create stored procedures, run code, input variables and drop and insert into tables.
The Code:
No comments:
Post a Comment