我有一个简单的表格：

CREATE TABLE "MY_TABLE" (
  id varchar2(255) NOT NULL PRIMARY KEY,
  name varchar2(255),
  website varchar2(500)
);

website的格式可以是https://www.google.com，如果是，那么我需要更新该行并将其设置为google.com。
然后，我需要计算有多少个name具有相同的更新域。我需要尽可能高效地完成这项工作，因此我正在尝试使用游标和函数。如果我有：

insert into MY_TABLE2(id, name, website) values ('1', 'a', 'dom1');
insert into MY_TABLE2(id, name, website) values ('2', 'b', 'dom2');
insert into MY_TABLE2(id, name, website) values ('3', 'c', 'dom1');
insert into MY_TABLE2(id, name, website) values ('4', 'd', null);

好的代码应该这样做：

select mt1.name AS "SPOT NAME",  (select count(mt2.id) 
                                   from my_table2 mt2 
                                   where  mt1.website = mt2.website  )
 from my_table2 mt1;

我有以下代码：
这将检查传递的website是否满足域的条件（假设regex是ok的）：

CREATE OR REPLACE FUNCTION is_domain_format(website my_table.website%type) RETURN varchar2 AS
v_is_in_domain_format boolean;
v_bool_as_varchar varchar2(5);
begin
    v_is_in_domain_format := regexp_like(website, '^[a-z0-9][-a-z.0-9]*[a-z0-9]$') and not regexp_like(website, '\.\.');
    IF v_is_in_domain_format THEN
        v_bool_as_varchar := 'true';
    ELSE
         v_bool_as_varchar := 'false';
    END IF;

    RETURN v_bool_as_varchar;
end is_domain_format;

然后我们将website实际格式化为域：

CREATE OR REPLACE FUNCTION formate_url (website_ my_table.website%type) RETURN varchar2 AS 
v_domain varchar2(255) default null;
BEGIN
  v_domain := REGEXP_REPLACE(website_, '(http[s]?://)?(www\.)?(.*?)((/|:)(.)*|$)', '\3');
  RETURN v_domain;
END formate_url;

然后我有一个嵌套表：

CREATE TYPE t_spot AS OBJECT (
  v_id varchar2(255),
  v_name varchar2(255),
  v_domain varchar2(255)
);

最后是一个流水线函数：

CREATE TYPE t_spot_tab IS TABLE OF t_spot;

create or replace function f_get_spots_piped
              return t_spot_tab PIPELINED as

    type r_spot is record ( v_id my_table.id%type,
                            v_website my_table.website%type);
 
   cursor c_spots_unformated is select id,website 
                                      from my_table 
                                      where website is not null and is_domain_format(website) = 'false';        

begin
  for spot_unformated_domain in c_spots_unformated loop
    update my_table set website = formate_url(spot_unformated_domain.website) where spot_unformated_domain.id = id;
  end loop;  

 for i in 1 .. 5 loop
  --this is only for a test...
  PIPE ROW (t_spot(1, 'a','b'));
 end loop;
 RETURN;
end;

我不明白为什么会出现以下错误：

ORA-14551: cannot perform a DML operation inside a query 
ORA-06512: at "SYS.F_GET_SPOTS_PIPED", line 13
ORA-06512: at "SYS.F_GET_SPOTS_PIPED", line 13
14551. 00000 -  "cannot perform a DML operation inside a query "
*Cause:    DML operation like insert, update, delete or select-for-update
           cannot be performed inside a query or under a PDML slave.
*Action:   Ensure that the offending DML operation is not performed or
           use an autonomous transaction to perform the DML operation within
           the query or PDML slave.

这是故意的还是我在某个地方犯了错？
我的意思是，如果这个异常是预期的，我怎么能写PL/SQL函数，将有效地更新和选择？
最后，我需要以下内容：

Name| Domain | Number_of_occurances_of_that_domain

因此，具有管道函数的查询应该看起来像这样：

select myTable.v_name as "Name", myTable.v_domain as "Domain", (select count(myTable1.v_id) 
                                                                    from f_get_spots_piped()   myTable1 where myTable.v_id=myTable1.v_id) as "Number_of_occurances_of_that_domain "

编辑1
谢谢@Litlefoot给我很好的解释！
然而，我已经尝试了这两种选择，他们都失败了，与以前类似的信息。
尝试1。
在管道函数中添加了pragma_transaction，如下所示：

create or replace function f_get_spots_piped
                  return t_spot_tab PIPELINED as
    
     pragma autonomous_transaction;

尝试2.
创建一个将执行更新的过程，然后从管道函数调用它。

create or replace procedure update_spots as 
    
    pragma autonomous_transaction; 
    type r_spot is record ( v_id my_table.id%type,
                            v_website my_table.website%type);
 
   cursor c_spots_unformated is select id,website 
                                      from my_table 
                                      where website is not null and is_domain_format(website) = 'false';        

begin
    
  for spot_unformated_domain in c_spots_unformated loop
    update my_table set website = formate_url(spot_unformated_domain.website) where spot_unformated_domain.id = id;
  end loop;  

end;

和原始功能：

CREATE TYPE t_spot_tab IS TABLE OF t_spot;

create or replace function f_get_spots_piped
              return t_spot_tab PIPELINED as
    
                                       

begin

  update_spots;

 for i in 1 .. 5 loop
  PIPE ROW (t_spot('a','b'));
 end loop;
 RETURN;
end;

当我这样做时，它失败了：select * from f_get_spots_piped() ;
再次感谢你的回答，希望你能从这里给我指出一个好的方向，因为我浪费了很多时间来设置这个查询：（