助け合いフォーラム
Oracle Master Silver SQL 2019(1Z0-071)
問題ID : 26794
問題を開く
EMPLOYEES表の構造を確認して下さい。
![<img src="/mondai3/img/jpg/48861.jpg">](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/19316/48861.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T042330Z&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEAwaDmFwLW5vcnRoZWFzdC0xIkcwRQIgf%2BzqFCJvQHCwpd9KQZfj%2BwFh942kafzm2pIFRXqTnBcCIQD6uWqxPnXhfOn5TT2GIUn1NBXuFXkZUvGzBJ7Q0MbwWiqxBAjl%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F8BEAAaDDYyMzQyOTgyNzI3NSIMCCMAJ4fQ0Jp2ziMkKoUEcwshKVX9srmpq3jK9XA%2Bpd1SWtbBzE2N7%2FcmnYCMEacy73KsIVR7AwQGHhXNF2Nc%2BCaq7wFnPq8c8IisX5MV7l3gdpLh5zO1q%2BBn%2BVhCjE9ucBNWtK3%2F3yEhpLoxJ6ZaxIEyXnhmv50ZAQbcKuux2lWZRaPe%2BuOXufwt08wGH99lYbSXcQb45Zbd373%2BFGLJsTDuv%2BObTnm%2FLyNS0kG%2BJEOqrcrdAQnQ0rmZfZnu8MxVypTCsvOD0k4FfvaVZ%2F4y3BbKvqWr6zNUjx%2FDOiqxFJtxhvPfDZHW15U0rl%2BAqgoNMHGG1p%2FZLGqwu9Lhrv8B2%2FG%2BOX%2Bg0wnNtQGY%2Fr%2By5sAx7SIKY8HZIKABnmxPPmRCEepfjNG%2FxkzgpDgJTzsN%2Bl2x%2FkSWVFbi20bVWszOn%2BRK2M5BCx9vLK6YsxlbQJrmD5ELpzryRL5uh0JwbeP354FpF603az9FFd0%2FVHRPpYxXG60%2FMYJGiZf9atN7IiFuXwUtJ38jvHx7zyMToyg%2BiQ6V7BNBe663hzNJPTEB%2BObghVYAsAFDe37TWg0pBV0uM0tOZWVyKzquvrw7LPkiF7VrUFQ1qbYa0EFcQ1Lbc6AZEZiNgNKcP289C%2B6rXEbVNHsKmXfQruFMZ3ML6WGwCVuYv6hj7UUYjTy4TPgVa9UxxliYirWqyDs%2F5YkPIS8t8wgb5jCz2pG1BjqmAb%2BFgTAGqCk6D0E%2FV5ihAi3xTSDmxL8rrT%2BYE60eA0EHE09Z6HRmGD0t3ZJmajP517o%2FpPpzlj60AD66omvV0HwiY2qpj9KXwBaDbV%2Bnerc%2Fgyks9ybTnsrcPUcPyHnthvQFbTldOk1LR9x9fLfK2Xf%2FuxJnssaPt7z7vXUZMWvqtEw90TsFFe62ZOG6Kfg56DlM1R7KIfpJSltaqBBACO%2FPa9fNp80%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIAZCJ2QHLFWNYC4QUJ%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=a058b99e55699265e470ca2e2ab980016d7c91c59b8ea2a4b6246e5f48279902)
EMPLOYEES表を基に次の3つのビューを作成しました。ただし、INSTEAD OFトリガーは定義していないものとします。
ビューと実行可能な操作の組合せとして正しいものはどれですか(該当するものを2つ選択してください)。
ビュー1)
CREATE OR REPLACE VIEW v_emp1
AS
SELECT employee_id, employee_name, salary * 12 sal
FROM employees;
ビュー2)
CREATE VIEW v_emp2
AS
SELECT department_id, AVG(salary) avgsal
FROM employees
WHERE salary > 600000
GROUP BY department_id;
ビュー3)
CREATE VIEW v_emp3
AS
SELECT employee_name, salary
FROM employees;
![<img src="/mondai3/img/jpg/48861.jpg">](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/19316/48861.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T042330Z&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEAwaDmFwLW5vcnRoZWFzdC0xIkcwRQIgf%2BzqFCJvQHCwpd9KQZfj%2BwFh942kafzm2pIFRXqTnBcCIQD6uWqxPnXhfOn5TT2GIUn1NBXuFXkZUvGzBJ7Q0MbwWiqxBAjl%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F8BEAAaDDYyMzQyOTgyNzI3NSIMCCMAJ4fQ0Jp2ziMkKoUEcwshKVX9srmpq3jK9XA%2Bpd1SWtbBzE2N7%2FcmnYCMEacy73KsIVR7AwQGHhXNF2Nc%2BCaq7wFnPq8c8IisX5MV7l3gdpLh5zO1q%2BBn%2BVhCjE9ucBNWtK3%2F3yEhpLoxJ6ZaxIEyXnhmv50ZAQbcKuux2lWZRaPe%2BuOXufwt08wGH99lYbSXcQb45Zbd373%2BFGLJsTDuv%2BObTnm%2FLyNS0kG%2BJEOqrcrdAQnQ0rmZfZnu8MxVypTCsvOD0k4FfvaVZ%2F4y3BbKvqWr6zNUjx%2FDOiqxFJtxhvPfDZHW15U0rl%2BAqgoNMHGG1p%2FZLGqwu9Lhrv8B2%2FG%2BOX%2Bg0wnNtQGY%2Fr%2By5sAx7SIKY8HZIKABnmxPPmRCEepfjNG%2FxkzgpDgJTzsN%2Bl2x%2FkSWVFbi20bVWszOn%2BRK2M5BCx9vLK6YsxlbQJrmD5ELpzryRL5uh0JwbeP354FpF603az9FFd0%2FVHRPpYxXG60%2FMYJGiZf9atN7IiFuXwUtJ38jvHx7zyMToyg%2BiQ6V7BNBe663hzNJPTEB%2BObghVYAsAFDe37TWg0pBV0uM0tOZWVyKzquvrw7LPkiF7VrUFQ1qbYa0EFcQ1Lbc6AZEZiNgNKcP289C%2B6rXEbVNHsKmXfQruFMZ3ML6WGwCVuYv6hj7UUYjTy4TPgVa9UxxliYirWqyDs%2F5YkPIS8t8wgb5jCz2pG1BjqmAb%2BFgTAGqCk6D0E%2FV5ihAi3xTSDmxL8rrT%2BYE60eA0EHE09Z6HRmGD0t3ZJmajP517o%2FpPpzlj60AD66omvV0HwiY2qpj9KXwBaDbV%2Bnerc%2Fgyks9ybTnsrcPUcPyHnthvQFbTldOk1LR9x9fLfK2Xf%2FuxJnssaPt7z7vXUZMWvqtEw90TsFFe62ZOG6Kfg56DlM1R7KIfpJSltaqBBACO%2FPa9fNp80%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIAZCJ2QHLFWNYC4QUJ%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=a058b99e55699265e470ca2e2ab980016d7c91c59b8ea2a4b6246e5f48279902)
EMPLOYEES表を基に次の3つのビューを作成しました。ただし、INSTEAD OFトリガーは定義していないものとします。
ビューと実行可能な操作の組合せとして正しいものはどれですか(該当するものを2つ選択してください)。
ビュー1)
CREATE OR REPLACE VIEW v_emp1
AS
SELECT employee_id, employee_name, salary * 12 sal
FROM employees;
ビュー2)
CREATE VIEW v_emp2
AS
SELECT department_id, AVG(salary) avgsal
FROM employees
WHERE salary > 600000
GROUP BY department_id;
ビュー3)
CREATE VIEW v_emp3
AS
SELECT employee_name, salary
FROM employees;
正解
ビュー1を通じて、EMPLOYEES表のデータの削除ができる
ビュー3を通じて、EMPLOYEES表のデータの更新ができる
解説
作成したビューを通じて実表のデータを操作することができます。
ただし、表に直接アクセスする場合とは異なり、ビューを通じての実表のデータの操作には、ビューの定義によっていろいろな制限があります。
ビューを通じて実表のデータを操作することができる基本的なルールは次の通りです。
![<img src="/mondai3/img/jpg/k49146.jpg">](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/19851/k49146.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T042330Z&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEAwaDmFwLW5vcnRoZWFzdC0xIkcwRQIgf%2BzqFCJvQHCwpd9KQZfj%2BwFh942kafzm2pIFRXqTnBcCIQD6uWqxPnXhfOn5TT2GIUn1NBXuFXkZUvGzBJ7Q0MbwWiqxBAjl%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F8BEAAaDDYyMzQyOTgyNzI3NSIMCCMAJ4fQ0Jp2ziMkKoUEcwshKVX9srmpq3jK9XA%2Bpd1SWtbBzE2N7%2FcmnYCMEacy73KsIVR7AwQGHhXNF2Nc%2BCaq7wFnPq8c8IisX5MV7l3gdpLh5zO1q%2BBn%2BVhCjE9ucBNWtK3%2F3yEhpLoxJ6ZaxIEyXnhmv50ZAQbcKuux2lWZRaPe%2BuOXufwt08wGH99lYbSXcQb45Zbd373%2BFGLJsTDuv%2BObTnm%2FLyNS0kG%2BJEOqrcrdAQnQ0rmZfZnu8MxVypTCsvOD0k4FfvaVZ%2F4y3BbKvqWr6zNUjx%2FDOiqxFJtxhvPfDZHW15U0rl%2BAqgoNMHGG1p%2FZLGqwu9Lhrv8B2%2FG%2BOX%2Bg0wnNtQGY%2Fr%2By5sAx7SIKY8HZIKABnmxPPmRCEepfjNG%2FxkzgpDgJTzsN%2Bl2x%2FkSWVFbi20bVWszOn%2BRK2M5BCx9vLK6YsxlbQJrmD5ELpzryRL5uh0JwbeP354FpF603az9FFd0%2FVHRPpYxXG60%2FMYJGiZf9atN7IiFuXwUtJ38jvHx7zyMToyg%2BiQ6V7BNBe663hzNJPTEB%2BObghVYAsAFDe37TWg0pBV0uM0tOZWVyKzquvrw7LPkiF7VrUFQ1qbYa0EFcQ1Lbc6AZEZiNgNKcP289C%2B6rXEbVNHsKmXfQruFMZ3ML6WGwCVuYv6hj7UUYjTy4TPgVa9UxxliYirWqyDs%2F5YkPIS8t8wgb5jCz2pG1BjqmAb%2BFgTAGqCk6D0E%2FV5ihAi3xTSDmxL8rrT%2BYE60eA0EHE09Z6HRmGD0t3ZJmajP517o%2FpPpzlj60AD66omvV0HwiY2qpj9KXwBaDbV%2Bnerc%2Fgyks9ybTnsrcPUcPyHnthvQFbTldOk1LR9x9fLfK2Xf%2FuxJnssaPt7z7vXUZMWvqtEw90TsFFe62ZOG6Kfg56DlM1R7KIfpJSltaqBBACO%2FPa9fNp80%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIAZCJ2QHLFWNYC4QUJ%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=add657c325b141207845ef0bfd02f24a07ab95fba20bd6eeb101d0df2c1e6d90)
設問のビュー1はビューの定義に式によって定義された列が含まれています。データの削除を行うことができます。
ビュー2はビューの定義にGROUP BY句とグループ関数が使用されているため、データの追加、更新、削除の全てを行うことができません。
ビュー3はNOT NULL制約が定義されているEMPLOYEE_ID列がビューの定義に含まれていません。データの更新を行うことができます。
以上より、
・ビュー1を通じて、EMPLOYEES表のデータの削除ができる
・ビュー3を通じて、EMPLOYEES表のデータの更新ができる
が正解となります。
設問のSQL文の実行結果は次のようになります。
[ビュー1]
![【図を表示】](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/20226/k49179.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T042330Z&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEAwaDmFwLW5vcnRoZWFzdC0xIkcwRQIgf%2BzqFCJvQHCwpd9KQZfj%2BwFh942kafzm2pIFRXqTnBcCIQD6uWqxPnXhfOn5TT2GIUn1NBXuFXkZUvGzBJ7Q0MbwWiqxBAjl%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F8BEAAaDDYyMzQyOTgyNzI3NSIMCCMAJ4fQ0Jp2ziMkKoUEcwshKVX9srmpq3jK9XA%2Bpd1SWtbBzE2N7%2FcmnYCMEacy73KsIVR7AwQGHhXNF2Nc%2BCaq7wFnPq8c8IisX5MV7l3gdpLh5zO1q%2BBn%2BVhCjE9ucBNWtK3%2F3yEhpLoxJ6ZaxIEyXnhmv50ZAQbcKuux2lWZRaPe%2BuOXufwt08wGH99lYbSXcQb45Zbd373%2BFGLJsTDuv%2BObTnm%2FLyNS0kG%2BJEOqrcrdAQnQ0rmZfZnu8MxVypTCsvOD0k4FfvaVZ%2F4y3BbKvqWr6zNUjx%2FDOiqxFJtxhvPfDZHW15U0rl%2BAqgoNMHGG1p%2FZLGqwu9Lhrv8B2%2FG%2BOX%2Bg0wnNtQGY%2Fr%2By5sAx7SIKY8HZIKABnmxPPmRCEepfjNG%2FxkzgpDgJTzsN%2Bl2x%2FkSWVFbi20bVWszOn%2BRK2M5BCx9vLK6YsxlbQJrmD5ELpzryRL5uh0JwbeP354FpF603az9FFd0%2FVHRPpYxXG60%2FMYJGiZf9atN7IiFuXwUtJ38jvHx7zyMToyg%2BiQ6V7BNBe663hzNJPTEB%2BObghVYAsAFDe37TWg0pBV0uM0tOZWVyKzquvrw7LPkiF7VrUFQ1qbYa0EFcQ1Lbc6AZEZiNgNKcP289C%2B6rXEbVNHsKmXfQruFMZ3ML6WGwCVuYv6hj7UUYjTy4TPgVa9UxxliYirWqyDs%2F5YkPIS8t8wgb5jCz2pG1BjqmAb%2BFgTAGqCk6D0E%2FV5ihAi3xTSDmxL8rrT%2BYE60eA0EHE09Z6HRmGD0t3ZJmajP517o%2FpPpzlj60AD66omvV0HwiY2qpj9KXwBaDbV%2Bnerc%2Fgyks9ybTnsrcPUcPyHnthvQFbTldOk1LR9x9fLfK2Xf%2FuxJnssaPt7z7vXUZMWvqtEw90TsFFe62ZOG6Kfg56DlM1R7KIfpJSltaqBBACO%2FPa9fNp80%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIAZCJ2QHLFWNYC4QUJ%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=524a6b8e87e395078c53535a39c60f410023de53a0579b28166f36dd891ad90b)
[ビュー2]
![【図を表示2】](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/20227/kk49179.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T042330Z&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEAwaDmFwLW5vcnRoZWFzdC0xIkcwRQIgf%2BzqFCJvQHCwpd9KQZfj%2BwFh942kafzm2pIFRXqTnBcCIQD6uWqxPnXhfOn5TT2GIUn1NBXuFXkZUvGzBJ7Q0MbwWiqxBAjl%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F8BEAAaDDYyMzQyOTgyNzI3NSIMCCMAJ4fQ0Jp2ziMkKoUEcwshKVX9srmpq3jK9XA%2Bpd1SWtbBzE2N7%2FcmnYCMEacy73KsIVR7AwQGHhXNF2Nc%2BCaq7wFnPq8c8IisX5MV7l3gdpLh5zO1q%2BBn%2BVhCjE9ucBNWtK3%2F3yEhpLoxJ6ZaxIEyXnhmv50ZAQbcKuux2lWZRaPe%2BuOXufwt08wGH99lYbSXcQb45Zbd373%2BFGLJsTDuv%2BObTnm%2FLyNS0kG%2BJEOqrcrdAQnQ0rmZfZnu8MxVypTCsvOD0k4FfvaVZ%2F4y3BbKvqWr6zNUjx%2FDOiqxFJtxhvPfDZHW15U0rl%2BAqgoNMHGG1p%2FZLGqwu9Lhrv8B2%2FG%2BOX%2Bg0wnNtQGY%2Fr%2By5sAx7SIKY8HZIKABnmxPPmRCEepfjNG%2FxkzgpDgJTzsN%2Bl2x%2FkSWVFbi20bVWszOn%2BRK2M5BCx9vLK6YsxlbQJrmD5ELpzryRL5uh0JwbeP354FpF603az9FFd0%2FVHRPpYxXG60%2FMYJGiZf9atN7IiFuXwUtJ38jvHx7zyMToyg%2BiQ6V7BNBe663hzNJPTEB%2BObghVYAsAFDe37TWg0pBV0uM0tOZWVyKzquvrw7LPkiF7VrUFQ1qbYa0EFcQ1Lbc6AZEZiNgNKcP289C%2B6rXEbVNHsKmXfQruFMZ3ML6WGwCVuYv6hj7UUYjTy4TPgVa9UxxliYirWqyDs%2F5YkPIS8t8wgb5jCz2pG1BjqmAb%2BFgTAGqCk6D0E%2FV5ihAi3xTSDmxL8rrT%2BYE60eA0EHE09Z6HRmGD0t3ZJmajP517o%2FpPpzlj60AD66omvV0HwiY2qpj9KXwBaDbV%2Bnerc%2Fgyks9ybTnsrcPUcPyHnthvQFbTldOk1LR9x9fLfK2Xf%2FuxJnssaPt7z7vXUZMWvqtEw90TsFFe62ZOG6Kfg56DlM1R7KIfpJSltaqBBACO%2FPa9fNp80%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIAZCJ2QHLFWNYC4QUJ%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=2e193716fa00419f1a05fd159284ef2fedcc3fc89839da91e760915898a4e29b)
[ビュー3]
![【図を表示3】](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/20228/kkk49179.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T042330Z&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEAwaDmFwLW5vcnRoZWFzdC0xIkcwRQIgf%2BzqFCJvQHCwpd9KQZfj%2BwFh942kafzm2pIFRXqTnBcCIQD6uWqxPnXhfOn5TT2GIUn1NBXuFXkZUvGzBJ7Q0MbwWiqxBAjl%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F8BEAAaDDYyMzQyOTgyNzI3NSIMCCMAJ4fQ0Jp2ziMkKoUEcwshKVX9srmpq3jK9XA%2Bpd1SWtbBzE2N7%2FcmnYCMEacy73KsIVR7AwQGHhXNF2Nc%2BCaq7wFnPq8c8IisX5MV7l3gdpLh5zO1q%2BBn%2BVhCjE9ucBNWtK3%2F3yEhpLoxJ6ZaxIEyXnhmv50ZAQbcKuux2lWZRaPe%2BuOXufwt08wGH99lYbSXcQb45Zbd373%2BFGLJsTDuv%2BObTnm%2FLyNS0kG%2BJEOqrcrdAQnQ0rmZfZnu8MxVypTCsvOD0k4FfvaVZ%2F4y3BbKvqWr6zNUjx%2FDOiqxFJtxhvPfDZHW15U0rl%2BAqgoNMHGG1p%2FZLGqwu9Lhrv8B2%2FG%2BOX%2Bg0wnNtQGY%2Fr%2By5sAx7SIKY8HZIKABnmxPPmRCEepfjNG%2FxkzgpDgJTzsN%2Bl2x%2FkSWVFbi20bVWszOn%2BRK2M5BCx9vLK6YsxlbQJrmD5ELpzryRL5uh0JwbeP354FpF603az9FFd0%2FVHRPpYxXG60%2FMYJGiZf9atN7IiFuXwUtJ38jvHx7zyMToyg%2BiQ6V7BNBe663hzNJPTEB%2BObghVYAsAFDe37TWg0pBV0uM0tOZWVyKzquvrw7LPkiF7VrUFQ1qbYa0EFcQ1Lbc6AZEZiNgNKcP289C%2B6rXEbVNHsKmXfQruFMZ3ML6WGwCVuYv6hj7UUYjTy4TPgVa9UxxliYirWqyDs%2F5YkPIS8t8wgb5jCz2pG1BjqmAb%2BFgTAGqCk6D0E%2FV5ihAi3xTSDmxL8rrT%2BYE60eA0EHE09Z6HRmGD0t3ZJmajP517o%2FpPpzlj60AD66omvV0HwiY2qpj9KXwBaDbV%2Bnerc%2Fgyks9ybTnsrcPUcPyHnthvQFbTldOk1LR9x9fLfK2Xf%2FuxJnssaPt7z7vXUZMWvqtEw90TsFFe62ZOG6Kfg56DlM1R7KIfpJSltaqBBACO%2FPa9fNp80%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIAZCJ2QHLFWNYC4QUJ%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=64af47e65b47fa0f81548367321530e4bddfbd4260dff7502ff65fbe1b64bff5)
ただし、表に直接アクセスする場合とは異なり、ビューを通じての実表のデータの操作には、ビューの定義によっていろいろな制限があります。
ビューを通じて実表のデータを操作することができる基本的なルールは次の通りです。
![<img src="/mondai3/img/jpg/k49146.jpg">](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/19851/k49146.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T042330Z&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEAwaDmFwLW5vcnRoZWFzdC0xIkcwRQIgf%2BzqFCJvQHCwpd9KQZfj%2BwFh942kafzm2pIFRXqTnBcCIQD6uWqxPnXhfOn5TT2GIUn1NBXuFXkZUvGzBJ7Q0MbwWiqxBAjl%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F8BEAAaDDYyMzQyOTgyNzI3NSIMCCMAJ4fQ0Jp2ziMkKoUEcwshKVX9srmpq3jK9XA%2Bpd1SWtbBzE2N7%2FcmnYCMEacy73KsIVR7AwQGHhXNF2Nc%2BCaq7wFnPq8c8IisX5MV7l3gdpLh5zO1q%2BBn%2BVhCjE9ucBNWtK3%2F3yEhpLoxJ6ZaxIEyXnhmv50ZAQbcKuux2lWZRaPe%2BuOXufwt08wGH99lYbSXcQb45Zbd373%2BFGLJsTDuv%2BObTnm%2FLyNS0kG%2BJEOqrcrdAQnQ0rmZfZnu8MxVypTCsvOD0k4FfvaVZ%2F4y3BbKvqWr6zNUjx%2FDOiqxFJtxhvPfDZHW15U0rl%2BAqgoNMHGG1p%2FZLGqwu9Lhrv8B2%2FG%2BOX%2Bg0wnNtQGY%2Fr%2By5sAx7SIKY8HZIKABnmxPPmRCEepfjNG%2FxkzgpDgJTzsN%2Bl2x%2FkSWVFbi20bVWszOn%2BRK2M5BCx9vLK6YsxlbQJrmD5ELpzryRL5uh0JwbeP354FpF603az9FFd0%2FVHRPpYxXG60%2FMYJGiZf9atN7IiFuXwUtJ38jvHx7zyMToyg%2BiQ6V7BNBe663hzNJPTEB%2BObghVYAsAFDe37TWg0pBV0uM0tOZWVyKzquvrw7LPkiF7VrUFQ1qbYa0EFcQ1Lbc6AZEZiNgNKcP289C%2B6rXEbVNHsKmXfQruFMZ3ML6WGwCVuYv6hj7UUYjTy4TPgVa9UxxliYirWqyDs%2F5YkPIS8t8wgb5jCz2pG1BjqmAb%2BFgTAGqCk6D0E%2FV5ihAi3xTSDmxL8rrT%2BYE60eA0EHE09Z6HRmGD0t3ZJmajP517o%2FpPpzlj60AD66omvV0HwiY2qpj9KXwBaDbV%2Bnerc%2Fgyks9ybTnsrcPUcPyHnthvQFbTldOk1LR9x9fLfK2Xf%2FuxJnssaPt7z7vXUZMWvqtEw90TsFFe62ZOG6Kfg56DlM1R7KIfpJSltaqBBACO%2FPa9fNp80%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIAZCJ2QHLFWNYC4QUJ%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=add657c325b141207845ef0bfd02f24a07ab95fba20bd6eeb101d0df2c1e6d90)
設問のビュー1はビューの定義に式によって定義された列が含まれています。データの削除を行うことができます。
ビュー2はビューの定義にGROUP BY句とグループ関数が使用されているため、データの追加、更新、削除の全てを行うことができません。
ビュー3はNOT NULL制約が定義されているEMPLOYEE_ID列がビューの定義に含まれていません。データの更新を行うことができます。
以上より、
・ビュー1を通じて、EMPLOYEES表のデータの削除ができる
・ビュー3を通じて、EMPLOYEES表のデータの更新ができる
が正解となります。
設問のSQL文の実行結果は次のようになります。
[ビュー1]
![【図を表示】](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/20226/k49179.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T042330Z&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEAwaDmFwLW5vcnRoZWFzdC0xIkcwRQIgf%2BzqFCJvQHCwpd9KQZfj%2BwFh942kafzm2pIFRXqTnBcCIQD6uWqxPnXhfOn5TT2GIUn1NBXuFXkZUvGzBJ7Q0MbwWiqxBAjl%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F8BEAAaDDYyMzQyOTgyNzI3NSIMCCMAJ4fQ0Jp2ziMkKoUEcwshKVX9srmpq3jK9XA%2Bpd1SWtbBzE2N7%2FcmnYCMEacy73KsIVR7AwQGHhXNF2Nc%2BCaq7wFnPq8c8IisX5MV7l3gdpLh5zO1q%2BBn%2BVhCjE9ucBNWtK3%2F3yEhpLoxJ6ZaxIEyXnhmv50ZAQbcKuux2lWZRaPe%2BuOXufwt08wGH99lYbSXcQb45Zbd373%2BFGLJsTDuv%2BObTnm%2FLyNS0kG%2BJEOqrcrdAQnQ0rmZfZnu8MxVypTCsvOD0k4FfvaVZ%2F4y3BbKvqWr6zNUjx%2FDOiqxFJtxhvPfDZHW15U0rl%2BAqgoNMHGG1p%2FZLGqwu9Lhrv8B2%2FG%2BOX%2Bg0wnNtQGY%2Fr%2By5sAx7SIKY8HZIKABnmxPPmRCEepfjNG%2FxkzgpDgJTzsN%2Bl2x%2FkSWVFbi20bVWszOn%2BRK2M5BCx9vLK6YsxlbQJrmD5ELpzryRL5uh0JwbeP354FpF603az9FFd0%2FVHRPpYxXG60%2FMYJGiZf9atN7IiFuXwUtJ38jvHx7zyMToyg%2BiQ6V7BNBe663hzNJPTEB%2BObghVYAsAFDe37TWg0pBV0uM0tOZWVyKzquvrw7LPkiF7VrUFQ1qbYa0EFcQ1Lbc6AZEZiNgNKcP289C%2B6rXEbVNHsKmXfQruFMZ3ML6WGwCVuYv6hj7UUYjTy4TPgVa9UxxliYirWqyDs%2F5YkPIS8t8wgb5jCz2pG1BjqmAb%2BFgTAGqCk6D0E%2FV5ihAi3xTSDmxL8rrT%2BYE60eA0EHE09Z6HRmGD0t3ZJmajP517o%2FpPpzlj60AD66omvV0HwiY2qpj9KXwBaDbV%2Bnerc%2Fgyks9ybTnsrcPUcPyHnthvQFbTldOk1LR9x9fLfK2Xf%2FuxJnssaPt7z7vXUZMWvqtEw90TsFFe62ZOG6Kfg56DlM1R7KIfpJSltaqBBACO%2FPa9fNp80%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIAZCJ2QHLFWNYC4QUJ%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=524a6b8e87e395078c53535a39c60f410023de53a0579b28166f36dd891ad90b)
SQLを表示
CREATE OR REPLACE VIEW v_emp1
AS
SELECT employee_id, employee_name, salary * 12 sal
FROM employees;
INSERT INTO v_emp1
VALUES (2000, 'Tanaka', 500000);
UPDATE v_emp1 SET sal = 500000;
DELETE v_emp1 WHERE sal > 5000000;
AS
SELECT employee_id, employee_name, salary * 12 sal
FROM employees;
INSERT INTO v_emp1
VALUES (2000, 'Tanaka', 500000);
UPDATE v_emp1 SET sal = 500000;
DELETE v_emp1 WHERE sal > 5000000;
[ビュー2]
![【図を表示2】](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/20227/kk49179.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T042330Z&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEAwaDmFwLW5vcnRoZWFzdC0xIkcwRQIgf%2BzqFCJvQHCwpd9KQZfj%2BwFh942kafzm2pIFRXqTnBcCIQD6uWqxPnXhfOn5TT2GIUn1NBXuFXkZUvGzBJ7Q0MbwWiqxBAjl%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F8BEAAaDDYyMzQyOTgyNzI3NSIMCCMAJ4fQ0Jp2ziMkKoUEcwshKVX9srmpq3jK9XA%2Bpd1SWtbBzE2N7%2FcmnYCMEacy73KsIVR7AwQGHhXNF2Nc%2BCaq7wFnPq8c8IisX5MV7l3gdpLh5zO1q%2BBn%2BVhCjE9ucBNWtK3%2F3yEhpLoxJ6ZaxIEyXnhmv50ZAQbcKuux2lWZRaPe%2BuOXufwt08wGH99lYbSXcQb45Zbd373%2BFGLJsTDuv%2BObTnm%2FLyNS0kG%2BJEOqrcrdAQnQ0rmZfZnu8MxVypTCsvOD0k4FfvaVZ%2F4y3BbKvqWr6zNUjx%2FDOiqxFJtxhvPfDZHW15U0rl%2BAqgoNMHGG1p%2FZLGqwu9Lhrv8B2%2FG%2BOX%2Bg0wnNtQGY%2Fr%2By5sAx7SIKY8HZIKABnmxPPmRCEepfjNG%2FxkzgpDgJTzsN%2Bl2x%2FkSWVFbi20bVWszOn%2BRK2M5BCx9vLK6YsxlbQJrmD5ELpzryRL5uh0JwbeP354FpF603az9FFd0%2FVHRPpYxXG60%2FMYJGiZf9atN7IiFuXwUtJ38jvHx7zyMToyg%2BiQ6V7BNBe663hzNJPTEB%2BObghVYAsAFDe37TWg0pBV0uM0tOZWVyKzquvrw7LPkiF7VrUFQ1qbYa0EFcQ1Lbc6AZEZiNgNKcP289C%2B6rXEbVNHsKmXfQruFMZ3ML6WGwCVuYv6hj7UUYjTy4TPgVa9UxxliYirWqyDs%2F5YkPIS8t8wgb5jCz2pG1BjqmAb%2BFgTAGqCk6D0E%2FV5ihAi3xTSDmxL8rrT%2BYE60eA0EHE09Z6HRmGD0t3ZJmajP517o%2FpPpzlj60AD66omvV0HwiY2qpj9KXwBaDbV%2Bnerc%2Fgyks9ybTnsrcPUcPyHnthvQFbTldOk1LR9x9fLfK2Xf%2FuxJnssaPt7z7vXUZMWvqtEw90TsFFe62ZOG6Kfg56DlM1R7KIfpJSltaqBBACO%2FPa9fNp80%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIAZCJ2QHLFWNYC4QUJ%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=2e193716fa00419f1a05fd159284ef2fedcc3fc89839da91e760915898a4e29b)
SQLを表示
CREATE VIEW v_emp2
AS
SELECT department_id, AVG(salary) avgsal
FROM employees
WHERE salary > 600000
GROUP BY department_id;
INSERT INTO v_emp2
VALUES (6, 500000);
UPDATE v_emp2 SET department_id = 6;
DELETE v_emp2 WHERE department_id = 5;
AS
SELECT department_id, AVG(salary) avgsal
FROM employees
WHERE salary > 600000
GROUP BY department_id;
INSERT INTO v_emp2
VALUES (6, 500000);
UPDATE v_emp2 SET department_id = 6;
DELETE v_emp2 WHERE department_id = 5;
[ビュー3]
![【図を表示3】](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/20228/kkk49179.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T042330Z&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEAwaDmFwLW5vcnRoZWFzdC0xIkcwRQIgf%2BzqFCJvQHCwpd9KQZfj%2BwFh942kafzm2pIFRXqTnBcCIQD6uWqxPnXhfOn5TT2GIUn1NBXuFXkZUvGzBJ7Q0MbwWiqxBAjl%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F8BEAAaDDYyMzQyOTgyNzI3NSIMCCMAJ4fQ0Jp2ziMkKoUEcwshKVX9srmpq3jK9XA%2Bpd1SWtbBzE2N7%2FcmnYCMEacy73KsIVR7AwQGHhXNF2Nc%2BCaq7wFnPq8c8IisX5MV7l3gdpLh5zO1q%2BBn%2BVhCjE9ucBNWtK3%2F3yEhpLoxJ6ZaxIEyXnhmv50ZAQbcKuux2lWZRaPe%2BuOXufwt08wGH99lYbSXcQb45Zbd373%2BFGLJsTDuv%2BObTnm%2FLyNS0kG%2BJEOqrcrdAQnQ0rmZfZnu8MxVypTCsvOD0k4FfvaVZ%2F4y3BbKvqWr6zNUjx%2FDOiqxFJtxhvPfDZHW15U0rl%2BAqgoNMHGG1p%2FZLGqwu9Lhrv8B2%2FG%2BOX%2Bg0wnNtQGY%2Fr%2By5sAx7SIKY8HZIKABnmxPPmRCEepfjNG%2FxkzgpDgJTzsN%2Bl2x%2FkSWVFbi20bVWszOn%2BRK2M5BCx9vLK6YsxlbQJrmD5ELpzryRL5uh0JwbeP354FpF603az9FFd0%2FVHRPpYxXG60%2FMYJGiZf9atN7IiFuXwUtJ38jvHx7zyMToyg%2BiQ6V7BNBe663hzNJPTEB%2BObghVYAsAFDe37TWg0pBV0uM0tOZWVyKzquvrw7LPkiF7VrUFQ1qbYa0EFcQ1Lbc6AZEZiNgNKcP289C%2B6rXEbVNHsKmXfQruFMZ3ML6WGwCVuYv6hj7UUYjTy4TPgVa9UxxliYirWqyDs%2F5YkPIS8t8wgb5jCz2pG1BjqmAb%2BFgTAGqCk6D0E%2FV5ihAi3xTSDmxL8rrT%2BYE60eA0EHE09Z6HRmGD0t3ZJmajP517o%2FpPpzlj60AD66omvV0HwiY2qpj9KXwBaDbV%2Bnerc%2Fgyks9ybTnsrcPUcPyHnthvQFbTldOk1LR9x9fLfK2Xf%2FuxJnssaPt7z7vXUZMWvqtEw90TsFFe62ZOG6Kfg56DlM1R7KIfpJSltaqBBACO%2FPa9fNp80%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIAZCJ2QHLFWNYC4QUJ%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=64af47e65b47fa0f81548367321530e4bddfbd4260dff7502ff65fbe1b64bff5)
SQLを表示
CREATE VIEW v_emp3
AS
SELECT employee_name, salary
FROM employees;
INSERT INTO v_emp3
VALUES ('Tanaka', 400000);
UPDATE v_emp3 SET salary = 250000;
DELETE v_emp3 WHERE salary = 250000;
AS
SELECT employee_name, salary
FROM employees;
INSERT INTO v_emp3
VALUES ('Tanaka', 400000);
UPDATE v_emp3 SET salary = 250000;
DELETE v_emp3 WHERE salary = 250000;
参考
作成したビューを通じて実表のデータを操作することができます。
ただし、表に直接アクセスする場合とは異なり、ビューを通じての実表のデータの操作には、ビューの定義によっていろいろな制限があります。
ビューを通じて実表のデータを操作することができる基本的なルールは次の通りです。
![【図を表示】](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/19851/k49146.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T042330Z&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEAwaDmFwLW5vcnRoZWFzdC0xIkcwRQIgf%2BzqFCJvQHCwpd9KQZfj%2BwFh942kafzm2pIFRXqTnBcCIQD6uWqxPnXhfOn5TT2GIUn1NBXuFXkZUvGzBJ7Q0MbwWiqxBAjl%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F8BEAAaDDYyMzQyOTgyNzI3NSIMCCMAJ4fQ0Jp2ziMkKoUEcwshKVX9srmpq3jK9XA%2Bpd1SWtbBzE2N7%2FcmnYCMEacy73KsIVR7AwQGHhXNF2Nc%2BCaq7wFnPq8c8IisX5MV7l3gdpLh5zO1q%2BBn%2BVhCjE9ucBNWtK3%2F3yEhpLoxJ6ZaxIEyXnhmv50ZAQbcKuux2lWZRaPe%2BuOXufwt08wGH99lYbSXcQb45Zbd373%2BFGLJsTDuv%2BObTnm%2FLyNS0kG%2BJEOqrcrdAQnQ0rmZfZnu8MxVypTCsvOD0k4FfvaVZ%2F4y3BbKvqWr6zNUjx%2FDOiqxFJtxhvPfDZHW15U0rl%2BAqgoNMHGG1p%2FZLGqwu9Lhrv8B2%2FG%2BOX%2Bg0wnNtQGY%2Fr%2By5sAx7SIKY8HZIKABnmxPPmRCEepfjNG%2FxkzgpDgJTzsN%2Bl2x%2FkSWVFbi20bVWszOn%2BRK2M5BCx9vLK6YsxlbQJrmD5ELpzryRL5uh0JwbeP354FpF603az9FFd0%2FVHRPpYxXG60%2FMYJGiZf9atN7IiFuXwUtJ38jvHx7zyMToyg%2BiQ6V7BNBe663hzNJPTEB%2BObghVYAsAFDe37TWg0pBV0uM0tOZWVyKzquvrw7LPkiF7VrUFQ1qbYa0EFcQ1Lbc6AZEZiNgNKcP289C%2B6rXEbVNHsKmXfQruFMZ3ML6WGwCVuYv6hj7UUYjTy4TPgVa9UxxliYirWqyDs%2F5YkPIS8t8wgb5jCz2pG1BjqmAb%2BFgTAGqCk6D0E%2FV5ihAi3xTSDmxL8rrT%2BYE60eA0EHE09Z6HRmGD0t3ZJmajP517o%2FpPpzlj60AD66omvV0HwiY2qpj9KXwBaDbV%2Bnerc%2Fgyks9ybTnsrcPUcPyHnthvQFbTldOk1LR9x9fLfK2Xf%2FuxJnssaPt7z7vXUZMWvqtEw90TsFFe62ZOG6Kfg56DlM1R7KIfpJSltaqBBACO%2FPa9fNp80%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIAZCJ2QHLFWNYC4QUJ%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=add657c325b141207845ef0bfd02f24a07ab95fba20bd6eeb101d0df2c1e6d90)
[ビューを通じてのデータの追加]
ビューを通じて実表のデータを追加する場合には、ビューの定義に次の要素が含まれていないことが条件になります。
・GROUP BY句
・グループ関数
・ROWNUM擬似列
・DISTINCTキーワード
・式によって定義された列 ※
・ビューに含まれない実表の列に定義されたNOT NULL制約 ※
![<img src="/mondai3/img/jpg/k49148.jpg">](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/19852/k49148.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T042330Z&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEAwaDmFwLW5vcnRoZWFzdC0xIkcwRQIgf%2BzqFCJvQHCwpd9KQZfj%2BwFh942kafzm2pIFRXqTnBcCIQD6uWqxPnXhfOn5TT2GIUn1NBXuFXkZUvGzBJ7Q0MbwWiqxBAjl%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F8BEAAaDDYyMzQyOTgyNzI3NSIMCCMAJ4fQ0Jp2ziMkKoUEcwshKVX9srmpq3jK9XA%2Bpd1SWtbBzE2N7%2FcmnYCMEacy73KsIVR7AwQGHhXNF2Nc%2BCaq7wFnPq8c8IisX5MV7l3gdpLh5zO1q%2BBn%2BVhCjE9ucBNWtK3%2F3yEhpLoxJ6ZaxIEyXnhmv50ZAQbcKuux2lWZRaPe%2BuOXufwt08wGH99lYbSXcQb45Zbd373%2BFGLJsTDuv%2BObTnm%2FLyNS0kG%2BJEOqrcrdAQnQ0rmZfZnu8MxVypTCsvOD0k4FfvaVZ%2F4y3BbKvqWr6zNUjx%2FDOiqxFJtxhvPfDZHW15U0rl%2BAqgoNMHGG1p%2FZLGqwu9Lhrv8B2%2FG%2BOX%2Bg0wnNtQGY%2Fr%2By5sAx7SIKY8HZIKABnmxPPmRCEepfjNG%2FxkzgpDgJTzsN%2Bl2x%2FkSWVFbi20bVWszOn%2BRK2M5BCx9vLK6YsxlbQJrmD5ELpzryRL5uh0JwbeP354FpF603az9FFd0%2FVHRPpYxXG60%2FMYJGiZf9atN7IiFuXwUtJ38jvHx7zyMToyg%2BiQ6V7BNBe663hzNJPTEB%2BObghVYAsAFDe37TWg0pBV0uM0tOZWVyKzquvrw7LPkiF7VrUFQ1qbYa0EFcQ1Lbc6AZEZiNgNKcP289C%2B6rXEbVNHsKmXfQruFMZ3ML6WGwCVuYv6hj7UUYjTy4TPgVa9UxxliYirWqyDs%2F5YkPIS8t8wgb5jCz2pG1BjqmAb%2BFgTAGqCk6D0E%2FV5ihAi3xTSDmxL8rrT%2BYE60eA0EHE09Z6HRmGD0t3ZJmajP517o%2FpPpzlj60AD66omvV0HwiY2qpj9KXwBaDbV%2Bnerc%2Fgyks9ybTnsrcPUcPyHnthvQFbTldOk1LR9x9fLfK2Xf%2FuxJnssaPt7z7vXUZMWvqtEw90TsFFe62ZOG6Kfg56DlM1R7KIfpJSltaqBBACO%2FPa9fNp80%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIAZCJ2QHLFWNYC4QUJ%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=e0f3eb465082a87b0f5185bc0d0cb7219150f8a6a9cd6c0cab4492d360fe3cd7)
![<img src="/mondai3/img/jpg/kk49148.jpg">](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/19853/kk49148.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T042330Z&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEAwaDmFwLW5vcnRoZWFzdC0xIkcwRQIgf%2BzqFCJvQHCwpd9KQZfj%2BwFh942kafzm2pIFRXqTnBcCIQD6uWqxPnXhfOn5TT2GIUn1NBXuFXkZUvGzBJ7Q0MbwWiqxBAjl%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F8BEAAaDDYyMzQyOTgyNzI3NSIMCCMAJ4fQ0Jp2ziMkKoUEcwshKVX9srmpq3jK9XA%2Bpd1SWtbBzE2N7%2FcmnYCMEacy73KsIVR7AwQGHhXNF2Nc%2BCaq7wFnPq8c8IisX5MV7l3gdpLh5zO1q%2BBn%2BVhCjE9ucBNWtK3%2F3yEhpLoxJ6ZaxIEyXnhmv50ZAQbcKuux2lWZRaPe%2BuOXufwt08wGH99lYbSXcQb45Zbd373%2BFGLJsTDuv%2BObTnm%2FLyNS0kG%2BJEOqrcrdAQnQ0rmZfZnu8MxVypTCsvOD0k4FfvaVZ%2F4y3BbKvqWr6zNUjx%2FDOiqxFJtxhvPfDZHW15U0rl%2BAqgoNMHGG1p%2FZLGqwu9Lhrv8B2%2FG%2BOX%2Bg0wnNtQGY%2Fr%2By5sAx7SIKY8HZIKABnmxPPmRCEepfjNG%2FxkzgpDgJTzsN%2Bl2x%2FkSWVFbi20bVWszOn%2BRK2M5BCx9vLK6YsxlbQJrmD5ELpzryRL5uh0JwbeP354FpF603az9FFd0%2FVHRPpYxXG60%2FMYJGiZf9atN7IiFuXwUtJ38jvHx7zyMToyg%2BiQ6V7BNBe663hzNJPTEB%2BObghVYAsAFDe37TWg0pBV0uM0tOZWVyKzquvrw7LPkiF7VrUFQ1qbYa0EFcQ1Lbc6AZEZiNgNKcP289C%2B6rXEbVNHsKmXfQruFMZ3ML6WGwCVuYv6hj7UUYjTy4TPgVa9UxxliYirWqyDs%2F5YkPIS8t8wgb5jCz2pG1BjqmAb%2BFgTAGqCk6D0E%2FV5ihAi3xTSDmxL8rrT%2BYE60eA0EHE09Z6HRmGD0t3ZJmajP517o%2FpPpzlj60AD66omvV0HwiY2qpj9KXwBaDbV%2Bnerc%2Fgyks9ybTnsrcPUcPyHnthvQFbTldOk1LR9x9fLfK2Xf%2FuxJnssaPt7z7vXUZMWvqtEw90TsFFe62ZOG6Kfg56DlM1R7KIfpJSltaqBBACO%2FPa9fNp80%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIAZCJ2QHLFWNYC4QUJ%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=9b6866ce97d7aa0812accf0a6b5d68dfd3a22436952a7844f8b2705106c744df)
ただし、NOT NULL制約が定義されている列に、デフォルト値が設定されていれば、ビューを通じてデータを追加することができます。
![<img src="/mondai3/img/jpg/kk49239.jpg">](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/19854/kk49239.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T042330Z&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEAwaDmFwLW5vcnRoZWFzdC0xIkcwRQIgf%2BzqFCJvQHCwpd9KQZfj%2BwFh942kafzm2pIFRXqTnBcCIQD6uWqxPnXhfOn5TT2GIUn1NBXuFXkZUvGzBJ7Q0MbwWiqxBAjl%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F8BEAAaDDYyMzQyOTgyNzI3NSIMCCMAJ4fQ0Jp2ziMkKoUEcwshKVX9srmpq3jK9XA%2Bpd1SWtbBzE2N7%2FcmnYCMEacy73KsIVR7AwQGHhXNF2Nc%2BCaq7wFnPq8c8IisX5MV7l3gdpLh5zO1q%2BBn%2BVhCjE9ucBNWtK3%2F3yEhpLoxJ6ZaxIEyXnhmv50ZAQbcKuux2lWZRaPe%2BuOXufwt08wGH99lYbSXcQb45Zbd373%2BFGLJsTDuv%2BObTnm%2FLyNS0kG%2BJEOqrcrdAQnQ0rmZfZnu8MxVypTCsvOD0k4FfvaVZ%2F4y3BbKvqWr6zNUjx%2FDOiqxFJtxhvPfDZHW15U0rl%2BAqgoNMHGG1p%2FZLGqwu9Lhrv8B2%2FG%2BOX%2Bg0wnNtQGY%2Fr%2By5sAx7SIKY8HZIKABnmxPPmRCEepfjNG%2FxkzgpDgJTzsN%2Bl2x%2FkSWVFbi20bVWszOn%2BRK2M5BCx9vLK6YsxlbQJrmD5ELpzryRL5uh0JwbeP354FpF603az9FFd0%2FVHRPpYxXG60%2FMYJGiZf9atN7IiFuXwUtJ38jvHx7zyMToyg%2BiQ6V7BNBe663hzNJPTEB%2BObghVYAsAFDe37TWg0pBV0uM0tOZWVyKzquvrw7LPkiF7VrUFQ1qbYa0EFcQ1Lbc6AZEZiNgNKcP289C%2B6rXEbVNHsKmXfQruFMZ3ML6WGwCVuYv6hj7UUYjTy4TPgVa9UxxliYirWqyDs%2F5YkPIS8t8wgb5jCz2pG1BjqmAb%2BFgTAGqCk6D0E%2FV5ihAi3xTSDmxL8rrT%2BYE60eA0EHE09Z6HRmGD0t3ZJmajP517o%2FpPpzlj60AD66omvV0HwiY2qpj9KXwBaDbV%2Bnerc%2Fgyks9ybTnsrcPUcPyHnthvQFbTldOk1LR9x9fLfK2Xf%2FuxJnssaPt7z7vXUZMWvqtEw90TsFFe62ZOG6Kfg56DlM1R7KIfpJSltaqBBACO%2FPa9fNp80%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIAZCJ2QHLFWNYC4QUJ%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=6cb79998312fb5db08dea6ae4dc5eee41b9f7b0e207790cc4081ea38c4a1d24d)
[ビューを通じてのデータの更新]
ビューを通じて実表のデータを更新する場合には、ビューの定義に次の要素が含まれていないことが条件になります。
・GROUP BY句
・グループ関数
・ROWNUM擬似列
・DISTINCTキーワード
・式によって定義された列 ※
![<img src="/mondai3/img/jpg/k49147.jpg">](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/19855/k49147.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T042330Z&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEAwaDmFwLW5vcnRoZWFzdC0xIkcwRQIgf%2BzqFCJvQHCwpd9KQZfj%2BwFh942kafzm2pIFRXqTnBcCIQD6uWqxPnXhfOn5TT2GIUn1NBXuFXkZUvGzBJ7Q0MbwWiqxBAjl%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F8BEAAaDDYyMzQyOTgyNzI3NSIMCCMAJ4fQ0Jp2ziMkKoUEcwshKVX9srmpq3jK9XA%2Bpd1SWtbBzE2N7%2FcmnYCMEacy73KsIVR7AwQGHhXNF2Nc%2BCaq7wFnPq8c8IisX5MV7l3gdpLh5zO1q%2BBn%2BVhCjE9ucBNWtK3%2F3yEhpLoxJ6ZaxIEyXnhmv50ZAQbcKuux2lWZRaPe%2BuOXufwt08wGH99lYbSXcQb45Zbd373%2BFGLJsTDuv%2BObTnm%2FLyNS0kG%2BJEOqrcrdAQnQ0rmZfZnu8MxVypTCsvOD0k4FfvaVZ%2F4y3BbKvqWr6zNUjx%2FDOiqxFJtxhvPfDZHW15U0rl%2BAqgoNMHGG1p%2FZLGqwu9Lhrv8B2%2FG%2BOX%2Bg0wnNtQGY%2Fr%2By5sAx7SIKY8HZIKABnmxPPmRCEepfjNG%2FxkzgpDgJTzsN%2Bl2x%2FkSWVFbi20bVWszOn%2BRK2M5BCx9vLK6YsxlbQJrmD5ELpzryRL5uh0JwbeP354FpF603az9FFd0%2FVHRPpYxXG60%2FMYJGiZf9atN7IiFuXwUtJ38jvHx7zyMToyg%2BiQ6V7BNBe663hzNJPTEB%2BObghVYAsAFDe37TWg0pBV0uM0tOZWVyKzquvrw7LPkiF7VrUFQ1qbYa0EFcQ1Lbc6AZEZiNgNKcP289C%2B6rXEbVNHsKmXfQruFMZ3ML6WGwCVuYv6hj7UUYjTy4TPgVa9UxxliYirWqyDs%2F5YkPIS8t8wgb5jCz2pG1BjqmAb%2BFgTAGqCk6D0E%2FV5ihAi3xTSDmxL8rrT%2BYE60eA0EHE09Z6HRmGD0t3ZJmajP517o%2FpPpzlj60AD66omvV0HwiY2qpj9KXwBaDbV%2Bnerc%2Fgyks9ybTnsrcPUcPyHnthvQFbTldOk1LR9x9fLfK2Xf%2FuxJnssaPt7z7vXUZMWvqtEw90TsFFe62ZOG6Kfg56DlM1R7KIfpJSltaqBBACO%2FPa9fNp80%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIAZCJ2QHLFWNYC4QUJ%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=edeb97af10361b40e9f679eecbeb0bad2ce3fd658df9d5eb0960d520a73b76cc)
![<img src="/mondai3/img/jpg/kk49147.jpg">](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/19856/kk49147.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T042330Z&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEAwaDmFwLW5vcnRoZWFzdC0xIkcwRQIgf%2BzqFCJvQHCwpd9KQZfj%2BwFh942kafzm2pIFRXqTnBcCIQD6uWqxPnXhfOn5TT2GIUn1NBXuFXkZUvGzBJ7Q0MbwWiqxBAjl%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F8BEAAaDDYyMzQyOTgyNzI3NSIMCCMAJ4fQ0Jp2ziMkKoUEcwshKVX9srmpq3jK9XA%2Bpd1SWtbBzE2N7%2FcmnYCMEacy73KsIVR7AwQGHhXNF2Nc%2BCaq7wFnPq8c8IisX5MV7l3gdpLh5zO1q%2BBn%2BVhCjE9ucBNWtK3%2F3yEhpLoxJ6ZaxIEyXnhmv50ZAQbcKuux2lWZRaPe%2BuOXufwt08wGH99lYbSXcQb45Zbd373%2BFGLJsTDuv%2BObTnm%2FLyNS0kG%2BJEOqrcrdAQnQ0rmZfZnu8MxVypTCsvOD0k4FfvaVZ%2F4y3BbKvqWr6zNUjx%2FDOiqxFJtxhvPfDZHW15U0rl%2BAqgoNMHGG1p%2FZLGqwu9Lhrv8B2%2FG%2BOX%2Bg0wnNtQGY%2Fr%2By5sAx7SIKY8HZIKABnmxPPmRCEepfjNG%2FxkzgpDgJTzsN%2Bl2x%2FkSWVFbi20bVWszOn%2BRK2M5BCx9vLK6YsxlbQJrmD5ELpzryRL5uh0JwbeP354FpF603az9FFd0%2FVHRPpYxXG60%2FMYJGiZf9atN7IiFuXwUtJ38jvHx7zyMToyg%2BiQ6V7BNBe663hzNJPTEB%2BObghVYAsAFDe37TWg0pBV0uM0tOZWVyKzquvrw7LPkiF7VrUFQ1qbYa0EFcQ1Lbc6AZEZiNgNKcP289C%2B6rXEbVNHsKmXfQruFMZ3ML6WGwCVuYv6hj7UUYjTy4TPgVa9UxxliYirWqyDs%2F5YkPIS8t8wgb5jCz2pG1BjqmAb%2BFgTAGqCk6D0E%2FV5ihAi3xTSDmxL8rrT%2BYE60eA0EHE09Z6HRmGD0t3ZJmajP517o%2FpPpzlj60AD66omvV0HwiY2qpj9KXwBaDbV%2Bnerc%2Fgyks9ybTnsrcPUcPyHnthvQFbTldOk1LR9x9fLfK2Xf%2FuxJnssaPt7z7vXUZMWvqtEw90TsFFe62ZOG6Kfg56DlM1R7KIfpJSltaqBBACO%2FPa9fNp80%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIAZCJ2QHLFWNYC4QUJ%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=1120fd6a17bb21d271372743f7de8aed32c89bf3ff610bc7b25958808b27580a)
ただし、ビューの定義に式を含む列が定義されている場合でも、対象列以外はデータの追加、更新を行うことができます。
![<img src="/mondai3/img/jpg/k49240.jpg">](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/19857/k49240.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T042330Z&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEAwaDmFwLW5vcnRoZWFzdC0xIkcwRQIgf%2BzqFCJvQHCwpd9KQZfj%2BwFh942kafzm2pIFRXqTnBcCIQD6uWqxPnXhfOn5TT2GIUn1NBXuFXkZUvGzBJ7Q0MbwWiqxBAjl%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F8BEAAaDDYyMzQyOTgyNzI3NSIMCCMAJ4fQ0Jp2ziMkKoUEcwshKVX9srmpq3jK9XA%2Bpd1SWtbBzE2N7%2FcmnYCMEacy73KsIVR7AwQGHhXNF2Nc%2BCaq7wFnPq8c8IisX5MV7l3gdpLh5zO1q%2BBn%2BVhCjE9ucBNWtK3%2F3yEhpLoxJ6ZaxIEyXnhmv50ZAQbcKuux2lWZRaPe%2BuOXufwt08wGH99lYbSXcQb45Zbd373%2BFGLJsTDuv%2BObTnm%2FLyNS0kG%2BJEOqrcrdAQnQ0rmZfZnu8MxVypTCsvOD0k4FfvaVZ%2F4y3BbKvqWr6zNUjx%2FDOiqxFJtxhvPfDZHW15U0rl%2BAqgoNMHGG1p%2FZLGqwu9Lhrv8B2%2FG%2BOX%2Bg0wnNtQGY%2Fr%2By5sAx7SIKY8HZIKABnmxPPmRCEepfjNG%2FxkzgpDgJTzsN%2Bl2x%2FkSWVFbi20bVWszOn%2BRK2M5BCx9vLK6YsxlbQJrmD5ELpzryRL5uh0JwbeP354FpF603az9FFd0%2FVHRPpYxXG60%2FMYJGiZf9atN7IiFuXwUtJ38jvHx7zyMToyg%2BiQ6V7BNBe663hzNJPTEB%2BObghVYAsAFDe37TWg0pBV0uM0tOZWVyKzquvrw7LPkiF7VrUFQ1qbYa0EFcQ1Lbc6AZEZiNgNKcP289C%2B6rXEbVNHsKmXfQruFMZ3ML6WGwCVuYv6hj7UUYjTy4TPgVa9UxxliYirWqyDs%2F5YkPIS8t8wgb5jCz2pG1BjqmAb%2BFgTAGqCk6D0E%2FV5ihAi3xTSDmxL8rrT%2BYE60eA0EHE09Z6HRmGD0t3ZJmajP517o%2FpPpzlj60AD66omvV0HwiY2qpj9KXwBaDbV%2Bnerc%2Fgyks9ybTnsrcPUcPyHnthvQFbTldOk1LR9x9fLfK2Xf%2FuxJnssaPt7z7vXUZMWvqtEw90TsFFe62ZOG6Kfg56DlM1R7KIfpJSltaqBBACO%2FPa9fNp80%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIAZCJ2QHLFWNYC4QUJ%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=436a827e603a68a122f2e8df69c4f9538e67612ef9d696166eccd10f4048b501)
[ビューを通じてのデータの削除]
ビューを通じて実表のデータを削除する場合には、ビューの定義に次の要素が含まれていないことが条件になります。
・GROUP BY句
・グループ関数
・ROWNUM擬似列
・DISTINCTキーワード
![【図を表示2】](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/19858/kk49146.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T042330Z&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEAwaDmFwLW5vcnRoZWFzdC0xIkcwRQIgf%2BzqFCJvQHCwpd9KQZfj%2BwFh942kafzm2pIFRXqTnBcCIQD6uWqxPnXhfOn5TT2GIUn1NBXuFXkZUvGzBJ7Q0MbwWiqxBAjl%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F8BEAAaDDYyMzQyOTgyNzI3NSIMCCMAJ4fQ0Jp2ziMkKoUEcwshKVX9srmpq3jK9XA%2Bpd1SWtbBzE2N7%2FcmnYCMEacy73KsIVR7AwQGHhXNF2Nc%2BCaq7wFnPq8c8IisX5MV7l3gdpLh5zO1q%2BBn%2BVhCjE9ucBNWtK3%2F3yEhpLoxJ6ZaxIEyXnhmv50ZAQbcKuux2lWZRaPe%2BuOXufwt08wGH99lYbSXcQb45Zbd373%2BFGLJsTDuv%2BObTnm%2FLyNS0kG%2BJEOqrcrdAQnQ0rmZfZnu8MxVypTCsvOD0k4FfvaVZ%2F4y3BbKvqWr6zNUjx%2FDOiqxFJtxhvPfDZHW15U0rl%2BAqgoNMHGG1p%2FZLGqwu9Lhrv8B2%2FG%2BOX%2Bg0wnNtQGY%2Fr%2By5sAx7SIKY8HZIKABnmxPPmRCEepfjNG%2FxkzgpDgJTzsN%2Bl2x%2FkSWVFbi20bVWszOn%2BRK2M5BCx9vLK6YsxlbQJrmD5ELpzryRL5uh0JwbeP354FpF603az9FFd0%2FVHRPpYxXG60%2FMYJGiZf9atN7IiFuXwUtJ38jvHx7zyMToyg%2BiQ6V7BNBe663hzNJPTEB%2BObghVYAsAFDe37TWg0pBV0uM0tOZWVyKzquvrw7LPkiF7VrUFQ1qbYa0EFcQ1Lbc6AZEZiNgNKcP289C%2B6rXEbVNHsKmXfQruFMZ3ML6WGwCVuYv6hj7UUYjTy4TPgVa9UxxliYirWqyDs%2F5YkPIS8t8wgb5jCz2pG1BjqmAb%2BFgTAGqCk6D0E%2FV5ihAi3xTSDmxL8rrT%2BYE60eA0EHE09Z6HRmGD0t3ZJmajP517o%2FpPpzlj60AD66omvV0HwiY2qpj9KXwBaDbV%2Bnerc%2Fgyks9ybTnsrcPUcPyHnthvQFbTldOk1LR9x9fLfK2Xf%2FuxJnssaPt7z7vXUZMWvqtEw90TsFFe62ZOG6Kfg56DlM1R7KIfpJSltaqBBACO%2FPa9fNp80%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIAZCJ2QHLFWNYC4QUJ%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=7ccc48665852949601ab68a18cf66a62a6be390faac478d152b8b26384ef3f8e)
![【図を表示3】](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/19859/kkk49146.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T042330Z&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEAwaDmFwLW5vcnRoZWFzdC0xIkcwRQIgf%2BzqFCJvQHCwpd9KQZfj%2BwFh942kafzm2pIFRXqTnBcCIQD6uWqxPnXhfOn5TT2GIUn1NBXuFXkZUvGzBJ7Q0MbwWiqxBAjl%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F8BEAAaDDYyMzQyOTgyNzI3NSIMCCMAJ4fQ0Jp2ziMkKoUEcwshKVX9srmpq3jK9XA%2Bpd1SWtbBzE2N7%2FcmnYCMEacy73KsIVR7AwQGHhXNF2Nc%2BCaq7wFnPq8c8IisX5MV7l3gdpLh5zO1q%2BBn%2BVhCjE9ucBNWtK3%2F3yEhpLoxJ6ZaxIEyXnhmv50ZAQbcKuux2lWZRaPe%2BuOXufwt08wGH99lYbSXcQb45Zbd373%2BFGLJsTDuv%2BObTnm%2FLyNS0kG%2BJEOqrcrdAQnQ0rmZfZnu8MxVypTCsvOD0k4FfvaVZ%2F4y3BbKvqWr6zNUjx%2FDOiqxFJtxhvPfDZHW15U0rl%2BAqgoNMHGG1p%2FZLGqwu9Lhrv8B2%2FG%2BOX%2Bg0wnNtQGY%2Fr%2By5sAx7SIKY8HZIKABnmxPPmRCEepfjNG%2FxkzgpDgJTzsN%2Bl2x%2FkSWVFbi20bVWszOn%2BRK2M5BCx9vLK6YsxlbQJrmD5ELpzryRL5uh0JwbeP354FpF603az9FFd0%2FVHRPpYxXG60%2FMYJGiZf9atN7IiFuXwUtJ38jvHx7zyMToyg%2BiQ6V7BNBe663hzNJPTEB%2BObghVYAsAFDe37TWg0pBV0uM0tOZWVyKzquvrw7LPkiF7VrUFQ1qbYa0EFcQ1Lbc6AZEZiNgNKcP289C%2B6rXEbVNHsKmXfQruFMZ3ML6WGwCVuYv6hj7UUYjTy4TPgVa9UxxliYirWqyDs%2F5YkPIS8t8wgb5jCz2pG1BjqmAb%2BFgTAGqCk6D0E%2FV5ihAi3xTSDmxL8rrT%2BYE60eA0EHE09Z6HRmGD0t3ZJmajP517o%2FpPpzlj60AD66omvV0HwiY2qpj9KXwBaDbV%2Bnerc%2Fgyks9ybTnsrcPUcPyHnthvQFbTldOk1LR9x9fLfK2Xf%2FuxJnssaPt7z7vXUZMWvqtEw90TsFFe62ZOG6Kfg56DlM1R7KIfpJSltaqBBACO%2FPa9fNp80%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIAZCJ2QHLFWNYC4QUJ%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=998b8d687299f7c42377ea93b0d9633b1378c147de6087bb8028cd68ce80adeb)
なお、実表に制約が定義されている場合は、ビューを通したデータの操作時にも制約のチェックが行われます。制約を満たさないデータの操作はできません。
【INSTEAD OFトリガー】
INSTEAD OFトリガーは、上記のようにDML文(INSERT、UPDATE、DELETE)を実行できないビューに対してDMLを実行できるようにするトリガーです。トリガーとは指定した条件の操作が行われた際に起動するプログラムです。Oracle独自のプログラミング言語であるPL/SQLで記述します。
ここでは、前述の[ビューを通じてのデータの削除]で作成したDISTINCTキーワードを定義に含むV_PRODビューに対してDELETE文が発行された時に起動するINSTEAD OFトリガーの例を記載します。
本来はビューの定義にDISTINCTキーワードが含まれている場合はビューを通じて実表のデータを削除することはできません。しかし、V_PRODビューにDELETE文が発行されると代わりに(INSTEAD OF)起動するINSTEAD OFトリガーを作成すると、実表に対してDELETE文が実行できるようになります。
![【図を表示4】](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/21738/kkkk49146.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T042330Z&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEAwaDmFwLW5vcnRoZWFzdC0xIkcwRQIgf%2BzqFCJvQHCwpd9KQZfj%2BwFh942kafzm2pIFRXqTnBcCIQD6uWqxPnXhfOn5TT2GIUn1NBXuFXkZUvGzBJ7Q0MbwWiqxBAjl%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F8BEAAaDDYyMzQyOTgyNzI3NSIMCCMAJ4fQ0Jp2ziMkKoUEcwshKVX9srmpq3jK9XA%2Bpd1SWtbBzE2N7%2FcmnYCMEacy73KsIVR7AwQGHhXNF2Nc%2BCaq7wFnPq8c8IisX5MV7l3gdpLh5zO1q%2BBn%2BVhCjE9ucBNWtK3%2F3yEhpLoxJ6ZaxIEyXnhmv50ZAQbcKuux2lWZRaPe%2BuOXufwt08wGH99lYbSXcQb45Zbd373%2BFGLJsTDuv%2BObTnm%2FLyNS0kG%2BJEOqrcrdAQnQ0rmZfZnu8MxVypTCsvOD0k4FfvaVZ%2F4y3BbKvqWr6zNUjx%2FDOiqxFJtxhvPfDZHW15U0rl%2BAqgoNMHGG1p%2FZLGqwu9Lhrv8B2%2FG%2BOX%2Bg0wnNtQGY%2Fr%2By5sAx7SIKY8HZIKABnmxPPmRCEepfjNG%2FxkzgpDgJTzsN%2Bl2x%2FkSWVFbi20bVWszOn%2BRK2M5BCx9vLK6YsxlbQJrmD5ELpzryRL5uh0JwbeP354FpF603az9FFd0%2FVHRPpYxXG60%2FMYJGiZf9atN7IiFuXwUtJ38jvHx7zyMToyg%2BiQ6V7BNBe663hzNJPTEB%2BObghVYAsAFDe37TWg0pBV0uM0tOZWVyKzquvrw7LPkiF7VrUFQ1qbYa0EFcQ1Lbc6AZEZiNgNKcP289C%2B6rXEbVNHsKmXfQruFMZ3ML6WGwCVuYv6hj7UUYjTy4TPgVa9UxxliYirWqyDs%2F5YkPIS8t8wgb5jCz2pG1BjqmAb%2BFgTAGqCk6D0E%2FV5ihAi3xTSDmxL8rrT%2BYE60eA0EHE09Z6HRmGD0t3ZJmajP517o%2FpPpzlj60AD66omvV0HwiY2qpj9KXwBaDbV%2Bnerc%2Fgyks9ybTnsrcPUcPyHnthvQFbTldOk1LR9x9fLfK2Xf%2FuxJnssaPt7z7vXUZMWvqtEw90TsFFe62ZOG6Kfg56DlM1R7KIfpJSltaqBBACO%2FPa9fNp80%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIAZCJ2QHLFWNYC4QUJ%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=b242fdf0788ef0bca29a0be0b6377aa09517f61547d16f4a5c3c6010d804c18a)
ただし、表に直接アクセスする場合とは異なり、ビューを通じての実表のデータの操作には、ビューの定義によっていろいろな制限があります。
ビューを通じて実表のデータを操作することができる基本的なルールは次の通りです。
![【図を表示】](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/19851/k49146.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T042330Z&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEAwaDmFwLW5vcnRoZWFzdC0xIkcwRQIgf%2BzqFCJvQHCwpd9KQZfj%2BwFh942kafzm2pIFRXqTnBcCIQD6uWqxPnXhfOn5TT2GIUn1NBXuFXkZUvGzBJ7Q0MbwWiqxBAjl%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F8BEAAaDDYyMzQyOTgyNzI3NSIMCCMAJ4fQ0Jp2ziMkKoUEcwshKVX9srmpq3jK9XA%2Bpd1SWtbBzE2N7%2FcmnYCMEacy73KsIVR7AwQGHhXNF2Nc%2BCaq7wFnPq8c8IisX5MV7l3gdpLh5zO1q%2BBn%2BVhCjE9ucBNWtK3%2F3yEhpLoxJ6ZaxIEyXnhmv50ZAQbcKuux2lWZRaPe%2BuOXufwt08wGH99lYbSXcQb45Zbd373%2BFGLJsTDuv%2BObTnm%2FLyNS0kG%2BJEOqrcrdAQnQ0rmZfZnu8MxVypTCsvOD0k4FfvaVZ%2F4y3BbKvqWr6zNUjx%2FDOiqxFJtxhvPfDZHW15U0rl%2BAqgoNMHGG1p%2FZLGqwu9Lhrv8B2%2FG%2BOX%2Bg0wnNtQGY%2Fr%2By5sAx7SIKY8HZIKABnmxPPmRCEepfjNG%2FxkzgpDgJTzsN%2Bl2x%2FkSWVFbi20bVWszOn%2BRK2M5BCx9vLK6YsxlbQJrmD5ELpzryRL5uh0JwbeP354FpF603az9FFd0%2FVHRPpYxXG60%2FMYJGiZf9atN7IiFuXwUtJ38jvHx7zyMToyg%2BiQ6V7BNBe663hzNJPTEB%2BObghVYAsAFDe37TWg0pBV0uM0tOZWVyKzquvrw7LPkiF7VrUFQ1qbYa0EFcQ1Lbc6AZEZiNgNKcP289C%2B6rXEbVNHsKmXfQruFMZ3ML6WGwCVuYv6hj7UUYjTy4TPgVa9UxxliYirWqyDs%2F5YkPIS8t8wgb5jCz2pG1BjqmAb%2BFgTAGqCk6D0E%2FV5ihAi3xTSDmxL8rrT%2BYE60eA0EHE09Z6HRmGD0t3ZJmajP517o%2FpPpzlj60AD66omvV0HwiY2qpj9KXwBaDbV%2Bnerc%2Fgyks9ybTnsrcPUcPyHnthvQFbTldOk1LR9x9fLfK2Xf%2FuxJnssaPt7z7vXUZMWvqtEw90TsFFe62ZOG6Kfg56DlM1R7KIfpJSltaqBBACO%2FPa9fNp80%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIAZCJ2QHLFWNYC4QUJ%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=add657c325b141207845ef0bfd02f24a07ab95fba20bd6eeb101d0df2c1e6d90)
[ビューを通じてのデータの追加]
ビューを通じて実表のデータを追加する場合には、ビューの定義に次の要素が含まれていないことが条件になります。
・GROUP BY句
・グループ関数
・ROWNUM擬似列
・DISTINCTキーワード
・式によって定義された列 ※
・ビューに含まれない実表の列に定義されたNOT NULL制約 ※
![<img src="/mondai3/img/jpg/k49148.jpg">](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/19852/k49148.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T042330Z&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEAwaDmFwLW5vcnRoZWFzdC0xIkcwRQIgf%2BzqFCJvQHCwpd9KQZfj%2BwFh942kafzm2pIFRXqTnBcCIQD6uWqxPnXhfOn5TT2GIUn1NBXuFXkZUvGzBJ7Q0MbwWiqxBAjl%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F8BEAAaDDYyMzQyOTgyNzI3NSIMCCMAJ4fQ0Jp2ziMkKoUEcwshKVX9srmpq3jK9XA%2Bpd1SWtbBzE2N7%2FcmnYCMEacy73KsIVR7AwQGHhXNF2Nc%2BCaq7wFnPq8c8IisX5MV7l3gdpLh5zO1q%2BBn%2BVhCjE9ucBNWtK3%2F3yEhpLoxJ6ZaxIEyXnhmv50ZAQbcKuux2lWZRaPe%2BuOXufwt08wGH99lYbSXcQb45Zbd373%2BFGLJsTDuv%2BObTnm%2FLyNS0kG%2BJEOqrcrdAQnQ0rmZfZnu8MxVypTCsvOD0k4FfvaVZ%2F4y3BbKvqWr6zNUjx%2FDOiqxFJtxhvPfDZHW15U0rl%2BAqgoNMHGG1p%2FZLGqwu9Lhrv8B2%2FG%2BOX%2Bg0wnNtQGY%2Fr%2By5sAx7SIKY8HZIKABnmxPPmRCEepfjNG%2FxkzgpDgJTzsN%2Bl2x%2FkSWVFbi20bVWszOn%2BRK2M5BCx9vLK6YsxlbQJrmD5ELpzryRL5uh0JwbeP354FpF603az9FFd0%2FVHRPpYxXG60%2FMYJGiZf9atN7IiFuXwUtJ38jvHx7zyMToyg%2BiQ6V7BNBe663hzNJPTEB%2BObghVYAsAFDe37TWg0pBV0uM0tOZWVyKzquvrw7LPkiF7VrUFQ1qbYa0EFcQ1Lbc6AZEZiNgNKcP289C%2B6rXEbVNHsKmXfQruFMZ3ML6WGwCVuYv6hj7UUYjTy4TPgVa9UxxliYirWqyDs%2F5YkPIS8t8wgb5jCz2pG1BjqmAb%2BFgTAGqCk6D0E%2FV5ihAi3xTSDmxL8rrT%2BYE60eA0EHE09Z6HRmGD0t3ZJmajP517o%2FpPpzlj60AD66omvV0HwiY2qpj9KXwBaDbV%2Bnerc%2Fgyks9ybTnsrcPUcPyHnthvQFbTldOk1LR9x9fLfK2Xf%2FuxJnssaPt7z7vXUZMWvqtEw90TsFFe62ZOG6Kfg56DlM1R7KIfpJSltaqBBACO%2FPa9fNp80%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIAZCJ2QHLFWNYC4QUJ%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=e0f3eb465082a87b0f5185bc0d0cb7219150f8a6a9cd6c0cab4492d360fe3cd7)
SQLを表示
CREATE VIEW v_prod
AS
SELECT prodid, category, name
FROM prod
WHERE category < 50;
INSERT INTO v_prod
VALUES (50, 60, 'MP3 Player');
SELECT prodid, category, name
FROM prod;
AS
SELECT prodid, category, name
FROM prod
WHERE category < 50;
INSERT INTO v_prod
VALUES (50, 60, 'MP3 Player');
SELECT prodid, category, name
FROM prod;
![<img src="/mondai3/img/jpg/kk49148.jpg">](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/19853/kk49148.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T042330Z&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEAwaDmFwLW5vcnRoZWFzdC0xIkcwRQIgf%2BzqFCJvQHCwpd9KQZfj%2BwFh942kafzm2pIFRXqTnBcCIQD6uWqxPnXhfOn5TT2GIUn1NBXuFXkZUvGzBJ7Q0MbwWiqxBAjl%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F8BEAAaDDYyMzQyOTgyNzI3NSIMCCMAJ4fQ0Jp2ziMkKoUEcwshKVX9srmpq3jK9XA%2Bpd1SWtbBzE2N7%2FcmnYCMEacy73KsIVR7AwQGHhXNF2Nc%2BCaq7wFnPq8c8IisX5MV7l3gdpLh5zO1q%2BBn%2BVhCjE9ucBNWtK3%2F3yEhpLoxJ6ZaxIEyXnhmv50ZAQbcKuux2lWZRaPe%2BuOXufwt08wGH99lYbSXcQb45Zbd373%2BFGLJsTDuv%2BObTnm%2FLyNS0kG%2BJEOqrcrdAQnQ0rmZfZnu8MxVypTCsvOD0k4FfvaVZ%2F4y3BbKvqWr6zNUjx%2FDOiqxFJtxhvPfDZHW15U0rl%2BAqgoNMHGG1p%2FZLGqwu9Lhrv8B2%2FG%2BOX%2Bg0wnNtQGY%2Fr%2By5sAx7SIKY8HZIKABnmxPPmRCEepfjNG%2FxkzgpDgJTzsN%2Bl2x%2FkSWVFbi20bVWszOn%2BRK2M5BCx9vLK6YsxlbQJrmD5ELpzryRL5uh0JwbeP354FpF603az9FFd0%2FVHRPpYxXG60%2FMYJGiZf9atN7IiFuXwUtJ38jvHx7zyMToyg%2BiQ6V7BNBe663hzNJPTEB%2BObghVYAsAFDe37TWg0pBV0uM0tOZWVyKzquvrw7LPkiF7VrUFQ1qbYa0EFcQ1Lbc6AZEZiNgNKcP289C%2B6rXEbVNHsKmXfQruFMZ3ML6WGwCVuYv6hj7UUYjTy4TPgVa9UxxliYirWqyDs%2F5YkPIS8t8wgb5jCz2pG1BjqmAb%2BFgTAGqCk6D0E%2FV5ihAi3xTSDmxL8rrT%2BYE60eA0EHE09Z6HRmGD0t3ZJmajP517o%2FpPpzlj60AD66omvV0HwiY2qpj9KXwBaDbV%2Bnerc%2Fgyks9ybTnsrcPUcPyHnthvQFbTldOk1LR9x9fLfK2Xf%2FuxJnssaPt7z7vXUZMWvqtEw90TsFFe62ZOG6Kfg56DlM1R7KIfpJSltaqBBACO%2FPa9fNp80%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIAZCJ2QHLFWNYC4QUJ%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=9b6866ce97d7aa0812accf0a6b5d68dfd3a22436952a7844f8b2705106c744df)
SQLを表示
DESC prod;
CREATE VIEW v_prod
AS
SELECT category, name
FROM prod
WHERE category < 50;
INSERT INTO v_prod
VALUES (70, 'HeadPhone');
CREATE VIEW v_prod
AS
SELECT category, name
FROM prod
WHERE category < 50;
INSERT INTO v_prod
VALUES (70, 'HeadPhone');
ただし、NOT NULL制約が定義されている列に、デフォルト値が設定されていれば、ビューを通じてデータを追加することができます。
![<img src="/mondai3/img/jpg/kk49239.jpg">](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/19854/kk49239.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T042330Z&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEAwaDmFwLW5vcnRoZWFzdC0xIkcwRQIgf%2BzqFCJvQHCwpd9KQZfj%2BwFh942kafzm2pIFRXqTnBcCIQD6uWqxPnXhfOn5TT2GIUn1NBXuFXkZUvGzBJ7Q0MbwWiqxBAjl%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F8BEAAaDDYyMzQyOTgyNzI3NSIMCCMAJ4fQ0Jp2ziMkKoUEcwshKVX9srmpq3jK9XA%2Bpd1SWtbBzE2N7%2FcmnYCMEacy73KsIVR7AwQGHhXNF2Nc%2BCaq7wFnPq8c8IisX5MV7l3gdpLh5zO1q%2BBn%2BVhCjE9ucBNWtK3%2F3yEhpLoxJ6ZaxIEyXnhmv50ZAQbcKuux2lWZRaPe%2BuOXufwt08wGH99lYbSXcQb45Zbd373%2BFGLJsTDuv%2BObTnm%2FLyNS0kG%2BJEOqrcrdAQnQ0rmZfZnu8MxVypTCsvOD0k4FfvaVZ%2F4y3BbKvqWr6zNUjx%2FDOiqxFJtxhvPfDZHW15U0rl%2BAqgoNMHGG1p%2FZLGqwu9Lhrv8B2%2FG%2BOX%2Bg0wnNtQGY%2Fr%2By5sAx7SIKY8HZIKABnmxPPmRCEepfjNG%2FxkzgpDgJTzsN%2Bl2x%2FkSWVFbi20bVWszOn%2BRK2M5BCx9vLK6YsxlbQJrmD5ELpzryRL5uh0JwbeP354FpF603az9FFd0%2FVHRPpYxXG60%2FMYJGiZf9atN7IiFuXwUtJ38jvHx7zyMToyg%2BiQ6V7BNBe663hzNJPTEB%2BObghVYAsAFDe37TWg0pBV0uM0tOZWVyKzquvrw7LPkiF7VrUFQ1qbYa0EFcQ1Lbc6AZEZiNgNKcP289C%2B6rXEbVNHsKmXfQruFMZ3ML6WGwCVuYv6hj7UUYjTy4TPgVa9UxxliYirWqyDs%2F5YkPIS8t8wgb5jCz2pG1BjqmAb%2BFgTAGqCk6D0E%2FV5ihAi3xTSDmxL8rrT%2BYE60eA0EHE09Z6HRmGD0t3ZJmajP517o%2FpPpzlj60AD66omvV0HwiY2qpj9KXwBaDbV%2Bnerc%2Fgyks9ybTnsrcPUcPyHnthvQFbTldOk1LR9x9fLfK2Xf%2FuxJnssaPt7z7vXUZMWvqtEw90TsFFe62ZOG6Kfg56DlM1R7KIfpJSltaqBBACO%2FPa9fNp80%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIAZCJ2QHLFWNYC4QUJ%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=6cb79998312fb5db08dea6ae4dc5eee41b9f7b0e207790cc4081ea38c4a1d24d)
SQLを表示
CREATE TABLE table2
(id NUMBER(2),
name VARCHAR2(10),
birth DATE DEFAULT SYSDATE NOT NULL
);
CREATE VIEW view2
AS
SELECT id, name FROM table2;
INSERT INTO view2
VALUES (1, 'Tanaka');
(id NUMBER(2),
name VARCHAR2(10),
birth DATE DEFAULT SYSDATE NOT NULL
);
CREATE VIEW view2
AS
SELECT id, name FROM table2;
INSERT INTO view2
VALUES (1, 'Tanaka');
[ビューを通じてのデータの更新]
ビューを通じて実表のデータを更新する場合には、ビューの定義に次の要素が含まれていないことが条件になります。
・GROUP BY句
・グループ関数
・ROWNUM擬似列
・DISTINCTキーワード
・式によって定義された列 ※
![<img src="/mondai3/img/jpg/k49147.jpg">](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/19855/k49147.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T042330Z&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEAwaDmFwLW5vcnRoZWFzdC0xIkcwRQIgf%2BzqFCJvQHCwpd9KQZfj%2BwFh942kafzm2pIFRXqTnBcCIQD6uWqxPnXhfOn5TT2GIUn1NBXuFXkZUvGzBJ7Q0MbwWiqxBAjl%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F8BEAAaDDYyMzQyOTgyNzI3NSIMCCMAJ4fQ0Jp2ziMkKoUEcwshKVX9srmpq3jK9XA%2Bpd1SWtbBzE2N7%2FcmnYCMEacy73KsIVR7AwQGHhXNF2Nc%2BCaq7wFnPq8c8IisX5MV7l3gdpLh5zO1q%2BBn%2BVhCjE9ucBNWtK3%2F3yEhpLoxJ6ZaxIEyXnhmv50ZAQbcKuux2lWZRaPe%2BuOXufwt08wGH99lYbSXcQb45Zbd373%2BFGLJsTDuv%2BObTnm%2FLyNS0kG%2BJEOqrcrdAQnQ0rmZfZnu8MxVypTCsvOD0k4FfvaVZ%2F4y3BbKvqWr6zNUjx%2FDOiqxFJtxhvPfDZHW15U0rl%2BAqgoNMHGG1p%2FZLGqwu9Lhrv8B2%2FG%2BOX%2Bg0wnNtQGY%2Fr%2By5sAx7SIKY8HZIKABnmxPPmRCEepfjNG%2FxkzgpDgJTzsN%2Bl2x%2FkSWVFbi20bVWszOn%2BRK2M5BCx9vLK6YsxlbQJrmD5ELpzryRL5uh0JwbeP354FpF603az9FFd0%2FVHRPpYxXG60%2FMYJGiZf9atN7IiFuXwUtJ38jvHx7zyMToyg%2BiQ6V7BNBe663hzNJPTEB%2BObghVYAsAFDe37TWg0pBV0uM0tOZWVyKzquvrw7LPkiF7VrUFQ1qbYa0EFcQ1Lbc6AZEZiNgNKcP289C%2B6rXEbVNHsKmXfQruFMZ3ML6WGwCVuYv6hj7UUYjTy4TPgVa9UxxliYirWqyDs%2F5YkPIS8t8wgb5jCz2pG1BjqmAb%2BFgTAGqCk6D0E%2FV5ihAi3xTSDmxL8rrT%2BYE60eA0EHE09Z6HRmGD0t3ZJmajP517o%2FpPpzlj60AD66omvV0HwiY2qpj9KXwBaDbV%2Bnerc%2Fgyks9ybTnsrcPUcPyHnthvQFbTldOk1LR9x9fLfK2Xf%2FuxJnssaPt7z7vXUZMWvqtEw90TsFFe62ZOG6Kfg56DlM1R7KIfpJSltaqBBACO%2FPa9fNp80%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIAZCJ2QHLFWNYC4QUJ%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=edeb97af10361b40e9f679eecbeb0bad2ce3fd658df9d5eb0960d520a73b76cc)
SQLを表示
CREATE VIEW v_prod
AS
SELECT category, name
FROM prod;
SELECT * FROM v_prod;
UPDATE v_prod SET name = 'Chopin'
WHERE category = 10;
SELECT prodid, category, name
FROM prod;
AS
SELECT category, name
FROM prod;
SELECT * FROM v_prod;
UPDATE v_prod SET name = 'Chopin'
WHERE category = 10;
SELECT prodid, category, name
FROM prod;
![<img src="/mondai3/img/jpg/kk49147.jpg">](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/19856/kk49147.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T042330Z&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEAwaDmFwLW5vcnRoZWFzdC0xIkcwRQIgf%2BzqFCJvQHCwpd9KQZfj%2BwFh942kafzm2pIFRXqTnBcCIQD6uWqxPnXhfOn5TT2GIUn1NBXuFXkZUvGzBJ7Q0MbwWiqxBAjl%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F8BEAAaDDYyMzQyOTgyNzI3NSIMCCMAJ4fQ0Jp2ziMkKoUEcwshKVX9srmpq3jK9XA%2Bpd1SWtbBzE2N7%2FcmnYCMEacy73KsIVR7AwQGHhXNF2Nc%2BCaq7wFnPq8c8IisX5MV7l3gdpLh5zO1q%2BBn%2BVhCjE9ucBNWtK3%2F3yEhpLoxJ6ZaxIEyXnhmv50ZAQbcKuux2lWZRaPe%2BuOXufwt08wGH99lYbSXcQb45Zbd373%2BFGLJsTDuv%2BObTnm%2FLyNS0kG%2BJEOqrcrdAQnQ0rmZfZnu8MxVypTCsvOD0k4FfvaVZ%2F4y3BbKvqWr6zNUjx%2FDOiqxFJtxhvPfDZHW15U0rl%2BAqgoNMHGG1p%2FZLGqwu9Lhrv8B2%2FG%2BOX%2Bg0wnNtQGY%2Fr%2By5sAx7SIKY8HZIKABnmxPPmRCEepfjNG%2FxkzgpDgJTzsN%2Bl2x%2FkSWVFbi20bVWszOn%2BRK2M5BCx9vLK6YsxlbQJrmD5ELpzryRL5uh0JwbeP354FpF603az9FFd0%2FVHRPpYxXG60%2FMYJGiZf9atN7IiFuXwUtJ38jvHx7zyMToyg%2BiQ6V7BNBe663hzNJPTEB%2BObghVYAsAFDe37TWg0pBV0uM0tOZWVyKzquvrw7LPkiF7VrUFQ1qbYa0EFcQ1Lbc6AZEZiNgNKcP289C%2B6rXEbVNHsKmXfQruFMZ3ML6WGwCVuYv6hj7UUYjTy4TPgVa9UxxliYirWqyDs%2F5YkPIS8t8wgb5jCz2pG1BjqmAb%2BFgTAGqCk6D0E%2FV5ihAi3xTSDmxL8rrT%2BYE60eA0EHE09Z6HRmGD0t3ZJmajP517o%2FpPpzlj60AD66omvV0HwiY2qpj9KXwBaDbV%2Bnerc%2Fgyks9ybTnsrcPUcPyHnthvQFbTldOk1LR9x9fLfK2Xf%2FuxJnssaPt7z7vXUZMWvqtEw90TsFFe62ZOG6Kfg56DlM1R7KIfpJSltaqBBACO%2FPa9fNp80%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIAZCJ2QHLFWNYC4QUJ%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=1120fd6a17bb21d271372743f7de8aed32c89bf3ff610bc7b25958808b27580a)
SQLを表示
CREATE VIEW v_prod
AS
SELECT category, MAX(name) name
FROM prod
GROUP BY category;
UPDATE v_prod SET name = 'Chopin'
WHERE category = 10;
AS
SELECT category, MAX(name) name
FROM prod
GROUP BY category;
UPDATE v_prod SET name = 'Chopin'
WHERE category = 10;
ただし、ビューの定義に式を含む列が定義されている場合でも、対象列以外はデータの追加、更新を行うことができます。
![<img src="/mondai3/img/jpg/k49240.jpg">](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/19857/k49240.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T042330Z&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEAwaDmFwLW5vcnRoZWFzdC0xIkcwRQIgf%2BzqFCJvQHCwpd9KQZfj%2BwFh942kafzm2pIFRXqTnBcCIQD6uWqxPnXhfOn5TT2GIUn1NBXuFXkZUvGzBJ7Q0MbwWiqxBAjl%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F8BEAAaDDYyMzQyOTgyNzI3NSIMCCMAJ4fQ0Jp2ziMkKoUEcwshKVX9srmpq3jK9XA%2Bpd1SWtbBzE2N7%2FcmnYCMEacy73KsIVR7AwQGHhXNF2Nc%2BCaq7wFnPq8c8IisX5MV7l3gdpLh5zO1q%2BBn%2BVhCjE9ucBNWtK3%2F3yEhpLoxJ6ZaxIEyXnhmv50ZAQbcKuux2lWZRaPe%2BuOXufwt08wGH99lYbSXcQb45Zbd373%2BFGLJsTDuv%2BObTnm%2FLyNS0kG%2BJEOqrcrdAQnQ0rmZfZnu8MxVypTCsvOD0k4FfvaVZ%2F4y3BbKvqWr6zNUjx%2FDOiqxFJtxhvPfDZHW15U0rl%2BAqgoNMHGG1p%2FZLGqwu9Lhrv8B2%2FG%2BOX%2Bg0wnNtQGY%2Fr%2By5sAx7SIKY8HZIKABnmxPPmRCEepfjNG%2FxkzgpDgJTzsN%2Bl2x%2FkSWVFbi20bVWszOn%2BRK2M5BCx9vLK6YsxlbQJrmD5ELpzryRL5uh0JwbeP354FpF603az9FFd0%2FVHRPpYxXG60%2FMYJGiZf9atN7IiFuXwUtJ38jvHx7zyMToyg%2BiQ6V7BNBe663hzNJPTEB%2BObghVYAsAFDe37TWg0pBV0uM0tOZWVyKzquvrw7LPkiF7VrUFQ1qbYa0EFcQ1Lbc6AZEZiNgNKcP289C%2B6rXEbVNHsKmXfQruFMZ3ML6WGwCVuYv6hj7UUYjTy4TPgVa9UxxliYirWqyDs%2F5YkPIS8t8wgb5jCz2pG1BjqmAb%2BFgTAGqCk6D0E%2FV5ihAi3xTSDmxL8rrT%2BYE60eA0EHE09Z6HRmGD0t3ZJmajP517o%2FpPpzlj60AD66omvV0HwiY2qpj9KXwBaDbV%2Bnerc%2Fgyks9ybTnsrcPUcPyHnthvQFbTldOk1LR9x9fLfK2Xf%2FuxJnssaPt7z7vXUZMWvqtEw90TsFFe62ZOG6Kfg56DlM1R7KIfpJSltaqBBACO%2FPa9fNp80%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIAZCJ2QHLFWNYC4QUJ%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=436a827e603a68a122f2e8df69c4f9538e67612ef9d696166eccd10f4048b501)
SQLを表示
DESC employees;
CREATE VIEW v_emp
AS
SELECT employee_id, employee_name, salary * 12 salary
FROM employees;
INSERT INTO v_emp
VALUES (1000, 'Tanaka', 8000000);
INSERT INTO v_emp (employee_id, employee_name)
VALUES (1000, 'Tanaka');
UPDATE v_emp SET salary = 0;
UPDATE v_emp SET employee_name = 'Yamada'
WHERE employee_id = 1000;
CREATE VIEW v_emp
AS
SELECT employee_id, employee_name, salary * 12 salary
FROM employees;
INSERT INTO v_emp
VALUES (1000, 'Tanaka', 8000000);
INSERT INTO v_emp (employee_id, employee_name)
VALUES (1000, 'Tanaka');
UPDATE v_emp SET salary = 0;
UPDATE v_emp SET employee_name = 'Yamada'
WHERE employee_id = 1000;
[ビューを通じてのデータの削除]
ビューを通じて実表のデータを削除する場合には、ビューの定義に次の要素が含まれていないことが条件になります。
・GROUP BY句
・グループ関数
・ROWNUM擬似列
・DISTINCTキーワード
![【図を表示2】](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/19858/kk49146.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T042330Z&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEAwaDmFwLW5vcnRoZWFzdC0xIkcwRQIgf%2BzqFCJvQHCwpd9KQZfj%2BwFh942kafzm2pIFRXqTnBcCIQD6uWqxPnXhfOn5TT2GIUn1NBXuFXkZUvGzBJ7Q0MbwWiqxBAjl%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F8BEAAaDDYyMzQyOTgyNzI3NSIMCCMAJ4fQ0Jp2ziMkKoUEcwshKVX9srmpq3jK9XA%2Bpd1SWtbBzE2N7%2FcmnYCMEacy73KsIVR7AwQGHhXNF2Nc%2BCaq7wFnPq8c8IisX5MV7l3gdpLh5zO1q%2BBn%2BVhCjE9ucBNWtK3%2F3yEhpLoxJ6ZaxIEyXnhmv50ZAQbcKuux2lWZRaPe%2BuOXufwt08wGH99lYbSXcQb45Zbd373%2BFGLJsTDuv%2BObTnm%2FLyNS0kG%2BJEOqrcrdAQnQ0rmZfZnu8MxVypTCsvOD0k4FfvaVZ%2F4y3BbKvqWr6zNUjx%2FDOiqxFJtxhvPfDZHW15U0rl%2BAqgoNMHGG1p%2FZLGqwu9Lhrv8B2%2FG%2BOX%2Bg0wnNtQGY%2Fr%2By5sAx7SIKY8HZIKABnmxPPmRCEepfjNG%2FxkzgpDgJTzsN%2Bl2x%2FkSWVFbi20bVWszOn%2BRK2M5BCx9vLK6YsxlbQJrmD5ELpzryRL5uh0JwbeP354FpF603az9FFd0%2FVHRPpYxXG60%2FMYJGiZf9atN7IiFuXwUtJ38jvHx7zyMToyg%2BiQ6V7BNBe663hzNJPTEB%2BObghVYAsAFDe37TWg0pBV0uM0tOZWVyKzquvrw7LPkiF7VrUFQ1qbYa0EFcQ1Lbc6AZEZiNgNKcP289C%2B6rXEbVNHsKmXfQruFMZ3ML6WGwCVuYv6hj7UUYjTy4TPgVa9UxxliYirWqyDs%2F5YkPIS8t8wgb5jCz2pG1BjqmAb%2BFgTAGqCk6D0E%2FV5ihAi3xTSDmxL8rrT%2BYE60eA0EHE09Z6HRmGD0t3ZJmajP517o%2FpPpzlj60AD66omvV0HwiY2qpj9KXwBaDbV%2Bnerc%2Fgyks9ybTnsrcPUcPyHnthvQFbTldOk1LR9x9fLfK2Xf%2FuxJnssaPt7z7vXUZMWvqtEw90TsFFe62ZOG6Kfg56DlM1R7KIfpJSltaqBBACO%2FPa9fNp80%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIAZCJ2QHLFWNYC4QUJ%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=7ccc48665852949601ab68a18cf66a62a6be390faac478d152b8b26384ef3f8e)
SQLを表示
CREATE VIEW v_prod
AS
SELECT category, name
FROM prod;
SELECT * FROM v_prod;
DELETE FROM v_prod
WHERE category = 50;
AS
SELECT category, name
FROM prod;
SELECT * FROM v_prod;
DELETE FROM v_prod
WHERE category = 50;
![【図を表示3】](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/19859/kkk49146.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T042330Z&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEAwaDmFwLW5vcnRoZWFzdC0xIkcwRQIgf%2BzqFCJvQHCwpd9KQZfj%2BwFh942kafzm2pIFRXqTnBcCIQD6uWqxPnXhfOn5TT2GIUn1NBXuFXkZUvGzBJ7Q0MbwWiqxBAjl%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F8BEAAaDDYyMzQyOTgyNzI3NSIMCCMAJ4fQ0Jp2ziMkKoUEcwshKVX9srmpq3jK9XA%2Bpd1SWtbBzE2N7%2FcmnYCMEacy73KsIVR7AwQGHhXNF2Nc%2BCaq7wFnPq8c8IisX5MV7l3gdpLh5zO1q%2BBn%2BVhCjE9ucBNWtK3%2F3yEhpLoxJ6ZaxIEyXnhmv50ZAQbcKuux2lWZRaPe%2BuOXufwt08wGH99lYbSXcQb45Zbd373%2BFGLJsTDuv%2BObTnm%2FLyNS0kG%2BJEOqrcrdAQnQ0rmZfZnu8MxVypTCsvOD0k4FfvaVZ%2F4y3BbKvqWr6zNUjx%2FDOiqxFJtxhvPfDZHW15U0rl%2BAqgoNMHGG1p%2FZLGqwu9Lhrv8B2%2FG%2BOX%2Bg0wnNtQGY%2Fr%2By5sAx7SIKY8HZIKABnmxPPmRCEepfjNG%2FxkzgpDgJTzsN%2Bl2x%2FkSWVFbi20bVWszOn%2BRK2M5BCx9vLK6YsxlbQJrmD5ELpzryRL5uh0JwbeP354FpF603az9FFd0%2FVHRPpYxXG60%2FMYJGiZf9atN7IiFuXwUtJ38jvHx7zyMToyg%2BiQ6V7BNBe663hzNJPTEB%2BObghVYAsAFDe37TWg0pBV0uM0tOZWVyKzquvrw7LPkiF7VrUFQ1qbYa0EFcQ1Lbc6AZEZiNgNKcP289C%2B6rXEbVNHsKmXfQruFMZ3ML6WGwCVuYv6hj7UUYjTy4TPgVa9UxxliYirWqyDs%2F5YkPIS8t8wgb5jCz2pG1BjqmAb%2BFgTAGqCk6D0E%2FV5ihAi3xTSDmxL8rrT%2BYE60eA0EHE09Z6HRmGD0t3ZJmajP517o%2FpPpzlj60AD66omvV0HwiY2qpj9KXwBaDbV%2Bnerc%2Fgyks9ybTnsrcPUcPyHnthvQFbTldOk1LR9x9fLfK2Xf%2FuxJnssaPt7z7vXUZMWvqtEw90TsFFe62ZOG6Kfg56DlM1R7KIfpJSltaqBBACO%2FPa9fNp80%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIAZCJ2QHLFWNYC4QUJ%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=998b8d687299f7c42377ea93b0d9633b1378c147de6087bb8028cd68ce80adeb)
SQLを表示
CREATE VIEW v_prod
AS
SELECT DISTINCT category, name
FROM prod;
SELECT * FROM v_prod;
DELETE FROM v_prod
WHERE category = 40;
AS
SELECT DISTINCT category, name
FROM prod;
SELECT * FROM v_prod;
DELETE FROM v_prod
WHERE category = 40;
なお、実表に制約が定義されている場合は、ビューを通したデータの操作時にも制約のチェックが行われます。制約を満たさないデータの操作はできません。
【INSTEAD OFトリガー】
INSTEAD OFトリガーは、上記のようにDML文(INSERT、UPDATE、DELETE)を実行できないビューに対してDMLを実行できるようにするトリガーです。トリガーとは指定した条件の操作が行われた際に起動するプログラムです。Oracle独自のプログラミング言語であるPL/SQLで記述します。
ここでは、前述の[ビューを通じてのデータの削除]で作成したDISTINCTキーワードを定義に含むV_PRODビューに対してDELETE文が発行された時に起動するINSTEAD OFトリガーの例を記載します。
本来はビューの定義にDISTINCTキーワードが含まれている場合はビューを通じて実表のデータを削除することはできません。しかし、V_PRODビューにDELETE文が発行されると代わりに(INSTEAD OF)起動するINSTEAD OFトリガーを作成すると、実表に対してDELETE文が実行できるようになります。
![【図を表示4】](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/21738/kkkk49146.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T042330Z&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEAwaDmFwLW5vcnRoZWFzdC0xIkcwRQIgf%2BzqFCJvQHCwpd9KQZfj%2BwFh942kafzm2pIFRXqTnBcCIQD6uWqxPnXhfOn5TT2GIUn1NBXuFXkZUvGzBJ7Q0MbwWiqxBAjl%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F8BEAAaDDYyMzQyOTgyNzI3NSIMCCMAJ4fQ0Jp2ziMkKoUEcwshKVX9srmpq3jK9XA%2Bpd1SWtbBzE2N7%2FcmnYCMEacy73KsIVR7AwQGHhXNF2Nc%2BCaq7wFnPq8c8IisX5MV7l3gdpLh5zO1q%2BBn%2BVhCjE9ucBNWtK3%2F3yEhpLoxJ6ZaxIEyXnhmv50ZAQbcKuux2lWZRaPe%2BuOXufwt08wGH99lYbSXcQb45Zbd373%2BFGLJsTDuv%2BObTnm%2FLyNS0kG%2BJEOqrcrdAQnQ0rmZfZnu8MxVypTCsvOD0k4FfvaVZ%2F4y3BbKvqWr6zNUjx%2FDOiqxFJtxhvPfDZHW15U0rl%2BAqgoNMHGG1p%2FZLGqwu9Lhrv8B2%2FG%2BOX%2Bg0wnNtQGY%2Fr%2By5sAx7SIKY8HZIKABnmxPPmRCEepfjNG%2FxkzgpDgJTzsN%2Bl2x%2FkSWVFbi20bVWszOn%2BRK2M5BCx9vLK6YsxlbQJrmD5ELpzryRL5uh0JwbeP354FpF603az9FFd0%2FVHRPpYxXG60%2FMYJGiZf9atN7IiFuXwUtJ38jvHx7zyMToyg%2BiQ6V7BNBe663hzNJPTEB%2BObghVYAsAFDe37TWg0pBV0uM0tOZWVyKzquvrw7LPkiF7VrUFQ1qbYa0EFcQ1Lbc6AZEZiNgNKcP289C%2B6rXEbVNHsKmXfQruFMZ3ML6WGwCVuYv6hj7UUYjTy4TPgVa9UxxliYirWqyDs%2F5YkPIS8t8wgb5jCz2pG1BjqmAb%2BFgTAGqCk6D0E%2FV5ihAi3xTSDmxL8rrT%2BYE60eA0EHE09Z6HRmGD0t3ZJmajP517o%2FpPpzlj60AD66omvV0HwiY2qpj9KXwBaDbV%2Bnerc%2Fgyks9ybTnsrcPUcPyHnthvQFbTldOk1LR9x9fLfK2Xf%2FuxJnssaPt7z7vXUZMWvqtEw90TsFFe62ZOG6Kfg56DlM1R7KIfpJSltaqBBACO%2FPa9fNp80%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIAZCJ2QHLFWNYC4QUJ%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=b242fdf0788ef0bca29a0be0b6377aa09517f61547d16f4a5c3c6010d804c18a)
SQLを表示
CREATE VIEW v_prod
AS
SELECT DISTINCT category, name
FROM prod;
CREATE OR REPLACE TRIGGER delete_v_prod
INSTEAD OF DELETE ON v_prod
BEGIN
DELETE prod WHERE category = :OLD.category;
END;
/
DELETE FROM v_prod
WHERE category = 40;
SELECT * FROM prod;
AS
SELECT DISTINCT category, name
FROM prod;
CREATE OR REPLACE TRIGGER delete_v_prod
INSTEAD OF DELETE ON v_prod
BEGIN
DELETE prod WHERE category = :OLD.category;
END;
/
DELETE FROM v_prod
WHERE category = 40;
SELECT * FROM prod;
ビュー1は追加可能ではないでしょうか
公開日 2023/07/09
ビュー1を通じて、EMPLOYEES表へのデータの追加ができる
は、計算式を含む列以外の指定であればデータの追加は可能なのではないでしょうか。
スタッフからの返信
この投稿に対して返信しませんか?
s staff_ishii
2023/07/11 17:50
shuichi0705 さん ご指摘の点を修正いたしました。 ご報告、誠にありがとうございました。