Prepared SQL
In the case that a statement must be created dynamically but will be executed repeatedly, the prepare phrase of compilation can consume significant resources and time.
To solve this problem, databases provide two kinds of prepared SQL: prepared statements and stored procedures.
Prepared Statements
PreparedStatement interface is for handling prepared SQL statement.
An SQL statement is precompiled and stored in a PreparedStatement object.
This object can then be used to execute this statement multiple times.
Stored Procedures
A stored procedure is a group of SQL statements that form a logical unit and perform a particular task. Stored procedures are used to encapsulate a set of operations or queries to execute on a database server.
- Stored procedures are supported by most DBMSs, but there is a fair amount of variation in their syntax and capabilities.
- Oracle JDBC drivers support execution of PL/SQL stored procedures and anonymous blocks.
PL/SQL
PL/SQL is an Oracle’s extension to SQL.
It is a transaction processing language that supports procedural constructs and variable declarations.
Unlike SQL, PL/SQL allows you to group SQL statements logically and send them to Oracle in a block rather than one by one.
External Procedures
An external procedure is a procedure implemented in a language such as C not stored in the database but callable from SQL.
After the procedure is implemented, the compiled code must be placed in a file accessible to the database server.