1.参数验证和错误反馈
mysql 5.5开始引入SIGNAL语句,通过它可以反馈存储过程的数据验证错误.在5.5之前的版本,只能通过其它方法变通解决.
SIGNAL语句的语法:
SIGNAL SQLSTATE [value] sqlstate_value

[SET MESSAGE_TEXT = message[,mysql_ERRNO = mysql_error_number]];

例子:

USE ap;DROP PROCEDURE IF EXISTS insert_invoice;DELIMITER //CREATE PROCEDURE insert_invoice(  vendor_id_param        INT,  invoice_number_param   VARCHAR(50),  invoice_date_param     DATE,  invoice_total_param    DECIMAL(9,2),  terms_id_param         INT,  invoice_due_date_param DATE)BEGIN  DECLARE terms_id_var           INT;  DECLARE invoice_due_date_var   DATE;    DECLARE terms_due_days_var     INT;  -- Validate paramater values  IF invoice_total_param < 0 THEN     SIGNAL SQLSTATE ‘22003‘       SET MESSAGE_TEXT = ‘The invoice_total column must be a positive number.‘,        MYSQL_ERRNO = 1264;   ELSEIF invoice_total_param >= 1000000 THEN    SIGNAL SQLSTATE ‘22003‘      SET MESSAGE_TEXT = ‘The invoice_total column must be less than 1,000,000.‘,       MYSQL_ERRNO = 1264;  END IF;  -- Set default values for parameters  IF terms_id_param IS NULL THEN    SELECT default_terms_id INTO terms_id_var    FROM vendors WHERE vendor_id = vendor_id_param;  ELSE    SET terms_id_var = terms_id_param;  END IF;  IF invoice_due_date_param IS NULL THEN    SELECT terms_due_days INTO terms_due_days_var      FROM terms WHERE terms_id = terms_id_var;    SELECT DATE_ADD(invoice_date_param, INTERVAL terms_due_days_var DAY)       INTO invoice_due_date_var;  ELSE    SET invoice_due_date_var = invoice_due_date_param;  END IF;  INSERT INTO invoices         (vendor_id, invoice_number, invoice_date,           invoice_total, terms_id, invoice_due_date)  VALUES (vendor_id_param, invoice_number_param, invoice_date_param,           invoice_total_param, terms_id_var, invoice_due_date_var);END//DELIMITER ;-- testCALL insert_invoice(34, ‘ZXA-080‘, ‘2012-01-18‘, 14092.59,                     3, ‘2012-03-18‘);CALL insert_invoice(34, ‘ZXA-082‘, ‘2012-01-18‘, 14092.59,                    NULL, NULL);-- this statement raises an errorCALL insert_invoice(34, ‘ZXA-083‘, ‘2012-01-18‘, -14092.59,                    NULL, NULL);-- clean upSELECT * FROM invoices WHERE invoice_id >= 115;DELETE FROM invoices WHERE invoice_id >= 115;

2.使用动态SQL
通过PREPARE,EXECUTE,DEALLOCATE等语句可以在存储过程中创建动态SQL.

例子:

USE ap;DROP PROCEDURE IF EXISTS select_invoices;DELIMITER //CREATE PROCEDURE select_invoices(  min_invoice_date_param   DATE,  min_invoice_total_param  DECIMAL(9,2))BEGIN  DECLARE select_clause VARCHAR(200);  DECLARE where_clause  VARCHAR(200);  SET select_clause = "SELECT invoice_id, invoice_number,                        invoice_date, invoice_total                        FROM invoices ";        SET where_clause =  "WHERE ";  IF min_invoice_date_param IS NOT NULL THEN    SET where_clause = CONCAT(where_clause,        " invoice_date > ‘", min_invoice_date_param, "‘");  END IF;  IF min_invoice_total_param IS NOT NULL THEN    IF where_clause != "WHERE " THEN      SET where_clause = CONCAT(where_clause, "AND ");    END IF;    SET where_clause = CONCAT(where_clause,        "invoice_total > ", min_invoice_total_param);  END IF;  IF where_clause = "WHERE " THEN    SET @dynamic_sql = select_clause;  ELSE    SET @dynamic_sql = CONCAT(select_clause, where_clause);      END IF;  PREPARE select_invoices_statement  FROM @dynamic_sql;  EXECUTE select_invoices_statement;  DEALLOCATE PREPARE select_invoices_statement;  END//DELIMITER ;CALL select_invoices(‘2011-07-25‘, 100);CALL select_invoices(‘2011-07-25‘, NULL);CALL select_invoices(NULL, 1000);CALL select_invoices(NULL, NULL);

3.使用游标

游标定义:
DECLARE cursor_name CURSOR FOR select_statement;
游标错误控制:
DECLARE CONTINUE HANDLER FOR NOT FOUND handler_statement;
打开游标:
OPEN cursor_name;
获取游标行的值并保存到一系列变量中:
FETCH cursor_name INTO variable1[, variable2][, variable3]…;
关闭游标:
CLOSE cursor_name;

例子:

USE ap;DROP PROCEDURE IF EXISTS test;DELIMITER //CREATE PROCEDURE test()BEGIN  DECLARE invoice_id_var    INT;  DECLARE invoice_total_var DECIMAL(9,2);    DECLARE row_not_found     TINYINT DEFAULT FALSE;  DECLARE update_count      INT DEFAULT 0;  DECLARE invoices_cursor CURSOR FOR    SELECT invoice_id, invoice_total  FROM invoices    WHERE invoice_total - payment_total - credit_total > 0;  DECLARE CONTINUE HANDLER FOR NOT FOUND    SET row_not_found = TRUE;  OPEN invoices_cursor;  WHILE row_not_found = FALSE DO    FETCH invoices_cursor INTO invoice_id_var, invoice_total_var;    IF invoice_total_var > 1000 THEN      UPDATE invoices      SET credit_total = credit_total + (invoice_total * .1)      WHERE invoice_id = invoice_id_var;      SET update_count = update_count + 1;    END IF;  END WHILE;  CLOSE invoices_cursor;  SELECT CONCAT(update_count, ‘ row(s) updated.‘);END//DELIMITER ;CALL test();

4.事务控制

先定义一个sql_error标识,然后对SQLEXCEPTION进行捕捉,最后根据sql_error标识来控制事务的commit或rollback.

看例子:

USE ap;DROP PROCEDURE IF EXISTS test;DELIMITER //CREATE PROCEDURE test()BEGIN  DECLARE sql_error INT DEFAULT FALSE;    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION    SET sql_error = TRUE;  START TRANSACTION;    INSERT INTO invoices  VALUES (115, 34, ‘ZXA-080‘, ‘2011-06-30‘,           14092.59, 0, 0, 3, ‘2011-09-30‘, NULL);  INSERT INTO invoice_line_items   VALUES (115, 1, 160, 4447.23, ‘HW upgrade‘);    INSERT INTO invoice_line_items   VALUES (115, 2, 167, 9645.36, ‘OS upgrade‘);    IF sql_error = FALSE THEN    COMMIT;    SELECT ‘The transaction was committed.‘;  ELSE    ROLLBACK;    SELECT ‘The transaction was rolled back.‘;  END IF;END//DELIMITER ;CALL test();-- Check dataSELECT invoice_id, invoice_numberFROM invoices WHERE invoice_id = 115;SELECT invoice_id, invoice_sequence, line_item_descriptionFROM invoice_line_items WHERE invoice_id = 115;-- Clean upDELETE FROM invoice_line_items WHERE invoice_id = 115;DELETE FROM invoices WHERE invoice_id = 115;

MySQL存储过程