第12章 会话间通信(Intersession Communication)
会话是单独的工作区.会话是从你连接数据库开始到断开连接.
有几种方法来让你在会话之间进行通信.
会话间通信可以实时同步离散的会话,并且可快速配置,但是事件传播的延迟(一般用AQ来解决)会带来负面影响.
本章主要讨论内置工具DBMS_PIPE和DBMS_ALERT.
要求永久或临时结构
永久或临时结构可以让你:
1.你可以使用DBMS_AQADM和DBMS_AQ包来完成AQ(Advanced Queuing).
它们包含了给每个参与者进行AQ的配置.然后你可以使用这些信息在会话间进行交换.
2.你可以使用表、权限、同义词来在会话间交换数据.这种方案比较简单,但是受到事务控制的限制.
一个事务必须完成并且提交改变.
不要求永久或临时结构
不需要永久或临时结构的时候,你可以:
1.你可以使用DBMS_PIPE内置包.
DBMS_PIPE使用SGA中叫pipes的动态内存结构.它和unix的管道很相似.
管道可以是本地的、私有的、或者公开的.它们以FIFO(先进先出)队列工作.
事务控制问题不能束缚它们。
你可以在会话间使用管道来异步地发送和接收数据.
2.你可以使用DBMS_ALERT内置包.
DBMS_ALERT也是使用SGA中的内存结构.这种结构没有正式的称为管道,它像公共管道一样工作.
它也和unix的管道很相似.它是可以公共访问的管道,或者FIFO队列.
这些管道一般用在事件触发器中,并且收到事务控制的限制.
这种警报管道在一个事件的结束会异步地在会话间通讯.
会话间通信方法对比
你应当理解什么时候和时候地方使用这些方法.
你不应该去使用永久或临时的结构来交换信息,使用这些类型的结构会导致文件访问,
这样会降低应用的性能.会话间通信应当尽量在内存中处理.
DBMS_PIPE和DBMS_ALERT包都工作在内存中,所以是我们的主要选择.
管道以两种方式来被定义支持会话间通信:
1.管道能支持单个用户的两个或多个会话间的通信.
2.可选的,它们能支持两个或多个用户间的通信.
DBMS_ALERT以一种异步事务控制机制的方式工作.它在以一个事件来通知订阅者.
订阅者随后对事件采取行动.
当你使用发布和订阅处理,轮询守护进程(后台进程)就被淘汰了.
DBMS_PIPE内置包
DBMS_PIPE有本地会话,私有用户和特殊公共管道.
在同样的会话中用多种类型可能导致一些问题.
本地会话管道是一个私有的缓冲区.不幸的是,同样的私有缓冲区服务也是用户私有和公共管道访问的接口.
私有管道和公共管道都是SGA的结构.
本地管道就是一个包含一个值的缓冲区.用户私有管道可以包含很多值在一个FIFO队列.
创建管道时,公共管道是缺省的管道.(使用CREATE_PIPE创建时,则此时缺省为私有管道)
此外,公共管道是设计用来在两个用户直接共享变量的.
CREATE_PIPE函数
原型:
CREATE_PIPE(pipe_name [, maxpipe_size [, private]])
CREATE_PIPE函数返回INTEGER数据类型,并且限制SQL方式访问.
它有三个参数:
1.PIPENAME
VARCHAR2类型.最大为128字节.你不应使用ORA$作为管道名前缀,因为ORA$是Oracle公司自己使用的.
2.MAXPIPESIZE
INTEGER类型,默认值为8192
3.PRIVATE
BOOLEAN类型,缺省为TRUE
如果使用CREATE_PIPE创建管道时,发现管道已存在,该函数不会更改已存在的管道.
它会返回0.(0表示成功完成,但此时并没有创建管道)
NEXT_ITEM_TYPE函数
原型:
NEXT_ITEM_TYPE(message_item)
NEXT_ITEM_TYPE没有参数.它可通过SQL访问.
它读取本地管道或缓冲区的内容.返回类型为INTEGER。
如果你清空了本地缓冲区,当你尝试安全返回一个值时将获得ORA-06556错误.
返回值 意思
0 空缓冲区
6 NUMBER类型
9 VARCHAR2类型
11 ROWID类型
12 DATE类型
23 RAW类型
PACK_MESSAGE存储
原型:
PACK_MESSAGE(message_item)
它将传入的值放到本地管道或缓冲区.
PURGE存储
原型:
PURGE(pipe_name)
清空管道,pipe_name必须是一个有效的私有或公共管道名.
RECEIVE_MESSAGE函数
原型:
RECEIVE_MESSAGE(pipe_name [, time_out_value])
它读取指定管道的内容,并将它转换到本地缓冲区.
REMOVE_PIPE函数
原型:
REMOVE_PIPE(pipe_name)
删除管道,pipe_name必须是一个有效的私有或公共管道名.
RESET_BUFFER存储
它没有参数,用来删除本地缓冲区的内容.
关于DBMS_PIPE包内的函数与存储的说明及使用,请查看官方文档:
http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_pipe.htm#ARPLS67407
DBMS_PIPE包使用示例:
--本地缓冲区的读写示例(跨会话的),缺省为公共管道,跨用户可访问
21:35:19 SCOTT@orcl> DECLARE
22:18:08 2 message VARCHAR2(30);
22:18:08 3 success INTEGER;
22:18:08 4 BEGIN
22:18:08 5 DBMS_OUTPUT.PUT_LINE('当前会话ID: ' || USERENV('SID'));
22:18:08 6 message := DBMS_PIPE.UNIQUE_SESSION_NAME; --获得一个唯一的会话名
22:18:08 7 DBMS_PIPE.RESET_BUFFER; --重置缓冲区
22:18:08 8 DBMS_PIPE.PACK_MESSAGE('我是管道测试信息'); --包装一个message到本地缓冲区
22:18:08 9 success := DBMS_PIPE.SEND_MESSAGE('cryking_pipe'); --使用pipe_cryking作为管道名发送信息
22:18:08 10 DBMS_OUTPUT.PUT_LINE('Written to pipe [' || message || ']');
22:18:08 11 END;
22:18:09 12 /
当前会话ID: 14
Written to pipe [ORA$PIPE$000E00250001]
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.08
22:18:46 SCOTT@orcl> declare
22:19:24 2 message VARCHAR2(30);
22:19:24 3 success INTEGER;
22:19:24 4 BEGIN
22:19:24 5 DBMS_OUTPUT.PUT_LINE('当前会话ID: ' || USERENV('SID'));
22:19:24 6 success := DBMS_PIPE.RECEIVE_MESSAGE('cryking_pipe', 0); --从管道pipe_cryking接收信息
22:19:24 7 DBMS_PIPE.UNPACK_MESSAGE(message); --解包信息
22:19:24 8 DBMS_OUTPUT.PUT_LINE('Message [' || message || ']');
22:19:24 9 END;
22:19:24 10 /
当前会话ID: 17
Message [我是管道测试信息]
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
--注意读取后的信息,不能再次读取了.(FIFO队列特性)
22:19:34 SCOTT@orcl> /
当前会话ID: 17
DECLARE
*
第 1 行出现错误:
ORA-06556: 管道为空, 无法实现 unpack_message 请求
ORA-06512: 在 "SYS.DBMS_PIPE", line 80
ORA-06512: 在 line 7
--本地缓冲区的读写示例 跨用户的
22:32:25 SCOTT@orcl> DECLARE
22:35:32 2 message VARCHAR2(30);
22:35:32 3 success INTEGER;
22:35:32 4 BEGIN
22:35:32 5 DBMS_OUTPUT.PUT_LINE('当前用户: ' || user);
22:35:32 6 message := DBMS_PIPE.UNIQUE_SESSION_NAME; --获得一个唯一的会话名
22:35:32 7 DBMS_PIPE.RESET_BUFFER; --重置缓冲区
22:35:32 8 DBMS_PIPE.PACK_MESSAGE('我是管道测试信息'); --包装一个message到本地缓冲区
22:35:32 9 success := DBMS_PIPE.SEND_MESSAGE('cryking_pipe'); --使用pipe_cryking作为管道名发送信息
22:35:32 10 DBMS_OUTPUT.PUT_LINE('Written to pipe [' || message || ']');
22:35:32 11 END;
22:35:33 12 /
当前用户: SCOTT
Written to pipe [ORA$PIPE$000E00250001]
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
22:33:02 CRY@orcl> declare
22:36:22 2 message VARCHAR2(30);
22:36:22 3 success INTEGER;
22:36:22 4 BEGIN
22:36:22 5 DBMS_OUTPUT.PUT_LINE('当前用户: ' || user);
22:36:22 6 success := DBMS_PIPE.RECEIVE_MESSAGE('cryking_pipe', 0); --从管道pipe_cryking接收信息
22:36:22 7 DBMS_PIPE.UNPACK_MESSAGE(message); --解包信息
22:36:22 8 DBMS_OUTPUT.PUT_LINE('Message [' || message || ']');
22:36:22 9 END;
22:36:23 10 /
当前用户: CRY
Message [我是管道测试信息]
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.03
22:36:24 CRY@orcl> /
当前用户: CRY
declare
*
ERROR at line 1:
ORA-06556: the pipe is empty, cannot fulfill the unpack_message request
ORA-06512: at "SYS.DBMS_PIPE", line 80
ORA-06512: at line 7
Elapsed: 00:00:00.06
创建管道
有2种类型的命名管道:一是私有命名管道;另一种是公共命名管道.
--创建私有命名管道示例
22:35:35 SCOTT@orcl> DECLARE
22:40:25 2 message_pipe VARCHAR2(30) := 'SCOTT$MESSAGE_INBOX';
22:40:25 3 message_size INTEGER := 20000;
22:40:25 4 retval INTEGER;
22:40:25 5 BEGIN
22:40:25 6 -- 定义一个私有管道
22:40:25 7 retval := DBMS_PIPE.CREATE_PIPE(message_pipe, message_size);
22:40:25 8 IF (retval = 0) THEN
22:40:25 9 DBMS_OUTPUT.PUT_LINE('MESSAGE_INBOX pipe is created.');
22:40:25 10 END IF;
22:40:25 11 EXCEPTION
22:40:25 12 WHEN others THEN
22:40:25 13 DBMS_OUTPUT.PUT_LINE(SQLERRM);
22:40:25 14 RETURN;
22:40:25 15 END;
22:40:26 16 /
MESSAGE_INBOX pipe is created.
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
如果此时我们用另外一个用户再创建同样一个名称的管道,会得到一个ORA-23322错误.
22:40:50 CRY@orcl> DECLARE
22:40:57 2 message_pipe VARCHAR2(30) := 'SCOTT$MESSAGE_INBOX';
22:40:57 3 message_size INTEGER := 20000;
22:40:57 4 retval INTEGER;
22:40:57 5 BEGIN
22:40:57 6 -- 定义一个私有管道
22:40:57 7 retval := DBMS_PIPE.CREATE_PIPE(message_pipe, message_size);
22:40:57 8 IF (retval = 0) THEN
22:40:57 9 DBMS_OUTPUT.PUT_LINE('MESSAGE_INBOX pipe is created.');
22:40:57 10 END IF;
22:40:57 11 EXCEPTION
22:40:57 12 WHEN others THEN
22:40:57 13 DBMS_OUTPUT.PUT_LINE(SQLERRM);
22:40:57 14 RETURN;
22:40:57 15 END;
22:40:58 16 /
ORA-23322: Privilege error accessing pipe
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.11
私有命名管道只能由创建它的用户来改变,任何其他用户改变它都会获得一个Privilege error.
如:(接上面的示例)
--用户CRY不能对SCOTT创建的管道SCOTT$MESSAGE_INBOX进行删除
22:46:10 CRY@orcl> DECLARE
22:46:10 2 retval INTEGER := DBMS_PIPE.REMOVE_PIPE('SCOTT$MESSAGE_INBOX');
22:46:10 3 BEGIN
22:46:10 4 NULL;
22:46:10 5 END;
22:46:11 6 /
DECLARE
*
ERROR at line 1:
ORA-23322: Privilege error accessing pipe
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_PIPE", line 132
ORA-06512: at line 2
Elapsed: 00:00:00.01
--SCOTT自己可以删除自己创建的私有管道
22:46:16 SCOTT@orcl> DECLARE
22:46:16 2 retval INTEGER := DBMS_PIPE.REMOVE_PIPE('SCOTT$MESSAGE_INBOX');
22:46:16 3 BEGIN
22:46:16 4 NULL;
22:46:16 5 END;
22:46:23 6 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.04
创建一个公共命名管道
23:04:57 SCOTT@orcl> DECLARE
23:04:59 2 message_pipe VARCHAR2(30) := 'SCOTT$MESSAGE_INBOX_PUBLIC';
23:04:59 3 message_size INTEGER := 20000;
23:04:59 4 message_flag BOOLEAN := false;
23:04:59 5 retval INTEGER;
23:04:59 6 BEGIN
23:04:59 7 DBMS_OUTPUT.PUT_LINE('当前用户: ' || user);
23:04:59 8 --定义一个公共管道
23:04:59 9 retval := DBMS_PIPE.CREATE_PIPE(message_pipe, message_size, message_flag);
23:04:59 10 IF (retval = 0) THEN
23:04:59 11 DBMS_OUTPUT.PUT_LINE('MESSAGE_INBOX_PUBLIC pipe is created.');
23:04:59 12 END IF;
23:04:59 13 EXCEPTION
23:04:59 14 WHEN others THEN
23:04:59 15 DBMS_OUTPUT.PUT_LINE(SQLERRM);
23:04:59 16 RETURN;
23:04:59 17 END;
23:05:02 18 /
当前用户: SCOTT
MESSAGE_INBOX_PUBLIC pipe is created.
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.04
--在另一个用户下删除公共管道
23:03:02 CRY@orcl> DECLARE
23:06:17 2 retval INTEGER;
23:06:17 3 BEGIN
23:06:17 4 DBMS_OUTPUT.PUT_LINE('当前用户: ' || user);
23:06:17 5 retval := DBMS_PIPE.REMOVE_PIPE('SCOTT$MESSAGE_INBOX_PUBLIC');
23:06:17 6 DBMS_OUTPUT.PUT_LINE('公有管道删除成功');
23:06:17 7 END;
23:06:18 8 /
当前用户: CRY
公有管道删除成功
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.05
私有管道和公共管道的读写信息的方式都一致.
你将数据放到本地缓冲区,然后通过命名管道发送.然后你使用相反的过程来读取数据.
使用命名管道读写数据,注意此时没有使用CREATE_PIPE来创建管道,则此时发送数据默认采用公共管道
--公共管道下SCOTT用户发送信息
23:05:04 SCOTT@orcl> DECLARE
23:17:45 2 line_return VARCHAR2(1) := CHR(10);
23:17:45 3 flag INTEGER;
23:17:45 4 BEGIN
23:17:45 5 -- 先清空管道内容
23:17:45 6 dbms_pipe.purge('SCOTT$MESSAGE_INBOX');
23:17:45 7 DBMS_OUTPUT.PUT_LINE('Input Message to Pipe');
23:17:45 8 DBMS_OUTPUT.PUT_LINE('---------------------');
23:17:45 9 --PACK_MESSAGE将信息放到本地缓冲区,SEND_MESSAGE用指定管道名发送信息
23:17:45 10 FOR i IN 1 .. 3 LOOP
23:17:45 11 DBMS_OUTPUT.PUT_LINE('SendMessage :[' || i || ']');
23:17:45 12 DBMS_PIPE.PACK_MESSAGE('SendMessage:[' || i || ']' || line_return);
23:17:45 13 flag := DBMS_PIPE.SEND_MESSAGE('SCOTT$MESSAGE_INBOX');
23:17:45 14 END LOOP;
23:17:45 15 IF (flag = 0) THEN
23:17:45 16 DBMS_OUTPUT.PUT_LINE('Message sent to SCOTT$MESSAGE_INBOX.');
23:17:45 17 END IF;
23:17:45 18 END;
23:17:46 19 /
Input Message to Pipe
---------------------
SendMessage :[1]
SendMessage :[2]
SendMessage :[3]
Message sent to SCOTT$MESSAGE_INBOX.
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.04
--使用公共管道接收信息,注意是不同的用户
23:06:19 CRY@orcl> DECLARE
23:20:42 2 line_return VARCHAR2(1) := CHR(10);
23:20:42 3 message VARCHAR2(4000);
23:20:42 4 output VARCHAR2(4000);
23:20:42 5 flag INTEGER;
23:20:42 6 BEGIN
23:20:42 7 DBMS_PIPE.RESET_BUFFER;--重置本地缓冲区
23:20:42 8 DBMS_OUTPUT.PUT_LINE('当前用户: ' || user);
23:20:42 9 DBMS_OUTPUT.PUT(line_return);
23:20:42 10 DBMS_OUTPUT.PUT_LINE('Output Message from Pipe');
23:20:42 11 DBMS_OUTPUT.PUT_LINE('------------------------');
23:20:42 12 FOR i IN 1 .. 3 LOOP
23:20:42 13 flag := DBMS_PIPE.RECEIVE_MESSAGE('SCOTT$MESSAGE_INBOX', 0);
23:20:42 14 -- Read message from local buffer.
23:20:42 15 DBMS_PIPE.UNPACK_MESSAGE(message);
23:20:42 16 output := output || message;
23:20:42 17 END LOOP;
23:20:42 18 IF (flag = 0) THEN
23:20:42 19 DBMS_OUTPUT.PUT(output);
23:20:42 20 DBMS_OUTPUT.PUT_LINE('Message received from SCOTT$MESSAGE_INBOX.');
23:20:42 21 END IF;
23:20:42 22 END;
23:20:43 23 /
当前用户: CRY
Output Message from Pipe
------------------------
SendMessage:[1]
SendMessage:[2]
SendMessage:[3]
Message received from SCOTT$MESSAGE_INBOX.
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.04
如果采用私有管道来发送数据,我们来看看不同会话和不同用户的情况
--不同用户,私有管道无法正常发送和接收数据
23:28:01 SCOTT@orcl> DECLARE
23:34:00 2 line_return VARCHAR2(1) := CHR(10);
23:34:00 3 flag INTEGER;
23:34:00 4 retval INTEGER;
23:34:00 5 BEGIN
23:34:00 6 retval := DBMS_PIPE.CREATE_PIPE('SCOTT$MESSAGE_INBOX_private', 20000); --创建私有管道
23:34:00 7 DBMS_OUTPUT.PUT_LINE('当前用户: ' || user);
23:34:00 8 DBMS_OUTPUT.PUT_LINE('当前会话ID: ' || USERENV('SID'));
23:34:00 9 DBMS_OUTPUT.PUT_LINE('Input private_Message to Pipe');
23:34:00 10 DBMS_OUTPUT.PUT_LINE('---------------------');
23:34:00 11 --PACK_MESSAGE将信息放到本地缓冲区,SEND_MESSAGE用指定管道名发送信息
23:34:00 12 FOR i IN 1 .. 3 LOOP
23:34:00 13 DBMS_OUTPUT.PUT_LINE('SendMessage :[' || i || ']');
23:34:00 14 DBMS_PIPE.PACK_MESSAGE('SendMessage:[' || i || ']' || line_return);
23:34:00 15 flag := DBMS_PIPE.SEND_MESSAGE('SCOTT$MESSAGE_INBOX_private');
23:34:00 16 END LOOP;
23:34:00 17 IF (flag = 0) THEN
23:34:00 18 DBMS_OUTPUT.PUT_LINE('private_Message sent to SCOTT$MESSAGE_INBOX_private.');
23:34:00 19 END IF;
23:34:00 20 END;
23:34:01 21 /
当前用户: SCOTT
当前会话ID: 14
Input private_Message to Pipe
---------------------
SendMessage :[1]
SendMessage :[2]
SendMessage :[3]
private_Message sent to SCOTT$MESSAGE_INBOX_private.
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
--使用不同用户接收数据时出现错误
23:28:34 CRY@orcl> DECLARE
23:36:03 2 line_return VARCHAR2(1) := CHR(10);
23:36:03 3 message VARCHAR2(4000);
23:36:03 4 output VARCHAR2(4000);
23:36:03 5 flag INTEGER;
23:36:03 6 BEGIN
23:36:03 7 DBMS_PIPE.RESET_BUFFER;--重置本地缓冲区
23:36:03 8 DBMS_OUTPUT.PUT_LINE('当前用户: ' || user);
23:36:03 9 DBMS_OUTPUT.PUT_LINE('当前会话ID: ' || USERENV('SID'));
23:36:03 10 DBMS_OUTPUT.PUT(line_return);
23:36:03 11 DBMS_OUTPUT.PUT_LINE('Output private_Message from Pipe');
23:36:03 12 DBMS_OUTPUT.PUT_LINE('------------------------');
23:36:03 13 FOR i IN 1 .. 3 LOOP
23:36:03 14 flag := DBMS_PIPE.RECEIVE_MESSAGE('SCOTT$MESSAGE_INBOX_private', 0);
23:36:03 15 -- Read message from local buffer.
23:36:03 16 DBMS_PIPE.UNPACK_MESSAGE(message);
23:36:03 17 output := output || message;
23:36:03 18 END LOOP;
23:36:03 19 IF (flag = 0) THEN
23:36:03 20 DBMS_OUTPUT.PUT(output);
23:36:03 21 DBMS_OUTPUT.PUT_LINE('Message received from SCOTT$MESSAGE_INBOX_private.');
23:36:03 22 END IF;
23:36:03 23 END;
23:36:03 24 /
当前用户: CRY
当前会话ID: 17
Output private_Message from Pipe
------------------------
DECLARE
*
ERROR at line 1:
ORA-23322: Privilege error accessing pipe
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_PIPE", line 176
ORA-06512: at line 14
Elapsed: 00:00:00.01
---私有管道在相同用户下可以发送和接收数据
--注意发送数据的会话ID为14,这里接收的会话ID为17,用户都是SCOTT
23:36:05 CRY@orcl> conn scott/tiger@orcl
Connected.
23:36:47 SCOTT@orcl> DECLARE
23:37:08 2 line_return VARCHAR2(1) := CHR(10);
23:37:08 3 message VARCHAR2(4000);
23:37:08 4 output VARCHAR2(4000);
23:37:08 5 flag INTEGER;
23:37:08 6 BEGIN
23:37:08 7 DBMS_PIPE.RESET_BUFFER;--重置本地缓冲区
23:37:08 8 DBMS_OUTPUT.PUT_LINE('当前用户: ' || user);
23:37:08 9 DBMS_OUTPUT.PUT_LINE('当前会话ID: ' || USERENV('SID'));
23:37:08 10 DBMS_OUTPUT.PUT(line_return);
23:37:08 11 DBMS_OUTPUT.PUT_LINE('Output private_Message from Pipe');
23:37:08 12 DBMS_OUTPUT.PUT_LINE('------------------------');
23:37:08 13 FOR i IN 1 .. 3 LOOP
23:37:08 14 flag := DBMS_PIPE.RECEIVE_MESSAGE('SCOTT$MESSAGE_INBOX_privat', 0);
23:37:08 15 -- Read message from local buffer.
23:37:08 16 DBMS_PIPE.UNPACK_MESSAGE(message);
23:37:08 17 output := output || message;
23:37:08 18 END LOOP;
23:37:08 19 IF (flag = 0) THEN
23:37:08 20 DBMS_OUTPUT.PUT(output);
23:37:08 21 DBMS_OUTPUT.PUT_LINE('Message received from SCOTT$MESSAGE_INBX_private.');
23:37:08 22 END IF;
23:37:08 23 END;
23:37:09 24 /
当前用户: SCOTT
当前会话ID: 17
Output private_Message from Pipe
------------------------
SendMessage:[1]
SendMessage:[2]
SendMessage:[3]
Message received from SCOTT$MESSAGE_INBOX_private.
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.04
以不同数据类型发送数据
--DBMS_PIPE.NEXT_ITEM_TYPE的使用演示
23:59:22 SCOTT@orcl> DECLARE
00:00:19 2 session VARCHAR2(30) := DBMS_PIPE.UNIQUE_SESSION_NAME;
00:00:19 3 line_return VARCHAR2(1) := CHR(10);
00:00:19 4 message VARCHAR2(4000);
00:00:19 5 output VARCHAR2(4000);
00:00:19 6 flag INTEGER;
00:00:19 7 code INTEGER;
00:00:19 8 message1 INTEGER := 1776;
00:00:19 9 message2 DATE := TO_DATE('2013-06-08','YYYY-MM-DD');
00:00:19 10 message3 VARCHAR2(30 CHAR) := 'Cryking ';
00:00:19 11 message11 INTEGER;
00:00:19 12 message12 DATE;
00:00:19 13 message13 VARCHAR2(30 CHAR);
00:00:19 14 BEGIN
00:00:19 15 DBMS_OUTPUT.PUT_LINE('当前用户: ' || user);
00:00:19 16 -- Write the messages to a pipe.
00:00:19 17 DBMS_PIPE.PURGE('SCOTT$MESSAGE_INBOX');
00:00:19 18 DBMS_OUTPUT.PUT_LINE('Input Message to Pipe');
00:00:19 19 DBMS_OUTPUT.PUT_LINE('会话: [' || session || ']');
00:00:19 20 DBMS_OUTPUT.PUT_LINE('--------------------------------');
00:00:19 21 -- Process message1.
00:00:19 22 DBMS_OUTPUT.PUT_LINE(message1 || '[NUMBER]');
00:00:19 23 DBMS_PIPE.PACK_MESSAGE(message1);
00:00:19 24 flag := DBMS_PIPE.SEND_MESSAGE('SCOTT$MESSAGE_INBOX');
00:00:19 25 -- Process message2.
00:00:19 26 DBMS_OUTPUT.PUT_LINE(message2 || '[DATE]');
00:00:19 27 DBMS_PIPE.PACK_MESSAGE(message2);
00:00:19 28 flag := DBMS_PIPE.SEND_MESSAGE('SCOTT$MESSAGE_INBOX');
00:00:19 29 -- Process message3.
00:00:19 30 DBMS_OUTPUT.PUT_LINE(message3 || '[VARCHAR2]');
00:00:19 31 DBMS_PIPE.PACK_MESSAGE(message3);
00:00:19 32 flag := DBMS_PIPE.SEND_MESSAGE('SCOTT$MESSAGE_INBOX');
00:00:19 33 IF (flag = 0) THEN
00:00:19 34 DBMS_OUTPUT.PUT_LINE('Message sent to SCOTT$MESSAGE_INBOX.');
00:00:19 35 END IF;
00:00:19 36 DBMS_OUTPUT.PUT(line_return);
00:00:19 37 -- Read the messages from a pipe.
00:00:19 38 DBMS_OUTPUT.PUT_LINE('Output Message from Pipe');
00:00:19 39 DBMS_OUTPUT.PUT_LINE('会话: [' || session || ']');
00:00:19 40 DBMS_OUTPUT.PUT_LINE('--------------------------------');
00:00:19 41 FOR i IN 1 .. 3 LOOP
00:00:19 42 DBMS_PIPE.RESET_BUFFER;
00:00:19 43 flag := DBMS_PIPE.RECEIVE_MESSAGE('SCOTT$MESSAGE_INBOX', 0);
00:00:19 44 code := DBMS_PIPE.NEXT_ITEM_TYPE;
00:00:19 45 CASE code
00:00:19 46 WHEN 6 THEN
00:00:19 47 -- Buffer is a NUMBER.
00:00:19 48 DBMS_PIPE.UNPACK_MESSAGE(message11);
00:00:19 49 output := output || message11 || '[NUMBER]' || line_return;
00:00:19 50 WHEN 9 THEN
00:00:19 51 -- Buffer is a VARCHAR2.
00:00:19 52 DBMS_PIPE.UNPACK_MESSAGE(message13);
00:00:19 53 output := output || message13 || '[VARCHAR2]' || line_return;
00:00:19 54 WHEN 12 THEN
00:00:19 55 -- Buffer is a DATE.
00:00:19 56 DBMS_PIPE.UNPACK_MESSAGE(message12);
00:00:19 57 output := output || message12 || '[DATE]' || line_return;
00:00:19 58 END CASE;
00:00:19 59 END LOOP;
00:00:19 60 -- Print messages.
00:00:19 61 IF (flag = 0) THEN
00:00:19 62 DBMS_OUTPUT.PUT(output);
00:00:19 63 DBMS_OUTPUT.PUT_LINE('Message received from SCOTT$MESSAGE_INBOX.');
00:00:19 64 END IF;
00:00:19 65 END;
00:00:19 66 /
当前用户: SCOTT
Input Message to Pipe
会话: [ORA$PIPE$000E00250001]
--------------------------------
1776[NUMBER]
2013-06-08[DATE]
Cryking [VARCHAR2]
Message sent to SCOTT$MESSAGE_INBOX.
Output Message from Pipe
会话: [ORA$PIPE$000E00250001]
--------------------------------
1776[NUMBER]
2013-06-08[DATE]
Cryking [VARCHAR2]
Message received from SCOTT$MESSAGE_INBOX.
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
将以上发送信息及接收信息的函数/存储等包装成包的形式以方便使用
00:18:50 SYS@orcl> CREATE OR REPLACE PACKAGE BODY messenger IS
00:18:50 2 FUNCTION get_user RETURN VARCHAR2 IS
00:18:50 3 BEGIN
00:18:50 4 FOR i IN (SELECT user FROM dual) LOOP
00:18:50 5 return i.user;
00:18:50 6 END LOOP;
00:18:50 7 END get_user;
00:18:50 8 FUNCTION send_message(user_name VARCHAR2,
00:18:50 9 message VARCHAR2,
00:18:50 10 message_box VARCHAR2 DEFAULT 'MESSAGE_INBOX'
)
00:18:50 11 RETURN INTEGER IS
00:18:50 12 message_pipe VARCHAR2(100 CHAR);
00:18:50 13 BEGIN
00:18:50 14 DBMS_PIPE.RESET_BUFFER;
00:18:50 15 message_pipe := UPPER(user_name) || '$' || UPPER(message_box);
00:18:50 16 DBMS_PIPE.PACK_MESSAGE(message);
00:18:50 17 -- Return 0 for sent message and 1 for unsent message.
00:18:50 18 IF (DBMS_PIPE.send_message(message_pipe) = 0) THEN
00:18:50 19 RETURN 0;
00:18:50 20 ELSE
00:18:50 21 RETURN 1;
00:18:50 22 END IF;
00:18:50 23 END send_message;
00:18:50 24 FUNCTION receive_message RETURN VARCHAR2 IS
00:18:50 25 message VARCHAR2(4000 CHAR) := NULL;
00:18:50 26 message_box VARCHAR2(100 CHAR);
00:18:50 27 inbox VARCHAR2(14 CHAR) := 'MESSAGE_INBOX';
00:18:50 28 timeout INTEGER := 0;
00:18:50 29 return_code INTEGER;
00:18:50 30 BEGIN
00:18:50 31 DBMS_PIPE.RESET_BUFFER;
00:18:50 32 message_box := get_user || '$' || inbox;
00:18:50 33 return_code := DBMS_PIPE.RECEIVE_MESSAGE(message_box, timeout);
00:18:50 34 CASE return_code
00:18:50 35 WHEN 0 THEN
00:18:50 36 DBMS_PIPE.UNPACK_MESSAGE(message);
00:18:50 37 WHEN 1 THEN
00:18:50 38 message := 'The message pipe is empty.';
00:18:50 39 WHEN 2 THEN
00:18:50 40 message := 'The message is too large for variable.';
00:18:50 41 WHEN 3 THEN
00:18:50 42 message := 'An interrupt occurred, contact the DBA.';
00:18:50 43 END CASE;
00:18:50 44 -- Return the message.
00:18:50 45 RETURN message;
00:18:50 46 END receive_message;
00:18:50 47 END messenger;
00:18:50 48 /
Package body created.
Elapsed: 00:00:00.38
00:19:14 SYS@orcl> grant execute on messenger to scott;
Grant succeeded.
Elapsed: 00:00:00.11
--测试使用创建的信息包来发送和接收信息
23:37:11 SCOTT@orcl> DECLARE
00:19:41 2 FUNCTION get_user RETURN VARCHAR2 IS
00:19:41 3 BEGIN
00:19:41 4 FOR i IN (SELECT user FROM dual) LOOP
00:19:41 5 return i.user;
00:19:41 6 END LOOP;
00:19:41 7 END get_user;
00:19:41 8 BEGIN
00:19:41 9 -- Send message.
00:19:41 10 IF (sys.MESSENGER.SEND_MESSAGE(get_user, 'Hello World!') = 0) THEN
00:19:41 11 DBMS_OUTPUT.PUT_LINE(sys.MESSENGER.RECEIVE_MESSAGE);
00:19:41 12 END IF;
00:19:41 13 END;
00:19:42 14 /
Hello World!
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.04