假設我有這兩個已經填滿的表，我想nb_departments在表 Locations 中添加一列，該列用該位置的部門數量進行初始化。
CREATE TABLE departments
(
department_id NUMBER(4) PRIMARY KEY,
department_name VARCHAR2(30) NOT NULL,
manager_id NUMBER(6),
location_id NUMBER(4) NOT NULL
);
CREATE TABLE locations
(
location_id NUMBER(4) PRIMARY KEY,
street_address VARCHAR2(40),
postal_code VARCHAR2(12),
city VARCHAR2(30),
state_province VARCHAR2(25),
country_id CHAR(2) NOT NULL
);
可以使用觸發器或使用來完成alter table add column default value嗎？
uj5u.com熱心網友回復：
您可以創建一個視圖：
CREATE VIEW vwlocations
(
location_id,
street_address,
postal_code,
city ,
state_province,
country_id,
nb_departments
) AS
SELECT l.location_id,
l.street_address,
l.postal_code,
l.city ,
l.state_province,
l.country_id,
COUNT(d.location_id) AS nb_departments
FROM locations l
JOIN departments d
ON l.location_id = d.location_id
GROUP BY l.location_id,
l.street_address,
l.postal_code,
l.city ,
l.state_province,
l.country_id
如果您確實想向現有表添加一列，您可以通過這種方式初始化您的列值并添加一個觸發器來管理此后的更新/插入/洗掉：
UPDATE locations SET locations.nb_departments = (SELECT COUNT(*) FROM departments WHERE locations.location_id = departments.location_id)
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/534410.html
標籤：数据库甲骨文
上一篇：如何解決錯誤“System.InvalidCastException-列包含NULL資料”
下一篇：視窗子句的決議作用及手段