Procedury - MIX - Cacher Snippet

12/14/2018 - 2:07 PM

Procedury - MIX

CREATE PROCEDURE SP_poradi()
BEGIN
 
DECLARE s_poradi INT(5);
DECLARE s_body INT(5);
DECLARE temp_body INT(5) DEFAULT 0;
DECLARE s_tym VARCHAR(50);
DECLARE my_rank INT(5) DEFAULT 0;
DECLARE done TINYINT(1) DEFAULT 0;
 
DECLARE i INT;
 
DECLARE rank CURSOR FOR
SELECT tym, body, poradi FROM poradi
ORDER BY body DESC;
 
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
 
SET i = 0;
OPEN rank;
rank_loop:  LOOP
 
FETCH rank INTO s_tym, s_body, s_poradi;
IF done THEN LEAVE rank_loop; END IF;

 
IF (temp_body <> s_body)
  THEN UPDATE poradi SET poradi = i + 1 WHERE tym = s_tym;
ELSE
  UPDATE poradi SET poradi = i + 0 WHERE tym = s_tym;
END IF;
 
SET i=i+1;
SET temp_body = s_body;  
 
 
END LOOP rank_loop;
CLOSE rank;
 
END;

table-filler.sql

CREATE PROCEDURE SP_Fill() 
BEGIN 

DECLARE i INT DEFAULT 0; 



my_loop: LOOP 
SET i = i + 1; 
IF (i > 1000) 
THEN LEAVE my_loop; 
END IF; 

INSERT INTO poradi VALUES(CONCAT('tym - ', i), i, 0); 

END LOOP my_loop; 
END;

random-rows.sql

CREATE TABLE filler (
        id INT NOT NULL PRIMARY KEY AUTO_INCREMENT
) ENGINE=Memory;

CREATE TABLE t_column (
        id INT NOT NULL PRIMARY KEY,
        col1 VARCHAR(10),
        col2 VARCHAR(10),
        col3 VARCHAR(10),
        col4 VARCHAR(10),
        col5 VARCHAR(10),
        col6 VARCHAR(10),
        col7 VARCHAR(10),
        col8 VARCHAR(10),
        col9 VARCHAR(10),
        col10 VARCHAR(10),
        col11 VARCHAR(10),
        col12 VARCHAR(10),
        col13 VARCHAR(10),
        col14 VARCHAR(10),
        col15 VARCHAR(10),
        col16 VARCHAR(10),
        col17 VARCHAR(10),
        col18 VARCHAR(10),
        col19 VARCHAR(10),
        col20 VARCHAR(10)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DELIMITER $$

CREATE PROCEDURE prc_filler(cnt INT)
BEGIN
        DECLARE _cnt INT;
        SET _cnt = 1;
        WHILE _cnt <= cnt DO
                INSERT
                INTO    filler
                SELECT  _cnt;
                SET _cnt = _cnt + 1;
        END WHILE;
END
$$

DELIMITER ;

START TRANSACTION;
CALL prc_filler(1000000);
COMMIT;

INSERT
INTO    t_column
SELECT  id,
        LPAD('', RAND(20090521) * 10, 'a'), 
        LPAD('', RAND(20090521 << 1) * 10, 'a'), 
        LPAD('', RAND(20090521 << 2) * 10, 'a'), 
        LPAD('', RAND(20090521 << 3) * 10, 'a'), 
        LPAD('', RAND(20090521 << 4) * 10, 'a'), 
        LPAD('', RAND(20090521 << 5) * 10, 'a'), 
        LPAD('', RAND(20090521 << 6) * 10, 'a'), 
        LPAD('', RAND(20090521 << 7) * 10, 'a'), 
        LPAD('', RAND(20090521 << 8) * 10, 'a'), 
        LPAD('', RAND(20090521 << 9) * 10, 'a'), 
        LPAD('', RAND(20090521 << 10) * 10, 'a'), 
        LPAD('', RAND(20090521 << 11) * 10, 'a'), 
        LPAD('', RAND(20090521 << 12) * 10, 'a'), 
        LPAD('', RAND(20090521 << 13) * 10, 'a'), 
        LPAD('', RAND(20090521 << 14) * 10, 'a'), 
        LPAD('', RAND(20090521 << 15) * 10, 'a'), 
        LPAD('', RAND(20090521 << 16) * 10, 'a'), 
        LPAD('', RAND(20090521 << 17) * 10, 'a'), 
        LPAD('', RAND(20090521 << 18) * 10, 'a'), 
        LPAD('', RAND(20090521 << 19) * 10, 'a')
FROM    filler;

random-rows-from-netteforum.sql

DELIMITER $$
DROP PROCEDURE IF EXISTS test$$
CREATE PROCEDURE test() BEGIN DECLARE COUNT INT DEFAULT 0; WHILE COUNT < 10000000 DO
INSERT INTO orders (amount, product)
VALUES (
          (SELECT FLOOR((RAND() * (9999 - 1000 + 1)) + 1000)),
          (SELECT *
           FROM
             (SELECT 'A'
              UNION ALL SELECT 'B'
              UNION ALL SELECT 'C'
              UNION ALL SELECT 'D'
              UNION ALL SELECT 'E'
              UNION ALL SELECT 'F'
              UNION ALL SELECT 'G'
              UNION ALL SELECT 'H'
              UNION ALL SELECT 'I'
              UNION ALL SELECT 'J') t
           ORDER BY RAND()
           LIMIT 1));
SET COUNT = COUNT + 1; END WHILE; END$$
DELIMITER ;

START TRANSACTION;
CALL test();
COMMIT;

kurzy.sql

CREATE DEFINER = `root` @`localhost` PROCEDURE `kurzy`() BEGIN #deklarace promennych
declare dt datetime;

declare m1 double;

declare m2 double;

declare m3 double;

declare m4 double;

declare m5 double;

declare m6 double;

declare m7 double;

declare m8 double;

declare m9 double;

declare m10 double;

declare id integer;

#bool pro konec cyklu
declare v_notfound BOOL default FALSE;

#kurzor
declare csr_emp cursor for
select
        *
from
        #oldDB.kurzy
WHERE
        #oldDB.kurzy.dt < '2008-03-25 16:44:00'
;

#udalost pro konec cyklu
declare continue handler for not found
set
        v_notfound = TRUE;

#udalost pro chybu
declare exit handler for sqlexception close csr_emp;

#pociatocna hodnota indexu
set
        id = 0;

#vytvorim kurzor
open csr_emp;

cursor_loop: loop #nacitam dataset do promennych
fetch csr_emp into dt,
m1,
m2,
m3,
m4,
m5,
m6,
m7,
m8,
m9,
m10;

#osetreni konce datasetu
if v_notfound then leave cursor_loop;

end if;

#vkladam riadky do DB
#1 mena
insert into
        #newDB.cis_rates
values
        (id, 'EUR', dt, m1);

#2 mena
insert into
        #newDB.cis_rates
values
        (id, 'USD', dt, m2);

#3 mena
insert into
        #newDB.cis_rates
values
        (id, 'SKK', dt, m3);

#4 mena
insert into
        #newDB.cis_rates
values
        (id, 'GBP', dt, m4);

#5 mena
insert into
        #newDB.cis_rates
values
        (id, 'CHF', dt, m5);

#6 mena
insert into
        #newDB.cis_rates
values
        (id, 'JPY', dt, m6);

end loop;

#uvolneni kurzoru
close csr_emp;

END

getParent.sql

BEGIN

DECLARE _tempPageId INT DEFAULT 0;
DECLARE _path TEXT DEFAULT '';
DECLARE _name TEXT DEFAULT '';

x: LOOP

	IF ISNULL(pageId) THEN
		LEAVE x;
	END IF;
	
	SELECT parent_id, name FROM pages WHERE id = pageId INTO _tempPageId, _name;
	SET pageId = _tempPageId;
	
	SET _path = CONCAT(_name, "/", _path);

END LOOP x;

SELECT _path;

END

advanced-categories-tree-etc.sql

/* https://github.com/Apress/def-guide-to-mysql-5/blob/master/samples/stored-procedures/sp.sql */

CREATE PROCEDURE categories_insert(IN newcatname VARCHAR(60), IN parent INT, OUT newid INT)
proc: BEGIN

 DECLARE cnt INT;
  SET newid=-1;

  -- basic validation
  SELECT COUNT(*) FROM categories 
  WHERE parentCatID=parent INTO cnt;
  IF ISNULL(newcatname) OR TRIM(newcatname)="" OR cnt=0 THEN
    LEAVE proc;
  END IF;

  -- test if category already exists
  SELECT COUNT(*) FROM categories 
  WHERE parentCatID=parent AND catName=newcatname 
  INTO cnt;
  IF cnt=1 THEN 
    SELECT catID FROM categories 
    WHERE parentCatID=parent AND catName=newcatname 
    INTO newid;
    LEAVE proc; 
  END IF;

  -- actually insert new category
  INSERT INTO categories (catName, parentCatID)
  VALUES (newcatname, parent);
  SET newid = LAST_INSERT_ID();

END proc 

-- ukazka pouziti
CALL categories_insert('Microsoft Access', 2, @catid)
SELECT @catid 


/*---------------------------------------------------------------------------------*/


CREATE PROCEDURE cursortest(OUT avg_len DOUBLE)
BEGIN
  DECLARE t, subt VARCHAR(100);
  DECLARE done INT DEFAULT 0;
  DECLARE n BIGINT DEFAULT 0;
  DECLARE cnt INT;

  DECLARE mycursor CURSOR FOR 
    SELECT title, subtitle FROM titles;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;

  SELECT COUNT(*) FROM titles INTO cnt;
  OPEN mycursor;
  myloop: LOOP
    FETCH mycursor INTO t, subt;
    IF done=1 THEN LEAVE myloop; END IF;
    SET n = n + CHAR_LENGTH(t);
    IF NOT ISNULL(subt) THEN
      SET n = n + CHAR_LENGTH(subt);
    END IF;
  END LOOP myloop;
  SET avg_len = n/cnt;
END$$


/*---------------------------------------------------------------------------------*/


CREATE PROCEDURE `mylibrary`.`find_subcategories`(IN id INT, IN cname VARCHAR(60), IN catlevel INT, INOUT catrank INT)
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE subcats CURSOR FOR 
    SELECT catID, catName FROM categories WHERE parentCatID=id
    ORDER BY catname;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;

  OPEN subcats;
  subcatloop: LOOP
    FETCH subcats INTO id, cname;
    IF done=1 THEN LEAVE subcatloop; END IF;
    SET catrank = catrank+1;
    INSERT INTO __subcats VALUES (catrank, catlevel, id, cname);
    CALL find_subcategories(id, cname, catlevel+1, catrank);
  END LOOP subcatloop;
  CLOSE subcats;
END$$


/*---------------------------------------------------------------------------------*/


CREATE PROCEDURE `mylibrary`.`get_parent_categories`(startid INT)
BEGIN
  DECLARE i, id, pid, cnt INT DEFAULT 0;
  DECLARE cname VARCHAR(60);

  DROP TABLE IF EXISTS __parent_cats;
  CREATE TEMPORARY TABLE __parent_cats 
    (level INT, catID INT, catname VARCHAR(60)) ENGINE = HEAP;

  main: BEGIN 
    -- test if startid is OK
    SELECT COUNT(*) FROM categories WHERE catID=startID INTO cnt;
    IF cnt=0 THEN LEAVE main; END IF;

    -- insert start category into new table
    SELECT catID, parentCatID, catName 
    FROM categories WHERE catID=startID 
    INTO id, pid, cname;
    INSERT INTO __parent_cats VALUES(i, id, cname);

    -- loop until root of categories is reached
    parentloop: WHILE NOT ISNULL(pid) DO
      SET i=i+1;
      SELECT catID, parentCatID, catName 
      FROM categories WHERE catID=pid 
      INTO id, pid, cname;
      INSERT INTO __parent_cats VALUES(i, id, cname);
    END WHILE parentloop;
  END main;

  SELECT catID, catname FROM __parent_cats ORDER BY level DESC;
  DROP TABLE __parent_cats;
END$$


/*---------------------------------------------------------------------------------*/


CREATE PROCEDURE `mylibrary`.`get_subcategories`(IN startid INT, OUT n INT)
BEGIN
  DECLARE cnt INT;
  DECLARE cname VARCHAR(60);
  DROP TABLE IF EXISTS __subcats;
  CREATE TEMPORARY TABLE __subcats
    (rank INT, level INT, catID INT, catname VARCHAR(60)) ENGINE = HEAP;
  SELECT COUNT(*) FROM categories WHERE catID=startID INTO cnt;
  IF cnt=1 THEN 
    SELECT catname FROM categories WHERE catID=startID INTO cname;
    INSERT INTO __subcats VALUES(0, 0, startid, cname);
    CALL find_subcategories(startid, cname, 1, 0);
  END IF;
  SELECT COUNT(*) FROM __subcats INTO n;
END$$


/*---------------------------------------------------------------------------------*/


CREATE PROCEDURE `mylibrary`.`get_title`(IN id INT)
BEGIN
  SELECT title, subtitle, publName 
  FROM titles, publishers 
  WHERE titleID=id 
  AND titles.publID = publishers.publID;
END$$
DROP FUNCTION IF EXISTS mylibrary.shorten$$
CREATE FUNCTION `mylibrary`.`shorten`(s VARCHAR(255), n INT) RETURNS VARCHAR(255)
BEGIN
  IF ISNULL(s) THEN
    RETURN '';
  ELSEIF n<15 THEN
    RETURN LEFT(s, n);
  ELSE
    IF CHAR_LENGTH(s) <= n THEN
      RETURN s;
    ELSE
      RETURN CONCAT(LEFT(s, n-10), ' ... ', RIGHT(s, 5));
    END IF;
  END IF;
  
END$$


/*---------------------------------------------------------------------------------*/


CREATE FUNCTION `mylibrary`.`swap_name`(s VARCHAR(100)) RETURNS VARCHAR(100)
BEGIN
  DECLARE pos, clen INT;
  SET s = TRIM(s);
  SET clen = CHAR_LENGTH(s);
  SET pos =  LOCATE(" ", REVERSE(s));
  IF pos = 0 THEN RETURN s; END IF;
  SET pos = clen-pos;
  RETURN CONCAT(SUBSTR(s, pos+2), " ", LEFT(s, pos));
END$$



CREATE PROCEDURE `mylibrary`.`titles_insert_all`(IN newtitle VARCHAR(100), IN publ VARCHAR(60), IN authList VARCHAR(255), OUT newID INT)
proc: BEGIN
  DECLARE cnt, pos INT;
  DECLARE aID, pblID, ttlID INT;
  DECLARE author VARCHAR(60);
  SET newID=-1;

  -- publisher
  SELECT COUNT(*) FROM publishers WHERE publname=publ INTO cnt;
  IF cnt=1 THEN
    SELECT publID FROM publishers WHERE publname=publ INTO pblID;
  ELSE
    INSERT INTO publishers (publName) VALUES (publ);
    SET pblID = LAST_INSERT_ID();
  END IF;

  -- insert title
  INSERT INTO titles (title, publID) VALUES (newtitle, pblID);
  SET ttlID = LAST_INSERT_ID();

  -- loop through all authors
  authloop: WHILE NOT (authList="") DO
    SET pos = LOCATE(";", authList);
    IF pos=0 THEN
      SET author = TRIM(authList);
      SET authList ="";
    ELSE
      SET author = TRIM(LEFT(authList, pos-1));
      SET authList = SUBSTR(authList, pos+1);
    END IF;
    IF author = "" THEN ITERATE authloop; END IF;

    -- find author or insert into authors table
    SELECT COUNT(*) FROM authors 
    WHERE authName=author OR authName=swap_name(author)
    INTO cnt;
    IF cnt>=1 THEN
      SELECT authID FROM authors 
      WHERE authName=author OR authName=swap_name(author)
      LIMIT 1 INTO aID;
    ELSE
      INSERT INTO authors (authName) VALUES (author);
      SET aID = LAST_INSERT_ID();
    END IF;

    -- update rel_title_authors
    INSERT INTO rel_title_author (titleID, authID)
    VALUES (ttlID, aID);
  END WHILE authloop;

  -- return value
  SET newID=ttlID;  
END proc$$


/*---------------------------------------------------------------------------------*/

SELECT
    rank,
    level,
    catID,
    CONCAT(SPACE(level * 2), catname)
FROM
    __subcats
ORDER BY
    rank rank level catID CONCAT(SPACE(level * 2), catname) 

0 0 1 Computer books
1 1 2 Databases
2 2 69 IBM DB/2
3 2 86 Microsoft Access
4 2 67 Microsoft SQL Server
5 2 34 MySQL
6 2 5 Object-oriented databases
7 2 68 Oracle
8 2 77 PostgreSQL
9 2 4 Relational Databases
10 2 8 SQL
11 1 36 LaTeX, TeX
12 1 56 Operating Systems
13 2 57 Linux
14 2 58 Mac OS
15 2 59 Windows
16 1 3 Programming
17 2 54 C
18 2 53 C#
19 2 55 C++
20 2 50 Java
21 2 7 Perl
22 2 6 PHP
23 2 52 VBA
24 2 51 Visual Basic
25 2 60 Visual Basic .NET

debug-procedure.sql

DELIMITER $$

DROP PROCEDURE IF EXISTS `debug_msg`$$
DROP PROCEDURE IF EXISTS `test_procedure`$$

CREATE PROCEDURE debug_msg(enabled INTEGER, msg VARCHAR(255))
BEGIN
  IF enabled THEN
    select concat('** ', msg) AS '** DEBUG:';
  END IF;
END $$

CREATE PROCEDURE test_procedure(arg1 INTEGER, arg2 INTEGER)
BEGIN
  SET @enabled = TRUE;

  call debug_msg(@enabled, 'my first debug message');
  call debug_msg(@enabled, (select concat_ws('','arg1:', arg1)));
  call debug_msg(TRUE, 'This message always shows up');
  call debug_msg(FALSE, 'This message will never show up');
END $$

DELIMITER ;

Transaction-in-procedure.sql

DELIMITER //
CREATE PROCEDURE SafeTransfer(
    IN from_account INT,
    IN to_account INT,
    IN amount DECIMAL(10,2)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SELECT 'Transaction Failed' AS message;
    END;

START TRANSACTION;
    UPDATE accounts SET balance = balance - amount WHERE id = from_account;
    UPDATE accounts SET balance = balance + amount WHERE id = to_account;
    COMMIT;
    SELECT 'Transaction Successful' AS message;
END //
DELIMITER ;

CALL SafeTransfer(1, 2, 100.00)

+----------------------+
| message              |
+----------------------+
| Transaction Successful |
+----------------------+

Temporary-teble-loop.sql

DELIMITER //
CREATE PROCEDURE MultiplicationTable(IN num INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    
    CREATE TEMPORARY TABLE temp_table (n INT, result INT);

WHILE i <= 10 DO
        INSERT INTO temp_table VALUES (i, i * num);
        SET i = i + 1;
    END WHILE;
    SELECT * FROM temp_table;
    DROP TEMPORARY TABLE temp_table;
END //
DELIMITER ;

CALL MultiplicationTable(5);

+----+--------+
| n  | result |
+----+--------+
| 1  | 5      |
| 2  | 10     |
| 3  | 15     |
| 4  | 20     |
| 5  | 25     |
| 6  | 30     |
| 7  | 35     |
| 8  | 40     |
| 9  | 45     |
| 10 | 50     |
+----+--------+

Conditional-logic.sql

DELIMITER //
CREATE PROCEDURE ApplyBonus(IN rating INT)
BEGIN
    IF rating = 5 THEN
        UPDATE employees SET salary = salary * 1.10;
    ELSEIF rating = 4 THEN
        UPDATE employees SET salary = salary * 1.05;
    ELSE
        UPDATE employees SET salary = salary * 1.02;
    END IF;
END //
DELIMITER ;

CALL ApplyBonus(5);

Cacher is the code snippet organizer for pro developers

We empower you and your team to get more done, faster

Procedury - MIX