On the move:

I'm currently moving the following to their own blogs: Unix, HTML for Blogs, Perl & Java.
The links are on the left side.

Monday, May 26, 2008

Defining a Variable

There are three ways to set up a variable: Define, Accept and New_Value.


Use Define when you know the value and you want to hard-code it in the query.

Define variable = text
Define DNo1 = 10

Select EmpNo, EName
From Emp
Where Deptno = &DNo1;

EMPNO ENAME
---------- ----------
7782 CLARK
7839 KING
7934 MILLER



Use Accept when you the query operator knows the value and you want them to type it.

Accept variable [NUMBER|CHAR|DATE [FORMAT format] [DEFAULT default] [PROMPT text|NOPROMPT] [HIDE]

The query will pause and the prompt display. The 'Number' option will assure a number is entered. 'Format 99' will make sure only two digits are entered. (I typed in the '20' when the query paused.)

Accept DNo2 Number Format 99 Prompt 'Please enter Department Number: '

Please enter Department Number: 20

Select EmpNo, EName
From Emp
Where DeptNo = &DNo2;

EMPNO ENAME
---------- ----------
7369 SMITH
7566 JONES
7788 SCOTT
7876 ADAMS
7902 FORD



Use New_Value when the value can be retrieved from a table.

Column column New_Value variable

Column DeptNo New_Value DNo3

Select Ename, Deptno
From Emp
Where Ename = 'ALLEN';

ENAME DEPTNO
---------- ----------
ALLEN 30

Select *
From Dept
Where Deptno = &DNo3;

DEPTNO DNAME LOC
---------- -------------- -------------
30 SALES CHICAGO

No comments:

Post a Comment