Oracle 存储过程，怎么改进下面这个存储过程，Oracle是否有split函数
Oracle 存储过程，如何改进下面这个存储过程，Oracle是否有split函数
作用：往表 TB_MESSAGE_READLOG插入记录。
表TB_MESSAGE_READLOG 结构：
1） ID int
2）MessageId int
3）UserId int
4）ReadTime date
总共4个字段
存储过程语句：
存在问题
1）以上每次只能插入一条记录，要求能批量插入，传入 vUserId INT,vMessageIds varchar2。
vMessageIds 的 值是使用逗号分隔的数字，格式 ^\d+(,\d+)*$ 。
原理是先分隔 vMessageIds 再循环插入， 如何才可以实现？
------解决思路----------------------
------解决思路----------------------
oracle没有自带split的。
自定义split，可以使用管道函数实现。你百度下就知道了。
------解决思路----------------------
自定义split函数；
CREATE OR REPLACE TYPE split_type IS TABLE OF VARCHAR2 (100);
CREATE OR REPLACE FUNCTION split (
p_str IN VARCHAR2,
p_delimiter IN VARCHAR2 default(',') --分隔符，默认逗号
)
RETURN split_type
IS
j INT := 0;
i INT := 1;
len INT := 0;
len1 INT := 0;
str VARCHAR2 (4000);
my_split split_type := split_type ();
BEGIN
len := LENGTH (p_str);
len1 := LENGTH (p_delimiter);
WHILE j < len
LOOP
j := INSTR (p_str, p_delimiter, i);
IF j = 0
THEN
j := len;
str := SUBSTR (p_str, i);
my_split.EXTEND;
my_split (my_split.COUNT) := str;
IF i >= len
THEN
EXIT;
END IF;
ELSE
str := SUBSTR (p_str, i, j - i);
i := j + len1;
my_split.EXTEND;
my_split (my_split.COUNT) := str;
END IF;
END LOOP;
RETURN my_split;
END split;
本帖最后由 default7 于 2014-09-16 15:38:21 编辑
存储过程PR_READLOG_ADD作用：往表 TB_MESSAGE_READLOG插入记录。
表TB_MESSAGE_READLOG 结构：
1） ID int
2）MessageId int
3）UserId int
4）ReadTime date
总共4个字段
存储过程语句：
/* Formatted on 2014/9/16 15:26:01 (QP5 v5.227.12220.39754) */
CREATE OR REPLACE PROCEDURE DEMO.PR_READLOG_ADD (
vUserId IN INT,
vMessageId IN INT,
oResInt OUT INT,
oResStr OUT VARCHAR2)
IS
i INT;
j INT;
/******************************************************************************
NAME: PR_READLOG_ADD
PURPOSE:
REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 2014/9/16 Administrator 1. Created this procedure.
NOTES:
Automatically available Auto Replace Keywords:
Object Name: PR_READLOG_ADD
Sysdate: 2014/9/16
Date and Time: 2014/9/16, 15:11:24, and 2014/9/16 15:11:24
Username: Administrator (set in TOAD Options, Procedure Editor)
Table Name: (set in the "New PL/SQL Object" dialog)
******************************************************************************/
BEGIN
IF NVL (vUserId, 0) = 0 OR NVL (vMessageId, 0) = 0 THEN
oResInt := 0;
oResStr := '传入参数不正确！';
ELSE
SELECT COUNT (1)
INTO i
FROM tb_message
WHERE id = vMessageId;
SELECT COUNT (1)
INTO j
FROM tb_message_readlog
WHERE messageid = vMessageId AND userid = vUserId;
IF i = 0 OR j = 0 THEN
oResInt := 0;
oResStr := '记录不存在';
ELSE
INSERT INTO tb_message_readlog (id,
messageid,
userid,
readtime)
VALUES (fn_gen_getid ('tb_readlog'),
vMessageId,
vUserId,
SYSDATE);
COMMIT;
oResInt := 1;
oResStr := '创建成功';
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
oResInt := 0;
oResStr := '系统异常，请重试！';
RAISE;
END PR_READLOG_ADD;
/
存在问题
1）以上每次只能插入一条记录，要求能批量插入，传入 vUserId INT,vMessageIds varchar2。
vMessageIds 的 值是使用逗号分隔的数字，格式 ^\d+(,\d+)*$ 。
原理是先分隔 vMessageIds 再循环插入， 如何才可以实现？
/* Formatted on 2014/9/16 15:34:47 (QP5 v5.227.12220.39754) */
CREATE OR REPLACE PROCEDURE DEMO.PR_READLOG_ADD (
vUserId IN INT,
vMessageIds IN VARCHAR2,
oResInt OUT INT,
oResStr OUT VARCHAR2)
IS
i INT;
j INT;
BEGIN
oResInt := 0;
oResStr := "";
--1 使用程序分割 vMessageIds
--2 循环检测再插入
--3
EXCEPTION
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
oResInt := 0;
oResStr := '系统异常，请重试！';
RAISE;
END PR_READLOG_ADD;
/
------解决思路----------------------
BEGIN
IF NVL (vUserId, 0) = 0 OR NVL (vMessageId, 0) = 0 THEN
oResInt := 0;
oResStr := '传入参数不正确！';
ELSE
SELECT COUNT (1)
INTO i
FROM tb_message
WHERE id = vMessageId;
IF i = 0 THEN
oResInt := 0;
oResStr := '记录不存在';
ELSE
INSERT INTO tb_message_readlog (id,messageid,userid,readtime)
SELECT id,messageid,userid,readtime
FROM(select fn_gen_getid ('tb_readlog') ID,
REGEXP_SUBSTR(vMessageId, '[^,]+', 1, LEVEL) MessageId ,
vUserId USERID,
SYSDATE READTIME
from dual
CONNECT BY LEVEL <= length(vMessageId)-length(replace(vMessageId, ',','')) + 1) A
WHERE EXISTS (SELECT 1
FROM tb_message_readlog
WHERE messageid = A.MessageId AND userid = A.UserId);
COMMIT;
oResInt := 1;
oResStr := '创建成功';
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
oResInt := 0;
oResStr := '系统异常，请重试！';
RAISE;
END PR_READLOG_ADD;
------解决思路----------------------
oracle没有自带split的。
自定义split，可以使用管道函数实现。你百度下就知道了。
------解决思路----------------------
自定义split函数；
CREATE OR REPLACE TYPE split_type IS TABLE OF VARCHAR2 (100);
CREATE OR REPLACE FUNCTION split (
p_str IN VARCHAR2,
p_delimiter IN VARCHAR2 default(',') --分隔符，默认逗号
)
RETURN split_type
IS
j INT := 0;
i INT := 1;
len INT := 0;
len1 INT := 0;
str VARCHAR2 (4000);
my_split split_type := split_type ();
BEGIN
len := LENGTH (p_str);
len1 := LENGTH (p_delimiter);
WHILE j < len
LOOP
j := INSTR (p_str, p_delimiter, i);
IF j = 0
THEN
j := len;
str := SUBSTR (p_str, i);
my_split.EXTEND;
my_split (my_split.COUNT) := str;
IF i >= len
THEN
EXIT;
END IF;
ELSE
str := SUBSTR (p_str, i, j - i);
i := j + len1;
my_split.EXTEND;
my_split (my_split.COUNT) := str;
END IF;
END LOOP;
RETURN my_split;
END split;