如何将随机姓名生成SQL转为函数/机制填充CUSTOMERS表？

我有一段可正常生成随机姓名的Oracle SQL代码，运行逻辑及输出如下：

原SQL代码：

WITH cte AS ( SELECT  level lvl FROM dual CONNECT BY level <= 5 )
        SELECT 
          case t1.lvl
               WHEN 1 THEN 'Faith'
                WHEN 2 THEN 'Tom'
                WHEN 3 THEN 'Anna'
                WHEN 4 THEN 'Lisa'
                WHEN 5 THEN 'Andy'
                end first_name,
         case t1.lvl
              WHEN 1 THEN 'Andrews'
               WHEN 2 THEN 'Thorton'
               WHEN 3 THEN 'Smith'
               WHEN 4 THEN 'Jones'
               WHEN 5 THEN 'Beirs'
               end last_name
   FROM (
   SELECT lvl
    FROM cte
    ORDER BY dbms_random.value(0,sign(lvl))
   ) t1

输出示例：

FIRST_NAME    LAST_NAME
Tom    Thorton
Anna    Smith
Andy    Beirs
Lisa    Jones
Faith    Andrews

FIRST_NAME    LAST_NAME
Lisa    Jones
Anna    Smith
Faith    Andrews
Andy    Beirs
Tom    Thorton

我希望将这段代码转化为可复用机制（比如函数），用来给CUSTOMERS表填充随机姓名。已编写带占位符的PL/SQL循环插入代码，需将'???'替换为随机生成的姓名：

CREATE TABLE CUSTOMERS (
 customer_id NUMBER,
 first_name VARCHAR2 (20),
 last_name VARCHAR2 (20));

begin
      for i in 1 .. 17 loop 
         INSERT into customers (customer_id,  first_name, last_name) 
      VALUES (i, '???', '???');
              end loop;
    end;
/

方案一：创建可复用的随机姓名函数 #

先定义存储姓名的对象类型，再创建返回随机姓名的函数，方便后续多次调用：

-- 创建存储姓名的对象类型
CREATE OR REPLACE TYPE name_rec IS OBJECT (
    first_name VARCHAR2(20),
    last_name VARCHAR2(20)
);
/

-- 生成随机姓名的函数
CREATE OR REPLACE FUNCTION get_random_name RETURN name_rec IS
    v_first_name VARCHAR2(20);
    v_last_name VARCHAR2(20);
BEGIN
    SELECT first_name, last_name
    INTO v_first_name, v_last_name
    FROM (
        WITH cte AS (SELECT level lvl FROM dual CONNECT BY level <=5)
        SELECT 
            CASE lvl
                WHEN 1 THEN 'Faith'
                WHEN 2 THEN 'Tom'
                WHEN 3 THEN 'Anna'
                WHEN 4 THEN 'Lisa'
                WHEN 5 THEN 'Andy'
            END first_name,
            CASE lvl
                WHEN 1 THEN 'Andrews'
                WHEN 2 THEN 'Thorton'
                WHEN 3 THEN 'Smith'
                WHEN 4 THEN 'Jones'
                WHEN 5 THEN 'Beirs'
            END last_name
        FROM cte
        ORDER BY dbms_random.value -- 简化排序逻辑，直接用随机值排序
    )
    WHERE ROWNUM = 1;
    
    RETURN name_rec(v_first_name, v_last_name);
END;
/

修改插入代码，调用函数获取随机姓名：

CREATE TABLE CUSTOMERS (
 customer_id NUMBER,
 first_name VARCHAR2 (20),
 last_name VARCHAR2 (20));

begin
    for i in 1 .. 17 loop 
        INSERT into customers (customer_id, first_name, last_name) 
        VALUES (i, get_random_name().first_name, get_random_name().last_name);
    end loop;
    COMMIT; -- 务必提交事务
end;
/

方案二：直接嵌入随机逻辑（无需创建函数） #

如果不需要长期复用，可直接将随机姓名查询嵌入INSERT语句，省去函数定义步骤：

CREATE TABLE CUSTOMERS (
 customer_id NUMBER,
 first_name VARCHAR2 (20),
 last_name VARCHAR2 (20));

begin
    for i in 1 .. 17 loop 
        INSERT into customers (customer_id, first_name, last_name) 
        SELECT i, first_name, last_name
        FROM (
            WITH cte AS (SELECT level lvl FROM dual CONNECT BY level <=5)
            SELECT 
                CASE lvl
                    WHEN 1 THEN 'Faith'
                    WHEN 2 THEN 'Tom'
                    WHEN 3 THEN 'Anna'
                    WHEN 4 THEN 'Lisa'
                    WHEN 5 THEN 'Andy'
                END first_name,
                CASE lvl
                    WHEN 1 THEN 'Andrews'
                    WHEN 2 THEN 'Thorton'
                    WHEN 3 THEN 'Smith'
                    WHEN 4 THEN 'Jones'
                    WHEN 5 THEN 'Beirs'
                END last_name
            FROM cte
            ORDER BY dbms_random.value
        )
        WHERE ROWNUM = 1;
    end loop;
    COMMIT;
end;
/

方案三：批量插入（高效生成大量数据） #

若需插入大量数据，循环插入效率较低，可使用批量生成的方式一次性插入：

CREATE TABLE CUSTOMERS (
 customer_id NUMBER,
 first_name VARCHAR2 (20),
 last_name VARCHAR2 (20));

INSERT INTO customers (customer_id, first_name, last_name)
SELECT 
    rownum,
    CASE TRUNC(dbms_random.value(1,6))
        WHEN 1 THEN 'Faith'
        WHEN 2 THEN 'Tom'
        WHEN 3 THEN 'Anna'
        WHEN 4 THEN 'Lisa'
        WHEN 5 THEN 'Andy'
    END first_name,
    CASE TRUNC(dbms_random.value(1,6))
        WHEN 1 THEN 'Andrews'
        WHEN 2 THEN 'Thorton'
        WHEN 3 THEN 'Smith'
        WHEN 4 THEN 'Jones'
        WHEN 5 THEN 'Beirs'
    END last_name
FROM dual
CONNECT BY level <=17; -- 生成17条数据

COMMIT;

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