68 lines
1.9 KiB
SQL
68 lines
1.9 KiB
SQL
alter table mieter
|
|
add column iban varchar(34);
|
|
|
|
delimiter //
|
|
|
|
create or replace function format_iban(p_iban varchar(34)) returns varchar(34)
|
|
begin
|
|
declare v_country varchar(2);
|
|
declare v_part1 varchar(4);
|
|
declare v_part2 varchar(4);
|
|
declare v_part3 varchar(4);
|
|
declare v_part4 varchar(4);
|
|
declare v_part5 varchar(4);
|
|
declare v_part6 varchar(2);
|
|
declare v_result varchar(34);
|
|
|
|
set v_country = substring(p_iban from 1 for 2);
|
|
|
|
if (v_country = 'DE') then
|
|
set v_part1 = substring(p_iban from 3 for 2);
|
|
set v_part2 = substring(p_iban from 5 for 4);
|
|
set v_part3 = substring(p_iban from 9 for 4);
|
|
set v_part4 = substring(p_iban from 13 for 4);
|
|
set v_part5 = substring(p_iban from 17 for 4);
|
|
set v_part6 = substring(p_iban from 21 for 2);
|
|
set v_result = concat(v_country, ' ', v_part1, ' ', v_part2, ' ', v_part3, ' ',
|
|
v_part4, ' ', v_part5, ' ', v_part6);
|
|
else
|
|
set v_result = p_iban;
|
|
end if;
|
|
|
|
return v_result;
|
|
end;
|
|
//
|
|
|
|
create or replace function check_iban(p_iban varchar(34)) returns boolean
|
|
begin
|
|
declare v_country varchar(2);
|
|
declare v_result boolean;
|
|
declare v_rearranged_iban varchar(36);
|
|
declare v_numeric_iban decimal(36, 0);
|
|
declare v_numeric_result decimal(36, 0);
|
|
|
|
set v_country = substring(p_iban from 1 for 2);
|
|
|
|
if (v_country = 'DE') then
|
|
set v_rearranged_iban = concat(substring(p_iban from 5), '1314', substring(p_iban from 3 for 2));
|
|
set v_numeric_iban = cast(v_rearranged_iban as decimal(36, 0));
|
|
set v_numeric_result = v_numeric_iban % 97;
|
|
if (v_numeric_result = 1) then
|
|
set v_result = TRUE;
|
|
else
|
|
set v_result = FALSE;
|
|
end if;
|
|
else
|
|
set v_result = TRUE;
|
|
end if;
|
|
|
|
return v_result;
|
|
end;
|
|
//
|
|
|
|
|
|
delimiter ;
|
|
|
|
-- alter table mieter
|
|
-- add constraint ck_iban
|
|
-- check(check_iban(iban) = TRUE); |