解决方案 »
- create spfile from pfile时出现下面错误
- oracle数据库中添加删除约束的问题
- oracle启动database control问题
- 新装的oracle,登录不了SQL PLUS,另外怎么用IE访问ORACLE啊?
- 如何提高Oracle存储过程的执行效率
- 如何用dd实现两个机器的oracle备份
- 如果将MSSQL的数据导入到Oracle中?
- 就是这样的问题,帮帮我
- SQL PLUS写个超难的查询的问题,不同方法分行返回对错不同,另如果大虾们懂ASP.NET帮定DATAGRID另给分
- plsql上没有connect as,怎么解决
- oracle12c 64位安装客户端问题
- oracle原始数据(dbf)如何迁移到新环境
select REGEXP_SUBSTR(str, '[^,]+', 1, LEVEL) STR
from (select '1,2,3,4,5,6,7' str from dual)
CONNECT BY LEVEL <= REGEXP_COUNT(str, ',') + 1;
--REGEXP_COUNT 函数11g之后可用,之前版本这样写
select REGEXP_SUBSTR(str, '[^,]+', 1, LEVEL) STR
from (select '1,2,3,4,5,6,7' str from dual)
CONNECT BY LEVEL <= length(str)-length(replace(str, ',','')) + 1;执行结果如下
STR
1
2
3
4
5
6
7
非常感谢,原来如此。
在Oracle里是如何拼接 SQL然后执行的啊 ……
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;
自定义split,可以使用管道函数实现。你百度下就知道了。
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;