(原发表于2011-1-16)
最先答对且答案未经编辑的puber将获得纪念章一枚(答案不可编辑但可发新贴补充或纠正)，其他会员如果提供有价值的分析、讨论也可获得纪念章一枚。
以往旧题索引：
http://www.itpub.net/forum.php?m ... eid&typeid=1808
原始出处：
http://www.plsqlchallenge.com/
作者：Steven Feuerstein
运行环境：SQLPLUS, SERVEROUTPUT已打开
注：本题给出答案时候要求给予简要说明才能得到奖品
我打开了 PL/Scope 并且编译了这个过程：
ALTER SESSION SET plscope_settings='IDENTIFIERS:ALL'
/
CREATE OR REPLACE PROCEDURE plch_proc (arg_in IN NUMBER)
IS
BEGIN
NULL;
END;
/
然后我写了如下代码块:
BEGIN
FOR rec
IN ( /*SELECT*/ )
LOOP
DBMS_OUTPUT.put_line (rec.info);
END LOOP;
END;
/
哪些选项可用来取代/*SELECT*/注释，使得执行之后屏幕上会显示如下文本:
PLCH_PROC-DECLARATION
PLCH_PROC-DEFINITION
ARG_IN-DECLARATION
NUMBER-REFERENCE
(A)
SELECT name || '-' || usage info
FROM (SELECT name
, usage
, usage_id
, usage_context_id
FROM user_identifiers
WHERE object_name = 'PLCH_PROC' AND object_type = 'PROCEDURE')
START WITH usage_context_id = 0
CONNECT BY PRIOR usage_id = usage_context_id
(B)
SELECT name || '-' || usage info
FROM (SELECT name
, usage
, usage_id
, usage_context_id
FROM user_identifiers
WHERE object_name = 'PLCH_PROC' AND object_type = 'PROCEDURE')
START WITH usage_context_id = 0
CONNECT BY PRIOR usage_id = usage_parent_id
(C)
SELECT name || '-' || usage info
FROM user_identifiers
WHERE object_name = 'PLCH_PROC' AND object_type = 'PROCEDURE'
ORDER BY info DESC
(D)
SELECT name || '-' || usage info
FROM (SELECT name
, usage
, usage_id
, usage_context_id
FROM user_identifiers
WHERE object_name = 'PLCH_PROC' AND object_type = 'PROCEDURE')
START WITH name = 'PLCH_PROC'
CONNECT BY PRIOR name = object_name
|
