PLSQL从动态字符串中提取值

DECLARE
  input_string VARCHAR2(100) := 'Name: John, Age: 30, Occupation: Engineer';
  name_value VARCHAR2(100);
  age_value NUMBER;
  occupation_value VARCHAR2(100);
BEGIN
  -- 提取姓名
  name_value := REGEXP_SUBSTR(input_string, 'Name: ([^,]+)', 1, 1, NULL, 1);
  
  -- 提取年龄
  age_value := TO_NUMBER(REGEXP_SUBSTR(input_string, 'Age: ([^,]+)', 1, 1, NULL, 1));
  
  -- 提取职业
  occupation_value := REGEXP_SUBSTR(input_string, 'Occupation: ([^,]+)', 1, 1, NULL, 1);
  
  -- 输出提取的值
  DBMS_OUTPUT.PUT_LINE('Name: ' || name_value);
  DBMS_OUTPUT.PUT_LINE('Age: ' || age_value);
  DBMS_OUTPUT.PUT_LINE('Occupation: ' || occupation_value);
END;
/