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

 找回密码
 注册
查看: 16264|回复: 6

[每日一题] PL/SQL Challenge 每日一题:2017-1-17 用SQL取代循环

[复制链接]
论坛徽章:
496
紫蜘蛛
日期:2007-09-26 17:05:56奥运会纪念徽章:垒球
日期:2008-09-15 01:28:12生肖徽章2007版:鸡
日期:2008-11-17 23:40:58生肖徽章2007版:马
日期:2008-11-18 05:09:48数据库板块每日发贴之星
日期:2008-11-29 01:01:02数据库板块每日发贴之星
日期:2008-12-05 01:01:03生肖徽章2007版:虎
日期:2008-12-10 07:47:462009新春纪念徽章
日期:2009-01-04 14:52:28数据库板块每日发贴之星
日期:2009-02-08 01:01:03生肖徽章2007版:蛇
日期:2009-03-09 22:18:53
发表于 2017-1-20 05:04 | 显示全部楼层 |阅读模式
(原发表于 2011-6-9)
最先答对且答案未经编辑的puber将获得纪念章一枚(答案不可编辑但可发新贴补充或纠正),其他会员如果提供有价值的分析、讨论也可获得纪念章一枚。

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

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

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

作者:        HotCoder1958

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

我创建了这两张表和数据:

CREATE TABLE plch_departments
(
   department_id     INTEGER PRIMARY KEY
, department_name   VARCHAR2 (100)
)
/

BEGIN
   INSERT INTO plch_departments
        VALUES (100, 'Marketing');

   INSERT INTO plch_departments
        VALUES (200, 'Catering');

   COMMIT;
END;
/

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

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

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

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

   COMMIT;
END;
/

哪些选项定义了plch_lucky_employees过程,使得下列代码块执行之后会显示 "2000000"?

DECLARE
   l_salary   NUMBER;
BEGIN
   plch_lucky_employees ('Marketing', 'J%');

   SELECT salary
     INTO l_salary
     FROM plch_employees
    WHERE employee_id = 100;

   sys.DBMS_OUTPUT.put_line (l_salary);
END;
/


(A)
CREATE OR REPLACE PROCEDURE plch_lucky_employees (
   department_name_in   IN plch_departments.department_name%TYPE
, last_name_like_in    IN VARCHAR2)
IS
BEGIN
   FOR drec IN (SELECT *
                  FROM plch_departments
                 WHERE department_name = department_name_in)
   LOOP
      FOR erec IN (  SELECT *
                       FROM plch_employees
                      WHERE department_id = drec.department_id
                   ORDER BY last_name)
      LOOP
         IF erec.last_name LIKE last_name_like_in
         THEN
            UPDATE plch_employees
               SET salary = salary * 2
             WHERE employee_id = erec.employee_id;
         END IF;
      END LOOP;
   END LOOP;
END;
/

(B)
CREATE OR REPLACE PROCEDURE plch_lucky_employees (
   department_name_in   IN plch_departments.department_name%TYPE
, last_name_like_in    IN VARCHAR2)
IS
BEGIN
   UPDATE plch_employees
      SET salary = salary * 2
    WHERE last_name LIKE last_name_like_in
          AND department_name = plch_lucky_employees.department_name_in;
END;
/

(C)
CREATE OR REPLACE PROCEDURE plch_lucky_employees (
   department_name_in   IN plch_departments.department_name%TYPE
, last_name_like_in    IN VARCHAR2)
IS
BEGIN
   UPDATE plch_employees
      SET salary = salary * 2
    WHERE last_name LIKE last_name_like_in
          AND department_id IN
                 (SELECT department_id
                    FROM plch_departments
                   WHERE department_name = plch_lucky_employees.department_name_in);
END;
/

(D)
CREATE OR REPLACE PROCEDURE plch_lucky_employees (
   department_name_in   IN plch_departments.department_name%TYPE
, last_name_like_in    IN VARCHAR2)
IS
BEGIN
   FOR drec IN (SELECT *
                  FROM plch_departments
                 WHERE department_name = department_name_in)
   LOOP
      FOR erec
         IN (  SELECT *
                 FROM plch_employees
                WHERE department_id = drec.department_id
                      AND last_name LIKE last_name_like_in
             ORDER BY last_name)
      LOOP
         UPDATE plch_employees
            SET salary = salary * 2
          WHERE employee_id = erec.employee_id;
      END LOOP;
   END LOOP;
END;
/
论坛徽章:
399
紫蛋头
日期:2012-05-21 10:19:41迷宫蛋
日期:2012-06-06 16:02:49奥运会纪念徽章:足球
日期:2012-06-29 15:30:06奥运会纪念徽章:排球
日期:2012-07-10 21:24:24鲜花蛋
日期:2012-07-16 15:24:59奥运会纪念徽章:拳击
日期:2012-08-07 10:54:50奥运会纪念徽章:羽毛球
日期:2012-08-21 15:55:33奥运会纪念徽章:蹦床
日期:2012-08-21 21:09:51奥运会纪念徽章:篮球
日期:2012-08-24 10:29:11奥运会纪念徽章:体操
日期:2012-09-07 16:40:00
发表于 2017-1-20 08:20 | 显示全部楼层
只有一个J开始的为什么循环

使用道具 举报

回复
论坛徽章:
20
2010年世界杯参赛球队:韩国
日期:2009-12-20 20:11:33沸羊羊
日期:2015-03-26 14:41:40暖羊羊
日期:2015-06-15 10:03:48天枰座
日期:2015-07-18 17:23:54托尼托尼·乔巴
日期:2017-01-25 09:38:19秀才
日期:2017-03-02 10:30:14秀才
日期:2017-03-02 10:30:35秀才
日期:2017-06-29 10:16:48技术图书徽章
日期:2017-07-11 09:10:262015年新春福章
日期:2015-03-06 11:57:31
发表于 2017-1-20 09:03 | 显示全部楼层
A,C,D.
B错误是由于plch_employees没有department_name 字段.

使用道具 举报

回复
招聘 : 系统分析师
论坛徽章:
483
马上有房
日期:2014-02-19 11:55:14itpub13周年纪念徽章
日期:2014-09-28 10:55:55itpub13周年纪念徽章
日期:2014-09-29 01:14:14itpub13周年纪念徽章
日期:2014-10-08 15:15:25itpub13周年纪念徽章
日期:2014-10-08 15:15:25马上有对象
日期:2014-10-12 11:58:40马上有车
日期:2014-11-16 17:11:29慢羊羊
日期:2015-02-09 17:04:38沸羊羊
日期:2015-03-04 14:43:432015年新春福章
日期:2015-03-06 11:57:31
发表于 2017-1-20 09:24 | 显示全部楼层
楼上眼尖,我还以为全都正确呢

使用道具 举报

回复
论坛徽章:
289
生肖徽章2007版:猴
日期:2008-05-16 11:28:59生肖徽章2007版:马
日期:2008-10-08 17:01:01SQL大赛参与纪念
日期:2011-04-13 12:08:17授权会员
日期:2011-06-17 16:14:53ITPUB元老
日期:2011-06-21 11:47:01ITPUB官方微博粉丝徽章
日期:2011-07-01 09:45:27ITPUB十周年纪念徽章
日期:2011-09-27 16:30:472012新春纪念徽章
日期:2012-01-04 11:51:22海蓝宝石
日期:2012-02-20 19:24:27铁扇公主
日期:2012-02-21 15:03:13
发表于 2017-1-20 09:49 | 显示全部楼层
C 推荐写法

使用道具 举报

回复
论坛徽章:
399
紫蛋头
日期:2012-05-21 10:19:41迷宫蛋
日期:2012-06-06 16:02:49奥运会纪念徽章:足球
日期:2012-06-29 15:30:06奥运会纪念徽章:排球
日期:2012-07-10 21:24:24鲜花蛋
日期:2012-07-16 15:24:59奥运会纪念徽章:拳击
日期:2012-08-07 10:54:50奥运会纪念徽章:羽毛球
日期:2012-08-21 15:55:33奥运会纪念徽章:蹦床
日期:2012-08-21 21:09:51奥运会纪念徽章:篮球
日期:2012-08-24 10:29:11奥运会纪念徽章:体操
日期:2012-09-07 16:40:00
发表于 2017-1-20 10:22 | 显示全部楼层
再插入一条
   INSERT INTO plch_employees
         VALUES (100
               , 'Joe'
               , 1000000
               , 200);
题目才有意义

使用道具 举报

回复
论坛徽章:
496
紫蜘蛛
日期:2007-09-26 17:05:56奥运会纪念徽章:垒球
日期:2008-09-15 01:28:12生肖徽章2007版:鸡
日期:2008-11-17 23:40:58生肖徽章2007版:马
日期:2008-11-18 05:09:48数据库板块每日发贴之星
日期:2008-11-29 01:01:02数据库板块每日发贴之星
日期:2008-12-05 01:01:03生肖徽章2007版:虎
日期:2008-12-10 07:47:462009新春纪念徽章
日期:2009-01-04 14:52:28数据库板块每日发贴之星
日期:2009-02-08 01:01:03生肖徽章2007版:蛇
日期:2009-03-09 22:18:53
 楼主| 发表于 2017-1-21 04:40 | 显示全部楼层
答案ACD, 3楼得奖。

A: 这个选项确实显示了正确的值,但是其实现方法既啰嗦又低效。

没有任何理由为了执行一个简单的UPDATE而写嵌套FOR游标循环。

B: 这个选项有“正确的思路”,即用单个UPDATE语句取代嵌套循环。然而过程无法编译,会报错:
"ORA-00904: "DEPARTMENT_NAME": invalid identifier"
department_name 是在 plch_departments 表, 而非 plch_employees 表

C: 这个选项用一个UPDATE完成了任务,不需要用FOR游标循环。
D:  这个选项确实显示了正确的值,但是其实现方法既啰嗦又低效。

没有任何理由为了执行一个简单的UPDATE而写嵌套FOR游标循环。
这个解答比另外一个嵌套循环好些,因为IF语句的逻辑被转移到查询里面,针对plch_employees表本身。这样,返回的行数会减少,并且过程中的PL/SQL代码也会被简化。

使用道具 举报

回复

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

本版积分规则

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