ITPUB论坛-中国最专业的IT技术社区

 找回密码
 注册
查看: 5514|回复: 10

[每日一题] PL/SQL Challenge 每日一题:2017-7-24 自定义函数

[复制链接]
论坛徽章:
480
榜眼
日期:2015-09-09 10:34:21秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12状元
日期:2015-11-23 10:04:09举人
日期:2015-11-23 10:04:09
发表于 2017-7-27 03:25 | 显示全部楼层 |阅读模式
(原发表于 2011-7-25)

最先答对且答案未经编辑的puber将获得纪念章一枚(答案不可编辑但可发新贴补充或纠正),其他会员如果提供有价值的分析、讨论也可获得纪念章一枚。

每两周的优胜者可获得itpub奖励的技术图书一本。

以往旧题索引:
http://www.itpub.net/forum.php?m ... eid&typeid=1808

原始出处:
http://www.plsqlchallenge.com/

作者:KenHolmslykke

运行环境:SQLPLUS, SERVEROUTPUT已打开
注:本题给出答案时候要求给予简要说明才能得到奖品

我创建了如下的表和数据:

CREATE TABLE plch_employees
(
   employee_id INTEGER
, last_name   VARCHAR2(100)
, salary      NUMBER
)
/

BEGIN
   INSERT INTO plch_employees
     VALUES (100, 'Jobs', 1000000);

   INSERT INTO plch_employees
     VALUES (200, 'Ellison', 1000000);

   INSERT INTO plch_employees
     VALUES (300, 'Gates', 1000000);

   COMMIT;
END;
/

然后我创建了下列函数:

CREATE OR REPLACE FUNCTION plch_new_salary (
   employee_id_in IN plch_employees.employee_id%TYPE)
   RETURN plch_employees.salary%TYPE
AS
   PRAGMA AUTONOMOUS_TRANSACTION;

   l_salary   plch_employees.salary%TYPE;
BEGIN
   UPDATE plch_employees
      SET salary = salary * 2;

   COMMIT;

   SELECT salary
     INTO l_salary
     FROM plch_employees
    WHERE employee_id = plch_new_salary.employee_id_in;

   RETURN l_salary;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      RETURN 0;
END;
/

在我执行下列代码块之后:

BEGIN
   FOR emp_rec IN (  SELECT *
                       FROM (SELECT salary
                                  , plch_new_salary (employee_id) new_salary
                               FROM plch_employees)
                   ORDER BY new_salary)
   LOOP
      DBMS_OUTPUT.put_line (
            'Salary changed from '
         || emp_rec.salary
         || ' to '
         || emp_rec.new_salary);
   END LOOP;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (SQLERRM);
END;
/

屏幕上会显示什么?

(A)
ORA-14551: cannot perform a DML operation inside a query

(B)
Salary changed from 1000000 to 2000000
Salary changed from 1000000 to 4000000
Salary changed from 1000000 to 8000000

(C)
Salary changed from 1000000 to 2000000
Salary changed from 1000000 to 2000000
Salary changed from 1000000 to 2000000

(D)
Salary changed from 1000000 to 2000000
Salary changed from 2000000 to 4000000
Salary changed from 4000000 to 8000000

论坛徽章:
31
SQL大赛参与纪念
日期:2011-04-13 12:08:17秀才
日期:2017-08-23 14:10:21技术图书徽章
日期:2017-08-23 14:17:18技术图书徽章
日期:2017-08-23 14:17:18秀才
日期:2017-09-08 11:09:04秀才
日期:2017-09-08 11:09:04秀才
日期:2017-09-08 11:09:04秀才
日期:2017-09-18 17:04:35秀才
日期:2017-09-25 15:09:50秀才
日期:2017-09-25 15:09:50
发表于 2017-7-27 08:21 | 显示全部楼层
A
在执行完题中的SQL之后,我又执行了一个SQL:
SELECT
    salary,
    plch_new_salary (employee_id) new_salary,
    plch_new_salary (employee_id) new_salary2,
    salary salary2
FROM
    plch_employees
得到结果是
    SALARY NEW_SALARY NEW_SALARY2  SALARY2
---------- ---------- ----------- -----------
   8000000   16000000    32000000 8000000
   8000000   64000000   128000000 8000000
   8000000  256000000   512000000 8000000
我理解,salary总保持最初的值是因为结果集存在内存中,没有用到自定义函数的salary直接从内存取值,所以值总是保持不变。
而调用了自定义函数的部分,每次都会重新执行,只把每次执行的返回值放到内存中。

使用道具 举报

回复
论坛徽章:
15
秀才
日期:2017-08-11 15:30:05技术图书徽章
日期:2017-08-23 14:17:00秀才
日期:2017-08-11 15:30:05秀才
日期:2017-08-11 15:30:05秀才
日期:2017-08-11 15:30:05秀才
日期:2017-08-11 15:30:05秀才
日期:2017-08-11 15:30:05秀才
日期:2017-08-11 15:30:05秀才
日期:2017-08-11 15:30:05秀才
日期:2017-08-11 15:30:05
发表于 2017-7-27 09:31 | 显示全部楼层
答案 B

3个员工的初始工资都是 1000000,因为函数是自治事务函数,所以给薪水加倍的这个存储过程每次都会执行。
因为自治事务的独立性,此时表的数据其实是没有改的。所以此时表内的工资没有变化,除非在主事务提交。

但是自治事务修改后的结果会保存在内存中

for循环运行一次,工资加倍一次,所以答案是B。

使用道具 举报

回复
论坛徽章:
31
SQL大赛参与纪念
日期:2011-04-13 12:08:17秀才
日期:2017-08-23 14:10:21技术图书徽章
日期:2017-08-23 14:17:18技术图书徽章
日期:2017-08-23 14:17:18秀才
日期:2017-09-08 11:09:04秀才
日期:2017-09-08 11:09:04秀才
日期:2017-09-08 11:09:04秀才
日期:2017-09-18 17:04:35秀才
日期:2017-09-25 15:09:50秀才
日期:2017-09-25 15:09:50
发表于 2017-7-27 09:42 | 显示全部楼层
我写错了,是B。。。。随手就写成A了

使用道具 举报

回复
论坛徽章:
15
秀才
日期:2017-08-11 15:30:05技术图书徽章
日期:2017-08-23 14:17:00秀才
日期:2017-08-11 15:30:05秀才
日期:2017-08-11 15:30:05秀才
日期:2017-08-11 15:30:05秀才
日期:2017-08-11 15:30:05秀才
日期:2017-08-11 15:30:05秀才
日期:2017-08-11 15:30:05秀才
日期:2017-08-11 15:30:05秀才
日期:2017-08-11 15:30:05
发表于 2017-7-27 10:00 | 显示全部楼层
我的判断理由是错误的

我重新运行了一次,结果和二楼说的一样。

使用道具 举报

回复
论坛徽章:
31
SQL大赛参与纪念
日期:2011-04-13 12:08:17秀才
日期:2017-08-23 14:10:21技术图书徽章
日期:2017-08-23 14:17:18技术图书徽章
日期:2017-08-23 14:17:18秀才
日期:2017-09-08 11:09:04秀才
日期:2017-09-08 11:09:04秀才
日期:2017-09-08 11:09:04秀才
日期:2017-09-18 17:04:35秀才
日期:2017-09-25 15:09:50秀才
日期:2017-09-25 15:09:50
发表于 2017-7-27 10:43 | 显示全部楼层
只是甲 发表于 2017-7-27 09:31
答案 B

3个员工的初始工资都是 1000000,因为函数是自治事务函数,所以给薪水加倍的这个存储过程每次都 ...

看了你的说明,对于自治事务的提交,我有点疑问,在这个代码块块里,主事务的提交具体指什么?select结束?END LOOP?还是代码块的END?

使用道具 举报

回复
论坛徽章:
15
秀才
日期:2017-08-11 15:30:05技术图书徽章
日期:2017-08-23 14:17:00秀才
日期:2017-08-11 15:30:05秀才
日期:2017-08-11 15:30:05秀才
日期:2017-08-11 15:30:05秀才
日期:2017-08-11 15:30:05秀才
日期:2017-08-11 15:30:05秀才
日期:2017-08-11 15:30:05秀才
日期:2017-08-11 15:30:05秀才
日期:2017-08-11 15:30:05
发表于 2017-7-27 10:56 | 显示全部楼层
fatfoxz 发表于 2017-7-27 10:43
看了你的说明,对于自治事务的提交,我有点疑问,在这个代码块块里,主事务的提交具体指什么?select结束 ...

我回复了,我后来测试了,我的判断逻辑是错误的。

因为是自治事务,所以每次其实都提交了。但是for循环一次,就运行一次,我真没想到。

因为自治事务的提交不影响主事务。  所以查到的薪水都是初始状态的薪水。而改变后的薪水是成倍增加的。

这个主事务的概念,估计要newkid来看看了

使用道具 举报

回复
论坛徽章:
31
SQL大赛参与纪念
日期:2011-04-13 12:08:17秀才
日期:2017-08-23 14:10:21技术图书徽章
日期:2017-08-23 14:17:18技术图书徽章
日期:2017-08-23 14:17:18秀才
日期:2017-09-08 11:09:04秀才
日期:2017-09-08 11:09:04秀才
日期:2017-09-08 11:09:04秀才
日期:2017-09-18 17:04:35秀才
日期:2017-09-25 15:09:50秀才
日期:2017-09-25 15:09:50
发表于 2017-7-27 10:59 | 显示全部楼层
只是甲 发表于 2017-7-27 10:00
我的判断理由是错误的

我重新运行了一次,结果和二楼说的一样。

Sorry,写6楼问题的时候没看到你在5楼的回复。。。
当时在想各种办法确认自治事务的commit是否即时生效。
由于解释的分歧,我在确认过程中又学到一些东西,谢谢!

使用道具 举报

回复
论坛徽章:
15
秀才
日期:2017-08-11 15:30:05技术图书徽章
日期:2017-08-23 14:17:00秀才
日期:2017-08-11 15:30:05秀才
日期:2017-08-11 15:30:05秀才
日期:2017-08-11 15:30:05秀才
日期:2017-08-11 15:30:05秀才
日期:2017-08-11 15:30:05秀才
日期:2017-08-11 15:30:05秀才
日期:2017-08-11 15:30:05秀才
日期:2017-08-11 15:30:05
发表于 2017-7-27 11:00 | 显示全部楼层
fatfoxz 发表于 2017-7-27 10:43
看了你的说明,对于自治事务的提交,我有点疑问,在这个代码块块里,主事务的提交具体指什么?select结束 ...

刚测试了下,我去掉自治事务

环境: 11.2.0.4

结果为A

SQL> BEGIN
  2     FOR emp_rec IN (  SELECT *
  3                         FROM (SELECT salary
  4                                    , plch_new_salary (employee_id) new_salary
  5                                 FROM plch_employees)
  6                     ORDER BY new_salary)
  7     LOOP
  8        DBMS_OUTPUT.put_line (
  9              'Salary changed from '
10           || emp_rec.salary
11           || ' to '
12           || emp_rec.new_salary);
13     END LOOP;
14  EXCEPTION
15     WHEN OTHERS
16     THEN
17        DBMS_OUTPUT.put_line (SQLERRM);
18  END;
19  /
ORA-14551: 无法在查询中执行 DML 操作

PL/SQL 过程已成功完成。

使用道具 举报

回复
论坛徽章:
31
SQL大赛参与纪念
日期:2011-04-13 12:08:17秀才
日期:2017-08-23 14:10:21技术图书徽章
日期:2017-08-23 14:17:18技术图书徽章
日期:2017-08-23 14:17:18秀才
日期:2017-09-08 11:09:04秀才
日期:2017-09-08 11:09:04秀才
日期:2017-09-08 11:09:04秀才
日期:2017-09-18 17:04:35秀才
日期:2017-09-25 15:09:50秀才
日期:2017-09-25 15:09:50
发表于 2017-7-27 13:13 | 显示全部楼层
由于我在2楼不小心把结果选项写错了,所以重新整理一下我的回答
答案是B,

我觉得我的SQL比题目的代码块更容易说明这个问题
SELECT
     salary,
     plch_new_salary (employee_id) new_salary,
     plch_new_salary (employee_id) new_salary2,
     salary salary2
FROM
     plch_employees
得到结果是
    SALARY NEW_SALARY NEW_SALARY2  SALARY2
---------- ---------- ----------- -----------
    8000000   16000000    32000000 8000000
    8000000   64000000   128000000 8000000
    8000000  256000000   512000000 8000000
我理解,SQL在执行完FROM和where后,已经构成了一个临时表,单纯的select只是从临时表取得并描述检索结果。
而在select中调用的自治事务是独立运行的,每次调用,update和commit都即时更新数据库,自治事务的select也即时取得最新的数据并返回到外面的检索结果中
又由于检索结果有三行,每行调用两次,所以我的SQL的结果有6次加倍。
而检索结果中的SALARY和SALARY2都从临时表取值,所以不会随着DB中的值被更新而更新。

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则

TOP技术积分榜 社区积分榜 徽章 电子杂志 团队 统计 虎吧 老博客 知识索引树 读书频道 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档 | IT博客
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛 | SAP ERP系统
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 网站律师 隐私政策 知识产权声明
京ICP备16024965号 北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表