替代变量与SQL*Plus环境设置

旧城等待， 2022-05-15 03:24 169阅读 0赞

\--============================

\-- 替代变量与SQL\*Plus环境设置

\--============================

1.SQL与PL/SQL代码终止符

SQL代码以";"来表示代码的终止

PL/SQL代码以"."来表示代码的终止，以"/"表示代码的执行

scott@ORCL> select \* from emp where empno=7788;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

\---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7788 SCOTT ANALYST 7566 19\-APR\-87 3100 20

scott@ORCL> declare v\_ename varchar2(10);

2 begin

3 select ename into v\_ename from emp where empno=7788;

4 dbms\_output.put\_line('Employee Name: '||v\_ename);

5 exception

6 when no\_data\_found then

7 dbms\_output.put\_line('There is no employee');

8 end;

9 .

scott@ORCL> /

Employee Name: SCOTT

PL/SQL procedure successfully completed.

2.替代变量

&变量名、&&变量名

执行时，如果&变量名，没有赋值的话，会提示输入变量值

scott@ORCL> select \* from emp where empno=&no;

Enter value for no: 7788

old 1: select \* from emp where empno=&no

new 1: select \* from emp where empno=7788

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

\---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7788 SCOTT ANALYST 7566 19\-APR\-87 3100 20

scott@ORCL> save /u01/bk/scripts/select\_empno.sql

Created file /u01/bk/scripts/select\_empno.sql

scott@ORCL> @/u01/bk/scripts/select\_empno.sql

Enter value for no: 7788

old 1: select \* from emp where empno=&no

new 1: select \* from emp where empno=7788

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

\---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7788 SCOTT ANALYST 7566 19\-APR\-87 3100 20

set verify 的使用

scott@ORCL> set verify off \--使用set verify off来关闭替代变量使用时的提示，即执行SQL语句前不显示使用的变量值

scott@ORCL> @/u01/bk/scripts/select\_empno.sql

Enter value for no: 7788

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

\---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7788 SCOTT ANALYST 7566 19\-APR\-87 3100 20

对于字符型和日期型数据，替代变量要加单引号(' ' )，将变量括起来

select \* from emp where job='&job'

&&对输入变量的再次引用，同时会保存变量的值

scott@ORCL> set serveroutput on;

scott@ORCL> begin

2 dbms\_output.put\_line('The num is '||&&num); \--使用双&&保存了变量num的值

3 dbms\_output.put\_line('The second num is '||&num); \--因此第二次输出变量num也是

4 end;

5 /

Enter value for num: 10

The num is 10

The second num is 10

替代变量的设置

set define character \--修改缺省的替代变量符号为其他符号，但不能使用数字和空格

set define on \--启用替代变量

set define off \--关闭替代变量

scott@ORCL> set define off

scott@ORCL> @/u01/bk/scripts/select\_empno.sql

SP2\-0552: Bind variable "NO" not declared.

3. DEFINE 定义变量

define job=SALESMAN

select \* from emp where job='&job'

DEFINE 查看变量

define

define varname

ACCEPT 定义替代变量

格式：accept 变量名［prompt 正文］\[HIDE\]

4.sql plus 环境设置

set设置环境变量

如set linesize 200

show 显示环境变量

如show all

show linesize

sql plus 环境配置文件

配置文件：

$ORACLE\_HOME/sqlplus/admin/glogin.sql

安装sql plus时创建的，卸载sql plus时会删除

用户配置文件：

文件名为login.sql，位置可任意放置，启动sql plus 时所在目录下有login.sql

就会按设置进行环境配置，否则其它目录下的login.sql不理采。

为了使用的方便一般会放到oracle用户的家目录

set echo @|start 运行脚本时，是否显示脚本内容

set echo on/off

scott@ORCL> set echo on;

scott@ORCL> @/u01/bk/scripts/select\_empno.sql

scott@ORCL> select \* from emp where empno=&no

2 /

Enter value for no: 7788

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

\---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7788 SCOTT ANALYST 7566 19\-APR\-87 3100 20

scott@ORCL> set echo off;

set feedback 设置脚注

查询时返回多少行，会有提示，feedback控制提示显示

可以是ON、OFF、n自然数，默认为

set feedback on/off

set feedback 10

set heading \{ on/off\} 控制是否显示列名

set linesize 设置每行字符数

set autocommit \{ on / off \}设置是否自动提交

set long \{ 80 ｜n\} 设置查看长字符字段时的显示宽度

column设置列格式

col ename format a15

通用的SQL\*Plus环境变量设置

源自Tom大师：Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions

define \_editor=vi

set serveroutput on size 1000000

set trimspool on

set long 5000

set linesize 180

set pagesize 9999

column plan\_plus\_exp format a80

column global\_name new\_value gname

set termout off

define gname=idle

column global\_name new\_value gname

select lower(user) || '@' || substr( global\_name, 1,

decode( dot, 0, length(global\_name), dot\-1) ) global\_name

from (select global\_name, instr(global\_name,'.') dot from global\_name );

set sqlprompt '&gname> '

set termout on

5.更多参考

有关SQL请参考

[SQLPlus常用命令][SQLPlus]

[SQL 基础--> 子查询][SQL _--_]

[SQL 基础-->多表查询][SQL _--]

[SQL基础-->分组与分组函数][SQL_--]

[SQL 基础-->常用函数][SQL _-- 1]

[SQL 基础--> ROLLUP与CUBE运算符实现数据汇总][SQL _--_ ROLLUP_CUBE]

[SQL基础-->层次化查询(START BY ... CONNECT BY PRIOR)][SQL_--_START BY ... CONNECT BY PRIOR]

有关PL/SQL请参考

PL/SQL --> 语言基础

PL/SQL --> 函数

PL/SQL --> 异常处理

PL/SQL --> 存储过程

PL/SQL --> 流程控制

[SQLPlus]: http://blog.csdn.net/robinson_0612/archive/2010/04/06/5454382.aspx
[SQL _--_]: http://blog.csdn.net/robinson_0612/archive/2010/05/14/5592002.aspx
[SQL _--]: http://blog.csdn.net/robinson_0612/archive/2010/05/06/5563199.aspx
[SQL_--]: http://blog.csdn.net/robinson_0612/archive/2010/05/05/5559110.aspx
[SQL _-- 1]: http://blog.csdn.net/robinson_0612/archive/2010/04/16/5495170.aspx
[SQL _--_ ROLLUP_CUBE]: http://blog.csdn.net/robinson_0612/archive/2010/06/01/5640031.aspx
[SQL_--_START BY ... CONNECT BY PRIOR]: http://blog.csdn.net/robinson_0612/archive/2010/05/22/5616877.aspx