sqlplus定义变量区别(替换变量)
来源:互联网 发布:access数据库视频教程 编辑:程序博客网 时间:2024/04/30 06:11
5、非PL/SQL变量5.1、替换变量(仅用于SQL *Plus或者用于原理和SQL *Plus相同的开发工具):
临时存储值
利用它可以达到创建通用脚本的目的
利用它可以达到和用户交互,故在SQL *Plus中又称交互式命令
替换变量的格式式在变量名称前加一个&,以便在运行SQL命令时提示用户输入替换数据,然后按输入数据运行SQL命令
语法:
(1)& :“&变量名”eg:&name;
生命周期:单次引用中,不需要声明,如果替换字符或日期类型,最好用单引号扩起
使用范围:where、order by、列表达式、表名、整个SELECT 语句中
(2)&& :“&&变量名”eg:&&name;
生命周期:整个会话(session连接),不需要声明
(3)define :“define 变量名=变量值”eg:DEFINE a = clark;
生命周期:整个会话,预先声明,使用时用&引用声明的变量
define variable=用户创建的CHAR类型的值:define 变量名=值;
define column_name(变量名):查看变量命令。
undefine 变量名:清除变量
define:查看在当前会话中所有的替换变量和它们的值
sqlplus定义变量区别
oracle定义变量(常量)常用:declare、define、variable
1)define、variable用于sqlplus中,在整个sqlplus连接中都生效,而declare用于pl/sql中。
SQL> show user
USER is "TEST"
SQL> var
SP2-0568: No bind variables declared.
SQL> define
DEFINE _DATE = "20-9月 -10" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "orcl" (CHAR)
DEFINE _USER = "TEST" (CHAR)
DEFINE _PRIVILEGE = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1002000400" (CHAR)
DEFINE _EDITOR = "ed" (CHAR)
DEFINE _O_VERSION = "Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options" (CHAR)
DEFINE _O_RELEASE = "1002000400" (CHAR)
SQL> var x number; ——定义变量x,类型是number
SQL> exec :x:=1; ——给变量赋值1
PL/SQL procedure successfully completed.
SQL> print x
X
----------
1
SQL> define y='10';
SQL> define
DEFINE _DATE = "20-9月 -10" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "orcl" (CHAR)
DEFINE _USER = "TEST" (CHAR)
DEFINE _PRIVILEGE = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1002000400" (CHAR)
DEFINE _EDITOR = "ed" (CHAR)
DEFINE _O_VERSION = "Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options" (CHAR)
DEFINE _O_RELEASE = "1002000400" (CHAR)
DEFINE Y = "10" (CHAR)
SQL> disconn
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> print x
SP2-0640: Not connected
SP2-0641: "PRINT" requires connection to server
SQL> var x
variable x
datatype NUMBER
x变量值没了,但是变量定义让然存在
SQL> define
DEFINE _DATE = "20-9月 -10" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "" (CHAR)
DEFINE _USER = "" (CHAR)
DEFINE _PRIVILEGE = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1002000400" (CHAR)
DEFINE _EDITOR = "ed" (CHAR)
DEFINE _O_VERSION = "Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options" (CHAR)
DEFINE _O_RELEASE = "1002000400" (CHAR)
DEFINE Y = "10" (CHAR)
define定义的y值仍然存在
SQL> show serveroutput
serveroutput OFF
SQL> set serveroutput on
SQL> declare
2 m number :=1;
3 n number;
4 begin
5 select n1 into n from test where rownum=m;
6 dbms_output.put_line(n);
7 end;
8 /
1
PL/SQL procedure successfully completed.
2)variable(var)和define区别在于,前者用于绑定变量,后者是用于&或&&进行变量替换。
SQL> var
SP2-0568: No bind variables declared.
SQL> var x number;
SQL> exec :x:=1;
PL/SQL procedure successfully completed.
SQL> select * from test where rownum=:x;
N1 N2
---------- ----------
1 2
SQL> define rn=1;
SQL> define
DEFINE _DATE = "20-9月 -10" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "orcl" (CHAR)
DEFINE _USER = "TEST" (CHAR)
DEFINE _PRIVILEGE = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1002000400" (CHAR)
DEFINE _EDITOR = "ed" (CHAR)
DEFINE _O_VERSION = "Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options" (CHAR)
DEFINE _O_RELEASE = "1002000400" (CHAR)
DEFINE RN = "1" (CHAR)
SQL> select * from test where rownum=&rn;
old 1: select * from test where rownum=&rn
new 1: select * from test where rownum=1
N1 N2
---------- ----------
1 2
- sqlplus定义变量区别(替换变量)
- sqlplus取消变量定义
- Oracle变量定义和使用(sqlplus和sql developer使用变量的区别)
- [用于sqlplus工具环境下的]替换变量(&和&&区别)
- sqlplus定义绑变量执行(简单例子)
- make替换变量的定义
- sqlplus中define定义的常量和variable定义的变量已经column给变量赋值的区别
- 变量声明和变量定义的区别
- 定义变量 与 声明变量 的区别
- 变量定义和变量声明的区别
- 使用Freemarker替换Java字符串定义变量
- SQL*Plus中替换变量与定义变量
- 变量替换
- 变量替换
- 变量替换
- Shell: 变量替换 命令替换 & ~波浪号替换的区别
- 类变量、成员变量、实例变量、局部变量、静态变量、全局变量的定义与区别
- 定义对象(变量)
- 一口一口吃掉Hibernate(七)——继承映射
- TCP延迟确认过长问题
- bash字符串常用函数
- C指针原理(33)-C指针基础
- 适配iOS7
- sqlplus定义变量区别(替换变量)
- Java程序输出一个整数的二进制,八进制,十六进制数
- magento数据库配置
- SQL中WHERE变量IS NULL条件导致全表扫描问题的解决方法
- HTTP超详细解析——03【压缩】
- Objective-C基于C语言闭包Block的实现
- 面试问题系列:windows和linux下的静态和动态链接库
- php实现socket(转)
- Linux实用工具和控制台下小技巧