ITコーディネータのシュウです。

お久しぶりです。この写真は2か月前くらいに、島根の実家に帰省する際に乗った、寝台特急「サンライズ出雲」の、ノビノビ座席に乗ったときに撮ったものです。寝台車というと、個室を思い浮かべますが、最近は頭の部分だけが仕切られていて、そんなにそれぞれのスペースは広くはないですが、カーペットの座敷で横になってしっかり寝れるようなタイプの格安の席があります。
ちょうど、高速バスがコロナ禍で運行していない時期で、帰省する手段を探したところ見つかり、寝台車に久しぶりに乗りましたが、横になって休める分、バスよりもゆっくり眠れるような気がします。

最近再びコロナの感染者が増えてきており、様々心配は尽きませんが、こういうときこそ絆を大切にし、励ましあいながら、頑張って乗り越えて行きたいものです。
コロナウィルスを始め、様々なことがあったこの1年もいよいよ終わろうとしています。皆様も健康に気を付けてよいお年をお迎えください。

＜本日の題材＞
SEQUENCE(シーケンス)の現在値の変更（SQL Server）

以前、SEQUENCEについてブログで取り上げましたが、SEQUENCEの現在の値を修正したいというケースが発生する場合があります。例えば、主キーの値をシーケンスを使って採番するようにしていたが、データのメンテナンスが必要になり、データをコピーして追加したところ、主キーの値がシーケンスの値と大きくかけ離れた大きな値になってしまったというようなケースです。私も実際に複数のテーブルでそういうケースにぶつかってしまい、一つずつ、シーケンスの設定を変更しなければいけないという状況が発生しました。このとき、テーブルの主キーの値の最大値をSQLで確認した後、SQL ServerManagement Studioで１つ１つのシーケンスの現在値を変更していきましたが、もっとスムーズに設定の変更ができる方法がないかを探したときに、対応した方法です。

例)
「testA」テーブルの主キー「id」(int型)をSEQUENCE「test_seqA」を使って採番するかたちにします。
100番までSEQUENCEを使って採番した後、データのメンテナンスが発生して、120番までデータを追加しましたが、そのときにはSEQUENCEを使って採番しなかったので、現在値が100のままになっているので、そのままでは次にSEQUENCEを使ってデータを登録すると、主キーの重複でエラーになってしまいます。ですので、SEQUENCEの現在の値を120に修正する必要があります。

まず、SEQUENCE「test_seqA」の現在の値を確認してみます。

SELECT current_value FROM sys.sequences WHERE name = 'test_seqA';

100という結果が出ました。

次に、テーブル「testA」の主キー「id」の最大値を確認してみます。

SELECT MAX(id) FROM testA;

120という値が確認されました。

SEQUENCEの現在の値を120に修正する方法の1つは、ALTER SEQUENCE文で修正を行う方法です。

ALTER SEQUENCE test_seqA
RESTART WITH 120
INCREMENT BY 1;

（ちなみに、SQL Server Management Studioでシーケンスのプロパティで、「シーケンスの再開」から変更することも可能です）

上記の作業を順に行えば修正はできますが、毎回、いちいち上記の処理を順番に行うのも面倒なので、今回は、上記のALTER SEQUENCE文を使用するのではなく、下記のようなSQL文を作成して、それを実行するかたちにします。

DECLARE
@id_MAX   INT
,@Seq_cur_val   sql_variant
,@count         INT = 0;

SELECT @id_MAX = MAX(id)
FROM testA;

SELECT @Seq_cur_val = CURRENT_VALUE FROM sys.sequences
WHERE name = ‘test_seqA’;

IF @id_MAX > CAST(@Seq_cur_val AS INT)
BEGIN
WHILE @count < @id_MAX
BEGIN
              SELECT @count = NEXT VALUE FOR test_seqA;
END;
END;

実行します。

実行後、SEQUENCE「test_seqA」の現在の値を確認してみます。

SELECT current_value FROM sys.sequences WHERE name = 'test_seqA';

 

現在値が120に変更されているのが確認できます。
この状態であれば、「tetA」テーブルにSEQUENCE「test_seqA」を使って主キーを採番することが可能です。次の採番データを登録して見ます。

INSERT INTO testA(id) VALUES(NEXT VALUE FOR test_seqA);

データは登録されました。データの主キーの値が大きいものから10件確認してみます。

SELECT TOP 10 * FROM testA
ORDER BY id DESC;

正しく採番されていることが確認できました。
ちなみに、dbSheetClientでは、上記のSQLのテーブル名、主キー名、SEQUENCE名などを、Excelのシートから値を渡すことができますので、上記のSQLを実行するタスクを1つ作成して、それを各テーブルで必要なタイミングで呼び出すようにすることで、複数のテーブルにおいてもSEQUENCEの現在値を修正することが可能です。これを使うことで、何とか問題なく処理をすることが可能になり、とても助かりました。

今日は以上まで

にほんブログ村

ITコーディネータのシュウです。

 

社長がアメリカのサンフランシスコにあるニューコムの支社に行ったついでに、シリコンバレーに寄った際に撮ってきたオラクル社の写真です。誰かが言ってましたが、丸いビルと模様はハードディスクを意味しているとか！　本当ですかね？　ただ、たしかにシリンダーみたいですよね。
グーグルやアップル、インテル、ヒューレットパッカード、シスコシステムズ、ヤフー、アドビ、イーベイなど、シリコンバレーにある著名な企業を上げればきりがないほどですが、シリコンバレーっていうのは、実際にシリコンバレーという都市が存在しているわけではなく、サンノゼ、マウンテンビュー、サニーベール、サンタクララといった複数の市からなる場所だそうです。

＜本日の題材＞
IDENTITY列

 前回、SEQUENCEについて取り上げましたが、今回はSQL Serverではよく使用されていたIDENTITYプロパティが、Oracle 12cから使用できるようになったという件を取り上げてみたいと思います。

SQL ServerのIDENTITY プロパティというのは、Accessの「オートナンバー型」のように、ある列の値を自動的に連番が設定されるようにしたい場合に使用します。

前回取り上げたシーケンスが、特定のテーブルとは関連づかない独立したオブジェクトであるのに対して、IDENTITYのほうは、特定のテーブルの列に関連づく機能というところが違います。

★例）SQL Serverの場合：

CREATE TABLE dbo.受注TBL(
  受注番号 INT IDENTITY(1,1)
,受注日   DATE
,顧客番号 VARCHAR(10)
,決済金額 DECIMAL(12)
, CONSTRAINT PK_受注TBL PRIMARY KEY (受注番号));

上記は、受注番号に IDENTITYプロパティを設定し、最初の値を「1」、増分値を「1」としています。

データを登録する場合は、以下のように、IDENTITYプロパティを設定した項目を除いて設定します。

INSERT INTO dbo.受注TBL(受注日,顧客番号,決済金額)
VALUES('2015-08-24','K0001051',34000);

INSERT INTO dbo.受注TBL(受注日,顧客番号,決済金額)
VALUES('2015-08-24','K0001078',42000);

結果を確認してみます。
SELECT * FROM dbo.受注TBL;

たしかに、受注番号は「1」から順に作成されています。

INSERT文にこのIDENTITYプロパティを設定した項目についても含めてしまうと、下記のようにエラーとなります。

INSERT INTO dbo.受注TBL(受注番号,受注日,顧客番号,決済金額)
VALUES(3, '2015-08-24','K0001078',42000);

このIDENTITYプロパティを設定した項目に対して、どうしても自動での登録ではなく、明示的に設定して登録したい場合には、「SET IDENTITY_INSERT テーブル名 ON」という設定をした後で行う必要があります。

SET IDENTITY_INSERT dbo.受注TBL ON;
INSERT INTO dbo.受注TBL(受注番号,受注日,顧客番号,決済金額)
VALUES(10, '2015-08-24','K0001083',17000);

結果を確認してみます。
SELECT * FROM dbo.受注TBL;

再度設定を元に戻して、登録を行います。

SET IDENTITY_INSERT dbo.受注TBL OFF;
INSERT INTO dbo.受注TBL(受注日,顧客番号,決済金額)
values('2015-08-24','K0001091',56000);

 結果を確認すると、

先ほどの受注番号の最大値「10」の後の連番で「11」の行が登録されているのが確認できます。

IDENTITY列というのは上記のようなかたちで使いますが、Oracleでは11gまではサポートされていませんでしたが、12c でこのIDENTITY列がサポートされるようになりました。

 ★例）Oracle 12c の場合

IDENTITYを設定したい列について、ユーザーが更新することが不可能な列として作成する場合、「GENERATED ALWAYS AS IDENTITY」をつけます。

CREATE TABLE 受注TBL(
  受注番号 INT GENERATED ALWAYS AS IDENTITY
, 受注日   DATE
, 顧客番号 VARCHAR(10)
, 決済金額 DECIMAL(12)
, CONSTRAINT PK_受注TBL PRIMARY KEY (受注番号));

SQL Serverのときと同様に、データを登録すると、

結果を確認すると、
SELECT * FROM 受注TBL;

 受注番号は、1からの連番で作成されているのが確認できます。

ここで、受注番号を更新してみます。
UPDATE 受注TBL SET
  受注番号 = 100
 WHERE 受注番号 = 1;

上記のように、更新できない旨のエラーが表示されます。

次に、上記の処理をロールバックします。
ROLLBACK;

]

再度登録します。

結果を確認します。
SELECT * FROM 受注TBL;

そうすると、受注番号は「3」から始まっていて、先ほどのロールバックの処理ではこのIDENTITYの値はロールバックされないことが確認できます。（シーケンスと同様）

次に、このIDENTITYの列の値を更新可能な列として作成する場合には、以下のように「GENERATED BY DEFAULT AS IDENTITY」をつけます。また、連番の最初の値を「100」とします。

CREATE TABLE 受注TBL(
  受注番号 INT GENERATED BY DEFAULT AS IDENTITY(START WITH 100)
, 受注日   DATE
, 顧客番号 VARCHAR(10)
, 決済金額 DECIMAL(12)
, CONSTRAINT PK_受注TBL PRIMARY KEY (受注番号));

再度、データを登録します。

データを確認します。

今度は、受注番号は、100からの連番で作成されているのが確認できます。
受注番号を更新してみます。

UPDATE 受注TBL SET
  受注番号 = 200
 WHERE 受注番号 = 100;

 今回は、更新できることが確認できました。
再度データを確認します。

 Oracleの場合、このIDENTITY列の実体は、SEQUENCEオブジェクトとDEFAULTの組合せとのことです。「USER_OBJECTS」の内容を確認してみます。

SELECT OBJECT_NAME, OBJECT_ID, OBJECT_TYPE
  FROM USER_OBJECTS;

SEQUENCEが作成されているのが確認できます。

 今日は以上まで

にほんブログ村

ITコーディネータのシュウです。

いよいよ夏本番という感じになってきました。
本当に暑い日が続きますね。
そういえば、高校野球の各都道府県大会も、いよいよ佳境に入り、各地で夏の甲子園の代表校が決まってきていますが、うちの子の通っている高校も、今回は上のほうまで勝ち上がっていっているので、とっても注目していました。
暑い中、一生懸命な姿に感動しますね。なかなか忙しくて球場まで応援に行けないですが、是非、どの学校も頑張ってほしいと思います。

＜本日の題材＞
SEQUENCE（SQL Server）

前回、ORACLEでのSEQUENCE（順序）について取り上げましたが、今回は、SQL Serverの環境で、SEQUENCE（シーケンス）について確認してみます。

SQL Server(2012以降)でSEQUENCEを作成する構文は、ORACLEとほぼ同じで以下のようになります。

CREATE SEQUENCE [schema_name . ] sequence_name
    [ AS [ built_in_integer_type | user-defined_integer_type ] ]
    [ START WITH value ]
    [ INCREMENT BY value ]
    [ { MINVALUE value } | { NO MINVALUE } ]
    [ { MAXVALUE value } | { NO MAXVALUE } ]
    [ CYCLE | { NO CYCLE } ]
    [ { CACHE value } | { NO CACHE } ]

前回のORACLEのときと同様、1から始まり、増分値が1、特にMAX値などは設定しない「order_sequence」という順序を作成したい場合には、以下のように作成します。

CREATE SEQUENCE dbo.order_sequence
START WITH 1
INCREMENT BY 1;

 次に、ORACLEの例と同様に、値を小さくしていくSEQUENCEを作成してみます。下記は、値を小さくしていき、最小値になったら元の最大値に戻す場合の例：

CREATE SEQUENCE dbo.minus_sequence
START WITH 100000
INCREMENT BY -1
MAXVALUE 100000
MINVALUE 1
CYCLE;

 

 SQL Server 2014 Management Studio で確認すると、シーケンスが作成されているのが確認できます。

 次に、実際の使い方ですが、ORACLEではNEXTVALというものを使っていましたが、SQL Serverでは NEXT VALUE FOR 関数を使用して、次の値を取得します。

例）受注情報を登録する「受注」テーブルに、order_sequence を利用して、次の受注番号を取得して登録します。

INSERT INTO dbo.受注(受注番号,受注日,顧客番号)
VALUES(NEXT VALUE FOR order_sequence, CONVERT(VARCHAR, getdate(), 111), 'K0001037');

先ほど登録した受注データを確認します。

SELECT 受注番号, 受注日, 顧客番号
 FROM 受注
WHERE 顧客番号 = 'K0001037';

もう1件登録してみます。

INSERT INTO dbo.受注(受注番号,受注日,顧客番号)
VALUES(NEXT VALUE FOR order_sequence, CONVERT(VARCHAR, getdate(), 111), 'K0001038');

 受注データを確認します。

SELECT 受注番号, 受注日, 顧客番号
  FROM 受注
WHERE 顧客番号 = 'K0001038';

 SQL Serverでは、現在のorder_sequenceの値は、SQL Server Management Studioで確認できます。
現在値を見たいシーケンスorder_sequenceにカーソルを置いて、右ボタンでのプロパティを表示すると、

 現在値が2になっていることが確認できます。

もうひとつの例として、Microsoft社のホームページに上がっていたものを参考に、テーブルの項目のデフォルト値にSEQUENCEのNEXT VALUE FOR関数を使うものを上げてみます。

まず、イベントのカウンター用のSEQUENCEを作成します。

CREATE SEQUENCE dbo.EventCounter
    AS int
    START WITH 1
    INCREMENT BY 1 ;

次に、上記のSEQUENCEをイベントIDという項目のデフォルト値に利用したテーブルを作成します。

CREATE TABLE dbo.EventLog(
  イベントID int DEFAULT (NEXT VALUE FOR EventCounter)
,イベント発生時刻 datetime NOT NULL DEFAULT (getdate())
,エラーCD varchar(20)
,イベント内容 nvarchar(256)
, CONSTRAINT PK_EventLog PRIMARY KEY (イベントID)
);

 このEventLog というテーブルにデータを登録します。

 INSERT dbo.EventLog(エラーCD, イベント内容)
   VALUES ('EC_QCTL_20', 'SQL実行時にエラーが発生しました。') ;

INSERT dbo.EventLog(エラーCD, イベント内容)
    VALUES ('EC_QSEL_2', 'クエリーの出力開始セルが正しく取得できませんでした。') ;

登録したデータを確認してみます。

SELECT * FROM dbo.EventLog
 ORDER BY イベントID;

項目：イベントIDには、EventCounterシーケンスで取得された、1から1つずつ増えていく番号が設定されていることが確認できました。

今日は以上まで

にほんブログ村

ITコーディネータのシュウです。

梅雨が明けて、めちゃめちゃ暑い日が続いていますね～。
外での活動は本当に大変だと思います。

さて、上記の写真については知人の方に頂いた、アメリカでの写真シリーズの最後で、「セドナ」と呼ばれる場所です。グランドキャニオンと同じアリゾナ州の中北部で、ボルテックスと呼ばれるパワースポットがあることでも有名であり、「スピリチュアル」な場所、"癒しの町"として人気があるそうです。写真はベルのかたちをした「ベルロック」という岩山です。他にも「カセドラルロック」、「コーヒーポットロック」、「スヌーピーロック」などの愛称で呼ばれる岩山があるそうです。どんな形をしているのかな？　興味がわきますね。それにパワースポットか、日々の仕事で疲れている私も癒されたい～。（行ってみたいな．．．）

＜本日の題材＞
SEQUENCE（ORACLEの順序）

今回は、SEQUENCE（シーケンス）という、複数のユーザーが一意の整数を生成するときに使用できるデータベース・オブジェクト（ORACLEでは順序という）について取り上げてみたいと思います。システムを開発する際に、受注用テーブルの受注番号など、一意に順に採番していく番号を主キーとするようなテーブルを設計することはよくあると思います。そのときに、キーの値が重複せずに簡単に採番していくことを可能にするものとして、使用されることが多いのではないかと思います。
今回は、ORACLEの環境で確認していきます。

SEQUENCEを作成する構文としては、

CREATE SEQUENCE sequence_name
[START WITH value]
[INCREMENT BY value]
[MAXVALUE value]
[MINVALUE value]
[NOCYCLE | CYCLE]
[CACHE value | NOCACHE ]

仮に、1から始まり、増分値が1、特にMAX値などは設定しない「order_sequence」という順序を作成したい場合には、以下のようにして作成します。

CREATE SEQUENCE order_sequence
START WITH 1
INCREMENT BY 1;

 ※順序を作成するためには、CREATE SEQUENCE権限が必要です。また、値を小さくしていくことも可能で、そのときは、INCREMENT BY の値をマイナスにします。
値を大きく、または小さくしていく場合に、最大値や最小値を指定して、それを超えるとまた最小値や最大値に戻すようにする場合には、MAXVALUE、MINVALUEの値を設定し、かつ、CYCLE という指定を行うことになります。

下記は、値を小さくしていき、最小値になったら元の最大値に戻す場合の例：

 次に、実際の使い方ですが、INSERT文のVALUES句、UPDATE文のSET句などの中で、NEXTVALというものを使って次の値を取得します。

例）受注情報を登録する「受注」テーブルに、順序を利用して、次の受注番号を取得して登録します。

INSERT INTO 受注(受注番号, 受注日, 顧客番号) VALUES(order_sequence.NEXTVAL, TO_DATE(sysdate), ‘K0001037’);

 受注番号はSEQUENCEを使って、データが登録されました。この登録した受注データを確認します。

SELECT 受注番号, 受注日, 顧客番号
  FROM 受注
WHERE 顧客番号 = 'K0001037';

現在のorder_sequenceの値をCURRVALを使用して確認します。
SELECT order_sequence.CURRVAL FROM dual;

たしかに、先ほどNEXTVALを使用して登録した受注番号と、CURRVALを使用して確認したシーケンスの番号が一致していることが確認できました。

次に、注意点ですが、sqlplusをもう一つ立ち上げて、先ほどとは別のセッションでログインしてみます。この後、現在のシーケンスの値を確認しようと思い、CURRVALを使用して確認してみます。

 先ほど別のセッションでは、CURRVALで「7」という値が返ってきたので、こちらでも同じ用に結果が帰って来ると思いきや、「ORA-08002：順序ORDER_SEQUENCE.CURRVALはこのセッションではまだ定義されていません」というエラーが返ってきました。

ここで、エラーメッセージをよく見ると、「このセッションでは」という記述がありますように、CURRVALというのはそのセッションの中で最後に生成した順序の値を表示するものであるため、まだこのセッションの中で一度もその順序を生成していない場合はエラーになってしまうということです。
この状態で、NEXTVALを使って順序を生成してみると、

SELECT order_sequence.NEXTVAL FROM dual;

CURRVALではエラーになった新しいセッションでも、NEXTVALでの順序の生成は可能であることがわかります。

 ここで、再度、先ほどエラーになったCURRVALで現在の順序の値を取得してみると、

 今度は確認できました。

それでは、ということで、もう一つ立ち上げていた元のsqlplusのほうで、現在の順序の値を確認してみると、

SELECT order_sequence.CURRVAL FROM dual;

先ほど作成した、もう片方での最終の値「8」ではなく、そのセッション内で最後に生成した順序の値「7」が返ってくることが注意点です。

この状態で、受注データを作成してみると、

INSERT INTO 受注(受注番号, 受注日, 顧客番号) VALUES(order_sequence.NEXTVAL, TO_DATE(sysdate), ‘K0001038’);

 登録した受注データを確認します。

SELECT 受注番号, 受注日, 顧客番号
  FROM 受注
WHERE 顧客番号 = 'K0001038';

受注番号は「9」となり、別のセッションで最後にNEXTVALで取得した「8」の次の番号が取られていることが確認できます。ですので、NEXTVALで取得する番号は、複数のセッションからのアクセスで重複せずに次の番号が生成されることがわかりました。

ちなみに、SQL ServerではSQL Server 2012から、SEQUENCEがサポートされています。次回は、SQL Serverで確認したいと思います。

今日は以上まで

にほんブログ村