Validacija po modulu 11

Jeste li znali da svaki matični broj fizičkih i pravnih lica mora zadovoljiti ispravnost "po modulu 11" ?!Na net - u sam uspjela pronaći opis pravila za validaciju, koje se koristi u našoj zemlji, ali i zemljama u okruženju ( http://hr.wikipedia.org/wiki/Matični_broj_građana). Međutim, nisam uspjela pronaći čak ni približno riješen problem u nekom od programskih jezika. Zato sam odlučila da isti riješim, za početak, u PL\SQL - u, a zatim i u Java - i.

Slijedi rješenje u PL\SQL - u:

1. Funkcija za ispitivanje validnosti:

function validacijaPoModulu11(MB varchar2) return boolean is
rezultat integer;
ostatak integer;
razlika integer;
praviMB varchar2(13);
ind boolean;
begin

if length(MB) <= 13 then

if length(MB) = 13 then

rezultat := to_number(substr(MB,1,1)) * 7 + to_number(substr(MB,2,1)) * 6 + to_number(substr(MB,3,1)) * 5 + to_number(substr(MB,4,1)) * 4 + to_number(substr(MB,5,1)) * 3 + to_number(substr(MB,6,1)) * 2 + to_number(substr(MB,7,1)) * 7 + to_number(substr(MB,8,1)) * 6 + to_number(substr(MB,9,1)) * 5 + to_number(substr(MB,10,1)) * 4 + to_number(substr(MB,11,1)) * 3 + to_number(substr(MB,12,1)) * 2;
ostatak := mod(rezultat,11);
razlika := 11 - ostatak;

if ostatak = 1 then
ind := false;
end if;

if ostatak = 0 then
if to_number(substr(MB,13,1)) <> 0 then
ind := false;
else
ind := true;
end if;
end if;

if (ostatak > 1 and ostatak < 11) then
if to_number(substr(MB,13,1)) <> razlika then
ind := false;
else
ind := true;
end if;
end if;

end if;

if length(MB) = 8 then

rezultat := to_number(substr(MB,1,1)) * 8 + to_number(substr(MB,2,1)) * 7 + to_number(substr(MB,3,1)) * 6 + to_number(substr(MB,4,1)) * 5 + to_number(substr(MB,5,1)) * 4 + to_number(substr(MB,6,1)) * 3 + to_number(substr(MB,7,1)) * 2;
ostatak := mod(rezultat,11);
razlika := 11 - ostatak;

if ostatak = 1 then
if to_number(substr(MB,8,1)) <> 0 then
ind := false;
else
ind := true;
end if;
end if;

if ostatak = 0 then
ind := false;
end if;

if (ostatak > 1 and ostatak < 11) then
if to_number(substr(MB,8,1)) <> razlika then
ind := false;
else
ind := true;
end if;
end if;

end if;


else
ind := false;
end if;

return ind;

end validacijaPoModulu11;

How to list all columns, primary keys, foreign keys, unique keys and check constraints for Oracle database

Few months ago I needed to list all constraints for all tables of Oracle database . Unfortunatelly, I couldn't find right scripts on the net, so I decided to wright them on my own.


1. First of those is script for listing all primary keys of some database. Beside name of primary key, it also shows name of column it refers to :

select b.constraint_name,b.column_name, c.nullable
from user_constraints a, user_cons_columns b ,user_tab_columns c
where a.constraint_type = 'P'
and a.table_name = b.table_name
and a.constraint_name = b.constraint_name
and b.table_name = c.table_name
and b.column_name = c.column_name

2. Second scrip lists all check constraints of database for certain user:

select a.constraint_name,
b.column_name, a.search_condition
from user_constraints a , user_cons_columns a
where a.constraint_type = 'C'
and a.table_name = b.table_name
and a.constraint_name = b.constraint_name

3. Third script lists all foreign constraints of some database:

select a1.constraint_name name_of_constraint,
b1.column_name column,
b2.table_name reference_table,
b2.column_name reference_column, c.nullable
from user_constraints a1 , user_cons_columns b1 ,
user_cons_columns b2, user_tab_columns c
where a1.constraint_type = 'R'
and a1.table_name = b1.table_name
and b1.constraint_name = a1.constraint_name
and b2.constraint_name = a1.r_constraint_name
and c.table_name = a1.table_name
and c.column_name = b1.column_name

3. Third script lists all foreign constraints of some database:

select a1.constraint_name name_of_constraint,
b1.column_name column,
b2.table_name reference_table,
b2.column_name reference_column, c.nullable
from user_constraints a1 , user_cons_columns b1 ,
user_cons_columns b2, user_tab_columns c
where a1.constraint_type = 'R'
and a1.table_name = b1.table_name
and b1.constraint_name = a1.constraint_name
and b2.constraint_name = a1.r_constraint_name
and c.table_name = a1.table_name
and c.column_name = b1.column_name

4. Fourth script lists columns of all tables of some database:

select a.column_name , a.data_type ,
a.data_length , a.data_precision, a.data_scale ,
a.nullable , NVL(b.comments,' ') comments
from user_tab_columns a , user_col_comments b
where a.table_name = b.table_name
and a.column_name = b.column_name
order by a.column_id asc;

5. Fifth script lists all unique keys of some database:

select a.constraint_name,
b.column_name, c.nullable
from user_constraints a ,
user_cons_columns B , user_tab_columns c
where a.constraint_type = 'U'
and a.table_name = b.table_name
and a.table_name= c.table_name
and b.column_name = c.column_name
and a.constraint_name = b.constraint_name