原来function里可以DML的

Wayne posted @ Wed, 06 Jul 2011 11:55:08 +0000 in Experience , 3190 readers

一直以为function里面是不能做除了Select之外的DML的,除了自治事务,并将其当作与Procedure的主要区别。这个从道理上也是讲的通的,因为function应当具有被多次执行而结果一致的特点,倘若做了insert,delete,update之类的事情,每次执行结果必然不同。因此,我坚信不移了。

没想到,今天在查阅同事写的已有的系统模块时,发现他的大量function都是带着Insert,update等操作的,并且还有OUT类型的参数!与procedure的唯一区别仅仅在于多了个return的值。当时我就疑问了,这执行不报错吗?我想顺手写个语句测试下,却发现带有out类型参数的,我都没法直接写SQL。于是乎,我自己另行写了个:

CREATE FUNCTION insert_new( new_val IN NUMBER) RETURN NUMBER

IS

BEGIN

  INSERT INTO testtable(col) VALUES( new_val);

  return 1;

END insert_new;

然后用SQL调用:

 SELECT insert_new(1) FROM DUAL;

如我所料,报错了,说不能执行这类DML云云。

带着我的疑问,去找同事询问。他表示,可以执行啊。然后边说边打开了一个PLSQL DEVELOPER的Test Window,输入几个参数,然后点击执行,居然果真成功执行了。

仔细观察了两个结果,最后终于发现了不同点。Test Window所用的是一个PLSQL Block,用了赋值语句来接收函数值。我仔细查了查书,发现书上原来写的也很清楚,长期以来我所坚信的那些限制都只是“在SQL中调用自定义函数”的限制,在PLSQL Block中并不适用。

NCERT science Exemp said:
Wed, 27 Sep 2023 08:56:13 +0000

You can check those NCERT Science Exemplar Problems 2024 here, This Page also Provide NCERT 7th Science Exemplar 2024, NCERT Students you can Subject Wise Pdf Download at Official Website at, Our website you will get NCERT Class 7th Science Exemplar Problems 2024 for All Chapters, Every Exemplar Problems and Solutions has been Provided with a Detailed Explanation, All the Exemplar Solutions & Exercises NCERT science Exemplar Solutions for 7th 2024 Provided in NCERT Exemplar are very important to Prepare for NCERT 7th Exam 2024.Here you will get the NCERT Class 7 Science Exemplar Problems and Solutions 2024 for All Pdf Chapters, All the Questions are Provided with an easy and Logical Explanation so as to give Students Understanding of the Exemplar Problems.


Login *


loading captcha image...
(type the code from the image)
or Ctrl+Enter