SQL*Plus also contains a powerful set of commands for formatting output and producing reports. While not as powerful or flexible as a full commercial report writing system, reports generated by SQL*Plus are attractive and easy to program, and may satisfy a variety of reporting needs.
Consider the following SQL statement, which lists all employees in the scott.emp table and prints a projected new salary based on a 10% increase:
SELECT empno,ename,sal,sal*1.10,comm FROM scott.emp /
When this statement is executed, the following output is produced:
EMPNO ENAME SAL SAL*1.10 COMM --------- --------- --------- --------- --------- 7369 SMITH 800 880 7499 ALLEN 1600 1760 300 7521 WARD 1250 1375 500 7566 JONES 2975 3272.5 7654 MARTIN 1250 1375 1400 7698 BLAKE 2850 3135 7782 CLARK 2450 2695 7788 SCOTT 3000 3300 7839 KING 5000 5500 7844 TURNER 1500 1650 0 7876 ADAMS 1100 1210 7900 JAMES 950 1045 7902 FORD 3000 3300 7934 MILLER 1300 1430 14 rows selected
While this output is readable and contains the requested information, it is probably not a report you would want to present to senior management. Try adding a few SQL*Plus formatting commands:
set space 2 set feedback off set linesize 54 set pagesize 30 COLUMN empno heading "Employee|Number" format 9999 COLUMN ename heading "Employee|Name" format a10 COLUMN sal heading "Current|Salary" format $9999.99 COLUMN newsal heading "New|Salary" format $9999.99 COLUMN comm heading "Commission" format $9999.99 TTITLE LEFT 'The Totally Bogus Company' - RIGHT 'Page: ' FORMAT 99 SQL.PNO SKIP 2 BTITLE CENTER 'Company Confidential' SELECT empno,ename,sal,sal*1.10 newsal,comm FROM scott.emp /
Now the following report will be produced:
The Totally Bogus Company Page: 1 Employee Employee Current New Number Name Salary Salary Commission -------- ---------- --------- --------- ---------- 7369 SMITH $800.00 $880.00 7499 ALLEN $1600.00 $1760.00 $300.00 7521 WARD $1250.00 $1375.00 $500.00 7566 JONES $2975.00 $3272.50 7654 MARTIN $1250.00 $1375.00 $1400.00 7698 BLAKE $2850.00 $3135.00 7782 CLARK $2450.00 $2695.00 7788 SCOTT $3000.00 $3300.00 7839 KING $5000.00 $5500.00 7844 TURNER $1500.00 $1650.00 $.00 7876 ADAMS $1100.00 $1210.00 7900 JAMES $950.00 $1045.00 7902 FORD $3000.00 $3300.00 7934 MILLER $1300.00 $1430.00 Company Confidential