oracle define变量和绑定变量

最新推荐文章于 2024-05-30 11:04:43 发布

转载最新推荐文章于 2024-05-30 11:04:43 发布 · 211 阅读

文章标签：

本文介绍了Oracle中定义和使用变量的方法，包括define变量和绑定变量的语法及示例。通过实例演示了如何定义变量、赋值及在SQL查询中使用这些变量。

oracle define变量和绑定变量

ORACLE提供了自己定义变量, 包括define变量和绑定变量。

============================================================
define变量

SQL> define x='abc'
要引用这个变量，需要使用&符号，假如是字符串，需要加上单引号。

SQL> select '&x' from dual;
原值 1: select '&x' from dual
新值 1: select 'abc' from dual

'AB
---
abc

============================================================
绑定变量

var na varchar2(20)
或者
variable na varchar2(20)

赋值
exec :na:='EMP';
或者
begin
:na:='EMP';
end;
/

打印出结果
print :na

使用绑定变量
select * from tab where OBJECT_NAME=:na;

-------------------------------------------------------------------------------------------------------------

下面是绑定变量的测试记录

[ora11g@ora11g ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jan 13 08:03:14 2015

Copyright (c) 1982, 2013, Oracle. All rights reserved.

SQL>
SQL> conn scott/tiger
Connected.
SQL>
SQL>
SQL>
SQL>
SQL> select OBJECT_NAME from tab;

OBJECT_NAME
----------------------------------
PK_DEPT
DEPT
EMP
PK_EMP
BONUS
SALGRADE
IDX
T
T2
T1
IDX_T
TEST
T3
TAB

14 rows selected.

SQL>
SQL> desc tab
Name Null? Type
----------------------------------------- -------- ----------------------------
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
NAMESPACE NUMBER
EDITION_NAME VARCHAR2(30)

SQL>
SQL> var na varchar2(20)
SQL>
SQL> exec :na:='EMP';

PL/SQL procedure successfully completed.

SQL>
SQL> print :na

NA
-------------------
EMP

SQL>
SQL> set autot trace
SQL>
SQL> select * from tab where OBJECT_NAME=:na;

Execution Plan
----------------------------------------------------------
Plan hash value: 1995730731

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 72 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TAB | 1 | 72 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_NAME"=:NA)

Statistics
----------------------------------------------------------
3 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
1328 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24626891/viewspace-2124236/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24626891/viewspace-2124236/