SQL Fundamentals:替代变量（&，&&)以及DEFINE,UNDEFINE,ACCEPT指令

首页 > 代码库 > SQL Fundamentals:替代变量（&，&&)以及DEFINE,UNDEFINE,ACCEPT指令

SQL Fundamentals:替代变量（&，&&)以及DEFINE,UNDEFINE,ACCEPT指令

2024-11-12 03:38:02 204人阅读

替代变量

利用替代变量可以实现数据操作的交互性。替代变量的操作类似于键盘输入操作.

所谓的替代变量，指的就是在进行查询或更新操作时，某些数据是由用户所输入的，而这些数据前可以使用“&”标记。

替代变量的使用

1、使用“&”定义替代变量

在WHERE子句中使用替代变量

1.直接使用替代变量：&inputsal，这个时候系统会弹出一个框，让你输入替代变量的值

SELECT ename,job,sal,hiredate FROM emp WHERE sal>&inputsal ;

2.查询一个雇员编号、姓名、职位、雇佣日期、基本工资，查询的雇员姓名由用户输入

SELECT empno,ename,job,hiredate,sal FROM emp

WHERE ename=&inputename ;

这里当用户输入SMITH时会出现错误，因为字符串需要单引号，那么我们改进一下.

SELECT empno,ename,job,hiredate,sal FROM emp

WHERE ename=‘&inputename‘;

这里还有一个问题，用户输入的时候不会考虑大小写，那么我们再改进以下

SELECT empno,ename,job,hiredate,sal FROM emp

WHERE UPPER(ename)=UPPER(‘&inputename‘);

3.根据雇员姓名的关键字（由用户输入）查询雇员编号、姓名、职位、雇佣日期、基本工资

SELECT empno,ename,job,hiredate,sal FROM emp

WHERE ename LIKE ‘%&inputkeyword%‘ ;

4.由用户输入雇佣日期，要求查询出所有早于此雇佣日期的雇员编号、姓名、职位、雇佣日期、基本工资

用户只能输入字符串，所以需要使用TO_DATE进行转换.

Select ename,empno,job,hiredate,sal FROM emp

WHERE hiredate<TO_DATE(‘&inputhiredate‘,‘yyyy-mm-dd‘);

输入查询雇员的职位及工资（高于输入工资）信息，而后显示雇员编号、姓名、职位、雇佣日期、基本工资

SELECT empno,ename,job,hiredate,sal FROM emp

WHERE job=UPPER(‘&inputjob‘)

AND sal>&inputsal ;

在SELECT子句中使用替代变量

SELECT &inputColumnName

FROM emp

WHERE deptno=&inputDeptno ;

在FROM子句中使用替代变量

SELECT *

FROM &inputTableName ;

在ORDER BY子句中使用替代变量

SELECT empno,ename,job,hiredate,sal

FROM emp

WHERE deptno=20

ORDER BY &inputOrderByColumn DESC ;

在GROUP BY子句中使用替代变量

SELECT &inputGroupByColumn ,SUM(sal) ,AVG(sal)

FROM emp e

GROUP BY &inputGroupByColumn ;

2、使用“&&”定义替代变量

1、使用‘&&’定义替代变量

SELECT &&inputGroupByColumn ,SUM(sal) ,AVG(sal)

FROM emp e

GROUP BY &inputGroupByColumn ;

2、取消定义的替代变量

UNDEFINE inputGroupByColumn

3.使用DEFINE定义替代变量

在Oracle中除了可以使用“&&”定义替代变量之外，还可以使用DEFINE命令来定义替代变量，用户可以利用DEFINE命令创建一个字符型的替代变量，而且此种方式定义的替代变量会一直保存到一个SESSION的操作结束或者是遇见UNDEFINE清除变量。

DEFINE命令格式

DEFINE 替代变量名称=值 ;

范例： DEFINE inputdname=‘ACCOUNTING‘

查询替代变量内容： DEFINE inputdname ;

使用DEFINE定义的替代变量： SELECT * FROM dept WHERE dname=‘&inputdname‘ ;

UNDEFINE命令格式

清除inputdname替代变量内容： UNDEFINE inputdname ;

如果不需要任何替代变量的定义，可以输入SET DEFINE OFF指令.

ACCEPT指令

可以指定替代变量的提示信息.

如果要使用ACCEPT指令，一定要结合脚本文件完成.

ACCEPT命令格式：

ACCEPT 替代变量名称 [数据类型] [FORMAT 格式] [PROMPT ‘提示信息‘] [HIDE]

ACCEPT语法中各个参数的作用：

替代变量名称	存储值的变量名称，如果该变量不存在，则由SQL*Plus创建该变量，但是在定义此替代变量名称前不能加上“&”；
数据类型	可以是NUMBER、VARCHAR或DATE型数据；
FORMAT 格式	指定格式化模型，例如A10或9.99；
PROMPT 提示信息	用户输入替代变量时的提示信息；
HIDE	隐藏输入内容，例如在输入密码时没有显示。

定义脚本文件使用ACCEPT指令 ——@test.sql

ACCEPT inputEname PROMPT ‘请输入要查询信息的雇员姓名：‘

SELECT empno,ename,job,hiredate,sal FROM emp

WHERE ename=UPPER(‘&inputename‘) ;

SQL> conn scott/tiger

Connected.

SQL> @wendytest.sql

请输入要查询信息的雇员姓名：SMITH

old 2: WHERE ename=UPPER(‘&inputename‘)

new 2: WHERE ename=UPPER(‘SMITH‘)

EMPNO ENAME JOB HIREDATE SAL

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

7369 SMITH CLERK 17-DEC-80 800

使用ACCEPT定义替代变量 —— @test.sql

ACCEPT inputGroupByColumn PROMPT ‘请输入要分组的字段：‘

SELECT &&inputGroupByColumn ,SUM(sal) ,AVG(sal)

FROM emp e

GROUP BY &inputGroupByColumn ;

SQL> @wendytest.sql

请输入要分组的字段：deptno

old 1: SELECT &&inputGroupByColumn ,SUM(sal) ,AVG(sal)

new 1: SELECT deptno ,SUM(sal) ,AVG(sal)

old 3: GROUP BY &inputGroupByColumn

new 3: GROUP BY deptno

DEPTNO SUM(SAL) AVG(SAL)

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

30 7800 1560

20 10875 2175

10 8750 2916.66667

使用HIDE选项：隐藏输入内容 —— @test.sql

ACCEPT inputGroupByColumn PROMPT ‘请输入要分组的字段：‘ HIDE

SELECT &&inputGroupByColumn ,SUM(sal) ,AVG(sal)

FROM emp e

GROUP BY &inputGroupByColumn ;

SQL> @wendytest.sql

请输入要分组的字段：***

old 1: SELECT &&inputGroupByColumn ,SUM(sal) ,AVG(sal)

new 1: SELECT job ,SUM(sal) ,AVG(sal)

old 3: GROUP BY &inputGroupByColumn

new 3: GROUP BY job

JOB SUM(SAL) AVG(SAL)

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

CLERK 4150 1037.5

SALESMAN 4000 1333.33333

PRESIDENT 5000 5000

MANAGER 8275 2758.33333

ANALYST 6000 3000

使用FORMAT：限定输入的数据长度 —— @test.sql

ACCEPT inputGroupByColumn PROMPT ‘请输入要分组的字段：‘ FORMAT A10

SELECT &&inputGroupByColumn ,SUM(sal) ,AVG(sal)

FROM emp e

GROUP BY &inputGroupByColumn ;

SQL> @wendytest.sql

请输入要分组的字段：ddddddddddddddd

SP2-0598: "ddddddddddddddd" does not match input format "A10"

请输入要分组的字段：

使用FORMAT：格式化输入 —— @ test.sql

ACCEPT inputDate DATE FORMAT ‘YYYY-MM-DD‘ PROMPT ‘请输入要查询的雇佣日期：‘

SELECT empno , ename , job , hiredate

FROM emp e

WHERE hiredate=TO_DATE(‘&inputDate‘,‘YYYY-MM-DD‘) ;

SQL> @wendytest.sql

请输入要查询的雇佣日期：99-33-33

SP2-0685: The date "99-33-33" is invalid or format mismatched "YYYY-MM-DD"

请输入要查询的雇佣日期：1987-09-14

old 3: WHERE hiredate=TO_DATE(‘&inputDate‘,‘YYYY-MM-DD‘)

new 3: WHERE hiredate=TO_DATE(‘1987-09-14‘,‘YYYY-MM-DD‘)

no rows selected

Substitution Variables（替代变量）

在WHERE子句中，通过使用替代变量来代替精确的值，就可以用不同的值运行同一个查询.

Use substitution variables to:

Temporarily store values with single-ampersand(&) and double-ampersand(&&) substitution

Use substitution variables to supplement the following:

WHERE conditions
ORDER BY clauses
Column expressions
Table names
Entire SELECT statements

Using the single-Ampersand Substitution Variable（&）

When a SQL query has to be executed more than once for the different set of inputs, substitution variables can be used. Substitution variables can be used to prompt for user inputs before the query execution. They are widely used in query based report generation which takes data range from the users as input for the conditional filtering and data display. Substitution variables are prefixed by a single-ampersand (&) symbol to temporarily store values. For example,

SELECT EMPLOYEE_ID, LAST_NAME, SALARY
FROM employees
WHERE LAST_NAME =‘&last_name’
OR EMPLOYEE_ID = ‘&EMPNO’;

When the above SELECT query is executed, oracle identifies the ‘&‘ as substitution variable. It prompts user to enter value for ‘last_name‘ and ‘EMPNO‘ as below.

Enter value for last_name:
Enter value for empno:

Once the user provides inputs to both the variables, values are substituted, query is verified and executed.

Points to be noted -

If the variable is meant to substitute a character or date value, the literal needs to be enclosed in single quotes. A useful technique is to enclose the ampersand substitution variable in single quotes when dealing with character and date values. 字符串值和日期值需要用单引号引起来，所以要将替代变量用单引号引起来.
Both SQL Developer and SQL* Plus support the substitution variables and the DEFINE/UNDEFINE commands. Though SQL Developer or SQL* Plus does not support validation checks (except for data type) on user input.
You can use the substitution variables not only in the WHERE clause of a SQL statement, but also as substitution for column names, expressions, or text.
也可以将替代变量用作列名、表达式、文本

如果不输入替代变量的值，将会得到一个错误

替代变量可用于SELECT语句的任何地方，除了在命令提示符后作为第一个单词输入.

Using the Double-Ampersand Substitution Variable（&&）

&&用户只需要输入一次就可以了，&用户需要每次输入

如果想重用变量值而不用每次提示用户进行输入，可以使用两个&&的替代变量.

SQL> SELECT ename,&&column_name

2 from emp

3 order by &&column_name;

Enter value for column_name: job

old 1: SELECT ename,&&column_name

new 1: SELECT ename,job

old 3: order by &&column_name

new 3: order by job

When the same substitution variable is used at more than one place, then to avoid re-entering the same data again, we use double ampersand substitution. In such cases, value of the substitution variable, once entered, would be substituted at all instants of usage.

SELECT first_name, HIRE_DATE, SEPARATION_DATE
FROM employees
WHERE HIRE_DATE LIKE ‘%&DT%‘ AND SEPARATION_DATE ‘%&&DT%‘

Note that the same value of &DT is substituted twice in the above query. So, its value once given by the user will be substituted at two places.

SQL Developer stores the value that is supplied by using the DEFINE command.

SQL Developer存储使用DEFINE命令所提供的值.

It uses it again whenever you reference the variable name. After a user variable is in place, you need to use the UNDEFINE command to delete it:

UNDEFINE colum_name

The DEFINE and VERIFY Commands

Use the DEFINE command to create and assign a value to a variable.

定义一个替代变量

Use the UNDEFINE command to remove a variable.

删除一个替代变量

SQL> DEFINE emp_sal=1000

SQL> select ename,job,sal

2 from emp

3 where sal>&emp_sal;

old 3: where sal>&emp_sal

new 3: where sal>1000

ENAME JOB SAL

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

WARD SALESMAN 1250

JONES MANAGER 2975

11 rows selected.

SQL> UNDEFINE emp_sal

SQL>

Setting the definition of variables in a session is set by DEFINE feature of SQL* Plus. The variables can be defined in the session, so as to avoid halt during query execution. Oracle reads the same variable whenever encountered in an SQL query. It is in ON state by default. With the help of DEFINE clause, one can declare a variable in command line before query execution as DEFINE variable=value;.

Use the VERIFY command to toggle切换 the display of the substitution variable, both before and after SQL Developer replaces substitution variables with values.

要确认SQL语句的变化，使用VERIFY命令

SET VERIFY ON/OFF

Verify command verifies the above substitution showing as OLD and NEW statement. It is OFF by default and can be set to ON using SET command.

SQL> SET DEFINE ON
SQL> SET VERIFY ON
SQL> DEFINE NAME = MARTIN‘
SQL> SELECT first_name, SALARY
FROM employees
WHERE first_name = ‘&NAME‘;
OLD 1: select first_name, sal from employee where first_name = ‘&first_name‘
new 1: select first_name, sal from employee where first_name = ‘MARTIN‘

first_name     SALARY
-------        -------
MARTIN         5000

SQL Fundamentals:替代变量（&，&&)以及DEFINE,UNDEFINE,ACCEPT指令

声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉：投诉/举报工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。

联系
我们

首页 > 代码库 > SQL Fundamentals:替代变量（&，&&)以及DEFINE,UNDEFINE,ACCEPT指令

SQL Fundamentals:替代变量（&，&&)以及DEFINE,UNDEFINE,ACCEPT指令

看完仍有疑问？有类似问题直接问程序猿