1 / 40

Chapter Twenty One Producing Readable Output

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

sade-brewer
Download Presentation

Chapter Twenty One Producing Readable Output

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Chapter Twenty One Producing Readable Output Objectives: Writing reports Page set up Page layout Queries with input Chapter 21: Report writing

  2. Producing Readable Output

  3. Summary of FormattingOutput Commands • COLUMN • TTITLE • BTITLE • BREAK • COMPUTE Chapter 21: Report writing

  4. COLUMN: COLumn name | expression CLEar FORmat format HEAding Text JUStify LE | CE | R NEWLine NEW_VALUE continue Chapter 21: Report writing

  5. COLUMN:(continued) NULl text NOPRInt PRInt WRApped TRUncated Chapter 21: Report writing

  6. 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

  7. 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

  8. 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

  9. Column Example: COLUMN id HEADING ‘Stud ID’ FORMAT 9999 TRUNCATED Chapter 21: Report writing

  10. Clear Setting COLumn Major CLEar COLumn Chapter 21: Report writing

  11. 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

  12. 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

  13. 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

  14. Check the Setting: COLUMN BREAK SHOW DEFINE Chapter 21: Report writing

  15. 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

  16. 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

  17. 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

  18. Page Setup Host: HOST myedit filename.SQL HOST list 2 Start: START filename.SQL Chapter 21: Report writing

  19. Page Setup Summary: COLUMN COLUMN colname TTITLE BTITLE BREAK COMPUTE DEFINE SHOW HEADSEP SHOW LINESIZE SHOW PAGESIZE SHOW NEWPAGE Chapter 21: Report writing

  20. 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

  21. Report Writing Example: Chapter 21: Report writing

  22. 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

  23. 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

  24. 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

  25. 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

  26. 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

  27. USING & VARIABLE: SELECT name, id, address FROM student WHERE id=&student_id; Enter value for student_id: Chapter 21: Report writing

  28. & 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

  29. & 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

  30. && 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

  31. 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

  32. 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

  33. 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

  34. 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

  35. 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

  36. 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

  37. 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

  38. 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

  39. 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

  40. 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

More Related