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

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

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

[复制链接]
论坛徽章:
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-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;
/
论坛徽章:
394
阿斯顿马丁
日期:2014-01-03 13:53:522014年世界杯参赛球队:喀麦隆
日期:2014-07-11 12:10:53马上有对象
日期:2014-04-09 16:19:542014年世界杯参赛球队: 洪都拉斯
日期:2014-06-25 08:25:55itpub13周年纪念徽章
日期:2014-09-28 10:55:55itpub13周年纪念徽章
日期:2014-10-01 15:27:22itpub13周年纪念徽章
日期:2014-10-09 12:04:18马上有钱
日期:2014-10-14 21:37:37马上有钱
日期:2015-01-22 00:39:13喜羊羊
日期:2015-02-20 22:26:07
发表于 2017-1-20 08:20 | 显示全部楼层
只有一个J开始的为什么循环

使用道具 举报

回复
论坛徽章:
19
迷宫蛋
日期:2011-11-25 14:00:47秀才
日期:2017-06-29 10:16:48秀才
日期:2017-03-02 10:30:35秀才
日期:2017-03-02 10:30:14托尼托尼·乔巴
日期:2017-01-25 09:38:19暖羊羊
日期:2015-06-15 10:03:48天枰座
日期:2015-07-18 17:23:542015年新春福章
日期:2015-03-06 11:57:31喜羊羊
日期:2015-03-04 14:49:39蛋疼蛋
日期:2013-06-21 13:21:23
发表于 2017-1-20 09:03 | 显示全部楼层
A,C,D.
B错误是由于plch_employees没有department_name 字段.

使用道具 举报

回复
招聘 : 系统分析师
论坛徽章:
477
本田
日期:2014-01-05 16:51:44技术图书徽章
日期:2014-04-21 10:26:402014年世界杯参赛球队: 伊朗
日期:2014-05-23 10:41:312014年世界杯参赛球队: 比利时
日期:2014-06-17 12:09:43itpub13周年纪念徽章
日期: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
发表于 2017-1-20 09:24 | 显示全部楼层
楼上眼尖,我还以为全都正确呢

使用道具 举报

回复
论坛徽章:
264
布鲁克
日期:2016-10-08 10:06:50秀才
日期:2016-05-20 15:09:32射手座
日期:2016-05-26 14:02:50双子座
日期:2016-05-25 16:05:44白羊座
日期:2016-05-23 11:49:19双鱼座
日期:2016-04-29 17:13:05秀才
日期:2016-04-29 15:03:39秀才
日期:2016-04-29 15:04:10技术图书徽章
日期:2016-04-29 15:04:10秀才
日期:2016-03-28 10:21:13
发表于 2017-1-20 09:49 | 显示全部楼层
C 推荐写法

使用道具 举报

回复
论坛徽章:
394
阿斯顿马丁
日期:2014-01-03 13:53:522014年世界杯参赛球队:喀麦隆
日期:2014-07-11 12:10:53马上有对象
日期:2014-04-09 16:19:542014年世界杯参赛球队: 洪都拉斯
日期:2014-06-25 08:25:55itpub13周年纪念徽章
日期:2014-09-28 10:55:55itpub13周年纪念徽章
日期:2014-10-01 15:27:22itpub13周年纪念徽章
日期:2014-10-09 12:04:18马上有钱
日期:2014-10-14 21:37:37马上有钱
日期:2015-01-22 00:39:13喜羊羊
日期:2015-02-20 22:26:07
发表于 2017-1-20 10:22 | 显示全部楼层
再插入一条
   INSERT INTO plch_employees
         VALUES (100
               , 'Joe'
               , 1000000
               , 200);
题目才有意义

使用道具 举报

回复
论坛徽章:
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-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号
  
快速回复 返回顶部 返回列表