MySQL Stored Procedure Learning Resources

Glad my comments were useful @StevenM!

I like learning from the MySQL Tutorial folks… see their link below.

But I basically quite quickly got a couple of snippets together and just went from there. The most useful ones I have are below.

Oh, and of course you have to consider using MySQL 8 now… well worthwhile reading up on that and using it in your development… where I think my looping example below has become obsolete but I’ve not yet researched the new method.

Good luck!

Best wishes,
Antony.

HERE IS MY LATEST MYSQL SNIPPETS FILE…

SELECT SUBSTRING(MD5(RAND()) FROM 1 FOR 16) AS password;

-- ---------------------------------------------------------------------------------------
-- Command line mysql
-----------------------------------------------------------------------------------------
mysql -h hostname -u user database < script.sql

-----------------------------------------------------------------------------------------
-- if-then-else
--     conditions <>   =   xxx
-----------------------------------------------------------------------------------------
IF (this_value <> 0) THEN 
	-- statements
ELSE
	-- statements
END IF;

-----------------------------------------------------------------------------------------
-- Last Inserted Item
-----------------------------------------------------------------------------------------
SET @this_variable = LAST_INSERT_ID(); 

-----------------------------------------------------------------------------------------
-- Counting Things
-----------------------------------------------------------------------------------------
SELECT COUNT(*) FROM table_name WHERE condition;

-----------------------------------------------------------------------------------------
-- Concatenation and Replace
-----------------------------------------------------------------------------------------
CONCAT(string1,string2, ... );                       -- a null makes the whole thing null
CONCAT_WS(seperator,string1,string2, ... );          -- a null is okay!
REPLACE(field_name,        string_to_find,        string_to_replace);  -- doesn't support % etc.

-----------------------------------------------------------------------------------------
-- SELECT VARIATIONS
-----------------------------------------------------------------------------------------
SELECT * FROM contacts WHERE first_name LIKE '%text%';
SELECT field_name INTO @field_name FROM table_name WHERE condition;

-----------------------------------------------------------------------------------------
-- CREATING AND MODIFYING ROWS
-----------------------------------------------------------------------------------------
INSERT INTO table_name (column1, column2...)
                VALUES (value1, value2, ...);
                
UPDATE table_name SET column1=value1, column2=value2 WHERE search_filter LIMIT 1;

DELETE FROM table_name WHERE (column = value);

-----------------------------------------------------------------------------------------
-- CREATE TABLE IF NOT EXISTS THIS_TABLE (id INT PRIMARY KEY AUTO_INCREMENT NOT NULL);
-----------------------------------------------------------------------------------------
ALTER TABLE THIS_TABLE ADD COLUMN subscriber_user INT UNSIGNED;
ALTER TABLE THIS_TABLE ADD FOREIGN KEY (subscriber_user) REFERENCES users(id);

ALTER TABLE THIS_TABLE ADD COLUMN THIS_COLUMN VARCHAR(128);              -- 
ALTER TABLE THIS_TABLE ADD COLUMN THIS_COLUMN INT UNSIGNED;              -- 
ALTER TABLE THIS_TABLE ADD COLUMN THIS_COLUMN BOOLEAN;                   -- 
ALTER TABLE THIS_TABLE ADD COLUMN THIS_COLUMN DATE;                      -- 
ALTER TABLE THIS_TABLE ADD COLUMN THIS_COLUMN TIMESTAMP;                 -- 

ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE KEY (email);
CREATE INDEX email ON users(email);

-- ---------------------------------------------------
-- PROCEDURE_X (ROW_IN, NEW_VALUE)
-- ---------------------------------------------------
DROP PROCEDURE IF EXISTS PROCEDURE_X;
DELIMITER //
CREATE PROCEDURE PROCEDURE_X ( ROW_IN INT UNSIGNED, NEW_VALUE INT UNSIGNED ) 
this_procedure: BEGIN
	LEAVE this_procedure;
END 
// DELIMITER ;

-- ---------------------------------------------------
-- DELETE_STRUCTURE (THIS_TABLE_ROW)
-- ---------------------------------------------------
DROP PROCEDURE IF EXISTS DELETE_STRUCTURE;
DELIMITER //
CREATE PROCEDURE DELETE_STRUCTURE ( IN THIS_TABLE_ROW_IN INT UNSIGNED)
BEGIN
SET FOREIGN_KEY_CHECKS = 0; -- turns off FK checks so tables can be deleted in whatever order you want!
DELETE FROM THIS_TABLE_ROW WHERE (id=THIS_TABLE_ROW_IN);
DELETE FROM OTHER_TABLE_ROW WHERE (THIS_COLUMN=THIS_TABLE_ROW_IN);
SET FOREIGN_KEY_CHECKS = 1;
END 
// DELIMITER ;

-- ---------------------------------------------------
-- COPY_COLUMN
-- ---------------------------------------------------
UPDATE TABLE_NAME SET DESTINATION_COLUMN=SOURCE_COLUMN;

-- ---------------------------------------------------
-- COPY_ROWS (subscriber_user, old_event, new_event)
-- ---------------------------------------------------
DROP PROCEDURE IF EXISTS COPY_ROWS;
DELIMITER //
CREATE PROCEDURE COPY_ROWS ( subscriber_user_in INT UNSIGNED, old_event INT UNSIGNED, new_event INT UNSIGNED ) 
BEGIN
	-- DECLARE variables
	DECLARE this_row INT UNSIGNED;
	DECLARE end_of_list BOOLEAN DEFAULT 0;
    
	-- GET and OPEN the list
	DECLARE list_cursor CURSOR FOR SELECT id FROM ROWS_TABLE WHERE subscriber_user = subscriber_user_in AND event_ = old_event AND audience LIKE '%this_text%' ;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET end_of_list=1;
    OPEN list_cursor;
    
	-- LOOP through the list
	row_loop: LOOP
		FETCH list_cursor INTO this_row;
		IF end_of_list=1 THEN LEAVE row_loop; END IF;
		CALL COPY_ROW(this_row, new_event);
	END LOOP row_loop;
	CLOSE list_cursor;
	-- That's it! :-)
END 
// DELIMITER ;

-- ---------------------------------------------------
-- FUNCTION_CALL (PARAMETERS)
-- ---------------------------------------------------
DROP FUNCTION IF EXISTS FUNCTION_CALL;
DELIMITER //
CREATE FUNCTION FUNCTION_CALL ( PARAMETERS ) RETURNS TYPE
this_function: BEGIN
	-- CODE...
    -- RETURN VALUE;
END 
// DELIMITER ;