28.Oracle数据库SQL开发之 SQLPlus使用——使用变量

来源：互联网发布：qt4.8.4 源码下载编辑：程序博客网时间：2024/06/05 15:32

28.Oracle数据库SQL开发之 SQLPlus使用——使用变量

欢迎转载，转载请标明出处： http://blog.csdn.net/notbaron/article/details/49722897

变量被创建之后，可以在SQL语句中代替实际值使用。被称为替换变量（substitution variable）.

替换变量有两种类型：

l 临时变量临时变量，只有在使用它的SQL语句中有效，值不能保留。

l 已定义变量会一直保留到被显式地删除、重定义或退出SQL*Plus为止。

1. 临时变量

在SQL语句中，使用字符&定义临时变量，后面跟上要定义的变量名。例如

&v_product_id就定义了一个名为v_product_id的变量。

例如，运行如下，会提示用户输入v_product_id的值。

SQL>select product_id,name,price

from products

where product_id=&v_product_id;

Enter value for v_product_id: 2

old 3: where product_id=&v_product_id

new 3: where product_id=2

PRODUCT_ID NAME

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

PRICE

----------

2 Chemistry

使用变量可以创建一些不懂SQL语句的人也可以运行的脚本。

再次执行，我们可以输入3.

SQL> /

Enter value for v_product_id: 3

old 3: where product_id=&v_product_id

new 3: where product_id=3

PRODUCT_ID NAME

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

PRICE

----------

3 Supernova

25.99

1.1 控制输出行

原行和新行的输出可以使用SETVERIFY命令控制。输入SET VERIFY OFF就会禁止显示原行和新行。例如：

SQL>set verify off

SQL>/

Enter value for v_product_id: 4

PRODUCT_ID NAME

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

PRICE

----------

4 Tank War

13.95

要重新显示这些行，输入SET VERIFY ON命令。例如：

SQL> set verify on;

SQL> /

Enter value for v_product_id: 3

old 3: where product_id=&v_product_id

new 3: where product_id=3

PRODUCT_ID NAME

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

PRICE

----------

3 Supernova

25.99

1.2 修改变量定义字符

SET DEFINE命令用于指定一个除字符&之外的字符，用来定义变量。

例如：

SQL>set define '#'

SQL>select product_id,name,price from products

2 where product_id=#v_product_id;

Enter value for v_product_id: 5

old 2: where product_id=#v_product_id

new 2: where product_id=5

PRODUCT_ID NAME

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

PRICE

----------

5 Z Files

49.99

改回命令如下：

SQL> set define '&'

1.3 使用变量替换表名和列名

变量不但可以用来替换列值，还可以用来替换表名和列名。

如下：

store@PDB1>select name,&v_col from &v_table where &v_col=&v_val;

Enter value forv_col: product_type_id

Enter value forv_table: products

Enter value forv_col: product_type_id

Enter value forv_val: 1

old 1: select name,&v_col from &v_tablewhere &v_col=&v_val

new 1: select name,product_type_id from productswhere product_type_id=1

NAME PRODUCT_TYPE_ID

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

Modern Science 1

Chemistry 1

使用&&可以避免重复输入一个变量，例如：

store@PDB1> select name,&&v_col from&v_table where &&v_col=&val;

Enter value for v_col: product_type_id

Enter value for v_table: products

Enter value for val: 1

old 1: select name,&&v_col from &v_table where&&v_col=&val

new 1: select name,product_type_id from products where product_type_id=1

NAME PRODUCT_TYPE_ID

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

Modern Science 1

Chemistry 1

使用变量为编写其他用户可以运行的脚本提供了很多灵活性。

2. 已定义变量

可以使用DEFINE命令定义变量。使用UNDEFINE命令删除变量。

ACCEPT命令，可以定义一个变量，同时为该变量指定一个数据类型。

2.1 使用DEFINE命令定义并查看变量

例如：

SQL>DEFINE v_product_id=7

查看

SQL>define v_product_id

执行如下：

store@PDB1> define v_product_id=7

store@PDB1> define v_product_id;

DEFINE V_PRODUCT_ID = "7" (CHAR)

输入DEFINE查看所有变量

store@PDB1> define v_product_id;

DEFINE V_PRODUCT_ID = "7" (CHAR)

store@PDB1> define

DEFINE _DATE = "07-NOV-15"(CHAR)

DEFINE _CONNECT_IDENTIFIER ="192.168.1.201/pdb1" (CHAR)

DEFINE _USER = "STORE"(CHAR)

DEFINE _PRIVILEGE = "" (CHAR)

DEFINE _SQLPLUS_RELEASE ="1201000200" (CHAR)

DEFINE _EDITOR = "vi"(CHAR)

DEFINE _O_VERSION = "Oracle Database 12c EnterpriseEdition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Automatic StorageManagement, OLAP, Advanced Analytics

and Real Application Testing options"(CHAR)

DEFINE _O_RELEASE = "1201000200" (CHAR)

DEFINE GNAME ="store@PDB1" (CHAR)

DEFINE V_COL ="product_type_id" (CHAR)

DEFINE V_PRODUCT_ID = "7" (CHAR)

可以再SQL语句中使用已定义的变量，如下

store@PDB1> select product_id,name,price fromproducts where product_id=&v_product_id;

old 1: select product_id,name,price from products whereproduct_id=&v_product_id

new 1: select product_id,name,price from products where product_id=7

PRODUCT_ID NAME PRICE

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

7 Space Force 9 13.49

2.2 使用ACCEPT命令定义并设置变量

ACCEPT命令等待用户为变量输入一个值。

简化语法如下：

ACCEPT variable_name [type] [FORMAT format][PROMPT prompt] [HIDE]

store@PDB1> accept v_customer_id NUMBERFORMAT 99 PROMPT 'Customer id: '

Customer id: 5

store@PDB1> accept v_date DATE FORMAT'DD-MON-YYYY' PROMPT 'Date: '

Date: 12-DEC-2006

store@PDB1> ACCEPT v_password CHARPROMPT 'Password: ' HIDE

Password:

其中HIDE表示隐藏输出。

然后输入DEFINE可以查看刚才输入的变量

store@PDB1> DEFINE

DEFINE _DATE = "07-NOV-15"(CHAR)

DEFINE _CONNECT_IDENTIFIER ="192.168.1.201/pdb1" (CHAR)

DEFINE _USER = "STORE"(CHAR)

DEFINE _PRIVILEGE = "" (CHAR)

DEFINE _SQLPLUS_RELEASE ="1201000200" (CHAR)

DEFINE _EDITOR = "vi"(CHAR)

DEFINE _O_VERSION = "Oracle Database 12c EnterpriseEdition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Automatic StorageManagement, OLAP, Advanced Analytics

and Real Application Testing options"(CHAR)

DEFINE _O_RELEASE = "1201000200" (CHAR)

DEFINE GNAME ="store@PDB1" (CHAR)

DEFINE V_COL ="product_type_id" (CHAR)

DEFINE V_PRODUCT_ID = "7" (CHAR)

DEFINE V_CUSTOMER_ID = 5 (NUMBER)

DEFINE V_DATE ="12-DEC-2006" (CHAR)

DEFINE V_PASSWORD = "huawei" (CHAR)

2.3 使用UNDEFINE 命令删除变量

store@PDB1> UNDEFINE v_customer_id

store@PDB1> UNDEFINE v_date

store@PDB1> UNDEFINE v_password

store@PDB1> UNDEFINE v_product_id

即使没有使用UNDEFINE命令显示删除变量，退出SQL*PLUS时，所有变量也全部会被删除。

0 0