| 3 | Mega Widget | 99.95 | 140 | 40.05 |Īs you can see, the calcProfit function automatically calculates the profit (price minus the cost) for each product in the table. | prod_id | prod_name | prod_cost | prod_price | profit | This SQL statement returns the following results: +-+-+-+-+-+ The following SQL statement demonstrates how to do this: SELECT *, calcProfit(prod_cost,prod_price) AS profit FROM products You can now execute the stored function in a database query. The DELIMITER command at the end of these statements returns processing to normal. The DELIMITER command at the beginning of these statements prevents MySQL from processing the function definition too soon. To create this stored function, run the following MySQL statements: DELIMITER $$ĬREATE FUNCTION calcProfit(cost FLOAT, price FLOAT) RETURNS DECIMAL(9,2) It calculates the profit by subtracting the cost from the price, and then returns the value to the calling expression. This function takes two input parameters: the cost and the price of something. Let's create a function named calcProfit. Now that we have a database and a table to work with, we are ready to create a stored function. The following SQL statement adds some sample data to the products table: INSERT INTO products (prod_name, prod_cost, prod_price) VALUES ('Basic Widget',5.95,8.35),('Micro Widget',0.95,1.35),('Mega Widget',99.95,140.00) Creating the stored function To do this, run the following SQL statement: CREATE TABLE products (prod_id INT NOT NULL AUTO_INCREMENT, prod_name VARCHAR(20) NOT NULL, prod_cost FLOAT NOT NULL DEFAULT 0.0, prod_price FLOAT NOT NULL DEFAULT 0.0, PRIMARY KEY(prod_id)) Next, create a table in the username_test database named products. Otherwise, if you are using the mysql command-line program, type the following SQL statement: USE username_test If you are using phpMyAdmin, click the name username_test to select the database. You can run the previous SQL command (and the following SQL commands) from the command line using the MySQL tool, or in your web browser using phpMyAdmin.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |