oracle里面的terminate,Terminate Intentionally Infinite Loop In Oracle

To terminate an intentionally infinite loop in Oracle, we can use DBMS_PIPE.

First, create a pipe.

DBMS_PIPE.create_pipe(pipename);

Then, insert message handling into loop block.

if DBMS_PIPE.receive_message(pipename,0) = 0 then

DBMS_PIPE.unpack_message(pipebuf);

Exit when pipebuf = 'stop';

end if;

When needed, send a message which content is "stop" to the pipe.

DBMS_PIPE.create_pipe(pipename);

DBMS_PIPE.pack_message('stop');

So the whole example is:

DECLARE

pipename CONSTANT VARCHAR2(12) := 'signaler';

result INTEGER;

pipebuf VARCHAR2(64);

BEGIN

result := DBMS_PIPE.create_pipe(pipename);

LOOP

xxxxx

DBMS_LOCK.sleep(10);

IF DBMS_PIPE.receive_message(pipename,0) = 0

THEN

DBMS_PIPE.unpack_message(pipebuf);

EXIT WHEN pipebuf = 'stop';

END IF;

END LOOP;

END;

DECLARE

pipename VARCHAR2(12) := 'signaler';

result INTEGER := DBMS_PIPE.create_pipe(pipename);

BEGIN

DBMS_PIPE.pack_message('stop');

END;