Oracle Pl/SQl：具有中间结果的自定义函数

发布于 2024-10-18 12:21:24 字数 431 浏览 1 评论 0原文

我需要使用一些 Oracle 内置方法（解析、strlength 等）将字符串转换为数字
我不知道如何为方法内的变量赋值，也不知道将声明部分放在哪里。

CREATE OR REPLACE FUNCTION EXAMPLE (param IN VARCHAR2) 
RETURN NUMBER AS
BEGIN

  SELECT <string_handling_using_param> 
    INTO var 
    FROM DUAL;

  RETURN TO_NUMBER(<some computation using var>);

END EXAMPLE ;

我已经尝试了围绕此函数的一些变体，例如添加 OUT 参数来存储中间变量，但随后我无法从常规 SQL 调用该函数...

关于如何实现此目的有什么建议吗？

原文

I need to transform a string into a number using some oracle built-in methods (parsing, strlength, ...)
I don't know how to assign a value to a variable inside the method, and I don't know where to put the declare section.

CREATE OR REPLACE FUNCTION EXAMPLE (param IN VARCHAR2) 
RETURN NUMBER AS
BEGIN

  SELECT <string_handling_using_param> 
    INTO var 
    FROM DUAL;

  RETURN TO_NUMBER(<some computation using var>);

END EXAMPLE ;

I have tried some variation around this function, like adding an OUT parameter for storing the intermediate var, but then I can not call the function from regular SQL...

Any suggestion on how to achieve this ?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

岁吢 2024-10-25 12:21:24

如果我理解正确，您只需要定义“var”变量...

create or replace FUNCTION EXAMPLE (param IN VARCHAR2)
RETURN NUMBER
AS
   var VARCHAR2(100);  -- This datatype may need modification
BEGIN
  select <string_handling>
  into   var
  from   dual;

  return to_number(<some computation using var>);
END EXAMPLE ;

具体取决于您正在做的事情，可能有一种更好的方法不需要 SELECT ... FROM DUAL：

create or replace FUNCTION EXAMPLE (param IN VARCHAR2)
RETURN NUMBER
AS
   var VARCHAR2(100);  -- This datatype may need modification
BEGIN
  var := <string_handling>;

  return to_number(<some computation using var>);
END EXAMPLE ;

If I understand correctly you just need to define the "var" variable...

create or replace FUNCTION EXAMPLE (param IN VARCHAR2)
RETURN NUMBER
AS
   var VARCHAR2(100);  -- This datatype may need modification
BEGIN
  select <string_handling>
  into   var
  from   dual;

  return to_number(<some computation using var>);
END EXAMPLE ;

Depending on exactly what you're doing, there may be a better approach that doesn't need the SELECT ... FROM DUAL:

create or replace FUNCTION EXAMPLE (param IN VARCHAR2)
RETURN NUMBER
AS
   var VARCHAR2(100);  -- This datatype may need modification
BEGIN
  var := <string_handling>;

  return to_number(<some computation using var>);
END EXAMPLE ;

回复收藏 0 原文

蓝色星空 2024-10-25 12:21:24

create or replace FUNCTION EXAMPLE (param IN VARCHAR2) 
RETURN NUMBER AS 
    -- declare section between AS and BEGIN
    var varchar2(100);
BEGIN   
   select <string_handling> into var from dual;  
   -- also note that many built-in functions can be done directly
   -- without calling a select, so in many cases
   -- var := substr(param,1,10) <or some other string handling>;
   -- is perfectly acceptable too. 
   return to_number(<some computation using var>); 
EXCEPTION -- if you need an exception handler
   when value_error then
      <do something with it or set a default return value or whatever>
END EXAMPLE ;

create or replace FUNCTION EXAMPLE (param IN VARCHAR2) 
RETURN NUMBER AS 
    -- declare section between AS and BEGIN
    var varchar2(100);
BEGIN   
   select <string_handling> into var from dual;  
   -- also note that many built-in functions can be done directly
   -- without calling a select, so in many cases
   -- var := substr(param,1,10) <or some other string handling>;
   -- is perfectly acceptable too. 
   return to_number(<some computation using var>); 
EXCEPTION -- if you need an exception handler
   when value_error then
      <do something with it or set a default return value or whatever>
END EXAMPLE ;

回复收藏 0 原文

~没有更多了~