首页 > 代码库 > ORACLE PL/SQL练习（七）
ORACLE PL/SQL练习（七）
PL/SQL变量的作用域，在当前块内部有效。
declare
v_num number(5,2) := 1.23;
begin
declare v_num char(10);
begin
v_num := 12345;
dbms_output.put_line(v_num);
end;
dbms_output.put_line(v_num);
end;
SQL> declare
2 v_num number(5,2) := 1.23;
3 begin
4 declare v_num char(10);
5 begin
6 v_num := 12345;
7 dbms_output.put_line(v_num);
8 end;
9 dbms_output.put_line(v_num);
10 end;
11 /
12345
1.23
PL/SQL procedure successfully completed.
替换变量
目的：性能提高，代码重用
SQL> select * from emp
2 where sal > 1700;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ------------------ ----- ----- ----------
7566 JONES MANAGER 7839 02-APR-81 2975 20
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7902 FORD ANALYST 7566 03-DEC-81 3000 20
6 rows selected.
SQL> select * from emp
2 where sal > 2000;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ------------------ ----- ----- ----------
7566 JONES MANAGER 7839 02-APR-81 2975 20
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7902 FORD ANALYST 7566 03-DEC-81 3000 20
6 rows selected.
--Oracle会认为以上两个SQL语句是不一样的，具有不同的SQL ID，虽然SQL语句很相似。这样，Oracle会对每个相似的SQL语句进行解析，增加了开销，降低了性能。如果代码可重用，就非常好了。
SQL> select empno,ename,sal,deptno from emp
2 where empno=&employee_num;
Enter value for employee_num: 7902
old 2: where empno=&employee_num -- set verify on会显示old和new
new 2: where empno=7902
EMPNO ENAME SAL DEPTNO
----- ---------- ----- ----------
7902 FORD 3000 20
替换变量使用场景：
-- WHERE条件
-- ORDER BY子句
-- 列表达式
-- 表名
-- 整个SELECT语句
SQL> select empno,ename,job,&coumn_name from emp
2 where &condition
3 order by &order_name;
Enter value for coumn_name: sal
old 1: select empno,ename,job,&coumn_name from emp
new 1: select empno,ename,job,sal from emp
Enter value for condition: sal > 3000
old 2: where &condition
new 2: where sal > 3000
Enter value for order_name: ename
old 3: order by &order_name
new 3: order by ename
EMPNO ENAME JOB SAL
----- ---------- --------- -----
7839 KING PRESIDENT 5000
--使用‘&‘符号，每遇到一次替换变量，就要提供一个值，对于同名的变量，要重复输入，很不方便。
SQL> select empno,ename,job,&column_name from emp
2 order by &column_name;
Enter value for column_name: sal
old 1: select empno,ename,job,&column_name from emp
new 1: select empno,ename,job,sal from emp
Enter value for column_name: sal
old 2: order by &column_name
new 2: order by sal
EMPNO ENAME JOB SAL
----- ---------- --------- -----
7369 SMITH CLERK 800
7900 JAMES CLERK 950
7876 ADAMS CLERK 1100
7521 WARD SALESMAN 1250
7654 MARTIN SALESMAN 1250
7934 MILLER CLERK 1300
7844 TURNER SALESMAN 1500
7499 ALLEN SALESMAN 1600
7782 CLARK MANAGER 2450
7698 BLAKE MANAGER 2850
7566 JONES MANAGER 2975
7788 SCOTT ANALYST 3000
7902 FORD ANALYST 3000
7839 KING PRESIDENT 5000
14 rows selected.
--使用‘&&‘,对相同的替换变量，仅需输入一次。
SQL> select empno,ename,job,&&column_name
2 from emp
3 order by &column_name;
Enter value for column_name: sal
old 1: select empno,ename,job,&&column_name
new 1: select empno,ename,job,sal
old 3: order by &column_name
new 3: order by sal
EMPNO ENAME JOB SAL
----- ---------- --------- -----
7369 SMITH CLERK 800
7900 JAMES CLERK 950
7876 ADAMS CLERK 1100
7521 WARD SALESMAN 1250
7654 MARTIN SALESMAN 1250
7934 MILLER CLERK 1300
7844 TURNER SALESMAN 1500
7499 ALLEN SALESMAN 1600
7782 CLARK MANAGER 2450
7698 BLAKE MANAGER 2850
7566 JONES MANAGER 2975
7788 SCOTT ANALYST 3000
7902 FORD ANALYST 3000
7839 KING PRESIDENT 5000
14 rows selected.
ACCEPT订制用户提示
将以下代码保存到C:\Users\John\Desktop\test1.sql文件中，在SQL*Plus中执行
代码：
accept dept_num prompt ‘请输入部门号:‘
select * from emp
where deptno=&dept_num;
SQL> start C:\Users\John\Desktop\test1.sql
请输入部门号:30
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ------------------ ----- ----- ----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7900 JAMES CLERK 7698 03-DEC-81 950 30
6 rows selected.
DEFINE和UNDEFINE
SQL> define name=ename
SQL> select &name,sal from emp;
ENAME SAL
---------- -----
SMITH 800
ALLEN 1600
WARD 1250
JONES 2975
MARTIN 1250
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
TURNER 1500
ADAMS 1100
JAMES 950
FORD 3000
MILLER 1300
14 rows selected.
变量撤销：
undefine name
本文出自 “技术小站” 博客，谢绝转载！
ORACLE PL/SQL练习（七）