Содержание
- 2. Objectives After completing this lesson, you should be able to do the following: Describe various types
- 3. SQL Functions Function Function performs action
- 4. Two Types of SQL Functions Functions Single-row functions Multiple-row functions
- 5. Single-Row Functions Manipulate data items Accept arguments and return one value Act on each row returned
- 6. Single-Row Functions Conversion Character Number Date General Single-row functions
- 7. Character Functions Character functions LOWER UPPER INITCAP CONCAT SUBSTR LENGTH INSTR LPAD Case conversion functions Character
- 8. Function Result Case Conversion Functions Convert case for character strings LOWER('SQL Course') UPPER('SQL Course') INITCAP('SQL Course')
- 9. Using Case Conversion Functions Display the employee number, name, and department number for employee Blake. SQL>
- 10. CONCAT('Good', 'String') SUBSTR('String',1,3) LENGTH('String') INSTR('String', 'r') LPAD(sal,10,'*') GoodString Str 6 3 ******5000 Function Result Character Manipulation
- 11. Using the Character Manipulation Functions SQL> SELECT ename, CONCAT (ename, job), LENGTH(ename), 2 INSTR(ename, 'A') 3
- 12. Number Functions ROUND: Rounds value to specified decimal ROUND(45.926, 2) 45.93 TRUNC: Truncates value to specified
- 13. Using the ROUND Function SQL> SELECT ROUND(45.923,2), ROUND(45.923,0), 2 ROUND(45.923,-1) 3 FROM DUAL; ROUND(45.923,2) ROUND(45.923,0) ROUND(45.923,-1)
- 14. SQL> SELECT TRUNC(45.923,2), TRUNC(45.923), 2 TRUNC(45.923,-1) 3 FROM DUAL; TRUNC(45.923,2) TRUNC(45.923) TRUNC(45.923,-1) --------------- ------------- --------------- 45.92
- 15. Using the MOD Function Calculate the remainder of the ratio of salary to commission for all
- 16. Working with Dates Oracle stores dates in an internal numeric format: Century, year, month, day, hours,
- 17. Arithmetic with Dates Add or subtract a number to or from a date for a resultant
- 18. Using Arithmetic Operators with Dates SQL> SELECT ename, (SYSDATE-hiredate)/7 WEEKS 2 FROM emp 3 WHERE deptno
- 19. Date Functions Number of months between two dates MONTHS_BETWEEN ADD_MONTHS NEXT_DAY LAST_DAY ROUND TRUNC Add calendar
- 20. MONTHS_BETWEEN ('01-SEP-95','11-JAN-94') Using Date Functions ADD_MONTHS ('11-JAN-94',6) NEXT_DAY ('01-SEP-95','FRIDAY') LAST_DAY('01-SEP-95') 19.6774194 '11-JUL-94' '08-SEP-95' '30-SEP-95'
- 21. Using Date Functions
- 22. Conversion Functions Implicit datatype conversion Explicit datatype conversion Datatype conversion
- 23. Implicit Datatype Conversion For assignments, Oracle can automatically convert VARCHAR2 or CHAR From To VARCHAR2 or
- 24. Implicit Datatype Conversion For expression evaluation, Oracle can automatically convert VARCHAR2 or CHAR From To VARCHAR2
- 25. Explicit Datatype Conversion NUMBER CHARACTER TO_CHAR
- 26. TO_CHAR Function with Dates The format model: Must be enclosed in single quotation marks and is
- 27. YYYY Date Format Model Elements YEAR MM MONTH DY DAY Full year in numbers Year spelled
- 28. Date Format Model Elements Time elements format the time portion of the date. Add character strings
- 29. Using TO_CHAR Function with Dates SQL> SELECT ename, 2 TO_CHAR(hiredate, 'fmDD Month YYYY') HIREDATE 3 FROM
- 30. TO_CHAR Function with Numbers Use these formats with the TO_CHAR function to display a number value
- 31. Using TO_CHAR Function with Numbers SQL> SELECT TO_CHAR(sal,'$99,999') SALARY 2 FROM emp 3 WHERE ename =
- 32. TO_NUMBER and TO_DATE Functions Convert a character string to a number format using the TO_NUMBER function
- 33. RR Date Format Current Year 1995 1995 2001 2001 Specified Date 27-OCT-95 27-OCT-17 27-OCT-17 27-OCT-95 RR
- 34. NVL Function Converts null to an actual value Datatypes that can be used are date, character,
- 35. SQL> SELECT ename, sal, comm, (sal*12)+NVL(comm,0) 2 FROM emp; Using the NVL Function ENAME SAL COMM
- 36. DECODE Function Facilitates conditional inquiries by doing the work of a CASE or IF-THEN-ELSE statement DECODE(col/expression,
- 37. Using the DECODE Function SQL> SELECT job, sal, 2 DECODE(job, 'ANALYST', SAL*1.1, 3 'CLERK', SAL*1.15, 4
- 38. Nesting Functions Single-row functions can be nested to any level. Nested functions are evaluated from deepest
- 39. Nesting Functions SQL> SELECT ename, 2 NVL(TO_CHAR(mgr),'No Manager') 3 FROM emp 4 WHERE mgr IS NULL;
- 40. Summary Use functions to: Perform calculations on data Modify individual data items Manipulate output for groups
- 41. Practice Overview Creating queries that require the use of numeric, character, and date functions Using concatenation
- 47. Скачать презентацию