PL/SQL 函数来检查长度并构建一个字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13939980/
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
PL/SQL function to check length and build a string
提问by user1888543
I have created a VIEW with a column containing aggregated values(stores the output in varchar2 data type). Some of the records exceed the maximum limit, for example varchar2(4000). I was suggested a PL/SQL function to check the length and build a string if necessary..
我创建了一个包含聚合值列的 VIEW（以 varchar2 数据类型存储输出）。某些记录超过了最大限制，例如 varchar2(4000)。有人建议我使用 PL/SQL 函数来检查长度并在必要时构建一个字符串。
I've checked, and tried to correct the syntax errors,
我已经检查并尝试纠正语法错误，
CREATE OR REPLACE
FUNCTION SUBURL(col1 IN VARCHAR2, col2 IN varchar2)
RETURN varchar2
IS
v_result_string varchar2(4000);
v_length varchar2(4000);
BEGIN
IF (length (col1) + length (col2) ... <= 4000) then return col1 || col2 || ...
END IF;
-- build the string and check the length for every column you contatenate
v_result_string := col1;
if (length(v_result_string) + length (col2) > 4000) THEN
-- add as much of col2 as you can up to a total of 4000
v_length := 4000 - length(v_result_string);
v_result_string := v_result_string + substr(col2, 1, v_length);
return v_result_string;
end if;
end suburl;
Two errors this time,
这次有两个错误，
Error(7,35): PLS-00103: Encountered the symbol "." when expecting one of the following: . ( ) , * % & = - + < / > at in is mod remainder not rem => <> or != or ~= >= <= <> and or like like2 like4 likec as between || member submultiset The symbol "." was ignored.
错误（7,35）：PLS-00103：遇到符号“。” 当期待以下之一时： . ( ) , * % & = - + < /> at in 是 mod 余数而不是 rem => <> 或 != 或 ~= >= <= <> 和或 like2 like4 likec as between || member submultiset 符号“.” 被忽略了。
Error(7,76): PLS-00103: Encountered the symbol "." when expecting one of the following: ( - + case mod new null continue avg count current max min prior sql stddev sum variance execute forall merge time timestamp interval date pipe
错误（7,76）：PLS-00103：遇到符号“。” 当期望以下之一时：（ - + case mod new null continue avg count current max min before sql stddev sumvariance execute forall merge time timestamp interval date pipe
回答by Justin Cave
There are a number of syntax errors in your code.
您的代码中有许多语法错误。
- First, is your function supposed to accept an argument? You have an open paren after the function name
FUNCTION suburl(which implies that you do expect to pass in a parameter. But then you don't declare a parameter, you just start in with the body of your function. Either remove that open paren or add the declaration of the parameter(s) you want to pass in. - Second, you are using local variables
v_lengthandv_result_stringthat are not declared. You would need to declare those. Ifv_resultl_stringis supposed to be a third variable rather than a misspelling ofv_result_string, you'd need to declare that as well. - Third, you are missing the
BEGINandENDthat begins and ends the body of the function
- 首先，你的函数是否应该接受一个参数？您在函数名称后有一个开放括号，
FUNCTION suburl(这意味着您确实希望传入一个参数。但是你没有声明一个参数，你只是从你的函数体开始。删除那个打开的括号或添加要传入的参数的声明。 - 其次，您使用的局部变量
v_length和v_result_string未申报。你需要声明这些。如果v_resultl_string应该是第三个变量而不是拼写错误的v_result_string，则您还需要声明它。 - 第三，你缺少的
BEGIN，并END开始和结束函数体
The skeleton of a function declaration is something like
函数声明的骨架类似于
CREATE OR REPLACE FUNCTION name_of_function( parameter_list )
IS
variable_declarations;
BEGIN
body_of_function;
END;
Beyond that, there are a number of syntax errors in the body of your code with unmatched parenthesis.
除此之外，您的代码正文中还有许多语法错误，并且括号不匹配。
if (length(v_result_string) + length (col2) > 4000 THEN
for example, is missing a close paren before the THEN
例如，在 THEN
if (length(v_result_string) + length (col2) > 4000)
then
What are the ellipses in your code supposed to represent? Are you concatenating more than just the col1and col2columns? The code path you're following if concatenating the columns results in a string longer than 4000 bytes makes very little sense to me as it stands. It can be simplified if you are only trying to concatentate the two columns but if there are more columns then you'd need to account for those as well.
你的代码中的省略号应该代表什么？您连接的不仅仅是col1和col2列吗？如果连接列导致字符串长度超过 4000 字节，那么您所遵循的代码路径对我来说意义不大。如果您只想连接两列，则可以简化它，但如果有更多列，那么您还需要考虑这些。
You used the word "aggregation" which implies that you want to combine the data from multiple different rows, not multiple different columns. If that is, in fact, what you are trying to accomplish, you could use a loop or you could write a user-defined aggregate function.
您使用了“聚合”一词，这意味着您希望合并来自多个不同行的数据，而不是多个不同的列。如果这实际上是您要完成的任务，则可以使用循环或编写用户定义的聚合函数。
