**答对且答案未经编辑的puber将获得纪念章一枚(答案不可编辑但可发新贴补充或纠正)，其他会员如果提供有价值的分析、讨论也可获得纪念章一枚。
每两周的优胜者可获得itpub奖励的技术图书一本。
以往旧题索引：
http://www.itpub.net/forum.php?m ... eid&typeid=1808
原始出处：
https://devgym.oracle.com/
作者：Anthony Harper
运行环境：SQLPLUS, SERVEROUTPUT已打开, **版本要求：21C
注：本题给出答案时候要求给予简要说明才能得到奖品
你有一张表，存储着书籍的作者，以及你为每个作者收集了多少本书：
create table quiz_authors
as
select ** as author_id, 'Jane Austen' as author_name, 1 as books_collected from dual union all
select 102, 'James Joyce', 2 from dual union all
select 103, 'Charles Dickens', 1 from dual union all
select 104, 'Isaac Asimov', 10 from dual;
给定下列这个未完成的代码，哪些选项可以用来取代##REPLACE##，以产生指定的结果：
set serveroutput on;
declare
type t_authors_by_name is table of quiz_authors%rowtype index by quiz_authors.author_name%type;
l_authors t_authors_by_name;
##REPLACE##
for i in indices of l_authors loop
dbms_output.put_line('l_authors(' || i || ').books_collected = ' || l_authors(i).books_collected);
end loop;
end;
/
正确的选项将会打印出这些结果：
l_authors(Charles Dickens).books_collected = 1
l_authors(Isaac Asimov).books_collected = 10
l_authors(James Joyce).books_collected = 2
l_authors(Jane Austen).books_collected = 1
(A)
cursor c_authors is
select
author_id,
author_name,
books_collected
from quiz_authors;
begin
for r in c_authors loop
l_authors(r.author_name) := r;
end loop;
(B)
begin
select author_id, author_name, books_collected
bulk collect into l_authors
from quiz_authors;
(C)
begin
l_authors := t_authors_by_name(for i in (select author_id, author_name, books_collected from quiz_authors) sequence => i);
(D)
begin
l_authors := t_authors_by_name(for i in (select author_id, author_name, books_collected from quiz_authors) index i.author_name => i);
(E)
cursor c_authors is
select
author_id,
author_name,
books_collected
from quiz_authors;
begin
l_authors := t_authors_by_name(for r in c_authors index r.author_name => r);
(F)
type t_authors is table of quiz_authors%rowtype index by pls_integer;
l_authors_temp t_authors;
begin
l_authors_temp := t_authors(for i in (select author_id, author_name, books_collected from quiz_authors) sequence => i);
l_authors := t_authors_by_name(for v in values of l_authors_temp index v.author_name => v);
(G)
type t_authors is table of quiz_authors%rowtype index by pls_integer;
l_authors_temp t_authors;
begin
select author_id, author_name, books_collected
bulk collect into l_authors_temp
from quiz_authors;
l_authors := t_authors_by_name(for v in values of l_authors_temp index v.author_name => v);
(H)
type t_authors is table of quiz_authors%rowtype index by pls_integer;
l_authors_temp t_authors;
begin
select author_id, author_name, books_collected
bulk collect into l_authors_temp
from quiz_authors;
for i in 1..l_authors_temp.count loop
l_authors(l_authors_temp(i).author_name) := l_authors_temp(i);
end loop;
(I)
l_sql varchar2(1000) := 'select author_id, author_name, books_collected from quiz_authors';
begin
l_authors := t_authors_by_name(for r quiz_authors%rowtype in (execute immediate l_sql) index r.author_name => r);
|
