CTE的Oracle表功能 [英] Oracle Table Function from CTE

问题描述

我想通过以下查询在oracle中创建表函数:

I'd like to create a table function in oracle from the following query:

 WITH A AS (
    SELECT * FROM PART_TABLE WHERE PART_NO LIKE part_num
 )
SELECT * FROM A;

'part_num'是传递给函数的参数.我在语法上遇到麻烦.这是我尝试过的:

'part_num' being a parameter passed into the function. I am having trouble with the syntax. This is what I have tried:

CREATE OR replace FUNCTION part_test_f(search_part IN varchar2)
RETURN part_test_t PIPELINED 
AS 

    rec PART_TEST;

    CURSOR cur(part_num) IS 
         WITH A AS (
            SELECT * FROM PART_TABLE WHERE PART_NO LIKE part_num
         )
        SELECT * FROM A;
BEGIN
        FOR record IN cur(search_part) LOOP
        rec := PART_TEST(record);
        pipe row(rec);
        END LOOP;
return;
END;

我已经创建了表和行的类型.感谢您的帮助.

I have created the types for the table and the rows. Thanks for any help.

我又给了它一次.声明现在看起来像:

I have given it another go. The declaration now looks like:

    create or replace FUNCTION part_test_f(search_part IN varchar2)
RETURN part_test_t PIPELINED
AS

    rec PART_TEST;

    CURSOR cur(part_num varchar2) RETURN PART_TEST IS
         WITH A AS (
            SELECT * FROM F6RD_PART WHERE PART_NO LIKE part_num
         )
        SELECT * FROM A;
BEGIN
        FOR rec IN cur(search_part) LOOP
        pipe row(rec);
        END LOOP;
return;
END;

现在我收到没有更多数据要从套接字读取"错误

Now I receive an 'No more data to read from socket' error

推荐答案

在处理Objects时，在制作select时要格外小心.您的代码中存在错误，导致出现问题.同样也不确定是否可以直接进行以下直接分配:

While dealing with Objects you be extra cautious while making select. There were errors in your code which were resulting in issues. Also am not very sure if direct assignment done below is actually allowed:

rec := PART_TEST(record);

但是，我在这里提出2种解决方案.首先使用Pipeline，其他不使用它.见下文:

However I propose 2 solutions here. First with Pipeline and other without it. See below:

-表和对象准备

--Table and Objects Preparation

CREATE TABLE part_table (
    part_no   NUMBER,
    col1      NUMBER
);

INSERT INTO PART_TABLE VALUES(1,11);
INSERT INTO PART_TABLE VALUES(1,33);
INSERT INTO PART_TABLE VALUES(2,22);

SELECT * FROM PART_TABLE;

CREATE OR REPLACE TYPE part_test IS OBJECT (
    part_no   NUMBER,
    col1      NUMBER
);

CREATE OR REPLACE TYPE part_test_t IS  TABLE OF part_test;
/

-具有管道功能

--Function with Pipeline

CREATE OR replace FUNCTION part_test_f(search_part IN number)
RETURN part_test_t PIPELINED 
AS 

 rec part_test; --<--Variable of type Object since we want to piperow.

 CURSOR cur(part_num number) IS 
      WITH A AS 
      (       --Make sure you cast your select statement of object type    
              SELECT part_test(PART_NO,col1) FROM PART_TABLE WHERE PART_NO LIKE part_num
       )
       SELECT * FROM A;        
BEGIN        
   OPEN cur(search_part) ;
    LOOP
      Fetch cur into rec;    --<-- Note here am not using `Bulk Collect` even though its being a collection since we are `piping` the rows.    
      exit when cur%NOTFOUND;
       pipe row(rec);         
    END LOOP;
RETURN ;
END;
/

结果:

SQL> SELECT * FROM TABLE (PART_TEST_F(1));

     PART_NO       COL1
    ---------- ----------
      1            11
      1            33

-没有管道

--Without Pipeline

CREATE OR REPLACE FUNCTION part_test_f (search_part IN NUMBER) 
RETURN part_test_t 
 AS
    rec   part_test_t;
    CURSOR cur ( part_num NUMBER) IS 
    WITH a AS 
    ( SELECT part_test( part_no,col1 )
       FROM part_table
       WHERE part_no LIKE part_num       
    ) 
    SELECT *  FROM a;
BEGIN
    OPEN cur(search_part);
    LOOP
        FETCH cur BULK COLLECT INTO rec;
        EXIT WHEN cur%notfound;      
    END LOOP;
    RETURN rec;
END;
/

结果:

SQL> Select * from table (part_test_f(1));

   PART_NO       COL1
---------- ----------
         1         11
         1         33

选择最适合您的一个，但我们都知道使用Pipeline函数的优点，因此最适合.

Choose whichever suits you but we all know the advantages of using Pipeline functions hence that would be best suited.

这篇关于CTE的Oracle表功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！