Việc sử dụng hai trong số các chức năng này, TO_CHAR và TO_DATE, đã được chứng minh ở Chương 9. TO_CHAR biến đổi một ngày vào một chuỗi ký tự (trong định dạng mà bạn yêu cầu). TO_CHAR có thể chuyển đổi không chỉ NGÀY nhưng cũng NUMBERs thành chuỗi ký tự. | Chapter 16 Advanced Use of Functions and Variables 299 LPAD o ROUND AVG ReturnedDate-CheckOutDate 2 0 o AS Graph from BOOKSHElF_CHECKOUT group by Name order by AVG ReturnedDate-CheckOutDate NAME Day 1 2 3 4 5 6 7 DAYSOUT . DORAH TALBOT EMILY TALBOT JED HOPKINS GERHARDT KENTGEN PAT LAVAY FRED fuller ROLAND BRANDT 13 ooooooo 14 ooooooo 18 ooooooooo 19 ooooooooo 21 oooooooooo 24 oooooooooooo 52 oooooooooooooooooooooooooo 7 rows selected. This use of LPAD is similar to what was done in Chapter 13 where the cows and bulls were shown in their family tree. Basically a lowercase o is the column here and it is padded on the left with a number of additional lowercase o s to the maximum width determined by ROUND AVG ReturnedDate-CheckOutDate 2 0 . Notice that the scale of the column heading is in increments of two. A simple change to the SQL will produce a classic graph rather than a bar chart. The literal column is changed from an o to a lowercase x and the padding on the left is done by spaces. select Name AVG ReturnedDate-CheckOutDate DaysOut LPAD x ROUND AVG ReturnedDate-CheckOutDate 2 0 AS Graph from BOOKSHeLf_CHECKOUT group by Name order by AVG ReturnedDate-CheckOutDate NAME Day 1 2 3 4 5 6 7 DAYSOUT . DORAH TALBOT EMILY TALBOT JED HOPKINS GERHARDT KENTGEN PAT LAVAY FRED fuller ROLAND BRANDT 13 x 14 x 18 x 19 x 21 x 24 x 52 x 7 rows selected. 300 Part II SQL and SQL Plus Another way to graph the data is by its distribution rather than by person. First a view is created that puts each number of days out into its decile. Thus 13 14 18 and 19 become 10 20 through 29 become 20 30 through 39 become 30 and so on I create or replace view DaysOutRange as select ROUND ReturnedDate-CheckOutDate -1 Decile COUNT Counter from BOOKSHELFCHECKOUT group by ROUND ReturnedDate-CheckOutDate -1 Next a column heading is set up similar to the previous heading although shorter and in increments of one I column Graph Heading Count justify c column Graph format a15 1 1
