|
"In the End, we
will remember not the words of our enemies, but the silence of
our friends." - Martin Luther King Jr. (1929-1968) |
Read
first then play the video:
SQL014(VIDEO)-The
BREAK ON clause
The BREAK ON clause
Introduction
Your organization now wants
to get a simple report from the following report layout.
Unfortunately, they don’t have the “REPORTS builder” tool and
you have to use sql*plus in order to fulfill their user requirements.
The client asked you to
query all employee names and their departments where their salaries
are greater than $3,150.00, sorted by department.
The report layout printout:
----------------Salary-----------------
Department Employee Salary
--------------------
-------------------- -----------
ACCOUNTING KING $5,250.00
********************
-----------
sum $5,250.00
SALES ALLEN $3,758.57
BLAKE $3,464.20
********************
-----------
sum $7,222.77
------------ confidential
-------------
Your assignments are:
1. To use TTITLE, COLUMN,
COMPUTE and BREAK commands,
2. To query department
name, employee names and their salaries, and
3. To have subtotal for
each department.
Topics:
REPHEADER
REPFOOTER
COLUMN <col> HEADING
<hdr> FORMAT a20
BREAK ON <col> SKIP n
COMPUTE SUM OF <col>
ON <col>
SPOOL
HOST
CLEAR BUFFER
CLEAR COLUMNS
CLEAR COMPUTE
Connect to SQLPLUS as the
iself user.
SQL> CONNECT iself/schooling
Set the pagesize to 55 and
the linesize to 80.
SQL> SET PAGESIZE 55
SQL> SET LINESIZE 80
REPHEADER and REPFOOTER
commands
Make a report header and
footer the way it was stated in the case study.
SQL> REPHEADER
'----------------Salary-----------------'
SQL> REPFOOTER '------------ confidential -------------'
COLUMN … HEADING …
FORMAT …command
Make your column heading
and format as stated in the case study.
SQL> COLUMN dname HEADING 'Department'
FORMAT a20
SQL> COLUMN sal HEADING 'Salary' FORMAT $99,999.99
SQL> COLUMN ename HEADING 'Employee' FORMAT a20
BREAK and COMPUTE command
Build a break on the
department number and compute a sub-total for each department.
Skip one line for each control break.
SQL> BREAK ON dname SKIP 1
SQL> COMPUTE SUM OF sal ON dname
SPOOL command
Use the SPOOL command to
populate a text file with any thing that you type in the SQLPLUS
prompt or you query. You have to SPOOL off in order to be able to open
the spool file. If you don’t spool off, you will see a zero byte
written in the file and you will not be able to see any output data.
Spool it on a file called
salary.out in your c: drive.
SQL> SPOOL c:.out
Query your report based on
your case study description.
SQL> SELECT dname, ename, sal
FROM emp e, dept d
WHERE e.deptno = d.deptno AND sal > 3150
ORDER BY dname
SQL> /
Spool off, of the output.
SQL> SPOOL OFF
You can use the text editor to open your spool file.
CLEAR command
Note that all the values in
REPHEADER, REPFOOTER, BUFFER, COLUMNS, COMPUTE and etc are going to
stay the same during your open session. In order to clean them, you
should use the CLEAR command for BUFFER, COLUMNS, and COMPUTE. And
input NULL to REPHEADER and REPFOOTER.
Clear the buffer, repheader,
repfooter, and compute all of the columns from your session.
SQL> REPHEADER ''
SQL> REPFOOTER ''
SQL> CLEAR BUFFER
SQL> CLEAR COLUMNS
SQL> CLEAR COMPUTE
|
"Whether you
think that you can, or that you can't, you are usually
right." - Henry Ford (1863-1947) |
Questions:
Q: What does the BREAK ON
clause in SQLPLUS?
Q: What do the REPHEADER
and REPFOOTER commands in SQLPLUS?
Q: What does the following
commands?
COLUMN
sal HEADING 'Salary' FORMAT $99,999.99
COLUMN
ename HEADING 'Employee' FORMAT a20
REPHEADER ''
BREAK
ON dname SKIP 1
COMPUTE SUM OF sal ON dname
SPOOL
c:.out
SPOOL OFF
REPFOOTER ''
CLEAR BUFFER
CLEAR COLUMNS
CLEAR COMPUTE
Q: What does the CLEAR
command in SQLPLUS?
|