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)