define相当于定义一个字符常量,通过define定义之后,在通过&或者&&引用的时候不需要输入了。oracle在执行的时候自动用值进行了替换;
variable定义的是绑定变量。
(1) define
SQL> alter session set nls_language = american;
Session altered.
SQL> define
DEFINE _DATE = "03-OCT-09" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "myoracle" (CHAR)
DEFINE _USER = "SCOTT" (CHAR)
DEFINE _PRIVILEGE = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1002000100" (CHAR)
DEFINE _EDITOR = "Notepad" (CHAR)
DEFINE _O_VERSION = "Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options" (CHAR)
DEFINE _O_RELEASE = "1002000100" (CHAR)
SQL>
SQL> define a
SP2-0135: symbol a is UNDEFINED
SQL> define a = 1
SQL>
SQL> define
DEFINE _DATE = "03-OCT-09" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "myoracle" (CHAR)
DEFINE _USER = "SCOTT" (CHAR)
DEFINE _PRIVILEGE = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1002000100" (CHAR)
DEFINE _EDITOR = "Notepad" (CHAR)
DEFINE _O_VERSION = "Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options" (CHAR)
DEFINE _O_RELEASE = "1002000100" (CHAR)
DEFINE A = "1" (CHAR)
SQL> select * from dept where deptno = &a;
old 1: select * from dept where deptno = &a
new 1: select * from dept where deptno = 1
no rows selected
SQL>
SQL> column dname new_value a
SQL> select * from dept;
DEPTNO DNAME LOC ID
---------- -------------- ------------- ----------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> define
DEFINE _DATE = "03-OCT-09" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "myoracle" (CHAR)
DEFINE _USER = "SCOTT" (CHAR)
DEFINE _PRIVILEGE = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1002000100" (CHAR)
DEFINE _EDITOR = "Notepad" (CHAR)
DEFINE _O_VERSION = "Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options" (CHAR)
DEFINE _O_RELEASE = "1002000100" (CHAR)
DEFINE A = "OPERATIONS" (CHAR)
SQL>
SQL> select * from dept where dname = &a;
old 1: select * from dept where dname = &a
new 1: select * from dept where dname = OPERATIONS
select * from dept where dname = OPERATIONS
*
ERROR at line 1:
ORA-00904: "OPERATIONS": invalid identifier
SQL> select * from dept where dname = '&a';
old 1: select * from dept where dname = '&a'
new 1: select * from dept where dname = 'OPERATIONS'
DEPTNO DNAME LOC ID
---------- -------------- ------------- ----------
40 OPERATIONS BOSTON
SQL> define a
DEFINE A = "OPERATIONS" (CHAR)
SQL>
SQL> print a
SP2-0552: Bind variable "A" not declared.
(2) variable
SQL> variable a number;
SQL> print a;
A
----------
SQL> exec :a := 10;
PL/SQL procedure successfully completed.
SQL> print a;
A
----------
10
SQL> select * from dept where deptno = :a ;
DEPTNO DNAME LOC ID
---------- -------------- ------------- ----------
10 ACCOUNTING NEW YORK
分享到:
相关推荐
sqlplus环境变量.docsqlplus环境变量.docsqlplus环境变量.docsqlplus环境变量.doc
SQLPlus中的复制和粘贴技巧 SQLPlus中的复制和粘贴技巧 SQLPlus中的复制和粘贴技巧
Oracle SQLPLUS基础及sqlplus命令详解,详细阐述了oracle sqlplus基本命令和相关用法
俺也不是啥老鸟,因为最近老是在批量修改Procedure,...在GOGO上搜了一下sqlplus中批量执行sql文件的方法,结果.......(不知道是不是我不会查)。 我最讨厌做这做那的,这个方法自认为简单高效,欢迎拍砖!!!
sqlplus日期格式和中文乱码解决文.pdf
oracle中的sqlplus基础教程,可以基本掌握sqlplus在oracle中的用法
sqlplus中文教程sqlplus中文教程sqlplus中文教程sqlplus中文教程
sqlplus教程(中文),大家都看看吧
可以方便大家的更好的学习sql/plus
Oracle sqlplus中的命令大全总结,提升sqlplus操作水平
Oracle SQLplus常用的命令和函数
oracle的DBA必须熟练掌握的工具,设置字体和字符集,可以更好得提高工作效率。
sqlplus 常用命令行参数,Oracle 中 sqlplus 初学者用的 常用命令行参数
详细介绍了 linux下安装sqlplus的过程,包括安装包下载,环境变量配合等
Oracle中常用的SqlPlus命令以及命令详解
Sqlplus常用的命令,数据库管理员的学习笔记。
SQLPLUS命令使用大全SQLPLUS命令使用大全SQLPLUS命令使用大全SQLPLUS命令使用大全
SQLPlus命令,SQLPlus命令SQLPlus命令SQLPlus命令SQLPlus命令SQLPlus命令SQLPlus命令SQLPlus命令SQLPlus命令SQLPlus命令
Oracle SQLPLUS 常用指令 技巧 详细解说
ORACLE SQLPLUS 命令大全ORACLE SQLPLUS 命令大全ORACLE SQLPLUS 命令大全ORACLE SQLPLUS 命令大全ORACLE SQLPLUS 命令大全