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

替换变量

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

时间： 2024-09-14 20:09:29

SQL*Plus中替换变量与定义变量的相关文章

浅谈MySQL存储过程中declare和set定义变量的区别_Mysql

在存储过程中常看到declare定义的变量和@set定义的变量.简单的来说,declare定义的类似是局部变量,@set定义的类似全局变量. 1.declare定义的变量类似java类中的局部变量,仅在类中生效.即只在存储过程中的begin和end之间生效. 2.@set定义的变量,叫做会话变量,也叫用户定义变量,在整个会话中都起作用(比如某个应用的一个连接过程中),即这个变量可以在被调用的存储过程或者代码之间共享数据.如何理解呢?可以看下面这个简单例子,很好理解.  (1)先执行下面脚本,创建

如何从Oracle 9i SQL*Loader中生成外部表定义

Oracle 9i中引入的外部表允许您将一个顺序文本文件映射到一个Oracle表定义中.一旦您定义了外部表,您就能应用SQL SELECT语句的所有功能--包括并行查询--来处理它. 外部表以SQL*Loader为基础,但定义它们的语法有所不同.如果您已经熟悉SQL*Loader,就有一条捷径:应用SQL*Loader本身生成一段为您建立外部表定义的脚本. 当您添加一个SQL*Loader命令行时,参数EXTERNAL_TABLE=GENERATE_ONLY,它将把系统使用的控制文件转译成一段S

如何在SQL *Plus中把数据库栏以变量的形式保存

Oracle SQL *Plus有一个非常有用的子参数(subparameter),它从属于一个叫着NEW_VALUE的数据栏参数.NEW_VALUE指示允许你以SQL *Plus脚本中的变量保存从Oracle表格查询而得到的数据. 用过使用NEW_VALUE参数,你可以像真正的编程语言那样使用SQL *Plus脚本来保存和寻址程序变量,如同PL/SQL那样.   保存SQL *Plus变量并在其中填充Oracle数据是非常重要的特性,由于它减少了对数据库访问,所以它提高了SQL *Plus脚本

SQL Server 中VARCHAR（MAX）变量赋值引起的性能问题。

案例环境:           操作系统版本 : Windows Server 2008 R2 Standard  SP1           数据库版本   :  Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64) 案例介绍:   由于不能将生产环境的代码和数据贴上来,所以我构造了下面一个小案例,当然没法和生产环境的案例一致.只能是接近而已.但是足以反映问题本质就足够了. DROP TABLE ProductPrice;   GO   CRE

mysql 存储过程中变量的定义与赋值操作_Mysql

一.变量的定义 mysql中变量定义用declare来定义一局部变量,该变量的使用范围只能在begin...end 块中使用,变量必须定义在复合语句的开头,并且是在其它语句之前,也可以同时申明多个变量,如果需要,可以使用default赋默认值. 定义一个变量语法如下: declare var_name[,...] type[default value]看一个变量定义实例 declare last date;二.mysql存储过程变量赋值 变量的赋值可直接赋值与查询赋值来操作,直接赋值可以用set

【 MAKEFILE 编程基础之三】详解 MAKEFILE 变量的定义规则使用！

本站文章均为 李华明Himi 原创,转载务必在明显处注明:  转载自[黑米GameDev街区] 原文链接: http://www.himigame.com/gcc-makefile/770.html 使用变量: 在Makefile中的定义的变量,就像是C/C++语言中的宏一样,他代表了一个文本字串,在Makefile中执行的时候其会自动原模原样地展开在所使用的地方.其与C/C++所不同的是,你可以在Makefile中改变其值.在Makefile中,变量可以使用在"目标","依

Windows Powershell 定义变量_PowerShell

变量可以临时保存数据,因此可以把数据保存在变量中,以便进一步操作. #定义变量 $a=10 $b=4 #计算变量 $result=$a*$b $msg="保存文本" #输出变量 $result $msg 40 保存文本 powershell 不需要显示地去声明,可以自动创建变量,只须记住变量的前缀为$. 创建好了变量后,可以通过变量名输出变量,也可以把变量名存在字符串中.但是有个例外单引号中的字符串不会识别和处理变量名. 选择变量名 在powershell中变量名均是以美元符"

javascript定义变量时有var和没有var的区别探讨_基础知识

我们先来看一段代码 function show(){ alert(abc); } var abc="defg"; show(); 有过C++或Java编程经验的人可能会说:"这程序,死定了,变量竟然在引用了该变量的函数后边定义,bug会灭掉你的."放在浏览器上运行一下,结果怎样?完美运行!接下来我们就说一下这是咋回事--有var和没有var定义的变量的区别. 1.没有var 简明的说,定义变量时省略var是不安全的,不过是合法的.这时无论该变量是在什么位置定义的,解

PHP 变量的定义方法_php基础

变量的定义 ⚑变量的类型 ⚑变量的使用 变量即是存在于内存中可以不断变化的一个个对象,我们可以将内存想象成一个街道,街道中有许多住户,每位住户都会有自己的门牌号码,这就好比是内存中的地址(C语言中经常提到的一个概念,在这里我们就不多讨论了),对于其中一家住户我们可以说是1号楼1号,还可以说成是王小明家,用一个名字来代替一个地址.而王小明家里某一时刻有几个人就是一个变化的量了,可能中午有3个人,下午只有1个人,晚上有5个人.因此,对于内存中的某一地址我们要引用,也可以叫它为A,或者area.这就是