tinoymalayil 0 Report post Posted July 27, 2009 Hi,What is stored procedure..Most of the companies using stored procedure to access database.please tell what are the benefits of using stored procedure instead of using tables directly..please reply how to study query language to create stored procedure for select,insert,delete,update operations in database... Share this post Link to post Share on other sites
k_nitin_r 8 Report post Posted July 27, 2009 Hi!@tinoymalayilA stored procedure is simply a sequence of SQL statements that are put together in a package located on the database server. The benefit of using stored procedures are: (i) modifying the behavior requires a simple re-creation (drop/create or replace) of the stored procedure with no change to the application therefore eliminating the need for compilation (ii) you can handle some operations at the database level without having to send the data all the way back to the application server reducing network traffic and improving system response time (iii) you can secure your database better by assigning privileges on the stored procedure instead of on the tables and views that are queried by the stored procedure (iv) you can create pre-defined reports as stored procedures if they are too complex to be created as views (v) they can be used as a standardized interface to access the database (vi) you can use them as functions on ORM entity classes instead of having to code in the functionality against the ORM entity classes, sometimes leading to better-optimized codeDepending on which database you are using the stored procedure language would vary. Microsoft's SQL Server uses T-SQL. As Sybase developed the engine for the earlier versions of Microsoft SQL Server, it too uses T-SQL although you would find some variations that both Sybase and Microsoft have added over the years. Oracle uses PL/SQL though you can also write stored procedures in Java.A recent (over roughly the past half decade) development that many database vendors (Microsoft SQL Server, Oracle, IBM DB/2) have been featuring is the ability to create stored procedures using .NET, just as Oracle has been supporting the creation of stored procedures with Java.There's quite a bit that you can do with stored procedures. For example, if you wanted to send an email whenever a database row is updated, you can create a trigger (a special form of stored procedures) to call a database mail function. You can also track changes to database rows by storing a copy of the old and new values.Regards,Nitin Reddy Share this post Link to post Share on other sites
