y0ceeQZ0

1CREATE OR REPLACE Procedure genssn
2   ( dato IN varchar2 )
3IS
4   d1 number;
5   d2 number;
6   m1 number;
7   m2 number;
8   y1 number;
9   y2 number;
10   random_num number;
11   padded varchar2(11);
12   i1 number;
13   i2 number;
14   i3 number;
15   c1 number;
16   c2 number;
17   v1 number;
18   v2 number;
19   valid number;
20BEGIN
21    d1 := to_number(substr(dato,1,1));    
22    d2 := to_number(substr(dato,2,1));  
23    m1 := to_number(substr(dato,3,1));  
24    m2 := to_number(substr(dato,4,1));  
25    y1 := to_number(substr(dato,5,1));  
26    y2 := to_number(substr(dato,6,1));  
27    random_num := to_number(round(dbms_random.value(1,499)));  
28    padded := LPAD(to_char(random_num),3,'0');
29    i1 := to_number(substr(padded,0,1));    
30    i2 := to_number(substr(padded,1,1));  
31    i3 := to_number(substr(padded,2,1));  
32    c1 := -1;
33    c2 := -1;
34    v1 := -1;
35    v2 := -1;
36 while (c1 > 9 and c2 > 9) or (c1 = -1 and c2 = -1) or valid = -1
37 loop
38        -- Calculate the two control numbers
39        v1 := (3*d1) + (7*d2) + (6*m1) + m2 + (8*y1) + (9*y2) + (4*i1) + (5*i2) + (2*i3);
40        if mod(v1, 11) = 0
41        then
42        c1 := 0;
43        else 
44        c1 := 11-(mod(v1,11));
45        end if;
46        v2 := (5*d1) + (4*d2) + (3*m1) + (2*m2) + (7*y1) + (6*y2) + (5*i1) +(4*i2) + (3*i3) + (2*c1);
47        if mod(v2, 11) = 0
48        then
49        c2 := 0;
50        else 
51        c2 := 11-(mod(v2,11));
52        end if;
53    end loop;
54	insert into stian.fnr2  (personnr) values (d1||d2||m1||m2||y1||y2||i1||i2||i3||c1||c2);
55	--where id=1000;
56	
57    dbms_output.put_line(d1||d2||m1||m2||y1||y2||i1||i2||i3||c1||c2);
58   -- dbms_output.put_line('d1' || d1); 
59    --dbms_output.put_line('d2'|| d2);
60   -- dbms_output.put_line('m1'|| m1);
61   -- dbms_output.put_line('m2'|| m2);
62   -- dbms_output.put_line('y1'|| y1 );
63   -- dbms_output.put_line('y2'|| y2 );
64  --  dbms_output.put_line('i1'|| i1 );
65   -- dbms_output.put_line('i2'|| i2 );
66   -- dbms_output.put_line('i3'|| i3 );
67   -- dbms_output.put_line('c1'|| c1 );
68   -- dbms_output.put_line('c2'|| c2 );
69   -- dbms_output.put_line('v1'|| v1 );
70   -- dbms_output.put_line('v2'|| v2 );
71END;
72/
73DECLARE
74   curfnr    VARCHAR2(11);
75   --TYPE curfnr  is table ov varchar2(50);
76   Cursor      c1 IS SELECT fnr FROM stian.fnr;
77  -- where not exists (select substr(personnr,1,6) from stian.fnr2);
78BEGIN
79   OPEN c1;
80   LOOP
81   --   FETCH c1 BULK COLLECT INTO curfnr LIMIT 100;
82	  FETCH c1 INTO curfnr;
83      EXIT WHEN c1%NOTFOUND;
84genssn(curfnr);
85delete from stian.fnr2 where 
86length(personnr) > 11;
87   END LOOP;
88END;
89/