Bind references (or bind variables) are used to replace a single value in SQL
or PL/SQL, such as a character string, number, or date. Specifically, bind references
may be used to replace expressions in SELECT
, WHERE
,
GROUP BY
, ORDER BY
, HAVING
, CONNECT
BY
, and START WITH
clauses of queries. Bind references may
not be referenced in FROM
clauses or in place of reserved words
or clauses.
You create a bind reference by typing a colon (:
) followed immediately
by the column or parameter name. If you do not create a column or parameter
before making a bind reference to it in a SELECT
statement, Reports
Builder will create a parameter for you by default.
In the following example, the value of DFLTCOMM
replaces null
values of COMMPLAN
in the rows selected.
SELECT CUSTID, NVL(COMMPLAN, :DFLTCOMM) COMMPLAN
FROM ORD;
The value of CUST
is used to select a single customer.
SELECT ORDID, TOTAL
FROM ORD
WHERE CUSTID = :CUST;
All non-aggregate expressions such as NVL(COMMPLAN, :DFLTCOMM)
in the SELECT
clause must be replicated in the GROUP BY
clause.
SELECT NVL(COMMPLAN, :DFLTCOMM) COMMPLAN, SUM(TOTAL) TOTAL
FROM ORD
GROUP BY NVL(COMMPLAN, :DFLTCOMM);
The value of MINTOTAL
is used to select customers with a minimum
total of orders.
SELECT CUSTID, SUM(TOTAL) TOTAL
FROM ORD
GROUP BY CUSTID HAVING SUM(TOTAL) > :MINTOTAL;
The value of SORT
is used to select either SHIPDATE
or ORDERDATE
as the sort criterion. Note that this is not the same
as ORDER BY 1
because :SORT
is used as a value rather
than to identify the position of an expression in the SELECT
list.
Note that DECODE
is required in this example. You cannot use a
bind variable in an ORDER BY
clause unless it is with DECODE
.
SELECT ORDID, SHIPDATE, ORDERDATE, TOTAL
FROM ORD
ORDER BY DECODE(:SORT, 1, SHIPDATE, 2, ORDERDATE);
References in CONNECT BY
and START WITH
clauses are
used in the same way as they are in the WHERE
and HAVING
clauses.
procedure double is
begin;
:my_param := :my_param*2;
end;
The value of myparam
is multiplied
by two and assigned to myparam
.
Differences between bind and lexical references
Copyright © 1984, 2005, Oracle. All rights reserved.