The SP treats input parameters as literal values and not executable code. This makes it harder for attackers to try and trick your queries into performing unintended actions. Also, in addition the DBMS caches them, so once executed, the queries, are ready for repeated execution. Some of the disadvantages I think are worth mentioning include:.
They are written in vendor specific languages and that makes it hard to transfer them from one installation, such as Oracle, to another like SQL Server. Testing and debugging stored procedures can be tricky. It can be more difficult to put together the debugging tools to allow you to step through and trace them.
It is harder to keep track of changes made to stored procedures than it is native source code. I feel that stored procedures have their place. I think that using stored procedures to insert and change data is a really good idea. I had put together some extensive ad-hoc report writers and needed a way to generate SQL code on the client and present it to the server.
For these ad-hoc cases I shy away from stored procedures. I want to remind you all that if you have other questions, you want answered, then post a comment. Kris Wenzel has been working with databases over the past 30 years as a developer, analyst, and DBA.
Kris has written hundreds of blog articles and many online courses. He loves helping others learn SQL. Nicely written and balanced post! Thanks Kris! May I add a express of thoughts? Reusability of encapsulated logic is one big advantage. I also feel, SPs are a independently assignable so, specific skills in team can be built and exploited , b independently testable thus, promoting parallelism in development and above all, pure fun to learn and write.
A lot, however, depends on how SPs are designed. A good SP does only one simple task and returns one error message. It may return any number of result sets, though. Overloading functionality spoils reusability and maintainability.
The reason, why I am harping on this is SPs have advantages like any other tool , only when designed well. Hi Venkataraman, Thanks for posting! I totally agree with your points. Hi, I have a stored proc which has two cursor inside it ,one is for fetching company details and other is for fetching the users for that company and my requirement is as follows.
When i execute that stored proc I need to process the users in a Batch wise manner say per batch. For Example if we have users for a company,if we execute the stored proc for the first time,TOP users should get executed which we can achieve using TOP in select statement inside user cursor,.
But for the second time if i execute the stored proc then we should be able to execute the next set i. Same way if i run the stored proc for the third time then it should process from to and so on..
Please let me know if you have any feasible solution. Can you pass in a parameter that is the batchrunnumber? Then you can use that to calculate record ranges. This lowers memory requirements and invocation overhead.
By grouping SQL statements, a stored procedure allows them to be executed with a single call. This minimizes the use of slow networks, reduces network traffic, and improves round-trip response time.
OLTP applications, in particular, benefit because result set processing eliminates network bottlenecks. Additionally, stored procedures enable you to take advantage of the computing resources of the server. For example, you can move computation-bound procedures from client to server, where they will execute faster.
Likewise, stored functions called from SQL statements enhance performance by executing application logic within the server. By designing applications around a common set of stored procedures, you can avoid redundant coding and increase your productivity.
You can use the Java integrated development environment IDE of your choice to create stored procedures. Then, you can deploy them on any tier of the network architecture. This broad access to stored procedures lets you share business logic across applications. For example, a stored procedure that implements a business rule can be called from various client-side applications, all of which can share that business rule.
Therefore, repeated compilation is not required during execution of the procedure, thereby increasing the speed of execution. However, if any updates are made in the procedure then it will be re-compiled. One very useful accepts of using a stored procedures is the security cover it provides to its data source. Since there are no direct accesses to the tables , it prevents unauthorized manipulation of records inside the tables.
A properly written procedure acts as a solid deterrence to SQL injections. SQL injections are a combination of SQL statements and techniques that can break into databases and do unauthorized manipulation to the data.
Set based processing usually refers to processing bulk data at once and it is much quicker. This is another advantage of using a stored procedure since it processes a bulk of data. Distributed applications across the network, rely heavily on these procedures as it reduces round trips.
Debugging is never easy, so it is not advisable to write and execute complex business logic using stored procedures. Therefore, if not handle properly it can lead to a mess and can give nightmares to DBAs. In a large enterprise, seasoned DBAs and database developers manage huge data pools. Application developers, on the other hand have to rely on them, as for any minor change they have refer it to a DBA, who will fix the bugs in existing procedures or create new procedures.
Don't get wrong on this. DBAs never come easy, never come cheap. Companies will have to incur an extra cost on hiring experts DBAs, who we believe are much more qualified in handling complex database procedures. Stored procedures often written in one platform may not run on another platform. Procedures written in Oracle are more sophisticated and you might have to write the entire procedure again for SQL Server.
We will create a simple procedure that does not take any parameter. Upon executing, the procedure will show a list of books.
0コメント