SQL*Plus中替换变量与定义变量

SQL*Plus中替换变量与定义变量

2014-09-11 21:25 by 潇湘隐者, 1266 阅读, 0 评论, 收藏, 编辑

替换变量

SQL*Plus中的替换变量又叫替代变量，它一般用来临时存储相关数据；在SQL语句之间传递值。一般使用&或&&前缀来指定替换变量. 关于使用替换变量，一般是利用其创建通用的脚本或达到和用户交换目的。如下所示：

例如，我需要查看表的相关信息，如果不使用替换变量，每次查询我都要修改脚本。非常不便，如果使用替换变量，我们可以将下面脚本存放在tab.sql脚本中,每次运行时，只需要输入替换变量的值就可以了。

COL LOGGING FOR A7;

COL OWNER FOR A12;

COL TABLE_NAME FOR A30

COL TABLESPACE_NAME FOR A30

SELECT OWNER, TABLE_NAME, TABLESPACE_NAME, STATUS, COMPRESSION, 

       LOGGING, PARTITIONED, NUM_ROWS,

       TO_CHAR(LAST_ANALYZED,'YY-MM-DD HH24:MI:SS') LAST_ANALYZED

FROM DBA_TABLES 

    WHERE TABLE_NAME LIKE '&table_name_like%'

    ORDER BY TABLE_NAME;

 

epps> @tab.sql

Enter value for table_name_like: EMP

old   4:  WHERE  TABLE_NAME LIKE '&table_name_like%'

new   4:  WHERE  TABLE_NAME LIKE 'EMP%'

OWNER     TABLE_NAME       TABLESPACE_NAME      STATUS   COMPRESS LOGGING PAR   NUM_ROWS LAST_ANALYZED

------- --------------- ---------------------- -------- -------- ------- --- ---------- -----------------

SCOTT        EMP               USERS             VALID    DISABLED YES     NO          14 09-07-20 22:00:12

WIPOWNER     EMPLOYEE_MASTER   WIPOWNER_DATA     VALID    DISABLED YES     NO       12084 14-09-07 14:08:22

epps> @tab.sql

Enter value for table_name_like: DEPT

old   4:  WHERE  TABLE_NAME LIKE '&table_name_like%'

new   4:  WHERE  TABLE_NAME LIKE 'DEPT%'

OWNER     TABLE_NAME    TABLESPACE_NAME    STATUS   COMPRESS LOGGING PAR   NUM_ROWS LAST_ANALYZED

------- -------------- ------------------ -------- -------- ------- --- ---------- -----------------

SCOTT       DEPT                USERS       VALID    DISABLED YES     NO           4 09-07-20 22:00:12

epps> 

如果替换变量为数字类型，那么可以直接是&+变量名称，如下所示&DEPTNO，

SQL> SELECT deptno, dname, loc 

  2  FROM SCOTT.DEPT

  3  WHERE DEPTNO= &DEPTNO;

Enter value for deptno: 10

old   3: WHERE DEPTNO= &DEPTNO

new   3: WHERE DEPTNO= 10

    DEPTNO DNAME          LOC

---------- -------------- -------------

        10 ACCOUNTING     NEW YORK

如果是字符串类型或日期类型，最好使用单引号将其包裹起来，否则在输入变量值时则必须加上单引号，不那样做就会报错，如下所示。

SQL> SELECT deptno, dname, loc 

  2  FROM SCOTT.DEPT

  3  WHERE DNAME= &DNAME;

Enter value for dname: SALES

old   3: WHERE DNAME= &DNAME

new   3: WHERE DNAME= SALES

WHERE DNAME= SALES

             *

ERROR at line 3:

ORA-00904: "SALES": invalid identifier

SQL> SELECT deptno, dname, loc 

  2  FROM SCOTT.DEPT

  3  WHERE DNAME= &DNAME;

Enter value for dname: 'SALES'

old   3: WHERE DNAME= &DNAME

new   3: WHERE DNAME= 'SALES'

    DEPTNO DNAME          LOC

---------- -------------- -------------

        30 SALES          CHICAGO

 

关于替换变量中&与&&的区别,两者区别在于作用范围不一样，&引用的替换变量只在当前SQL有效，而&&引用的替换变量则在当前会话有效。下面以人们经常引用的例子来说明

SQL> SELECT 2+&NUM FROM DUAL;

Enter value for num: 2

old   1: SELECT 2+&NUM FROM DUAL

new   1: SELECT 2+2 FROM DUAL

       2+2

----------

         4

SQL> SELECT 3+&NUM FROM DUAL;

Enter value for num: 3

old   1: SELECT 3+&NUM FROM DUAL

new   1: SELECT 3+3 FROM DUAL

       3+3

----------

         6

SQL> SELECT 2+&&NUM FROM DUAL;

Enter value for num: 2

old   1: SELECT 2+&&NUM FROM DUAL

new   1: SELECT 2+2 FROM DUAL

       2+2

----------

         4

SQL> SELECT 3+&&NUM FROM DUAL;

old   1: SELECT 3+&&NUM FROM DUAL

new   1: SELECT 3+2 FROM DUAL

       3+2

----------

         5

 

小知识1：在存储过程或包体里面，经常有在字符串中使用&的情况，执行脚本时，经常会将这些字符串视为替换变量，要求输入值，这样烦不甚烦，其实只需要设置一下SQL*PLUS的环境变量即可避免这种情况。通常通过SET DEFINE OFF

小知识2：如果要显示SQL*Plus使用替换值替换后的脚本文件，可以使用SET VERIFY ON/OFF 命令

 

SQL> SET VERIFY OFF

SQL> SELECT 2+&NUM FROM DUAL;

       2+2

----------

         4

SQL> SET VERIFY ON

SQL> SELECT 2+&NUM FROM DUAL;

old   1: SELECT 2+&NUM FROM DUAL

new   1: SELECT 2+2 FROM DUAL

       2+2

----------

         4

 

用户定义变量

使用DEFINE定义了的变量（或许我们可以叫其定义常量，和C/C++中的DEFINE非常类似），可以使用&引用声明的变量。其作用范围或生命周期通常是整个会话。如果定义了变量后，需要清除变量，则可以使用UNDEFINE清除变量，使用DEFINE VARIABLE来查看变量

SQL> DEFINE NUM=2;            --定义变量

SQL> DEFINE NUM                    --查看变量

DEFINE NUM             = "2" (CHAR)

SQL> SELECT 2+&NUM FROM DUAL;      --引用变量

old   1: SELECT 2+&NUM FROM DUAL

new   1: SELECT 2+2 FROM DUAL

       2+2

----------

         4

SQL> UNDEFINE NUM;               --清除变量

SQL> SELECT 2+&NUM FROM DUAL;    --清除变量后，&NUM变成替换变量了。需要输入值

Enter value for num: 

 

小知识点：查看当前会话下，SQL*Plus下定义的变量

SQL> define

DEFINE _DATE = "11-SEP-14" (CHAR)

DEFINE _CONNECT_IDENTIFIER = "epps" (CHAR)

DEFINE _USER = "SYS" (CHAR)

DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR)

DEFINE _SQLPLUS_RELEASE = "1002000400" (CHAR)

DEFINE _EDITOR = "ed" (CHAR)

DEFINE _O_VERSION = "Oracle Database 10g Release 10.2.0.4.0 - Production" (CHAR)

DEFINE _O_RELEASE = "1002000400" (CHAR)

 

 

VARIABLE变量和DEFINE变量不同的是其需要指定变量类型，类似编程语言中的强类型和弱类型变量。前者用于绑定变量，后者是用于&或&&进行变量替换

 

SQL> VAR NUM NUMBER;

SQL> EXEC :NUM :=10;

PL/SQL procedure successfully completed.

SQL> PRINT NUM;

NUM

----------

10

 

DECLARE定义变量则一般用于PL/SQL中。一般大家也用得比较多。在此不做说明。

参考资料：

http://www.doc88.com/p-783671200732.html

http://blog.itpub.net/9933980/viewspace-627702/

http://blog.csdn.net/haiross/article/details/15340489

作者： 潇湘隐者

出处： http://www.cnblogs.com/kerrycode/

本文版权归作者所有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接.

=====================================

  sqlplus变量定义, 调用过程 

 

oracle定义变量（常量）常用：declare、define、variable

1）define、variable用于sqlplus中，在整个sqlplus连接中都生效(until exit,disc是cut down session)，而declare用于pl/sql中。

2）variable（var）和define区别在于，前者用于绑定变量，后者是用于&或&&进行变量替换(使用场合,拿来当输入参数)。

define

SQL> define x='SCOTT'
SQL> define
DEFINE _DATE           = "29-9月 -13" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "myorcl11" (CHAR)
DEFINE _USER           = "SYS" (CHAR)
DEFINE _PRIVILEGE      = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1102000100" (CHAR)
DEFINE _EDITOR         = "vim" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options" (CHAR)
DEFINE _O_RELEASE      = "1102000100" (CHAR)
DEFINE X               = "SCOTT" (CHAR)
SQL> select distinct owner from dba_segments where wner='&x';
old   1: select distinct owner from dba_segments where wner='&x'
new   1: select distinct owner from dba_segments where wner='SCOTT'

OWNER
------------------------------------------------------------
SCOTT

var

SQL> var y numb
Usage: VAR[IABLE] [ [ NUMBER | CHAR | CHAR (n [CHAR|BYTE]) |
                    VARCHAR2 (n [CHAR|BYTE]) | NCHAR | NCHAR (n) |
                    NVARCHAR2 (n) | CLOB | NCLOB | BLOB | BFILE
                    REFCURSOR | BINARY_FLOAT | BINARY_DOUBLE ] ]
SQL> var y varchar2(20)
SQL> var
variable   y
datatype   VARCHAR2(20)
SQL> exec :y := 'SCOTT'

PL/SQL procedure successfully completed.

SQL> print :y

Y
-------
SCOTT

SQL> select distinct owner from dba_segments where   owner=':y'; --wrong

no rows selected

SQL> select distinct owner from dba_segments where wner=:y;

OWNER
------------------------------------------------------------
SCOTT

SQL> variable dba varchar2(30)

SQL> exec :dba := dbms_utility.make_data_block_address(4, 20);

PL/SQL procedure successfully completed.

SQL> print dba

DBA

--------------------------------

16777236

执行存储过程：(加楼上共4种方法)

法1

BEGIN

    getDeptCount;

END;

法2

EXEC getDeptCount    

法3

CALL  getDeptCount();

注意：

定义无参存储过程时，存储过程名后不能加()

在块中或是通过EXEC调用存储过程时可以省略()

通过CALL调用无参存储过程必须加上()

参数只能指定类型,不能指定长度精度范围,可以指定默认值：

varchar2(200)  ,只能用varchar2

    ...PROCEDURE add_deptno(v_deptno IN dept.deptno%TYPE,

                v_dname IN VARCHAR2,

                v_loc IN dept.loc%TYPE DEFAULT 'BEJING')...

给过程传递参数的方法：

    1，位置传递 exec add_dept(50,'SALES','BEIJING')

    2，名称传递 exec add_dept(v_dname=>'SALES',v_deptno=>50,v_loc=>'BEIJING')

    3，组合传递 exec add_dept(50,v_loc=>'BEIJING,v_dname=>'SALES')

=============================

oracle 的sqlplus 的声明变量和定义变量

• 博客分类： 
• oracle，sqlplus

 

在SQLPLUS里面，我们可以使用show all来查看系统的一些设置。

sql>  show all;

appinfo 为 OFF 并且已设置为 "SQL*Plus"

arraysize 15

autocommit OFF

autoprint OFF

autorecovery OFF

autotrace OFF

blockterminator "." (hex 2e)

btitle OFF 为下一条 SELECT 语句的前几个字符

cmdsep OFF

colsep " "

compatibility version NATIVE

concat "." (hex 2e)

copycommit 0

COPYTYPECHECK 为 ON

define "&" (hex 26)

describe DEPTH 1 LINENUM OFF INDENT ON

echo OFF

editfile "afiedt.buf"

embedded OFF

escape OFF

用于 6 或更多行的 FEEDBACK ON

flagger OFF

flush ON

heading ON

headsep "|" (hex 7c)

instance "local"

linesize 80

lno 14

loboffset 1

logsource ""

long 80

longchunksize 80

markup HTML OFF HEAD "<style type='text/css'> body {font:10pt Arial,Helvetica,sa

ns-serif; color:black; background:White;} p {font:10pt Arial,Helvetica,sans-seri

f; color:black; background:White;} table,tr,td {font:10pt Arial,Helvetica,sans-s

erif; color:Black; background:#f7f7e7; padding:0px 0px 0px 0px; margin:0px 0px 0

px 0px;} th {font:bold 10pt Arial,Helvetica,sans-serif; color:#336699; backgroun

d:#cccc99; padding:0px 0px 0px 0px;} h1 {font:16pt Arial,Helvetica,Geneva,sans-s

erif; color:#336699; background-color:White; border-bottom:1px solid #cccc99; ma

rgin-top:0pt; margin-bottom:0pt; padding:0px 0px 0px 0px;} h2 {font:bold 10pt Ar

ial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; margin-t

op:4pt; margin-bottom:0pt;} a {font:9pt Arial,Helvetica,sans-serif; color:#66330

0; background:#ffffff; margin-top:0pt; margin-bottom:0pt; vertical-align:top;}</

style><title>SQL*Plus Report</title>" BODY "" TABLE "border='1' width='90%' alig

n='center' summary='Script output'" SPOOL OFF ENTMAP ON PREFORMAT OFF

newpage 1

null ""

numformat ""

numwidth 10

pagesize 14

PAUSE 为 OFF

pno 0

recsep WRAP

recsepchar " " (hex 20)

release 1002000100

repfooter OFF  为 NULL

repheader OFF  为 NULL

serveroutput OFF

shiftinout INVISIBLE

showmode OFF

spool OFF

sqlblanklines OFF

sqlcase MIXED

sqlcode 0

sqlcontinue "> "

sqlnumber ON

sqlpluscompatibility 10.2.0

sqlprefix "#" (hex 23)

sqlprompt "SQL> "

sqlterminator ";" (hex 3b)

suffix "sql"

tab ON

termout ON

timing OFF

trimout ON

trimspool OFF

ttitle OFF 为下一条 SELECT 语句的前几个字符

underline "-" (hex 2d)

USER 为 "SYS"

verify ON

wrap : 将换至下一行

这些都是系统的一些设置变量。

实际上，ORACLE还提供了自己定义变量  包括define 变量和绑定变量

sql > define x

SQL> define x='abc'

要引用这个变量，需要使用and符号，假如是字符串，需要加上单引号。

SQL> select '&x' from dual;

原值    1: select '&x' from dual

新值    1: select 'abc' from dual

'AB

---

abc

================================

绑定变量

要想使用绑定变量，需要先定义它

variable x varchar2(10)

begin

  :x := 'hello';

end;

/

print :x

define 变量总是被sqlplus 扩展的字符串，而declared变量可以被SQL和PLSQL作为真正的绑定变量使用

参考：

sqlplus 变量 百度