I want to create an Oracle Procedure to replace words in DB.
I have a key-value list which contains the original words and the replaced value of the words.
The question is how I can define a variable in the oracle procedure, so that i can take original words and replace with the values of the words.
And another question is that, I want to set a variable V which can store the list of ids. Than I can use it like select * from table where id in V.How to define the variable?
Thanks
-
1probably the best friend for you is Oracle documentation for creating proceduresAndriy Ivaneyko– Andriy Ivaneyko2015-12-16 09:17:58 +00:00Commented Dec 16, 2015 at 9:17
-
if you're looking for somethin similar to dictionaries, associative arrays is maybe what you're looking forpablomatico– pablomatico2015-12-16 09:49:02 +00:00Commented Dec 16, 2015 at 9:49
Add a comment
|
1 Answer
Key-value list can be implemented in below way:
DECLARE
TYPE mapper IS TABLE OF VARCHAR2(30) INDEX BY VARCHAR2(30);
htab mapper;
BEGIN
htab('A') := 'B';
htab('B') := 'A';
dbms_output.put_line(htab('A'));
END;
/
Regarding your second question, there is a lot of samples in the Internet how to split string in Oracle. One of them is:
CREATE OR REPLACE TYPE split_tbl as table of varchar2(2000);
CREATE OR REPLACE FUNCTION split
(
p_list varchar2,
p_del varchar2 := ','
) return split_tbl pipelined
is
l_idx pls_integer;
l_list varchar2(2000) := p_list;
l_value varchar2(2000);
begin
loop
l_idx := instr(l_list,p_del);
if l_idx > 0 then
pipe row(substr(l_list,1,l_idx-1));
l_list := substr(l_list,l_idx+length(p_del));
else
pipe row(l_list);
exit;
end if;
end loop;
return;
end split;
/
After that you can use split function in below way:
select * from table(split('2d,2fdsd'))
It will return:
COLUMN_VALUE
------
2d
2fdsd
Your query :
select * from table where id in V
may be replaced by:
select t.*
from table t
join table(split('1,2')) V
on V.Column_Value = t.id
