この記事は約12分39秒で読むことができます。

sql oracle sqlplus バインド変数

置換変数の定義

パターン1


SELECT sysdate FROM dual WHERE rownum = '&bind1' or rownum = '&bind2';

パターン2


SELECT sysdate FROM dual WHERE rownum = '&1' or rownum = '&2';

置換変数の設定

パターン1


[oracle@d102981b4117 ~]$ sqlplus aine/ORACLE_PWD@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Sat Jan 12 09:31:23 2019
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Last Successful login time: Sat Jan 12 2019 09:28:00 +00:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

AINE@pdb1> define
DEFINE _DATE           = "12-JAN-19" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "pdb1" (CHAR)
DEFINE _USER           = "AINE" (CHAR)
DEFINE _PRIVILEGE      = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1803000000" (CHAR)
DEFINE _EDITOR         = "nano" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0" (CHAR)
DEFINE _O_RELEASE      = "1803000000" (CHAR)
AINE@pdb1> define
DEFINE _DATE           = "12-JAN-19" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "pdb1" (CHAR)
DEFINE _USER           = "AINE" (CHAR)
DEFINE _PRIVILEGE      = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1803000000" (CHAR)
DEFINE _EDITOR         = "nano" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0" (CHAR)
DEFINE _O_RELEASE      = "1803000000" (CHAR)
AINE@pdb1> define bind1=1
AINE@pdb1> define bind2=2
AINE@pdb1> define
DEFINE _DATE           = "12-JAN-19" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "pdb1" (CHAR)
DEFINE _USER           = "AINE" (CHAR)
DEFINE _PRIVILEGE      = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1803000000" (CHAR)
DEFINE _EDITOR         = "nano" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0" (CHAR)
DEFINE _O_RELEASE      = "1803000000" (CHAR)
DEFINE BIND1           = "1" (CHAR)
DEFINE BIND2           = "2" (CHAR)

AINE@pdb1> SELECT sysdate FROM dual WHERE rownum = '&bind1' or rownum = '&bind2';
old   1: SELECT sysdate FROM dual WHERE rownum = '&bind1' or rownum = '&bind2'
new   1: SELECT sysdate FROM dual WHERE rownum = '1' or rownum = '2'

1 row selected.

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 208277575

-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
|   1 |  COUNT           |      |       |            |          |
|*  2 |   FILTER         |      |       |            |          |
|   3 |    FAST DUAL     |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(ROWNUM=1 OR ROWNUM=2)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        554  bytes sent via SQL*Net to client
        624  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

置換変数の設定

パターン2


AINE@pdb1> host vim test.sql

AINE@pdb1> host ls
epel-release-7-11.noarch.rpm  rlwrap-extensions  setPassword.sh  test.sql

AINE@pdb1> @test.sql 1 2
old   1: SELECT sysdate FROM dual WHERE rownum = '&1' or rownum = '&2'
new   1: SELECT sysdate FROM dual WHERE rownum = '1' or rownum = '2'

1 row selected.

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 208277575

-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
|   1 |  COUNT           |      |       |            |          |
|*  2 |   FILTER         |      |       |            |          |
|   3 |    FAST DUAL     |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(ROWNUM=1 OR ROWNUM=2)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        554  bytes sent via SQL*Net to client
        624  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Leave a Reply

Your email address will not be published. Required fields are marked *