oracle占位符怎么打,PL/SQL Challenge 每日一题：2018-7-11 动态SQL中的占位符-CSDN博客

博客内容涉及SQL函数的编写，具体是一个名为`qz_name_from_id`的函数，需要通过参数`id_in`获取`qz_hominids`表中对应的物种名称。问题在于找出可以替代代码中`##REPLACE##`的部分，使得当调用函数`qz_name_from_id(4)`时能正确返回'Chimpanzee'。提供的选项中涉及动态SQL的执行和绑定变量，需要选择一个正确的方法来实现目标。

摘要由CSDN通过智能技术生成

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

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

以往旧题索引：

http://www.itpub.net/forum.php?m ... eid&typeid=1808

原始出处：

http://www.plsqlchallenge.com/

作者：Steven Feuerstein

运行环境：SQLPLUS, SERVEROUTPUT已打开

注：本题给出答案时候要求给予简要说明才能得到奖品

我执行了下列语句:

CREATE TABLE qz_hominids

(

id INTEGER,

nm VARCHAR2 (100)

)

BEGIN

INSERT INTO qz_hominids

VALUES (1, 'Neanderthal');

INSERT INTO qz_hominids

VALUES (2, 'Homo Sapiens');

INSERT INTO qz_hominids

VALUES (3, 'Homo Erectus');

INSERT INTO qz_hominids

VALUES (4, 'Chimpanzee');

COMMIT;

END;

哪些选项可用来取代下列代码中的 ##REPLACE##:

CREATE OR REPLACE FUNCTION qz_name_from_id (id_in IN INTEGER)

RETURN VARCHAR2

AUTHID DEFINER

l_the_name qz_hominids.nm%TYPE;

BEGIN

##REPLACE##

RETURN l_the_name;

END;

使得下列代码执行之后会显示 "Chimpanzee"?

BEGIN

DBMS_OUTPUT.put_line (qz_name_from_id (4));

END;

(A)

EXECUTE IMMEDIATE

'SELECT nm FROM qz_hominids WHERE id = '

|| id_in

|| ' AND id BETWEEN '

|| id_in

|| ' AND '

|| id_in

INTO l_the_name;

(B)

EXECUTE IMMEDIATE

'SELECT nm FROM qz_hominids WHERE id = :1 AND id BETWEEN :2 AND :3'

INTO l_the_name

USING id_in, id_in, id_in;

(C)

EXECUTE IMMEDIATE

'SELECT nm FROM qz_hominids WHERE id = :1 AND id BETWEEN :1 AND :1'

INTO l_the_name

USING id_in, id_in, id_in;

(D)

EXECUTE IMMEDIATE

'SELECT nm FROM qz_hominids WHERE id = :1 AND id BETWEEN :1 AND :1'

INTO l_the_name

USING id_in;

(E)

EXECUTE IMMEDIATE

'SELECT nm FROM qz_hominids

WHERE id = :equal_id AND id BETWEEN :start_id AND :end_id'

INTO l_the_name

USING id_in, id_in, id_in;

(F)

EXECUTE IMMEDIATE

'BEGIN

SELECT nm INTO :the_name

FROM qz_hominids WHERE id = :1 AND id BETWEEN :2 AND :3;

END;'

USING OUT l_the_name, id_in, id_in, id_in;

(G)

EXECUTE IMMEDIATE

'BEGIN

SELECT nm INTO :the_name

FROM qz_hominids WHERE id = :1 AND id BETWEEN :1 AND :1;

END;'

USING OUT l_the_name, id_in;