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

 找回密码
 注册
查看: 3801|回复: 5

[每日一题] PL/SQL Challenge 每日一题:2016-12-30 第三范式

[复制链接]
论坛徽章:
484
秀才
日期: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:04:09举人
日期:2015-11-23 10:04:09秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21
发表于 2017-1-5 04:51 | 显示全部楼层 |阅读模式

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

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

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

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

作者:ChrisSaxon

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

你正在创建一个社交应用,它把用户信息存在这张表:

create table plch_users (
  person_id   integer not null primary key,
  given_name  varchar2(100) not null,
  family_name varchar2(100) not null,
  birth_date  date not null,
  zodiac_sign varchar2(20) not null
);

insert into plch_users (
  person_id, given_name, family_name, birth_date, zodiac_sign
) values (
  1, 'Sally', 'Data', date'1974-01-01', 'Capricorn'
);
insert into plch_users (
  person_id, given_name, family_name, birth_date, zodiac_sign
) values (
  2, 'Quinn', 'Data', date'1974-01-01', 'Capricorn'
);

commit;

你还创建了这个函数来确定一个人的星座:

create or replace function plch_get_zodiac_sign ( birth_date date )
  return varchar2 deterministic as
  fixed_year date;
  star_sign  varchar2(20);
begin
  
  -- place standard year on date to allow basic range analysis
  fixed_year := to_date(
    to_char( birth_date, 'ddmm' ) || '1904',
      'ddmmyyyy'
  );
  
  star_sign :=
    case
      when fixed_year <= date'1904-01-19' then 'Capricorn'
      when fixed_year <= date'1904-02-18' then 'Aquarius'
      when fixed_year <= date'1904-03-20' then 'Pisces'
      when fixed_year <= date'1904-04-19' then 'Aries'
      when fixed_year <= date'1904-05-20' then 'Taurus'
      when fixed_year <= date'1904-06-20' then 'Gemini'
      when fixed_year <= date'1904-07-22' then 'Cancer'
      when fixed_year <= date'1904-08-22' then 'Leo'
      when fixed_year <= date'1904-09-22' then 'Virgo'
      when fixed_year <= date'1904-10-22' then 'Libra'
      when fixed_year <= date'1904-11-21' then 'Scorpio'
      when fixed_year <= date'1904-12-21' then 'Sagittarius'
                                          else 'Capricorn'
    end;
  
  return star_sign;
  
end plch_get_zodiac_sign;
/

以上述数据为例,下列的哪些选项修改了schema, 使得你在执行这两个update之后:

update plch_users
set    birth_date = date'1974-10-01'
where  person_id = 1;

update plch_users
set    zodiac_sign = 'Libra'
where  person_id = 2;

select * from plch_users order by person_id;

查询会返回这个输出:

PERSON_ID GIVEN_NAME FAMILY_NAM BIRTH_DATE  ZODIAC_SIG
---------- ---------- ---------- ----------- ----------
         1 Sally      Data       01-OCT-1974 Libra
         2 Quinn      Data       01-JAN-1974 Capricorn

并且第二个update会抛出异常?

假设这个查询使用了如下的 SQL*Plus 设置:

col given_name format a10
col family_name format a10
col zodiac_sign format a10
alter session set nls_date_format = 'dd-MON-yyyy';
alter session set nls_date_language = 'AMERICAN';


(A)
create table plch_dates (
  calendar_date date not null primary key,
  zodiac_sign   varchar2(20) not null
);

insert into plch_dates values (date'1974-01-01', 'Capricorn' );
insert into plch_dates values (date'1974-10-01', 'Libra' );

alter table plch_users drop ( zodiac_sign );
alter table plch_users add constraint plch_user_date_fk
  foreign key ( birth_date )
  references plch_dates ( calendar_date );
  
rename plch_users to plch_users_t;
create or replace view plch_users as
  select pu.person_id, pu.given_name, pu.family_name,
         pu.birth_date, pd.zodiac_sign
  from   plch_users_t pu
  join   plch_dates pd
  on     pu.birth_date = pd.calendar_date;

(B)
alter table plch_users drop ( zodiac_sign ) ;
alter table plch_users add (
  zodiac_sign varchar2(20) as (
    cast ( plch_get_zodiac_sign ( birth_date ) as varchar2(20) )
  )
);

(C)
create or replace trigger plch_user_tg
before insert or update on plch_users
for each row
begin
  :new.zodiac_sign := plch_get_zodiac_sign( :new.birth_date );
end;
/


(D)
alter table plch_users drop ( zodiac_sign );
rename plch_users to plch_users_t;

create or replace view plch_users as
  select person_id, given_name, family_name, birth_date,
         plch_get_zodiac_sign( birth_date ) zodiac_sign
  from   plch_users_t;
论坛徽章:
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-5 07:48 | 显示全部楼层
date'1974-10-01' 我的生年

使用道具 举报

回复
论坛徽章:
7
秀才
日期:2017-01-20 11:00:36秀才
日期:2017-01-20 11:04:31罗罗诺亚·索隆
日期:2016-12-23 09:11:49秀才
日期:2017-03-02 10:35:32技术图书徽章
日期:2017-03-02 10:36:07秀才
日期:2017-03-02 10:36:07秀才
日期:2017-03-20 13:42:20
发表于 2017-1-5 10:14 | 显示全部楼层
A正确,执行第二个update时,根据条件person_id = 2无法在表plch_dates中找到对应的行
BD正确,虚拟列不能被update
C错误,执行第二个update时不报错,但是select结果一致

使用道具 举报

回复
认证徽章
论坛徽章:
14
秀才
日期:2016-11-25 16:52:36秀才
日期:2017-01-20 11:00:36奥运会纪念徽章:跳水
日期:2016-09-13 10:47:52ITPUB15周年纪念
日期:2016-10-13 13:15:34布鲁克
日期:2016-10-12 08:34:20奥运会纪念徽章:皮划艇激流回旋
日期:2016-09-21 17:33:26优秀写手
日期:2014-08-14 06:00:13咸鸭蛋
日期:2012-12-05 15:47:46ITPUB 11周年纪念徽章
日期:2012-10-09 18:08:15奥运会纪念徽章:跳水
日期:2012-09-05 16:10:53
发表于 2017-1-5 16:40 | 显示全部楼层
〇〇 发表于 2017-1-5 07:48
date'1974-10-01' 我的生年

大叔 你好!
  大叔  再见!   

使用道具 举报

回复
论坛徽章:
484
秀才
日期: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:04:09举人
日期:2015-11-23 10:04:09秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21
 楼主| 发表于 2017-1-6 11:32 | 显示全部楼层
答案ABD, 3楼得奖。

A: 这对于所示的数据是行得通的,因此满足了需求。视图中的Zodiac_sign不是“key preserved”的,这意味着对于plch_dates的主键,视图中可能有多于一行数据。试图修改它会报错:
ORA-01779: cannot modify a column which maps to a non key-preserved table
但它还够不完整。
对于一个生产系统,你还必须为每个可能的出生日期向PLCH_dates插入一行数据。假设你只考虑活着的人,这也是自1900年以来所有的每一天。

B:这会将plch_users中的物理列替换成一个虚拟列。这意味着Oracle在运行时计算zodiac_sign的值。所以它总是和birth_date同步的。

你不能直接为虚拟列设置值。正如需求所言,第二个UPDATE会报错:
ORA-54017: UPDATE operation disallowed on virtual columns

C: 触发器总是会将zodiac_sign的值覆盖为函数调用的结果。所以查询输出是正确的。但是它没有按要求(为第二个UPDATE)抛出错误!所以它不满足需求。

D: 这是DIY的虚拟列。视图取代了表。函数调用取代了列。你可以用这个方法在10g或更早版本中模拟虚拟列。虚拟列必须使用确定性函数。所以如果你的函数不是确定性的,你就可以用这个方法。

你不能够在视图上执行DML来修改zodiac_sign。这会报错:
ORA-01733: virtual column not allowed here

使用道具 举报

回复
论坛徽章:
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-6 21:07 来自手机 | 显示全部楼层
视图似乎在生产中很常用

使用道具 举报

回复

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

本版积分规则

SACC2017购票7.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证:060528号 北京市公安局海淀分局网监中心备案编号:1101082001 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表