Arxiv

Archive for the ‘My Stored Routines’ Category

Insert procedure with incremental session variable

Oktyabr 20, 2013 Bir şərh yazın

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)

İyul 29, 2013 Bir şərh yazın

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 😉

Kateqoriyalar: My Stored Routines Etiketlər: ,

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

Mart 13, 2013 Bir şərh yazın 
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

Yanvar 13, 2013 Bir şərh yazın 
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

Yanvar 13, 2013 Bir şərh yazın 
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