blocks|key|1438757|text|为了总结这些注释，可以在PL/函数中使用。|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|1438758|您不能做的是从SQL调用该函数，因为select语句也不应该将更新和删除等应用于隐藏的副作用。|CODE|1438759|首先，SQL语言保留按任何顺序以任何方式执行查询的权利，以及它决定使用的任何缓存。(它甚至可能在执行期间停止并重新启动。这取决于SQL引擎。)因此，您的函数可能会按任何顺序被调用一次或一百次，这取决于执行计划，因此结果是不可预测的。|1438760|entityMap^0|9|6|0|I|6|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|P|8|@$9|Q|A|R|B|H]]|D|@]|E|$]]|$1|I|3|J|5|6|7|S|8|@]|D|@]|E|$]]|$1|K|3|-4|5|6|7|T|8|@]|D|@]|E|$]]]|L|$]]

Just to summarise the comments, you can have DML inside a PL/SQL function. 

What you can't do is call that function from SQL, because a <code>select</code> statement shouldn't also apply updates and deletes and so on as a hidden side effect.

For one thing, the SQL language reserves the right to execute the query any way it chooses, in any order, and with any caching it decides make use of. (It might even stop and restart during execution. That's up to the SQL engine.) Therefore your function might get called once or a hundred times, in any order, depending on the execution plan, and so the results would be unpredictable.

blocks|key|1438824|text|函数中的insert语句缺少values关键字；|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1438825|insert+into+EMPLOYEE('22',12000,'john');|code-block|syntax|javascript|1438826|应该是|1438827|insert+into+EMPLOYEE+values+('22',12000,'john');|1438828|不过，最好也包括列名列表。从代码的一小部分来看，这是唯一无效的部分。您遗漏的位中可能有其他错误。(如果表中的第一列是数字列，则不应该传递字符串--它可以工作，但可以进行隐式转换，最好避免。如果这个列是一个字符串，它真的应该是吗？)|1438829|UDF只能有select语句，而不能插入/更新/删除语句。|blockquote|1438830|那是不对的。可以在函数中使用DML+(insert/update/delete)，但只能从PL/SQL上下文调用DML(尽管在PL/SQL中，人们常说函数应该查询数据而不产生副作用，只有过程应该修改数据；但这不受语言本身的限制)：|1438831|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++++++|1438832|但是不能从SQL上下文调用它：|1438833|select+test(42)+from+dual;

ORA-14551:+cannot+perform+a+DML+operation+inside+a+query+
ORA-06512:+at+"MYSCHEMA.TEST",+line+4|1438834|文档列出对从SQL调用的函数的限制。和将在此警告中详细说明。|1438835|因为SQL是一种声明性语言，而不是命令式(或过程性)语言，所以您无法知道一个SQL语句调用的函数将运行多少次--即使函数是用PL/SQL+(一种命令式语言)编写的。|1438836|如果允许函数执行DML，那么您将无法控制执行DML的次数。例如，如果它正在进行插入，它可能尝试插入同一行两次，或者重复数据，或者违反约束。|1438837|entityMap|0|LINK|mutability|MUTABLE|url|https://docs.oracle.com/en/database/oracle/oracle-database/12.2/lnpls/plsql-subprograms.html#GUID-4FC97312-AC30-4A6E-8337-996198191E29|1|https://docs.oracle.com/en/database/oracle/oracle-database/12.2/adfns/coding-subprograms-and-packages.html#GUID-C35B709B-EE78-4DB4-9B63-54EB8B4D639F^0|E|6|0|0|0|0|0|0|0|0|0|0|2|G|0|J|B|1|0|0|0^^$0|@$1|2|3|4|5|6|7|1H|8|@$9|1I|A|1J|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|1K|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|1L|8|@]|D|@]|E|$]]|$1|M|3|N|5|H|7|1M|8|@]|D|@]|E|$I|J]]|$1|O|3|P|5|6|7|1N|8|@]|D|@]|E|$]]|$1|Q|3|R|5|S|7|1O|8|@]|D|@]|E|$]]|$1|T|3|U|5|6|7|1P|8|@]|D|@]|E|$]]|$1|V|3|W|5|H|7|1Q|8|@]|D|@]|E|$I|J]]|$1|X|3|Y|5|6|7|1R|8|@]|D|@]|E|$]]|$1|Z|3|10|5|H|7|1S|8|@]|D|@]|E|$I|J]]|$1|11|3|12|5|6|7|1T|8|@]|D|@$9|1U|A|1V|1|1W]|$9|1X|A|1Y|1|1Z]]|E|$]]|$1|13|3|14|5|S|7|20|8|@]|D|@]|E|$]]|$1|15|3|16|5|6|7|21|8|@]|D|@]|E|$]]|$1|17|3|-4|5|6|7|22|8|@]|D|@]|E|$]]]|18|$19|$5|1A|1B|1C|E|$1D|1E]]|1F|$5|1A|1B|1C|E|$1D|1G]]]]

The insert statement inside your function is missing the <code>values</code> keyword;

<pre><code>insert into EMPLOYEE('22',12000,'john');
</code></pre>

should be

<pre><code>insert into EMPLOYEE values ('22',12000,'john');
</code></pre>

though it's better to include the list of column names too. From the small part of the code you showed that is the only thing that is invalid. There could be other errors in the bits you have omitted. (If the first column in your table is numeric then you shouldn't be passing a string - it works but does implicit conversion and is best avoided. And if the column is a string, should it be really?)

<blockquote>
 UDF can only have select statements inside it and not insert/update/delete statements
</blockquote>

That is not correct. You can have DML (insert/update/delete) in a function, but you can only call it from a PL/SQL context (though even in PL/SQL, it's often said that functions should query data with no side effects and only procedures should modify data; but that is not restricted by the language itself):

<pre><code>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 
</code></pre>

But you cannot call it from a SQL context:

<pre><code>select test(42) from dual;

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

The documentation <a href="https://docs.oracle.com/en/database/oracle/oracle-database/12.2/lnpls/plsql-subprograms.html#GUID-4FC97312-AC30-4A6E-8337-996198191E29" rel="noreferrer">lists restrictions on functions called from SQL</a>, and <a href="https://docs.oracle.com/en/database/oracle/oracle-database/12.2/adfns/coding-subprograms-and-packages.html#GUID-C35B709B-EE78-4DB4-9B63-54EB8B4D639F" rel="noreferrer">goes into more detail in this warning</a>:

<blockquote>
 Because SQL is a declarative language, rather than an imperative (or procedural) one, you cannot know how many times a function invoked by a SQL statement will run—even if the function is written in PL/SQL, an imperative language.
</blockquote>

If the function was allowed to do DML then you would have no control over how many times that DML was performed. If it was doing an insert, for instance, it might try to insert the same row twice and either duplicate data or get a constraint violation.

One of the main differences between UDF and SP is that UDF can only have select statements inside it and not insert/update/delete statements. Can someone please explain the reason behind this?The below function:

<pre><code>create function test(..)
...
BEGIN 
insert into EMPLOYEE('22',12000,'john');
return 0;
END
</code></pre>

is not valid. But why is this so?

User defined function can only have select statements

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

UDF和SP的主要区别之一是，UDF只能在其中包含select语句，而不能插入/更新/delete语句。有谁能解释一下背后的原因吗?以下功能：create function test(..)...BEGIN insert into EMPLOYEE('22',12000,'john');return 0;END是无效的...

问用户定义的函数只能具有select语句。
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问用户定义的函数只能具有select语句。EN