17.05 - CREATE VIEWおよびREPLACE VIEWの例 - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQLデータ定義言語 構文規則および例

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
2021年1月
Content Type
プログラミング リファレンス
Publication ID
B035-1144-175K-JPN
Language
日本語 (日本)

例: 列タイトルを持つビューの作成

次のリクエストは、departmentテーブルのビューを作成することを示します。ビュー内の各列は、標題と一緒に定義されています。そのため、ビュー データは、departmentテーブルに定義されたものとは異なる列の標題で表示されます。

    CREATE VIEW dept AS
     SELECT   deptno(TITLE 'Department Number'),
              deptname(TITLE 'Department Name'), 
              loc (TITLE 'Department Location'), 
              mgrno(TITLE 'Manager Number') 
     FROM department;

例: 更新、挿入、または削除リクエストでのビューの使用

次の例では、ビューが基礎としているテーブル セットのデータに、追加、変更、または削除を行なうUPDATE、INSERT、またはDELETEリクエストでのビュー名の使い方を示します。例えば、ビューでのデータの更新は、基礎となるテーブルのデータを変更します。ビューで行を挿入または削除すると、基礎となるテーブルで行が追加または削除されます。

次のstaff_infoビューについて検討します。このビューは、副社長と管理職者を除くすべての従業員の従業員番号、氏名、職種、部門番号、性別、および生年月日の情報に人事部の事務員がアクセスできるようにします。

    CREATE VIEW staff_info (number, name, position, department,                         sex, dob) AS
     SELECT employee.empno, name, jobtitle, deptno, sex, dob 
     FROM employee
     WHERE jobtitle NOT IN ('Vice Pres', 'Manager')
    WITH CHECK OPTION;

staff_infoの所有者にemployee表への挿入権限があり、職員にもstaff_infoへの挿入権限がある場合、その職員はこのビューを使用して新しい行をemployeeに追加することができます。例えば、次のINSERTリクエストを実行すると、employee表の中に、指定された情報を含む行が挿入されます。

    INSERT INTO staff_info (number, name, position, department, sex,
                dob) 
     VALUES (10024, 'Crowell N', 'Secretary', 200, 'F', 'Jun 03 1960');

次のWHERE句に示したstaff_infoに対する制約は、WITH CHECK OPTION句を含むこのビューを使用するすべての挿入に適用されます。

    ...
    WHERE jobtitle NOT IN ('Vice Pres', 'Manager') 
    ...

そのため、前に示したINSERTリクエストは、Crowellのために入力されたPositionがVice PresまたはManagerの場合には失敗となります。

このビューがWITH CHECK OPTIONなしで定義されていて、ユーザーがUPDATE権限を付与されている場合、そのユーザーはjobtitleをVice PresまたはManagerに更新できます。ユーザーは、変更された行にビューを介してアクセスすることはできません。

次のリクエストは、前のINSERTリクエストによってCrowellに入力された部門番号(200~300)を変更します。

    UPDATE staff_info 
     SET department = 300 
     WHERE number = 10024;

次のDELETEリクエストを実行すると、staff_info表から従業員Crowellの行が削除されます。

    DELETE FROM staff_info 
     WHERE number = 10024;

ビューは、ユーザーがテーブル データにアクセスできるようにする便利な方法です。ただし、前の例で示したように、あるビューに対する挿入、更新、および削除の権限を他のユーザーに与えることは、自分のデータへの何らかの管理を手放すことになります。このような権限の付与については、よく注意する必要があります。

デフォルトでは、ビュー定義に明示的にWITH CHECK OPTIONが含まれていない限り、更新または挿入される値は制約されません。

例: 動的UDT式を持つビューの作成

この例では、ビュー定義に動的UDTを指定する方法を示しています。

まず、次のリクエストは、列式がすべて指定されている長いバージョンを示しています。

     SELECT udf_aggregate_mp_struc(NEW VARIANT_TYPE(MultiType.w AS w,             MultiType.x AS x, 
            MultiType.y AS y, 
            NEW MP_STRUCTURED_INT(MultiType.w, MultiType.x, 
            MultiType.y) AS z)) AS m 
     FROM MultiType;
      *** Query completed. One row found. One column returned.
      *** Total elapsed time was 1 second.
               m
     -----------
              60

さらに、前のSELECTリクエストを組み込むビューを作成します。

     CREATE VIEW multitype_v AS
     SELECT udf_aggregate_mp_struc(NEW VARIANT_TYPE(MultiType.w AS w,             MultiType.x AS x, 
            MultiType.y AS y, 
            NEW MP_STRUCTURED_INT(MultiType.w, MultiType.x, 
            MultiType.y) AS z)) AS m FROM MultiType;

新しく作成したビューに対して、次のSELECTリクエストを実行すると、同じ結果が返されます。

     SELECT * 
     FROM multitype_v;
      *** Query completed. One row found. One column returned.
      *** Total elapsed time was 1 second.
               m
     -----------
              60

例: ビューの置換

employee_infoビュー内の部門nameの列を部門番号の列に変更するには、次のように入力します。

    REPLACE VIEW employee_info (number, name, position, department)
     AS SELECT employee.empno, name, jobtitle, deptno 
        FROM emp_info
        WHERE jobtitle NOT IN ('vice pres', 'manager');

ビューを置き換えるには、ビューまたはそれを含むデータベースまたはユーザーに対するDROP権限が必要です。

存在しないビューを指定したREPLACE VIEWリクエストを入力した場合、システムは、そのREPLACE文の指定に従ってビューを作成します。

例: 行レベル セキュリティ制約を持つテーブルのビューの作成

この例では、行レベル セキュリティ制約が定義されているテーブルのビューを作成する方法を示します。最初にemp_recordというテーブルを作成します。このテーブルで、ビューemp_record_viewが定義されます。

emp_record表の行レベル セキュリティ制約をgroup_membershipとして定義します。Vantageは、このemp_recordを作成するときに、group_membershipという名前の4番目のセキュリティ制約列を暗黙的に表に追加します。

     CREATE TABLE emp_record (
       emp_name   VARCHAR(30), 
       emp_number INTEGER, 
       salary     INTEGER,
       group_membership CONSTRAINT)
     UNIQUE PRIMARY INDEX (emp_name);

次に、emp_recordのビューemp_record_viewを定義します。

     CREATE VIEW emp_record_view AS
       SELECT emp_number, salary, group_membership 
       FROM emp_record
       WHERE emp_name=user;

例: CREATE VIEWでのWITH修飾子の使用

WITH修飾子の共通テーブル式(CTE)は、CTEが間接的に自己参照を行なわない場合、WITH修飾子で定義された先行するCTEまたは後続のCTEを参照できます。つまり、循環参照は許可されません。

この例では、テーブルの定義は次のとおりです。

CREATE TABLE orders (customer_id INTEGER, total_cost FLOAT);
INSERT INTO orders (43563, 734.12);
INSERT INTO orders (65758, 211.15);
INSERT INTO orders (23235, 1264.98);
INSERT INTO orders (43563, 583.23);
INSERT INTO orders (89786, 278.66);
INSERT INTO orders (13253, 401.97);
INSERT INTO orders (98765, 1042.23);
INSERT INTO orders (23235, 699.23);
INSERT INTO orders (43563, 935.35);
INSERT INTO orders (88354, 375.09);

この例では、WITH修飾子の名前付きクエリー使用してsales_vビューを作成します。WITH修飾子には、multiple_order_totalsとして指定された非再帰的共通テーブル式(CTE)が含まれており、WITH句で以前定義されたmultiple_ordersテーブルを参照します。

CREATE VIEW sales_v AS
   WITH multiple_orders AS (
   SELECT customer_id, COUNT(*) AS order_count
   FROM orders
   GROUP BY customer_id
   HAVING COUNT(*) > 1
),
multiple_order_totals AS (
   SELECT customer_id, SUM(total_cost) AS total_spend
   FROM orders
   WHERE customer_id IN (SELECT customer_id FROM multiple_orders) 
   GROUP BY customer_id
)
SELECT * FROM multiple_order_totals;

次に、ビューにクエリーをかけることができます。

SELECT * FROM sales_v
ORDER BY total_spend DESC;

このクエリーは、以下のような応答セットを返します。

customer_id total_spend
43563 2.25270000000000E 003
23235 1.96421000000000E 003

この例のWITH修飾子にはmultiple_order_totalsとして指定された非再帰的共通テーブル式(CTE)が含まれており、WITH句でそれ以降に定義されたmultiple_ordersテーブルを参照します。

CREATE VIEW sales_v AS
WITH multiple_order_totals AS (
   SELECT customer_id, SUM(total_cost) AS total_spend
   FROM orders
   WHERE customer_id IN (SELECT customer_id FROM multiple_orders)
   GROUP BY customer_id
),
multiple_orders AS (
   SELECT customer_id, COUNT(*) AS order_count
   FROM orders
   GROUP BY customer_id
   HAVING COUNT(*) > 1
)
SELECT * FROM multiple_order_totals;

次に、ビューにクエリーをかけることができます。

SELECT * FROM sales_v
ORDER BY total_spend DESC;

このクエリーは、以下のような応答セットを返します。

customer_id total_spend
43563 2.25270000000000E 003
23235 1.96421000000000E 003

例: CREATE VIEWのWITH修飾子での再帰的問合わせの使用

次に、再帰的問合わせs5に基づいたt1_viewビューを示します。

CREATE VIEW t1_view AS WITH RECURSIVE  s5 (MinVersion_view) AS (SELECT a1 FROM t1 WHERE a1 > 1
UNION ALL
SEL MinVersion_view FROM s5 WHERE MinVersion_view > 3),
RECURSIVE s6 (MinVersion_view2) AS (SELECT a1 FROM t1 WHERE a1 = 2
UNION ALL
SEL MinVersion_view2 FROM S6 WHERE MinVersion_view2 > 2)
SEL * FROM s5,s6;
次の文はt1_viewビューの内容です。
SEL * FROM t1_view;
MinVersion_view MinVersion_view2
3 2
2 2

例: テーブルとの自己結合を定義するビューの作成

次のリクエストは、自分の上司よりその職種において経験のある従業員を人事担当重役が追跡するために使用するビューを作成します。このリクエストは、employee表の自己結合を定義しています。FROM句で作成される相関名workersmanagersは、自己結合に関係する2つの一時テーブルを参照します。

    CREATE VIEW emp_info (workername,workeryrsexp,department, 
                          managername,manageryrsexp) AS
     SELECT workers.name, workers.yrsexp, workers.deptno, 
            managers.name, managers.yrsexp
     FROM employee AS workers, employee AS managers 
     WHERE workers.deptno = managers.deptno 
     AND managers.jobtitle IN ('Manager', 'Vice Pres') 
     AND workers.yrsexp > managers.yrsexp;

例: ビュー定義からのSQL UDFの呼び出し

この例はv1というビューの定義の選択リストでSQL UDF value_expressionを呼び出します。

     CREATE VIEW v1 (a, b, c) 
       AS SELECT a1, test.value_expression(3,4), c1
          FROM t1 
          WHERE a1 > b1;

次の例は、v2というビューのWHERE句でSQL UDF value_expressionを呼び出します。

     CREATE VIEW v2 (a, b, c) 
       AS SELECT a1, b1, c1 
          FROM t1
          WHERE test.value_expression(b1, c1) > 10;

例: 集約を含むビューの作成

次のリクエストでは、ビュー定義における集約の使い方を示します。結果の行は部門番号によってまとめられ、平均給与が35,000ドル以上の行だけを含みます。

    CREATE VIEW dept_sal (deptno, minsal, maxsal, avgsal) AS 
     SELECT deptno, MIN(salary), MAX(salary), AVG(salary) 
     FROM employee 
     GROUP BY deptno 
     HAVING AVG(salary) >= 35000;

ここで、このビューを使用して次のSELECTリクエストを実行します。

    SELECT * 
    FROM dept_sal; 

この問合わせは、次の応答セットを返します。

    DeptNo         MinSal         MaxSal         AvgSal 
    ------     ----------      ---------     ----------
       600      28,600.00      45,000.00      36,650.00
       300      23,000.00      65,000.00      47,666.67
       700      30,000.00      45,000.00      37,666.67
       500      22,000.00      56,000.00      38,285.71

次のSQLリクエストは、その後に示す応答セットを返します。

    SELECT deptno, minsal, minsal+10000, avgsal 
    FROM dept_sal 
    WHERE avgsal > (minsal + 10000);
    
    DeptNo         MinSal      (MinSal+10000)         AvgSal 
    ------     ----------   -----------------     ----------
       500      22,000.00            32000.00      38,285.71
       300      23,000.00            33000.00      47,666.67

例: 名前や職種のみのビューの作成

次のリクエストは、employee(従業員)テーブルのビューを作成し、部門300の従業員の名前と職種にだけアクセスすることを示します。

    CREATE VIEW dept300 (name, jobtitle) AS 
     SELECT name, jobtitle 
     FROM employee 
     WHERE DeptNo = 300 
    WITH CHECK OPTION;

WITH CHECK OPTIONによって、DeptNoが300でない行について、このビューを介してemployeeにINSERTすることと、employeeを更新することが防止されます。