everythingOracle.bizhat.com

 

'Everything you wanted
to know about Oracle'

Training References Syntax Tell A Friend Contact Us

 

 

SQL

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19
<< Previous

Chapter # 11

Next >>


 

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 oracle user.
SQL> CONNECT oracle/learning
 

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
 

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?

     Reviews and Templates for FrontPage
     

Copyright © everythingOracle.bizhat.com 2006 All Rights Reserved.