Oracle-テーブルパイプライン関数

産業用システムでは、多くの場合、SQLクエリでこのデータにアクセスできる機能を備えたpl / sqlコードを使用してデータ変換を実行する必要があります。 これを行うために、oracleは表関数を使用します。



テーブル関数は、コレクションの形式でデータを返す関数です。クエリのfromセクションで、このコレクションがリレーショナルテーブルであるかのように参照できます。 コレクションは、table（）関数を使用してリレーショナルデータセットに変換されます。



ただし、このような関数には1つの欠点があります。最初にコレクションを完全に埋めてから、このコレクションが呼び出し元の処理に戻るためです。 このような各コレクションはメモリに保存され、負荷の高いシステムではこれが問題になる可能性があります。 また、呼び出し処理では、しばらくアイドル状態のコレクションがいっぱいになります。 この欠点を解決するために、テーブルコンベア機能が必要です。



パイプライン関数は、コレクションの形式でデータを返すテーブル関数と呼ばれますが、非同期的に実行します。つまり、コレクションの1つのレコードが受信され、このレコードはすぐに処理される呼び出しコードに直ちに送信されます。 この場合、メモリが保存され、時間の単純さが排除されます。



そのような関数がどのように作成されるかを考えてみましょう。 この例では、hrトレーニングスキームとその3つのテーブル（従業員、部門、場所）を使用します。



•従業員-従業員のテーブル。

•departments-部門のテーブル。

•位置-地理的位置のテーブル。



このスキーマとテーブルは、デフォルトですべてのOracleベースアセンブリにあります。



hrスキームでは、テストパッケージを作成し、コードを実装します。 作成された関数は、特定の部門の従業員に関するデータを返します。 これを行うには、返されるデータのタイプをパッケージ仕様で記述する必要があります。

create or replace package hr.test as type t_employee is record ( employee_id integer, first_name varchar2(50), last_name varchar2(50), email varchar2(50), phone_number varchar2(12), salary number(8,2), salary_recom number(8,2), department_id integer, department_name varchar2(100), city varchar2(50) ); type t_employees_table is table of t_employee; end;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

•employee_id-従業員ID

•first_name-名前

•last_name-姓

•メール-メールアドレス

•phone_number-電話

•給与-給与

•salary_recom-推奨給与

•department_id-部門ID

•department_name-部門の名前

•都市-都市



次に、関数自体について説明します。

 function get_employees_dep(p_department_id integer) return t_employees_table pipelined;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

この関数は部門IDを受け入れ、作成したt_employees_tableタイプのコレクションを返します。 pipelinedキーワードは、この関数をパイプライン化します。 一般に、パッケージの仕様は次のとおりです。

 create or replace package hr.test as type t_employee is record ( employee_id integer, first_name varchar2(50), last_name varchar2(50), email varchar2(50), phone_number varchar2(12), salary number(8,2), salary_recom number(8,2), department_id integer, department_name varchar2(100), city varchar2(50) ); type t_employees_table is table of t_employee; function get_employees_dep(p_department_id integer) return t_employees_table pipelined; end;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

パッケージ本体について考えてみましょう。これは、get_employees_dep関数の本体を記述しています。

 create or replace package body hr.test as function get_employees_dep(p_department_id integer) return t_employees_table pipelined as begin for rec in ( select emps.employee_id, emps.first_name, emps.last_name, emps.email, emps.phone_number, emps.salary, 0 as salary_recom, dep.department_id, dep.department_name, loc.city from hr.employees emps join hr.departments dep on emps.department_id = dep.department_id join hr.locations loc on dep.location_id = loc.location_id where dep.department_id = p_department_id ) loop if (rec.salary >= 8000) then rec.salary_recom := rec.salary; else rec.salary_recom := 10000; end if; pipe row (rec); end loop; end; end;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

この関数では、特定の部門の従業員に関するデータのセットを取得し、従業員の給与が8,000未満の場合、推奨される給与を10,000に設定し、コレクション全体の完了を待たずに各行が呼び出し元に送信されるという事実について、このセットの各行を分析します処理します。 関数本体にreturnキーワードがなく、パイプ行（rec）が存在することに注意してください。



pl / sqlブロックで作成された関数を呼び出すことは残っています：

 declare v_department_id integer :=100; begin for rec in ( select * from table (hr.test.get_employees_dep(v_department_id)) emps )loop --    end loop; end;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

そのため、パイプライン化されたテーブル関数を使用するだけで、パフォーマンスの低下を招くことなく、pl / sqlコードを使用して、任意の複雑なロジックで満たされた選択を行うことができます。