6 LOBに対するDDL文とDML文
DDL文とDML文はLOBに対して機能します。
内容は次のとおりです。
関連項目:
4000バイトを超えるバインドが必要なときLOBにINSERTする方法は、次の「INSERT操作とUPDATE操作でのすべてのサイズのバインド」の項を参照してください。
6.1 1つ以上のLOB列を含む表の作成
1つ以上のLOB列を含む表を作成できます。
EMPTY_BLOB()およびEMPTY_CLOB()ファンクションを使用すると、LOBは初期化されますが、データは移入されません。また、空のLOBがNULLでないことにも注意してください。
関連項目:
次のものを含むCREATE TABLEおよびALTER TABLE文でLOBを使用する場合の構文の完全な仕様については、『Oracle Database SQL言語リファレンス』を参照してください。
-
BLOB、CLOB、NCLOBおよびBFILE列 -
EMPTY_BLOBおよびEMPTY_CLOBファンクション -
埋込みオブジェクトのLOB属性および永続LOB列に対するLOB STORAGE句
シナリオ
この例では、次のサンプル・スキーマを使用します。
-
人事管理(
HR) -
受注(
OE) -
製品メディア(
PM)
ノート:
HRスキーマとOEスキーマを作成した後にPMスキーマを作成する必要があることに注意してください。
ノート:
SQL DDLを使用すると1つ以上のLOB列を含む表を直接作成できるため、DBMS_LOBパッケージを使用する必要はありません。
関連項目:
サンプル・スキーマの詳細は、『Oracle Databaseサンプル・スキーマ』を参照してください。/* Setup script for creating Print_media,
Online_media and associated structures
*/
DROP USER pm CASCADE;
DROP DIRECTORY ADPHOTO_DIR;
DROP DIRECTORY ADCOMPOSITE_DIR;
DROP DIRECTORY ADGRAPHIC_DIR;
DROP INDEX onlinemedia CASCADE CONSTRAINTS;
DROP INDEX printmedia CASCADE CONSTRAINTS;
DROP TABLE online_media CASCADE CONSTRAINTS;
DROP TABLE print_media CASCADE CONSTRAINTS;
DROP TYPE textdoc_typ;
DROP TYPE textdoc_tab;
DROP TYPE adheader_typ;
DROP TABLE adheader_typ;
CREATE USER pm identified by password;
GRANT CONNECT, RESOURCE to pm;
CREATE DIRECTORY ADPHOTO_DIR AS '/tmp/';
CREATE DIRECTORY ADCOMPOSITE_DIR AS '/tmp/';
CREATE DIRECTORY ADGRAPHIC_DIR AS '/tmp/';
CREATE DIRECTORY media_dir AS '/tmp/';
GRANT READ ON DIRECTORY ADPHOTO_DIR to pm;
GRANT READ ON DIRECTORY ADCOMPOSITE_DIR to pm;
GRANT READ ON DIRECTORY ADGRAPHIC_DIR to pm;
GRANT READ ON DIRECTORY media_dir to pm;
CONNECT pm/password (or &pass);
COMMIT;
CREATE TABLE a_table (blob_col BLOB);
CREATE TYPE adheader_typ AS OBJECT (
header_name VARCHAR2(256),
creation_date DATE,
header_text VARCHAR(1024),
logo BLOB );
CREATE TYPE textdoc_typ AS OBJECT (
document_typ VARCHAR2(32),
formatted_doc BLOB);
CREATE TYPE Textdoc_ntab AS TABLE of textdoc_typ;
CREATE TABLE adheader_tab of adheader_typ (
Ad_finaltext DEFAULT EMPTY_CLOB(), CONSTRAINT
Take CHECK (Take IS NOT NULL), DEFAULT NULL);
CREATE TABLE online_media
( product_id NUMBER(6),
product_photo ORDSYS.ORDImage,
product_photo_signature ORDSYS.ORDImageSignature,
product_thumbnail ORDSYS.ORDImage,
product_video ORDSYS.ORDVideo,
product_audio ORDSYS.ORDAudio,
product_text CLOB,
product_testimonials ORDSYS.ORDDoc);
CREATE UNIQUE INDEX onlinemedia_pk
ON online_media (product_id);
ALTER TABLE online_media
ADD (CONSTRAINT onlinemedia_pk
PRIMARY KEY (product_id), CONSTRAINT loc_c_id_fk
FOREIGN KEY (product_id) REFERENCES oe.product_information(product_id)
);
CREATE TABLE print_media
(product_id NUMBER(6),
ad_id NUMBER(6),
ad_composite BLOB,
ad_sourcetext CLOB,
ad_finaltext CLOB,
ad_fktextn NCLOB,
ad_testdocs_ntab textdoc_tab,
ad_photo BLOB,
ad_graphic BFILE,
ad_header adheader_typ,
press_release LONG) NESTED TABLE ad_textdocs_ntab STORE AS textdocs_nestedtab;
CREATE UNIQUE INDEX printmedia_pk
ON print_media (product_id, ad_id);
ALTER TABLE print_media
ADD (CONSTRAINT printmedia_pk
PRIMARY KEY (product_id, ad_id),
CONSTRAINT printmedia_fk FOREIGN KEY (product_id)
REFERENCES oe.product_information(product_id)
);6.2 LOBを含むネストした表の作成
LOBを含むネストした表を作成できます。
LOB属性を含むオブジェクト型を作成してから、そのオブジェクト型を基にネストした表を作成する必要があります。次の例のPrint_media表には、textdoc_tab型を持つネストした表ad_textdoc_ntabが含まれます。この型は、次の2種類のLOBデータ型を使用しています。
-
BFILE: 広告用のグラフィック -
CLOB: 広告用の記録
ネストした表の実際の埋込みは、その表を含む構造が定義されるときに行われます。この例では、次に示すように、Print_media表の作成時にNESTED TABLE文によって行われます。
/* Create type textdoc_typ as the base type for the nested table textdoc_ntab, where textdoc_ntab contains a LOB: */ CREATE TYPE textdoc_typ AS OBJECT ( document_typ VARCHAR2(32), formatted_doc BLOB ); / /* The type has been created. Now you need a */ /* nested table of that type to embed in */ /* table Print_media, so: */ CREATE TYPE textdoc_ntab AS TABLE of textdoc_typ; / CREATE TABLE textdoc_ntable ( id NUMBER, ntab_col textdoc_ntab) NESTED TABLE ntab_col STORE AS textdoc_nestedtab; DROP TYPE textdoc_typ force; DROP TYPE textdoc_ntab; DROP TABLE textdoc_ntable;
関連項目:
-
CREATETABLEの詳細は、『Oracle Database SQL言語リファレンス』を参照してください
6.3 別の表からのLOBの選択による行の挿入
LOBを含む行をSELECTによって挿入できます。
ノート:
BFILEには参照セマンティクスが適用されますが、永続LOB型のBLOB、CLOBおよびNCLOBにはコピー・セマンティクスが使用されます。BLOB、CLOBまたはNCLOBが、ある行から同じ表または別の表の行にコピーされる場合は、LOBロケータのみでなく実際のLOB値がコピーされます。
LOBに関してオブジェクト・リレーショナル技法を使用すると、関連する表の共通テンプレートとして型を定義できるというメリットがあります。たとえば、アーカイブ・データを格納する表と、これらのライブラリを使用する作業表の両方が共通の構造を持つことになります。
たとえば、Print_mediaおよびOnline_mediaが同じスキーマを持つとします。この文は、表Print_mediaに新しいLOBロケータを作成します。また、Online_mediaから、表Print_mediaに挿入された新しいLOBロケータによって示される場所にLOBデータをコピーします。
次のコード・フラグメントは、表Online_mediaが、表Print_mediaのad_textdocs_ntab列によって参照されるPrint_mediaと同じタイプであるという事実に基づいています。ライブラリ表に値を挿入し、これと同じデータをSELECTによってPrint_mediaに挿入します。
/* Store records in the archive table Online_media: */
INSERT INTO Online_media
VALUES (3060, NULL, NULL, NULL, NULL,
'some text about this CRT Monitor', NULL);
/* 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);
関連項目:
-
この例で使用されるPMスキーマおよび
Print_media表の詳細は、『Oracle Databaseサンプル・スキーマ』を参照してください
6.4 表へのLOB値の挿入
EMPTY_CLOB()またはEMPTY_BLOB()を使用してLOB値を挿入できます。
使用上のノート
次に、LOB挿入のガイドラインを示します。
NULL以外のLOB列の作成
データを永続LOBに書き込む前に、LOB列および非NULLにします。つまり、LOB列に空または移入済のLOB値を指すロケータを含める必要があります。BLOB列の値を初期化するには、デフォルトの述語としてEMPTY_BLOB()ファンクションを使用します。同様に、CLOB列またはNCLOB列の値を初期化するには、EMPTY_CLOB()ファンクションを使用します。
サイズが4000バイト未満の文字列またはRAW文字列を含むLOB列も初期化できます。次に例を示します。
INSERT INTO Print_media (product_id, ad_id, ad_sourcetext)
VALUES (1, 1, 'This is a One Line Advertisement');
この初期化は、CREATE TABLE操作中でも実行できることに注意してください。
関連項目:
これらのファンクションは、Oracle SQLでは特別ファンクションとして使用できますが、DBMS_LOBパッケージには含まれていません。
/* In the new row of table Print_media,
the columns ad_sourcetext and ad_fltextn are initialized using EMPTY_CLOB(),
the columns ad_composite and ad_photo are initialized using EMPTY_BLOB(),
the column formatted-doc in the nested table is initialized using
EMPTY_BLOB(),
the column logo in the column object is initialized using EMPTY_BLOB(): */
INSERT INTO Print_media
VALUES (3060,11001, EMPTY_BLOB(), EMPTY_CLOB(),EMPTY_CLOB(),EMPTY_CLOB(),
textdoc_tab(textdoc_typ ('HTML', EMPTY_BLOB())), EMPTY_BLOB(), NULL,
adheader_typ('any header name', <any date>, 'ad header text goes here',
EMPTY_BLOB()),
'Press release goes here');6.5 初期化したLOBロケータ・バインド変数を使用した行の挿入
LOBロケータ・バインド変数を初期化することにより、行を挿入できます。
ここに示す例は、複数のプログラム環境での例です。
内容は次のとおりです。
-
C (OCI): 『Oracle Call Interfaceプログラマーズ・ガイド』の「リレーショナル関数」
-
C++(OCCI): 『Oracle C++ Call Interfaceプログラマーズ・ガイド』
-
COBOL (Pro*COBOL): 『Pro*COBOLプログラマーズ・ガイド』のLOBの詳細、LOB文の使用上のノート、および「埋込みSQLおよびプリコンパイラ・ディレクティブ」の
INSERTに関する項。 -
C/C++ (Pro*C/C++): 『Pro*C/C++プログラマーズ・ガイド』の
INSERTに関する項 -
Java (JDBC): 『Oracle Database JDBC開発者ガイド』の「LOBの操作」の
BLOBまたはCLOB列の作成と移入に関する項
6.5.1 LOBロケータ・バインド変数を使用した行の挿入について
次の各点について考慮する必要があります。
前提条件
この方法で行を挿入するには、次の条件を満たしている必要があります。
-
ソース行を含む表が存在していること。
-
挿入先の表が存在していること。
LOB列を含む表の作成方法の詳細は、「LOB記憶域パラメータ」を参照してください。
使用上のノート
4000バイトを超えるバインドが関係する場合の、LOBを含む行のINSERTおよびUPDATE方法のガイドラインについては、「INSERT操作とUPDATE操作でのすべてのサイズのバインド」を参照してください。
構文
各プログラム環境でこの操作を使用する方法の詳細は、次のマニュアルを参照してください。
6.5.2 PL/SQL: 初期化したLOBロケータ・バインド変数を使用した行の挿入
PL/SQLでLOBロケータ・バインド変数を初期化することにより、行を挿入できます
/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/plsql/linsert.sql */
/* 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;
/6.5.3 C(OCI): 初期化したLOBロケータ・バインド変数を使用した行の挿入
C (OCI)でLOBロケータ・バインド変数を初期化することにより、行を挿入できます。
/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/oci/linsert.c */
/* 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));
}6.5.4 COBOL(Pro*COBOL): 初期化したLOBロケータ・バインド変数を使用した行の挿入
COBOL (Pro*COBOL)でLOBロケータ・バインド変数を初期化することにより、行を挿入できます。
* This file is installed in the following path when you install
* the database: $ORACLE_HOME/rdbms/demo/lobs/procob/linsert.pco
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.ノート:
この機能を簡単に説明するために、この例では、デプロイされたシステムで通常使用されるパスワード管理手法を実行していません。本番環境では、Oracle Databaseのパスワード管理ガイドラインに従い、サンプル・アカウントを無効にしてください。パスワード管理ガイドラインおよびその他のセキュリティ上の推奨事項については、『Oracle Databaseセキュリティ・ガイド』を参照してください。
6.5.5 C/C++(Pro*C/C++): 初期化したLOBロケータ・バインド変数を使用した行の挿入
C/C++ (Pro*C/C++)でLOBロケータ・バインド変数を初期化することにより、行を挿入できます。
/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/proc/linsert.pc */
#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;
}6.5.6 Java(JDBC): 初期化したLOBロケータ・バインド変数を使用した行の挿入
Java (JDBC)でLOBロケータ・バインド変数を初期化することにより、行を挿入できます。
/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/java/linsert.java */
// 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();
}
}
}6.6 EMPTY_CLOB()またはEMPTY_BLOB()を使用したLOBの更新
EMPTY_CLOB()またはEMPTY_BLOB()を使用してLOB値を更新できます。
ノート:
EMPTY_CLOB()またはEMPTY_BLOB()のかわりに実際の値でLOBを更新するとパフォーマンスが改善されます。
事前条件
データを永続LOBに書き込む前に、LOB列および非NULLにします。つまり、LOB列に空または移入済のLOB値を指すロケータを含める必要があります。BLOB列の値を初期化するには、デフォルトの述語としてEMPTY_BLOB()ファンクションを使用します。同様に、CLOB列またはNCLOB列の値を初期化するには、EMPTY_CLOB()ファンクションを使用します。
サイズが4000バイト未満の文字列またはRAW文字列を含むLOB列も初期化できます。次に例を示します。
UPDATE Print_media
SET ad_sourcetext = 'This is a One Line Story'
WHERE product_id = 2268;
この初期化は、CREATE TABLEの操作中も実行可能です(「1つ以上のLOB列を含む表の作成」を参照)。また、この場合はINSERTを使用しても実行できます。
次の例では、EMPTY_CLOBを使用して様々なデータ型にLOBを更新する場合の一連の手続きを示します。
UPDATE Print_media SET ad_sourcetext = EMPTY_CLOB()
WHERE product_id = 3060 AND ad_id = 11001;
UPDATE Print_media SET ad_fltextn = EMPTY_CLOB()
WHERE product_id = 3060 AND ad_id = 11001;
UPDATE Print_media SET ad_photo = EMPTY_BLOB()
WHERE product_id = 3060 AND ad_id = 11001;
6.7 別の表からのLOBの選択による行の更新
SQL UPDATE AS SELECT文を使用して、別の表からLOBを選択してLOB列を含む行を更新できます。
この方法を使用するには、参照を使用して更新する必要があります。たとえば、次のコードでは、online_mediaのデータが更新されます。
Rem 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;