助け合いフォーラム
Oracle Master Silver SQL 2019(1Z0-071)
問題ID : 26580
問題を開く
DEPARTMENTS表の構造を確認して下さい。
次のSQL文の実行結果として、正しいものはどれですか。
CREATE TABLE departments5
AS
SELECT * FROM departments
WHERE 1 = 2;
CREATE SEQUENCE seq_dept;
INSERT INTO departments5 VALUES (seq_dept.currval, 'Sales', 1001);
UPDATE departments5 SET department_id = seq_dept.nextval
WHERE manager_id = 1001;
次のSQL文の実行結果として、正しいものはどれですか。
CREATE TABLE departments5
AS
SELECT * FROM departments
WHERE 1 = 2;
CREATE SEQUENCE seq_dept;
INSERT INTO departments5 VALUES (seq_dept.currval, 'Sales', 1001);
UPDATE departments5 SET department_id = seq_dept.nextval
WHERE manager_id = 1001;
正解
NEXTVAL擬似列を参照する前にCURRVAL擬似列を参照しているので、エラーとなる
解説
現在のセッションで一度も順序値が生成されていないときにCURRVAL擬似列を参照すると、エラーとなります。
設問のSQL文では、順序を生成した後、NEXTVAL擬似列を参照せずにCURRVAL擬似列を参照しているためエラーとなります。
以上より、
・NEXTVAL擬似列を参照する前にCURRVAL擬似列を参照しているので、エラーとなる
が正解となります。
設問のSQL文の実行結果は次のようになります。
その他の選択肢については次のとおりです。
・順序の増分値が指定されていないため、CREATE SEQUENCE文で、エラーとなる
順序値の増分値が指定されていない場合は、デフォルトの増分値が適用されます。
・正常に実行され、MANAGER_IDが1001の行のDEPARTMENT_IDの値が2となる
・正常に実行され、MANAGER_IDが1001の行のDEPARTMENT_IDの値が1となる
INSERT文でエラーとなるため、DEPARTMENTS5表へのデータの追加/更新は行われません。
設問のSQL文では、順序を生成した後、NEXTVAL擬似列を参照せずにCURRVAL擬似列を参照しているためエラーとなります。
以上より、
・NEXTVAL擬似列を参照する前にCURRVAL擬似列を参照しているので、エラーとなる
が正解となります。
設問のSQL文の実行結果は次のようになります。
SQLを表示
CREATE TABLE departments5
AS
SELECT * FROM departments
WHERE 1 = 2;
CREATE SEQUENCE seq_dept;
INSERT INTO departments5 VALUES (seq_dept.CURRVAL, 'Sales', 1001);
AS
SELECT * FROM departments
WHERE 1 = 2;
CREATE SEQUENCE seq_dept;
INSERT INTO departments5 VALUES (seq_dept.CURRVAL, 'Sales', 1001);
その他の選択肢については次のとおりです。
・順序の増分値が指定されていないため、CREATE SEQUENCE文で、エラーとなる
順序値の増分値が指定されていない場合は、デフォルトの増分値が適用されます。
・正常に実行され、MANAGER_IDが1001の行のDEPARTMENT_IDの値が2となる
・正常に実行され、MANAGER_IDが1001の行のDEPARTMENT_IDの値が1となる
INSERT文でエラーとなるため、DEPARTMENTS5表へのデータの追加/更新は行われません。
参考
【順序】
順序は、指定した規則に従って一意の番号を自動的に生成するスキーマ・オブジェクトです。順序は主に重複したデータを受け付けない、PRIMARY KEY制約が定義された列の値を生成する際に利用されます。
順序は複数のユーザーで共有可能なため、複数のユーザーが同じ順序で順序値を生成した場合、常に全体を通して一意な順序値(※)が生成されます。また、順序は表と関連付けられるものではないので、複数の表で1つの順序を使用することもできます。
※ただし、順序の作成時にCYCLEオプション(順序値が最大値または最小値に達した場合、初期値に戻り繰り返し順序を生成する)を指定した場合は、重複した順序値が生成されることもあります。
順序は指定された規則に従って一意な順序値を生成しますが、ロールバックが発生した時などには欠番が生じることもあり、連番が保証されているわけではありません。
以下で順序値が欠番になるケースを解説します。
[ロールバックが発生した時]
順序で値を生成したINSERT文がロールバックされると、表の状態はロールバックされますが、生成した順序値はロールバックされないので、欠番が生じる場合があります。
[システムがクラッシュした時]
順序をキャッシュする設定で作成すると、メモリ上に順序値がキャッシュされ、データディクショナリ（オブジェクトの情報を格納する表）に次に生成する順序値が書き込まれます(順序値を20個生成する設定の場合、メモリ上に1から20の順序値が生成され、データディクショナリに21が書き込まれる)。
順序値がキャッシュされている状態の時にシステムがクラッシュすると、メモリ上にキャッシュされている順序値が失われてしまいます。
Oracleサーバーには、データディクショナリに書き込まれた次に生成する順序値（この場合は21）しか残されていませんので、システムを再起動した時には、次に生成する順序値から順序値が生成され、クラッシュ前にメモリ上にキャッシュされていた順序値は欠番となります。
[複数の表で同じ順序を使用]
複数の表で同じ順序を使用している場合も、それら複数の表全体で一意な順序値が生成されるので、1つの表だけを見ると連番とならない場合があります。
【順序の作成】
順序の作成はCREATE SEQUENCE権限を持つユーザーによって行われます。
CREATE SEQUENCE 順序名
[INCREMENT BY n]
[START WITH n]
[MAXVALUE n | NOMAXVALUE]
[MINVALUE n | NOMINVALUE]
[CYCLE | NOCYCLE]
[CACHE n | NOCACHE];
CREATE SEQUENCE文のオプションは次の通りです。
順序から新しい順序値を取得したり、現在の順序値を確認するためには、NEXTVAL疑似列、CURRVAL疑似列を参照します。
それぞれの参照方法は次の通りです。
順序名.NEXTVAL
順序名.CURRVAL
[NEXTVAL疑似列]
NEXTVAL疑似列を参照すると、新しい順序値が生成されて返されます。
[CURRVAL疑似列]
CURRVAL疑似列を参照すると、最後に生成された順序値が返されます。
NEXTVAL疑似列、CURRVAL疑似列は以下の箇所で参照することができます。
・主問合せのSELECT句
・INSERT文の副問合せのSELECT句
・INSERT文のVALUES句
・UPDATE文のSET句
・CREATE TABLE文またはALTER TABLE文の列のDEFAULT値
※Oracle 12cより、CREATE TABLE文やALTER TABLE文のデフォルト値にNEXTVAL疑似列とCURRVAL疑似列を指定できるようになりました。
また、NEXTVAL疑似列、CURRVAL疑似列は以下では使用できません。
・SELECT文、UPDATE文、DELETE文内の副問合せ
・ビューのSELECT句
・DISTINCTキーワードが指定されたSELECT文
・GROUP BY句、ORDER BY句、HAVING句を持つSELECT文
・集合演算子UNION、INTERSECT、MINUSによって別のSELECT文と結合されているSELECT文
・SELECT文のWHERE句
・CHECK制約の条件
なお、現在のセッションで一度も順序値が生成されていない(一度もNEXTVAL疑似列を参照していない)ときにCURRVAL疑似列を参照すると、エラーとなります
順序は、指定した規則に従って一意の番号を自動的に生成するスキーマ・オブジェクトです。順序は主に重複したデータを受け付けない、PRIMARY KEY制約が定義された列の値を生成する際に利用されます。
順序は複数のユーザーで共有可能なため、複数のユーザーが同じ順序で順序値を生成した場合、常に全体を通して一意な順序値(※)が生成されます。また、順序は表と関連付けられるものではないので、複数の表で1つの順序を使用することもできます。
※ただし、順序の作成時にCYCLEオプション(順序値が最大値または最小値に達した場合、初期値に戻り繰り返し順序を生成する)を指定した場合は、重複した順序値が生成されることもあります。
順序は指定された規則に従って一意な順序値を生成しますが、ロールバックが発生した時などには欠番が生じることもあり、連番が保証されているわけではありません。
以下で順序値が欠番になるケースを解説します。
[ロールバックが発生した時]
順序で値を生成したINSERT文がロールバックされると、表の状態はロールバックされますが、生成した順序値はロールバックされないので、欠番が生じる場合があります。
SQLを表示
CREATE TABLE departments6
AS
SELECT * FROM departments
WHERE 1 = 0;
CREATE sequence seq_dept;
INSERT INTO departments6
VALUES (seq_dept.NEXTVAL, 'Sales', 1001);
COMMIT;
INSERT INTO departments6
VALUES (seq_dept.NEXTVAL, 'Dev', 1002);
SELECT * FROM departments6;
ROLLBACK;
INSERT INTO departments6
VALUES (seq_dept.NEXTVAL, 'Dev', 1002);
SELECT * FROM departments6;
AS
SELECT * FROM departments
WHERE 1 = 0;
CREATE sequence seq_dept;
INSERT INTO departments6
VALUES (seq_dept.NEXTVAL, 'Sales', 1001);
COMMIT;
INSERT INTO departments6
VALUES (seq_dept.NEXTVAL, 'Dev', 1002);
SELECT * FROM departments6;
ROLLBACK;
INSERT INTO departments6
VALUES (seq_dept.NEXTVAL, 'Dev', 1002);
SELECT * FROM departments6;
[システムがクラッシュした時]
順序をキャッシュする設定で作成すると、メモリ上に順序値がキャッシュされ、データディクショナリ（オブジェクトの情報を格納する表）に次に生成する順序値が書き込まれます(順序値を20個生成する設定の場合、メモリ上に1から20の順序値が生成され、データディクショナリに21が書き込まれる)。
順序値がキャッシュされている状態の時にシステムがクラッシュすると、メモリ上にキャッシュされている順序値が失われてしまいます。
Oracleサーバーには、データディクショナリに書き込まれた次に生成する順序値（この場合は21）しか残されていませんので、システムを再起動した時には、次に生成する順序値から順序値が生成され、クラッシュ前にメモリ上にキャッシュされていた順序値は欠番となります。
[複数の表で同じ順序を使用]
複数の表で同じ順序を使用している場合も、それら複数の表全体で一意な順序値が生成されるので、1つの表だけを見ると連番とならない場合があります。
【順序の作成】
順序の作成はCREATE SEQUENCE権限を持つユーザーによって行われます。
CREATE SEQUENCE 順序名
[INCREMENT BY n]
[START WITH n]
[MAXVALUE n | NOMAXVALUE]
[MINVALUE n | NOMINVALUE]
[CYCLE | NOCYCLE]
[CACHE n | NOCACHE];
SQLを表示
CREATE SEQUENCE s_id
INCREMENT BY 1
START WITH 1
MAXVALUE 100
MINVALUE 1
CYCLE
CACHE 20;
INCREMENT BY 1
START WITH 1
MAXVALUE 100
MINVALUE 1
CYCLE
CACHE 20;
CREATE SEQUENCE文のオプションは次の通りです。
順序から新しい順序値を取得したり、現在の順序値を確認するためには、NEXTVAL疑似列、CURRVAL疑似列を参照します。
それぞれの参照方法は次の通りです。
順序名.NEXTVAL
順序名.CURRVAL
[NEXTVAL疑似列]
NEXTVAL疑似列を参照すると、新しい順序値が生成されて返されます。
SQLを表示
CREATE SEQUENCE seq_id;
SELECT seq_id.NEXTVAL FROM dual;
SELECT seq_id.NEXTVAL FROM dual;
SELECT seq_id.NEXTVAL FROM dual;
SELECT seq_id.NEXTVAL FROM dual;
[CURRVAL疑似列]
CURRVAL疑似列を参照すると、最後に生成された順序値が返されます。
SQLを表示
CREATE SEQUENCE seq_id;
SELECT seq_id.NEXTVAL FROM dual;
SELECT seq_id.NEXTVAL FROM dual;
SELECT seq_id.CURRVAL FROM dual;
SELECT seq_id.NEXTVAL FROM dual;
SELECT seq_id.NEXTVAL FROM dual;
SELECT seq_id.CURRVAL FROM dual;
NEXTVAL疑似列、CURRVAL疑似列は以下の箇所で参照することができます。
・主問合せのSELECT句
・INSERT文の副問合せのSELECT句
・INSERT文のVALUES句
・UPDATE文のSET句
・CREATE TABLE文またはALTER TABLE文の列のDEFAULT値
※Oracle 12cより、CREATE TABLE文やALTER TABLE文のデフォルト値にNEXTVAL疑似列とCURRVAL疑似列を指定できるようになりました。
また、NEXTVAL疑似列、CURRVAL疑似列は以下では使用できません。
・SELECT文、UPDATE文、DELETE文内の副問合せ
・ビューのSELECT句
・DISTINCTキーワードが指定されたSELECT文
・GROUP BY句、ORDER BY句、HAVING句を持つSELECT文
・集合演算子UNION、INTERSECT、MINUSによって別のSELECT文と結合されているSELECT文
・SELECT文のWHERE句
・CHECK制約の条件
なお、現在のセッションで一度も順序値が生成されていない(一度もNEXTVAL疑似列を参照していない)ときにCURRVAL疑似列を参照すると、エラーとなります
SQLを表示
CREATE SEQUENCE seq_id;
SELECT seq_id.CURRVAL FROM dual;
SELECT seq_id.CURRVAL FROM dual;
解説に誤字
h
hstuding
投稿日 2023/10/24
解説のその他の選択肢についての部分にて
・正常に実行され、MANAGER_IDが1001の行のDEPARTMENT_IDの値が2となる
・正常に実行され、MANAGER_IDが1001の行のDEPARTMENT_IDの値が2となる
INSERT文でエラーとなるため、DEPARTMENTS5表へのデータの追加/更新は行われません。
と、同じ内容が重なっていました。
選択肢を確認すれば誤字だとわかるものですが、内容の誤りになるので報告です。
スタッフからの返信
この投稿に対して返信しませんか？
s staff_ishii
2023/10/24 16:03
hstuding さん ご指摘の点を修正いたしました。 ご報告、誠にありがとうございました。