Oracle SQL Jumpstart with Examples- P5

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:50

Thêm vào BST

Báo xấu

81
lượt xem 11
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Oracle SQL Jumpstart with Examples- P5: Review "As a consultant with more than 12 years of experience working with Oracle databases on a daily basis, reviewing this book was a unique and enjoyable experience. The SQL language is without doubt one of the most critical database skills and it is best learned by example. This book addresses that crucial need. Mr. Powell does an excellent job of clarifying the concepts by using meaningful and easy to understand examples. Frankly, I have not come across any other book on SQL that is as good a compilation of SQL concepts in a single...

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Oracle SQL Jumpstart with Examples- P5

170 8.4 Using iSQL*Plus Figure 8.27 Your Customized Report Running Web Page. cuted within the browser HTML page, both of which are stored on the server running the HTTP server process. Figure 8.28 The CDREPORT Script Was Run and Displayed. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
8.4 Using iSQL*Plus 171 This example placed the username, password, and Oracle database net- work name (SID) in the source code. This is not necessarily a good idea in a commercial environment from a security perspective. Anyone can view the source code of an HTML document and retrieve information. It would be better to modify the HTML in your document so that the user is required to enter a username and password. Additionally, secure socket layers (SSL) can be used to encrypt data traveling between a Web browser and the server. 8.4.2 iSQL*Plus versus SQL*Plus The main features of iSQL*Plus are similar to the features of SQL*Plus or SQL*Plus Worksheet: Enter SQL commands in a box and click the Execute button to dis- play the results. Results can be displayed below the box or in a new browser window. Adjust environment settings by clicking the Preferences button, using a series of radio buttons and boxes to modify settings such as HEAD- ING, RECSEP, and so on. Use variables just like SQL*Plus, except you cannot use the ACCEPT or PROMPT commands to prompt for values. iSQL*Plus displays its own prompt. Note: iSQL*Plus allows prompts for input values. Review and retrieve previous SQL commands by clicking the History button, much like SQL*Plus Worksheet. 8.4.3 Troubleshooting iSQL*Plus If there are problems running iSQL*Plus, conﬁguration settings and other things can be checked on the server. Check that the port number is the default 7778 value in the ﬁle HTTPD.CONF in the $ORACLE_HOME/Apache/Apache/conf directory. Chapter 8 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
172 8.4 Using iSQL*Plus The port number should also be in the SETUPINFO.TXT ﬁle in the directory $ORACLE_HOME/Apache/Apache. This ﬁle should con- tain entries such as the following: http://:7778 http://:4443 Note: Replace hostname as appropriate. The ﬁle called ORACLE_APACHE.CONF in the $ORACLE_HOME/Apache/Apache/conf directory must include the ﬁle ISQLPLUS.CONF in the ORACLE_HOME/sqlplus/admin directory. The include command should be of the following form. Be sure there are no comments (#) in unexpected places. include "$ORACLE_HOME/sqlplus/admin/isqlplus.conf" Try stopping and restarting the HTTP Server, especially if you have made any changes to any conﬁguration ﬁles. A bug in Oracle 9.2 for Windows 2000 caused errors when starting and stopping the HTTP Server using both the Windows service and the Apache command on the Start menu. A solution to this issue is to set the service to Manual and always start and stop Apache and the HTTP Server with the fol- lowing commands executed in a DOS shell (the command line): C:\oracle\ora92\Apache\Apache\apache -k start C:\oracle\ora92\Apache\Apache\apache - k shutdown 8.4.4 Customizing iSQL*Plus Display Numerous preferences can be changed from the iSQL*Plus interface on the client machine. Additionally, on the server there is an HTML cascading style sheet.1 This style sheet can be altered to change output appearance. The HTML cascading style sheet is called IPLUS.CSS and is located in the $ORACLE_HOME/sqlplus/admin/iplus directory on the server. Changing the style sheet allows customization of colors and fonts used by iSQL*Plus when it formats output for queries. Using the same simple query used pre- Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
8.4 Using iSQL*Plus 173 Figure 8.29 Changing the iSQL*Plus Style Sheet on the Server. viously in Figure 8.26, Figure 8.29 has an altered appearance as a result of changes to the style sheet ﬁle on the server. In Figure 8.29, all text color is removed, all background colors apart from that in the headings is removed, and all borders are removed from everything but headings. The style sheet has numerous elements. I changed the following two elements: The TH or HTML table heading tag or element is changed where highlighted. TH { font : bold 10pt Arial, Helvetica, sans-serif; color : black; background : #f0f0f0; padding : 0px 0px 0px 0px; } The combination TABLE, TR, TD element is also changed where highlighted. TABLE, TR, TD { font : 10pt Arial, Helvetica, sans-serif; Chapter 8 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
174 8.5 Endnotes color : Black; background : white; border : 1 padding : 0px 0px 0px 0px; margin : 0px 0px 0px 0px; } This is a quick introduction to iSQL*Plus that should help you get started with your own experimentation. Remember to review the help screens provided inside iSQL*Plus for more examples of code and quick reference to SQL syntax. This chapter has covered more detail on SQL*Plus and related tools, in addition to the introductory information provided in Chapter 1. The next chapter moves back into Oracle SQL and looks at functions, namely single- row functions. 8.5 Endnotes 1. www.oracledbaexpert.com/menu/DHTML.html Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
9 Single-Row Functions In this chapter: What types of built-in functions are available? What are single-row functions? What are the categories of single-row functions? How do functions work with queries? What are the options when formatting strings, numbers, and dates? What are data conversion functions? How are functions combined? This chapter uses the queries you have worked with in previous chapters and expands the way you can use columns by introducing functions. You will examine the types of functions used for different data types. Finally, you will experiment with combining functions together for more ﬂexibility. A function is a built-in PL/SQL program that always returns a single value. You can use the predeﬁned functions (such as the ones discussed in this chapter) or you can create your own (see Chapter 24). A function always returns a single value, as opposed to a procedure, which is a similar type of program but is able to return more than one value. You can call a function within a query or other SQL command. You have already seen a few functions in previous chapters (e.g., NVL and SYSDATE). Before we examine single-row functions in detail, let’s look at Oracle-provided built- in functions in general. Grouping functions are covered in Chapter 11, reg- ular expression functions in Chapter 14, object reference functions in Chapter 16, and XML functions in Chapter 17. 175 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
176 9.2 Single-Row Functions 9.1 Types of Functions Oracle divides all functions into the following categories: Single-Row Functions. Functions that operate on a single row at a time. This chapter examines this type of function. For example, the UPPER() function converts characters to uppercase. Grouping Functions. Chapter 11 covers grouping functions in detail. Aggregate Functions. Functions that operate on a group of rows at one time and return a single row. For example, the COUNT() function counts the number of rows in a table. Analytical Functions. Functions that operate on groups of rows and return one or more summary rows. For example, the STD- DEV() OVER() function returns the standard deviation rows based on values in one or more columns. Object Reference Functions. Functions that manipulate the value in columns with the REF datatype in object tables. For example, the DEREF() function returns the value of an attribute in the referenced object table (see Chapter 16). User-Deﬁned Functions. Functions that are built by you and per- form whatever data manipulations you program them to do. Exam- ples of user-deﬁned functions are given throughout this book, with syntactical details in Chapter 24. This chapter covers many of the dozens of single-row functions available for your use in queries. 9.2 Single-Row Functions Single-row functions add a great deal of power to queries. Use functions in the SELECT clause to modify the appearance of dates, for example. Add functions to the WHERE clause to help determine which rows to include in query results. Place functions in the ORDER BY clause to ﬁne-tune sorting. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
9.2 Single-Row Functions 177 Note: Placing functions in WHERE and ORDER BY clauses can be detri- mental to performance.1 There are so many single-row functions that there is not room to cover them all in this chapter. However, you will gain experience with the com- monly used functions. Other more obscure functions are detailed in Oracle documentation. Single-row functions can be subdivided into the following categories: Character or String Functions. Functions that require a character value or string as input (see Figure 9.1). Number Functions. Functions that require a number as input. Most of these return a number. For example, the SIGN function returns -1 if the number is negative, 0 if it is zero, and 1 if it is posi- tive (see Figure 9.2). Binary Floating-Point Number Functions. These func- tions are new to Oracle Database 10g and could possibly be viewed as a subset of number functions, except that they operate speciﬁcally on binary ﬂoating-point numbers (see Figure 9.2). Datetime Functions. Functions that require a date value as input (see Figure 9.3). Conversion Functions. Functions that convert one datatype to another. For example, the TO_CHAR function can convert a date or number to a character value (see Figure 9.4). Miscellaneous Functions. Functions that perform unusual tasks. For example, the DECODE function acts like an IF-THEN-ELSE con- struct or CASE statement (see Figure 9.5). Figures 9.1 through 9.5 show all the different types of single-row func- tions. Functions highlighted and marked with an asterisk (*INITCAP) in each ﬁgure are discussed in this chapter. Additionally, many functions are referred to in other chapters. Chapter 9 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
178 9.2 Single-Row Functions Figure 9.1 Single-Row String Functions. Figure 9.2 Single-Row Number Functions. Figure 9.3 Single-Row Datetime Functions. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
9.2 Single-Row Functions 179 Figure 9.4 Single-Row Conversion Functions. Figure 9.5 Single-Row Miscellaneous Functions. Chapter 9 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
180 9.2 Single-Row Functions The next sections deﬁne all of the functions highlighted in Figures 9.1 and 9.2, divided by their categories. Functions detailed in this chapter are generally the more useful functions. As already stated, the remaining func- tions tend to be obscure and seldom used. In fact, some functions included in this chapter are obscure. Let’s begin with string functions. 9.2.1 String Functions The string functions manipulate alphanumeric data. In this section, after each function is deﬁned, an example shows how the function is used and what it returns. CONCAT(expression, expression). Concatenation of strings is the adding together of two strings. This function performs the same task as the string concatenation operator || (see Chapter 7). CONCAT('Oracle',' Database 10g') = 'Oracle Database 10g' 'Oracle'||' Database '||'10g' = 'Oracle Database 10g' 'My name is '||FIRST_NAME = 'My name is Jim' LOWER(expression), UPPER(expression), and INITCAP(expres- sion). LOWER converts to lowercase, UPPER to uppercase, and INITCAP to mixed case (ﬁrst letter of each word in uppercase). INITCAP('oracle certified professional') = 'Oracle Certified Professional' INSTR(expression, substring [, position [, occurrence]]). Returns the position of a substring within a string (the ﬁrst character in the string is at position 1). The position and occurrence parameters are optional. The position parameter determines a start point to search from, and occurrence indicates which duplicate, if any, of the sub- string should be matched. In the following example, the second occurrence of the string 10g begins at position 19: INSTR('oracle 10g oracle 10g oracle 10g','10g',1,2) = 19 LENGTH(expression). The length in characters of a string. LENGTH('oracle certified professional') = 29 LENGTH(LAST_NAME) = length of the data in the column LPAD(expression, n [, expression]) and RPAD(expression, n [, expression]). Left or right pad a string from the left or the right (start or end of the string) with the speciﬁed characters in the second string, up to a string length of n characters. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
9.2 Single-Row Functions 181 LPAD('oracle',10,'X') = 'XXXXoracle' RPAD('oracle',10,'X') = 'oracleXXXX' Note: Padding a string is sometimes referred to as ﬁlling a string. TRIM([[LEADING|TRAILING|BOTH] character FROM] expression), LTRIM(expression, string-set), and RTRIM(expres- sion, string-set). LTRIM and RTRIM will remove from the left and the right of the string, respectively, any characters contained within the string set, until a character not in the string set is found. The LTRIM and RTRIM functions are less useful than the TRIM func- tion. TRIM will remove characters from the string from the left, the right, or both. In its simplest form, TRIM can be used to remove leading and trailing spaces from a string. TRIM(' oracle ') = 'oracle' Remember that spaces embedded between other characters do not get removed, until a character not in the string set is found. As a result, for the next example there is no change. TRIM(' o r a c l e ') = 'o r a c l e' TRIM( LEADING '-' FROM '---608-444-3029') = '608-444-3029' REPLACE(expression, search [, replace]) and TRANS- LATE(expression, search [, replace]). REPLACE will replace every occurrence of the search string with the replacement string. Where the REPLACE function matches any search string within the string, TRANSLATE will match each character between the search and replace strings by the position of characters in both the search and replace strings. Phew! In simple terms, REPLACE replaces groups of characters and TRANSLATE translates individual characters. REPLACE(' o r a c l e',' ','') = 'oracle' REPLACE('My dog has fleas.','as','odd') = 'My dog hodd fleodd.' In the ﬁrst TRANSLATE function example following, nothing is changed because the space in the search string has no corresponding value in the replace string. TRANSLATE(' o r a c l e ','oracle ','12345X') = '12345X' TRANSLATE('My dog has fleas.','agf','AGF') = 'My doG hAs FleAs.' Chapter 9 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
182 9.2 Single-Row Functions SUBSTR(expression, [-]position[, length]). The SUBSTR func- tion returns a portion of a string. If the length parameter is omitted, then all characters after the value of position are returned. If the posi- tion parameter is positive, then the substring value is extracted from the left of the string; otherwise, if the parameter is negative, the value is extracted from the right (end) of the string. SUBSTR('oracle certified professional', 8,9) = 'certified' SUBSTR('oracle certified professional',-12,12) = 'professional' Here is a quick example using some of the string functions men- tioned previously. Figure 9.6 shows the results. The query shows the complete value of the NAME column, followed by the length of the value, a section of the name, and ﬁnally, the position of the second occurrence of the letter “a” in the name. Notice that the INSTR func- tion returns zero if it cannot ﬁnd a match. SELECT NAME, LENGTH(NAME) "Length" , SUBSTR(NAME,5,5) "Letters 5 thru 9" , INSTR(NAME,'a',1,2) "Second a" FROM ARTIST; Now let’s proceed to number functions. 9.2.2 Number Functions Number functions require numbers, not strings, as input. They nearly always return a numeric value. ABS(n). Finds an absolute value of a number. An absolute value function returns the positive or unsigned value of a negative or posi- tive number. ABS(-125) = 125 ABS(125) = 125 CEIL(n) and FLOOR(n). Ceiling and ﬂoor are similar to rounding and truncating functions. Ceiling returns the next integer greater than n. Floor returns the next integer less than n. CEIL(1.1) = 2 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
9.2 Single-Row Functions 183 Figure 9.6 Some String Functions. FLOOR(1.9) = 1 MOD(m, n). MOD is the modulus or remainder function, which returns the remainder of the ﬁrst value divided by the second value (m divided by n). The ﬁrst value is returned if the second value is zero. MOD(5,2) = 1 MOD(4,0) = 4 MOD(9,3) = 0 MOD(23,4) = 3 POWER(m, n). The exponential function raises m to the power of n (the nth power). POWER(2,3) = 8 (23 = 2 * 2 * 2 = 4 * 2 = 8) ROUND(n [, places]). ROUND is a proper mathematical rounding function as opposed to the CEIL and FLOOR functions. For the ROUND function, a decimal 5 and over will be rounded up and below 5 will be rounded down. The third example following is rounded to two decimal places and the fourth to three decimal places. Chapter 9 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
184 9.2 Single-Row Functions ROUND(1.4) = 1 ROUND(1.5) = 2 ROUND(1.42356,2) = 1.42 ROUND(1.42356,3) = 1.424 ROUND(18755.24,-2) = 18800 SIGN(n). Returns –1 if negative, 0 if 0, and 1 if positive. SIGN(-5032) = –1 SIGN(0) = 0 SIGN(5000) = 1 SQRT(n). Calculates the square root of a number. SQRT(4) = 2 (2 * 2 = 4) TRUNC(n [, places]). TRUNC is a truncate function. A truncate function always rounds down by removing trailing numerals from a number, effectively rounding down regardless of the .5 cutoff value. TRUNC can also truncate both sides of the decimal point. TRUNC(147.65,1) = 147.6 TRUNC(147.65,-2) = 100 Other Mathematical Functions. The following functions perform obscure mathematical or trigonometric calculations. These types of functions are rarely used other than in ﬁnancial or numerically related applications. Some of these functions are listed here. There are many other Oracle built-in functions to do all sorts of weird and wonderful things (see Oracle documentation). SIN(n), COS(n), and TAN(n). Sine, cosine, and tangent. ASIN(n), ACOS(n), and ACOS(n). Arcsine, arccosine, and arctan- gent (the inverse of sine, cosine, and tangent). SINH(n), COSH(n), and TANH(n). Hyperbolic sine, cosine, and tangent. EXP(n), LN(n), and LOG(n). e raised to the nth power, the natural logarithm, and the logarithm. Here is a query using some of the number functions mentioned. The query uses the STUDIOTIME table and applies various func- tions to the AMOUNT_CHARGED column values. The result is shown in Figure 9.7. SELECT ST.ARTIST_ID, ST.AMOUNT_CHARGED "Amount" , ROUND(ST.AMOUNT_CHARGED,1) "Rounded to one decimal" Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
9.2 Single-Row Functions 185 Figure 9.7 Number Functions Return Results Based on the Column Value. , ROUND(ST.AMOUNT_CHARGED,-1) "Rounded to tens" , TRUNC(ST.AMOUNT_CHARGED) "Truncated" FROM STUDIOTIME ST WHERE ROWNUM < 15; 9.2.2.1 Binary Floating-Point Number Functions As already mentioned, these functions are new to Oracle Database 10g and could possibly be viewed as a subset of number functions, except that they operate speciﬁcally on binary ﬂoating-point numbers. This section is par- tially repeated from Chapter 2. TO_BINARY_DOUBLE(expression, format) and TO_BINARY_ FLOAT(expression, format) allow for conversions. Essentially, these functions are conversion functions, but they are listed here because they deal with binary ﬂoating-point numbers. NANVL(value, replace). NANVL returns a replacement value if the initial value is not a number. Chapter 9 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
186 9.2 Single-Row Functions REMAINDER(n, m). This function is a remainder or modulus function speciﬁcally for binary ﬂoating-point numbers. The next section covers date functions. 9.2.3 Date Functions ADD_MONTHS(date, months), NEXT_DAY(date, weekday), LAST_DAY(date), and MONTHS_BETWEEN(date, date). ADD_MONTHS will add or subtract a number of months to a date where differences in the number of days in months default to the last day in the resulting month. NEXT_DAY ﬁnds the ﬁrst day from the date speciﬁed for the day of the week in the string contained in the second parameter. LAST_DAY ﬁnds the last day in the month. MONTHS_BETWEEN will return the number of months between two dates. ADD_MONTHS('27-AUG-02',4) = 27-DEC-02 NEXT_DAY('27-AUG-02','MONDAY') = 02-SEP-02 LAST_DAY('27-AUG-02') = 31-AUG-02 MONTHS_BETWEEN('27-AUG-02','01-JAN-02') = 7.83870968 months Note: In the examples listed, note how dates are listed as strings and a TO_DATE conversion function is not required. This is because DD- MON-YY is the default date format and there is an implicit string-to-date datatype conversion. The default date format can be altered. Datatypes are covered in Chapter 16. SYSDATE, CURRENT_DATE, CURRENT_TIMESTAMP(preci- sion), LOCALTIMESTAMP(precision), and SYSTIMESTAMP. SYSDATE and CURRENT_DATE ﬁnd the system date setting on the database server where CURRENT_DATE is timezone sensitive. The other functions all provide different variations on timestamps. SQL> SELECT SYSDATE FROM DUAL; SYSDATE -------------- 23-JAN-04 SQL> SELECT SYSTIMESTAMP FROM DUAL; SYSTIMESTAMP ------------------------ 23-JAN-04 01.03.20.661000 AM -05:00 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
9.2 Single-Row Functions 187 Table 9.1 Some of the ROUND and TRUNC Function Date Formatting Format Characters Rounding and Truncating CC The ﬁrst year in a century. YYYY, YEAR, YY The nearest year, rounds up on July 1st. Q The nearest quarter, rounds up on the 16th of month two. MONTH, MON, MM The nearest month, rounds up on the 16th. WW The same day of the week as the ﬁrst day of the year. W The same day of the week as the ﬁrst day of the month. DDD, DD The day. DAY, D The ﬁrst day of the week. HH, HH12, HH24 The hour (HH24 is a 24-hour clock). MI The minute. ROUND(date [, format]) and TRUNC(date [, format]). These two functions round up or truncate dates according to the format speciﬁ- cation. See Table 9.1 with date formatting rules for the ROUND and TRUNC functions. Some examples of date ROUND and TRUNC functions are as follows. Let’s say that our current date is 26-AUG-02. ROUND(SYSDATE,'YEAR') = 01-JAN-03 TRUNC(SYSDATE,'YEAR') = 01-JAN-02 ROUND(SYSDATE,'MONTH') = 01-SEP-02 TRUNC(SYSDATE,'MONTH') = 01-AUG-02 ROUND(SYSDATE,'WW') = 27-AUG-02 TRUNC(SYSDATE,'WW') = 20-AUG-02 EXTRACT (format, date). The EXTRACT date function is proba- bly one of the most useful and largely unknown date functions. For- mat settings are simple, speciﬁc, and can be YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, or various TIMEZONE options. Chapter 9 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
188 9.2 Single-Row Functions Two examples in the following two queries have their results shown in Figure 9.8. SELECT EXTRACT(YEAR FROM DATE '2004-02-09') AS YEAR , EXTRACT(MONTH FROM DATE '2004-02-09') AS MONTH , EXTRACT(DAY FROM DATE '2004-02-09') AS DAY FROM DUAL; SELECT EXTRACT(YEAR FROM SYSDATE) AS YEAR , EXTRACT(MONTH FROM SYSDATE) AS MONTH , EXTRACT(DAY FROM SYSDATE) AS DAY FROM DUAL; Looking at some general date function examples, the next query uses several date functions. The ﬁnal expression uses the SYSDATE function and subtracts two dates. The results are in days, so to help compare the MONTHS_BETWEEN function and the next column, that following col- Figure 9.8 The EXTRACT Function Retrieves Parts of Dates. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
9.2 Single-Row Functions 189 umn is divided by the average number of days per month (30.44). The two values Months-1 and Months-2 are very close but not identical. This is a rather odd example, but Figure 9.9 shows the result. SELECT SESSION_DATE , ADD_MONTHS(SESSION_DATE,3) "Plus 3 Months" , ROUND(SESSION_DATE,'Month') "Round off" , MONTHS_BETWEEN(SYSDATE,DUE_DATE) "Months-1" , (SYSDATE-DUE_DATE)/30.44 "Months-2" FROM STUDIOTIME WHERE ROWNUM < 15; The next section examines datatype conversion functions. Figure 9.9 Date Functions Use Oracle Database 10g’s Standard Date Output Format. Chapter 9 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.