Stored Procedure for SQL
What is Stored Procedure
Why we need Stored Procedure
Stored Procedure Function:
Structured of PostgreSQL function:
Here, we explain how the structure looks in the stored procedure function. there are two arguments passed in function with its data type, using these two arguments we can write some logical code in the block between BEGIN and END.
After creating the above function, To execute the above function we just need to call below the line
We also call below the line get function
For more understanding, I wrote one simple example below.
Here in the above function we just wrote the code for the sum of two numbers and multiplication of two numbers.
We passed four integer variables a, b, sum, and product in the sum_and_product function. Using a and b variables we add and multiply those numbers and store their value to corresponding variables sum and product.
We can also replace the same function using the below code
This code creates function
sum_and_product or replaces the function if it is already in the system.
When we need to display the message to the user then we used WARNING, NOTICE, and INFO.
WARNING: It is used when we want to display a warning message to the user.
INFO: It is used when we want to display an information message to the user.
NOTICE: It is used when we want to display the error message to the user.
Difference between Stored procedure and SQL queries:
Stored procedure without function
We don’t create functions in this kind of stored procedure. Generally when we want to manipulate some data in the system this kind of stored procedure is useful.
In this example, we are updating the tables1 records using table2. If we use normal queries and code then it will take more time compared to this query.
Exception Handling With Stored procedures:
Create test function:
This schema is created for your connection and is where temporary tables are stored. When your connection expires and is closed this schema is dropped. Turns out that if you create a function on this schema, the schema will be created automatically.
- Faster to execute the complex database operation
- It reduces network traffic.
- It is reusable
- Debugging is difficult.
- Code is difficult because it contains complex and large queries.
- Dependent on the database.