User Defined Functions — Complete Guide
User Defined Functions — Complete Guide: free step-by-step lesson with examples, common mistakes, and interview tips — part of MySQL Tutorial on Toolliyo Academy.
On this page
MySQL Tutorial · Lesson 52 of 100
User Defined Functions
Basics ✓ → Advanced
Advanced · 2 — Production · ~6 min · MySQL — Stored Procedures & Triggers
What is this?
User-defined functions (UDFs) return a single value per call and can be used in SELECT expressions. Unlike procedures, they cannot modify data (no INSERT/UPDATE inside).
Why should you care?
Reusable GST calculation in SQL reports keeps finance spreadsheets aligned with app tax logic.
See it live — copy this example
Run in MySQL Workbench or the mysql CLI.
DELIMITER //
CREATE FUNCTION fn_dataflow_gst_inr(base_inr DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
RETURN ROUND(base_inr * 0.18, 2);
//
DELIMITER ;
SELECT order_ref, total_inr, fn_dataflow_gst_inr(total_inr) AS gst
FROM orders LIMIT 5;What happened?
- Function multiplies by 18% GST and rounds.
- Used inline in SELECT like built-in functions.
- DETERMINISTIC marks pure math — required for some replication setups.
Practice next
- CREATE FUNCTION as above.
- SELECT with fn in column list.
- Try INSERT inside function — rejected.
- Add slashed rate param with second function variant.
- Use in ORDER BY fn_dataflow_gst_inr(total_inr) DESC.
Remember
UDFs return scalar values in queries. No data modification inside UDF body. Good for shared calculations.
DataFlow invoice export
Finance CSV adds GST column via UDF — same rate as checkout service.
Outcome: Audit matches app without duplicate Python formula.
Interview prep for this lesson
Practice these questions aloud after reading—each links to a full structured answer.
Sign in to ask a question or upvote helpful answers.
No questions yet — be the first to ask!
