Chapter 11 替代变量

Chapter 11 替代变量


select empno, ename, sal
from emp
where sal >= 1500

select empno, ename, sal
from emp
where sal >= &v_salary

SQL>
SQL> select empno, ename, sal
  2  from emp
  3  where sal >= &v_salary
  4  ;

EMPNO ENAME            SAL
----- ---------- ---------
 7499 ALLEN        1600.00
 7566 JONES        2975.00
 7698 BLAKE        2850.00
 7782 CLARK        2450.00
 7839 KING         5000.00
 7902 FORD         3000.00

6 rows selected

SQL> save D:\sql\sql replace
SQL> @D:\sql\sql
Error reading file

SQL> @D:\sql\sql.sql
Error reading file

SQL>
SQL> select empno, ename, sal
  2  from emp
  3  where sal >= &v_salary
  4  ;

EMPNO ENAME            SAL
----- ---------- ---------
 7499 ALLEN        1600.00
 7566 JONES        2975.00
 7698 BLAKE        2850.00
 7782 CLARK        2450.00
 7839 KING         5000.00
 7902 FORD         3000.00

6 rows selected

SQL> save D:\sql\sql
SQL> @D:\sql\sql
SQL> @D:\sql\sql

EMPNO ENAME            SAL
----- ---------- ---------
 7499 ALLEN        1600.00
 7566 JONES        2975.00
 7698 BLAKE        2850.00
 7782 CLARK        2450.00
 7839 KING         5000.00
 7844 TURNER       1500.00
 7902 FORD         3000.00

7 rows selected

SQL> set verify on
SQL> @D:\sql\sql
&v_salary: 1600

EMPNO ENAME            SAL
----- ---------- ---------
 7499 ALLEN        1600.00
 7566 JONES        2975.00
 7698 BLAKE        2850.00
 7782 CLARK        2450.00
 7839 KING         5000.00
 7902 FORD         3000.00

6 rows selected

SQL>

select empno, ename, sal, job, deptno
from emp
where job = &v_job;

SQL> select empno, ename, sal, job, deptno
  2  from emp
  3  where job = '&v_job';

EMPNO ENAME            SAL JOB       DEPTNO
----- ---------- --------- --------- ------
 7369 SMITH         800.00 CLERK         20
 7900 JAMES         950.00 CLERK         30
 7934 MILLER       1300.00 CLERK         10
 
 
 
select &column1, &column2
from &table_name
where &condition
order by &sorting;


SQL> define v_job
Symbol v_job is UNDEFINED

SQL> define v_job
Symbol v_job is UNDEFINED

SQL> define v_job = CLERK
SQL> define v_job
DEFINE v_job            = "CLERK" (CHAR)
---------------------------------------
Define v_job = CLERK

select ename, sal, job
from emp
where job='&v_job'
order by sal;


SQL> define
DEFINE _SQLPLUS_RELEASE = "000000000" (CHAR)
DEFINE _EDITOR          = "PLSQLDev" (CHAR)
DEFINE _O_VERSION       = "Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 " (CHAR)
DEFINE _O_RELEASE       = "000000000" (CHAR)
DEFINE v_job            = "CLERK" (CHAR)


SQL> select ename, sal,job
  2  from emp
  3  where job = Upper('&v_job')
  4  order by sal;

ENAME            SAL JOB
---------- --------- ---------
SMITH         800.00 CLERK
JAMES         950.00 CLERK
MILLER       1300.00 CLERK

SQL> L
SQL> save d:\sql\acc_job
SQL> edit d:\sql\acc_job
Unknown object: d:\sql\acc_job

SQL> @D:\sql\acc_job

ENAME            SAL JOB
---------- --------- ---------
SMITH         800.00 CLERK
JAMES         950.00 CLERK
MILLER       1300.00 CLERK

================acc_job.sql==============
accept v_job prompt 'Please Enter the Job Title:'

select ename, sal,job
from emp
where job = Upper('&v_job')
order by sal;

SQL> get d:\sql\acc_job
accept v_job prompt 'Please Enter the Job Title:'

select ename, sal,job
from emp
where job = Upper('&v_job')
order by sal;

accept pwd prompt 'Please Enter Your Password:'Hide
select ename, sal, job
from emp
where '&pwd'='MANAGER'
/