Replace variables with SQL * Plus Environment Settings

1. SQL and PL/SQL code Terminator

SQL code uses ";" to indicate code termination

PL/SQL code uses "." To indicate code termination, and uses "/" to indicate code execution.

Scott @ ORCL> select * from emp where empno = 7788;

 

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7788 scott analyst 7566 19-APR-87 3100 20

 

Scott @ ORCL> declare v_ename varchar2 (10 );

2 begin

3 select ename into v_ename from emp where empno = 7788;

4 dbms_output.put_line ('employee Name: '| v_ename );

5 exception

6 when no_data_found then

7 dbms_output.put_line ('there is no employee ');

8 end;

9.

Scott @ ORCL>/

Employee Name: SCOTT

 

PL/SQL procedure successfully completed.

2. Substitution Variables

& Variable name, & variable name

During execution, if & variable name is not assigned a value, the system will prompt you to enter the variable value.

 

Scott @ ORCL> select * from emp where empno = & no;

Enter value for no: 7788

Old 1: select * from emp where empno = & no

New 1: select * from emp where empno = 7788

 

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7788 scott analyst 7566 19-APR-87 3100 20

 

Scott @ ORCL> save/u01/bk/scripts/select_empno. SQL

Created file/u01/bk/scripts/select_empno. SQL

Scott @ ORCL> @/u01/bk/scripts/select_empno. SQL

Enter value for no: 7788

Old 1: select * from emp where empno = & no

New 1: select * from emp where empno = 7788

 

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7788 scott analyst 7566 19-APR-87 3100 20

 

Use of set verify

Scott @ ORCL> set verify off -- use set verify off to disable the prompt to replace the variable usage. That is, the variable value is not displayed before the SQL statement is executed.

Scott @ ORCL> @/u01/bk/scripts/select_empno. SQL

Enter value for no: 7788

 

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7788 scott analyst 7566 19-APR-87 3100 20

 

For string and date data, replace variables with single quotation marks ('') to enclose the variables.

Select * from emp where job = '& job'

 

& Rereference the input variable and save the value of the Variable

Scott @ ORCL> set serveroutput on;

Scott @ ORCL> begin

2 dbms_output.put_line ('the num is '| & num); -- use double & to save The value of The variable num

3 dbms_output.put_line ('The second num is '| & num); -- therefore, The second output variable num is

4 end;

5/

Enter value for num: 10

The num is 10

The second num is 10

Substitution variable settings

Set define character -- modify the default substitution variable symbol to another symbol, but cannot use numbers or spaces

Set define on -- enable substitution Variables

Set define off -- disable substitution Variables

Scott @ ORCL> set define off

Scott @ ORCL> @/u01/bk/scripts/select_empno. SQL

SP2-0552: Bind variable "NO" not declared.

1 2 Next Page