PLS-00428:此SELECT语句中应有一个INTO子句 [英] PLS-00428: an INTO clause is expected in this SELECT statement

问题描述

我想将Rownum存储为变量，而不是使用昂贵的Join. 我需要从Select语句中获取此信息，因为Rownum在各种环境下都会有所不同，因此它不能是代码中的文字字符串.

I am wanting to store a Rownum as a variable rather than use a costly Join. I need to get this from a Select statement as the Rownum will be different on various environments so it cannot be a literal string in the code.

对于上下文，此查询在Oracle Siebel CRM模式上执行，并且检索某些具有特定类型和属性的产品.

For context, this query is executed on Oracle Siebel CRM schema and it retrieves some products of specific type and attributes.

我尝试在Toad和Oracle SQL Developer中使用以下SQL代码，但是出现以下错误:

I tried using the following SQL code in Toad and Oracle SQL Developer, however I am getting the following error:

PLS-00428:此SELECT语句中应包含一个INTO子句

PLS-00428: an INTO clause is expected in this SELECT statement

这是代码

    DECLARE
    PROD_ROW_ID varchar(10) := NULL;
BEGIN
    SELECT ROW_ID INTO VIS_ROW_ID FROM SIEBEL.S_PROD_INT WHERE PART_NUM = 'S0146404';

    BEGIN
        SELECT rtrim(VIS.SERIAL_NUM) || ',' || rtrim(PLANID.DESC_TEXT) || ',' ||
               CASE
               WHEN PLANID.HIGH = 'TEST123'
               THEN
                   CASE
                   WHEN to_date(PROD.START_DATE) + 30 > sysdate
                   THEN 'Y'
                   ELSE 'N'
                   END
               ELSE 'N'
               END
               || ',' || 'GB' || ',' ||
               rtrim(to_char(PROD.START_DATE, 'YYYY-MM-DD'))
        FROM SIEBEL.S_LST_OF_VAL PLANID
            INNER JOIN SIEBEL.S_PROD_INT PROD
                ON PROD.PART_NUM = PLANID.VAL
            INNER JOIN SIEBEL.S_ASSET NETFLIX
                ON PROD.PROD_ID = PROD.ROW_ID
            INNER JOIN SIEBEL.S_ASSET VIS
                ON VIS.PROM_INTEG_ID = PROD.PROM_INTEG_ID
            INNER JOIN SIEBEL.S_PROD_INT VISPROD
                ON VIS.PROD_ID = VISPROD.ROW_ID
        WHERE PLANID.TYPE = 'Test Plan'
              AND PLANID.ACTIVE_FLG = 'Y'
              AND VISPROD.PART_NUM = VIS_ROW_ID
              AND PROD.STATUS_CD = 'Active'
              AND VIS.SERIAL_NUM IS NOT NULL;
    END;
END;
/

推荐答案

在PLSQL块中，必须将选择语句的列分配给变量，而在SQL语句中不是这种情况.

In PLSQL block, columns of select statements must be assigned to variables, which is not the case in SQL statements.

第二个BEGIN的SQL语句没有INTO子句，这会导致错误.

The second BEGIN's SQL statement doesn't have INTO clause and that caused the error.

DECLARE
   PROD_ROW_ID   VARCHAR (10) := NULL;
   VIS_ROW_ID    NUMBER;
   DSC           VARCHAR (512);
BEGIN
   SELECT ROW_ID
     INTO VIS_ROW_ID
     FROM SIEBEL.S_PROD_INT
    WHERE PART_NUM = 'S0146404';

   BEGIN
      SELECT    RTRIM (VIS.SERIAL_NUM)
             || ','
             || RTRIM (PLANID.DESC_TEXT)
             || ','
             || CASE
                   WHEN PLANID.HIGH = 'TEST123'
                   THEN
                      CASE
                         WHEN TO_DATE (PROD.START_DATE) + 30 > SYSDATE
                         THEN
                            'Y'
                         ELSE
                            'N'
                      END
                   ELSE
                      'N'
                END
             || ','
             || 'GB'
             || ','
             || RTRIM (TO_CHAR (PROD.START_DATE, 'YYYY-MM-DD'))
        INTO DSC
        FROM SIEBEL.S_LST_OF_VAL PLANID
             INNER JOIN SIEBEL.S_PROD_INT PROD
                ON PROD.PART_NUM = PLANID.VAL
             INNER JOIN SIEBEL.S_ASSET NETFLIX
                ON PROD.PROD_ID = PROD.ROW_ID
             INNER JOIN SIEBEL.S_ASSET VIS
                ON VIS.PROM_INTEG_ID = PROD.PROM_INTEG_ID
             INNER JOIN SIEBEL.S_PROD_INT VISPROD
                ON VIS.PROD_ID = VISPROD.ROW_ID
       WHERE     PLANID.TYPE = 'Test Plan'
             AND PLANID.ACTIVE_FLG = 'Y'
             AND VISPROD.PART_NUM = VIS_ROW_ID
             AND PROD.STATUS_CD = 'Active'
             AND VIS.SERIAL_NUM IS NOT NULL;
   END;
END;
/

参考

http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/static.htm#LNPLS00601 http://docs.oracle.com/cd/B19306_01 /appdev.102/b14261/selectinto_statement.htm#CJAJAAIG http://pls-00428.ora-code.com/

这篇关于PLS-00428:此SELECT语句中应有一个INTO子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！