"MySQL must know" reading notes-use of stored procedures

The previous knowledge and application of MySQL just stay in the stage of adding and removing changes, recently just learning MySQL related content, read a book called "MySQL Must know will", after watching the advanced usage of MySQL have a certain understanding. The following contents are only reading notes.

If there is reprint please indicate the source ~

Use of stored procedures to execute stored procedures

Call Productpricing ()

Execute a stored procedure named productpricing

Create a stored procedure

CREATE PROCEDURE productpricing () BEGIN SELECT avg  as Priceaverage  from Products ; END;

Note: If you use the MySQL command-line program, the ";" is a delimiter, and there is also ";" in the stored procedure. This causes syntax errors with SQL in the stored procedure, and the workaround is to temporarily change the delimiter of the statement:

// .... DELIMITER;

where, except \, any character can be used as a statement delimiter

To delete a stored procedure

DROP PROCEDURE productpring;

Just give the name of the stored procedure, not with ()

Use of stored procedure parameters

--create a stored procedureCREATE PROCEDUREOrderTotal (inchOnumberINT, out OtotalDECIMAL(8,2))BEGINSELECT sum(Item_price*quantity) fromOrderItemsWHEREOrder_num=Onumber intoototal;END;--calledCall OrderTotal (234567,@total)--Show ResultsSELECT @total

Intelligent stored Procedures

The previous stored procedure is just for understanding and learning, and the stored procedures in the actual application are not as simple as above. The following is a more complex stored procedure:

--To create a stored procedure named OrderTotal--Parameters: onumber-Order number taxable-whether to be taxed (0, No 1 required) ototal-The total returnedCREATEprocecure OrderTotal (inchOnumberINT,inchTaxable Boolean,out OtotalDECIMAL(8,2)) COMMENT'obtain order total, optionally adding'BEGIN--Define temporary variables total-store the totals that are queriedDECLARETotalDECIMAL(8,2)--temporary Variable taxrate-tax pointDECLARETaxRateINT DEFAULT 6;--get totals for queriesSELECT Sum(Item_price*quantity) fromOrderItemsWHEREOrder_num=Onumber intoTotal ;--determine whether you need to be taxedIFTaxable Then--Add the tax portion to the totalSELECTTotal+(Total/ -*TaxRate) intoTotal ;END IF;--return the final totalSELECTTotal intoototal;END;

"MySQL must know" reading notes-use of stored procedures