sql

PROCEDURE Bsorder_Ti_To_Tscs IS

    CURSOR Cur_Bsorder_Header_Ti IS
      SELECT Id,
             Bs_No,
             Line_Name,
             Contract_No,
             Wip_Id,
             Vendor_Id,
             Price,
             Restore_State,
             Sugg_Qty,
             Order_State,
             Pr_Header_No,
             Pr_Line_No,
             Po_Header_No,
             Po_Line_No,
             Ec_Id,
             Remark,
             Created_By,
             Creation_Date,
             Last_Updated_By,
             Last_Update_Date,
             Last_Update_Login,
             Start_Date,
             Pro_State,
             Order_Type,
             So_No,
             Description
        FROM Tsp_Sale_Order_Header_Ti Ti
       WHERE NOT EXISTS (SELECT 1 FROM Tscs_Sale_Order_Header_t t WHERE Ti.Id = t.Id);

    l_Bsorder_Header_Ti Cur_Bsorder_Header_Ti%ROWTYPE;

    CURSOR Cur_Bsorder_Line_Ti(p_Id NUMBER) IS

      SELECT Lti.Id,
             Lti.Order_Id,
             Lti.Inventory_Item_Id,
             Lti.Organization_Id,
             Lti.Use_Quantity,
             Lti.Theory_Issue_Quantity,
             Lti.Fact_Issue_Quantity,
             Lti.Erp_Meet_State,
             Lti.Remark,
             Lti.Created_By,
             Lti.Creation_Date,
             Lti.Last_Updated_By,
             Lti.Last_Update_Date,
             Lti.Last_Update_Login,
             Lti.Befor_Taxes_Unit_Price,
             Lti.After_Taxes_Unit_Price,
             Lti.Befor_Taxes_Total_Price,
             Lti.After_Taxes_Total_Price,
             Lti.Rate,
             Lti.Moq,
             Lti.Term_Name,
             Lti.Send_Date,
             Lti.Description,
             Lti.Segment1
        FROM Tsp_Sale_Order_List_Ti Lti, Tsp_Sale_Order_Header_Ti Hti
       WHERE Hti.Id = p_Id;

    l_Bsorder_Line_Ti Cur_Bsorder_Line_Ti%ROWTYPE;
  BEGIN
    --WIRTE BACK APPROVED
    FOR l_Bsorder_Header_Ti IN Cur_Bsorder_Header_Ti
  LOOP

      INSERT INTO Tscs_Sale_Order_Header_t
        (Id,
         Bs_No,
         Line_Name,
         Contract_No,
         Wip_Id,
         Vendor_Id,
         Price,
         Restore_State,
         Sugg_Qty,
         Order_State,
         Pr_Header_No,
         Pr_Line_No,
         Po_Header_No,
         Po_Line_No,
         Ec_Id,
         Remark,
         Created_By,
         Creation_Date,
         Last_Updated_By,
         Last_Update_Date,
         Last_Update_Login,
         Start_Date,
         Pro_State,
         Order_Type,
         Description)
      VALUES
        (l_Bsorder_Header_Ti.Id,
         l_Bsorder_Header_Ti.Bs_No,
         l_Bsorder_Header_Ti.Line_Name,
         l_Bsorder_Header_Ti.Contract_No,
         l_Bsorder_Header_Ti.Wip_Id,
         l_Bsorder_Header_Ti.Vendor_Id,
         l_Bsorder_Header_Ti.Price,
         l_Bsorder_Header_Ti.Restore_State,
         l_Bsorder_Header_Ti.Sugg_Qty,
         l_Bsorder_Header_Ti.Order_State,
         l_Bsorder_Header_Ti.Pr_Header_No,
         l_Bsorder_Header_Ti.Pr_Line_No,
         l_Bsorder_Header_Ti.Po_Header_No,
         l_Bsorder_Header_Ti.Po_Line_No,
         l_Bsorder_Header_Ti.Ec_Id,
         l_Bsorder_Header_Ti.Remark,
         l_Bsorder_Header_Ti.Created_By,
         l_Bsorder_Header_Ti.Creation_Date,
         l_Bsorder_Header_Ti.Last_Updated_By,
         l_Bsorder_Header_Ti.Last_Update_Date,
         l_Bsorder_Header_Ti.Last_Update_Login,
         l_Bsorder_Header_Ti.Start_Date,
         l_Bsorder_Header_Ti.Pro_State,
         l_Bsorder_Header_Ti.Order_Type,
         l_Bsorder_Header_Ti.Description);
      COMMIT;
      FOR l_Bsorder_Line_Ti IN Cur_Bsorder_Line_Ti(l_Bsorder_Header_Ti.Id)
      LOOP

        INSERT INTO Tscs_Sale_Order_List_t
          (Id,
           Order_Id,
           Inventory_Item_Id,
           Organization_Id,
           Use_Quantity,
           Theory_Issue_Quantity,
           Fact_Issue_Quantity,
           Erp_Meet_State,
           Remark,
           Created_By,
           Creation_Date,
           Last_Updated_By,
           Last_Update_Date,
           Last_Update_Login,
           Befor_Taxes_Unit_Price,
           After_Taxes_Unit_Price,
           Befor_Taxes_Total_Price,
           After_Taxes_Total_Price,
           Rate,
           Moq,
           Term_Name,
           Send_Date,
           Description)
        VALUES
          (l_Bsorder_Line_Ti.Id,
           l_Bsorder_Line_Ti.Order_Id,
           l_Bsorder_Line_Ti.Inventory_Item_Id,
           l_Bsorder_Line_Ti.Organization_Id,
           l_Bsorder_Line_Ti.Use_Quantity,
           l_Bsorder_Line_Ti.Theory_Issue_Quantity,
           l_Bsorder_Line_Ti.Fact_Issue_Quantity,
           l_Bsorder_Line_Ti.Erp_Meet_State,
           l_Bsorder_Line_Ti.Remark,
           l_Bsorder_Line_Ti.Created_By,
           l_Bsorder_Line_Ti.Creation_Date,
           l_Bsorder_Line_Ti.Last_Updated_By,
           l_Bsorder_Line_Ti.Last_Update_Date,
           l_Bsorder_Line_Ti.Last_Update_Login,
           l_Bsorder_Line_Ti.Befor_Taxes_Unit_Price,
           l_Bsorder_Line_Ti.After_Taxes_Unit_Price,
           l_Bsorder_Line_Ti.Befor_Taxes_Total_Price,
           l_Bsorder_Line_Ti.After_Taxes_Total_Price,
           l_Bsorder_Line_Ti.Rate,
           l_Bsorder_Line_Ti.Moq,
           l_Bsorder_Line_Ti.Term_Name,
           l_Bsorder_Line_Ti.Send_Date,
           l_Bsorder_Line_Ti.Description);
        COMMIT;
      END LOOP;
    END LOOP;

  EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;

  END Bsorder_Ti_To_Tscs;


--command 中创建job

variable jobtelbook number;
begin
dbms_job.submit(:jobtelbook,'p_telbook_toempl;',sysdate,'sysdate+1/1440');
end;
/

--查看允许job运行的个数
show  parameter  job_queue_process;
--修改允许运行job个数
ALTER SYSTEM SET job_queue_processes = 20;

--本数据库中定义到任务队列中的任务
select * from DBA_JOBS_RUNNING   
---目前正在运行的任务
select * from DBA_JOBS   

-- 当前用户拥有的任务    
select * from  USER_JOBS

--停止正在运行的JOB
execute   dbms_job.broken(21,true);


create or replace procedure P_Telbook_ToEmpl is

CURSOR Cur_Telbook is
select t.eiid,
       t.nname,
       t.intel,
       t.mobile,
       t.outtel,
       t.dept
       from telbook t;
l_Telbook Cur_Telbook%ROWTYPE;
begin
delete  ts_employee t where
t.employee_id<0;
commit;
FOR l_Telbook IN Cur_Telbook
   
    LOOP
   
    insert into ts_employee
(employee_id,
employee_name,
telephone,
attribute5,
mobile,
org_name,
old_employee_id,
is_pass,
update_date)
values
(-(TELBOOK_SEQUENCE.NEXTVAL),
    l_Telbook.Nname,
    l_Telbook.Outtel,
    l_Telbook.Intel,
    l_Telbook.Mobile,
    l_Telbook.Dept,
    -(l_Telbook.Eiid),
    'Y',
    to_char(sysdate,'yyyy-mm-dd hh:mm:ss'));
   END LOOP;
   commit;


 
end P_Telbook_ToEmpl;

--创建sequence
create sequence TELBOOK_SEQUENCE
minvalue 1
maxvalue 9999999999999999999999999999
start with 182025
increment by 1
cache 20;

---递归查询应用--
SELECT distinct *
  FROM t_news_section
where is_active = 'Y'
START WITH id in (316,317)
CONNECT BY PRIOR parent_id = id