文档详情

吉林大学数据库技术与Oracle sql-03.ppt

发布：2025-06-09约1.06万字共50页下载文档

文本预览下载声明

Single-RowFunctions

ObjectivesAftercompletingthislesson,youshouldbeabletodothefollowing:Describevarioustypesoffunctionsavailable

inSQLUsecharacter,number,anddatefunctionsinSELECTstatementsDescribetheuseofconversionfunctions

SQLFunctionsFunctionInputarg1arg2argnFunctionperformsactionOutputResultvalue

TwoTypesofSQLFunctionsFunctionsSingle-rowfunctionsMultiple-rowfunctions

Single-RowFunctionsSinglerowfunctions:ManipulatedataitemsAcceptargumentsandreturnonevalueActoneachrowreturnedReturnoneresultperrowMaymodifythedatatypeCanbenestedAcceptargumentswhichcanbeacolumnoranexpressionfunction_name[(arg1,arg2,...)]

Single-rowFunctionsConversionCharacterNumberDateGeneralSingle-rowfunctions

CharacterFunctionsCharacterfunctionsLOWERUPPERINITCAPCONCATSUBSTRLENGTHINSTRLPAD|RPADTRIMREPLACECase-manipulationfunctionsCharacter-manipulationfunctions

FunctionResultCaseManipulationFunctionsThesefunctionsconvertcaseforcharacterstrings.LOWER(SQLCourse)UPPER(SQLCourse)INITCAP(SQLCourse)sqlcourseSQLCOURSESqlCourse

UsingCaseManipulationFunctionsDisplaytheemployeenumber,name,anddepartmentnumberforemployeeHiggins:SELECTemployee_id,last_name,department_idFROMemployeesWHERElast_name=higgins;norowsselectedSELECTemployee_id,last_name,department_idFROMemployeesWHERELOWER(last_name)=higgins;

CONCAT(Hello,World)SUBSTR(HelloWorld,1,5)LENGTH(HelloWorld)INSTR(HelloWorld,W)LPAD(salary,10,*)RPAD(salary,10,*)TRIM(HFROMHelloWorld)HelloWorldHello106*****2400024000*****elloWorldFunctionResultCharacterManipulationFunctionsThesefunctionsmanipulatecharacterstrings:

SELECTemployee_id,CONCAT(first_name,last_name)NAME,job_id,LENGTH(last_name),INSTR(last_name,a)Containsa?FROMemployeesWHERESUB

显示全部

相似文档