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

 找回密码
 注册
查看: 4565|回复: 4

[每日一题] PL/SQL Challenge 每日一题:2017-3-26 管道函数

[复制链接]
论坛徽章:
482
秀才
日期:2015-09-09 10:33:01秀才
日期: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秀才
日期:2016-01-06 14:10:21
发表于 2017-3-31 05:25 | 显示全部楼层 |阅读模式

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

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

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

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

作者:Steven Feuerstein

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

我执行了这些语句:

CREATE TABLE qz_oceans
(
   ocean_id    INTEGER PRIMARY KEY
, ocean_name  VARCHAR2 (100) UNIQUE
)
/

BEGIN
   INSERT INTO qz_oceans VALUES (100, 'Pacific');
   INSERT INTO qz_oceans VALUES (200, 'Atlantic');
   INSERT INTO qz_oceans VALUES (300, 'Indian');
   INSERT INTO qz_oceans VALUES (400, 'Arctic');
   INSERT INTO qz_oceans VALUES (500, 'Southern');
   COMMIT;
END;
/

CREATE OR REPLACE TYPE qz_ids_t IS TABLE OF INTEGER;
/

CREATE OR REPLACE PACKAGE qz_pkg AUTHID DEFINER
IS
   CURSOR refcur_c
   IS
      SELECT ocean_id FROM qz_oceans;

   TYPE refcur_t IS REF CURSOR
      RETURN refcur_c%ROWTYPE;

   FUNCTION ids (rows_in refcur_t)
      RETURN qz_ids_t
      PIPELINED;
END;
/

哪些选项实现了包体,使得下列查询执行之后:

SELECT 'id=' || COLUMN_VALUE
  FROM TABLE (qz_pkg.ids (
                CURSOR (SELECT ocean_id FROM qz_oceans
                         WHERE ocean_name LIKE '%a%ic')))
/

"id=100" 和 "id=200" 会被显示?

注意:这两个字符串的显示顺序无关紧要。


(A)
CREATE OR REPLACE PACKAGE BODY qz_pkg
IS
   FUNCTION ids (rows_in IN refcur_t)
      RETURN qz_ids_t PIPELINED
   IS
      l_id NUMBER;
   BEGIN
      LOOP
         FETCH rows_in INTO l_id;

         EXIT WHEN rows_in%NOTFOUND;

         IF l_id > 300
         THEN
            UPDATE qz_oceans SET ocean_name = UPPER (ocean_name)
             WHERE ocean_id = l_id;
         ELSE
            PIPE ROW (l_id);
         END IF;
      END LOOP;
      CLOSE rows_in;

      RETURN;
   END;
END;
/

(B)
CREATE OR REPLACE PACKAGE BODY qz_pkg
IS
   FUNCTION ids (rows_in IN refcur_t) RETURN qz_ids_t PIPELINED
   IS   
      PRAGMA AUTONOMOUS_TRANSACTION;
      l_id NUMBER;
   BEGIN
      LOOP
         FETCH rows_in INTO l_id;

         EXIT WHEN rows_in%NOTFOUND;

         UPDATE qz_oceans SET ocean_name = UPPER (ocean_name)
          WHERE ocean_id = l_id;
         COMMIT;

         PIPE ROW (l_id);            
      END LOOP;
      CLOSE rows_in;

      RETURN;
   END;
END;
/

(C)
CREATE OR REPLACE PACKAGE BODY qz_pkg
IS   
   FUNCTION ids (rows_in IN refcur_t) RETURN qz_ids_t PIPELINED
   IS
      PRAGMA AUTONOMOUS_TRANSACTION;
      l_id NUMBER;
   BEGIN
      LOOP
         FETCH rows_in INTO l_id;

         EXIT WHEN rows_in%NOTFOUND;

         UPDATE qz_oceans SET ocean_name = UPPER (ocean_name)
          WHERE ocean_id = l_id;

         PIPE ROW (l_id);            
      END LOOP;
      CLOSE rows_in;

      COMMIT;
      RETURN;
   END;
END;
/

(D)
CREATE OR REPLACE PACKAGE BODY qz_pkg
IS
   FUNCTION ids (rows_in IN refcur_t) RETURN qz_ids_t PIPELINED
   IS
      PRAGMA AUTONOMOUS_TRANSACTION;
      l_id NUMBER;
   BEGIN
      LOOP
         FETCH rows_in INTO l_id;

         EXIT WHEN rows_in%NOTFOUND;

         UPDATE qz_oceans SET ocean_name = UPPER (ocean_name)
          WHERE ocean_id = l_id;

         PIPE ROW (l_id);
      END LOOP;
      CLOSE rows_in;

      RETURN;
   END;
END;
/

(E)
CREATE OR REPLACE PACKAGE BODY qz_pkg
IS
   FUNCTION ids (rows_in IN refcur_t) RETURN qz_ids_t PIPELINED
   IS
      l_id NUMBER;
   BEGIN
      LOOP
         FETCH rows_in INTO l_id;

         EXIT WHEN rows_in%NOTFOUND;

         UPDATE qz_oceans SET ocean_name = UPPER (ocean_name)
          WHERE ocean_id = l_id;

         PIPE ROW (l_id);
      END LOOP;
      CLOSE rows_in;

      RETURN;
   END;
END;
/

论坛徽章:
1
秀才
日期:2017-04-05 13:27:59
发表于 2017-3-31 16:19 | 显示全部楼层
本帖最后由 dl_zhb 于 2017-3-31 16:20 编辑

A,B正确
A:SELECT ocean_id FROM qz_oceans
                         WHERE ocean_name LIKE '%a%ic' 返回的是 100 和200的数据 但update部分是>300 实际是什么都不执行的
B:使用自治事务 理解是先查询结果 再UPDATE 因此第一次执行的时候有结果 第二次就没有结果了(查询后 UPDATE掉了)
C:使用自治事务 缺少COMMIT(循环外)
D:使用自治事务 缺少COMMIT
E:未使用自治事务 报错“无法在查询中执行DML” 理解错误是因为 查询的数据有UPDATE操作 因为未使用自治事务 因此UPDATE未提交 所以报错

感觉这题跟管道函数没有什么关系 貌似考的是 自治中的COMMIT 和 管道函数中 执行DML  返回结果和DML的先后关系。

现查的相关资料自己理解的 不知道对不对 等吧主回复正确答案啊

使用道具 举报

回复
论坛徽章:
1
秀才
日期:2017-04-05 13:27:59
发表于 2017-3-31 16:21 | 显示全部楼层
A,B正确
A:SELECT ocean_id FROM qz_oceans
                         WHERE ocean_name LIKE '%a%ic' 返回的是 100 和200的数据 但update部分是>300 实际是什么都不执行的
B:使用自治事务 理解是先查询结果 再UPDATE 因此第一次执行的时候有结果 第二次就没有结果了(查询后 UPDATE掉了)
C:使用自治事务 缺少COMMIT(循环外)
D:使用自治事务 缺少COMMIT
E:未使用自治事务 报错“无法在查询中执行DML” 理解错误是因为 查询的数据有UPDATE操作 因为未使用自治事务 因此UPDATE未提交 所以报错

使用道具 举报

回复
论坛徽章:
482
秀才
日期:2015-09-09 10:33:01秀才
日期: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秀才
日期:2016-01-06 14:10:21
 楼主| 发表于 2017-4-1 02:10 | 显示全部楼层
答案AB, 3楼得奖。

A: 通常来说,非查询类的DML只能够用于定义为自治事务的函数,并且你必须确保对PIPE_ROW的调用之前已经提交了所有的修改。
但在此例中,UPDATE语句永远没有执行到,所有的ID值都小于300.
B: 这个选项在每个PIPE ROW语句前都做了提交,确保事务已经不再处于“活跃”状态。这是很必要的,因为PIPE ROW导致数据(以及控制权)被返回到调用的查询语句中。

C:在这个实现方法中,ORACLE在执行查询的时候报错:
"ORA-06519: active autonomous transaction detected and rolled back"
但是在RETURN之前有一个COMMIT,为什么ORACLE还会抱怨“活跃”的事务?这上因为当ORACLE执行PIPE ROW语句,它把数据(以及控制权)返回给调用的查询语句。所以为了避免此错误,你必须在任何PIPE ROW之前执行COMMIT

D:在这个实现方法中,ORACLE在执行查询的时候报错:
"ORA-06519: active autonomous transaction detected and rolled back"
注意函数中根本没有COMMIT,但它被定义为自治事务。如果一个自治事务执行了插入、修改或者删除,它就必须在返回控制权之前COMMIT或者ROLLBACK。对管道函数而言,你必须在PIPE ROW之前提交,因为它会把控制权返回给调用的查询。

E:
在这个实现方法中,ORACLE在执行查询的时候报错:
"ORA-14551: cannot perform a DML operation inside a query"
不管是否为管道函数,如果你在一个函数中要执行一个插入、修改或删除,而函数又是从SQL查询中被调用,那么它就必须被定义为自治事务。

使用道具 举报

回复
认证徽章
论坛徽章:
170
布鲁克
日期:2016-05-31 16:31:17弗兰奇
日期:2016-05-31 16:31:17妮可·罗宾
日期:2016-07-04 11:53:09托尼托尼·乔巴
日期:2016-05-31 16:31:17山治
日期:2016-05-31 16:31:17乌索普
日期:2016-05-31 16:31:17娜美
日期:2016-05-31 16:31:17罗罗诺亚·索隆
日期:2016-05-31 16:31:17蒙奇·D·路飞
日期:2016-05-31 16:31:17目光如炬
日期:2017-03-19 22:00:00
发表于 2017-4-5 13:27 | 显示全部楼层
截止到今日每日一题奖励已发,辛苦newkid大师
本期从3-15到3-26期
每10期题目发一次奖,10期内获奖最多的再奖励一本技术图书~原有的秀才系列徽章继续根据兑换规则兑换礼品~
大家多多努力哦~
本期获奖情况如下:(其中3-25期无人获奖)
solomon_007
bell6248
jakesamz
abc_gdong
jakesamz
wmxcn2000
通垭口村的
abc_gdong
dl_zhb
恭喜abc_gdong同学获得一本技术图书,图书徽章已发,可以到书单换任意一本~
各位童鞋继续加油哦~

使用道具 举报

回复

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

本版积分规则

SACC2017购票8.8折优惠进行时

2017中国系统架构师大会(SACC2017)将于10月19-21日在北京新云南皇冠假日酒店震撼来袭。今年,大会以“云智未来”为主题,云集国内外顶级专家,围绕云计算、人工智能、大数据、移动互联网、产业应用等热点领域展开技术探讨与交流。本届大会共设置2大主会场,18个技术专场;邀请来自互联网、金融、制造业、电商等多个领域,100余位技术专家及行业领袖来分享他们的经验;并将吸引4000+人次的系统运维、架构师及IT决策人士参会,为他们提供最具价值的交流平台。
----------------------------------------
优惠时间:2017年8月30日前

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