Advertisement
Guest User

Untitled

a guest
Oct 13th, 2019
208
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 4.21 KB | None | 0 0
  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.  
  17. drop procedure parse_emails;
  18. drop procedure extract_domains;
  19.  
  20. delimiter //
  21. create procedure parse_emails(emails_list varchar(1024))
  22. begin
  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;
  59. end //
  60.  
  61. delimiter //
  62. create procedure extract_domains()
  63. begin
  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;
  89. end //
  90.  
  91. call extract_domains();
  92.  
  93.  
  94.  
  95.  
  96. call parse_emails('fsgdfh@ya.ru,te@example.com');
  97. call parse_emails('te@example.com');
  98.  
  99. select * from domains;
  100. delete from domains;
  101.  
  102. insert into users (name, gender, email) values ('vasya', 1, 'test@example.com');
  103. insert into users (name, gender, email) values ('petya', 1, 'fsgdfh@ya.ru');
  104. insert into users (name, gender, email) values ('sanya', 1, 'te@example.com');
  105.  
  106. select * from users;
  107.  
  108. select count_str('test@example.com,fahh@gmail.ru', ',');
  109.  
  110. drop function count_str;
  111.  
  112. CREATE 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.  
  119. drop function if exists extract_test;
  120.  
  121. create function extract_test() RETURNS varchar(1024) DETERMINISTIC
  122. begin
  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;
  129. end;
  130.  
  131. select extract_test();
  132.  
  133. drop function extract_ls;
  134. create function extract_ls() RETURNS varchar(4024) DETERMINISTIC
  135. begin
  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;
  153. end;
  154.  
  155. select extract_ls();
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement