ノート:

Oracle Database 23aiのSQL機能の詳細

イントロダクション

このチュートリアルでは、知っておく必要がある10の機能と、既存の機能と比較する方法を学習します。これらの機能は次のとおりです。

目的

前提条件

機能1: FROM句の使用

Oracle Database 23aiで導入された興味深い機能は、SELECT文のFROM句のオプション性です。このバージョンまでは、FROM句が必要でした。

次に、Oracle Database 23aiでFROM機能を使用しないSELECTの潜在的な利点を示します。

機能2: BOOLEANデータ型の使用

Oracle Database 23aiでは、新しいBOOLEANデータ型が導入されています。これにより、数値またはVarcharでシミュレートするのではなく、真のブール列/変数が使用されます。ブール述語を記述する機能により、SQL文の構文が簡略化されます。

  1. TEST_BOOLEANという表を作成します。

    CREATE TABLE IF NOT EXISTS TEST_BOOLEAN (name VARCHAR2(100), IS_SLEEPING BOOLEAN);

Table TEST_BOOLEAN created.

Elapsed: 00:00:00.004

  2. 新しい表にデータを入力します。値IS_SLEEPINGは、NOT NULLがデフォルトとしてFALSEに設定されます。

    ALTER TABLE TEST_BOOLEAN modify (IS_SLEEPING boolean NOT NULL);

    SQLのブールの使用方法を示すイメージ 

    ALTER TABLE TEST_BOOLEAN modify (IS_SLEEPING default FALSE);

Table TEST_BOOLEAN altered.

Elapsed: 00:00:00.014

    ここでは、Mick、KeithおよびRonの様々なタイプのブール入力を確認できます。すべて有効です。Mickの場合、デフォルトの FALSE値が使用されます- Mickはスリープしていません。

    1 row inserted.

Elapsed: 00:00:00.006

    Keithでは、NO値を使用します。Keithはスリープしていません。

    INSERT INTO TEST_BOOLEAN (name, is_sleeping) values ('Keith','NO');

1 row inserted.

Elapsed: 00:00:00.002

    Ronには 1の値を使用します。Ronはスリープ状態です。

  3. ブール値に基づいた結果をご覧ください。

    SELECT * FROM test_boolean;

    どの種類のブール型システムを導入したかを覚えておく必要はありません。前述のように、0/1、True/False、Yes/Noまたはその他の共通入力を使用すると、正確な表値が返されます。

機能3: IF NOT EXISTS DDL句の使用

Oracle Database 23ai以降、新しいIF NOT EXISTS DDL句を使用すると、DDLエラーの処理方法を決定できます。これによってDDLスクリプトが簡略化されます。オブジェクトの存在や存在しないことによる潜在的なエラーがスクリプトに隠される可能性があるためです。

  1. まず、この新機能を使用せずにテストします。次のステートメントを実行します。

    DROP TABLE DEPT;

    削除する既存のDEPT表がないため、ORA-00942: table or view does not existというエラーが表示されます。

  2. ただし、Oracle Database 23aiでは、エラーなしでDROP IF EXISTSを使用できます。これにより、エラーを回避しながら安心できます。ここで、同じ文を実行しますが、この新しいIF EXISTS機能を含めます。

    DROP TABLE IF EXISTS DEPT;

  3. 同様に、この機能を使用して、表が存在しない場合は作成できます。そのDEPT表を作成します。

    CREATE TABLE IF NOT EXISTS DEPT
	(DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
	DNAME VARCHAR2(14) ,
	LOC VARCHAR2(13) ) ;

    IF [NOT] EXISTS DDL句の使用方法を示すイメージ

  4. この機能を使用して、このチュートリアル全体でさらにサンプル表を作成します。ここでは、EMPという従業員表を作成します。

    CREATE TABLE IF NOT EXISTS EMP
(EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);

Table EMP created.

Elapsed: 00:00:00.006

機能4: 複数行にINSERTを使用

他の頻繁に使用されるデータベース管理システムとの共存および互換性を確保するもう1つの興味深い機能は、複数値のINSERT文です。

  1. 例として、Oracleデータベースの以前のバージョンでは、複数の行を挿入するには、行ごとに個別の挿入文が必要でした。

    INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');
INSERT INTO DEPT VALUES (50,'HR','LOS ANGELES');
INSERT INTO DEPT VALUES (60,'IT','SAN FRANCISCO');
INSERT INTO DEPT VALUES (70,'MANUFACTURING','DETROIT');

    Oracle Database 23aiでは、1つのINSERT文にこれらのすべての行を挿入できる新しい構文が導入されたため、1つのDMLに複数のタプルを挿入できます。次のステートメントを実行します。

    INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK'),
(20,'RESEARCH','DALLAS'),
(30,'SALES','CHICAGO'),
(40,'OPERATIONS','BOSTON'),
(50,'HR','LOS ANGELES'),
(60,'IT','SAN FRANCISCO'),
(70,'MANUFACTURING','DETROIT');

    マルチバリューINSERTの使用方法を示すイメージ

    この文を使用すると、他のデータベースとの互換性の向上に加えて、自動コミット・モードでの一部の挿入操作の一貫性を確保できます。これは、たとえば、このモードを使用してデータを処理するOracle APEXアプリケーションにとって重要です。

  2. この機能を使用してEMP表の値を入力するには、次の文を実行します。

    INSERT INTO EMP VALUES
(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20),
(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30),
(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30),
(7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20),
(7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30),
(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30),
(7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10),
(7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87')-85,3000,NULL,20),
(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10),
(7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30),
(7876,'ADAMS','CLERK',7788,to_date('13-JUL-87')-51,1100,NULL,20),
(7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30),
(7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20),
(7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);

機能5: 新しいVALUEコンストラクタの使用

Oracleデータベース23ai以降、表値コンストラクタが拡張されました。INSERT文でこれを使用して、1つのコマンドで複数の行を作成できるようになりました。SELECT文およびビュー因数分解構文でも使用できます。この場合、文の構文が簡略化され、DUAL表の使用が回避されます。

次の文は、その場でテーブル機能の一種のように見えます。

SELECT * FROM (VALUES (50,'HR'), (60,'DEV'), (70,'AI')) virt_dept (deptno, dname);

新規表値コンストラクタの使用方法を示すイメージ

機能6: GROUP BY句での別名の使用

Oracle Database 23aiでは、SELECT文のGROUP BY句で別名を使用する機能が導入されています。この機能により、複雑な式を使用した問合せの記述が簡略化され、Teradata、MySQL、PostgreSQLなどの他のリレーショナル・データベースとの互換性が向上します。

たとえば次のようにします。

SELECT to_char(hiredate,'YYYY') "Year", count(*)
FROM emp
GROUP BY to_char(hiredate,'YYYY');

Oracle Database 23aiでは、次の図に示すように、これをより簡単に記述できます。

GROUP BY句での別名の使用方法を示すイメージ

機能7: UPDATE文およびMERGE文のRETURNING句の使用

この句は、EXECUTE IMMEDIATE文の一部として以前に実装されていました。ただし、Oracle Database 23aiでは、従来の静的DML文の一部として認識できます。

  1. この場合、処理された行から列の古い値と新しい値を取得できます。まずは、今の王の給料を見てみましょう。

    SELECT ename, sal FROM emp WHERE ename = 'KING';

  2. LiveSQLで変数を使用するには、PL/SQLで文をラップアウトします。このスクリプトを実行します。最初に古い給与と新しい給与の変数を作成し、次にRETURNING句を使用してKingの給与を更新して変数を設定します。次に、結果を確認します。

    BEGIN
	DECLARE
		old_salary NUMBER;
		new_salary NUMBER;
	BEGIN
		UPDATE emp
		SET sal = sal + 1000
		WHERE ename = 'KING'
		RETURNING OLD sal, NEW sal INTO old_salary, new_salary;
		DBMS_OUTPUT.PUT_LINE('Old Salary: ' || old_salary);
		DBMS_OUTPUT.PUT_LINE('New Salary: ' || new_salary);
	END;
END;

    UPDATE文およびMERGE文のRETURNING句の使用方法を示すイメージ 

    Old Salary: 6000
New Salary: 7000

    この例ではUPDATE文を使用していましたが、RETURNING句はMERGE文と同様に使用できます。

機能8: UPDATEおよびDELETEでの結合の使用

外部表の条件に基づいて結合を使用して表データを更新できます。サブ選択またはIN句は必要ありません。

  1. 次の文を実行して、調査部門の従業員給与情報を確認します。

    select e.sal, e.empno from emp e, dept d where e.deptno=d.deptno and d.dname='RESEARCH';

  2. 給与情報を更新するには、Oracle Database 23aiより前に、ネストされた文を使用する必要があります。

    UPDATE emp e set e.sal=e.sal*2
WHERE e.deptno in
(SELECT d.deptno FROM dept d WHERE e.deptno=d.deptno
and d.dname='RESEARCH');

    Oracle Database 23aiでは、次のように使用できます。

    UPDATE emp e set e.sal=e.sal*2  FROM dept d  WHERE e.deptno=d.deptno  and d.dname='RESEARCH';

    UPDATEおよびDELETEでの結合の使用方法を示すイメージ

  3. 給与が正常に更新されたことを確認できます。

    select e.sal, e.empno from emp e, dept d where e.deptno=d.deptno and d.dname='RESEARCH';

    UPDATEおよびDELETEでの結合の使用方法を示すイメージ

機能9: 注釈の使用

注釈は、データベース・オブジェクトのオプションのメタデータです。注釈は、名前/値ペアまたは名前自体です。名前とオプションの値は自由形式のテキスト・フィールドです。注釈は、注釈が追加されているデータベース・オブジェクトに対する下位要素として表されます。サポート対象のスキーマ・オブジェクトには、表、ビュー、マテリアライズド・ビューおよび索引があります。注釈を使用すると、データベース・オブジェクトに関するメタデータを格納および取得できます。これを使用して、ビジネス・ロジックやユーザー・インタフェースをカスタマイズしたり、メタデータ・リポジトリにメタデータを提供したりできます。表レベルまたは列レベルでCREATE文またはALTER文を使用して追加できます。

注釈を使用すると、データベース・オブジェクトに関するメタデータを格納および取得できます。これを使用して、ビジネス・ロジックやユーザー・インタフェースをカスタマイズしたり、メタデータ・リポジトリにメタデータを提供したりできます。

  1. 列および表注釈を使用して、注釈付き表EMP_ANNOTATED_NEWを作成します。

    CREATE TABLE emp_annotated_new
	(empno number annotations(identity, display 'person_identity', details 'person_info'),
	ename varchar2(50),
	salary number annotations (display 'person_salary', col_hidden))
	annotations (display 'employee_table');

    データベース・オブジェクトの新規メタデータである注釈の使用方法を示すイメージ

  2. USER_ANNOTATIONSやUSER_ANNOTATIONS_USAGEなどのデータ・ディクショナリ・ビューは、使用状況の監視に役立ちます。

    SELECT object_name, object_type, column_name, annotation_name, annotation_value FROM user_annotations_usage;

    データベース・オブジェクトの新規メタデータである注釈の使用方法を示すイメージ

機能10: SQLドメインの使用

SQLドメインは、スキーマに属しているディクショナリ・オブジェクトで、オプションのプロパティと制約のセットがカプセル化され、CREATE DOMAIN文を使用して作成されます。ドメインは、制約、表示、順序付けおよび注釈属性を提供します。SQLドメインを定義した後、そのドメインの列に関連付ける表の列を定義して、ドメインのオプションのプロパティおよび制約を明示的にその列に適用できます

SQLドメインを使用すると、ユーザーは列の目的の使用を宣言できます。これらはデータ・ディクショナリ・オブジェクトであるため、抽象ドメイン固有のナレッジを簡単に再利用できます。

  1. yearbirthという名前のドメインとpersonという名前の表を作成します。

    CREATE DOMAIN yearbirth as number(4)
constraint check ((trunc(yearbirth) = yearbirth) and (yearbirth >= 1900))
display (case when yearbirth < 2000 then '19-' ELSE '20-' end)||mod(yearbirth, 100)
order (yearbirth -1900)
annotations (title 'yearformat');

    SQL Domainsで軽量オブジェクト型を使用する方法を示すイメージ

    CREATE TABLE person
(id number(5),
name varchar2(50),
salary number,
person_birth number(4) DOMAIN yearbirth
)
annotations (display 'person_table');

    SQL Domainsで軽量オブジェクト型を使用する方法を示すイメージ

    ```降下人;

    名前がNULLですか。タイプ

ID番号(5)名前 VARCHAR2(50)給与番号 PERSON_BIRTH番号(4)ドメイン年生

INSERT INTO person values (1,'MARTIN',3000, 1988);

2. With the new function `DOMAIN_DISPLAY` you can display the property.

SELECT DOMAIN_DISPLAY(person_birth) FROM person;


   ![Image showing how to use Light weight object types" with SQL Domains ](./images/Picture14.png "Light weight object types with SQL Domains")

3. Domain usage and annotations can be monitored with data dictionary views. Let us view `user_annotations_usage`.

user_annotations_usageから*を選択します。


   Define reusable domain types (lightweight objects).

ドメインEmailAddressをVARCHAR2(100)として作成チェック(REGEXP_LIKE(値、'^[^@]+@[^@]+.[^@]+$'));

CREATE TABLE users ( user_id NUMBER, email EmailAddress ); ``

SQL Domainsで軽量オブジェクト型を使用する方法を示すイメージ

Oracle Database 23aiの機能の制限事項

一般的な制限

確認

その他の学習リソース

docs.oracle.com/learnで他のラボを確認するか、Oracle Learning YouTubeチャネルで無料のラーニング・コンテンツにアクセスしてください。また、education.oracle.com/learning-explorerにアクセスして、Oracle Learning Explorerになります。

製品ドキュメントについては、Oracle Help Centerを参照してください。