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

本文出自 “技术小站” 博客，谢绝转载！