/*=== CS 325 - Week 13 Asynchronous Material - Part 2 there is DEFINITELY more in SQL Reading Packet 8 than we'll be touching on here! * please check it out, and try out the examples in ther yourself! last modified: 2021-11-17 ===*/ spool 325lect13-2-out.txt /*=== SQL*Plus does have various default format settings for the output of SELECT statements...! AND it also has some commands for changing those defaults. SQL, we'll see, also has some functions that can let us specify certain values (esp. strings and dates) in preferred ways. So -- say you would like "nicer" output of your query. "Nicer"? * numbers in a column all formatted to the SAME number of decimal places * columns that are wide enough to show the whole column heading * including a title...! * avoiding ugly-line-wrapping...! * displaying dates in more-accepted formats * ...and more! * these can help you to create nice versions of REPORTS ...for OUR purposes, a REPORT is a presentation of data (such as the results of a query) that is: * well-formatted * attractive * self-explanatory on its own to a reader ===*/ /*=== SQL*Plus command: / useful when DEVELOPING a report...! just asks to re-run the MOST-PREVIOUS SQL statement ^^^ (you have a query you want to be the basis of a report, you try it, you set up a SQL*Plus command to try to make it look better, you can type / to rerun the query and see if you like the result now affected by that SQL*Plus command better...!) ===*/ select * from empl; prompt ===== prompt about to use forward-slash to re-run the previous SQL statement: prompt ===== / /*=== SQL*Plus command: clear Here's the thing: you get certain SQL*Plus defaults. When you muck with those, those defaults stay changed until the end of that sqlplus session UNLESS you change them again OR unless you clear those changes clear won't put back ALL your changes, but it will put back some. THE IDEA: as GOOD STYLE and CS 325 COURSE STYLE, if you write a SQL script that CHANGES SQL*Plus defaults, you should put those BACK to those defaults at the end of that script! (so work done AFTER that script in the same sqlplus session is not adversely affected by things you did JUST for that script) clear is ONE thing you'll be using for this. Three of the SQL*Plus commands we will demo today are: break column (col for short) compute ...and THESE you can clear using: clear breaks clear columns clear computes (this works too! Thanks, S. Griffin! 8-) ) clear breaks columns computes ===*/ /*=== SQL*Plus setting: feedback "X rows selected", "X rows updated", etc.! USEFUL, but you don't usually want them in your report output! show feedback * shows the current setting of feedback set feedback 15 -- or other desired number * now feedback will be only be seen if 15 or more rows in result set feedback off * now there will be NO feedback messages! no matter how many rows are selected/updated/deleted/etc. ===*/ prompt ===== prompt showing (default) value of feedback: prompt ===== show feedback set feedback 15 prompt ===== prompt now, with feedback of 15, you will NOT see '14 rows selected' message: prompt ===== select * from empl; /*=== SQL*Plus setting: pagesize pagesize: number of lines in a "page" (that number of lines SQL*Plus will display before re-displaying column headings, etc.) show pagesize -- to see its current value; default is 14 (!!) set pagesize 30 -- and now the pagesize is 30, or whatever value you -- choose set pagesize 0 -- means you NEVER want page breaks, never want to -- repeat column headings (good for creating a flat -- file of data from a query for, say, another -- application) ===*/ prompt ===== prompt showing (default) value of pagesize: prompt ===== show pagesize set pagesize 30 prompt ===== prompt now see the difference in this query with a pagesize of 30: prompt ===== / /*=== SQL*Plus setting: linesize how many characters will be in a line before line-wrapping will occur! show linesize -- shows the current value set linesize 50 -- changes linesize to the value specified ===*/ prompt ===== prompt showing (default) value of linesize: prompt ===== show linesize set linesize 50 prompt ===== prompt here is how this query looks with a linesize of 50: prompt ===== / set linesize 80 /*=== SQL*Plus column command (abbeviation: col) THIS lets you specify more about the formatting to be used for a particular column in a query!!! (again: JUST changes the appearance, does NOT change WHAT is stored in the database!!!!!!!!!!!) column col_to_format heading desired_heading format desired_fmt ^ can be col * changes the default format for col_to_format to what is specified for EVERY select with that column! <-- until you clear it or reset it... * if the desired_heading has blanks, enclose it in EITHER single or double quotes...! Can even have multi-line desired_heading by putting | where you want the break to happen ===*/ select empl_last_name from empl; col empl_last_name heading "Employee|Last Name" prompt ===== prompt after using col command to change empl last name column heading: prompt ===== / /*=== col command - for NON-numeric columns for varchar2, char, and date data, you use A followed by the desired column width col empl_last_name heading "Employee|Last Name" format a25 ...now empl_last_name's display width will be 25 characters; ===*/ col empl_last_name heading "Employee|Last Name" format a25 prompt ===== prompt ...and now with a format of a25: prompt ===== / /*=== what if the column is ah too narrow? ===*/ col empl_last_name heading "Employee|Last Name" format a2 prompt ===== prompt ...and now with a format of a2: prompt ===== / /*=== ...the column WRAPS!!! you can ask it to truncate that display with TRU or TRUNCATED after the A format ===*/ prompt ===== prompt ...and now with a format of s2 TRU (truncated) prompt ===== col empl_last_name heading "Employee|Last Name" format a2 TRU / /*=== col command - numeric columns do NOT use an A format! INSTEAD, you give a numeric format pattern -- MANY options, here are a few: * to specify an integer right-justified in a certain width, put that many 9s format 999999999 -- format right-justified in a field 9 wide * include a decimal point if you want all values displayed to that fractional precision * want commas in big numbers? Or $? See reading packet! ===*/ prompt ===== prompt do NOT use A formats for NUMERIC columns!!!!!! prompt ===== col salary format A5 select salary from empl; col salary format 999999 prompt ===== prompt ...after changing salary column format to 999999: prompt ===== / col salary format 99999.99 prompt ===== prompt ...and after changing it to 99999.99: prompt ===== / col salary format 99999.999999 prompt ===== prompt ...and after changing it to 99999.999999: prompt ===== / col avg_salary format 99999.99 prompt ===== prompt after writing a col command using a computed column's column alias: prompt ===== select job_title, avg(salary) avg_salary from empl group by job_title; /*=== SQL*Plus command: break used with queries that have an ORDER BY clause to get "prettier" results by suppressing duplicated consecutive values break asks to suppress repeated consecutive values in the column you ask to break on break on dept_num ...and now if 3 rows in a row have dept_num 100, you see 100 just once (then blanks) you can ask for a blank line between break'd on values with skip: break on dept_num skip 1 SEE MORE in reading packet!!! ===*/ col dept_num heading 'Dept #' format A6 col empl_last_name heading "Employee" format a10 col salary heading 'Salary' format $999999.99 prompt ===== prompt have given col commands for dept_num, empl_last_name, salary: prompt ===== select dept_num, empl_last_name, salary from empl order by dept_num; break on dept_num prompt ===== prompt previous query, but now breaking on dept_num: prompt ===== / break on dept_num skip 1 prompt ===== prompt ...now breaking on dept_num, also with skip 1: prompt ===== / /*=== SQL*Plus compute command ONLY MAKES SENSE when used with break! "I want a computation done on the rows in a certain column each time a break occurs" break on dept_num skip 1 compute avg of salary on dept_num -- compute avg of salaries with that -- dept_num Amongst the functions supported are: avg min max count SEE MORE in reading packet! ===*/ compute avg of salary on dept_num prompt ===== prompt ...now also with compute avg of salary on dept_num: prompt ===== / compute count of salary on dept_num prompt ===== prompt ...now with compute count of salary on dept_num: prompt ===== / /*=== SQL command ttitle - set a TOP title on each "page" btitle - set a BOTTOM title on each "page" ttitle 'Beautiful|Three Line|Top Title' btitle "Gorgeous|Two Line BOTTOM Title" ttitle off btitle off ...to turn these off ===*/ ttitle 'Beautiful|Three Line|Top Title' prompt ===== prompt ...now with a 3-line top title: prompt ===== / btitle "Gorgeous|Two Line BOTTOM Title" prompt ===== prompt ...now also with a 2-line bottom title: prompt ===== / /*=== CLEAN UP section (put BACK the SQL*Plus defaults...!) these are lovely in a little clean-up.sql script you can then call from all your report scripts! start clean-up.sql (and I'll probably post such a script... 8-) ) ===*/ prompt ===== prompt and now clean up, resetting things changed back to SQL*Plus defaults: prompt ===== clear breaks columns computes set space 1 set feedback 6 set pagesize 14 set linesize 80 set newpage 1 set heading on ttitle off btitle off spool off