Oracle SQL plus环境设置与使用

墨蓝 2022-08-27 15:50 196阅读 0赞

# 目录 #

*  glogin.sql配置
 *  替代变量
 *  定制SQL.PLUS环境
 *  spool用法

# glogin.sql配置 #

配置文件位置：$ORACLE\_HOME/sqlplus/admin/glogin.sql

修改默认的sql缓存区编辑器

define \_editor='/usr/bin/vi'

set line 120

col parameter for a30

col value for a40

col property\_name for a25

col property\_value for a30

col description for a30

col ename for a15

col job for a12

col table\_name for a15

col username for a15

col account\_status for a20

col dname for a20

col loc for a20

col owner for a15

col column\_name for a20

col constraint\_name for a25

col constraint\_type for a25

col index\_name for a20

col index\_type for a20

col TABLESPACE\_NAME for a25

col OBJECT\_NAME for a20

col ROLE for a30

col GRANTED\_ROLE for a30

col view\_name for a20

col text for a30

col sequence\_name for a25

col privilege for a20

col grantor for a12

col oracle\_username for 25

col os\_user\_name for a25

# 替代变量 #

A substitution variable can be used anywhere in SQL and SQL\*Plus commands, except as the first word entered at the command prompt.

替代变量

set verify on | off

单个(&)符号

select ename,job,sal  
from emp  
where ename='&name'  
/

select ename,sal

from emp

where job='&job'

&&符号

select ename,job,sal,&&col\_name

from emp

order by &col\_name

定义用户变量（sqlplus环境里的变量）

DEFINE创建一个字符型用户变量

define ename=SCOTT

SELECT ename,job,sal

from emp

where ename='&ename'

ACCEPT读取用户输入并将其保存在变量里

ACCEPT命令

accept dept prompt 'provide the deptment name: '

select \* from dept where dname='&dept';

可以使用define命令查看变量

SQL> define dept

DEFINE DEPT            = "SALES" (CHAR)

UNDEFINE清除变量

undefine ename,dept

参考资料：

替换变量（仅用于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：查看在当前会话中所有的替换变量和它们的值

（4）accept

生命周期：整个会话

预先声明，可以客户化提示信息，使用时用&引用声明的变量。

定义：

accept 变量名name number/char/date prompt '提示信息内容'即：ACC\[EPT\] variable \[NUM\[BER\] | CHAR | DATE\] \[FOR\[MAT\] format\] \[DEF\[AULT\] default\] \[PROMPT text | NOPR\[OMPT\]\] \[HIDE\]

解释：

PROMPT命令：用于输出提示用户的信息，以便使用户了解脚本文件的功能和运行情况

PAUSE命令：用于暂停脚本文件的运行

HIDE选项：用于隐藏用户的输入，使别人不可见，安全

这条命令的意思是：当plsql程序段执行到变量name的时候，此时需要用户的交互才能继续执行下去，plsql程序段会显示“提示信息内容”让用户输入相关信息（如果指定hide选项，那么在接下去用户输入的东西将被用星号显示出来增加安全，有点像输入密码），用户输入的内容被接收到并且把它付给name，关于在“提示信息内容”下用户输入的内容的类型，plsql程序段开发人员来通过number/char/date指定，变量name得到正确的值以后，继续执行相关下面的程序！

例：accept a char prompt '请输入员工的雇佣时间(yyyy-mm-dd):' hide

例：accept a char prompt 'input a:' hide

替换变量非常的依赖SQL \*Plus的环境，当环境变量verify被关闭的时候它就不可用，只有通过打开它才能使用：

set verify(环境变量) off;     关闭调试命令（关掉替换过程）

set verify(环境变量) on;     打开调试命令(可以看到替换过程)

# 定制SQL.PLUS环境 #

为环境 变量赋值

SET 环境变量名 变量值

显示变量设置

SHOW 变量名 | ALL

环境变量例子

ARRAYSIZE     控制SQL.PLUS每次从数据库提取的资料数量，可设定1到5000，值越大执行效率越好

COLSEP          设定选择出的各列的间隔

FEEDBACK          查询选择出的行数大于N时，显示返回的行数

HEADING          控制报表是否输出字段名称

LINESIZE          设定单行显示长度

LONG               设定LONG类型最大显示宽度

PAGESIZE          设定每页资料显示数量

PAUSE               控制报表执行时如何卷动屏幕

写一个脚本，要求显示某个雇佣时间范围内，所有雇员的姓名、工作和雇佣日期的信息。要求：将名字和工作连接起来，中间用“,”隔开，将列命名为EMPLOYEES和HIREDATE。并使用ACCEPT提示客户输入两个时间范围。头标题为：employee and hiredate info

ttitle 'employee and hiredate info';  
BTITLE www.cuug.com  
set pagesize 21  
set line 80  
set verify off  
col EMPLOYEES for a35  
accept lowdate prompt 'please enter the low date range (MM/DD/YYYY):'  
accept highdate prompt 'Please enter the high date range (MM/DD/YYYY):'  
  
select a.ename||','||b.loc EMPLOYEES,a.HIREDATE  
from emp a, dept b  
where a.deptno=b.deptno and hiredate>to\_date('&lowdate','mm/dd/yyyy') and hiredate<to\_date('&highdate','mm/dd/yyyy')

/  
  
col EMPLOYEES clear  
ttitle off  
BTITLE off  
set pagesize 14  
set line 120  
set verify on

# spool用法 #

\#vi 1.sql

spool on

spool /export/home/oracle/1.txt

select \* from dept

spool off

SQL>1.sql

\#cat /export/home/oracle/1.txt