четвъртък, юли 21, 2011

MySQL stored procedure examples

MySQL stored procedure, examle 1:
- check for valid input parameter
- declare handler for db error (1062 => duplicate key)
- insert data
- return status

DROP PROCEDURE IF EXISTS test_proc;

DELIMITER $$
CREATE PROCEDURE test_proc (
        IN p_test_id INT,
        IN p_test_string VARCHAR(255)
    )
BEGIN
  main: BEGIN

    # declare handler when duplicate key db error is thrown
    DECLARE EXIT HANDLER FOR 1062 BEGIN
        SELECT 1 AS status_code, 'Duplicate entry for input key values' AS status_message;
    END;

    # check if input parameter is NULL
    IF p_test_id IS NULL OR p_test_id < 1 THEN
        SELECT 2 AS status_code, 'Missing or invalid p_test_id parameter' AS status_message;
        LEAVE main;
    END IF;

    INSERT INTO test_table
    SET test_id = p_test_id,
        test_string = p_test_string;    

    SELECT 0 AS status_code, 'OK' AS status_message;
  END main;
END; 
$$
DELIMITER ;

# CALL test_proc(10, 'test string parameter');
MySQL stored procedure, example 2:
- optional p_search_criteria parameter; not used if NULL, but used to search with %LIKE% if not NULL
- pagination with prepared statement, because LIMIT cannot use input parameters directly
- return result from query directly
DROP PROCEDURE IF EXISTS test_proc_2;

DELIMITER $$
CREATE PROCEDURE test_proc_2 (
        IN p_search_criteria VARCHAR(100),
        IN p_page INT,
        IN p_page_size INT
    )
BEGIN
  main: BEGIN

    IF p_page IS NULL OR p_page < 1 THEN
        SET p_page = 0;
    ELSE
        SET p_page = p_page - 1;
    END IF;

    IF p_page_size IS NULL OR p_page_size < 1 THEN
        SET p_page_size = 20;
    END IF;

    PREPARE STMT FROM 'SELECT * FROM test_table 
                       WHERE ? IS NULL OR test_column_1 LIKE ? OR test_column_2 LIKE ? 
                       LIMIT ?, ?';
    SET @c1 = p_search_criteria;
    SET @c2 = CONCAT('%', p_search_criteria, '%');
    SET @start = p_page * p_page_size;
    SET @size = p_page_size;

    EXECUTE STMT USING @c1, @c2, @c2, @start, @size;
    DEALLOCATE PREPARE STMT;
  END main;
END;
$$
DELIMITER ;

# CALL test_proc_2('searched text', 1, 10);

Няма коментари: