強制SQL在限定時間內返回結果

設計出發點：

客戶的流水線上會有N個工人，每個工人守在流水線的一個岔路上。
這時要對每個工人的作業量進行平衡劃分，就需要對每個工人的手工剩餘工作量進行統計，然後把新的作業流向手頭工作量少的工人手裡。

但假如統計的時間很長，流水線上的作業還沒來得及分配岔口號，就會一直走到直線的岔口上，結果導致直線的岔口工作量很大，而其它岔口卻很空閒。
這時候就可以強制SQL統計在限定時間內必須返回結果；如果限定時間不能得到查詢結果，則可以忽略統計而考慮立刻分配隨機的岔口號已分流新的工作量。

 

實現思路：

下面列出整個邏輯的實現過程：
1）模擬需要各種時間來得到結果的程式碼：
create or replace function send_number ( id number) return number as
  v_result  number;
begin
  dbms_lock.sleep(id);
  return id;
end send_number;
–可根據輸入引數，來模擬需要固定時間的查詢

如：下面查詢就需要4秒來得到結果
SQL> select send_number(4) from dual;
 
SEND_NUMBER(4)
————–
             4
 
Executed in 4.041 seconds

2）生成agent程式來執行釋出的查詢，agent程式從mc$agent中獲得查詢的語句，然後將結果傳到mc$pipe管道中
create or replace procedure pipe_agent is
  n         number;
  v_result  number;
  i_sql     varchar2(4000);
begin
  while 1>0 loop
     i_sql:=null;
     v_result:=dbms_pipe.receive_message( pipename => 'mc$agent');
      if v_result=0 then
         while dbms_pipe.next_item_type>0 loop
         exit when i_sql is not null;
              dbms_pipe.unpack_message(i_sql);
         end loop;
      end if;
     dbms_output.put_line(i_sql);
     execute immediate i_sql into n;
     dbms_pipe.pack_message(item => n);
     v_result:=dbms_pipe.send_message(pipename => 'mc$pipe');
  end loop;
end pipe_agent;

3）函式pipe_return用於從mc$pipe中獲得查詢結果，如果在5秒內得到的話，就返回正常結果，如果超時，則返回空
即使之後查詢得到
create or replace function pipe_return(i_sql varchar2)  return number as
  v_message  number;
  v_result   number;
begin
    v_message:=null;
    dbms_pipe.purge(pipename => 'mc$agent');
    dbms_pipe.purge(pipename => 'mc$pipe');
    dbms_pipe.pack_message(item => i_sql);
    v_result:=dbms_pipe.send_message(pipename => 'mc$agent');
    v_result:=dbms_pipe.receive_message( pipename => 'mc$pipe',
                                         timeout => 5);
      if v_result=0 then
         while dbms_pipe.next_item_type>0 loop
         exit when v_message is not null;
              dbms_pipe.unpack_message(v_message);
         end loop;
      /*else
      如果超時，說明發布的SQL查詢超時；
      則發動另一個agent程式，以便以後的查詢使用
      這裡作為演示，不涉及更復雜的情況
      */
      end if;
   dbms_output.put_line(v_message);
   return v_message;
end pipe_return;

4)演示
開啟一個會話，傳送agent
begin
  pipe_agent;
end;

另起一個會話，進行演示
注意：實際需要的查詢sql語句，作為pipe_return的引數傳入
a）不超時的查詢
SQL> SQL> select pipe_return('select send_number(2) from dual') from dual;
 
PIPE_RETURN('SELECTSEND_NUMBER
——————————
                             2
 
Executed in 2.012 seconds
 

b）超時的查詢
SQL> select pipe_return('select send_number(6) from dual') from dual;
 
PIPE_RETURN('SELECTSEND_NUMBER
——————————
 
Executed in 5.024 seconds

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/10009036/viewspace-1061458/，如需轉載，請註明出處，否則將追究法律責任。