ORACLE PL/SQL Practice (vii)

The scope of the PL/SQL variable, which is valid within the current block.

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);

Ten end;

11/

12345

1.23

PL/SQL procedure successfully completed.

Substitution variables

Purpose: Improved performance, code reuse

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 will consider the above two SQL statements to be different, with a distinct SQL ID, although the SQL statements are similar. In this way, Oracle parses each similar SQL statement, adding overhead and reducing performance. If the code is reusable, it's great.

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 will show old and new

New 2:where empno=7902

EMPNO ename SAL DEPTNO

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

7902 FORD 3000 20

Replace variable usage scenarios:

--Where condition

--ORDER BY clause

--Column expression

--Table name

--The entire SELECT statement

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

--Using the ' & ' symbol, each time you encounter a replacement variable, provide a value, for a variable of the same name, it is inconvenient to repeat the input.

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

Rows selected.

--Using ' && ', for the same substitution variable, enter only once.

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

Rows selected.

Accept Custom User Tips

Save the following code in the C:\Users\John\Desktop\test1.sql file and execute it in the Sql*plus

Code:

Accept Dept_num Prompt ' Please enter the department number: '

SELECT * FROM emp

where deptno=&dept_num;

sql> Start C:\Users\John\Desktop\test1.sql

Please enter department number: 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 and 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

Rows selected.

Variable revocation:

Undefine Name

This article from "Technology Station" blog, declined reprint!

ORACLE PL/SQL Practice (vii)