功能PL / SQL ORACLE

法比曼尼斯人

我需要使用以下sql执行功能

此sql从数字转换为字母

With
  Numero as (
  select 3 N from dual
  )
  ,
  PreProcesado1 as (
  select   N
        , floor(mod(N, 10)) Unidades
    from Numero
  )
  select   N,     case Unidades 
                  when 0 then ''
                  when 1 then 'one'
                  when 2 then 'two'
                  when 3 then 'three'
                  when 4 then 'four'
                  when 5 then 'five'
                  when 6 then 'six'
                  when 7 then 'seven'
                  when 8 then 'eight'
                  when 9 then 'nine'
                end
              end
            from PreProcesado1;

我正在这样做,但是它不起作用,必须有一个输入参数,在这种情况下是entry_numero,并且应该将其转换

create or replace function fun_departamento(entry_numero number)
return varchar2 is
response varchar2(120);
begin
  With
      Numero as (
      select entry_numero N from dual
      )
      ,
      PreProcesado1 as (
      select   N
            , floor(mod(N, 10)) Unidades
        from Numero
      )
      select   N,     case Unidades 
                      when 0 then ''
                      when 1 then 'one'
                      when 2 then 'two'
                      when 3 then 'three'
                      when 4 then 'four'
                      when 5 then 'five'
                      when 6 then 'six'
                      when 7 then 'seven'
                      when 8 then 'eight'
                      when 9 then 'nine'
                    end
                  end
                  into response
                  from PreProcesado1; 
return response;
end;
/

我收到此错误,非常感谢您的帮助

PL/SQL: SQL Statement ignored
PL/SQL: ORA-00947: not enough values
小脚丫

正如您所说的那样,您将按照自己的方式进行操作,然后将第16和29行视为

  • 第16行:您要选择两个值(NCASE表达式)
  • 第29行:您尝试将2个值放入单个变量(response

因此,N从第16行删除(或添加另一个变量以插入其中)。

SQL> create or replace function fun_departamento(entry_numero number)
  2  return varchar2 is
  3  response varchar2(120);
  4  begin
  5    With
  6        Numero as (
  7        select entry_numero N from dual
  8        )
  9        ,
 10        PreProcesado1 as (
 11        select   N
 12              , floor(mod(N, 10)) Unidades
 13          from Numero
 14        )
 15        select
 16                 --  N,                  --> without it! ...
 17                   case Unidades
 18                        when 0 then ''
 19                        when 1 then 'one'
 20                        when 2 then 'two'
 21                        when 3 then 'three'
 22                        when 4 then 'four'
 23                        when 5 then 'five'
 24                        when 6 then 'six'
 25                        when 7 then 'seven'
 26                        when 8 then 'eight'
 27                        when 9 then 'nine'
 28                    end
 29                    into response      --> ... as you're inserting into a single variable
 30                    from PreProcesado1;
 31  return response;
 32  end;
 33  /

Function created.

测试:

SQL> select fun_departamento(5) from dual;

FUN_DEPARTAMENTO(5)
--------------------------------------------------------------------------------
five

SQL>

本文收集自互联网,转载请注明来源。

如有侵权,请联系 [email protected] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章