教学讲稿oracle官方les

1、Using Single-Row Functions to Customize Output ObjectivesAfter completing this lesson, you should be able to do the following:Describe various types of functions available in SQLUse character, number, and date functions in SELECT statementsLesson AgendaSingle-row SQL functionsCharacter functionsNumb

2、er functionsWorking with datesDate functionsSQL FunctionsFunctionInputarg 1arg 2arg nFunction performs actionOutputResultvalueTwo Types of SQL FunctionsSingle-row functionsMultiple-rowfunctionsReturn one result per rowReturn one result per set of rowsFunctionsSingle-Row FunctionsSingle-row functions

3、:Manipulate data itemsAccept arguments and return one valueAct on each row that is returnedReturn one result per rowMay modify the data typeCan be nestedAccept arguments that can be a column or an expressionfunction_name (arg1, arg2,.)Single-Row FunctionsConversionCharacterNumberDateGeneralSingle-ro

4、w functionsLesson AgendaSingle-row SQL functionsCharacter functionsNumber functionsWorking with datesDate functionsCharacter FunctionsCharacterfunctionsLOWERUPPERINITCAPCONCATSUBSTRLENGTHINSTRLPAD | RPADTRIMREPLACECase-conversion functionsCharacter-manipulationfunctionsCase-Conversion FunctionsThese

5、 functions convert the case for character strings:sql courseLOWER(SQL Course)Sql CourseINITCAP(SQL Course)SQL COURSEUPPER(SQL Course)ResultFunctionSELECT employee_id, last_name, department_idFROM employeesWHERE LOWER(last_name) = higgins;Using Case-Conversion FunctionsDisplay the employee number, na

6、me, and department number for employee Higgins:SELECT employee_id, last_name, department_idFROM employeesWHERE last_name = higgins;Character-Manipulation FunctionsThese functions manipulate character strings:BLACK and BLUE REPLACE(JACK and JUE,J,BL) 10LENGTH(HelloWorld)6INSTR(HelloWorld, W)*24000LPA

7、D(salary,10,*)24000*RPAD(salary, 10, *)HelloWorldCONCAT(Hello, World)elloWorldTRIM(H FROM HelloWorld)HelloSUBSTR(HelloWorld,1,5)ResultFunctionSELECT employee_id, CONCAT(first_name, last_name) NAME, job_id, LENGTH (last_name), INSTR(last_name, a) Contains a?FROM employeesWHERE SUBSTR(job_id, 4) = REP

8、;Using the Character-Manipulation Functions231213Lesson AgendaSingle-row SQL functionsCharacter functionsNumber functionsWorking with datesDate FunctionsNumber FunctionsROUND: Rounds value to a specified decimalTRUNC: Truncates value to a specified decimalMOD: Returns remainder of division100MOD(160

9、0, 300)45.93ROUND(45.926, 2)45.92TRUNC(45.926, 2)ResultFunctionSELECT ROUND(45.923,2), ROUND(45.923,0), ROUND(45.923,-1)FROM DUAL;Using the ROUND FunctionDUAL is a dummy table that you can use to view results from functions and calculations.331212Using the TRUNC FunctionSELECT TRUNC(45.923,2), TRUNC

10、(45.923), TRUNC(45.923,-1)FROM DUAL;331212SELECT last_name, salary, MOD(salary, 5000)FROM employeesWHERE job_id = SA_REP;Using the MOD FunctionFor all employees with the job title of Sales Representative, calculate the remainder of the salary after it is divided by 5,000.Lesson AgendaSingle-row SQL

11、functionsCharacter functionsNumber functionsWorking with datesDate functionsSELECT last_name, hire_dateFROM employeesWHERE hire_date 01-FEB-88;Working with DatesThe Oracle database stores dates in an internal numeric format: century, year, month, day, hours, minutes, and seconds.The default date dis

12、play format is DD-MON-RR.Enables you to store 21st-century dates in the 20th century by specifying only the last two digits of the yearEnables you to store 20th-century dates in the21st century in the same wayRR Date FormatCurrent Year1995199520012001 27-OCT-9527-OCT-1727-OCT-1727-OCT-95 19952017201

13、71995 1995191720172095If two digits of the current year are:04904950995099The return date is in the current centuryThe return date is in the century after the current oneThe return date is in the century before the current oneThe return date is in the current centuryIf the specified two-digit year i

14、s:YY FormatRR FormatSpecified DateCurrent YearUsing the SYSDATE FunctionSYSDATE is a function that returns:DateTimeSELECT sysdateFROM dual;Arithmetic with DatesAdd or subtract a number to or from a date for a resultant date value.Subtract two dates to find the number of days between those dates.Add

15、hours to a date by dividing the number of hours by 24.SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKSFROM employeesWHERE department_id = 90;Using Arithmetic Operatorswith DatesLesson AgendaSingle-row SQL functionsCharacter functionsNumber functionsWorking with datesDate functionsDate-Manipulation F

16、unctionsNext day of the date specifiedNEXT_DAYLast day of the monthLAST_DAYRound dateROUNDTruncate dateTRUNCNumber of months between two datesMONTHS_BETWEENAdd calendar months to dateADD_MONTHSResultFunctionUsing Date Functions08-SEP-95NEXT_DAY (01-SEP-95,FRIDAY)28-FEB-95LAST_DAY (01-FEB-95)19.67741

17、94MONTHS_BETWEEN (01-SEP-95,11-JAN-94)29-FEB-96ADD_MONTHS (31-JAN-96,1)ResultFunctionUsing ROUND and TRUNC Functions with DatesAssume SYSDATE = 25-JUL-03:01-JUL-03TRUNC(SYSDATE ,MONTH)01-JAN-03TRUNC(SYSDATE ,YEAR)01-AUG-03ROUND(SYSDATE,MONTH)01-JAN-04ROUND(SYSDATE ,YEAR)ResultFunctionSummaryIn this lesson, you should have learned how to:Perform calculations on data using functionsModify individual data item