Merge Into ----using table to keep properties contents

When there are some property contents we want to keep, usually, we can use Property table to keep them.

 

1. Create one table with two fields: one is identifier (key), the other is value (value)     key : value

       

CREATE TABLE properties_bvoip3 (identifier VARCHAR2 (4000), value VARCHAR2 (4000)) ;

　　2.Insert data to property table

 

merge into properties_bvoip3 p
using (select 'one_key' identifier, 'one_value' value from dual) s
on (p.identifier = s.identifier)
when matched then update set p.value = s.value
when not matched then insert (identifier,value) values (s.identifier, s.value);

　　3.Get the value of one key from property table

　　1).Create one function to get value

create or replace function get_property_bvoip3( p_key Varchar2(4000)) return varchar2 as
l_tmp varchar2(4000);
begin
    select value into l_tmp from properties_bvoip3 where p_key=identifier;
    return l_tmp;
    exception when others then
        return null;
end;
/

　　2).Get value

l_value := get_property_bvoip3('one_key');