例: 列タイトルを持つビューの作成
次のリクエストは、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;
SEL * FROM t1_view;
| MinVersion_view | MinVersion_view2 |
|---|---|
| 3 | 2 |
| 2 | 2 |
例: テーブルとの自己結合を定義するビューの作成
次のリクエストは、自分の上司よりその職種において経験のある従業員を人事担当重役が追跡するために使用するビューを作成します。このリクエストは、employee表の自己結合を定義しています。FROM句で作成される相関名workersとmanagersは、自己結合に関係する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を更新することが防止されます。