Oracle PL(SQL)单行函数和组函数及使用

ORACLEPL/SQPL/SQL1.1)2)2.3.1.1)2)2.3.1.1)IF2)CASE2.1)LOOP…EXIT…END2)WHILE…LOOP3)FOR…LOOP3.4.1.2.3.1.2.3.4.5.6.7.1)%ISOPEN2)%FOUND3)%NOTFOUND4)%ROWCOUNT1.2.3.4.1.2.3.4.1.2.3.4.5.1.2.3.1.PL/SQLPL/SQLPL/SQL1)DeclarationsectionDECLAREExecutablesectionPL/SQLBEGINPL/SQLExceptionsection2)PL/SQL[DECLARE]DeclarationStatementsBEGINExecutableStatements[EXCEPTIONExceptionHandlers]ENDPL/SQLSQLSQLPL/SQLBEGINDECLAREEND--2.PL/SQLBEGINEXCEPTIONEXCEPTIONEND:=SELECTINTOFETCHINTOPL/SQLSQLPL/SQLSELECTSELECTINTONULLCOMMITROLLBACK(DataDefinitionlanguage)DDLEXECUTEIMMEDIATEDBMS_SQL3.SQL*PLUSPL/SQLPL/SQL/EXECUTEEXECUTEPL/SQLDeveloperEXECUTEBEGINEND1.1)Variable_Name[CONSTANT]databyte[NOTNULL][:=DEFAULTEXPRESSION]NOTNULL2)eno:=7369;myname:='SCOTT';eno:=&empno;empnoenoSQLSELECTINTOFETCHINTOSELECTEMP_NAMEINTOMyNameFROMEMPLOYEESWHEREEMPID=eno;2.CONSTANTSQL%TYPE%ROWTYPE3.0-90*:.‘“‘“[]PL/SQL30DECLAREORDER_NONUMBER(3);CUST_NAMEVARCHAR2(20);ORDER_DATEDATE;EMP_NOINTEGER:=25;PICONSTANTNUMBER:=3.1416;BEGINNULL;END;!--[if!supportLists]--!--[endif]--+-*/**!--[if!supportLists]--!--[endif]--===!=:=!--[if!supportLists]--!--[endif]--ISNULLNULLTRUELIKEBETWEENIN!--[if!supportLists]--!--[endif]--ANDORNOT!--[if!supportLists]--1.1)IFIfIFConditionTHENStatement;ENDIF;thenendifIFConditionTHENStatements_1;ELSEStatements_2;ENDIF;thenelseIFCondition1THENStatements_1;ELSEIFCondition2THENStatements_2;ELSEStatements_3;ENDIF;ifthenelseifthenelseIFIFIF..ELSEIFIF…ELSE2)CASECASECASEGradeWHEN'A'THENDBMS_OUTPUT.PUT_LINE('Excellent');WHEN'B'THENDBMS_OUTPUT.PUT_LINE('VeryGood');WHEN'C'THENDBMS_OUTPUT.PUT_LINE('Good');WHEN'D'THENDBMS_OUTPUT.PUT_LINE('Fair');WHEN'F'THENDBMS_OUTPUT.PUT_LINE('Poor');ELSEDBMS_OUTPUT.PUT_LINE('Nosuchgrade');ENDCASE;CASECASE2.1)LOOP…EXIT…ENDLOOPLOOPStatements;ENDLOOP;StatementsEXIT2)WHILE…LOOPWHILE…LOOPWHILEConditionLOOPStatements;ENDLOOP;WHILE…LOOPTRUEFALSE3)FOR…LOOPFOR…LOOPFORCounterIN[REVERSE]Start_Range…End_RangeLOOPStatements;ENDLOOP;LOOPWHILEFORCounterStart_RangeStart_Range+1End_Range,Start_RangeEnd_RangeREVERSE3.GOTOGOTOLABEL;GOTOPL/SQLGOTOIFDECLAREXNUMBER(3);YNUMBER(3);V_COUNTERNUMBER(2);BEGINX:=100;FORV_COUNTERIN1..10LOOPIFV_COUNTER=4THENGOTOend_of_loop;ENDIF;X:=X+10;ENDLOOP;end_of_loopY:=X;dbms_output.put_line('Y:'||Y);END;“Y130”4.GOTO1.BEGINEXCEPTIONWHENExcep_Name1THENStatements1;WHENExcep_Name2THENStatements2;WHENOTHERSTHENStatements3;END;2.ACCESS_INTO_NULLORA-06530-6530CASE_NOT_FOUNDORA-06592-6592COLLECTION_IS_NULLORA-06531-6531CURSOR_ALREADY_OPENORA-06511-6511DUP_VAL_ON_INDEXORA-00001-1INVALID_CURSORORA-01001-1001INVALID_NUMBERORA-01722-1722LOGIN_DENIEDORA-01017-1017NO_DATA_FOUNDORA-01403-1403NOT_LOGGED_ONORA-01012-1012PROGRAM_ERRORORA-06501-6501ROWTYPE_MISMATCHORA-06504-6504SELF_IS_NULLORA-30625-30625STORAGE_ERRORORA-06500-6500SUBSCRIPT_BEYOND_COUNTORA-06533-6533SUBSCRIPT_OUTSIDE_LIMITORA-06532-6532SYS_INVALID_ROWIDORA-01410-1410TIMEOUT_ON_RESOURCEORA-00051-51TOO_MANY_ROWSORA-01422-1422VALUE_ERRORORA-06502-6502ZERO_DIVIDEORA-01476-1476ACCESS_INTO_NULLYourprogramattemptstoassignvaluestotheattributesofanuninitialized(atomicallynull)object.CASE_NOT_FOUNDoneofthechoicesintheWHENclausesofaASEtatementisselected,andthereisnoELSEclause.COLLECTION_IS_NULLYourprogramattemptstoapplycollectionmethodsotherthanEXISTStoanuninitialized(atomicallynull)nestedtableorvarray,ortheprogramattemptstoassignvaluestotheelementsofanuninitializednestedtableorvarray.CURSOR_ALREADY_OPENYourprogramattemptstoopenanalrea***opencursor.Acursormustbeclosedbeforeitcanbereopened.AcursorFORloopautomaticallyopensthecursortowhichitrefers.So,yourprogramcannotopenthatcursorinsidetheloop.DUP_VAL_ON_INDEXYourprogramattemptstostoreduplicatevaluesinadatabasecolumnthatisconstrainedbyauniqueindex.INVALID_CURSORYourprogramattemptsanillegalcursoroperationsuchasclosinganunopenedcursor.INVALID_NUMBERInaSQLstatement,theconversionofacharacterst***intoanumberfailsbecausethest***doesnotrepresentavalidnumber.(Inproceduralstatements,VALUE_ERRORisraised.)ThisexceptionisalsoraisedwhentheLIMIT-clauseexpressioninabulkFETCHstatementdoesnotevaluatetoapositivenumber.LOGIN_DENIEDYourprogramattemptstologontoOraclewithaninvalidusernameand/orpassword.NO_DATA_FOUNDASELECTINTOstatementreturnsnorows,oryourprogramreferencesadeletedelementinanestedtableoranuninitializedelementinanindex-bytable.SQLaggregatefunctionssuchasAVGandSUMalwaysreturnavalueoranull.So,aSELECTINTOstatementthatcallsanaggregatefunctionneverraisesNO_DATA_FOUND.TheFETCHstatementisexpectedtoreturnnorowseventually,sowhenthathappens,noexceptionisraised.NOT_LOGGED_ONYourprogramissuesadatabasecallwithoutbeingconnectedtoOracle.PROGRAM_ERRORPL/SQLhasaninternalproblem.ROWTYPE_MISMATCHThehostcursorvariableandPL/SQLcursorvariableinvolvedinanassignmenthaveincompatiblereturntypes.Forexample,whenanopenhostcursorvariableispassedtoastoredsubprogram,thereturntypesoftheactualandformalparametersmustbecompatible.SELF_IS_NULLYourprogramattemptstocallaMEMBERmethodonanullinstance.Thatis,thebuilt-inparameterSELF(whichisalwaysth