How to Write Stored Procedures

Hello Friends! We again meet with another post. That is about database stored procedures. This is also can be someone’s hate chapter. As I told earlier this is also an easy chapter. Like triggers. If you learn the theories nicely then it would not be a problem anyway. How to write stored procedures are easier than triggers. If you already know triggers learning stored procedures is easy to you. Again I am telling if you are lack in query writing this chapter look little bit harder to you. Let’s learn query writing as well as then this will be easy. So let’s start the topic.

What is database stored procedures

This is the first step of learning how to write stored procedures. Learning what it is. Stored procedure is a SQL code that you can save. This is not something that automatically executed. We have to execute procedures manually. Stored procedures do a task different than triggers. By using database stored procedures we can give specific data to enter. If you remember in triggers we can’t insert data what we want. We can insert data to the table which is in trigger firing table. We can only insert those details already available in the trigger firing column. Even though by using stored procedures we can insert specific data. In simply we can define the parameters in stored procedures.

Inside a trigger we can include a database stored procedures. Yet we can’t do vise – versa. That means include a trigger inside a stored procedure. I will give you that syntax also. As we discussed in the trigger topic. In stored procedures also we have usages. Those usages are as follows,

• Improve performances
• Compile only once
• Provide security
• Return zero or more values
• Only change when business logic changes

Stored procedure syntax

This syntax is also similar like trigger syntax.

CREATE – Like trigger syntax this is the keyword to create the procedure.

PROCEDURE –  This is the keyword to identify the creation is a stored procedure.

Procedure_name – This is the name that we put the procedure we create. We can put any meaningful name as the stored procedure name.

Parameter name – The parameters we are inserting we are defining here. Therefore we have to put the parameter name. This needs to be also a meaningful name. This mostly star with @ sign. We write the parameter name by adding @ sign to the beginning of the parameter. Sometimes when writing stored procedures some ommit this @ sign also write just the parameter name.

Data type – This is the data type of the parameter we mentioned earlier. Likewise we discussed in the triggers. These data types are the data types that we mostly used in the database. Examples like VARCHAR, CHAR, NUMBER, INTEGER.

Mode – As mode we usually put IN keyword.

IS/AS – We can put either words to write procedures.

Variable declaration – Next is the variable declaration. Here we can declare any variables we need for the procedure execution.

BEGIN –  After begin keyword we can write the sql queries for the procedures. The things we wanted to do. It can be inserting table or updating table or anything.

END – With end command our procedure ends. This denote that procedure sql code is end from here.

Triggers with procedures syntax

In this case remember we are not writing procedure creation code inside the trigger. What we write is code for the executing stored procedure. When the trigger fires stored procedure will executed. Hence inside the trigger we write stored procedure execution code. Because it is executing manually. EXECUTE is the sql command to execute the stored procedure.

Let’s see example question for database stored procedures

Question 01

Write a stored procedure to delete an order from the order table for a given order id. Assume order_id is of the type VARCHAR(10). Assume table name is tbl_order

Answer 01

Here even though it didn’t says stored procedure you have to ring bells in your mind. Cause it says delete a record for given order_id so it cannot done by a trigger. It only can done by a stored procedure. Here is the answer. Writing stored procedures are easier than writing triggers. You don’t have to think much. Just adapt with the syntax. Put correct parameters to the syntax. Here varchar(10) means the length of the varchar is 10. You can enter 10 characters as the order_id

This is the answer for that question. We can take any name for parameter as I said. Let’s see another question. This time we will see a bigger one.

Question 02

There is a product table in a database. The product table is to maintain product details. Assume the table name is tbl_product. The prices of the product is manage from this table. Due to a tax increase there need to be a change for the price in the product table. The table has pro_price that is the buying price from seller. We have to add the tax to the product where buying price is more than 1000. Then selling price has to updated. The tax is 10%.

Answer 02

Since selling price attribute not given let’s take it as pro_sprice. Here we might need some variables. Because we have to calculate the new selling price for the product. For that we need a variable to store the new price. So we need to declare variable at first. Here’s a tip for writing stored procedures. This applies for the triggers also not only stored procedures. Before you directly write the answer in the answer sheet use a rough paper and build the sql code. Then you will realize you need different data. Then you can write the answer in the answer sheet. Don’t bother no one at once can’t say we need these variables for this triggers or procedures. Therefore don’t underestimate your skill. Use a rough paper and build it firstly. Then write it in the answer sheet.

Conclusion

Here we come to end of the post. Today we learnt about stored procedures. We saw an examples also. If you want more stored procedures to practice how to write stored procedures comment us and let us know. We can write another post about it. If you leant something from this post please comment us and tell us how it was. If you have doubts about other learning topics or about this topic also please tell us by commenting. Please share this among your friends also then they also can learn. Meet you with another new interesting post. Till then goodbye!