您的位置: 首页  >  技术问答  >  错误(9,1):PLS-00428:在创建存储过程时，此SELECT语句中应有一个INTO子句

错误(9,1):PLS-00428:在创建存储过程时，此SELECT语句中应有一个INTO子句

分类: 技术问答 • 2022-04-23 16:07:05
问题描述：

我已经创建了一个SP，但是在编译时却出现

I have created a SP, but while compiling I am getting error as

错误(9,1):PLS-00428:此SELECT语句中应包含一个INTO子句

Error(9,1): PLS-00428: an INTO clause is expected in this SELECT statement

下面是SP 

CREATE OR REPLACE PROCEDURE FIP_VALID_TBL_TRANSMEDIA 
 (
   POUTMSG OUT VARCHAR2
  )

 AS 
BEGIN

 SELECT distinct  TO_CHAR(sp.RJ_SPAN_ID) AS SPAN_ID, TO_CHAR(sp.RJ_MAINTENANCE_ZONE_CODE) AS MAINT_ZONE_CODE
                                    from  APP_FTTX.span@SAT sp
                                    WHERE LENGTH(sp.RJ_SPAN_ID) = 21
                                    AND (sp.RJ_SPAN_ID LIKE ('%SPN%') OR sp.RJ_SPAN_ID LIKE ('%SPQ%') OR sp.RJ_SPAN_ID LIKE ('%SPR%')    
                                    OR sp.RJ_SPAN_ID LIKE ('%SPS%'))
                                    AND (sp.RJ_SPAN_ID LIKE ('%_BU') OR sp.RJ_SPAN_ID LIKE ('%_MP'))
                                    AND sp.INVENTORY_STATUS_CODE = 'IPL'
                                    AND sp.RJ_MAINTENANCE_ZONE_CODE = 'INMUNVMB01'
                                    AND (sp.RJ_INTRACITY_LINK_ID NOT LIKE ('%\_9%') ESCAPE '\' OR 
      sp.RJ_INTRACITY_LINK_ID IS NULL)                                        
             MINUS

    SELECT distinct  TO_CHAR(sp.RJ_SPAN_ID) AS SPAN_ID, TO_CHAR(sp.RJ_MAINTENANCE_ZONE_CODE) AS 
    MAINT_ZONE_CODE
                                    from  APP_FTTX.span@SAT sp
                                    INNER JOIN APP_FTTX.transmedia@SAT tm  -- transmedia added
                                    on sp.RJ_SPAN_ID = tm.RJ_SPAN_ID
                                    WHERE length(sp.RJ_SPAN_ID) = length(tm.RJ_SPAN_ID)
                                    and sp.RJ_MAINTENANCE_ZONE_CODE = tm.RJ_MAINTENANCE_ZONE_CODE
                                    and sp.INVENTORY_STATUS_CODE = tm.INVENTORY_STATUS_CODE
                                    AND LENGTH(sp.RJ_SPAN_ID) = 21
                                    AND (sp.RJ_SPAN_ID LIKE ('%SPN%') OR sp.RJ_SPAN_ID LIKE ('%SPQ%') 
OR sp.RJ_SPAN_ID LIKE ('%SPR%')    
                                    OR sp.RJ_SPAN_ID LIKE ('%SPS%'))
                                    AND (sp.RJ_SPAN_ID LIKE ('%_BU') OR sp.RJ_SPAN_ID LIKE ('%_MP'))
                                    AND sp.INVENTORY_STATUS_CODE = 'IPL'
                                    AND sp.RJ_MAINTENANCE_ZONE_CODE = 'INMUNVMB01'
                                    AND (sp.RJ_INTRACITY_LINK_ID NOT LIKE ('%\_9%') ESCAPE '\' OR 
sp.RJ_INTRACITY_LINK_ID IS NULL);

  NULL;
END FIP_VALID_TBL_TRANSMEDIA;

请在这里提出问题

更新

表描述

           Name            Null Type           
           --------------- ---- -------------- 
           SPAN_ID              NVARCHAR2(100) 
           MAINT_ZONE_CODE      NVARCHAR2(100) 
           UPDATED_DATE         DATE           
           UPDATED_BY           NVARCHAR2(100)

答

我只想在此过程中插入

i want to insert in this procedure only

您可以只使用 insert ...选择 [语法]:

You can just use the insert ... select [syntax]:

CREATE OR REPLACE PROCEDURE FIP_VALID_TBL_TRANSMEDIA 
 (
   POUTMSG OUT VARCHAR2
  )

 AS 
BEGIN

 INSERT INTO TBL_VLD_FIBERINV_DATA (SNAP_ID, MAINT_ZONE_CODE)
 SELECT TO_CHAR(sp.RJ_SPAN_ID), TO_CHAR(sp.RJ_MAINTENANCE_ZONE_CODE)
 ...
 MINUS
 SELECT TO_CHAR(sp.RJ_SPAN_ID), TO_CHAR(sp.RJ_MAINTENANCE_ZONE_CODE)
 ...

您不需要MINUS的 distinct ；并且列别名没有用.

You don't need distinct with MINUS; and the column aliases aren't useful.

或者，如果您要设置其他列，但它们尚未设置为默认值，则可以在查询部分中包含这些值-在此处使用系统时间和当前用户:

Or if you want to set the other columns, and they don't already default, you can include values for those in the query part - here using the system time and current user:

 INSERT INTO TBL_VLD_FIBERINV_DATA (SNAP_ID, MAINT_ZONE_CODE, UPDATED_DATE, UPDATED_BY)
 SELECT TO_CHAR(sp.RJ_SPAN_ID), TO_CHAR(sp.RJ_MAINTENANCE_ZONE_CODE), SYSDATE, USER
 ...
 MINUS
 SELECT TO_CHAR(sp.RJ_SPAN_ID), TO_CHAR(sp.RJ_MAINTENANCE_ZONE_CODE), SYSDATE, USER
 ...

您需要执行该过程才能实际执行任何操作；仅创建或编译它不会导致其中的代码(即插入代码)运行.您可以从一个简单的匿名块中做到这一点:

You need to execute the procedure for it to actually do anything; just creating or compiling it doesn't cause the code within it (i.e. the insert) to be run. You can do that from a simple anonymous block:

DECLARE
 OUTMSG VARCHAR2(4000);
BEGIN
 FIP_VALID_TBL_TRANSMEDIA (POUTMSG => OUTMSG);
END;
/

您必须声明并传递一个变量以匹配过程的形式参数，即使您当前不填充该变量.(希望您不打算捕获异常并将异常消息放入该变量...)

You have to declare and pass a variable to match the procedure's formal argument, even though you don't currently populate that. (Hopefully you don't intend to catch exceptions and put the exception message into that variable...)

在目标表中使用 nvarchar2 ，并且即使对于用户ID，其大小也为100，这似乎有点奇怪；并让 to_char()调用看起来已经是字符串的东西.您正在使用

It seems a bit odd to be using nvarchar2 in your target table, and to have the size as 100 even for user ID; and to have to_char() calls for what appear to already be strings. You're using the to_char(char) function which always returns varchar2, so then putting that into nvarchar2 seems strange...

相关推荐