Can anyone say Stored procedures pros and cons AND Java Stored Procedures pros and con?

Question

Hi! Can anyone say Stored procedures pros and cons AND Java Stored Procedures pros and con? And how to test it.

Best regards!

Answer 1

Stored procedures pro:

-Secure

-Performance and scalability

-Allows for changes to one tier (the database itself rather then the actual code of the interface / web page)

-Can be scripted out or moved over easily

Answer 2

The arguments for and against stored procedures tend to split on what you think is the correct answer to the question: does business logic belong in the database or the application? I will try to be neutral in my presentation of the arguments. If I succeed some of my pros and cons will contradict themselves.

PRO

1. Stored procedures make it easy to share database code across applications
2. Co-locating data-related logic with the data makes it easy to enforce business rules across applications. This approach privileges the data owner over the data user.
3. Stored procedures use a language tailored to database programming.
4. Stored procedures scale with the database.

CON

1. Business logic does not belong in the database.
2. Stored procedures are written in specialist and clunky programming languages which the average developer has no interest in learning.
3. We can't ask the DBA to write stored procedures because DBAs hate developers.
4. Stored procedures run in the database and the database is the bottleneck.

Many of these general points also apply to Java Stored Procedures. I wrote an answer to your related question, so these Pros and Cons may seem familar.

PRO

1. Java stored procedures allow us to extend the functionality accessible to database programs.
2. In particular it can allow us greater flexibility to integrate operations in the database and the OS domains.
3. Lots of developers know how to write Java.
4. Java stored procedures allow us to deploy our database application across different DBMS products.

CON

1. Java does not perform as well as native database code.
2. Java stored procedures entail writing bespoke code which duplicates built-in functionality..
3. Java is not tailored to database operations.
4. Java can pose security problems. especially when it comes to running programs on the OS from inside the database.

The following is true of native stored procedures and Java stored procedures: code written by developers with no understanding of how databases work can perform really badly. This applies equally to front-ends built or ORM tools configured without the appropriate level of expertise. However, this situation is less likely to arise with native stored procedures because their functionality is shaped towards building efficient database applications.