租户端 数据库 TDSQL PostgreSQL 版 开发指南 forall改写

forall改写

最近更新时间: 2024-06-12 15:06:00

  • Oracle语法。

     create table t5(f1 int,f2 int);
 insert into t5 values(1,1);
 insert into t5 values(2,2);
 commit;

 create table t6(f1 int,f2 int);

 create or replace procedure p_forall is 
     TYPE t5list IS TABLE OF t5.f2%TYPE;
     t5s t5list;
 BEGIN
     SELECT f2 BULK COLLECT INTO t5s FROM t5;    
     FORALL i IN t5s.FIRST .. t5s.LAST            
         insert into t6 values(t5s(i),t5s(i));   
     commit; 
 END;
 /

  • TDSQL-PG改写。

     create or replace procedure p_forall is
     TYPE t5list IS TABLE OF t5.f2%TYPE;
     t5s t5list;
 BEGIN
     SELECT f2 BULK COLLECT INTO t5s FROM t5;    
     FOR i IN t5s.FIRST .. t5s.LAST
     LOOP        
         insert into t6 values(t5s[i],t5s[i]);   
     END LOOP; 
     commit; 
 END;
 /