时间戳

首页 > 解决方案 > 用户定义的函数只能有选择语句

sql - 用户定义的函数只能有选择语句

问题描述

UDF 和 SP 之间的主要区别之一是 UDF 只能在其中包含 select 语句，而不能包含 insert/update/delete 语句。有人可以解释一下这背后的原因吗？以下功能：

create function test(..)
...
BEGIN 
insert into EMPLOYEE('22',12000,'john');
return 0;
END

无效。但为什么会这样呢？

标签： sqloraclestored-proceduresplsqluser-defined-functions

解决方案

函数内的插入语句缺少values关键字；

insert into EMPLOYEE('22',12000,'john');

应该

insert into EMPLOYEE values ('22',12000,'john');

尽管最好也包括列名列表。从您展示的代码的一小部分来看，这是唯一无效的。您省略的位中可能还有其他错误。（如果表中的第一列是数字，那么您不应该传递字符串 - 它可以工作，但会进行隐式转换，最好避免。如果该列是字符串，真的应该是这样吗？）

UDF 里面只能有 select 语句，不能有 insert/update/delete 语句

这是不正确的。您可以在函数中使用 DML（插入/更新/删除），但您只能从 PL/SQL 上下文中调用它（尽管即使在 PL/SQL 中，也常说函数应该查询数据而没有副作用，只有过程应该修改数据；但这不受语言本身的限制）：

create table employee (id varchar2(3), salary number, name varchar2(10));

Table EMPLOYEE created.

create function test(unused number)
return number as
BEGIN 
  insert into EMPLOYEE (id, salary, name)
  values ('22',12000,'john');
  return 0;
END;
/

Function TEST compiled


declare
  rc number;
begin
  rc := test(42);
end;
/

PL/SQL procedure successfully completed.

select * from employee;

ID      SALARY NAME      
--- ---------- ----------
22       12000 john

但是您不能从 SQL 上下文中调用它：

select test(42) from dual;

ORA-14551: cannot perform a DML operation inside a query 
ORA-06512: at "MYSCHEMA.TEST", line 4

该文档列出了对从 SQL 调用的函数的限制，并在此警告中进行了更详细的说明：

因为 SQL 是一种声明性语言，而不是命令式（或过程式）语言，所以您无法知道 SQL 语句调用的函数将运行多少次——即使该函数是用命令式语言 PL/SQL 编写的。

如果允许该函数执行 DML，那么您将无法控制执行 DML 的次数。例如，如果它正在执行插入操作，它可能会尝试两次插入同一行并重复数据或违反约束。

推荐阅读