PL/SQLベース---複雑なデータ型とカスタム型

PL/SQLベース-複雑なデータ型とカスタム型
PLSQLでよく使用されるカスタムタイプは、レコードタイプ、PLSQLメモリテーブルタイプの2つです(テーブルのデータフィールドの単純さと複雑さに応じて、単純な配列とレコード配列のような機能をそれぞれ実現できます).
その他にも、CLOB、BFILEという大きなオブジェクトタイプがあります
一.メモリテーブルオブジェクト(コレクション)
一般的な関数
関数名
機能
COUNT
コレクション内の要素の数を返します.
DELETE
コレクション内のすべての要素を削除
DELETE(x)
要素の下にxと表示されている要素を削除するのはVARAYに対して不正です
DELETE(x,y)
XからYまでのエレメントを削除してVARAYに不正
EXTEND
集合の最後にIndex_の要素を追加by不正
EXTEND(x)
集合の最後にx個の要素対Index_を追加by不正
EXTEND(x,n)
集合の最後に要素nのx個のコピー対Index_を追加するby不正
EXIST(x)
集合要素xが初期化されている場合はTRUEに戻り、そうでない場合はFALSEに戻る
FIRST
集合の最初の要素の下の記号を返し、VARAY集合に対して常に1を返します.
LAST
集合内の最後の要素の下の記号を返します.VARAYに対する戻り値は常にCOUNTに等しいです.
LIMIT
VARRYコレクションの最大要素数Index_を返します.byコレクションとネストテーブルは役に立たない
NEXT(x)
x番目の要素の直後の要素値を返し、xが最後の要素である場合nullを返します.
PRIOR(x)
x番目の要素の直前にある要素の値を返し、xが最初の要素である場合nullを返します.
TRIM
コレクションの末端からindex_について要素を削除します.by非合法
TRIM(x)
コレクションの末端からx個の要素を削除
1 .変長配列(VARAY)-単純配列、下付き自動生成(int)、中間から削除不可

--     
TYPE type_name IS VARRAY (max_elements)
       OF element_datatype [ NOT NULL ];

DECLARE 
   TYPE t_array IS VARRAY(5) OF NUMBER;

   m_data t_array;

BEGIN 
  m_data := t_array(0,1,2,3,4);
  FOR i IN 1..5 LOOP
    DBMS_OUTPUT.put_line(m_data(i));
  END LOOP;
END;

注意:
1 .VARAY変数はストレージスペースを初期化する必要があります

  1. m_data := t_array(0,1,2,3,4);
  2. m_date.EXTEND(x[,n]);

2 .下付き文字は1から:1..N
3 .サイズはタイプ定義のサイズを超えてはいけません:TYPE t_array IS VARRAY(5) OF NUMBER
2 .索引表(Associative arrays)-索引の種類がカスタマイズされている(値が1つずつ対応している)ので、削除できます.次の表は変更されません.
配列値と一意のインデックスの関連付け、および長くなる配列は下付きで異なります

    TYPE type_name IS TABLE OF element_datatype [ NOT NULL ]
    --->
    INDEX BY BINARY_INTEGER;
    INDEX BY PLS_INTEGER;
    INDEX BY POSITIVE;
    INDEX BY NATURAL;
    INDEX BY SIGNTYPE;
    INDEX BY VARCHAR2(32767);
    INDEX BY table.column%TYPE;
    INDEX BY cursor.column%TYPE;
    INDEX BY package.variable%TYPE;
    INDEX BY package.subtype;


--    

DECLARE 
   TYPE t_associative_table IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
   v_table t_associative_table;

BEGIN
  --      v_table := t_associative_table(0,1,2,2,4);
  v_table(1) := 1;
  v_table(2) := 2;
  v_table(3) := 3;
  v_table(4) := 3;
  v_table(5) := 5;

  FOR i IN 1..5 LOOP
    IF v_table.EXISTS(i) THEN
        v_table.DELETE(i);
    END IF;
  END LOOP;  
  DBMS_OUTPUT.put_line(v_table.COUNT());
END;

3 .ネストされたテーブル(Nested tables)-インデックスタイプはINTEGER(>0)で、削除可能で、下付きは変更されません.
インデックス・タイプを指定する必要がなく、デフォルトでは整数であり、負数ではない特殊なインデックス・テーブルと見なすことができます.
データベース内のテーブルに対応可能

--     
TYPE type_name IS TABLE OF element_datatype [ NOT NULL ];

--    
DECLARE
  TYPE t_nested_table IS TABLE OF NUMBER;
  v_table t_nested_table;


BEGIN
  v_table := t_nested_table(0,1,2,2,4);
  v_table.DELETE(1);
  DBMS_OUTPUT.put_line(v_table.COUNT());
  FOR i IN 2..5 LOOP
    IF v_table.EXISTS(i) THEN
       DBMS_OUTPUT.put_line(v_table(i));
    END IF;
  END LOOP;

END;

二.レコード(レコード)
データベース内のレコードに相当するデータセット

TYPE type_name IS RECORD
    (field_declaration[, field_declaration]…);
identifier type_name;

--field_declaration        
    field_name  field_type;
    field_name  variable%TYPE/Table.column%TYPE;
    field_name  Table%ROWTYPE;
    [NUT NULL]
    [DEFAULT value]


--  

 DECLARE
   TYPE T_RECORD_DEF IS RECORD
        (
               employee_id NUMBER,
               lats_name   VARCHAR2(20) DEFAULT 'NO_NAME');
   e_record_1 T_RECORD_DEF;
   e_record_2 T_RECORD_DEF;

BEGIN
  SELECT e.EMPLOYEE_ID,e.Last_Name
  INTO e_record_1 
  FROM EMPLOYEES e WHERE e.EMPLOYEE_ID = 100;

  DBMS_OUTPUT.put_line(e_record_1.lats_name);
  DBMS_OUTPUT.put_line(e_record_2.lats_name);
END;

注意:
フィールド名は大文字と小文字を区別しません
Recordタイプは初期化する必要はありません.
RecordはTableタイプと同時に使用でき、類似のテーブル構造を構成する
%TYPE、TABLE%ROWTYPEを適切に使用します(この宣言にはデフォルト情報は含まれません).
二.ビッグオブジェクトタイプ(CLOB、NCLOB、BLOB、BFILE)
さぎょう
使用
CLOB
文字の大きいオブジェクトで、データベースの内部に格納されます.
NCLOB
データベース内に格納されたマルチバイト文字の大きなオブジェクト.
BLOB
データベース内に格納されたバイナリ・オブジェクト.
BFILE
データベースの外部に格納されたバイナリファイル.

-- Writw...
    DECLARE
       directions CLOB;
       amount BINARY_INTEGER;
       offset INTEGER;
       first_direction VARCHAR2(100);
       more_directions VARCHAR2(500);
    BEGIN
       --Delete any existing rows for 'Munising Falls' so that this
       --example can be executed multiple times
       DELETE
         FROM waterfalls
        WHERE falls_name='Munising Falls';

       --Insert a new row using EMPTY_CLOB(  ) to create a LOB locator
       INSERT INTO waterfalls
                 (falls_name,falls_directions)
          VALUES ('Munising Falls',EMPTY_CLOB(  ));

       --Retrieve the LOB locator created by the previous INSERT statement
       SELECT falls_directions
         INTO directions
         FROM waterfalls
        WHERE falls_name='Munising Falls';

       --Open the LOB; not strictly necessary, but best to open/close LOBs.
       DBMS_LOB.OPEN(directions, DBMS_LOB.LOB_READWRITE);

       --Use DBMS_LOB.WRITE to begin
       first_direction := 'Follow I-75 across the Mackinac Bridge.';
       amount := LENGTH(first_direction);  --number of characters to write
       offset := 1; --begin writing to the first character of the CLOB
       DBMS_LOB.WRITE(directions, amount, offset, first_direction);

       --Add some more directions using DBMS_LOB.WRITEAPPEND
       more_directions := ' Take US-2 west from St. Ignace to Blaney Park.'
                       || ' Turn north on M-77 and drive to Seney.'
                       || ' From Seney, take M-28 west to Munising.';
       DBMS_LOB.WRITEAPPEND(directions,
                            LENGTH(more_directions), more_directions);

       --Add yet more directions
       more_directions := ' In front of the paper mill, turn right on H-58.'
                       || ' Follow H-58 to Washington Street. Veer left onto'
                       || ' Washington Street. You''ll find the Munising'
                       || ' Falls visitor center across from the hospital at'
                       || ' the point where Washington Street becomes'
                       || ' Sand Point Road.';
       DBMS_LOB.WRITEAPPEND(directions,
                            LENGTH(more_directions), more_directions);

       --Close the LOB, and we are done.
       DBMS_LOB.CLOSE(directions);
    END;

---Reading...
    DECLARE
       directions CLOB;
       directions_1 VARCHAR2(300);
       directions_2 VARCHAR2(300);
       chars_read_1 BINARY_INTEGER;
       chars_read_2 BINARY_INTEGER;
       offset INTEGER;
    BEGIN
       --Retrieve the LOB locator inserted previously
       SELECT falls_directions
         INTO directions
         FROM waterfalls
        WHERE falls_name='Munising Falls';

       --Begin reading with the first character
       offset := 1;

       --Attempt to read 229 characters of directions, chars_read_1 will
       --be updated with the actual number of characters read
       chars_read_1 := 229;
       DBMS_LOB.READ(directions, chars_read_1, offset, directions_1);

       --If we read 229 characters, update the offset and try to
       --read 255 more.
       IF chars_read_1 = 229 THEN
          offset := offset + chars_read_1;
          chars_read_2 := 255;
          DBMS_LOB.READ(directions, chars_read_2, offset, directions_2);
       ELSE
          chars_read_2 := 0;
          directions_2 := '';
       END IF;

       --Display the total number of characters read
       DBMS_OUTPUT.PUT_LINE('Characters read = ' ||
                            TO_CHAR(chars_read_1+chars_read_2));

       --Display the directions
       DBMS_OUTPUT.PUT_LINE(directions_1);
       DBMS_OUTPUT.PUT_LINE(directions_2);
    END;

BFILE処理は事前にDIRCTORYを確立する必要があり、現在のユーザにはこのDIRECTORYを読み取る権限がある

CREATE DIRECTORY bfile_data AS '      ：c:\data\file.txt';

GRANT READ ON DIRECTORY bfile_data TO      ;

--  
    DECLARE
       web_page BFILE;
    BEGIN
       --Delete row for Tannery Falls so this example can
       --be executed multiple times
       DELETE FROM waterfalls WHERE falls_name='Tannery Falls';

       --Invoke BFILENAME to create a BFILE locator
       web_page := BFILENAME('BFILE_DATA','Tannery Falls.htm');

       --            

       --Save our new locator in the waterfalls table
       INSERT INTO waterfalls (falls_name, falls_web_page)
          VALUES ('Tannery Falls',web_page);
    END;

--  
    DECLARE
       web_page BFILE;
       html RAW(60);
       amount BINARY_INTEGER := 60;
       offset INTEGER := 1;
    BEGIN
       --Retrieve the LOB locat0r for the web page
       SELECT falls_web_page
         INTO web_page
         FROM waterfalls
        WHERE falls_name='Tannery Falls';

       --Open the locator, read 60 bytes, and close the locator
       DBMS_LOB.OPEN(web_page);
       DBMS_LOB.READ(web_page, amount, offset, html);
       DBMS_LOB.CLOSE(web_page);

       --Uncomment following line to display results in hex
       --DBMS_OUTPUT.PUT_LINE(RAWTOHEX(html));

       --Cast RAW results to a character string we can read
       DBMS_OUTPUT.PUT_LINE(UTL_RAW.CAST_TO_VARCHAR2(html));
    END;


--    
    DECLARE
       Tannery_Falls_Directions BFILE
          := BFILENAME('BFILE_DATA','TanneryFalls.directions');
       directions CLOB;
       destination_offset INTEGER := 1;
       source_offset INTEGER := 1;
       language_context INTEGER := DBMS_LOB.default_lang_ctx;
       warning_message INTEGER;
    BEGIN
       --Delete row for Tannery Falls, so this example
       --can run multiple times.
       DELETE FROM waterfalls WHERE falls_name='Tannery Falls';

       --Insert a new row using EMPTY_CLOB(  ) to create a LOB locator
       INSERT INTO waterfalls
                 (falls_name,falls_directions)
          VALUES ('Tannery Falls',EMPTY_CLOB(  ));

       --Retrieve the LOB locator created by the previous INSERT statement
       SELECT falls_directions
         INTO directions
         FROM waterfalls
        WHERE falls_name='Tannery Falls';

       --Open the target CLOB and the source BFILE
       DBMS_LOB.OPEN(directions, DBMS_LOB.LOB_READWRITE);
       DBMS_LOB.OPEN(Tannery_Falls_Directions);

       --Load the contents of the BFILE into the CLOB column
       DBMS_LOB.LOADCLOBFROMFILE 
(directions, Tannery_Falls_Directions,
                                 DBMS_LOB.LOBMAXSIZE,
                                 destination_offset, source_offset,
                                 NLS_CHARSET_ID('US7ASCII'),
                                 language_context, warning_message);

       --Check for the only possible warning message.
       IF warning_message = DBMS_LOB.WARN_INCONVERTIBLE_CHAR THEN
            dbms_output.put_line(
               'Warning! Some characters couldn''t be converted.');
       END IF;

       --Close both LOBs
       DBMS_LOB.CLOSE(directions);
       DBMS_LOB.CLOSE(Tannery_Falls_Directions);
    END;

DBMS_LOB常用関数:具体的な使い方は関連ドキュメントを参照
関数＃カンスウ＃
機能
APPEND
ソースLOBの内容を宛先LOBに追加
CLOSE
開いているLOBを閉じる
FILECLOSE
開いているBFILEロケータが指すOSファイルを閉じる
FILECLOSEALL
現在のセッションで開いているすべてのBFILEファイルを閉じる
FILEEXISTS
ファイルを確定loc対応OSファイルが存在するか、1:存在0:存在しない
FILEGETNAME
BFILEロケータに対応するディレクトリの別名とファイル名を取得
FILEISOPEN
BFILE対応OSファイルが開いているかどうかを確認します
FILEOPEN
ファイルを開く
COPY
ソースLOBから宛先LOBへのデータのコピー
ERASE
LOBのすべてまたは一部を削除
TRIM
指定した長さにLOB値を減らす
WRITE
LOBへのデータの書き込み
INSTR
特定のスタイルデータを返し、LOBのあるオフセット位置からN回出現する特定の位置
OPEN
LOB,open_を開くmode(読み取り専用:dbms_lob.lob_readonly、dbms_lob.lob_readwrite)
GETLENGTH
LOBの長さを取得
READ
LOBからデータを読み出す
SUBSTR
文字処理関数SUBSTRの使用方法と同様
WRITEAPPEND
バッファデータをLOB末尾に書き込む
注:CLOBなどの大きい対象は直接IS NULLを使って空(EMPTY_CLOB()IS NULL)を判定することができなくて、正しい判断は以下の通りです:

  IF some_clob IS NULL THEN
       --There is no data
    ELSEIF DBMS_LOB.GETLENGTH(some_clob) = 0 THEN
       --There is no data
    ELSE
       --Only now is there data
    END IF;

CLOB、NCLOBの処理と文字列は類似しています:|、substrなどの文字列に対する操作をサポートします;