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で確認したいと思います。

今日は以上まで

にほんブログ村