Why we are using store procedure?

Store procedures are just a set of queries which can be used to run in one click. They are mainly used to carry out several operation under one command eg., If This Then That. This may raise another question in you, Then what is Trigger. Triggers are designed to do the task at a particular action like Insert,Update or delete, we cannot use a insert trigger for update action and vice versa. Where as a Store procedures are not designed in such a way, we can use them at any place we want and can use any kind of query we lie to use and can execute them at any point of time depending on our requirement. read less

Stored Procedures provide various advantages over inline sql statements 1. SQL Injections :- Secure as compare to Inline SQL Query. 2. Increase Database Performance :- When a Stored procedure is executed its execution plan is stored in memory and next time stored procedure is called the execution plan from memory without recalculating every time 3. Reuse :- One stored procedure is used in many places 4. Enable Security Model :- Can apply additional security setting in the Stored Procedure. read less

There are so many advantages using store procedure over using direct sql in your project. Performance Stored procedures are compiled once and stored in executable form, so procedure calls are quick and efficient. Executable code is automatically cached and shared among users. 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. Productivity and Ease of Use By designing applications around a common set of stored procedures, you can avoid redundant coding and increase your productivity. Moreover, stored procedures let you extend the functionality of the RDBMS. For example, stored functions called from SQL statements enhance the power of SQL. 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. Moreover, they can be called by standard Java interfaces such as JDBC, CORBA, and EJB and by programmatic interfaces and development tools such as SQLJ, the OCI, Pro*C/C++, and JDeveloper. 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. In addition, you can leverage the server's Java facilities while continuing to write applications for your favorite programmatic interface. Scalability Stored procedures increase scalability by isolating application processing on the server. In addition, automatic dependency tracking for stored procedures aids the development of scalable applications. The shared memory facilities of the Multi-Threaded Server (MTS) enable Oracle8i to support more than 10,000 concurrent users on a single node. For more scalability, you can use the Net8 Connection Manager to multiplex Net8 connections. Maintainability Once it is validated, a stored procedure can be used with confidence in any number of applications. If its definition changes, only the procedure is affected, not the applications that call it. This simplifies maintenance and enhancement. Also, maintaining a procedure on the server is easier than maintaining copies on various client machines. Interoperability Within the RDBMS, Java conforms fully to the Java Language Specification and furnishes all the advantages of a general-purpose, object-oriented programming language. Also, like PL/SQL, Java provides full access to Oracle data, so any procedure written in PL/SQL can be written in Java. PL/SQL stored procedures complement Java stored procedures. Typically, SQL programmers who want procedural extensions favor PL/SQL, and Java programmers who want easy access to Oracle data favor Java. The RDBMS allows a high degree of interoperability between Java and PL/SQL. Java applications can call PL/SQL stored procedures using an embedded JDBC driver. Conversely, PL/SQL applications can call Java stored procedures directly. Security You can restrict access to Oracle data by allowing users to manipulate the data only through stored procedures that execute with their definer's privileges. For example, you can allow access to a procedure that updates a database table, but deny access to the table itself. Replication With Oracle Advanced Replication, stored procedures can be replicated (copied) from one Oracle8i database to another. This feature makes them ideal for implementing a central set of business rules. Once written, the stored procedures are replicated and distributed to work groups and branch offices throughout the company. In this way, policies can be revised on a central server rather than on individual servers. read less

Stored procedures manage, control and validate data. Large queries can be avoided. Reduces network traffic since they need not be recompiled. Even though the stored procedure itself may be a complex piece of code, we need not write it over and over again. Hence stored procedures increases reusability of code Permissions can be granted for stored procedures. Hence, increases security read less