Bind variables can be properly used to help develop efficient and extensible applications.
A bind variable is a placeholder in an SQL statement. It must be replaced with a valid value (a constant) or a value address (a variable), for the statement to be properly executed. By using bind variables, you can write an SQL statement that accepts input values or parameters at runtime.
Similar to parameters in subprograms, the values of bind variables in SQL statements are provided at runtime. A subprogram can be compiled once and then run multiple times by using different parameters. Similarly, an SQL statement with bind variable placeholders can be hard parsed once, and then soft parsed by using different bind variables.
A hard parse includes optimization and row source generation. It is a CPU-intensive operation. It is also a non-extensible operation that cannot be executed in parallel with many other operations. In a soft parse, the statement is directly executed without optimization and row source generation. Therefore, for the same statement, the parsing speed of a soft parse is usually much faster than that of a hard parse.
Although soft parses are more efficient than hard parses, the costs of large-scale soft parses are very high. Using Procedural Language (PL) is the most convenient way to maximize the effectiveness and extensibility of applications and minimize the parsing costs.
Example of using bind variables
delimiter /
DECLARE
v_sql varchar2(2000) := '';
s_name ware.w_name%TYPE := '';
BEGIN
v_sql := 'select w_name from ware where w_id = :1';
EXECUTE IMMEDIATE v_sql INTO s_name USING '1' ;
dbms_output.put_line('w_name IS ' || s_name);
END;
/
delimiter ;