题目是:
编写存储过程,实现功能:
1.输入参数:字符串,分隔符,序号
2.输出参数:字符串
3.注:序号可为负数,为负数时从右往左找字符串例:
输入参数分别为: ab@@cd@@ee@@ff @@ 2 则输出:cd
输入参数分别为: ab@@cd@@ee@@ff @@ -2 则输出:ee
输入参数分别为: ab@@cd@@ee@@ff @@ 4 则输出:ff我写的代码如下 改了很多次
CREATE OR REPLACE PROCEDURE str_to(input IN VARCHAR2,
separator IN VARCHAR2,
serialno IN INTEGER) IS v_input VARCHAR2(100) := input;
v_separator VARCHAR2(5) := separator;
v_separatorno INTEGER;
v_serialno INTEGER := serialno;
v_output VARCHAR2(20);
i NUMBER;
strlength INTEGER;
str VARCHAR2(5);
TYPE type_array IS VARRAY(200) OF VARCHAR2(20);
array1 type_array;
array2 type_array;
flag BOOLEAN;
-- num NUMBER;
-- a INTEGER;
BEGIN
array1 := type_array();
array2 := type_array();
strlength := length(v_input);
v_separatorno := length(v_separator);
flag := FALSE;
array2.extend;
IF v_serialno < 0 THEN
BEGIN
v_serialno := -v_serialno;
flag := TRUE;
SELECT REVERSE(v_input) INTO v_input FROM dual;
END;
END IF;
--如果序号为负数,将输入的字符串反转
FOR i IN 1 .. strlength LOOP
array1.extend;
array1(i) := substr(v_input, - (strlength - i + 1));
--DBMS_OUTPUT.put_line(array1(i));
END LOOP;
COMMIT;
--逆向剪切字符串,逐一存入数组
-- a := -v_separatorno;
FOR i IN 1 .. strlength LOOP
--SELECT substr(array1(i), 1,v_separatorno) INTO str FROM dual;
--DBMS_OUTPUT.put_line(str);
IF substr(array1(i), 1, v_separatorno) <> v_separator THEN --若数组元素不以分隔符为首将数组1的元素存到数组2中
FOR j IN 1 .. array2.count LOOP
--array1.extend;
--array2.extend;
array2(j) := array1(i); --但是这里会出现以一个“@”为首的元素
DBMS_OUTPUT.put_line(array2(j));
END LOOP;
END IF;
END LOOP;
array2.extend;
/*v_output := array2(v_serialno);*/
/* SELECT INSTR(array2(v_serialno), '[^v_separator]+', 1, 1)
INTO num
FROM dual;*/ --扫描分隔符出现的位置
/* SELECT SUBSTR(array2(v_serialno), 1, 2)
INTO v_output
FROM DUAL;*/
SELECT SUBSTR(array2(v_serialno),
1,
v_serialno)
INTO v_output
FROM DUAL; --从第一位开始截取,得到的结果是字符串的最后一位 有点搞不懂= =!
IF flag = TRUE THEN
BEGIN
SELECT REVERSE(v_output) INTO v_output FROM dual;
END;
END IF; --当序号为负的时候保证输出的字符串是正向的
DBMS_OUTPUT.put_line(v_output);
END str_to;
存储过程分隔符
编写存储过程,实现功能:
1.输入参数:字符串,分隔符,序号
2.输出参数:字符串
3.注:序号可为负数,为负数时从右往左找字符串例:
输入参数分别为: ab@@cd@@ee@@ff @@ 2 则输出:cd
输入参数分别为: ab@@cd@@ee@@ff @@ -2 则输出:ee
输入参数分别为: ab@@cd@@ee@@ff @@ 4 则输出:ff我写的代码如下 改了很多次
CREATE OR REPLACE PROCEDURE str_to(input IN VARCHAR2,
separator IN VARCHAR2,
serialno IN INTEGER) IS v_input VARCHAR2(100) := input;
v_separator VARCHAR2(5) := separator;
v_separatorno INTEGER;
v_serialno INTEGER := serialno;
v_output VARCHAR2(20);
i NUMBER;
strlength INTEGER;
str VARCHAR2(5);
TYPE type_array IS VARRAY(200) OF VARCHAR2(20);
array1 type_array;
array2 type_array;
flag BOOLEAN;
-- num NUMBER;
-- a INTEGER;
BEGIN
array1 := type_array();
array2 := type_array();
strlength := length(v_input);
v_separatorno := length(v_separator);
flag := FALSE;
array2.extend;
IF v_serialno < 0 THEN
BEGIN
v_serialno := -v_serialno;
flag := TRUE;
SELECT REVERSE(v_input) INTO v_input FROM dual;
END;
END IF;
--如果序号为负数,将输入的字符串反转
FOR i IN 1 .. strlength LOOP
array1.extend;
array1(i) := substr(v_input, - (strlength - i + 1));
--DBMS_OUTPUT.put_line(array1(i));
END LOOP;
COMMIT;
--逆向剪切字符串,逐一存入数组
-- a := -v_separatorno;
FOR i IN 1 .. strlength LOOP
--SELECT substr(array1(i), 1,v_separatorno) INTO str FROM dual;
--DBMS_OUTPUT.put_line(str);
IF substr(array1(i), 1, v_separatorno) <> v_separator THEN --若数组元素不以分隔符为首将数组1的元素存到数组2中
FOR j IN 1 .. array2.count LOOP
--array1.extend;
--array2.extend;
array2(j) := array1(i); --但是这里会出现以一个“@”为首的元素
DBMS_OUTPUT.put_line(array2(j));
END LOOP;
END IF;
END LOOP;
array2.extend;
/*v_output := array2(v_serialno);*/
/* SELECT INSTR(array2(v_serialno), '[^v_separator]+', 1, 1)
INTO num
FROM dual;*/ --扫描分隔符出现的位置
/* SELECT SUBSTR(array2(v_serialno), 1, 2)
INTO v_output
FROM DUAL;*/
SELECT SUBSTR(array2(v_serialno),
1,
v_serialno)
INTO v_output
FROM DUAL; --从第一位开始截取,得到的结果是字符串的最后一位 有点搞不懂= =!
IF flag = TRUE THEN
BEGIN
SELECT REVERSE(v_output) INTO v_output FROM dual;
END;
END IF; --当序号为负的时候保证输出的字符串是正向的
DBMS_OUTPUT.put_line(v_output);
END str_to;
存储过程分隔符
CREATE OR REPLACE PROCEDURE str_to(input IN VARCHAR2,
separator IN VARCHAR2,
serialno IN INTEGER,
str out varchar2) IS
v_count number;
v_lv number;
BEGIN
select regexp_count(input, separator) + 1 into v_count from dual; if (serialno >= 0) then
v_lv := serialno;
else
v_lv := v_count + serialno + 1;
end if; with t as
(select level lv,
regexp_substr(input, '[^' || separator || ']+', 1, level) str
from dual
connect by level <= regexp_count(input, separator) + 1)
select str into str from t where lv = v_lv; dbms_output.put_line(str);
END str_to;
Line: 8
Text: select regexp_count(input, separator) + 1 into v_count from dual;Error: PL/SQL: ORA-00904: "REGEXP_COUNT": 标识符无效
Line: 20
Text: connect by level <= regexp_count(input, separator) + 1)我运行了下你的代码 有这个错误 是不是版本的问题?我的是oracle 10g
separator IN VARCHAR2,
serialno IN INTEGER) IS v_input VARCHAR2(100) := input;
v_separator VARCHAR2(5) := separator;
v_separatorno INTEGER;
v_serialno INTEGER := serialno;
v_output VARCHAR2(20);
i NUMBER;
TYPE type_array IS VARRAY(200) OF VARCHAR2(20);
array1 type_array;
flag BOOLEAN;
BEGIN
array1 := type_array();
v_separatorno := length(v_separator);
flag := FALSE;
IF v_serialno < 0 THEN
BEGIN
v_serialno := -v_serialno;
flag := TRUE;
SELECT REVERSE(v_input) INTO v_input FROM dual;
END;
END IF;
--如果序号为负数,将输入的字符串反转
FOR i IN 1 .. array1.count LOOP
array1.extend;
array1(i) := instr(v_input, v_separator, 1, i);
DBMS_OUTPUT.put_line(array1(i));
END LOOP;
COMMIT; IF v_serialno = 1 THEN
BEGIN
array1.extend;
SELECT SUBSTR(v_input, 1, array1(1) - 1) INTO v_output FROM DUAL;
END;
ELSE
BEGIN
array1.extend;
SELECT SUBSTR(v_input,
array1(v_serialno - 1) + v_separatorno,
array1(v_serialno) - array1(v_serialno - 1) - 1)
INTO v_output
FROM DUAL;
END;
END IF;
IF flag = TRUE THEN
BEGIN
SELECT REVERSE(v_output) INTO v_output FROM dual;
END;
END IF; --当序号为负的时候保证输出的字符串是正向的
DBMS_OUTPUT.put_line(v_output);
END str_to_proc;
我又写了一个 可是SELECT SUBSTR(v_input,
array1(v_serialno - 1) + v_separatorno,
array1(v_serialno) - array1(v_serialno - 1) - 1)
INTO v_output
FROM DUAL; 老是出现下标超出数量的错误
Line: 8
Text: select regexp_count(input, separator) + 1 into v_count from dual;Error: PL/SQL: ORA-00904: "REGEXP_COUNT": 标识符无效
Line: 20
Text: connect by level <= regexp_count(input, separator) + 1)我运行了下你的代码 有这个错误 是不是版本的问题?我的是oracle 10g
是版本问题,改一下即可,看下是否可以:
CREATE OR REPLACE PROCEDURE str_to(input IN VARCHAR2,
separator IN VARCHAR2,
serialno IN INTEGER,
str out varchar2) IS
v_count number;
v_lv number;
BEGIN
select length(replace(input, separator, '@')) -
length(replace(replace(input, separator, '@'), '@', '')) + 1
into v_count
from dual; if (serialno >= 0) then
v_lv := serialno;
else
v_lv := v_count + serialno + 1;
end if; with t as
(select level lv,
regexp_substr(input, '[^' || separator || ']+', 1, level) str
from dual
connect by level <= v_count)
select str into str from t where lv = v_lv; dbms_output.put_line(str);
exception
when others then
str := '';
END str_to;
Line: 8
Text: select regexp_count(input, separator) + 1 into v_count from dual;Error: PL/SQL: ORA-00904: "REGEXP_COUNT": 标识符无效
Line: 20
Text: connect by level <= regexp_count(input, separator) + 1)我运行了下你的代码 有这个错误 是不是版本的问题?我的是oracle 10g
是版本问题,改一下即可,看下是否可以:
CREATE OR REPLACE PROCEDURE str_to(input IN VARCHAR2,
separator IN VARCHAR2,
serialno IN INTEGER,
str out varchar2) IS
v_count number;
v_lv number;
BEGIN
select length(replace(input, separator, '@')) -
length(replace(replace(input, separator, '@'), '@', '')) + 1
into v_count
from dual; if (serialno >= 0) then
v_lv := serialno;
else
v_lv := v_count + serialno + 1;
end if; with t as
(select level lv,
regexp_substr(input, '[^' || separator || ']+', 1, level) str
from dual
connect by level <= v_count)
select str into str from t where lv = v_lv; dbms_output.put_line(str);
exception
when others then
str := '';
END str_to;
可以了 好厉害~谢谢哦
Line: 8
Text: select regexp_count(input, separator) + 1 into v_count from dual;Error: PL/SQL: ORA-00904: "REGEXP_COUNT": 标识符无效
Line: 20
Text: connect by level <= regexp_count(input, separator) + 1)我运行了下你的代码 有这个错误 是不是版本的问题?我的是oracle 10g
是版本问题,改一下即可,看下是否可以:
CREATE OR REPLACE PROCEDURE str_to(input IN VARCHAR2,
separator IN VARCHAR2,
serialno IN INTEGER,
str out varchar2) IS
v_count number;
v_lv number;
BEGIN
select length(replace(input, separator, '@')) -
length(replace(replace(input, separator, '@'), '@', '')) + 1
into v_count
from dual; if (serialno >= 0) then
v_lv := serialno;
else
v_lv := v_count + serialno + 1;
end if; with t as
(select level lv,
regexp_substr(input, '[^' || separator || ']+', 1, level) str
from dual
connect by level <= v_count)
select str into str from t where lv = v_lv; dbms_output.put_line(str);
exception
when others then
str := '';
END str_to;
对了 假如
BEGIN
proc_str_to('ab@@cd@@ee@@ff@','@@',-2);
END;
输出的是ff 把@也当作分隔符了 这个好像不对
那你这个实际得到的结果是什么??我改了一下 把length(replace(input, separator, '@')) 的@ 改成了# 输出了ee 这个是对的但如果调用
BEGIN
proc_str_to('ab@@cd@@ee@@ff@','@@',-1);
END;
输出的是ff 正确的应该是ff@
那你这个实际得到的结果是什么??我改了一下 把length(replace(input, separator, '@')) 的@ 改成了# 输出了ee 这个是对的但如果调用
BEGIN
proc_str_to('ab@@cd@@ee@@ff@','@@',-1);
END;
输出的是ff 正确的应该是ff@改了下,试下:CREATE OR REPLACE PROCEDURE str_to(input IN VARCHAR2,
separator IN VARCHAR2,
serialno IN INTEGER,
str out varchar2) IS
v_count number;
v_lv number;
v_input varchar2(4000);
v_replace varchar2(10) := '▽';
BEGIN v_input := replace(input, separator, v_replace); select length(v_input) - length(replace(v_input, v_replace, '')) + 1
into v_count
from dual; if (serialno >= 0) then
v_lv := serialno;
else
v_lv := v_count + serialno + 1;
end if; with t as
(select level lv,
regexp_substr(v_input, '[^' || v_replace || ']+', 1, level) str
from dual
connect by level <= v_count)
select str into str from t where lv = v_lv; dbms_output.put_line(str);
exception
when others then
str := '';
END str_to;
那你这个实际得到的结果是什么??我改了一下 把length(replace(input, separator, '@')) 的@ 改成了# 输出了ee 这个是对的但如果调用
BEGIN
proc_str_to('ab@@cd@@ee@@ff@','@@',-1);
END;
输出的是ff 正确的应该是ff@改了下,试下:CREATE OR REPLACE PROCEDURE str_to(input IN VARCHAR2,
separator IN VARCHAR2,
serialno IN INTEGER,
str out varchar2) IS
v_count number;
v_lv number;
v_input varchar2(4000);
v_replace varchar2(10) := '▽';
BEGIN v_input := replace(input, separator, v_replace); select length(v_input) - length(replace(v_input, v_replace, '')) + 1
into v_count
from dual; if (serialno >= 0) then
v_lv := serialno;
else
v_lv := v_count + serialno + 1;
end if; with t as
(select level lv,
regexp_substr(v_input, '[^' || v_replace || ']+', 1, level) str
from dual
connect by level <= v_count)
select str into str from t where lv = v_lv; dbms_output.put_line(str);
exception
when others then
str := '';
END str_to;
可以了!!非常感谢!!!
begin
str_to_proc2('@@ab@@cd@@@e@e@@ff',
'@@',
-1);
end;
这个输出是空。。应该要ff
begin
str_to_proc2('@@ab@@cd@@@e@e@@ff',
'@@',
-1);
end;
这个输出是空。。应该要ff嗯,有很多考虑不全的地方。所以测试是很重要的:CREATE OR REPLACE PROCEDURE str_to(input IN VARCHAR2,
separator IN VARCHAR2,
serialno IN INTEGER,
str out varchar2) IS
v_count number;
v_lv number;
v_input varchar2(4000);
v_replace varchar2(10) := '▽';
BEGIN v_input := replace(input, separator, v_replace); select length(v_input) - length(replace(v_input, v_replace, '')) + 1
into v_count
from dual; if (serialno >= 0) then
v_lv := serialno;
elsif (v_input like v_replace || '%') then
v_lv := v_count + serialno;
else
v_lv := v_count + serialno + 1;
end if; with t as
(select level lv,
regexp_substr(v_input, '[^' || v_replace || ']+', 1, level) str
from dual
connect by level <= v_count)
select str into str from t where lv = v_lv; dbms_output.put_line(str);
exception
when others then
str := '';
END str_to;