oracle - 如何在 PL/SQL 中使用 select 语句为 SQL*Plus 变量赋值？

Question

如何使用 select 语句为变量分配值并在 SQL*Plus 脚本中使用它，像这样？

VARIABLE FullCatCode VARCHAR2(7)

exec :FullCatCode := (SELECT CatCode from draw_catcodes where series = 123 and base = 158);

SELECT :FullCatCode || '-' || Other stuff... from table_name...

编辑:@AlexPoole，对于不精确的问题陈述感到抱歉。我正在从 280K 记录数据集中提取 50 列数据。我根据来自辅助表 (draw_catcodes) 的子字符串为每条记录分配序列 nextval。我在 SQL*Plus 中运行 SELECT 语句，假脱机到一个 CSV 文件。 SQL 现在看起来像这样(在下面测试您的第二个建议):

COLUMN CatCode NEW_VALUE FullCatCode;

SELECT
(CASE d.image WHEN 0 THEN 'NoImage.pdf'
 ELSE lower(d.prefix || lpad(d.series,3,0) || lpad(d.base,3,0) || lpad(d.suffix,2,0) || lpad(d.rev,2,0) || '.pdf') END) as "Filename",
(SELECT EngDiscipline from draw_catcodes c where d.series = c.series and d.base = c.base) as "EngDiscipline",

(SELECT CatCode from draw_catcodes c where d.series = c.series and d.base = c.base),

SELECT &FullCatCode || '-' ||
lpad((CASE substr(&FullCatCode,0,3)
WHEN 'AEG' THEN (SELECT AEG_seq.NEXTVAL FROM DUAL)
WHEN 'ARY' THEN (SELECT ARY_seq.NEXTVAL FROM DUAL)
WHEN 'BBR' THEN (SELECT BBR_seq.NEXTVAL FROM DUAL)
ELSE 0
END),6,0) as "ItemID",
upper(d.prefix || '-' || lpad(d.series,3,0) || '-' || lpad(d.base,3,0) || '-' || lpad(d.suffix,2,0)) as "LegacyID",
...
from tablename where ...

我从一个“更新”序列表的存储函数开始(无法从函数执行 DML)，尝试了一个带有 OUT 变量的存储过程(无法从 SQL SELECT 中调用该过程)，现在尝试序列在 CASE 语句中(无法弄清楚如何使用如上所述的变量)......任何建议将不胜感激!

编辑:@AlexPoole，由于变量在这种情况下不起作用，我只是直接选择我想要的值，然后使用 CASE 语句来指定正确的序列。但是语法不正确，因为我在下一行收到 ORA-00933: SQL command not properly end 错误:

SELECT ((SELECT CatCode from draw_catcodes c where d.series = c.series and d.base = c.base) || '-' ||
lpad((CASE substr((SELECT CatCode from draw_catcodes c where d.series = c.series and d.base = c.base),0,3)
WHEN 'AEG' THEN AEG_seq.NEXTVAL
WHEN 'ARY' THEN ARY_seq.NEXTVAL
WHEN 'BBR' THEN BBR_seq.NEXTVAL
...
WHEN 'SPR' THEN SPR_seq.NEXTVAL
WHEN 'SRL' THEN SRL_seq.NEXTVAL
ELSE 0
END),6,0))  as "ItemID" FROM DUAL,
upper(d.prefix || '-' || lpad(d.series,3,0) || '-' || lpad(d.base,3,0) || '-' || lpad(d.suffix,2,0)) as "LegacyID",

编辑:@AlexPoole，我添加了 JOIN 并清理了 SELECT 但现在得到一个 ORA-02287: sequence number not allowed here

(c.CatCode || '-' || 
(SELECT lpad(
(CASE substr(c.CatCode,0,3)
WHEN 'AEG' THEN AEG_seq.NEXTVAL
WHEN 'ARY' THEN ARY_seq.NEXTVAL
WHEN 'BBR' THEN BBR_seq.NEXTVAL
WHEN 'BSY' THEN BSY_seq.NEXTVAL
...
WHEN 'SDR' THEN SDR_seq.NEXTVAL
WHEN 'SLC' THEN SLC_seq.NEXTVAL
WHEN 'SLD' THEN SLD_seq.NEXTVAL
WHEN 'SMS' THEN SMS_seq.NEXTVAL
WHEN 'SPP' THEN SPP_seq.NEXTVAL
WHEN 'SPR' THEN SPR_seq.NEXTVAL
WHEN 'SRL' THEN SRL_seq.NEXTVAL
ELSE 0 END ),6,0) FROM DUAL)) as "ItemID",
...
FROM md_draw d 
join draw_catcodes c on d.series = c.series and d.base = c.base
order by lpad(d.series,3,0), lpad(d.base,3,0), lpad(d.suffix,2,0);

有什么建议么？

编辑:@AlexPoole，你说得对，我删除了包含序列调用的子查询，但仍然得到 ORA-02287: sequence number not allowed here 错误:

SELECT
(CASE d.image WHEN 0 THEN 'NoImage.pdf'
 ELSE lower(d.prefix || lpad(d.series,3,0) || lpad(d.base,3,0) || lpad(d.suffix,2,0) || lpad(d.rev,2,0) || '.pdf') END) as "Filename",
c.EngDiscipline as "EngDiscipline",
c.CatCode || '-' || lpad(CASE substr(c.CatCode,0,3)
WHEN 'AEG' THEN AEG_seq.NEXTVAL
WHEN 'ARY' THEN ARY_seq.NEXTVAL
WHEN 'BBR' THEN BBR_seq.NEXTVAL
...
WHEN 'SPP' THEN SPP_seq.NEXTVAL
WHEN 'SPR' THEN SPR_seq.NEXTVAL
WHEN 'SRL' THEN SRL_seq.NEXTVAL
ELSE 0 END,6,'0') as "ItemID",
upper(d.prefix || '-' || lpad(d.series,3,0) || '-' || lpad(d.base,3,0) || '-' || lpad(d.suffix,2,0)) as "LegacyID",
    ...
FROM md_draw SAMPLE (1) d 
join draw_catcodes c on d.series = c.series and d.base = c.base
order by c.ccProgram, lpad(d.series,3,0), lpad(d.base,3,0), lpad(d.suffix,2,0);

Answer 1

这相当容易。在 PL/SQL 中，查询要么需要是游标，要么必须选择某些内容；在此，进入您的绑定(bind)变量:

VARIABLE FullCatCode VARCHAR2(7)

exec SELECT CatCode into :FullCatCode from draw_catcodes where series = 123 and base = 158;

SELECT :FullCatCode || '-' || Other stuff... from table_name...

如果您只打算在以后的 SQL 语句中使用它，您还可以使用替换变量，而不是绑定(bind)变量:

COLUMN CatCode NEW_VALUE FullCatCode

SELECT CatCode from draw_catcodes where series = 123 and base = 158;

SELECT &FullCatCode || '-' || Other stuff... from table_name...

对于您修改后的问题，这根本不是您想要做的。您试图在同一查询的另一部分引用子查询中的值，而不是稍后在脚本中的单独语句。并且不涉及 PL/SQL。

上述任何一种机制都无法做到这一点；第一个是因为没有 PL/SQL block 来执行 select ... into , 第二个是因为替换变量在语句运行之前被评估和替换 - new_value直到查询运行后才存在。

但是，您不希望在这里使用子查询，您应该使用连接，例如:

SELECT
  CASE d.image WHEN 0 THEN 'NoImage.pdf'
    ELSE lower(d.prefix || lpad(d.series,3,'0') || lpad(d.base,3,'0') || lpad(d.suffix,2,'0') || lpad(d.rev,2,'0') || '.pdf')
    END as "Filename",
  c.EngDiscipline as "EngDiscipline",
  c.CatCode,  -- not sure if you actually want this raw value?
  c.CatCode || '-' || lpad(CASE substr(c.CatCode,0,3)
    WHEN 'AEG' THEN AEG_seq.NEXTVAL
    WHEN 'ARY' THEN ARY_seq.NEXTVAL
    WHEN 'BBR' THEN BBR_seq.NEXTVAL
    ELSE 0
    END,6,'0') as "ItemID",
  upper(d.prefix || '-' || lpad(d.series,3,'0') || '-' || lpad(d.base,3,'0') || '-' || lpad(d.suffix,2,'0'))
    as "LegacyID",
  ...
from tablename d
join draw_catcodes c on d.series = c.series and d.base = c.base
where ...

可以直接引用 c.CatCode在您尝试使用的地方 &FullCatCode .

如果仅存在此查询的序列并且 ItemId 没有更广泛的意义，则可以使用分析函数来生成 ItemIds:

  c.CatCode || '-' ||
    lpad(row_number() over (partition by substr(c.CatCode,0,3) order by null),6,'0')
    as "ItemID",

ItemIds 不会有确定性的顺序，但顺序方法不会；如果需要，您可以修改窗口子句以指定顺序。