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 ;
-- test
CALL 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 error
CALL insert_invoice(34, 'ZXA-083', '2012-01-18', -14092.59,
NULL, NULL);
-- clean up
SELECT * 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 data
SELECT invoice_id, invoice_number
FROM invoices WHERE invoice_id = 115;
SELECT invoice_id, invoice_sequence, line_item_description
FROM invoice_line_items WHERE invoice_id = 115;
-- Clean up
DELETE FROM invoice_line_items WHERE invoice_id = 115;
DELETE FROM invoices WHERE invoice_id = 115;