Stored Procedure Advantages & Disadvantages

Stored procedures are not just for large projects, but for small and medium-sized projects, it is also necessary to use stored procedures. Its power and advantages are mainly reflected in: 1. Stored procedures are compiled only at creation time, and each subsequent execution of the stored procedure does not need to be recompiled, and the general SQL statements are compiled once per execution, so using stored procedures can improve database execution speed. 2. When complex operations are performed on a database, such as when multiple tables are update,insert,query,delete, this complex operation can be encapsulated with stored procedures and used in conjunction with the transactional processing provided by the database. These operations, if done with a program, become a single SQL statement that may be connected to the database multiple times. Instead of storage, you only need to connect to the database once. 3. Stored procedures can be reused to reduce the workload of database developers.　4. High security, can be set only a certain user has the right to use the specified stored procedure. The disadvantage of stored procedures 1: Debugging trouble, but with PL/SQL Developer debugging is very convenient! 　　compensate for this shortcoming. 2: Porting issues, database-side code is of course related to the database. 　　However, if you are doing engineering projects, there is basically no transplant problem. 3: Recompile the problem because the backend code is compiled before it is run, and if an object with a reference relationship changes, the affected stored procedures, packages will need to be recompiled (but can also be set to run for automatic compilation). 4: If in a program system to use a large number of stored procedures, to the time of application delivery as the user needs increase will lead to changes in data structure, then the system related problems, and finally if users want to maintain the system can be said to be difficult, and the cost is unprecedented. More trouble with maintenance!

Stored Procedure Advantages & Disadvantages