2.2 表でのLOB値の挿入および更新

Oracle Databaseには、データベース表のLOB列で使用可能なデータを挿入および更新する様々な方法が用意されています。

2.2.1 バッファを使用した挿入および更新

文字列は、CLOBまたはNCLOB列に直接挿入できます。同様に、RAWバッファをBLOB列に挿入できます。これは、LOBにデータを挿入する最も効率的な方法です。

次のコード・スニペットは、CLOB列に文字列を挿入します。

/* Store records in the archive table Online_media: */ 
INSERT INTO Online_media (product_id, product_text) VALUES (3060, 'some text about this CRT Monitor');

次のコード・スニペットは、CLOB列の値を文字バッファで更新します。

UPDATE Online_media set product_text = 'some other text' where product_id = 3060;

関連項目:

INSERTおよびUPDATE操作の詳細は、LOBのデータ・インタフェースを参照してください

2.2.2 別の表からのLOBの選択による挿入および更新

同じ表または別の表のLOB列からデータを選択すると、表のLOB列に挿入できます。SQL演算子またはPL/SQL関数から返されるLOBを選択して、表のLOB列にデータを挿入できます。

複数の表の一部である列からデータを選択する場合は、次の条件を満たしていることを確認します。

  • LOBデータ型は、表の両方の列で同じです。
  • 両方の列で使用される2つのLOBデータ型間の暗黙的な変換が可能です。

BLOBCLOBまたはNCLOBが、ある行から同じ表または別の表の行にコピーされる場合は、LOBロケータのみでなく実際のLOB値がコピーされます。

次のコード・スニペットでは、別の表からLOBを選択してLOB列を挿入する方法を示します。列online_media.product_textおよびprint_media.ad_sourcetextは、どちらもCLOB型です。

/* Insert values into Print_media by selecting from Online_media: */ 
INSERT INTO Print_media (product_id, ad_id, ad_sourcetext) 
(SELECT product_id, 11001, product_text FROM Online_media WHERE product_id = 3060);

/* Insert values into Print_media by selecting a SQL function returning a CLOB */
INSERT INTO Print_media (product_id, ad_id, ad_sourcetext)
(SELECT product_id, 11001, substr(product_text, 5) FROM Online_media WHERE product_id = 3060);

/* Updating a row by selecting a LOB from another table (persistent LOBs) */ 

UPDATE Print_media SET ad_sourcetext = (SELECT product_text FROM online_media WHERE product_id = 3060);
 WHERE product_id = 3060 AND ad_id = 11001;

/* Updating a row by selecting a SQL function returning a CLOB */

UPDATE Print_media SET ad_sourcetext = (SELECT substr(product_text, 5) FROM online_media WHERE product_id = 3060); 
WHERE product_id = 3060 AND ad_id = 11001;

次のコード・スニペットは、別の表からのLOBの選択によるLOB列の更新を示しています。

/* Updating a row by selecting a LOB from another table (persistent LOBs) */ 
UPDATE Print_media SET ad_sourcetext = (SELECT product_text FROM online_media WHERE product_id = 3060);
WHERE product_id = 3060 AND ad_id = 11001;

/* Updating a row by selecting a SQL function returning a CLOB */ 
UPDATE Print_media SET ad_sourcetext = (SELECT substr(product_text, 5) FROM online_media WHERE product_id = 3060) 
WHERE product_id = 3060 AND ad_id = 11001;

関連項目:

2.2.3 NULLまたは空のLOBを使用した挿入および更新

永続LOB (表内のLOB列またはユーザーが定義したオブジェクト型のLOB属性)を、NULLまたは空に設定できます。

NULLのLOB値の挿入

NULLに設定された永続LOBにロケータはありません。NULL値はロケータではなく、表内の行に格納されます。これは、スカラー・データ型の場合と同じプロセスです。NULL値をLOB列に挿入するには、次のような文を使用します。

INSERT INTO print_media(product_id, ad_id, ad_sourcetext) VALUES (1, 1, NULL);

これは、次のようなSELECT文を使用して、LOBにNULL値が含まれているかどうかを確認する場合に役立ちます。

SELECT COUNT (*) FROM print_media WHERE ad_graphic IS NULL;

注意:

他のプログラム・インタフェースでは、NULL LOBに対してDBMS_LOB関数またはLOB APIをコールできないため、SQLのUPDATE文を使用してLOB列をNULL以外(または空)の値にリセットする必要があります。

EMPTY LOB値の挿入

DBMS_LOB.WRITEOCILobWrite2などのAPIを使用して永続LOBにデータを書き込む前に、LOB列をNULL以外にする必要があります。つまり、空または移入済のLOB値を指すロケータが含まれている必要があります。

BLOB列の値を初期化するには、デフォルトの述語としてEMPTY_BLOB()関数を使用します。同様に、CLOB列またはNCLOB列の値を初期化するには、EMPTY_CLOB()関数を使用します。INSERTおよびUPDATE文でRETURNING句を使用すると、APIを使用してLOBを書き込む際のラウンドトリップ回数を最小限に抑えられます。

次のPL/SQLブロックでは、EMPTY_CLOB()関数を使用して空のLOBでCLOB列を初期化し、EMPTY_CLOB()関数を使用して空のCLOBで列のLOB値を更新します。

DECLARE
    c CLOB;
    amt INTEGER := 11;
    buf VARCHAR(11) := 'Hello there';
BEGIN 
  /* Insert empty_clob() */
  INSERT INTO Print_media(product_id, ad_id, ad_sourcetext) VALUES (1, 1, EMPTY_CLOB()) RETURNING ad_source INTO c;
  /* The following statement updates the persistent LOB directly */
  DBMS_LOB.WRITE(c, amt, 1, buf);

  /* Update column to an empty_clob() */
  UPDATE Print_media SET ad_sourcetext = EMPTY_CLOB() WHERE product_id = 2 AND ad_id = 2 RETURNING ad_source INTO c;
  /* The following statement updates the persistent LOB directly */
  DBMS_LOB.WRITE(c, amt, 1, buf);
END;
/

2.2.4 LOBロケータを使用した挿入および更新

以前に永続LOBロケータまたは一時LOBロケータによって移入されたLOB変数を持つプログラム・インタフェースを使用している場合は、LOBバインド変数を初期化して行を挿入できます。

LOB変数に永続LOBまたは一時LOBを移入するには、SQLを使用してデータベースからLOBを選択するか、一時LOBを作成します。この項では、様々なプログラム環境でこれを実現する方法について説明します。

2.2.4.1 PL/SQL: 初期化したLOBロケータ・バインド変数を使用した行の挿入

次のコード・スニペットは、PL/SQL APIを使用して初期化したLOBロケータ・バインド変数を使用して行を挿入する方法を示しています。

/* inserting a row through an insert statement */

CREATE OR REPLACE PROCEDURE insertLOB_proc (Lob_loc IN BLOB) IS
BEGIN
  /* Insert the BLOB into the row */
  DBMS_OUTPUT.PUT_LINE('------------ LOB INSERT EXAMPLE ------------');
  INSERT INTO print_media (product_id, ad_id, ad_photo) 
        VALUES (3106, 60315, Lob_loc);
END;
/

2.2.4.2 JDBC (Java): 初期化したLOBロケータ・バインド変数を使用した行の挿入

次のコード・スニペットは、JDBC APIを使用して初期化したLOBロケータ・バインド変数を使用して行を挿入する方法を示しています。

// Core JDBC classes: 
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
 
// Oracle Specific JDBC classes: 
import oracle.sql.*;
import oracle.jdbc.driver.*;
 
public class linsert
{
  public static void main (String args [])
       throws Exception
  {
    // Load the Oracle JDBC driver
    DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver ());
    // Connect to the database: 
    Connection conn =
       DriverManager.getConnection ("jdbc:oracle:oci8:@", "pm", "password");
 
    // It's faster when auto commit is off: 
    conn.setAutoCommit (false);
 
    // Create a Statement: 
    Statement stmt = conn.createStatement ();
    try
    {
       ResultSet rset = stmt.executeQuery (
  "SELECT ad_photo FROM Print_media WHERE product_id = 3106 AND ad_id = 13001");
       if (rset.next())
       {
          // retrieve the LOB locator from the ResultSet
          BLOB adphoto_blob = ((OracleResultSet)rset).getBLOB (1);
          OraclePreparedStatement ops = 
          (OraclePreparedStatement) conn.prepareStatement(
"INSERT INTO Print_media (product_id, ad_id, ad_photo) VALUES (2268, "
+ "21001, ?)");
          ops.setBlob(1, adphoto_blob);
          ops.execute();
          conn.commit();
          conn.close();
       }
    }
    catch (SQLException e)
    {
       e.printStackTrace();
    }
  }
}

2.2.4.3 OCI (C): 初期化したLOBロケータ・バインド変数を使用した行の挿入

次のコード・スニペットでは、OCI APIを使用して初期化したLOBロケータ・バインド変数を使用して行を挿入する方法を示します。

/* Insert the Locator into table using Bind Variables. */
#include <oratypes.h>
#include <lobdemo.h>
void insertLOB_proc(OCILobLocator *Lob_loc, OCIEnv *envhp,
                    OCIError *errhp, OCISvcCtx *svchp, OCIStmt *stmthp)
{
  int            product_id;
  OCIBind       *bndhp3;
  OCIBind       *bndhp2;
  OCIBind       *bndhp1;
  text          *insstmt = 
   (text *) "INSERT INTO Print_media (product_id, ad_id, ad_sourcetext) \
             VALUES (:1, :2, :3)";

  printf ("----------- OCI Lob Insert Demo --------------\n");   
  /* Insert the locator into the Print_media table with product_id=3060 */
  product_id = (int)3060;

  /* Prepare the SQL statement */
  checkerr (errhp, OCIStmtPrepare(stmthp, errhp, insstmt, (ub4) 
                                  strlen((char *) insstmt),
                                  (ub4) OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT));

  /* Binds the bind positions */
  checkerr (errhp, OCIBindByPos(stmthp, &bndhp1, errhp, (ub4) 1,
                                (void *) &product_id, (sb4) sizeof(product_id),
                                SQLT_INT, (void *) 0, (ub2 *)0, (ub2 *)0,
                                (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT));

  checkerr (errhp, OCIBindByPos(stmthp, &bndhp1, errhp, (ub4) 2,
                                (void *) &product_id, (sb4) sizeof(product_id),
                                SQLT_INT, (void *) 0, (ub2 *)0, (ub2 *)0,
                                (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT));

  checkerr (errhp, OCIBindByPos(stmthp, &bndhp2, errhp, (ub4) 3,
                                (void *) &Lob_loc, (sb4) 0,  SQLT_CLOB,
                                (void *) 0, (ub2 *)0, (ub2 *)0,
                                (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT));

  /* Execute the SQL statement */
  checkerr (errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0,
                                  (CONST OCISnapshot*) 0, (OCISnapshot*) 0,  
                                  (ub4) OCI_DEFAULT));
}

2.2.4.4 Pro*C/C++ (C/C++): 初期化したLOBロケータ・バインド変数を使用した行の挿入

次のコード・スニペットでは、Pro*C/C++ APIを使用して初期化したLOBロケータ・バインド変数を使用して行を挿入する方法を示します。

#include <oci.h>
#include <stdio.h>
#include <sqlca.h>

void Sample_Error()
{
   EXEC SQL WHENEVER SQLERROR CONTINUE;
   printf("%.*s\n", sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc);
   EXEC SQL ROLLBACK WORK RELEASE;
   exit(1);
}

void insertUseBindVariable_proc(Rownum, Lob_loc)
   int Rownum, Rownum2;
   OCIBlobLocator *Lob_loc;
{
   EXEC SQL WHENEVER SQLERROR DO Sample_Error();
   EXEC SQL INSERT INTO Print_media (product_id, ad_id, ad_photo)
      VALUES (:Rownum, :Rownum2, :Lob_loc);
}
void insertBLOB_proc()
{
   OCIBlobLocator *Lob_loc;

   /* Initialize the BLOB Locator: */
   EXEC SQL ALLOCATE :Lob_loc;

   /* Select the LOB from the row where product_id = 2268 and ad_id=21001: */
   EXEC SQL SELECT ad_photo INTO :Lob_loc
      FROM Print_media WHERE product_id = 2268 AND ad_id = 21001;

   /* Insert into the row where product_id = 3106 and ad_id = 13001: */
   insertUseBindVariable_proc(3106, 13001, Lob_loc);

   /* Release resources held by the locator: */
   EXEC SQL FREE :Lob_loc;
}

void main()
{
   char *samp = "pm/password";
   EXEC SQL CONNECT :pm;
   insertBLOB_proc();
   EXEC SQL ROLLBACK WORK RELEASE;
}

2.2.4.5 Pro*COBOL (COBOL): 初期化したLOBロケータ・バインド変数を使用した行の挿入

次のコード・スニペットでは、Pro*COBOL APIを使用して初期化したLOBロケータ・バインド変数を使用して行を挿入する方法を示します。

COBOL (Pro*COBOL)でLOBロケータ・バインド変数を初期化することにより、行を挿入できます。

   IDENTIFICATION DIVISION.
       PROGRAM-ID. INSERT-LOB.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.

       01 BLOB1 SQL-BLOB.
       01  USERID   PIC X(11) VALUES "PM/password".
           EXEC SQL INCLUDE SQLCA END-EXEC.

       PROCEDURE DIVISION.
       INSERT-LOB.
    
           EXEC SQL WHENEVER SQLERROR DO PERFORM SQL-ERROR END-EXEC.
           EXEC SQL CONNECT :USERID END-EXEC.
      * Initialize the BLOB locator
           EXEC SQL ALLOCATE :BLOB1 END-EXEC.
      * Populate the LOB
           EXEC SQL WHENEVER NOT FOUND GOTO END-OF-BLOB END-EXEC.
           EXEC SQL 
              SELECT AD_PHOTO INTO :BLOB1 FROM PRINT_MEDIA
               WHERE PRODUCT_ID = 2268 AND AD_ID = 21001 END-EXEC.
  
      * Insert the value with PRODUCT_ID of 3060
           EXEC SQL 
              INSERT INTO PRINT_MEDIA (PRODUCT_ID, AD_PHOTO)
                 VALUES (3060, 11001, :BLOB1)END-EXEC.

      * Free resources held by locator
       END-OF-BLOB.
           EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC.
           EXEC SQL FREE :BLOB1 END-EXEC.
           EXEC SQL ROLLBACK WORK RELEASE END-EXEC.
           STOP RUN.

       SQL-ERROR.
           EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC.
           DISPLAY " ".
           DISPLAY "ORACLE ERROR DETECTED:".
           DISPLAY " ".
           DISPLAY SQLERRMC.
           EXEC SQL ROLLBACK WORK RELEASE END-EXEC.
           STOP RUN.