Chapter 9 SQLPLUS REPORTS.ppt

Chapter 9 : SQL*PLUS REPORTS A SQL*Plus PROGRAM COMMAND File Interactive commands can be used to specify report headings, report footers, report titles, page numbers, and other common report features that managers tend to request. Unfortunately, if you exit SQL*Plus, all of the information about a reports features is lost unless you save your commands in a file. For this reason, we will also focus on creating files that will store SQL*Plus commands. Well refer to this type of file as a SQL*Plus program command file, or simply a SQL program. The filename extension used for these files is .sql. Example Following program gives a sample SQL*Plus program command file. This SQL program will produce the report shown in Figures 9.1a and 9.1b. All of the data for the report is selected from the assignment table of the Company database. The SQL program has numerous commands. Example REM Program: ch9-1.sql REM Programmer: dbock; 3-20-2003 REM Description: A program to list employee work history REM on projects. ? TTITLE Project Information BTITLE SKIP 1 CENTER Not for external dissemination. REPHEADER Project Report #1 –- prepared by D. Bock SKIP 2 REPFOOTER SKIP 3 -- Last Page of Report -- ? SET LINESIZE 55 SET PAGESIZE 24 SET NEWPAGE 1 ? Example Cont’d COLUMN Emp. Soc. Sec. # FORMAT A16 COLUMN Hours Worked FORMAT 999.99 SELECT work_emp_ssn Emp. Soc. Sec. #, work_pro_number Project #, work_hours Hours Worked FROM assignment ORDER BY work_emp_ssn, work_pro_number; The report produced is shown next. Sun Apr 14 page 1 Project Information ? Project Report #1 - prepared by D. Bock ? Emp. Soc. Sec. # Project # Hours Worked 999111111 1 31.40 999111111 2 8.50 999222222 10 34.50 999222222 30 5.10 999333333 3 42.10 999444444 1 999444444 2 12.20 999444444