Oracle中user_updatable_columns显示异常及可更新视图等问题咨询

测试环境准备 #

先创建测试表及数据：

create table test_customer(
customer_id number(2),
customer_name varchar2(100),
customer_town varchar2(50),
primary key(customer_id));
create table test_account(
account_id number(2),
interest number(3,2),
balance number(5,2),
overdraft number(5),
primary key(account_id));
create table test_customer_account(
customer_id number(2),
account_id number(2),
primary key(customer_id, account_id),
foreign key(customer_id) references test_customer(customer_id),
foreign key(account_id) references test_account(account_id));

insert into test_customer values(1,'John','London');
insert into test_customer values(2,'Melissa','Bristol');
insert into test_account values(1,0.12,100,500);
insert into test_account values(2,0.5,300,120);
insert into test_account values(3,0.3,10,1000);
insert into test_customer_account values(1,1);
insert into test_customer_account values(2,1);
insert into test_customer_account values(2,2);

创建关联视图：

create view test_customer_account_view as
select test_customer.customer_id,test_account.account_id,balance, interest
from test_customer, test_account, test_customer_account
where test_customer.customer_id=test_customer_account.customer_id and
test_account.account_id=test_customer_account.account_id;

实际操作中，执行UPDATE test_customer_account_view SET interest=1 WHERE balance=12;成功，但查询select * from user_updatable_columns where table_name='TEST_CUSTOMER_ACCOUNT_VIEW';显示所有列的updatable、insert、delete均为No。针对以下三个问题逐一解答：

问题1：我的理解是否正确？若正确，为何user_updatable_columns中overdraft列的可更新等属性为No？ #

你的核心理解是对的：test_customer和test_account属于键保留表，理论上它们的列可以通过视图更新，这也是你执行UPDATE语句成功的原因。

但user_updatable_columns显示所有列不可更新，是因为你用了旧式的隐式笛卡尔连接语法，未使用显式JOIN关键字（如INNER JOIN），Oracle的数据字典无法正确解析视图中的表关联关系，从而误判所有列不可更新——但Oracle内核在执行实际SQL时，能正确识别键保留表，所以你的UPDATE操作能正常执行。

另外，overdraft列并未被包含在视图的SELECT列表中，user_updatable_columns只会统计视图中存在的列，所以你查询结果里根本不会出现overdraft列，大概率是混淆了基表和视图的列。

问题2：执行delete from test_customer_account_view where customer_id=1;时，为何仅删除test_customer_account的记录，而非作为第一个键保留表的test_customer的记录？ #

这是因为视图的DELETE操作默认只会作用于不会引发外键约束冲突的表：test_customer是主表，直接删除它会导致test_customer_account中的外键记录因失去关联而失效，违反外键约束；而test_customer_account是关联中间表，删除它不会影响主表数据，Oracle默认选择删除该表的记录。

如果要删除test_customer的记录，需要创建视图时显式指定INSTEAD OF触发器，自定义删除逻辑，否则默认行为只会删除关联中间表的行。

问题3：balance定义为number(5,2)，为何值1000不被允许？按定义应为最多5位数字，其中最多2位小数，1000是4位整数无小数，为何不符合要求？ #

number(p,s)的规则是：p代表总精度（整数+小数部分的总位数），s代表小数位数。number(5,2)表示总共有5位数字，其中2位是小数，那么整数部分最多只能有5-2=3位。1000是4位整数，已经超过了3位的限制，所以会被判定为非法值。

举个例子：

• 合法值：999.99（3位整数+2位小数，总5位）、100.00（3位整数）
• 非法值：1000.00（4位整数+2位小数，总6位）、1000（4位整数，整数部分超限制）

内容的提问来源于stack exchange，提问作者user3474704