Does any RDBMS provide the feature of prepared sta

2019-06-14 19:02发布

问题:

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.

回答1:

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