如何在PL/SQL中为表添加带函数默认值的EMAIL列

你想要的直接在ALTER TABLE ADD语句中用函数赋值的写法在Oracle里不支持，可按以下两种方式实现需求：

方法一：静态填充列（一次性赋值，后续需手动维护） #

1. 添加允许为空的EMAIL列
   先创建列并暂时允许为空，因为需要先完成数据填充：

ALTER TABLE My_Table ADD EMAIL VARCHAR2(64);

2. 调用函数批量更新EMAIL值
   通过函数为每一行生成对应邮箱地址，建议先过滤掉USERS表中不存在的UserNo_column值，避免函数抛出NO_DATA_FOUND异常：

UPDATE My_Table mt
SET EMAIL = userno2email(mt.UserNo_column)
WHERE EXISTS (SELECT 1 FROM USERS u WHERE u.O_USERNO = mt.UserNo_column);

对于无匹配邮箱的行，可设置默认值（比如SET EMAIL = 'unknown@example.com'）或删除对应行，否则后续无法设置NOT NULL约束。

3. 设置列的非空约束
   确认所有行的EMAIL字段都有有效值后，修改列属性为非空：

ALTER TABLE My_Table MODIFY EMAIL VARCHAR2(64) NOT NULL;

方法二：创建虚拟列（动态计算，自动同步） #

如果希望EMAIL值随USERS表中邮箱的变更自动更新，可创建虚拟列，它会在查询时自动调用函数计算值，无需手动维护：

ALTER TABLE My_Table ADD EMAIL VARCHAR2(64) 
GENERATED ALWAYS AS (userno2email(UserNo_column)) 
VIRTUAL NOT NULL;

注意：虚拟列无法手动更新，且要求函数是确定性函数（相同输入返回相同输出）；同时需确保所有UserNo_column值都能在USERS表中找到对应邮箱，否则查询时会抛出异常。

优化你的函数 #

当前函数存在参数名潜在歧义问题，且未处理NO_DATA_FOUND异常，建议调整：

CREATE OR REPLACE FUNCTION userno2email 
  (p_userno IN NUMBER) -- 重命名参数避免歧义
RETURN VARCHAR2 IS 
  email VARCHAR2(64);
BEGIN
  SELECT O_EMAIL INTO email 
  FROM USERS 
  WHERE O_USERNO = p_userno;
  RETURN email;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN NULL; -- 或返回默认邮箱、抛出自定义异常，按需调整
END;

内容的提问来源于stack exchange，提问作者Morten Sørensen