Using Bind Variables With PL/SQL

When you embed an INSERT, UPDATE, DELETE, or SELECT SQL statement directly in your PL/SQL code, PL/SQL turns the variables in the WHERE and VALUES clauses into bind variables automatically. Oracle Database XE can reuse these SQL statement each time the same code is executed. When running similar statements with different variable values, you can improve performance by calling a stored procedure that accepts parameters, then issues the statements with the parameters substituted in the appropriate places.

You need to specify bind variables with dynamic SQL, in clauses such as WHERE and VALUES where you normally use variables. Instead of concatenating literals and variable values into a single string, replace the variables with the names of bind variables (preceded by a colon), and specify the corresponding PL/SQL variables with the USING clause. Using the USING clause, instead of concatenating the variables into the string, reduces parsing overhead and lets Oracle Database XE reuse the SQL statements.

In Example: Using Dynamic SQL to Manipulate Data in PL/SQL, :dptid, :dptname, :mgrid, and :locid are examples of bind variables.