PL/SQL函数参数 [英] PL/SQL function parameter

问题描述

在PL/SQL中，以下代码将失败.不允许为varchar2参数定义大小.你知道为什么吗?我该如何解决?

In PL/SQL, the code below will fail. It doesn't allow the definition of size for the varchar2 parameter. Do you know why? How do I fix it?

create or replace function logMessage(msg varchar2(2000))
return number as
begin
    null;    
    return 1;
end logMessage;
/

错误消息是

1/33 PLS-00103:预期时遇到符号(" 以下之一:

1/33 PLS-00103: Encountered the symbol "(" when expecting one of the following:

:=. )，@％默认字符用符号:="代替 ("继续.

:= . ) , @ % default character The symbol ":=" was substituted for "(" to continue.

推荐答案

您可以通过删除大小约束来解决此问题.不需要:

You fix it by removing the size constraint. It's not needed:

create or replace function logMessage (msg in varchar2)
return number is
begin
    null;    
    return 1;
end logMessage;
/

我认为您的功能比这稍微复杂些吗?

I assume your function is slightly more complicated than this?

create function语句的完整语法来自文档是:

CREATE [OR REPLACE] FUNCTION [Owner.]FunctionName 
     [(arguments [IN|OUT|IN OUT][NOCOPY] DataType [DEFAULT expr][,...])]
     RETURN DataType [InvokerRightsClause] [DETERMINISTIC]
     {IS|AS} 

关于 TECH Net 更有用.

There's a lot of information around the specifics if you're interested but you may find TECH on the Net more useful.

回答第一个问题为什么我不知道，也找不到答案.但是引用APC :

In answer to your first question of why I don't know and can't find an answer. But to quote APC:

这很烦人，但这是PL/SQL的工作方式，因此我们必须忍受 它.

This is annoying but it's the way PL/SQL works so we have to live with it.

简单地讲，您应该在运行时知道多长时间，并且能够处理某件事.不过，您可以考虑以下几种选择:

Put simply, you should know at run-time how long something is going to be and be able, therefore, to deal with it. There are a few options you can consider though:

如果知道消息的长度，则可以定义一个变量，其默认值是参数的substr:

If you know what length you want message to be you can define a variable, the default value of which is a substr of the parameter:

create or replace function logmessage ( msg in varchar2 ) return number is

   l_msg varchar2(2000) := substr(msg,1,2000);

begin
   return 1;
end;

或者，您可以在函数本身中检查长度:

Alternatively, you can check the length in the function itself:

create or replace function logmessage ( msg in varchar2 ) return number is

begin

   if length(msg) > 2000 then
      return 0;
   end if;

   return 1;
end;

这篇关于PL/SQL函数参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！