Oracle存储过程在此SELECT语句中应有INTO子句 [英] Oracle stored procedure an INTO clause is expected in this SELECT statement

问题描述

我有一个下面提到的存储过程.

I am having a stored procedure mentioned below.

create or replace
PROCEDURE example(
    in_start_date IN VARCHAR2,
    in_svc_provider     IN a_message.msg_service_provider%type,sell OUT  number)
IS
BEGIN SELECT COUNT(*)   as sell
FROM a_message b1 WHERE TO_CHAR(b1.msg_when_created,'YYYY-MM-DD') = in_start_date
AND b1.msg_trans_type = 'SELL'
AND b1.msg_service_provider = in_svc_provider;

end;

在执行存储过程时，出现以下错误.

While executing the stored procedure I am getting following error.

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

能否请您提供此问题的解决方案.在sql中执行同一命令时，它可以正常工作，但是在存储过程中发生编译错误，这意味着在存储过程INTO中替换AS将给出相同的输出. /p>

Can you please provide me the resolution for this issue.while executing the same command in sql it is working fine but in stored procedure compilation error is occurring it means in stored procedure INTO replacing AS will give the same output please clarify.

推荐答案

错误消息是不言自明的； SELECT的PL/SQL版本需要INTO子句，因此您的查询结果还有待改进.您已经有一个OUT参数将该值放入:

The error message is fairly self-explanatory; the PL/SQL version of a SELECT requires an INTO clause so the result of your query has somewhere to go. You already have an OUT parameter to put the value into:

create or replace
PROCEDURE example(
    in_start_date IN VARCHAR2,
    in_svc_provider IN a_message.msg_service_provider%type,
    sell OUT number)
IS
BEGIN
  SELECT COUNT(*) INTO sell
  FROM a_message b1
  WHERE TO_CHAR(b1.msg_when_created,'YYYY-MM-DD') = in_start_date
  AND b1.msg_trans_type = 'SELL'
  AND b1.msg_service_provider = in_svc_provider;
end;

SELECT现在已成为INTO您的OUT参数，并且任何调用您的过程的人都可以使用它的值.

The SELECT is now INTO your OUT parameter, and its value will be available to whoever calls your procedure.

仅当您的查询始终返回恰好一行时，此方法才有效.如果它不返回任何东西，那么您将得到一个无数据发现的异常.如果返回的行多于一列，您将获得一个太多行的异常.而且您需要为查询返回的每一列都有一个变量-在这种情况下只有一个.您还可以声明一个局部变量(在IS和BEGIN之间)，以保存将在过程中处理的临时值，但在这里也不需要.

This only works if your query will always return exactly one row. If it doesn't return anything then you'll get a no-data-found exception; if it returns more than one row you'll get a too-many-rows exception. And you need to have a variable for each column your query returns - only one in this case. You can also declare a local variable (between IS and BEGIN) to hold temporary values that you will manipulate within the procedure, but you don't need that here either.

当您编译过程时，由于该语法错误，它会说它已编译并带有警告.如果是在SQL * Plus或SQL Developer以及其他工具中创建的，则可以通过发出命令show errors或在任何时候通过查询user_errors视图直接看到该错误.当您调用该过程时，该过程无效并被自动重新编译，这只是重新生成了相同的错误，因为没有任何更改.那就是您看到PLS-00428消息的时间.最好在编译时查找错误，而不是在执行时等待重新编译.

When you compiled your procedure it would have said it compiled with warnings, because of that syntax error. If you created it in SQL*Plus or SQL Developer, and maybe some other tools, you could have seen the error straight away by issuing the command show errors, or at any time by querying the user_errors view. When you called the procedure it was invalid and was automatically recompiled, which just regenerated the same error as nothing had changed; that's when you saw the PLS-00428 message. It's better to look for errors at compile time than wait for recompilation at execution time.

顺便说一句，通常最好将固定值转换为表使用的数据类型，而不是相反.当您这样做时:

Incidentally, it's generally better to convert a fixed value into the data type used by your table, rather than the other way round. When you do this:

  WHERE TO_CHAR(b1.msg_when_created,'YYYY-MM-DD') = in_start_date

...表中的每一列都必须将其msg_when_created DATE值转换为要与in_start_date字符串进行比较的字符串，这将阻止使用该列上的索引.最好这样做:

... every column in your table has to have its msg_when_created DATE value converted to a string to be compared to the in_start_date string, which would prevent an index on that column being used. It's preferable to do:

  WHERE b1.msg_when_created = TO_DATE(in_start_date, 'YYYY-MM-DD')

或者如果您的栏有时间成分:

or if your column has a time component:

  WHERE b1.msg_when_created >= TO_DATE(in_start_date, 'YYYY-MM-DD')
  AND b1.msg_when_created < TO_DATE(in_start_date, 'YYYY-MM-DD') + INTERVAL '1' DAY

让调用者将值转换为DATE更好，这样您就不必担心匹配传递的格式:

It would be even better to make your caller convert the value to a DATE so you don't have to worry about matching a passed format:

...
    in_start_date IN a_message.msg_when_created%TYPE,
...
  WHERE b1.msg_when_created >= TRUNC(in_start_date)
  AND b1.msg_when_created < TRUNC(in_start_date) + INTERVAL '1' DAY

这篇关于Oracle存储过程在此SELECT语句中应有INTO子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！