(原发表于 2011-7-28) 最先答对且答案未经编辑的puber将获得纪念章一枚(答案不可编辑但可发新贴补充或纠正),其他会员如果提供有价值的分析、讨论也可获得纪念章一枚。 每两周的优胜者可获得itpub奖励的技术图书一本。 以往旧题索引: http://www.itpub.net/forum.php?m ... eid&typeid=1808 原始出处: http://www.plsqlchallenge.com/ smknq.cn 作者:Steven Feuerstein 运行环境:SQLPLUS, SERVEROUTPUT已打开 注:本题给出答案时候要求给予简要说明才能得到奖品 我们有两只宠物:一只大鹦鹉(Mercury,它是一只非洲灰鹦鹉)和一只大猫(Moshe)。我想要记录它们看兽医的信息,用的是一个名为plch_vet_visit_t的对象类型和一个名为plch_vet_visits_t的此对象的嵌套表类型。 CREATE TYPE plch_vet_visit_t IS OBJECT /* SPECIFICATION AND/OR BODY TO BE COMPLETED BY CHOICES */ ... CREATE OR REPLACE TYPE plch_vet_visits_t IS TABLE OF plch_vet_visit_t ppreee.cn 下面的代码块用MULTISET EXCEPT比较这些访问记录: DECLARE mercury_visits plch_vet_visits_t := plch_vet_visits_t ( plch_vet_visit_t (TRUNC(SYSDATE) - 20, 'Clip wings') , plch_vet_visit_t (TRUNC(SYSDATE) - 5, 'Check cholesterol')); moshe_visits plch_vet_visits_t := plch_vet_visits_t (plch_vet_visit_t (TRUNC(SYSDATE), 'Overweight')); moshe2_visits plch_vet_visits_t := plch_vet_visits_t (plch_vet_visit_t (TRUNC(SYSDATE), 'Overweight')); minus_visits plch_vet_visits_t; BEGIN minus_visits := mercury_visits MULTISET EXCEPT moshe_visits; IF minus_visits.COUNT = 0 THEN DBMS_OUTPUT.put_line ('Same visits!'); ELSE DBMS_OUTPUT.put_line ('Different visits!'); END IF; minus_visits := moshe_visits MULTISET EXCEPT moshe2_visits; pmhld.cn npntn.info nkyny.cn ejeepa.cn ffldg.cn kzqmt.cn qbgtr.cn qzxxk.cn IF minus_visits.COUNT = 0 THEN DBMS_OUTPUT.put_line ('Same visits!'); ELSE DBMS_OUTPUT.put_line ('Different visits!'); END IF; END; / 哪些选项包含了plch_vet_visit_t对象的实现呆啊吗,使得上述代码块执行之后会显示如下两行文本? Different visits! Same visits! rjynt.cn eowzpz.info grjzg.cn qgtzz.cn yhgaxz.info blpbw.cn ghsqz.cn (A) CREATE TYPE plch_vet_visit_t IS OBJECT (visit_date DATE , reason VARCHAR2 (100) , MAP MEMBER FUNCTION visit_map RETURN INTEGER); / fxcxs.cn chjpf.cn kxrxh.cn jdsdk.info ftdtb.info ztrtk.cn rmdct.cn CREATE OR REPLACE TYPE BODY plch_vet_visit_t IS MAP MEMBER FUNCTION visit_map RETURN INTEGER IS BEGIN RETURN CASE WHEN self.visit_date < TRUNC(SYSDATE) - 10 THEN 100 WHEN self.visit_date < TRUNC(SYSDATE) THEN 10 ELSE 1 END; END; END; / mdcqs.cn mflxm.cn wslkg.cn yqwqr.cn obvrui.cn ylnpg.cn pzdsr.cn cjcnk.cn xtjqm.cn (B) CREATE TYPE plch_vet_visit_t IS OBJECT (visit_date DATE , reason VARCHAR2 (100) , ORDER MEMBER FUNCTION visit_order (visit_in IN plch_vet_visit_t) RETURN INTEGER); / CREATE OR REPLACE TYPE BODY plch_vet_visit_t IS ORDER MEMBER FUNCTION visit_order (visit_in IN plch_vet_visit_t) RETURN INTEGER IS BEGIN RETURN CASE WHEN self.visit_date < visit_in.visit_date THEN -1 WHEN self.visit_date = visit_in.visit_date THEN 0 ELSE 1 END; END; END; / pnxsb.cn bngtm.cn gkwhqv.info qdntzx.info nmqph.cn ckqsq.cn rqffn.cn jqxqk.info hlkxs.cn (C) CREATE TYPE plch_vet_visit_t IS OBJECT (visit_date DATE , reason VARCHAR2 (100)); / (D) CREATE TYPE plch_vet_visit_t IS OBJECT (visit_date DATE , reason VARCHAR2 (100) , MAP STATIC FUNCTION visit_map RETURN INTEGER); / rtftd.cn fnnxh.cn xyrmz.cn jjnxm.cn dgazvr.cn qxjgb.cn hpgyq.cn CREATE OR REPLACE TYPE BODY plch_vet_visit_t IS MAP STATIC FUNCTION visit_map RETURN INTEGER IS BEGIN RETURN CASE WHEN self.visit_date < TRUNC(SYSDATE) - 10 THEN 100 WHEN self.visit_date < TRUNC(SYSDATE) THEN 10 ELSE 1 END; END; END; / |
SQL Challenge 每日一题:2017-8-1 自定义嵌套表类型的MULTISET
最新推荐文章于 2023-07-19 16:49:46 发布