Use of Bind Variable in SQLPLUS and SQL Queries of PL/SQL Block
Bind variable is used in SQLPLUS as replacement of literal values.
If you want to use bind variable in SQLPLUS first need to declare it as PL/SQL and then use in your statements or queries.
Following are the steps show you the example of using bind variables in SQLPLUS windows:
Create or Declare the Bind variable
Create the bind variable with VARIABLE Keyword and then specify its data type.
VARIABLE bind_var NUMBER
Refer or use of bind variable
use variables in PL/SQL by typing a colon (:) followed by variable name
:bind_var := 1;
Display assigned value of bind variable
PRINT bind_var
Example of bind variable as PL/SQL:
-- Assign value to bind variable
VARIABLE bind_var NUMBER;
Begin
:bind_var:= 1;
End;
/
--- Output
Print :bind_var
BIND_VAR
----------
1
Example of Bind Variable as SELECT query in PL/SQL:
-- Create table
SQL> create table test(id number,name varchar2(10));
Table created.
-- Insert data
SQL> insert into test values(1,'RAM');
1 row created.
SQL> Commit;
-- Use bind variable to fetch data from SQL Query
VARIABLE bind_var NUMBER;
VARIABLE bind_var2 varchar2(10);
Begin
:bind_var:= 1;
select name into :bind_var2 from test where id = :bind_var;
End;
/
-- Output
SQL> Print :bind_var2
BIND_VAR2
----------------
RAM
