Table Functions
今日はOOUG(OTTAWA Oracle Users Group)からの引用です。 PL/SQLに関するいくつかのテクニックをまとめて紹介している プレゼンテーション資料です。 例えば。。。 ・DBMS_PROFILER（9月30日号でもご紹介しています） http://imoment.web.fc2.com/20060930.html ・自立型トランザクション （親のトランザクションとは独立して別のトランザクションとして動作できます） ・テーブルファンクションのパイプライン制御 （今回取り上げます） ・DBMS_XPLAN （autotraceの実行計画表示に比べて色々なことができます） ・DBMS_LOCK.SLEEP （unixのsleepコマンドと同様に眠れます） ・select for update wait nsec （指定秒分まつことができます） などなど、色々とシンプルに紹介されています。 ほとんど英文なしでひたすら例が記載されていますので、 英語が苦手でも大丈夫だと思います。 本当に英文が全然ないので、今回の引用は 選択の余地がありませんでした^^; ■ OTTAWA Oracle Users Group, Inc. http://www.oug-ottawa.org/ (memberで無くてもDownloadsより資料は参照できます) ■ 引用資料 http://www.oug-ottawa.org/oougpresentations/2005nov24_plsql.zip ＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿ ￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣ 記事本文 ＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿ ￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣ ◆ Table Functions Produce collection of rows (nested table / varray) Query results or assign to collection variable Can use Cursor Expressions Table functions can be: * pipelined -Rows are returned as they are produced instead of all at once, in one batch, when function completes * parallelized * streamed ＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿ ￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣ 英語の解釈 ※自然な語順で解釈する癖をつけるために 敢えて不自然な日本語になっています。 ()書きは後続修飾節の修飾対象です。 ＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿ ￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣ ◆ Table Functions Produce collection of rows 行のコレクションを生成します。 (nested table / varray) ネスト表 / varray Query results or assign to collection variable 参照結果を戻したり、コレクション型の変数に割り当てたりできます。 Can use Cursor Expressions カーソル式を使用できます。 Table functions can be: テーブルファンクションはできます * pipelined パイプライン制御 -Rows are returned as they are produced 生成されると同時に行は戻ります。 instead of all at once, in one batch, 全ての行を一つのバッチで一度に戻すのではなく when function completes ファンクションが完了した時に（戻すのではなく） * parallelized パラレル化 * streamed ストリーム化 ＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿ ￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣ 英語解説 ＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿ ￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣ Table Functions ※実際のテーブル内容の変わりに関数の戻り値をテーブルの内容として 使用することができます。 select * from table(function_name(cursor(select * from ex_table))); Produce collection of rows ※当然この関数の戻り値は行の集まりです。 (nested table / varray) ※ネスト表は名前の通り、テーブルの中にさらにテーブルのような配列を 持つことができる機能です。変数として扱えるのでテーブルファンクションの 戻り値としても使用できます。 type rec_typ is record ( col1 number, col2 varchar(10)); type next_typ is table of rec_typ; varrayは配列です。 create type eg_varray as varray(10) of varchar2(10); Can use Cursor Expressions ※テーブルファンクションへの引数としてカーソル式を渡すことができます。 以下の例ではselect結果のカーソルをテーブルファンクションに 引渡しています。 select * from table(function_name(cursor(select * from ex_table))); * pipelined ※パイプライン化することで複数のレコードを同時並行的に処理 してくれます。(例は解説に掲載しました) ＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿ ￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣ ★ 英語ぷちクイズ ★ ※答えと思うリンクをぷちっとクリックしてください。 ＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿ ￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣ ☆ちょっぴり不自然なのはどっち？ ◆they recently bought a house. ┗ http://clickenquete.com/a/a.php?M0002066Q0017596A12cdc ◆they lately bought a house. ┗ http://clickenquete.com/a/a.php?M0002066Q0017596A2b27c ○結果を見る ┗ http://clickenquete.com/a/r.php?Q0017596Ce615 締切：2006年11月02日23時00分 協力：クリックアンケート http://clickenquete.com/ 似ているようで少し違うrecentlyとlatelyの ニュアンスの違いご存知ですか？ こたえは次回に発表します。 ★★ランキングにも参加しております★★ こちらも「ぽち」していただけるととても嬉しいです。(^-^) http://english.blogmura.com/in/080814.html ■ 前回のこたえ ☆どれが一番命令口調ですか？ ◆You should get this work done. ◆You must get this work done. ◆You ought to get this work done. ◆You had better get this work done. ○結果を見る ┗ http://clickenquete.com/a/r.php?Q0017445Cc87c 答えはぶっちぎりでyou had betterです。 最近はあまり見かけませんが以前は英語の先生でも had betterの方がshouldよりも低姿勢だと 説明している人もいました。 一見やわらかい表現ですが、極端なシチュエーションだと 銃をつきつけるぐらいの勢いがあります。 以下のようにorと組み合わせて使うことが良くあります。 you had better 命令 OR! 命令を守らない場合の何かしら良くない結末 「you had better get this work done, or the Boss will be mad!」 上司に使ったら首になるかも！？ 残りはcontext依存ですが、 had better,must,should,out toの順でしょうか。 ＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿ ￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣ Oracle解説 ＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿ ￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣ 典型的なテーブルファンクションの使用例としては、 他システムから送られてくるcsvファイルなどの 効率的な取り込みなどがあります。 他システムから送られてくるcsvファイルを 外部表(例ではex_table)で読み込んで、 plsqlで1行ずつフェッチして、 複雑な変換などを施してダイレクトに実表に入れ込んでと。。。 これをパラレルでパイプライン化して実行すれば、 SQLLDRなどでは叶わない柔軟な変換を実現でき、 中間テーブルを利用するのに比べると遥かに早いスピードも実現できます。 ※外部表について知りたい方は後続の説明をご覧ください create view ex_view as select * from table(function_name(cursor(select * from ex_table))); insert into real_tab select /*+ APPEND */ from ex_view; パイプライン化することで複数のレコードを同時並行的に処理 してくれます。 create or replace package ex_pkg is type ex_refcur is ref cursor return ex_table%rowtype; type ex_recset_typ is record( col1 number, col2 varchar2(10) ); type ex_recset is table of ex_recset_typ; function ex_func(aaa ex_refcur) return ex_recset pipelined; end ex_pkg; / create or replace package body ex_pkg is function ex_func(aaa ex_refcur) return ex_recset pipelined is outrec ex_recset_typ; inrec aaa%rowtype begin loop fetch aaa into inrec; exit when aaa%notfound; outrec.col1 := inrec.col1; outrec.col2 := inrec.col2; pipe row(outrec); end loop; colse aaa; return; end; end ex_pkg; / ==== 外部表ってなに？ ココカラ ================== 外部表とはあたかもテーブルを検索するように 外部のOSなどのファイルにアクセスする機能です。 例を見れば一目瞭然です。 conn / as sysdba create or replace directory ex_dir as '/tmp/test_dir'; grant read,write on directory ex_dir to public; create table ex_table (col1 number, col2 varchar2(10) ) organization external ( type oracle_loader -- oracle_loaderの代わりにoracle_datapumpとすることで -- data_pumpのダンプファイルを外部表で参照できます。 default directory ex_dir access parameters -- アクセスパラメータの詳細はユーティリティマニュアル -- をご確認ください。 ( records delimited by newline badfile ex_dir:'%a_%p.bad' logfile ex_dir:'%a_%p.log' fields teminated by ',' missing field values are null (col1,col2) ) location ('ex1.dat','ex2.dat') ) parallel 3 reject limit unlimited; alter session enable parallel dml; insert into real_tab select * from ex_table; ==== 外部表ってなに？ ココマデ ================== ＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿ ￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣ 編集後記 ＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿ ￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣ 今号からご購読いただいた皆様はじめまして。 本日も最後まで読んでいただきありがとうございます。 先週から風邪気味だったのですが、気にせず夜更かしや 残業などしまくっていたら見事に持病の喘息が併発！ 仕事休めない状況なのでしんどい一週間でした。。。 最近Mr.BigのTo be with youが何故か頭に流れたので 通勤時間に聞いています。 ディープインパクト事件は結局真相は闇の中に 埋もれたままになるのでしょうか。 ディープインパクトの強さは薬のお陰では なかったことを本当に心から強く願います。 それではまた。 ＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿ ￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣ おわりに ＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿ ￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣ 記述誤りなどのご指摘、 記事に関する疑問点・質問・感想・ご意見・ご感想など yakusa_oracle@yahoo.co.jpまでお願い致します。 簡単な自己紹介はこちら http://pr2.cgiboy.com/S/3191274 バックナンバー兼ブログはこちら http://imoment.web.fc2.com/ 登録・解除はこちらから http://www.mag2.com/m/0000200441.htm
