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
30
使用变量可以创建一些不懂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时,所有变量也全部会被删除。