400 likes | 498 Views
Chapter Twenty One Producing Readable Output. Objectives: Writing reports Page set up Page layout Queries with input. Producing Readable Output. Summary of Formatting Output Commands. COLUMN TTITLE BTITLE BREAK COMPUTE. COLUMN:. COLumn name | expression CLE ar FOR mat format
E N D
Chapter Twenty One Producing Readable Output Objectives: Writing reports Page set up Page layout Queries with input Chapter 21: Report writing
Summary of FormattingOutput Commands • COLUMN • TTITLE • BTITLE • BREAK • COMPUTE Chapter 21: Report writing
COLUMN: COLumn name | expression CLEar FORmat format HEAding Text JUStify LE | CE | R NEWLine NEW_VALUE continue Chapter 21: Report writing
COLUMN:(continued) NULl text NOPRInt PRInt WRApped TRUncated Chapter 21: Report writing
Formatting Option: COLUMN Name FORMAT A10 COLUMN id FORMAT 9999 COLUMN cr FORMAT 9 COLUMN Gpa FORMAT 9.99 FORMAT: $9999 9 0999 990 9,999.99 DATE A10 L999 Chapter 21: Report writing
Formatting Option: COLUMN Name FORMAT A10 COLUMN id FORMAT 9999 COLUMN cr FORMAT 9 COLUMN GPA FORMAT 9.99 SELECT Name, id, cr, GPA FROM student WHERE Major=‘COSC’ ORDER BY GPA; Chapter 21: Report writing
Heading Option: COLUMN Name HEADING ‘Student’’s Name’ COLUMN id HEADING ‘Student’’s ID’ COLUMN cr*grade HEADING ‘Score’ COLUMN name HEADING ‘Student’’s Name’ JUSTIFY LE COLumn Name HEAding ‘Name’ FORmat A10 COLumn Major FORmat A7 NULL ‘NO Major’ COL address HEADING ‘Addr’ FORMAT A7 TRUNC Chapter 21: Report writing
Column Example: COLUMN id HEADING ‘Stud ID’ FORMAT 9999 TRUNCATED Chapter 21: Report writing
Clear Setting COLumn Major CLEar COLumn Chapter 21: Report writing
Break on BREAK ON id SKIP 2 BREAK ON id DUPLICATE SKIP 2 BREAK ON id NODUPLICATE BREAK ON Major ON ID BREAK ON REPORT PAGE ON dept PAGE Chapter 21: Report writing
Break on Format: BREAK ON column BREAK ON row BREAK ON page BREAK ON report SKIP n SKIP page CLEAR BREAK Chapter 21: Report writing
Compute COMPUTE SUM OF gpa ON id COMPUTE SUM LABEL ‘Total’ OF gpa ON id COMPUTE AVG OF a, b, c, d ON e COMPUTE SUM AVG COUNT OF gpa ON id AVG COUNT MAX MIN STD VAR Chapter 21: Report writing
Check the Setting: COLUMN BREAK SHOW DEFINE Chapter 21: Report writing
Page Setup Line size: SET LINESIZE 75 Page size: SET PAGESIZE 60 Blank lines at the top: SET NEWPAGE 3 Writing into a file: SPOOL filename.lis SPOOL Gstudent.lis SPOOL OFF Chapter 21: Report writing
Page Setup Display: SET TERMOUT ON SPOOL filename.lis SET TERMOUT OFF Heading separator: SET HEADSEP char SET HEADSEP ! Underlining: SET UNDERLINE OFF Chapter 21: Report writing
Page Setup Title: TTITLE text | variable TTITLE ‘Information ‘ BTITLE ‘Confidential‘ TTITLE Left ‘Page: ’ SQL.PNO – Right ‘Date: ‘ SYSDATE Skip 1 – Center ‘ My Report ‘ Skip 3; SQL.LNO SQL.PNO SQL.RELEASE SQL.SQLCODE SQL.USER Chapter 21: Report writing
Page Setup Host: HOST myedit filename.SQL HOST list 2 Start: START filename.SQL Chapter 21: Report writing
Page Setup Summary: COLUMN COLUMN colname TTITLE BTITLE BREAK COMPUTE DEFINE SHOW HEADSEP SHOW LINESIZE SHOW PAGESIZE SHOW NEWPAGE Chapter 21: Report writing
Set Commands COLSEP [ |Text] FEEDBACK [OFF| ON| n] HEADING [OFF| ON] LINESIZE [n] PAGESIZE [n] PAUSE [OFF| ON| Text] TERMOUT [OFF| ON] Chapter 21: Report writing
Report Writing Example: Chapter 21: Report writing
Report Writing Example: CLEAR BREAK CLEAR COMPUTE COLUMN dept HEADING ‘Department’ FORMAT A12 COLUMN c_num HEADING ‘Course|No.’ FORMAT 999 COLUMN title HEADING ‘Course|Name’ FORMAT A13 COLUMN cr HEADING ‘Credit’ FORMAT 99 COLUMN D_start HEADING ‘Date|Started’ FORMAT DATE COLumn m_enrol HEAding ‘Max|Enrollment’ FORMAT 999 Chapter 21: Report writing
Report Writing Example: COL SYSDATE NEW_VALUE xdate NOPRINT FORMAT A1 TRUNC BREAK ON dept SKIP 2 COMPUTE SUM OF cr ON dept --COMPUTE SUM OF cr ON dept REPORT TTITLE LEFT ‘Your Name:’SKIP 2 - RIGHT xdate SKIP 1 - CENTER ‘List of Cources’ SKIP 1 Chapter 21: Report writing
Report Writing Example: BTITLE LEFT ‘Report1.sql’ - RIGHT ‘Page No: ’ SQL.PNO SELECT dept, c_num, title, cr, d_start.m_enroll FROM department WHERE dept IN (‘COSC’, ‘MATH’) ORDER BY dept; Chapter 21: Report writing
Login File Login.sql prompt login.sql loaded set feedback off set sqlprompt ‘ ‘ set sqlnumber off set numwidth 5 set pagesize 24 set linesize 79 Chapter 21: Report writing
Substitution Variables • Substitution variables to temporarily store values - & - & & - DEFINE and ACCEPT • Pass variable values between SQL statements • Dynamically alter headers and footers Chapter 21: Report writing
USING & VARIABLE: SELECT name, id, address FROM student WHERE id=&student_id; Enter value for student_id: Chapter 21: Report writing
& Substitution Variables • User provides the input to the query SELECT Name, GPA, ID FROM Student WHERE UPPER(Major) = &Major_Input; Enter Value for Major_Input: ‘COSC’ Chapter 21: Report writing
& Substitution Variables SELECT Name, ID, &Column_name FROM Student WHERE &condition ORDER BY ℴ Enter Value for Column_name: Enter Value for Condition: Enter Value for Order: Chapter 21: Report writing
&& Substitution Variables Use a value of a variable more than one time SELECT Name, ID, &&Column_name FROM Student WHERE &Column_name=&NewName; Enter value for Column_name: Chapter 21: Report writing
SET VERIFY ON: SQL> SET VERIFY ON SQL> SELECT name, id, address 2 FROM student 3 WHERE id=&student_id; Enter value for student_id: 1111 Old 3: where id=&student_id; New 3: where id= 1111 Chapter 21: Report writing
Character and Date Values With Substitution Variables: SELECT name, id, b_date FROM student WHERE b_date = '&new_birthdate'; Enter value for new_birthdate: Chapter 21: Report writing
Specifying Column Names, Expressions, and Text at Run Time • WHERE condition • ORDER BY clause • COLUMN expression • TABLE name • Entire SELECT statement Chapter 21: Report writing
Example SELECT name, id, rank, &column_name FROM faculty WHERE &condition ORDER BY &order_column; Enter value for column_name: Enter value for condition: Enter value for order_column: Chapter 21: Report writing
Defining User Variables • You can pre-define variables using: • DEFINE: Create a user variable of CHAR data type • ACCEPT: Read user input and store it in a variable Chapter 21: Report writing
Defining User Variables • If you need to predefine a variable that includes spaces, you need to enclose the value within single quotation marks, when using the DEFINE command. • DEFINE variable=CAR • DEFINE variable • DEFINE • ACCEPT Chapter 21: Report writing
ACCEPT Command: • Create a customized prompt when accepting user input • Explicitly defines a NUMBER or DATE datatype variable • Hides user input for security Chapter 21: Report writing
ACCEPT Command: ACCEPT variable [ datatype] [FORMAT format][prompt text ] [HIDE] ACCEPT field PROMPT 'Input your Major:‘ SELECT * FROM student WHERE major=UPPER ('&field') Input your major: Chapter 21: Report writing
DEFINE and UNDEFINE • Use the UNDEFINE command to clear • Exit SQL*plus • To define variables for every session, modify your login.sql file Chapter 21: Report writing
Create a Variable DEFINE dept_name=COSC DEFINE dept_name SELECT * FROM faculty WHERE dept=UPPER ('&dept_name'); UNDEFINE dept_name Chapter 21: Report writing