oracle 多线程并发任务

oracle 创建多个定时任务job达到异步多线程的方式执行多个操作任务

   Procedure Trimspaceall Is
 Begin
   For Rec In (Select * From Tabs Where Regexp_Like(Table_Name, '[0-9]')) Loop
     --通过任务后台 模拟多线程
     Dbms_Scheduler.Create_Job(Job_Name   => 'Trimspace' || Rec.Table_Name,
                               Job_Type   => 'stored_procedure',
                               Job_Action => ' common.Trimspace(Tablename => ' ||
                                             Rec.Table_Name || ')',
                               Enabled    => True,
                               Auto_Drop  => True);
     Commit;
   End Loop;

 Procedure Trimspace(Tablename In Varchar2) Is
   As_Out Clob := Null;
   Type Table_Cols Is Table Of Cols%Rowtype Index By Binary_Integer;
   t_Table_Cols Table_Cols;
   v_Id         Err_Message.Id%Type;
   v_Sql        Varchar2(2000);
   Type Seq_Cur Is Ref Cursor;
   My_Seq_Cur Seq_Cur;
   v_Seq      Number;
   v_Rd       Rowid;
   i_Count    Number := 1;
 Begin
   Dbms_Lob.Createtemporary(As_Out, True);
   Select *
     Bulk Collect
     Into t_Table_Cols
     From Cols
    Where Table_Name = Upper(Tablename);
   If t_Table_Cols.Count <= 0 Then
     Return;
   End If;
   v_Id := Err_Seq.Nextval;
   Insert Into Err_Message
     (Id, Table_Name, Index_Key)
   Values
     (v_Id, Upper(Tablename), To_Char(Sysdate, 'yyyymmddHH24miss'));
   Dbms_Lob.Append(As_Out, ' update ');
   Dbms_Lob.Append(As_Out, Tablename);
   Dbms_Lob.Append(As_Out, ' set  ');
   For i In 1 .. t_Table_Cols.Count Loop
     If Upper(t_Table_Cols(i).Column_Name) = 'DATA_SEQ' Then
       Dbms_Lob.Append(As_Out, t_Table_Cols(i).Column_Name);
       Dbms_Lob.Append(As_Out, '=:v_seq');
     Else
       Dbms_Lob.Append(As_Out, t_Table_Cols(i).Column_Name);
       Dbms_Lob.Append(As_Out, '=trim(replace(replace(');
       Dbms_Lob.Append(As_Out, t_Table_Cols(i).Column_Name);
       Dbms_Lob.Append(As_Out, ',chr(10)),chr(13)))');
     End If;
     If i < t_Table_Cols.Count Then
       Dbms_Lob.Append(As_Out, ',');
     End If;
   End Loop;
   Dbms_Lob.Append(As_Out, ' where rowid =:v_rd');
   v_Sql := '  select a.rowid rd, row_number() over(partition by company order by company) row_number  from ' ||
            Tablename || ' a ';
   Open My_Seq_Cur For v_Sql;
   Loop
     Fetch My_Seq_Cur
       Into v_Rd, v_Seq;
     Exit When My_Seq_Cur%Notfound;
     Execute Immediate As_Out
       Using v_Seq, v_Rd;
     If i_Count = 10000 Then
       i_Count := 1;
       Commit;
     Else
       i_Count := i_Count + 1;
     End If;
   End Loop;
   --记录总条数
   Close My_Seq_Cur;
   Commit;
   Update Err_Message
      Set Message = To_Char(Sysdate, 'yyyymmddHH24miss')
    Where Id = v_Id;
 Exception
   When Others Then
     Update Err_Message Set Message = Geterrmsg Where Id = v_Id;
 End Trimspace;