Oracle PLSQL函数如何实现单行返回多个主键约束列？

• 循环逻辑中直接内嵌RETURN语句：第一次遍历到约束列时就会终止函数运行，仅能返回第一个匹配的列名
• 无字符串拼接逻辑：每次循环都用新的列名直接覆盖返回变量，就算不提前返回也只能拿到最后一个列名
• 返回变量长度定义过小：wReturn仅定义了30位长度，仅能容纳单个Oracle列名，多列拼接后会触发字符串溢出报错

CREATE OR REPLACE FUNCTION Get_Constraint_Columns(
/*
* Oct 10th, 2021
* Return the column names that make up a PK constraint and columns that make up unique constraint.
*/
iTable VARCHAR2, iConstraint VARCHAR2
)
RETURN VARCHAR2
AS
  -- 放大返回值长度，预留足够拼接空间
  wReturn VARCHAR2(1000);
CURSOR ColumnSelected IS
  SELECT Column_Name
  FROM User_Cons_Columns
  WHERE Table_Name = iTable AND Constraint_Name = iConstraint
  ORDER BY Position;
CurrentRow User_Cons_Columns%ROWTYPE;

BEGIN
 FOR CurrentRow IN ColumnSelected LOOP
   -- 拼接列名，用逗号分隔
   wReturn := wReturn || ',' || CurrentRow.Column_Name;
 END LOOP;
 -- 去掉开头多余的逗号，外层包裹括号匹配你要的输出格式
 RETURN '(' || LTRIM(wReturn, ',') || ')';
END;
/

注意：Oracle系统表中存储的表名、约束名默认都是大写，传入参数时如果是小写需要先用UPPER()函数转换，否则会查询不到结果

可以直接用Oracle内置的LISTAGG聚合函数完成拼接，代码更简洁：

CREATE OR REPLACE FUNCTION Get_Constraint_Columns(
iTable VARCHAR2, iConstraint VARCHAR2
)
RETURN VARCHAR2
AS
  wReturn VARCHAR2(1000);
BEGIN
  SELECT '(' || LISTAGG(Column_Name, ',') WITHIN GROUP (ORDER BY Position) || ')'
  INTO wReturn
  FROM User_Cons_Columns
  WHERE Table_Name = iTable AND Constraint_Name = iConstraint;
  RETURN wReturn;
EXCEPTION
  -- 处理无匹配约束的场景，避免触发NO_DATA_FOUND报错
  WHEN NO_DATA_FOUND THEN
    RETURN '()';
END;
/

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