CREATE （テンポラリ） TABLE
テンポラリ テーブルは、CREATE TABLE ステートメントを使用して作成することもできます。しかし、テンポラリ テーブルのための CREATE TABLE 構文は永続テーブルのためのものより限定的です。この理由により、またその他の特性があることから、テンポラリ テーブルについては単独のセクションで説明します。
構文
CREATE TABLE <# | ##>テーブル名 (テーブル要素[, テーブル要素]...)
テーブル要素 ::= 列定義 | テーブル制約定義
列定義 ::=
列名 データ型 [
DEFAULT デフォルト値の式] [
列制約定義 [
列制約定義]...
データ型 ::=
データ型名 [(
桁数[,
小数位])]
桁数 ::= 整数
小数位 ::= 整数
デフォルト値の式 ::= デフォルト値の式 + デフォルト値の式
| デフォルト値の式 - デフォルト値の式
| デフォルト値の式 * デフォルト値の式
| デフォルト値の式 / デフォルト値の式
| デフォルト値の式 & デフォルト値の式
| デフォルト値の式 | デフォルト値の式
| デフォルト値の式 ^ デフォルト値の式
| ( デフォルト値の式)
| -デフォルト値の式
| +デフォルト値の式
| ~デフォルト値の式
| ?
| リテラル
| スカラー関数
| {fn スカラー関数｝
| NULL
リテラル ::= '文字列' | N'文字列'
| 数字
| {d '日付リテラル'}
| {t '時刻リテラル'}
| {ts 'タイムスタンプ リテラル'}
列制約定義 ::= [CONSTRAINT 制約名] 列制約
制約名 ::= ユーザー定義名
列制約 ::= NOT NULL
| NOT MODIFIABLE
| REFERENCES テーブル名 [(列名)] [参照アクション]
テーブル制約定義 ::= [CONSTRAINT 制約名] テーブル制約
テーブル制約 ::=
UNIQUE (
列名[,
列名]...)
REFERENCES テーブル名 [(列名[, 列名]...)] [参照アクション]
参照アクション ::= 参照更新アクション [参照削除アクション]
| 参照削除アクション [参照更新アクション]
照合順序名 ::= '文字列' | ユーザー定義名
備考
テンポラリ テーブルは中間結果や作業領域として使用されます。永続テーブルとは異なり、テンポラリ テーブルのデータは SQL セッション中のある時点または SQL セッションの最後に破棄されます。データはデータベースに保存されません。
テンポラリ テーブルは、中間テーブルに対して処理を継続することにより、中間結果を絞り込むのに便利です。複雑なデータ操作は、単純な手順に分割することでより簡単になります。それぞれの手順では、前の手順の結果のテーブルに対して操作を行います。テンポラリ テーブルはベース テーブルです。つまり、データはそれ自体に含まれています。これをビューと対比してみると、ビューは他テーブルのデータの間接的な表現です。
PSQL は以下の 2 つのタイプのテンポラリ テーブルをサポートします。
•ローカル
•グローバル
どちらのタイプもストアド プロシージャ内で使用できます。
テンポラリ テーブルの作成される場所と使用される場所を対比してテンポラリ テーブルの特性をまとめ、次の表に示します。特性は、テーブルが作成または使用される場所がストアド プロシージャの内部か外部かによって異なります。表の終わりにある脚注に注意事項が示されています。
メタデータのバージョン 1 とバージョン 2 では、テンポラリ テーブル名の許容サイズを除き、テーブルの特性は同じです。
表 22 テンポラリ テーブルの特性
テーブルの特性 | ローカル テンポラリ テーブル | グローバル テンポラリ テーブル |
SP 外部1 | SP 内部 | SP 外部 | SP 内部 |
テーブル名の最初の文字は # である必要がある（下の 以前のリリースとの互換性を参照してください。） | はい | はい | いいえ | いいえ |
テーブル名の最初の文字は ## である必要がある（下の 以前のリリースとの互換性を参照してください。） | いいえ | いいえ | はい | はい |
テーブルのコンテキストはテーブルを作成するデータベースと同じ | はい | はい | はい | はい |
2 つ以上のセッションが同一名称のテーブルを作成可能2 | はい | はい | いいえ | いいえ |
メタデータ バージョン 1 の場合、テーブル名の最大長（長さには "#"、"##"、アンダースコア、および ID も含む）については、『 Advanced Operations Guide』の 識別子の種類別の制限を参照してください | はい3 | はい4 | はい3 | はい4 |
メタデータ バージョン 2 の場合、テーブル名の最大長（長さには "#"、"##"、アンダースコア、および ID も含む）については、『 Advanced Operations Guide』の 識別子の種類別の制限を参照してください | はい3 | はい4 | はい3 | はい4 |
別のデータベース内のテーブルには、そのテーブル名で修飾することによりアクセス可能 | いいえ | いいえ | はい | はい |
SELECT、INSERT、UPDATE、および DELETE ステートメントがテーブルで許可されている | はい | はい | はい | はい |
ALTER TABLE および DROP TABLE ステートメントがテーブルで許可されている | はい | はい | はい | はい |
テーブルにビューを作成できる | いいえ | いいえ | いいえ | いいえ |
テーブルにユーザー定義関数を作成できる | いいえ | いいえ | いいえ | いいえ |
テーブルにトリガーを作成できる | いいえ | いいえ | いいえ | いいえ |
テーブルで権限の許可または取り消しができる | いいえ | いいえ | いいえ | いいえ |
FOREIGN KEY 制約が CREATE TABLE ステートメントで使用できる5 | いいえ | いいえ | いいえ | いいえ |
SELECT INTO ステートメントでテーブルにデータを入れられる | はい | はい | はい | はい |
SELECT INTO ステートメントでテーブルを作成できる6 | はい | はい | はい | はい |
ある SQL セッションで作成されたテーブルが、ほかの SQL セッションからアクセスできる | いいえ | いいえ | はい | はい |
プロシージャで作成されたテーブルは、そのプロシージャの外でアクセス可能 | 適用外 | いいえ | 適用外 | はい |
最上位のプロシージャで作成されたテーブルは、ネストされたプロシージャでアクセス可能 | 適用外 | いいえ | 適用外 | いいえ |
再帰的ストアド プロシージャ内の CREATE TABLE ステートメントは再帰呼び出しでテーブル名エラーを返す | 適用外 | はい7 | 適用外 | はい9 |
テーブルは明示的削除時に削除される | はい | はい | はい | はい |
テーブルはそのテーブルが作成されたセッションの最後に削除される | はい | はい8 | はい | はい |
テーブルはそのテーブルが作成されたプロシージャの最後に削除される | 適用外 | はい | 適用外 | いいえ |
テーブルは別のセッションのトランザクションの最後に削除される | 適用外 | 適用外 | はい | はい |
1 SP はストアド プロシージャを表します。 2 データベース エンジンは、ストアド プロシージャの名前とセッション固有の ID をユーザー定義名に自動的に付加することによって、テーブル名が確実に一意になるようにします。この機能はユーザーに透過的です。 3 テーブル名の長さの合計には、"#" または "##" に加え、アンダースコア＋セッション ID も含められます。セッション ID はオペレーティング システムによって 8、9、または 10 バイトになります。『 Advanced Operations Guide』の 識別子の種類別の制限を参照してください。 4 テーブル名の長さの合計には、"#" または "##" に加え、アンダースコア＋ストアド プロシージャの名前＋アンダースコア＋セッション ID も含められます。セッション ID はオペレーティング システムによって 8、9、または 10 バイトになります。『 Advanced Operations Guide』の 識別子の種類別の制限を参照してください。 5 制約は警告を返しますがテーブルは作成されます。 6 テーブルは、1 つの SELECT INTO ステートメントによって作成されデータが入れられます。 7 テーブル名は、ストアド プロシージャが最初に実行されるときから既に存在しています。 8 プロシージャの実行が終了する前にセッションの終了が発生した場合。 |
以前のリリースとの互換性
PSQL v9 Service Pack 2 より前の PSQL のリリースは、永続テーブルに "#" または "##" で始まる名前を付けることを許可していました。PSQL v9 Service Pack 2 以降のリリースでは、永続テーブルに "#" または "##" で始まる名前を付けることはできません。"#" または "##" で始まるテーブルはテンポラリ テーブルであり、TEMPDB データベース内に作成されます。
PSQL v12 より前のバージョンの PSQL で作成された "#" または "##" で始まる永続テーブルにアクセスしようとすると、「テーブルが見つからない」というエラーが返されます。
『
PSQL User's Guide』の
ステートメント区切り文字も参照してください。
TEMPDB データベース
PSQL のインストールは TEMPDB という名前のシステム データベースを作成します。TEMPDB はすべてのテンポラリ テーブルを保持します。TEMPDB データベースは削除しないでください。削除してしまうと、テンポラリ テーブルが作成できなくなります。
TEMPDB は PSQL 製品のインストール ディレクトリに作成されます。『
Getting Started With PSQL』の
PSQL ファイルはどこにインストールされますか？を参照してください。
インストール後に、TEMPDB の辞書ファイルとデータ ファイルの場所を変更することもできます。『
PSQL User's Guide』の
データベースのプロパティを参照してください。
注意： TEMPDB は、データベース エンジンが排他的に使用するシステム データベースです。TEMPDB を永続テーブル、ビュー、ストアド プロシージャなどの格納場所として使用しないでください。
ローカル テンポラリ テーブルのテーブル名
データベース エンジンは、複数のセッションで作成されたテンポラリ テーブルを区別するため、ローカル テンポラリ テーブルの名前に自動的に情報を付け加えます。付加情報の長さは、オペレーティング システムによって異なります。
ローカル テンポラリ テーブルの名前は少なくとも 10 バイトになります。ただしこれは、ローカル テンポラリ テーブルを作成するストアド プロシージャの数が 1,296 を超えなければという条件付きです。10 バイトには "#" 文字も含まれます。1,296 の制限は同一セッション内のストアド プロシージャに適用されます。
名前の最大長は 20 バイトで、"#" 文字、テーブル名、および追加情報を含みます。
トランザクション
グローバル テンポラリ テーブルは、明示的に削除されるか、またはテーブルが作成されたセッションの終了時に自動的に削除されます。トランザクション内で、テーブルを作成したのではないセッションがそのテーブルを使用している場合、トランザクション完了時に削除されます。
SELECT INTO
単一の SELECT INTO ステートメントを使用して、テンポラリ テーブルを作成しデータを入れることができます。たとえば、SELECT * INTO #mytmptbl FROM Billing は、#mytmptbl という名前のローカル テンポラリ テーブルを作成します（#mytmptbl は存在していないものとします）。テンポラリ テーブルには DEMODATA サンプル データベースの Billing テーブルと同じデータが含まれます。
SELECT INTO ステートメントが同じテンポラリ テーブル名を使用して 2 度目に実行されると、テンポラリ テーブルが既に存在するのでエラーが返されます。
SELECT INTO ステートメントは 2 つ以上のテーブルからテンポラリ テーブルを作成することができます。ただし、テンポラリ テーブルを作成する元の各テーブルの列名は重複していない必要があります。重複しているとエラーが返されます。
このエラーは、列名をテーブル名で修飾し、各列にエイリアスを指定すれば回避することができます。たとえば、テーブル t1 と t2 には両方とも col1 と col2 の 2 つの列があるとします。次のステートメントはエラーを返します。
SELECT t1.co1, t1.col2, t2.col1, t2.col2 INTO #mytmptbl FROM t1, t2
代わりに、次のようなステートメントを使用します。
SELECT t1.co1 c1, t1.col2 c2, t2.col1 c3, t2.col2 c4 INTO #mytmptbl FROM t1, t2
制限
•ストアド プロシージャ内部で作成されたローカル テンポラリ テーブルは、そのストアド プロシージャのスコープ内にあります。ストアド プロシージャの実行後、ローカル テンポラリ テーブルは破棄されます。
•UNION および UNION ALL キーワードは、SELECT INTO ステートメントと共に使用することはできません。
•SELECT INTO ステートメントの結果を取得できるのは、1 つのテンポラリ テーブルだけです。単独の SELECT INTO ステートメントによって、データを選択（SELECT）し、それを複数のテンポラリ テーブルに入れることはできません。
ストアド プロシージャのキャッシング
ローカルまたはグローバルのテンポラリ テーブルを参照するストアド プロシージャは、キャッシュ設定に関わらず
キャッシュされません。
SET CACHED_PROCEDURES および
SET PROCEDURES_CACHE を参照してください。
例
次の例では、#b_temp という名前のローカル テンポラリ テーブルを作成し、DEMODATA サンプル データベースの Billing テーブルのデータを入れます。
SELECT * INTO '#b_temp' FROM Billing
============
次の例では、ID、Dept_Name、Building_Name、Room_Number の列から成り、列 ID に基づく主キーを持つ ##tenurefac という名前のグローバル テンポラリ テーブルを作成します。
CREATE TABLE ##tenurefac
(ID UBIGINT,
Dept_Name CHAR(20) CASE,
Building_Name CHAR(25) CASE,
Room_Number UINTEGER,
PRIMARY KEY(ID))
============
次の例では、テンポラリ テーブル ##tenurefac を変更し、列 Research_Grant_Amt を追加します。
ALTER TABLE ##tenurefac ADD Research_Grant_Amt DOUBLE
============
次の例では、テンポラリ テーブル ##tenurefac を削除します。
DROP TABLE ##tenurefac
============
次の例では、ストアド プロシージャ内で 2 つのテンポラリ テーブルを作成し、そのテーブルにデータを入れ、変数に値を割り当てます。その値はテンポラリ テーブルから選択されます。
メモ： SELECT INTO は、変数に値を割り当てるために使用するのであれば、ストアド プロシージャ内で使用できます。
CREATE PROCEDURE "p11" ()
AS BEGIN
DECLARE :val1_int INTEGER;
DECLARE :val2_char VARCHAR(20);
CREATE TABLE #t11 (col1 INT, col2 VARCHAR(20));
CREATE TABLE #t11 (col1 INT, col2 VARCHAR(20));
INSERT INTO #t11 VALUES (1,'t1 col2 text');
INSERT INTO #t12 VALUES (2,'t2 col2 text');
SELECT col1 INTO :val1_int FROM #t11 WHERE col1 = 1;
SELECT col2 INTO :val2_char FROM #t12 WHERE col1 = 2;
PRINT :val1_int;
PRINT :val2_char;
COMMIT;
END;
CALL P11 ()
============
次の例では、グローバル テンポラリ テーブル ##enroll_student_global_temp_tbl を作成し、次にストアド プロシージャ Enrollstudent を作成します。呼び出されると、プロシージャは指定された Student ID、Class ID、および GPA（Grade Point Average）を基に、##enroll_student_global_temp_tbl にレコードを挿入します。SELECT でテンポラリ テーブル内のすべてのレコードを選択し、その結果を表示します。グローバル テンポラリ テーブルの名前の長さは、メタデータ バージョン 2 でのみ許容されます。
CREATE TABLE ##enroll_student_global_temp_tbl (student_id INTEGER, class_id INTEGER, GPA REAL);
CREATE PROCEDURE Enrollstudent(in :Stud_id integer, in :Class_Id integer, IN :GPA REAL);
BEGIN
INSERT INTO ##enroll_student_global_temp_tbl VALUES(:Stud_id, :Class_id, :GPA);
END;
CALL Enrollstudent(1023456781, 146, 3.2)
SELECT * FROM ##enroll_student_global_temp_tbl
============
次の例では、ストアド プロシージャ内で 2 つのテンポラリ テーブルを作成し、そのテーブルにデータを入れ、変数に値を割り当てます。その値はテンポラリ テーブルから選択されます。
CREATE PROCEDURE "p11" ()
AS BEGIN
DECLARE :val1_int INTEGER;
DECLARE :val2_char VARCHAR(20);
CREATE TABLE #t11 (col1 INT, col2 VARCHAR(20));
CREATE TABLE #t11 (col1 INT, col2 VARCHAR(20));
INSERT INTO #t11 VALUES (1,'t1 col2 text');
INSERT INTO #t12 VALUES (2,'t2 col2 text');
SELECT col1 INTO :val1_int FROM #t11 WHERE col1 = 1;
SELECT col2 INTO :val2_char FROM #t12 WHERE col1 = 2;
PRINT :val1_int;
PRINT :val2_char;
COMMIT;
END;
CALL P11 ()
関連項目