chazzy
04-12-2006, 10:51 PM
Why Stored Procedures help make your code easier to read and more likely to be reused
A Lesson on Code Reduction
So in some ways, I am doing this similar to how database journal did theirs, except I want to gear it more for the novice, the beginner in database design to help them understand why their code will be easier to manage using a stored procedure.
In this document, I will refer to a “subscriber” object. They will have one row containing their “core” data – who they are, where, and basic information. They could also have any number of entries in a secondary table; this might store a list of orders or something along those lines. They have an identifier that will be used, their “name”, which is unique to them.
So you’re probably saying to yourselves “but, I already know what you’re going to say. I should retrieve the data that I want and only want by using something like this:”
SELECT column_list FROM subscriber_data WHERE subscriber_name = @the_name;
Well, you’re right and you’re wrong. What about the data contained in the secondary table? That’s also a part of the object, now it looks like we need to issue another query to get all of that data.
SELECT column_list FROM subscriber_secondary WHERE subscriber_name = @the_name;
So now we need to take all of that and construct some sort of subscriber object. Now, my argument is that there is an easier way to do all this, a simple stored procedure. Let me give everyone a little bit more information on the structure of these two tables, just for this example:
subscriber_data:
subscriber_name
subscriber_signed_up_date
subscriber_address
subscriber_secondary:
subscriber_name
subscriber_order_number
So that means that our select statements now look like this:
SELECT @the_name,subscriber_signed_up_date,subscriber_address FROM subscriber_data WHERE subscriber_name = @the_name;
SELECT subscriber_order_number FROM subscriber_secondary WHERE subscriber_name = @the_name;
Our subscriber object is going to look like this:
String subscriber_name
Date subscriber_signed_up_date
String subscriber_address
String[] order_numbers
We are assuming that there can be any number of order_numbers 0 to n, and exactly 1 row for the subscriber.
Now note, when you create the procedure, the variables used inside must resemble the column types used in the SQL. Keep an eye out for possible variable name reuse, as this can cause issues with calling the results. Typically, procedure variables will start with a “p_” or “v_”, but this is by convention only.
CREATE PROCEDURE getSubscriber(IN name varchar)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE order_number varchar(25);
DECLARE order_number_list TEXT;
DECLARE order_numbers CURSOR FOR SELECT subscriber_order_number FROM subscriber_secondary WHERE subscriber_name = name;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN open_numbers;
REPEAT
FETCH order_numbers INTO order_number;
IF NOT done THEN
IF order_number_list IS NULL THEN
SET order_number_list = order_number;
ELSE
SET order_number_list = CONCAT(order_number_list,'|',order_number);
END IF;
END IF;
UNTIL done END REPEAT;
CLOSE order_numbers;
SELECT name,subscriber_signed_up_date,subscriber_address,order_number_list FROM subscriber_data WHERE subscriber_name = name;
END
So that when called, this will return the results of a query, but will already have the . Now, you would simply have a command that ran:
CALL getSubscriber(‘subscriber_name’);
Then you would just parse the result and use that to create your new object. No need to issue multiple queries, typically faster in operation, and more concise.
A Lesson on Code Reduction
So in some ways, I am doing this similar to how database journal did theirs, except I want to gear it more for the novice, the beginner in database design to help them understand why their code will be easier to manage using a stored procedure.
In this document, I will refer to a “subscriber” object. They will have one row containing their “core” data – who they are, where, and basic information. They could also have any number of entries in a secondary table; this might store a list of orders or something along those lines. They have an identifier that will be used, their “name”, which is unique to them.
So you’re probably saying to yourselves “but, I already know what you’re going to say. I should retrieve the data that I want and only want by using something like this:”
SELECT column_list FROM subscriber_data WHERE subscriber_name = @the_name;
Well, you’re right and you’re wrong. What about the data contained in the secondary table? That’s also a part of the object, now it looks like we need to issue another query to get all of that data.
SELECT column_list FROM subscriber_secondary WHERE subscriber_name = @the_name;
So now we need to take all of that and construct some sort of subscriber object. Now, my argument is that there is an easier way to do all this, a simple stored procedure. Let me give everyone a little bit more information on the structure of these two tables, just for this example:
subscriber_data:
subscriber_name
subscriber_signed_up_date
subscriber_address
subscriber_secondary:
subscriber_name
subscriber_order_number
So that means that our select statements now look like this:
SELECT @the_name,subscriber_signed_up_date,subscriber_address FROM subscriber_data WHERE subscriber_name = @the_name;
SELECT subscriber_order_number FROM subscriber_secondary WHERE subscriber_name = @the_name;
Our subscriber object is going to look like this:
String subscriber_name
Date subscriber_signed_up_date
String subscriber_address
String[] order_numbers
We are assuming that there can be any number of order_numbers 0 to n, and exactly 1 row for the subscriber.
Now note, when you create the procedure, the variables used inside must resemble the column types used in the SQL. Keep an eye out for possible variable name reuse, as this can cause issues with calling the results. Typically, procedure variables will start with a “p_” or “v_”, but this is by convention only.
CREATE PROCEDURE getSubscriber(IN name varchar)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE order_number varchar(25);
DECLARE order_number_list TEXT;
DECLARE order_numbers CURSOR FOR SELECT subscriber_order_number FROM subscriber_secondary WHERE subscriber_name = name;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN open_numbers;
REPEAT
FETCH order_numbers INTO order_number;
IF NOT done THEN
IF order_number_list IS NULL THEN
SET order_number_list = order_number;
ELSE
SET order_number_list = CONCAT(order_number_list,'|',order_number);
END IF;
END IF;
UNTIL done END REPEAT;
CLOSE order_numbers;
SELECT name,subscriber_signed_up_date,subscriber_address,order_number_list FROM subscriber_data WHERE subscriber_name = name;
END
So that when called, this will return the results of a query, but will already have the . Now, you would simply have a command that ran:
CALL getSubscriber(‘subscriber_name’);
Then you would just parse the result and use that to create your new object. No need to issue multiple queries, typically faster in operation, and more concise.