为何UDF仅支持SELECT语句而禁止INSERT/UPDATE/DELETE？示例无效原因解析

Awesome question—let’s break down this restriction clearly, starting with why your specific function fails.

First, your CREATE FUNCTION test(..) example is invalid because it includes an INSERT statement, which violates a fundamental rule for user-defined functions: UDFs are strictly read-only. They can’t modify database state through Data Manipulation Language (DML) operations like INSERT, UPDATE, or DELETE.

Now, why does this rule exist? Here are the key reasons:

• UDFs are meant to be predictable, side-effect-free functions
  Think of UDFs like functions in regular programming languages—you pass inputs, you get a consistent output based on those inputs, and they shouldn’t change anything outside their scope. When you run an INSERT, you’re altering the database (a "side effect"). Imagine using this UDF in a SELECT query that returns 100 rows—you’d accidentally insert 100 duplicate entries into EMPLOYEE without meaning to. That’s a recipe for bugs, data inconsistencies, and hard-to-debug behavior.

• UDFs integrate directly into query execution plans
  Databases optimize queries by reordering operations, caching results, or parallelizing work. If a UDF could modify data, the optimizer couldn’t safely do any of this. For example, if your UDF was used in a WHERE clause, the database might run it dozens of times during query execution, leading to repeated, unintended data changes. Stored procedures don’t have this problem because they’re executed as standalone operations, not embedded in query plans.

• It aligns with relational database principles
  Relational databases separate read operations (like SELECT) from write operations (DML). UDFs are designed to compute values from existing data—they’re tools for querying, not modifying. Stored procedures are the right tool for executing sequences of operations that include DML, transactions, or other state-changing logic.

If you need to run that INSERT logic, rewrite it as a stored procedure instead. SPs are built exactly for this kind of state-modifying work.

内容的提问来源于stack exchange，提问作者ghostrider