sql plus中帮定变量的使用

一.声明绑定变量
SQL(c3rpt)>var SYS_B_0 varchar2(10)
SQL(c3rpt)>var SYS_B_1 varchar2(10)
SQL(c3rpt)>var SYS_B_2 varchar2(10)
SQL(c3rpt)>var SYS_B_3 varchar2(10)
SQL(c3rpt)>var SYS_B_4 number

二.给绑定变量赋值
SQL(c3rpt)>exec :SYS_B_0:='737'
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
SQL(c3rpt)>exec :SYS_B_1:='20080610'
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
SQL(c3rpt)>exec :SYS_B_2:='20080617'
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
SQL(c3rpt)>exec :SYS_B_3:='BK7E081'
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
SQL(c3rpt)>exec :SYS_B_4:=230
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
SQL(c3rpt)>set autot on

三.使用绑定变量
SQL(c3rpt)>SELECT assy."TXNDATE", def."SEQUENCE", def."LOSSQTY", def."LOSSREASONNAME",
2 def."QTYADJUSTREASONNAME", def."PDL", def."CUSTOMERNAME"
3 FROM "ATC_REPORT"."V_ASSYOUTLOT" assy,
4 "ATC_REPORT"."LOTMOVEDEFECTTABLE" def
5 WHERE assy."CONTAINERNAME" = def."CONTAINERNAME"
6 AND def."CUSTOMERNAME" = :"SYS_B_0"
7 AND assy."TXNDATE" >= to_date(:"SYS_B_1",'yyyymmdd')
8 AND assy."TXNDATE" < to_date(:"SYS_B_2",'yyyymmdd')
9 AND def."PDL" = :"SYS_B_3"
10 AND def."SEQUENCE" = :"SYS_B_4";
110 rows selected.
Elapsed: 00:00:02.28
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=271 Card=1 Bytes=157)
1 0 FILTER
2 1 NESTED LOOPS (Cost=271 Card=1 Bytes=157)
3 2 NESTED LOOPS (Cost=269 Card=1 Bytes=141)
4 3 NESTED LOOPS (Cost=268 Card=1 Bytes=108)
5 4 NESTED LOOPS (Cost=160 Card=2 Bytes=136)
6 5 TABLE ACCESS (BY INDEX ROWID) OF 'LOTMOVEDEFECTTABLE' (Cost=154 Card=2 Bytes=74)
7 6 INDEX (RANGE SCAN) OF 'LMD_INDX4' (NON-UNIQUE) (Cost=4 Card=528)
8 5 TABLE ACCESS (BY INDEX ROWID) OF 'CONTAINER' (Cost=3 Card=1 Bytes=31)
9 8 INDEX (RANGE SCAN) OF 'CONTAINER1' (NON-UNIQUE) (Cost=2 Card=1)
10 9 TABLE ACCESS (BY INDEX ROWID) OF 'LOTMOVEDEFECTTABLE' (Cost=5 Card=19 Bytes=361)
11 10 INDEX (RANGE SCAN) OF 'LMD_INDX1' (NON-UNIQUE) (Cost=3 Card=38)
12 4 TABLE ACCESS (BY INDEX ROWID) OF 'HISTORYMAINLINE' (Cost=54 Card=1 Bytes=40)
13 12 INDEX (RANGE SCAN) OF 'HISTORYMAINLINE1' (NON-UNIQUE) (Cost=5 Card=106)
14 3 TABLE ACCESS (BY INDEX ROWID) OF 'OPERATION' (Cost=1 Card=1 Bytes=33)
15 14 INDEX (UNIQUE SCAN) OF 'OPERATION289' (UNIQUE)
16 2 INDEX (RANGE SCAN) OF 'MV_INDX1' (NON-UNIQUE) (Cost=2Card=1 Bytes=16)