如何使用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命令未在下一行正确结束错误:
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:此处不允许使用序列号
(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:此处不允许使用序列号错误:
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);

最佳答案

这很容易。在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块可以执行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没有更广泛的意义,则可以使用分析函数来生成ItemId:
  c.CatCode || '-' ||
    lpad(row_number() over (partition by substr(c.CatCode,0,3) order by null),6,'0')
    as "ItemID",

ItemIds不会有确定的顺序,但是序列方法就不会。并且如果需要的话,您可以修改windowing子句以指定顺序。

关于oracle - 如何在PL/SQL中使用select语句为SQL * Plus变量分配值?,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/36189566/

10-08 22:23
查看更多