带有ora-01744 INTO的PL/SQL oracle函数 [英] PL/SQL oracle function with ora-01744 INTO

问题描述

我在myqsl中有一组需要编写oracle版本的函数.

I have set of functions in myqsl that I need to write oracle versions.

在我遇到限制1 问题之前，一切都会好起来的.我认为我要查询的限额是个问题，但无法弄清楚出了什么问题.你可以帮我吗?

Which is going well until I face limit 1 issue. I think my query for limit is the problem but couldn't figure out what's wrong. Can you give me a hand?

MySQL版本

SELECT concat(fld_name, ' ', fld_surname) 
   INTO info 
FROM tbl_customer 
WHERE fld_flat_id = flatId and fld_type = typeId 
order by fld_own_date desc limit 1;

Oracle版本(引起问题)

SELECT Q1.* 
FROM (
    SELECT ROWNUM AS RWNR2, Q2.* 
    FROM (
        SELECT (NAME || ' ' || SURNAME) 
           INTO info 
        FROM TB_CUSTOMER 
        WHERE FLAT_ID = flatId AND TYPE = typeId 
        ORDER BY OWN_DATE DESC
    ) Q2 
    WHERE ROWNUM <= 1 
 ) Q1 
 WHERE Q1.RWNR2 >  0;

通过我知道表名和字段不同的方式，此预览中的值是正确的.

And by the way I know table names and fields are different, values are correct in this preview.

推荐答案

INTO子句必须位于最外面的SELECT而不是内部的子句中.而且您不需要两个嵌套层次，一个派生表和一个rownum <= 1就足够了.

The INTO clause must be in the outer-most SELECT not the inner one. And you don't need two levels of nesting, one derived table and a rownum <= 1 is enough.

所以应该是这样的:

SELECT full_name
  INTO info
FROM (
    SELECT (NAME || ' ' || SURNAME) as full_name
    FROM TB_CUSTOMER 
    WHERE FLAT_ID = flatId 
      AND TYPE = typeId 
    ORDER BY OWN_DATE DESC
) Q2 
WHERE ROWNUM <= 1;

请注意，如果内部选择未返回任何内容，它可能仍会失败，并显示找不到行"错误.

Note that it might still fail with a "no rows found" error if the inner select does not return anything.

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