Oracle管道函數(shù)pipelined?function的用法小結(jié)

更新時間：2023年07月27日 15:29:12 作者：DbWong_0918

這篇文章主要介紹了Oracle管道函數(shù)pipelined?function的用法,本文通過實例代碼給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下

Oracle 管道函數(shù)pipelined function簡單的使用

如果在函數(shù)(function)中加關(guān)鍵字 pipelined，就表明這是一個oracle管道函數(shù)，其返回值類型必為集合，體現(xiàn)出來的數(shù)據(jù)結(jié)構(gòu)類似于表，即可以理解成，使用管道函數(shù)可以返回一張查詢表，可以是單行數(shù)據(jù)也可以是多行數(shù)據(jù)，而不是平常函數(shù)返回的單行數(shù)據(jù)

這種返回多行數(shù)據(jù)在需要大數(shù)據(jù)量處理的時候很有用，且在復雜的方法下更好地去處理一些復雜的邏輯查詢

要使用管道函數(shù)的話需要先進行一下類型的類型的創(chuàng)建

首先我們創(chuàng)建一個類型對象

CREATE OR REPLACE Type test_Row_Type As Object
(
  test1 Varchar2(512),
  test2 Varchar2(3000),
  test3 Varchar2(3000),
  test4 Varchar2(3000),
  test5 Varchar2(3000),
  test6 Number,
  test7 Number
)

然后我們創(chuàng)建嵌套的表

CREATE OR REPLACE TYPE test_Row_Type_TABLE AS TABLE OF test_Row_Type;

接下來創(chuàng)建一個臨時表，用來測試數(shù)據(jù)

create table TTTtable
(
  aaa VARCHAR2(50),
  bbb VARCHAR2(50),
  ccc VARCHAR2(50)
)

其中數(shù)據(jù)如下

然后我們開始寫管道函數(shù)

CREATE OR REPLACE FUNCTION test_Row_pipelined(p_varchar in varchar2)
  return test_Row_Type_TABLE
  --關(guān)鍵字加在這里
  pipelined as
  p_num integer := 0;
  ret   test_Row_Type;
begin
  --查詢臨時表數(shù)據(jù)
  for sub_data in (select t.bbb, t.ccc
                     from TTTtable t
                    where t.aaa = p_varchar) loop
    p_num := p_num + 1;
    ret := test_Row_Type(p_num,
                             sub_data.bbb,
                             sub_data.ccc,
                             '',
                             '',
                             0,
                             0);
    --將數(shù)據(jù)放入管道，pipe row()語法被用來返回該集合的單個元素
    pipe row(ret);
  end loop;
  --函數(shù)使用一個空的return結(jié)束
  return;
end;

如果是使用

select test_Row_pipelined('a') from dual

則返回的是<Collection>數(shù)據(jù)，此時在PL/SQL中打開就是這樣的

同時，我們還可以使用這種語法

select * from table(test_Row_pipelined('a')) t

結(jié)果也是這樣的，不過推薦使用這種方法，因為這種方式是可以使用where條件去進行過濾的

比如我們加上where

select t.* from table(test_Row_pipelined('a')) t where t.test1 = 1

則得到的結(jié)果就是這樣的

這就是管道函數(shù)的最基本的用法

PS:在管道函數(shù)中可以進行DML操作，一般要加自治事務處理，但是不能直接對管道函數(shù)結(jié)果集進行DML操作，如果要對管道函數(shù)進行DML操作，建議做成視圖再加觸發(fā)器

使用管道函數(shù)制作分割函數(shù)的部分放在了這里

具體實現(xiàn)邏輯：

CREATE OR REPLACE FUNCTION test_Row_pipelined(p_insvar    in varchar2,
                                                  p_delimiter in varchar2)
  return test_Row_Type_TABLE
  pipelined as
  p_num     integer := 1;
  p_num1    integer;
  p_length  integer;
  p_start   integer := 1;
  p_varchar varchar2(200);
  ret       test_Row_Type;
begin
  --如果不是以分隔符結(jié)尾的，就拼接上去
  select case
           when (select count(1)
                   from dual
                  where p_insvar like '%' || p_delimiter) > 0 then
            p_insvar
           else
            p_insvar || p_delimiter
         end
    into p_varchar
    from dual;
  --整個字符串的長度
  select length(p_varchar) into p_length from dual;
  --當起始長度大于整體長度的時候
  while (p_start <= p_length) loop
    --找到字符串與分隔符的分割后的下標
    select instr(p_varchar, p_delimiter, p_start) into p_num1 from dual;
    --如果沒有，則返回全部字符串，說明到了結(jié)尾了
    if p_num1 = 0 then
      ret := test_Row_Type(p_num,
                               substr(p_varchar, p_start),
                               p_num1,
                               p_start,
                               p_length,
                               0,
                               0);
      pipe row(ret);
    else
      --否則，截取字符傳中，從p_start開始找到下標減去p_start的部分
      ret := test_Row_Type(p_num,
                               substr(p_varchar, p_start, p_num1 - p_start),
                               p_num1,
                               p_start,
                               p_length,
                               0,
                               0);
      pipe row(ret);
     --新的起始點為下標加1
      p_start := p_num1 + 1;
    end if;
    --序號加1
    p_num := p_num + 1;
  end loop;
  return;
end;

到此這篇關(guān)于Oracle管道函數(shù)pipelined function簡單的使用的文章就介紹到這了,更多相關(guān)oracle管道函數(shù)使用內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: