请告诉我如何更改查询，以便最终级别不重复行，即执行后有 8 行，而不是 30 行。例如，您可以写 distinct，但查询需要很长时间是时候处理大数据了。
with cte as
(select 1 as first_ticket_barcode
from dual
union all
select 5 as first_ticket_barcode
from dual
)
select first_ticket_barcode + level -1
from cte
connect by level <= 4
没找到明确的解释，用数据类型声明变量和用子类型声明有什么区别？
例如，如果我声明一个具有类型varchar2的变量或具有从创建的子类型的变量，有什么区别varchar2？
如果条件为真，RETURN 过程将返回什么？
PROCEDURE reload(p_sequence VARCHAR2) IS
BEGIN
DECLARE
lb_is_enabled CHAR (1);
BEGIN
BEGIN
SELECT is_enabled INTO lb_is_enabled
FROM tload
WHERE sequence_name = p_sequence;
EXCEPTION
WHEN NO_DATA_FOUND THEN NULL;
END;
IF NVL (lb_is_enabled, 'Y') != 'Y'
THEN
RETURN;
END IF;
END;
END;
还是只需要这个构造来退出嵌套块？
该脚本在“Oracle PL/SQL for Professionals”一书中介绍：
PROCEDURE explain_joke (main_category_in IN joke_category.category_id%TYPE)
IS
/*
|| Курсор со списком параметров, состоящим
|| из единственного строкового параметра.
*/
CURSOR joke_cur (category_in IN VARCHAR2)
IS
SELECT name, category, last_used_date
FROM joke
WHERE category = UPPER (category_in);
joke_rec joke_cur%ROWTYPE;
BEGIN
/* Теперь при открытии курсора ему передается аргумент */
OPEN joke_cur (main_category_in)
请解释为什么在为过程中的参数声明类型时指定explain_joketable.column ( )。joke_category.category_id
这是一个错误还是允许这个变量声明？
有这个代码：
CURSOR emp_cur RETURN employees%ROWTYPE
IS
SELECT * FROM employees
WHERE department_id = 10;
你能解释一下为什么这里需要这个子句RETURN吗？
然后我是否需要将光标传递给变量，例如：ins_c emp_cur%ROWTYPE;？
有这个块：
declare
type restype is table of varchar2 (64);
res restype;
tabnam varchar2(200):= '&table_l' ;
colnum int;
colnam varchar2 (32) := &column_l;
begin
select column_name into colnam
from all_tab_columns
where owner||'.'||table_name = upper (tabnam)
and column_id = colnam;
EXECUTE IMMEDIATE 'select '||colnam||' from '||tabnam bulk collect into res;
for i in 1..res.count loop
dbms_output.put_line (res(i));
end loop;
end;
此查询返回由参数（表 + 列号）指定的列的所有行。问题是行数过多，超出了缓冲区限制。
有没有办法解决这个问题？
也许根据这个请求创建一个函数会有所帮助？
declare
res varchar2(200);
tabnam varchar2(200) := 'DWH_gate.T$TERMINAL';
colnum int := 3;
colnam varchar2 (32) := 'ID_AGENT';
begin
EXECUTE IMMEDIATE 'select '||colnam||' from DWH_gate.T$TERMINAL' into res;
dbms_output.put_line (res);
end;
此查询引发错误。我知道错误是显示的不是一行，而是几行，而且很可能需要一个光标。但是无论我尝试了多少，错误仍然出现。
或者有一个稍微修改的查询：
declare
res varchar2(200);
tabnam varchar2(200) := 'DWH_gate.T$TERMINAL';
colnum int := 3;
colnam varchar2 (32) := 'ID_AGENT';
begin
res := 'select '||colnam||' from DWH_gate.T$TERMINAL';
EXECUTE IMMEDIATE res;
dbms_output.put_line (res);
end;
但它显示简单select ID_AGENT from DWH_gate.T$TERMINAL。
如何获取查询结果？
我有一个包含以下示例数据的表：
with t as (
select 123456 DRAW_KEY, 1 WINCAT_NUM,0 WIN_AMOUNT,0 WIN_COUNT
from dual
union all
select 123456 DRAW_KEY, 2 WINCAT_NUM,0 WIN_AMOUNT,0 WIN_COUNT
from dual
union all
select 123456 DRAW_KEY, 3 WINCAT_NUM,0 WIN_AMOUNT,0 WIN_COUNT
from dual
union all
select 123456 DRAW_KEY, 4 WINCAT_NUM,30000 WIN_AMOUNT,12 WIN_COUNT
from dual
union all
select 123456 DRAW_KEY, 5 WINCAT_NUM,6000 WIN_AMOUNT,73 WIN_COUNT
from dual
) select * from t
基于这些数据，我创建了一个piplined函数：
CREATE TYPE l_result AS OBJECT (
DRAW_KEY number,
WINCAT_NUM number,
WIN_AMOUNT number,
WIN_COUNT number);
/
CREATE TYPE table_pip IS TABLE OF l_result;
CREATE FUNCTION MyFunction1(v_draw_key in number) RETURN table_pip Pipelined IS
v_query clob;
v_cols clob;
BEGIN
select listagg(wincat_num ,', ') within group (order by wincat_num)
into v_cols
from customers1
where draw_key = 123456; --v_draw_key!
v_query := 'SELECT * FROM (
select draw_key,wincat_num,win_amount,win_count
from customers1
where draw_key = 123456) PIVOT (
sum(win_count) as win_count,sum(win_amount) as win_amount
FOR wincat_num IN ('||v_cols||'))';
EXECUTE IMMEDIATE v_query into l_result;
pipe row(l_result);
END;
但它给出了错误：
PLS-00321：表达式“L_RESULT”不适合作为赋值语句的左侧
和
PLS-00306：调用“L_RESULT”时参数的数量或类型错误。
我写错了什么，请告诉我？