SEQUENCE(ORACLEの順序)


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

0530-026

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

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

<本日の題材>
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;

cre_sequence

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

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

cre_sequence2

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

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

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

sequence_ins1

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

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

受注_確認1

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

seq_currval

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

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

seq_currval_2

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

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

SELECT order_sequence.NEXTVAL FROM dual;

seq_nextval

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

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

seq_currval_3

 今度は確認できました。

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

SELECT order_sequence.CURRVAL FROM dual;

seq_currval

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

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

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

受注追加

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

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

受注_確認2

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

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

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

次のHTML タグと属性が使えます: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>