• http://www.sh-qiandun.com
  • ITPUB论坛-中国最专业的IT技术社区

     找回密码
     注册
    查看: 745|回复: 2

    [每日一题] PL/SQL Challenge 每日一题:2018-1-3 DML的RETURNING子句

    [复制链接]
    论坛徽章:
    486
    秀才
日期:2018-06-23 10:33:01秀才
日期:2018-06-23 10:03:12秀才
日期:2018-06-23 10:03:12秀才
日期:2018-06-23 10:03:12状元
日期:2018-06-23 10:04:09举人
日期:2018-06-23 10:04:09秀才
日期:2018-06-23 14:10:21秀才
日期:2018-06-23 14:10:21秀才
日期:2018-06-23 14:10:21秀才
日期:2018-06-23 14:10:21
    跳转到指定楼层
    1#
    发表于 2018-1-6 04:06 | 只看该作者 回帖奖励 |倒序浏览 |阅读模式
    最先答对且答案未经编辑的puber将获得纪念章一枚(答案不可编辑但可发新贴补充或纠正),其他会员如果提供有价值的分析、讨论也可获得纪念章一枚。

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

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

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

    作者:Steven Feuerstein

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

    我执行了这些语句:

    CREATE TABLE qz_employees
    (
       employee_id   INTEGER,
       salary        NUMBER
    )
    /

    BEGIN
       INSERT INTO qz_employees
            VALUES (100, 1000);

       INSERT INTO qz_employees
            VALUES (200, 2000);

       COMMIT;
    END;
    /

    哪些选项创建了一个名为QZ_RAISE_SALARY的过程,使得下列代码块执行之后, "Raise % = 200" 会被显示?

    DECLARE
       l_raise_pct   NUMBER;
    BEGIN
       qz_raise_salary (i_employee_id    => 100,
                        i_raise_amount   => 1000,
                        o_raise_pct      => l_raise_pct);
       DBMS_OUTPUT.put_line ('Raise % = ' || l_raise_pct);
    END;
    /

    (A)
    CREATE OR REPLACE PROCEDURE qz_raise_salary (
       i_employee_id    IN     qz_employees.employee_id%TYPE,
       i_raise_amount   IN     qz_employees.salary%TYPE,
       o_raise_pct         OUT NUMBER)
       AUTHID DEFINER
    AS
       l_old_salary   qz_employees.salary%TYPE;
       l_new_salary   qz_employees.salary%TYPE;
    BEGIN
           SELECT e.salary
             INTO l_old_salary
             FROM qz_employees e
            WHERE e.employee_id = i_employee_id
       FOR UPDATE OF e.salary;

          UPDATE qz_employees e
             SET e.salary = e.salary + i_raise_amount
           WHERE e.employee_id = i_employee_id
       RETURNING e.salary
            INTO l_new_salary;

       o_raise_pct := l_new_salary / l_old_salary * 100;
    END qz_raise_salary;
    /

    (B)
    CREATE OR REPLACE PROCEDURE qz_raise_salary (
       i_employee_id    IN     qz_employees.employee_id%TYPE,
       i_raise_amount   IN     qz_employees.salary%TYPE,
       o_raise_pct         OUT NUMBER)
       AUTHID DEFINER
    AS
       l_old_salary   qz_employees.salary%TYPE;
       l_new_salary   qz_employees.salary%TYPE;
    BEGIN
       UPDATE qz_employees e
          SET e.salary = e.salary + i_raise_amount
        WHERE e.employee_id = i_employee_id
      RETURNING OLD E.salary, NEW E.salary
         INTO l_old_salary, l_new_salary;

       o_raise_pct := l_new_salary / l_old_salary * 100;
    END qz_raise_salary;
    /

    (C)
    CREATE OR REPLACE PROCEDURE qz_raise_salary (
       i_employee_id    IN     qz_employees.employee_id%TYPE,
       i_raise_amount   IN     qz_employees.salary%TYPE,
       o_raise_pct         OUT NUMBER)
       AUTHID DEFINER
    AS
       l_old_salary   qz_employees.salary%TYPE;
       l_new_salary   qz_employees.salary%TYPE;
    BEGIN
          UPDATE qz_employees e
             SET e.salary = e.salary + i_raise_amount
           WHERE e.employee_id = i_employee_id
       RETURNING (SELECT e1.salary
                    FROM qz_employees e1
                   WHERE e1.employee_id = i_employee_id),
                 e.salary
            INTO l_old_salary, l_new_salary;

       o_raise_pct := l_new_salary / l_old_salary * 100;
    END qz_raise_salary;
    /

    (D)
    CREATE OR REPLACE FUNCTION qz_salary (
       i_employee_id   IN qz_employees.employee_id%TYPE)
       RETURN qz_employees.salary%TYPE
       AUTHID DEFINER
    IS
       l_salary   qz_employees.salary%TYPE;
    BEGIN
       SELECT e1.salary
         INTO l_salary
         FROM qz_employees e1
        WHERE e1.employee_id = i_employee_id;

       RETURN l_salary;
    END;
    /

    CREATE OR REPLACE PROCEDURE qz_raise_salary (
       i_employee_id    IN     qz_employees.employee_id%TYPE,
       i_raise_amount   IN     qz_employees.salary%TYPE,
       o_raise_pct         OUT NUMBER)
    AS
       l_old_salary   qz_employees.salary%TYPE;
       l_new_salary   qz_employees.salary%TYPE;
    BEGIN
          UPDATE qz_employees e
             SET e.salary = e.salary + i_raise_amount
           WHERE e.employee_id = i_employee_id
       RETURNING qz_salary (i_employee_id), e.salary
            INTO l_old_salary, l_new_salary;

       o_raise_pct := l_new_salary / l_old_salary * 100;
    END qz_raise_salary;
    /
    论坛徽章:
    20
    秀才
日期:2018-06-23 15:38:46秀才
日期:2018-06-23 15:05:41秀才
日期:2018-06-23 15:13:25秀才
日期:2018-06-23 15:17:54秀才
日期:2018-06-23 15:18:22秀才
日期:2018-06-23 15:18:22秀才
日期:2018-06-23 15:18:22秀才
日期:2018-06-23 15:18:22秀才
日期:2018-06-23 15:18:22秀才
日期:2018-06-23 15:05:41
    2#
    发表于 2018-1-6 17:30 | 只看该作者
    AC  
    B oldnew 语法错误  D 变异表问题
    论坛徽章:
    486
    秀才
日期:2018-06-23 10:33:01秀才
日期:2018-06-23 10:03:12秀才
日期:2018-06-23 10:03:12秀才
日期:2018-06-23 10:03:12状元
日期:2018-06-23 10:04:09举人
日期:2018-06-23 10:04:09秀才
日期:2018-06-23 14:10:21秀才
日期:2018-06-23 14:10:21秀才
日期:2018-06-23 14:10:21秀才
日期:2018-06-23 14:10:21
    3#
     楼主| 发表于 2018-1-9 06:02 | 只看该作者
    答案AC, 2楼得奖。

    A: 首先我获取旧的薪水值。
    然后我执行更新,用RETURNING得到新的薪水值。
    然后进行计算。

    这个没问题,但是它需要两个上下文切换(两个不同的SQL语句传递给SQL引擎)

    B: 这很可爱,但它不被支持。你不能在RETURNING子句中引用新/旧值。
    C: 正如 Oren Nakdimon 在他的博客中所指出的,也是本题的灵感:(http://db-oriented.com.bwp888.com/2017/10/0 ... ncement-suggestion/)

    为了避免先SELECT再UPDATE, 这是一个权宜之计(一个丑陋、令人迷惑、很可能性能不佳的解决方案),我们可以利用RETURNING INTO子句中的标量子查询遵循语句级一致读这个事实来实现相同的功能——它会返回发生DML之前的数据。

    D: 前一选项在RETURNING子句中包含标量子查询,与此不同的是,函数中的SELECT会报错:

    ORA-04091: table OWNER.QZ_EMPLOYEES is mutating, trigger/function may not see it


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

    本版积分规则

    -东堤横街论坛,东堤横街社区 - www-bbs-12q.bwp888.com
    DTCC2018购票6.8折优惠进行时

    中国数据库技术大会是国内数据库及大数据领域规模最大、最受欢迎的技术交流盛会。 2018年5月10-12日,第九届中国数据库技术大会将如约而至。本届大会以“数领先机•智赢未来”为主题,设定2大主会场及20个技术专场,邀请来自国内外互联网、金融、教育等行业百余位技术专家,共同探讨Oracle、MySQL、NoSQL、大数据等领域的前瞻性热点话题与技术。
    ----------------------------------------
    优惠时间:2018-06-23前

    报名链接>>
    阿弓镇论坛 南宁市仙湖开发区论坛 福山区论坛 东留春论坛 陕县论坛
    桓仁满族自治县论坛 平顺县论坛 桂洋镇论坛 良庆区论坛 澎湖岛论坛
    电子杂志 虎吧 老博客 读书频道 积分竞拍 文本模式 帮助
      IT博客
      ChinaUnix | ChinaUnix博客 | ChinaUnix论坛 | SAP ERP系统
    CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 网站律师 隐私政策 知识产权声明
    京ICP备16024965号 北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
      
    快速回复 返回顶部 返回列表
    海北藏族自治州论坛 宁河县论坛 参内论坛 滨海县论坛 崇义论坛
    大铭论坛 边坝县论坛 咸安区论坛 屏东论坛 西安市论坛