Archive

Archive for the ‘My Stored Routines’ Category

Insert procedure with incremental session variable

Bu MySQL prosedur daxilinde biz 1 сədvəldən cursor vasitəsilə məlumatı çəkib digər bir cədvələ session variable increment etməklə insert edirik.
Şəxsi task-ım olan bu prosedur kodunu paylaşıram:

DELIMITER $$
 
CREATE  PROCEDURE insert_it()
BEGIN
    declare var_match int;
	declare v_last_row_fetched int default 0;
	
     
    declare cursor1 cursor for
    select id from wd_matches;
 
    declare continue handler for not found set v_last_row_fetched=1;
 
    set v_last_row_fetched=0;
    open cursor1;
    cursor_loop: loop
    fetch cursor1 into var_match;
            if v_last_row_fetched=1 then
                    leave cursor_loop;
            end if;
			
			set @matcher = var_match;
			set @counter=0;

			inserting: loop
				if @counter > 17 then
					leave inserting;
				end if;

				set @counter = @counter+1;
				
				insert into wd_squads(`match`,player,ord,`status`) values(@matcher,0,@counter,'0');
				
			end loop inserting;
    end loop cursor_loop;
    close cursor1;
    set v_last_row_fetched=0;
end $$

Təşəkkürlər 😉

Kateqoriyalar: My Stored Routines

MySQL Stored function\SQL imkanları ilə text parsing(Advance Usage)

Bugünkü mövzu bir xeyli maraqlı olduğu üçün təcili olaraq qeyd etməkdən zövq alıram.
Əlimizdə olan cədvəlimiz:

CREATE TABLE `wd_universities` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(150) NOT NULL,
  `country` tinyint(3) unsigned NOT NULL,
  `city` smallint(5) unsigned NOT NULL,
  `status` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=128 DEFAULT CHARSET=utf8

Və içindəki məlumatlardan bir qismi:

*************************** 119. row ***************************
     id: 119
   name: <!--az>9 N-li orta məktəb</az--><!--en>9 N-li orta məktəb</en--><!--ru>9 N-li orta məktəb</ru-->
country: 0
   city: 0
 status: 0
*************************** 120. row ***************************
     id: 120
   name: <!--az>Azerbaycan muellimler inisitutu</az--><!--en>Azerbaycan muellimler inisitutu</en--><!--ru>Azerbaycan muellimler inisitutu</ru-->
country: 0
   city: 0
 status: 0

Gördüyümüz kimi 1 row daxilində

<!--az></az--> , <!--en></en-->, <!--ru></ru-->

tag-ləri arasında məlumatlar var. Bizə lazımdır ki,

<!--en></en-->

tag-ləri arasında olan məlumatı İD-ni verməklə götürək.

İlk öncə

<!--en></en-->

tag-lərinin yer aldığı row-ları match etməliyik. Burda regexp bizə yardım edir:

mysql> select id,name from wd_universities where name regexp '<\!--en>.*</en-->';
.
.
| 107 | <!--az>koopirasiya kolleci</az--><!--en>koopirasiya kolleci</en--><!--ru>koopirasiya kolleci</ru-->                                                                            |
| 108 | <!--az>Gəncə Dövlət Universiteti</az--><!--en>Gəncə Dövlət Universiteti</en--><!--ru>Gəncə Dövlət Universiteti</ru-->                                                          |
| 109 | <!--az>Qafqaz Universiteti</az--><!--en>Qafqaz Universiteti</en--><!--ru>Qafqaz Universiteti</ru-->                                                                            |
| 110 | <!--az>Azerbaycan malliya iqtisad kolleci</az--><!--en>Azerbaycan malliya iqtisad kolleci</en--><!--ru>Azerbaycan malliya iqtisad kolleci</ru-->                               |
| 111 | <!--az>QERB UNIVERSITETI</az--><!--en>QERB UNIVERSITETI</en--><!--ru>QERB UNIVERSITETI</ru--> 

İkinci olaraq bizə bu row-lar daxilində

<!--en>

-li məlumatı çıxarmaq lazımdır. Onu da aşağıdakı sorğu ilə müəyyən edirik:

select 
    substring((select name from wd_universities where
                name regexp '<!--en>.*</en-->' and id = 108),
        locate('<!--en>',
                (select name from wd_universities where
                        name regexp '<!--en>.*</en-->' and id = 108)),
        (locate('</en-->',
                (select name from wd_universities
                    where name regexp '<!--en>.*</en-->' and id = 108))) - 
           (locate('<!--en>',
                (select name from wd_universities
                    where name regexp '<!--en>.*</en-->' and id = 108))));

Nümunə Output:

  <!--en>Gəncə Dövlət Universiteti 

Daha sonra bizə lazımdır ki tag-ləri əvvəldən və sondan silən bir funksiyamız olsun.Funksiya:

delimiter $$
 
CREATE FUNCTION fnStripTags( Dirty varchar(200) )
RETURNS varchar(200)
DETERMINISTIC
BEGIN
  DECLARE iStart, iEnd, iLength int;
    WHILE Locate( '<', Dirty ) > 0 And Locate( '>', Dirty, Locate( '<', Dirty )) > 0 DO
      BEGIN
        SET iStart = Locate( '<', Dirty ), iEnd = Locate( '>', Dirty, Locate('<', Dirty ));
        SET iLength = ( iEnd - iStart) + 1;
        IF iLength > 0 THEN
          BEGIN
            SET Dirty = Insert( Dirty, iStart, iLength, '');
          END;
        END IF;
      END;
    END WHILE;
    RETURN Dirty;
END $$

Funksiya istifadəsi:

mysql> select fnStripTags('<!--en>Gəncə Dövlət Universiteti');
+-----------------------------------------------------+
| fnStripTags('<!--en>Gəncə Dövlət Universiteti')     |
+-----------------------------------------------------+
| Gəncə Dövlət Universiteti                           |
+-----------------------------------------------------+
1 row in set (0.00 sec)

Son olaraq və ən əsas olaraq bizim məntiqi sonluq yaradan funksiyamız. Funksiya parametr olaraq İD-ni qəbul edir. Bu İD-yə uyğun olan

<!--en-->

-li məlumatı tapır. Tag-ləri silir və bizə təmiz lazım olan məlumatı çıxardır:

DELIMITER $$
 
CREATE  function exclude_tags_en(u_id int)
RETURNS varchar(150) charset utf8
DETERMINISTIC
BEGIN
    declare v_name varchar(150) charset utf8;
    declare v_last_row_fetched int default 0;
    declare v_result varchar(150) charset utf8;
       
     declare cursor1 cursor for
        select name from wd_universities where name regexp '<\!--en>.*</en-->' and id=u_id;
 
    declare continue handler for not found set v_last_row_fetched=1;
 
    set v_last_row_fetched=0;
    open cursor1;
    cursor_loop: loop
    fetch cursor1 into v_name;
            if v_last_row_fetched=1 then
                    leave cursor_loop;
            end if;
                       
    set @query_parse=(select substring((v_name),
        locate('<!--en>',(v_name)),
        (locate('</en-->',(v_name)))-(locate('<!--en>',(v_name)))));
                       
                set v_result=(SELECT fnStripTags(@query_parse));
                return v_result;      
    end loop cursor_loop;
    close cursor1;
    set v_last_row_fetched=0;
     
END$$

Istifadəsi:

mysql> select exclude_tags_en(108);
+-------------------------------+
| exclude_tags_en(108)          |
+-------------------------------+
| Gəncə Dövlət Universiteti     |
+-------------------------------+
1 row in set (0.00 sec)

mysql> select exclude_tags_en(109);
+----------------------+
| exclude_tags_en(109) |
+----------------------+
| Qafqaz Universiteti  |
+----------------------+
1 row in set (0.00 sec)

Beləliklə tapşırıq yerinə yetirilmişdir.
Təşəkkürlər 😉

Kateqoriyalar: My Stored Routines

Fibonacci ardıcıllığı

İyun 21, 2013 2 şərh

Biraz da maraq qataq işimizə ))
Bu procedure input olaraq verilən rəqəmin sayı qədər fibonacci sequence ardıcıllığı generate edir.

delimiter $$

create procedure fibonacci(n int)
begin
		declare n1 int default not null;
		declare n2 int default not null;
		declare n3 int default 0;
		declare seq text;
		declare count int default 0;

		set count=0;
		set n1=1;
		set n2=0;
		set n3=null;
		set seq='';

		while count<n do
				set count=count+1;
				set n3 = n1 + n2; 
				set n1=n2;
				set n2 = n3;
				set seq = (select CONCAT(seq,n3,', ') from dual);
		end while;
	
		SELECT seq AS fibonacci_numbers;
		
end $$

Və məsələn ilk 5 Fibonacci rəqəmləri:

mysql> call fibonacci(5);
+-------------------+
| fibonacci_numbers |
+-------------------+
| 1, 1, 2, 3, 5,    |
+-------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

İlk 10:

mysql> call fibonacci(10);
+------------------------------------+
| fibonacci_numbers                  |
+------------------------------------+
| 1, 1, 2, 3, 5, 8, 13, 21, 34, 55,  |
+------------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Təşəkkürlər 😉

Müəyyən həcmdən böyük cədvəllər üçün EVENT

Delimiter $$
CREATE EVENT event_huge_table_ ON SCHEDULE EVERY 20 second
COMMENT "Mueyyen hecmden boyuk cedveller uzerinde is" DO 
BEGIN 
    DECLARE v_total_mb FLOAT(10,4); 
    declare v_last_row_fetched int default 0;
    
	 DECLARE cursor1 CURSOR FOR
       SELECT 
        sum(TRUNCATE((data_length+index_length)/1024/1024,4)) as total_mb
        FROM information_schema.`TABLES`
        where table_schema='mysqlspp'
        group by table_name;

   declare continue handler for not found set v_last_row_fetched=1;
   
	set v_last_row_fetched=0;
	 
	 OPEN cursor1;
      cursor_loop: LOOP FETCH cursor1 INTO v_total_mb; 
 
        IF v_last_row_fetched=1 THEN
				leave cursor_loop;
        END IF; 
        
        
        if v_total_mb>20 then
           insert into test_zerofill(name) values('dusen var?');
        end if;
		     
       end loop cursor_loop;
    close cursor1;
    set v_last_row_fetched=0;
END $$
Kateqoriyalar: My Stored Routines

Alter Table Charset and Collation

DELIMITER $$

CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `multiple_alter_table_charset`()
BEGIN
	declare v_table_name varchar(30);
	declare v_last_row_fetched int default 0;
	declare v_table_schema varchar(30);
	
	declare cursor1 cursor for
	select table_schema,table_name from information_schema.tables
	where table_schema not in ('mysql' , 'performance_schema', 'information_schema')
	and table_type = 'base table'
	and table_collation = 'latin1_swedish_ci';

	declare continue handler for not found set v_last_row_fetched=1;

	set v_last_row_fetched=0;
	open cursor1;
	cursor_loop: loop
	fetch cursor1 into v_table_schema,v_table_name;
			if v_last_row_fetched=1 then
					leave cursor_loop;
			end if;
			
			set @sql_v=concat('alter table ',v_table_schema,'.',v_table_name,' CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci');
			prepare stmt from @sql_v;
			EXECUTE stmt;
			deallocate prepare stmt;
	end loop cursor_loop;
	close cursor1;
	set v_last_row_fetched=0;
	
END$$

Procedure bütün latin1-ləri tapır və hamısını UTF8-ə çevirir

*Diqqət!!! Production-da istifadə etməzdən əvvəl hər hansı bir ERROR əleyhinə test-də sınamaq məsləhətdir.*

Kateqoriyalar: My Stored Routines

Alter Table Engine procedure

delimiter $$

CREATE PROCEDURE `multiple_alter`()
begin 
	declare v_table_schema varchar(30);
	declare v_table_name varchar(30);
	declare v_last_row_fetched int default 0;
	
	declare cursor1 cursor for
	select 
        a.table_schema, a.table_name from
        information_schema.tables as a
        where a.engine = 'MyISAM'
	and a.table_schema not in ('mysql' , 'information_schema','performance_schema');

	declare continue handler for not found set v_last_row_fetched=1;

	set v_last_row_fetched=0;
	open cursor1;
	cursor_loop: loop
	fetch cursor1 into v_table_schema,v_table_name;
			if v_last_row_fetched=1 then
					leave cursor_loop;
			end if;
			
			set @sql_v=concat('alter table ',v_table_schema,'.',v_table_name,' engine=innodb');
			prepare stmt from @sql_v;
			EXECUTE stmt;
			deallocate prepare stmt;
	end loop cursor_loop;
	close cursor1;
	set v_last_row_fetched=0;
	
end$$

Yuxarıdakı procedure database-də olan bütün MyİSAM cədvəlləri tapır və hamısını İnnoDB-yə çevirir.

Diqqət!!! Production-da istifadə etməzdən əvvəl hər hansı bir ERROR əleyhinə test-də sınamaq məsləhətdir.

Kateqoriyalar: My Stored Routines