h85kNeUx

1# create database testdb
2#
3# use testdb
4#
5# CREATE TABLE `users` (
6# `id` int(11) NOT NULL AUTO_INCREMENT,
7# `name` varchar(32) NOT NULL,
8# `gender` tinyint(2) NOT NULL,
9# `email` varchar(1024) NOT NULL,
10# PRIMARY KEY (`id`)
11# ) ENGINE=InnoDB;
12#
13#
14#
15#
16
17drop procedure parse_emails;
18drop procedure extract_domains;
19
20delimiter //
21create procedure parse_emails(emails_list varchar(1024))
22begin
23    declare len int unsigned;
24    declare cur_pos int unsigned default 0;
25    # declare found_emails int unsigned default 0;
26    declare cur_email varchar(255);
27#     declare lp_emails varchar(1024);
28    declare cur_domain varchar(255);
29
30    case
31        when
32            LENGTH(emails_list) < 1
33        then
34            set len = 0;
35        else
36            set len = count_str(emails_list, ',') + 1;
37    end case;
38
39    while cur_pos < len
40    do
41        set cur_email = SUBSTRING_INDEX(SUBSTRING_INDEX(emails_list, ',', cur_pos + 1), ',', -1);
42#         set lp_emails = substring_index(emails_list, ',', cur_pos); # обрезаем по длине
43#         set cur_email = substring_index(lp_emails, ',', -1); # достаём последний
44        set cur_domain = substring_index(cur_email, '@', -1);
45
46        case
47            when
48                not exists(select * from domains where name = cur_domain)
49            then
50                insert into domains (name, number) value (cur_domain, 1);
51            else
52                update domains set number = number + 1 where name = cur_domain;
53        end case;
54
55        # set found_emails = found_emails + 1;
56        # set cur_pos = cur_pos + length(cur_email) + 1;
57        set cur_pos = cur_pos + 1;
58    end while;
59end //
60
61delimiter //
62create procedure extract_domains()
63begin
64    declare done bool default false;
65    declare emailListItem varchar(1024);
66    declare cursorEmailList cursor for select email from users;
67    declare continue handler for not found set done = true;
68
69    drop table if exists domains;
70    create table domains
71    (
72        name varchar(255) not null,
73        number int not null,
74        PRIMARY KEY (name)
75    ) engine = InnoDB;
76
77    open cursorEmailList;
78
79    myloop: while done = false
80    do
81        fetch cursorEmailList into emailListItem;
82        IF done THEN
83            LEAVE myloop;
84        END IF;
85        call parse_emails(emailListItem);
86    end while myloop;
87
88    close cursorEmailList;
89end //
90
91call extract_domains();
92
93
94
95
96call parse_emails('fsgdfh@ya.ru,te@example.com');
97call parse_emails('te@example.com');
98
99select * from domains;
100delete from domains;
101
102insert into users (name, gender, email) values ('vasya', 1, 'test@example.com');
103insert into users (name, gender, email) values ('petya', 1, 'fsgdfh@ya.ru');
104insert into users (name, gender, email) values ('sanya', 1, 'te@example.com');
105
106select * from users;
107
108select count_str('test@example.com,fahh@gmail.ru', ',');
109
110drop function count_str;
111
112CREATE FUNCTION count_str(haystack VARCHAR(1024), needle VARCHAR(32))
113  RETURNS INTEGER DETERMINISTIC
114  BEGIN
115    RETURN ROUND((CHAR_LENGTH(haystack) - CHAR_LENGTH(REPLACE(haystack, needle, ""))) / CHAR_LENGTH(needle));
116  END;
117
118
119drop function if exists extract_test;
120
121create function extract_test() RETURNS varchar(1024) DETERMINISTIC
122begin
123    declare emailListItem varchar(1024);
124    declare cursorEmailList cursor for select email from users;
125    open cursorEmailList;
126    fetch cursorEmailList into emailListItem;
127    close cursorEmailList;
128    return emailListItem;
129end;
130
131select extract_test();
132
133drop function extract_ls;
134create function extract_ls() RETURNS varchar(4024) DETERMINISTIC
135begin
136    declare done bool default false;
137    declare emailListItem varchar(1024);
138    declare res varchar(4024) default '';
139    declare cursorEmailList cursor for select email from users;
140    declare continue handler for not found set done = true;
141
142    open cursorEmailList;
143    myloop: while done = false
144    do
145        fetch cursorEmailList into emailListItem;
146        IF done THEN
147          LEAVE myloop;
148        END IF;
149        set res = concat(res, ' _ ', emailListItem);
150    end while myloop;
151    close cursorEmailList;
152    return res;
153end;
154
155select extract_ls();