Source: http://www.cnblogs.com/zhuyp1015/p/3575823.html will use several tables mysql> DESC products;+------------+--------------+- -----+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| prod_id | Int (11) | NO | PRI | NULL | auto_increment |
| vend_id | Int (11) | YES | | NULL | |
| Prod_name | varchar (100) | YES | | NULL | |
| Prod_price | Int (11) | YES | | NULL | |
| Prod_desc | varchar (300) | YES | | NULL | |
+------------+--------------+------+-----+---------+----------------+ mysql> DESC orders;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| Order_num | Int (11) | NO | PRI | NULL | auto_increment |
| order_date | Date | YES | | NULL | |
| cust_id | varchar (20) | YES | | NULL | |
+------------+-------------+------+-----+---------+----------------+ mysql> DESC orderitems;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| Order_num | Int (11) | NO | PRI | NULL | auto_increment |
| Order_item | varchar (20) | YES | | NULL | |
| prod_id | varchar (20) | YES | | NULL | |
| Quantity | Int (11) | YES | | NULL | |
| Item_price | Int (11) | YES | | NULL | |
+------------+-------------+------+-----+---------+----------------+ Create stored procedure: Parameters need to be specified out/in/inout
CREATE PROCEDUREProductpricing (
outPL DECIMAL (8,2),
outph DECIMAL (8,2),
outPA DECIMAL (8,2))
BEGIN
SELECTMin (Prod_price)
intoPl
fromProducts
;SELECT Max (prod_price) into the ph from the products; SELECT AVG (Prod_price) into PA from products;
END; Calling stored procedure: Call Productpricing (@pricelow, @pricehigh, @priceaverage); Select the value returned: select @pricelow; Select @pricelow, @pricehigh, choose multiple Delete stored procedures @priceaverage --: Drop PROCEDURE productpricing; ----------- -------------------------------------- create PROCEDURE ordertotal ( int Onumber int, out ototal DECIMAL (8,2)) begin select sum (item_price * quantity) from& nbsp;orderitems where order_num = onumber into ototal; end; Calls: Call OrderTotal (20005, @total); select @total; Stored Procedure actual scenario: you need to get the same order totals as before, but you need to add sales tax to the totals, But only for some customers, then need to do: 1. Get Total 2. Add the sales tax field to the total 3. Return total (with or without tax) create PROCEDURE ordertotal (in Onumber int,in taxable boolean,out octoal DECIMAL (8,2)) begin --Comments Declare variable for total declare Total DECIMAL (8,2); declare ta Xrate INT DEFAULT 6; --Get the order total select Sum (item_price * quantity) FROM orderitems where Order_ num = onumber into total; --is this taxable?
IFTaxable
ThenSELECT Total + (tatal/100 *taxrate) to total;
END IF;SELECT total into ototal; END; Call OrderTotal (2005, 0, @total); SELECT @total; Check stored procedure: SHOW CREATE PROCEDURE ordertoal; ----------------------------------------------------------------------------------------------------SELECT Returns a result set that may contain more than one row of data, and sometimes it is necessary to forward or rewind a row or rows in the retrieved row. This is the reason for using cursors. A cursor is a database query stored on a MySQL server that is not a SELECT statement but a result set that is retrieved by the statement. After the cursor is stored, the application can scroll or browse the data as needed. Cursors are primarily used in interactive applications where users need to scroll through the data on the screen and browse or make changes to the data. MySQL cursors can only be used for stored procedures. Steps to use cursors: 1. Defines a cursor (for a SELECT statement) 2. Open Cursor 3. For cursors filled with data, remove each row 4 as needed. Simple example of closing a cursor: CREATE PROCEDURE processorders () BEGIN
DECLAREOrdernumbers
CURSORFor SELECT order_num from orders; OPEN ordernumbers; CLOSE ordernumbers; END;----------------using cursor data CREATE PROCEDURE processorders () BEGIN DECLARE o INT; DECLARE ordernumbers CURSOR for SELECT order_num from orders; OPEN ordernumbers;
FETCHOrdernumbers into O; CLOSE ordernumbers; END;----------------Loop Retrieve data CREATE PROCEDURE processorders () BEGIN DECLARE o INT; DECLARE done BOOLEAN DEFAULT 0; DECLARE ordernumbers CURSOR for SELECT order_num from orders; --Declare Continue handler Declare continue handler for SQLSTATE ' 02000 ' SET done = 1; --SQLSTATE ' 02000 ' is a condition that is not found, when no more rows are readable, set done = 1 and then exit OPEN ordernumbers;
REPEAT
FETCHOrdernumbers into O;
UNTILDone
END REPEAT; CLOSE ordernumbers; END;----------------------------------------------------------------------------------------------------------- -----Use table to record the value of the cursor FETCH create PROCEDURE processorders () BEGIN DECLARE o INT; DECLARE done BOOLEAN DEFAULT 0; DECLARE T DECIMAL (8,2); DECLARE ordernumbers CURSOR for SELECT order_num from orders; --Declare Continue handler Declare continue handler for SQLSTATE ' 02000 ' SET done = 1; --SQLSTATE ' 02000 ' is a condition that is not found, set done = 1 when no more rows are readable, and then exit--Creates a table create table IF not EXISTS ordertotals ( Order_num INT, Total Deciaml (8,2)); OPEN ordernumbers;
REPEAT
FETCHOrdernumbers into O; Call OrderTotal (o,1,t); --Call procedure-Insert Table INSERT into OrderTotals (Order_num, total) VALUES (o,t);
UNTILDone
END REPEAT; CLOSE ordernumbers; END;---------------------------------------------------------------------------------------------------------- Triggers: When a trigger is automatically executed when an event occurs, you need to give 4 messages: 1. Unique trigger Name 2. Trigger-associated table 3. The trigger should respond to the activity (Delete/insert/ UPDATE) 4. When the trigger executes--------------------CREATE TRIGGER newproduct after INSERT on the products for each ROW SELECT ' Product added ' ; --The example trigger displays the PRODUCT added message after each insert---Delete trigger drop TRIGGER newproduct; ----------------------------------------------------------------------------------------------------Transaction Processing ( Transaction processing) can be used to maintain database integrity, which ensures that a batch of MySQL operations is either fully executed or not executed. Several terms: Transaction: Transaction refers to a set of SQL statement fallback: Rollback refers to revoking the specified SQL statement procedure submission: Commit refers to the database table retention point that will be written to the stored SQL statement results: SavePoint refers to the temporary placeholder set in the transaction. You can post it back to it-------------SELECT * from OrderTotals;
START TRANSACTION;D elete from OrderTotals; --Delete Table select * from OrderTotals; --Confirm deletion
ROLLBACK;-- rollbackSELECT * from OrderTotal; --Once again, the--------------commit MySQL statement is done directly against the database table for implicit commits, that is, the commit operation is performed automatically. In a transaction, a commit is not implicitly executed, and a commit statement is required. START transaction;delete from orderitems where order_num = 20010;delete from orders where order_num = 20010; COMMIT;
MySQL Stored procedures/cursors/triggers/transactions
