oracle SELECT INTO 和多个变量用于 UPDATE

SELECT INTO and multiple variables for UPDATE

提问by robartle

I'm a neophyte with Oracle and PL/SQL; I'm having some issues with a select into several variables. What I'm trying to do is search for multiple items in one table (SA_SPECIFICATION_DETAILS) and concatenate them together into a field in the other table (SA_ASSET). The SA_SPECIFICATION_DETAILS table holds several rows of attributes per each SA_ASSET.ASSET_ID. I've been working on this a few days now and keep getting several errors. Here is my sample code:

我是 Oracle 和 PL/SQL 的新手；我在选择多个变量时遇到了一些问题。我想要做的是在一个表 (SA_SPECIFICATION_DETAILS) 中搜索多个项目，并将它们连接到另一个表 (SA_ASSET) 中的一个字段中。SA_SPECIFICATION_DETAILS 表为每个 SA_ASSET.ASSET_ID 保存多行属性。我已经为此工作了几天，并不断收到几个错误。这是我的示例代码：

DECLARE 
manuf VARCHAR(50);
mods VARCHAR(50);
mvs VARCHAR(50);
yeara VARCHAR(4);
assid VARCHAR(50):= &assid;

BEGIN
SELECT TRIM(ATTRIBUTE_VALUE) INTO manuf      
    FROM
      SA_SPECIFICATION_DETAILS d,
      SA_ASSET a
    WHERE
      d.SPECIFICATION_NO = a.SPECIFICATION_NO
    AND d.ATTRIBUTE_DESC = 'MANUFACTURER'
    AND ASSET_ID = assid;

SELECT TRIM(ATTRIBUTE_VALUE) INTO mods
    FROM
      SA_SPECIFICATION_DETAILS d,
      SA_ASSET a
    WHERE
      d.SPECIFICATION_NO = a.SPECIFICATION_NO
    AND d.ATTRIBUTE_DESC = 'MODEL'
    AND ASSET_ID = assid;

SELECT TRIM(ATTRIBUTE_VALUE) INTO mvs
    FROM
      SA_SPECIFICATION_DETAILS d,
      SA_ASSET a
   WHERE
      d.SPECIFICATION_NO = a.SPECIFICATION_NO
    AND d.ATTRIBUTE_DESC = 'MAIN VALVE SIZE'
    AND ASSET_ID = assid;

SELECT TRIM(ATTRIBUTE_VALUE) INTO yeara
    FROM
      SA_SPECIFICATION_DETAILS d,
      SA_ASSET a
    WHERE
      d.SPECIFICATION_NO = a.SPECIFICATION_NO
    AND d.ATTRIBUTE_DESC = 'YEAR MANUFACTURED'
    AND ASSET_ID = assid;

dbms_output.ENABLE(buffer_size => NULL);
DBMS_OUTPUT.PUT_LINE ('Variables:');
dbms_output.put_line (manuf);
dbms_output.put_line (mods);
dbms_output.put_line (mvs);
dbms_output.put_line (yeara);
dbms_output.put_line (assid);
END;

BEGIN
UPDATE
  SA_ASSET
SET
  ASSET_DESC = TRIM(ATTRIBUTE1)
  || ' - Service Type: PW, Manf: '
  || manuf
  || ', Model: '
  || mods
  || ', Main Valve Size: '
  || mvs
  || ', Year Manf: '
  || yearm
  || ', Location: '
  || TRIM(SA_ASSET.STREET_NUMBER_CHAR)
  || ' '
  || TRIM(SA_ASSET.STREET_NAME)
WHERE
  ASSET_TYPE     = 'HYDRANT'
AND ASSET_STATUS = 'ACTIVE'
AND UPPER(ASSET_DESC) NOT LIKE '%LOCATION:%'
AND UPPER(ASSET_DESC) NOT LIKE '%HYDRANT%'
AND SA_ASSET.ASSET_ID = assid
END;

I've tested this and it fails with: Error report: ORA-06550: line 73, column 1: PLS-00103: Encountered the symbol "BEGIN" ORA-06550: line 96, column 1: PLS-00103: Encountered the symbol "END" when expecting one of the following:

我已经对此进行了测试，但失败了：错误报告：ORA-06550：第 73 行，第 1 列：PLS-00103：遇到符号“开始” ORA-06550：第 96 行，第 1 列：PLS-00103：遇到符号期待以下之一时的“END”：

So I commented out the update portion and tried this:

所以我注释掉了更新部分并尝试了这个：

    declare 
    manuf sa_specification_details.attribute_value%type;
    mods sa_specification_details.attribute_value%type;
    mvs sa_specification_details.attribute_value%type;
    yeara sa_specification_details.attribute_value%type;
    assid varchar2(15):= &assid;

    --dbms_output.ENABLE(buffer_size => NULL);

    BEGIN
    SELECT ATTRIBUTE_VALUE INTO manuf      
        FROM
          SA_SPECIFICATION_DETAILS d,
          SA_ASSET a
        WHERE
          d.SPECIFICATION_NO = a.SPECIFICATION_NO
        AND d.ATTRIBUTE_DESC = 'MANUFACTURER'
        AND ASSET_ID = assid;
    dbms_output.put_line (manuf);

   SELECT ATTRIBUTE_VALUE INTO mods
        FROM
          SA_SPECIFICATION_DETAILS d,
          SA_ASSET a
        WHERE
          d.SPECIFICATION_NO = a.SPECIFICATION_NO
        AND d.ATTRIBUTE_DESC = 'MODEL'
        AND ASSET_ID = assid;
    dbms_output.put_line (mods);

    SELECT ATTRIBUTE_VALUE INTO mvs
        FROM
          SA_SPECIFICATION_DETAILS d,
          SA_ASSET a
       WHERE
          d.SPECIFICATION_NO = a.SPECIFICATION_NO
        AND d.ATTRIBUTE_DESC = 'MAIN VALVE SIZE'
        AND ASSET_ID = assid;
    dbms_output.put_line (mvs);

    SELECT ATTRIBUTE_VALUE INTO yeara
        FROM
          SA_SPECIFICATION_DETAILS d,
          SA_ASSET a
        WHERE
          d.SPECIFICATION_NO = a.SPECIFICATION_NO
        AND d.ATTRIBUTE_DESC = 'YEAR MANUFACTURED'
        AND ASSET_ID = assid;
    dbms_output.put_line (yeara);  

    SYS.dbms_output.ENABLE;
    DBMS_OUTPUT.PUT_LINE ('Variables:');
    dbms_output.put_line (manuf);
    dbms_output.put_line (mods);
    dbms_output.put_line (mvs);
    dbms_output.put_line (yeara);
    dbms_output.put_line (assid); 
    END;

Which gets me the error: Error report: ORA-01403: no data found ORA-06512: at line 11 01403. 00000 - "no data found" *Cause:
*Action:

这让我得到了错误：错误报告：ORA-01403：未找到数据 ORA-06512：在第 11 行 01403.00000 -“未找到数据”*原因：
*操作：

So I ran the query without the select into:

所以我在没有选择的情况下运行查询：

SELECT
  ATTRIBUTE_VALUE
FROM
  SA_SPECIFICATION_DETAILS d,
  SA_ASSET a
WHERE
  d.SPECIFICATION_NO = a.SPECIFICATION_NO
AND d.attribute_desc = 'MANUFACTURER'
AND asset_id = '001722';

And it returns a single row/single column (aka it works). So what am I doing wrong? I've declared the variables, I've got the selects to run correctly but it's not passing the variables (that I can tell - haven't got dbms_output.put_line (manuf); to work). Am I close or on the wrong track completely?

它返回单行/单列（也就是它的工作原理）。那么我做错了什么？我已经声明了变量，我已经让选择正确运行，但它没有传递变量（我可以说 - 没有 dbms_output.put_line (manuf); 工作）。我是完全关闭还是走错了路？

回答by DazzaL

in your code, you need to add single quotes here:

在您的代码中，您需要在此处添加单引号：

assid VARCHAR2(50):= '&assid';

also remove the end/begin here:

也删除这里的结尾/开始：

dbms_output.put_line (assid);
END;

BEGIN

as you want this as one block (as you're using the variables from the first selects in the update).

因为您希望将其作为一个块（因为您正在使用更新中第一个选择的变量）。

finally i think you mean this to be yeara and not yearm

最后我想你的意思是这是 yeara 而不是 yearm

  || ', Year Manf: '
  || yearm <-- yeara?

an optimisation you can do too, is replace the 4 selects with one:

您也可以做的优化是用一个替换 4 个选择：

select max(case d.attribute_desc
             when 'MANUFACTURER' then trim(attribute_value)
           end) manuf,
       max(case d.attribute_desc
             when 'MODEL' then trim(attribute_value)
           end) mods,
       max(case d.attribute_desc
             when 'MAIN VALVE SIZE' then trim(attribute_value)
           end) mvs,
       max(case d.attribute_desc
             when 'YEAR MANUFACTURED' then trim(attribute_value)
           end) yeara
  into manuf, mods, mvs, yeara
  from sa_specification_details d,
       sa_asset a
 where d.specification_no = a.specification_no
   and d.attribute_desc in ( 'MANUFACTURER', 'MODEL', 'MAIN VALVE SIZE',
                             'YEAR MANUFACTURED' )
   and asset_id = assid;