| 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. Just beware that I have the name of my database ( pizza_store) embedded in my stored procedure definition, so you'll need to change that line to refer to your 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. Insert into crust_sizes (crust_size,description) values (16, '16 INCH') Insert into crust_sizes (crust_size,description) values (14, '14 INCH') Insert into crust_sizes (crust_size,description) values (12, '12 INCH') In case you want to try this example on your own system I'm including the definition for my MySQL crust_sizes database table here, as well as some INSERT statements that populate the table. MySQL schema for my sample database table Now, I can run my MySQL stored procedure by calling it with the call command, like this: I'm not passing any parameters into this stored procedure - at this point there's no need to make this any harder than it needs to be. As you can see, the query that it runs is very simple: That definition basically says that I want to create a stored procedure named GetCrustSizes in a database I have named pizza_store. Here's how I define that stored proc:ĭROP PROCEDURE IF EXISTS `pizza_store`.`GetCrustSizes` $$ Next, I want to create a simple MySQL stored procedure that returns all the data in that table. | description | varchar(20) | NO | UNI | NULL | | | crust_size | int(11) | NO | UNI | NULL | | | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | Field | Type | Null | Key | Default | Extra | My sample MySQL database tableįirst, I start by already having a table in my MySQL database named crust_sizes that exists and is populated with some sample data. ![]() It's not quite a "Hello, world" stored proc, but it's close. In this blog post I'll show you how to create a very simple MySQL stored procedure. I'm not normally a big stored procedure user or developer, but since stored procedures are supported in MySQL 5.0 and beyond I thought I'd give them a spin.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |