CODEWITHSUNDEEP
oracle-databasestored-procedures
Felix Xie
Felix Xie

Reputation: 89

how to define key-value variable in Oracle procedure

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

Upvotes: 0

Views: 1176

Answers (1)

dcieslak
dcieslak

Reputation: 2715

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

Upvotes: 1

Related Questions