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

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

2014-09-11 21:25 by 潇湘隐者, 1266 阅读, 0 评论, 收藏编辑

替换变量

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> 

clip_image001

如果替换变量为数字类型,那么可以直接是&+变量名称,如下所示&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

clip_image002

 

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

作者: 潇湘隐者

本文版权归作者所有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接.

=====================================



 

oracle定义变量(常量)常用:declare、define、variable

1)define、variable用于sqlplus中,在整个sqlplus连接中都生效(until exit,disc是cut down session),而declare用于pl/sql中。

2)variable(var)和define区别在于,前者用于绑定变量,后者是用于&或&&进行变量替换(使用场合,拿来当输入参数)。


define
SQL> define x='SCOTT'
SQL> define

DEFINE _DATE           = "29-9月 -13" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "myorcl11" (CHAR)
DEFINE _USER           = "SYS" (CHAR)
DEFINE _PRIVILEGE      = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1102000100" (CHAR)
DEFINE _EDITOR         = "vim" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options" (CHAR)
DEFINE _O_RELEASE      = "1102000100" (CHAR)
DEFINE X               = "SCOTT" (CHAR)
SQL> select distinct owner from dba_segments where wner='&x';
old   1: select distinct owner from dba_segments where wner='&x'
new   1: select distinct owner from dba_segments where wner='SCOTT'

OWNER
------------------------------------------------------------
SCOTT

var
SQL> var y numb
Usage: VAR[IABLE] [ [ NUMBER | CHAR | CHAR (n [CHAR|BYTE]) |
                    VARCHAR2 (n [CHAR|BYTE]) | NCHAR | NCHAR (n) |
                    NVARCHAR2 (n) | CLOB | NCLOB | BLOB | BFILE
                    REFCURSOR | BINARY_FLOAT | BINARY_DOUBLE ] ]
SQL> var y varchar2(20)
SQL> var

variable   y
datatype   VARCHAR2(20)
SQL> exec :y := 'SCOTT'

PL/SQL procedure successfully completed.

SQL> print :y

Y
-------
SCOTT

SQL> select distinct owner from dba_segments where   owner=':y'; --wrong

no rows selected

SQL> select distinct owner from dba_segments where wner=:y;

OWNER
------------------------------------------------------------
SCOTT


SQL> variable dba varchar2(30)
SQL> exec :dba := dbms_utility.make_data_block_address(4, 20);

PL/SQL procedure successfully completed.
SQL> print dba
DBA
--------------------------------
16777236

执行存储过程:(加楼上共4种方法)

法1
BEGIN
    getDeptCount;
END;
法2
EXEC getDeptCount    
法3
CALL  getDeptCount();
注意:

定义无参存储过程时,存储过程名后不能加()
在块中或是通过EXEC调用存储过程时可以省略()
通过CALL调用无参存储过程必须加上()

参数只能指定类型,不能指定长度精度范围,可以指定默认值:
varchar2(200)  ,只能用varchar2

    ...PROCEDURE add_deptno(v_deptno IN dept.deptno%TYPE,
                v_dname IN VARCHAR2,
                v_loc IN dept.loc%TYPE DEFAULT 'BEJING')...
给过程传递参数的方法:
    1,位置传递 exec add_dept(50,'SALES','BEIJING')
    2,名称传递 exec add_dept(v_dname=>'SALES',v_deptno=>50,v_loc=>'BEIJING')
    3,组合传递 exec add_dept(50,v_loc=>'BEIJING,v_dname=>'SALES')
=============================

在SQLPLUS里面,我们可以使用show all来查看系统的一些设置。


sql>  show all;


appinfo 为 OFF 并且已设置为 "SQL*Plus"

arraysize 15

autocommit OFF

autoprint OFF

autorecovery OFF

autotrace OFF

blockterminator "." (hex 2e)

btitle OFF 为下一条 SELECT 语句的前几个字符

cmdsep OFF

colsep " "

compatibility version NATIVE

concat "." (hex 2e)

copycommit 0

COPYTYPECHECK 为 ON

define "&" (hex 26)

describe DEPTH 1 LINENUM OFF INDENT ON

echo OFF

editfile "afiedt.buf"

embedded OFF

escape OFF

用于 6 或更多行的 FEEDBACK ON

flagger OFF

flush ON

heading ON

headsep "|" (hex 7c)

instance "local"

linesize 80

lno 14

loboffset 1

logsource ""

long 80

longchunksize 80

markup HTML OFF HEAD "<style type='text/css'> body {font:10pt Arial,Helvetica,sa

ns-serif; color:black; background:White;} p {font:10pt Arial,Helvetica,sans-seri

f; color:black; background:White;} table,tr,td {font:10pt Arial,Helvetica,sans-s

erif; color:Black; background:#f7f7e7; padding:0px 0px 0px 0px; margin:0px 0px 0

px 0px;} th {font:bold 10pt Arial,Helvetica,sans-serif; color:#336699; backgroun

d:#cccc99; padding:0px 0px 0px 0px;} h1 {font:16pt Arial,Helvetica,Geneva,sans-s

erif; color:#336699; background-color:White; border-bottom:1px solid #cccc99; ma

rgin-top:0pt; margin-bottom:0pt; padding:0px 0px 0px 0px;} h2 {font:bold 10pt Ar

ial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; margin-t

op:4pt; margin-bottom:0pt;} a {font:9pt Arial,Helvetica,sans-serif; color:#66330

0; background:#ffffff; margin-top:0pt; margin-bottom:0pt; vertical-align:top;}</

style><title>SQL*Plus Report</title>" BODY "" TABLE "border='1' width='90%' alig

n='center' summary='Script output'" SPOOL OFF ENTMAP ON PREFORMAT OFF

newpage 1

null ""

numformat ""

numwidth 10

pagesize 14

PAUSE 为 OFF

pno 0

recsep WRAP

recsepchar " " (hex 20)

release 1002000100

repfooter OFF  为 NULL

repheader OFF  为 NULL

serveroutput OFF

shiftinout INVISIBLE

showmode OFF

spool OFF

sqlblanklines OFF

sqlcase MIXED

sqlcode 0

sqlcontinue "> "

sqlnumber ON

sqlpluscompatibility 10.2.0

sqlprefix "#" (hex 23)

sqlprompt "SQL> "

sqlterminator ";" (hex 3b)

suffix "sql"

tab ON

termout ON

timing OFF

trimout ON

trimspool OFF

ttitle OFF 为下一条 SELECT 语句的前几个字符

underline "-" (hex 2d)

USER 为 "SYS"

verify ON

wrap : 将换至下一行


这些都是系统的一些设置变量。


实际上,ORACLE还提供了自己定义变量  包括define 变量和绑定变量


sql > define x


SQL> define x='abc'

要引用这个变量,需要使用and符号,假如是字符串,需要加上单引号。


SQL> select '&x' from dual;

原值    1: select '&x' from dual

新值    1: select 'abc' from dual


'AB

---

abc


================================

绑定变量

要想使用绑定变量,需要先定义它


variable x varchar2(10)


begin


  :x := 'hello';


end;


/


print :x


define 变量总是被sqlplus 扩展的字符串,而declared变量可以被SQL和PLSQL作为真正的绑定变量使用



参考:
sqlplus 变量 百度

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值