17,382
社区成员




create or replace procedure pipestoploop is
pipename constant varchar2(12) := 'balabala';
pipebuf varchar2(64);
resulter integer;
begin
--execute immediate 'grant execute on DBMS_LOCK to system';
--execute immediate 'grant execute on DBMS_PIPE to system';
resulter := dbms_pipe.create_pipe(pipename);
dbms_output.put_line('开始执行循环!');
loop
dbms_output.put_line('循环体中休息1s');
dbms_lock.sleep(1);
--status为0为成功可以UNPACK_MESSAGE,1为超时没有数据,2为信息太大,3为内部错误
resulter:=dbms_pipe.receive_message(pipename, 10);
if resulter = 0 then
dbms_pipe.unpack_message(pipebuf);
dbms_output.put_line(pipebuf);
exit when pipebuf = 'stop';
else
DBMS_OUTPUT.PUT_LINE('管道中现在没有信息返回');
DBMS_OUTPUT.PUT_LINE(resulter);
exit;
end if;
end loop;
end pipestoploop;
------------------------------------------------------------------------------------------------------------------------------
create or replace procedure pipestoploop2 is
pipename varchar2(64):='balabala';
resulter integer:=dbms_pipe.create_pipe(pipename);
status number;
begin
dbms_output.put_line(resulter);
dbms_output.put_line('发送stop命令!');
DBMS_PIPE.purge(pipename);
dbms_pipe.pack_message('stop');
status:=DBMS_PIPE.send_message(pipename);
dbms_output.put_line(status);
--DBMS_PIPE.PACK_MESSAGE(999999);
--status:=DBMS_PIPE.send_message(pipename);
end pipestoploop2;
-------------------------------------------------------------------------------------------------
SQL> exec pipestoploop;
SQL> exec pipestoploop2;
SQL>
开始执行循环!
循环体中休息1s
管道中现在没有信息返回
1
PL/SQL procedure successfully completed