Tham khảo tài liệu 'oracle sql internals handbook phần 9', công nghệ thông tin, cơ sở dữ liệu phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả | You can store SQL in the application itself Java side and access the database directly from the applet via a SQL statement. Or the programmer can make a request from the applet to the servlet which then sends the SQL to the database. Another option within Java is to simply make a procedure or function call Callable Statement in JDBC Driver and process the result set. In the last scenario the SQL would reside inside the database in an Oracle package. This article explores the advantages and disadvantages of these options and includes performance benchmarks. It s my intention to objectively answer my own questions while helping to make your decision easier when you encounter this issue in your own projects. The Power of a Package If you have the option of installing a PL SQL package as part of your application you should seriously consider installing it. Although the focus of this article is on PL SQL the package could also be written in Java in versions of Oracle and later. PL SQL packages have many advantages Privilege Management-Instead of being concerned about whether each user has the rights to perform a function and trapping exceptions throughout your code you can grant execute on a package. The user inherits rights to all of the underlying objects indirectly through package execution. For example let s assume that part of your code issued a TRUNCATE command on a table. If the command is issued as the connected user you can expect privilege problems and would need to resolve these problems by granting the proper privileges to the user. You would either have to connect as someone with privileges behind the scenes or put the TRUNCATE command in a procedure call in the package. The procedure becomes the gatekeeper 144 Oracle SQL Internals Handbook of the transaction and ensures that any access to the underlying objects goes through the procedure. Global Location-Having the SQL in one spot is the most flexible option By having calc_inventory as a procedure call
