Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- # create database testdb
- #
- # use testdb
- #
- # CREATE TABLE `users` (
- # `id` int(11) NOT NULL AUTO_INCREMENT,
- # `name` varchar(32) NOT NULL,
- # `gender` tinyint(2) NOT NULL,
- # `email` varchar(1024) NOT NULL,
- # PRIMARY KEY (`id`)
- # ) ENGINE=InnoDB;
- #
- #
- #
- #
- drop procedure parse_emails;
- drop procedure extract_domains;
- delimiter //
- create procedure parse_emails(emails_list varchar(1024))
- begin
- declare len int unsigned;
- declare cur_pos int unsigned default 0;
- # declare found_emails int unsigned default 0;
- declare cur_email varchar(255);
- # declare lp_emails varchar(1024);
- declare cur_domain varchar(255);
- case
- when
- LENGTH(emails_list) < 1
- then
- set len = 0;
- else
- set len = count_str(emails_list, ',') + 1;
- end case;
- while cur_pos < len
- do
- set cur_email = SUBSTRING_INDEX(SUBSTRING_INDEX(emails_list, ',', cur_pos + 1), ',', -1);
- # set lp_emails = substring_index(emails_list, ',', cur_pos); # обрезаем по длине
- # set cur_email = substring_index(lp_emails, ',', -1); # достаём последний
- set cur_domain = substring_index(cur_email, '@', -1);
- case
- when
- not exists(select * from domains where name = cur_domain)
- then
- insert into domains (name, number) value (cur_domain, 1);
- else
- update domains set number = number + 1 where name = cur_domain;
- end case;
- # set found_emails = found_emails + 1;
- # set cur_pos = cur_pos + length(cur_email) + 1;
- set cur_pos = cur_pos + 1;
- end while;
- end //
- delimiter //
- create procedure extract_domains()
- begin
- declare done bool default false;
- declare emailListItem varchar(1024);
- declare cursorEmailList cursor for select email from users;
- declare continue handler for not found set done = true;
- drop table if exists domains;
- create table domains
- (
- name varchar(255) not null,
- number int not null,
- PRIMARY KEY (name)
- ) engine = InnoDB;
- open cursorEmailList;
- myloop: while done = false
- do
- fetch cursorEmailList into emailListItem;
- IF done THEN
- LEAVE myloop;
- END IF;
- call parse_emails(emailListItem);
- end while myloop;
- close cursorEmailList;
- end //
- call extract_domains();
- call parse_emails('fsgdfh@ya.ru,te@example.com');
- call parse_emails('te@example.com');
- select * from domains;
- delete from domains;
- insert into users (name, gender, email) values ('vasya', 1, 'test@example.com');
- insert into users (name, gender, email) values ('petya', 1, 'fsgdfh@ya.ru');
- insert into users (name, gender, email) values ('sanya', 1, 'te@example.com');
- select * from users;
- select count_str('test@example.com,fahh@gmail.ru', ',');
- drop function count_str;
- CREATE FUNCTION count_str(haystack VARCHAR(1024), needle VARCHAR(32))
- RETURNS INTEGER DETERMINISTIC
- BEGIN
- RETURN ROUND((CHAR_LENGTH(haystack) - CHAR_LENGTH(REPLACE(haystack, needle, ""))) / CHAR_LENGTH(needle));
- END;
- drop function if exists extract_test;
- create function extract_test() RETURNS varchar(1024) DETERMINISTIC
- begin
- declare emailListItem varchar(1024);
- declare cursorEmailList cursor for select email from users;
- open cursorEmailList;
- fetch cursorEmailList into emailListItem;
- close cursorEmailList;
- return emailListItem;
- end;
- select extract_test();
- drop function extract_ls;
- create function extract_ls() RETURNS varchar(4024) DETERMINISTIC
- begin
- declare done bool default false;
- declare emailListItem varchar(1024);
- declare res varchar(4024) default '';
- declare cursorEmailList cursor for select email from users;
- declare continue handler for not found set done = true;
- open cursorEmailList;
- myloop: while done = false
- do
- fetch cursorEmailList into emailListItem;
- IF done THEN
- LEAVE myloop;
- END IF;
- set res = concat(res, ' _ ', emailListItem);
- end while myloop;
- close cursorEmailList;
- return res;
- end;
- select extract_ls();
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement