oracle PL/SQL 函数参数

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/11282735/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-19 00:56:28  来源:igfitidea点击:

PL/SQL function parameter

oracleplsql

提问by user595234

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?

在 PL/SQL 中,下面的代码将失败。它不允许为 varchar2 参数定义大小。你知道为什么吗?我如何解决它?

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

error message is

错误信息是

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

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

1/33 PLS-00103:在预期以下情况之一时遇到符号“(”:

:=。) , @% 默认字符 符号“:=”被替换为“(”以继续。

回答by Ben

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?

我假设你的功能比这稍微复杂一点?

The full syntax for the create functionstatement from the documentationis:

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

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

There's a lotof information around the specificsif you're interested but you may find TECH on the Netmore useful.

很多的周围信息的细节,如果你有兴趣,但你会发现在网络上TECH更加有用。



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

回答你的第一个问题,为什么我不知道也找不到答案。但引用 APC

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

这很烦人,但这是 PL/SQL 的工作方式,所以我们必须忍受它。

Put simply, you should knowat 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:

简单地说,你应该知道,在运行时间长的东西是怎么回事是和能,因此,对付它。不过,您可以考虑以下几个选项:

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

如果你知道你想要的消息长度,你可以定义一个变量,它的默认值是substr参数的 a :

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;

回答by anazimok

Parameters are declared with data types but without data type length or precision. That means that a parameter may be declared as VARCHAR2 but it will not be declared with a length component (VARCHAR2(30) would not be valid).

参数用数据类型声明,但没有数据类型长度或精度。这意味着可以将参数声明为 VARCHAR2,但不会使用长度分量声明(VARCHAR2(30) 将无效)。