Oracle SQL Jumpstart with Examples- P4

Oracle SQL Jumpstart with Examples- P4: 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...
Nội dung trích xuất từ tài liệu:
Oracle SQL Jumpstart with Examples- P4 120 6.3 Sorting Methods BEGIN vSPLIT := INSTR(pTIME,:); vHOURS := TO_NUMBER(SUBSTR(pTIME,1,vSPLIT-1)); vSECONDS := TO_NUMBER(SUBSTR(pTIME,vSPLIT+1)); RETURN vHOURS+(vSECONDS/60); EXCEPTION WHEN OTHERS THEN RETURN 0; END; / And now we can replace the ORDER BY clause with the function as an expression, making for a much easier to read ORDER BY clause. The result is shown in Figure 6.9. ORDER BY GETTIME(PLAYING_TIME) NULLS FIRST, 3 DESC, 1; Figure 6.9 Using an Expression in the ORDER BY Clause.Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. 6.3 Sorting Methods 121 Obviously, a position number cannot be applied to the expression unless the expression is placed into the SELECT list. Note: Copying the expression from the ORDER BY into the SELECT col- umns list could possibly help performance. We can change the query something like that shown following. The expression GETTIME(PLAYING_TIME) has been added to the query, and the ORDER BY clause has been changed to accommodate it. The result in Figure 6.10 shows the same sorted order on the PLAYING_TIME column value as shown in Figure 6.9. SELECT RECORDING_DATE, PLAYING_TIME, GETTIME(PLAYING_TIME) , TITLE FROM SONG WHERE TITLE LIKE %a% AND TITLE LIKE %e% AND TITLE LIKE %i% ORDER BY 3 NULLS FIRST, 4 DESC, 1; Figure 6.10 ORDER BY Clause Expressions Cannot Use Positions. Chapter 6Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. 122 6.4 Endnotes You have now added most of the fundamental features to the SELECT statement, namely the SELECT, FROM, WHERE, and ORDER BY clauses. Later chapters expand on a multitude of other features and mecha- nisms. The next chapter digresses somewhat and covers operators, condi- tions, and pseudocolumns. 6.4 Endnotes 1. Oracle Performance Tuning for 9i and 10g (ISBN: 1-55558-305-9)Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. 7 Operators, Conditions, and Pseudocolumns In this chapter: What is precedence? What is an operator and what is available? What is a condition and what is available? What are pseudocolumns and what is available? Note: Backus-Naur Form syntax angle brackets are used syntactically in this chapter to represent substitution of all types. For example, = . Operators, conditions, and pseudocolumns are used and often explained throughout this book. This chapter may duplicate parts of other chapters with the intention of including all specific details in a single chapter. Addi- tionally, some of the content of this chapter is common to many software products. Need it be repeated? Yes, because this title is intended for use as an SQL reference book. Note: This chapter may reclassify the categories of operators, conditions, and pseudocolumns both with respect to Oracle documentation and other chapters in this book. Let’s begin with the simplest of things, precedence. ...