MySQL stored procedures

1. Parameter Validation and error feedback
MySQL 5.5 begins with the introduction of the signal statement, which allows feedback on the data validation errors of the stored procedure. Versions prior to 5.5 can only be resolved by other means.
Syntax for the signal statement:
SIGNAL SQLSTATE [Value] SQLState_value

[SET Message_text = message[,mysql_errno = Mysql_error_number]];

Example:

Use Ap;drop PROCEDURE IF EXISTS insert_invoice;delimiter//create PROCEDURE insert_invoice (Vendor_id_param INT,         Invoice_number_param VARCHAR (), 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 is less than 1,000,000. ', Mysql_errno = 1264;  END IF; --Set default values for parameters IF Terms_id_param are NULL then SELECT default_terms_id to 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 to Terms_due_days_var from terms WHERE terms_id = t    Erms_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 (ZXA-080 ', ' 2012-01-18 ', 14092.59, 3, ' 2012-03-18 '); CAL L Insert_invoice (ZXA-082 ', ' 2012-01-18 ', 14092.59, NULL, NULL);--this statement raises an Errorca  LL Insert_invoice (ZXA-083, ' 2012-01-18 ', -14092.59, NULL, NULL);--Clean upselect * from invoices WHERE invoice_id >= 115;delete from invoicesWHERE invoice_id >= 115; 

2. Using Dynamic SQL
You can create dynamic SQL in a stored procedure by using statements such as Prepare,execute,deallocate.

Example:

Use Ap;drop PROCEDURE IF EXISTS select_invoices;delimiter//create PROCEDURE select_invoices (Min_invoice_date_param DA  TE, 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 isn't NULL then SET where_clause = CONCAT (Where_clause, "Invoice_date >", Min_i  Nvoice_date_param, "'");  END IF; If Min_invoice_total_param is isn't NULL then IF where_clause! = "where" then SET where_clause = CONCAT (Where_claus    E, "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. Using Cursors

Cursor Definitions:
DECLARE cursor_name cursor for select_statement;
Cursor Error Control:
DECLARE CONTINUE HANDLER for not FOUND handler_statement;
To open a cursor:
OPEN cursor_name;
Gets the value of the cursor row and saves it in a series of variables:
FETCH cursor_name into variable1[, variable2][, Variable3] ...;
To close a cursor :
CLOSE cursor_name;

Example:

 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_tot  Al-credit_total > 0;  DECLARE CONTINUE HANDLER for not FOUND SET row_not_found = TRUE;  OPEN Invoices_cursor;    While row_not_found = FALSE does FETCH invoices_cursor into Invoice_id_var, Invoice_total_var;      IF Invoice_total_var > 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. Transaction control

Define a SQL_error Identity, then capture the SqlException, and finally control the commit or rollback of the transaction based on the Sql_error identity.

See Example:

Use Ap;drop PROCEDURE IF EXISTS test;delimiter//create PROCEDURE Test () BEGIN DECLARESQL_error INT DEFAULT FALSE;  DECLARE CONTINUE HANDLER for SQLEXCEPTION SET sql_error = TRUE;    START TRANSACTION;  INSERT into Invoices VALUES (ZXA-080, ' 2011-06-30 ', 14092.59, 0, 0, 3, ' 2011-09-30 ', NULL);    INSERT into Invoice_line_items VALUES (1, 4447.23, ' HW upgrade ');    INSERT into Invoice_line_items VALUES (2, 167, 9645.36, ' OS upgrade ');  IF sql_error = FALSE then COMMIT;  SELECT ' The transaction was committed. ';    ELSE ROLLBACK;  SELECT ' The transaction is 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 =;

MySQL stored procedures