I learned the concept of prepared statements in JDBC in Java. So I think that prepared statement is a concept in JDBC, but not in RDBMS.
To see whether my guess is right, may I ask whether any major RDBMS provide the feature of prepared statements, in their PL/PSM like languages,such as PL/SQL, PL/pgSQL, MySQL, Transact-SQL?
If there is any such RDBMS, is prepared statement provided in SQL, or in PL/PSM like languages,such as PL/SQL, PL/pgSQL, MySQL, Transact-SQL?
I read DIfference Between Stored Procedures and Prepared Statements..?, but I can't find which provides the feature of prepared statements, although I think prepared statement is a concept in JDBC not in RDBMS, and stored procedure is a concept in RDBMS only.
Every implementation of SQL-compliant RDBMS should support an API for server-side prepared statements. I can't think of one RDBMS that doesn't support prepared statements.
JDBC has a class for PreparedStatement. The implementation varies by each brand of JDBC driver, but all those that I have used just delegate to the RDBMS API. The JDBC driver sends an SQL query string to the database server, and the SQL may contain parameter placeholders for example ?
(some brands — like Oracle — support named parameters).
Some database implementations provide packages or functions you can use to execute a prepared statement, so you can create a query at runtime within a stored procedure.
- Oracle: https://docs.oracle.com/cd/A57673_01/DOC/api/doc/PAD18/ch8.htm
- Microsoft SQL Server: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-prepare-transact-sql?view=sql-server-2017
Some database implementations also support PREPARE
and EXECUTE
statements that you can call as a query. This allows you to use prepared statements in a stored procedure or an SQL script.
- MySQL: https://dev.mysql.com/doc/refman/8.0/en/sql-syntax-prepared-statements.html
- PostgreSQL: https://www.postgresql.org/docs/10/static/sql-prepare.html