《数据库技术与Oracle：sql-03 Single-Row Functions》由会员分享，可在线阅读，更多相关《数据库技术与Oracle：sql-03 Single-Row Functions（50页珍藏版）》请在金锄头文库上搜索。
1、3Single-Row Functions3-2ObjectivesAfter completing this lesson, you should be able to do the following:Describe various types of functions availablein SQLUse character, number, and date functions in SELECT statementsDescribe the use of conversion functions 3-3SQL FunctionsFunctionFunctionInputarg 1a
2、rg 1arg 2arg 2arg arg n nFunction performs actionOutputResultResultvaluevalue3-4Two Types of SQL FunctionsFunctionsFunctionsSingle-row Single-row functionsfunctionsMultiple-rowMultiple-rowfunctionsfunctions3-5Single-Row FunctionsSingle row functions:Manipulate data itemsAccept arguments and return o
3、ne valueAct on each row returnedReturn one result per rowMay modify the data typeCan be nestedAccept arguments which can be a column or an expressionfunction_name (arg1, arg2,.)3-6Single-row FunctionsConversionConversionCharacterCharacterNumberNumberDateDateGeneralGeneralSingle-row Single-row functi
4、onsfunctions3-7Character FunctionsCharacterCharacterfunctionsfunctionsLOWERUPPERINITCAPCONCATSUBSTRLENGTHINSTRLPAD | RPADTRIMREPLACECase-manipulation Case-manipulation functionsfunctionsCharacter-manipulationCharacter-manipulationfunctionsfunctions3-9FunctionResultCase Manipulation FunctionsThese fu
5、nctions convert case for character strings.LOWER(SQL Course)UPPER(SQL Course)INITCAP(SQL Course)sql courseSQL COURSESql Course3-10Using Case Manipulation FunctionsDisplay the employee number, name, and department number for employee Higgins:SELECT employee_id, last_name, department_idFROM employeesW
6、HERE last_name = higgins;no rows selectedno rows selectedSELECT employee_id, last_name, department_idFROM employeesWHERE LOWER(last_name) = higgins;3-11CONCAT(Hello, World)SUBSTR(HelloWorld,1,5)LENGTH(HelloWorld)INSTR(HelloWorld, W)LPAD(salary,10,*)RPAD(salary, 10, *)TRIM(H FROM HelloWorld)HelloWorl
7、dHello106*2400024000*elloWorldFunctionResultCharacter Manipulation FunctionsThese functions manipulate character strings:3-12SELECT 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;Using the Charac
8、ter Manipulation Functions1231233-13Number FunctionsROUND: Rounds value to specified decimalROUND(45.926, 2)45.93TRUNC: Truncates value to specified decimalTRUNC(45.926, 2) 45.92MOD: Returns remainder of divisionMOD(1600, 300)1003-14SELECT ROUND(45.923,2), ROUND(45.923,0), ROUND(45.923,-1)FROM DUAL;
9、Using the ROUND FunctionDUAL is a dummy table you can use to view results from functions and calculations.1233123-15SELECT TRUNC(45.923,2), TRUNC(45.923), TRUNC(45.923,-2)FROM DUAL;Using the TRUNC Function3121233-16SELECT last_name, salary, MOD(salary, 5000)FROM employeesWHERE job_id = SA_REP;Using
10、the MOD FunctionCalculate the remainder of a salary after it is divided by 5000 for all employees whose job title is sales representative.3-17Working with DatesOracle database stores dates in an internal numeric format: century, year, month, day, hours, minutes, seconds.The default date display form
11、at is DD-MON-RR.Allows you to store 21st century dates in the 20th century by specifying only the last two digits of the year. Allows you to store 20th century dates in the 21st century in the same way. SELECT last_name, hire_dateFROM employeesWHERE last_name like G%;3-18Working with DatesSYSDATE is
12、 a function that returns:Date Time3-19Arithmetic 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 hours to a date by dividing the number of hours by 24.3-20Using Arithmetic Operatorswith DatesSELECT
13、last_name, (SYSDATE-hire_date)/7 AS WEEKSFROM employeesWHERE department_id = 90;3-21Date FunctionsNumber of monthsbetween two datesMONTHS_BETWEENADD_MONTHSNEXT_DAYLAST_DAYROUNDTRUNC Add calendar months to dateNext day of the date specifiedLast day of the monthRound date Truncate dateFunctionDescript
14、ion3-22MONTHS_BETWEEN (01-SEP-95,11-JAN-94)Using Date FunctionsADD_MONTHS (11-JAN-94,6)NEXT_DAY (01-SEP-95,FRIDAY) LAST_DAY(01-FEB-95) 19.677419411-JUL-9408-SEP-9528-FEB-953-23ROUND(SYSDATE,MONTH) 01-AUG-95ROUND(SYSDATE ,YEAR) 01-JAN-96TRUNC(SYSDATE ,MONTH) 01-JUL-95 TRUNC(SYSDATE ,YEAR) 01-JAN-95Us
15、ing Date FunctionsAssume SYSDATE = 25-JUL-95:3-24Conversion FunctionsImplicit data typeImplicit data typeconversionconversionExplicit data typeExplicit data typeconversionconversionData typeData typeconversionconversion3-25Implicit Data Type ConversionFor assignments, the Oracle server can automatic
16、allyconvert the following:VARCHAR2 or CHARFromToVARCHAR2 or CHARNUMBERDATENUMBERDATEVARCHAR2VARCHAR23-26Implicit Data Type ConversionFor expression evaluation, the Oracle Server can automatically convert the following:VARCHAR2 or CHARFromToVARCHAR2 or CHARNUMBERDATE3-27Explicit Data Type ConversionN
17、UMBERCHARACTERTO_CHARTO_NUMBERDATETO_CHARTO_DATE3-28Explicit Data Type ConversionNUMBERCHARACTERTO_CHARTO_NUMBERDATETO_CHARTO_DATE3-30Using the TO_CHAR Function with DatesThe format model:Must be enclosed in single quotation marks and is case sensitiveCan include any valid date format elementHas an
18、fm element to remove padded blanks or suppress leading zerosIs separated from the date value by a commaTO_CHAR(date, format_model)3-31YYYYElements of the Date Format ModelYEARMMMONTHDYDAYFull year in numbersYear spelled outTwo-digit value for monthThree-letter abbreviation of the day of the weekFull
19、 name of the day of the weekFull name of the monthMONThree-letter abbreviation of the monthDDNumeric day of the month3-32Using the TO_CHAR Function with NumbersThese are some of the format elements you can use with the TO_CHAR function to display a number value as a character:TO_CHAR(number, format_
20、model)90$L.,Represents a numberForces a zero to be displayedPlaces a floating dollar signUses the floating local currency symbolPrints a decimal pointPrints a thousand indicator3-33SELECT TO_CHAR(salary, $99,999.00) SALARYFROM employeesWHERE last_name = Ernst;Using the TO_CHAR Function with Numbers3
21、-34Using the TO_NUMBER and TO_DATE Functions Convert a character string to a number format using the TO_NUMBER function:Convert a character string to a date format using the TO_DATE function:These functions have an fx modifier. This modifier specifies the exact matching for the character argument an
22、d date format model of a TO_DATE functionTO_NUMBER(char, format_model)TO_DATE(char, format_model)3-35Using the TO_NUMBER and TO_DATE Functions Convert a character string to a number format using the TO_NUMBER function:Convert a character string to a date format using the TO_DATE function:These funct
23、ions have an fx modifier. This modifier specifies the exact matching for the character argument and date format model of a TO_DATE functionTO_NUMBER(char, format_model)TO_DATE(char, format_model)3-36RR Date FormatCurrent Year1995199520012001Specified Date27-OCT-9527-OCT-1727-OCT-1727-OCT-95RR Format
24、1995201720171995YY Format1995191720172095If 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 specifi
25、ed two-digit year is:3-37Example of RR Date FormatTo find employees hired prior to 1990, use the RR format, which produces the same results whether the command is run in 1999 or now:SELECT last_name, TO_CHAR(hire_date, DD-Mon-YYYY)FROM employeesWHERE hire_date TO_DATE(01-Jan-90, DD-Mon-RR);3-38Nesti
26、ng FunctionsSingle-row functions can be nested to any level.Nested functions are evaluated from deepest level to the least deep level.F3(F2(F1(col,arg1),arg2),arg3)Step 1 = Result 1Step 2 = Result 2Step 3 = Result 33-39SELECT last_name, NVL(TO_CHAR(manager_id), No Manager)FROM employeesWHERE manager
27、_id IS NULL;Nesting Functions3-40General FunctionsThese functions work with any data type and pertainto using nulls.NVL (expr1, expr2)NVL2 (expr1, expr2, expr3)NULLIF (expr1, expr2)COALESCE (expr1, expr2, ., exprn)3-41NVL FunctionConverts a null to an actual value.Data types that can be used are dat
28、e, character, and number.Data types must match:NVL(commission_pct,0)NVL(hire_date,01-JAN-97)NVL(job_id,No Job Yet)3-42SELECT last_name, salary, NVL(commission_pct, 0), (salary*12) + (salary*12*NVL(commission_pct, 0) AN_SALFROM employees;Using the NVL Function12123-43SELECT last_name, salary, commiss
29、ion_pct, NVL2(commission_pct, SAL+COMM, SAL) incomeFROM employees WHERE department_id IN (50, 80);Using the NVL2 Function12123-44Conditional ExpressionsProvide the use of IF-THEN-ELSE logic within a SQL statementUse two methods:CASE expressionDECODE function3-45The CASE ExpressionFacilitates conditi
30、onal inquiries by doing the work of an IF-THEN-ELSE statement:CASE expr WHEN comparison_expr1 THEN return_expr1 WHEN comparison_expr2 THEN return_expr2 WHEN comparison_exprn THEN return_exprn ELSE else_exprEND3-46SELECT last_name, job_id, salary, CASE job_id WHEN IT_PROG THEN 1.10*salary WHEN ST_CLE
31、RK THEN 1.15*salary WHEN SA_REP THEN 1.20*salary ELSE salary END REVISED_SALARYFROM employees;Using the CASE ExpressionFacilitates conditional inquiries by doing the work of an IF-THEN-ELSE statement:3-47The DECODE FunctionFacilitates conditional inquiries by doing the work of a CASE or IF-THEN-ELSE
32、 statement:DECODE(col|expression, search1, result1 , search2, result2,., , default)3-48Using the DECODE FunctionSELECT last_name, job_id, salary, DECODE(job_id, IT_PROG, 1.10*salary, ST_CLERK, 1.15*salary, SA_REP, 1.20*salary, salary) REVISED_SALARYFROM employees;3-49Using the DECODE FunctionSELECT
33、last_name, salary, DECODE (TRUNC(salary/2000, 0), 0, 0.00, 1, 0.09, 2, 0.20, 3, 0.30, 4, 0.40, 5, 0.42, 6, 0.44, 0.45) TAX_RATEFROM employeesWHERE department_id = 80;Display the applicable tax rate for each employee in department 80.3-50SummaryIn this lesson, you should have learned how to: Perform calculations on data using functionsModify individual data items using functionsManipulate output for groups of rows using functionsAlter date formats for display using functionsConvert column data types using functionsUse NVL functionsUse IF-THEN-ELSE logic
