1.SQL与PL/SQL代码终止符
SQL代码以";"来表示代码的终止
PL/SQL代码以"."来表示代码的终止，以"/"表示代码的执行
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.替代变量
&变量名、&&变量名
执行时，如果&变量名，没有赋值的话，会提示输入变量值
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
set verify 的使用
scott@ORCL> set verify off --使用set verify off来关闭替代变量使用时的提示，即执行SQL语句前不显示使用的变量值
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
对于字符型和日期型数据，替代变量要加单引号( ' ' )，将变量括起来
select * from emp where job='&job'
&& 对输入变量的再次引用，同时会保存变量的值
scott@ORCL> set serveroutput on;
scott@ORCL> begin
2 dbms_output.put_line('The num is '||&&num); --使用双&&保存了变量num的值
3 dbms_output.put_line('The second num is '||&num); --因此第二次输出变量num也是
4 end;
5 /
Enter value for num: 10
The num is 10
The second num is 10
替代变量的设置
set define character --修改缺省的替代变量符号为其他符号，但不能使用数字和空格
set define on --启用替代变量
set define off --关闭替代变量
scott@ORCL> set define off
scott@ORCL> @/u01/bk/scripts/select_empno.sql
SP2-0552: Bind variable "NO" not declared.